DATABASE LAB PAGE 1 100 Points Total Developed by K. David Smith, Instructor, Computing and Technology Department Cameron University, Lawton, OK 1 Develop a relational Database in MS Access. The database must have a minimum of THREE tables. Each database must also have one input form and one report. The input form and report must be fed by a query, and not the tables. Step 1: Define the problem that requires the database as a solution. The Key to GOOD DB development is to DEFINE DEFINE DEFINE!!!! Step 2: Design the database. Develop the ERD of the database you will build in MS Access. The ERD must show: 1. Entities (people, places, things, concepts). Entities equate to tables. 2. Attributes the characteristics of the entities. Attributes equate to columns. 3. Primary Keys the identifiers of the various instances of the entities (shown by a solid underline of the attribute). 4. Foreign Keys the way Entities are linked through the relationships (shown by a dashed underline of the attribute or with a (FK) next to the name of the attribute). 5. Cardinality the types of relationships between the entities (1:1, 1:N, M:N). Use crows feet to show the many side of the relationships. ERD Reminders: 1. Primary Keys migrate to the many side of the relationship to become foreign keys. 2. Many to Many (M:N) relationships become another table in the database (see example). 3. Do not model users of the database or outputs (reports) of the database. 4. Do not use plurals in naming entities (use STUDENT not STUDENTS). 5. Name the relationships. 6. You may have to use concatenated primary keys. You can use ERDPlus to create your DB. This program can be used on a MAC or PC. https://erdplus.com/ Use the StandAlone button to create. See Diagrams Below. DATABASE LAB PAGE 2 100 Points Total Developed by K. David Smith, Instructor, Computing and Technology Department Cameron University, Lawton, OK 2 Note: setting up erdplus to make the relationship go in the correct direction is not intuitive (in fact it seems backwards. Play with it to get it right.). DATABASE LAB PAGE 3 100 Points Total Developed by K. David Smith, Instructor, Computing and Technology Department Cameron University, Lawton, OK 3 Final Product Step 3: Develop the relational database in MS Access. Populate the database with straw man data. I would recommend you use Generate Data populate your DB. You can find it at http://www.generatedata.com/ . It will put in up to 100 instances in each table. See Diagrams Below. DATABASE LAB PAGE 4 100 Points Total Developed by K. David Smith, Instructor, Computing and Technology Department Cameron University, Lawton, OK 4 Once you hit the Generate Button you will get the following dialoge box. Example of Dataset generated in Excel. DATABASE LAB PAGE 5 100 Points Total Developed by K. David Smith, Instructor, Computing and Technology Department Cameron University, Lawton, OK 5 Use the Import Wizard in Excel to bring it inot your database. You will have to make sure you have the same datatypes for each table and the column names must be the same and in the same order!!! There are possible problems with Referential Integrity as well, so you may want to wait until the tables are populated to make the relationships in the database. Also, when dealing with Referential Integrity, you must have a Primary Key (in the one table) that MATCHES a Foreign Key (in the many table). Example STUDENT Primary Key Student_ID [PK1] Non-Key Attributes Student_Name Student_Address Student_City Student_State Student_Zip Student_Telephone COURSE Primary Key Course_Prefix [PK1] Course_Number [PK2] Non-Key Attributes Course_Name Course_Hours takes SECTION Primary Key Call_Number [PK1] Semester [PK2] Year [PK3] Student_ID [PK4] [FK] Course_Prefix [PK5] [FK] Course_Number [PK6] [FK] Non-Key Attributes Room_Number Grades STUDENT Primary Key Student_ID [PK1] Non-Key Attributes Student_Name Student_Address Student_City Student_State Student_Zip Student_Telephone COURSE Primary Key Course_Prefix [PK1] Course_Number [PK2] Non-Key Attributes Course_Name Course_Hours enroll in consist of Example 1: M:N relationships become a table in the database. NOTE: in this case a CASE tool was used to develop the model, and the tool uses PK to designate Primary Keys and FK to designate Foreign Keys. Notice the concatenated (compound) keys for Tables COURSE and SECTION. DATABASE LAB PAGE 6 100 Points Total Developed by K. David Smith, Instructor, Computing and Technology Department Cameron University, Lawton, OK 6 Example Completed Database in MS Access Above. DATABASE LAB PAGE 7 100 Points Total Developed by K. David Smith, Instructor, Computing and Technology Department Cameron University, Lawton, OK 7 This is the DDL that was used to create the database example. It is meant only for reference as to how it works. You are not responsible for it. CREATE TABLE COURSE( Course_Prefix TEXT(10) NOT NULL, Course_Number TEXT(10) NOT NULL, Course_Name TEXT(10) NOT NULL, Course_Hours TEXT(10) NOT NULL) ALTER TABLE COURSE ADD CONSTRAINT COURSE_PK PRIMARY KEY (Course_Prefix,Course_Number) CREATE TABLE SECTION( Call_Number TEXT(10) NOT NULL, Semester TEXT(10) NOT NULL, Year TEXT(10) NOT NULL, Room_Number TEXT(10) NOT NULL, Grades TEXT(10) NOT NULL, Student_ID TEXT(10) NOT NULL, Course_Prefix TEXT(10) NOT NULL, Course_Number TEXT(10) NOT NULL) ALTER TABLE SECTION ADD CONSTRAINT SECTION_PK PRIMARY KEY (Call_Number,Semester,Year,Student_ID,Course_Prefix,Course_Number) CREATE TABLE STUDENT( Student_ID TEXT(10) NOT NULL, Student_Name TEXT(10) NOT NULL, Student_Address TEXT(10) NOT NULL, Student_City TEXT(10) NOT NULL, Student_State TEXT(10) NOT NULL, Student_Zip TEXT(10) NOT NULL, Student_Telephone TEXT(10) NOT NULL) ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_PK PRIMARY KEY (Student_ID) ALTER TABLE SECTION ADD CONSTRAINT enroll_in FOREIGN KEY (Student_ID) REFERENCES STUDENT (Student_ID) ALTER TABLE SECTION ADD CONSTRAINT consist_of FOREIGN KEY (Course_Prefix,Course_Number) REFERENCES COURSE (Course_Prefix,Course_Number)