Exercise 2 – Database Design
Read below everything very carefully:
1. Use Microsoft Access to create TWO “BLANK databases” including all the tables for the TWO database designs shown on page 2 and page 3 of this document. By creating a “Blank database”, we mean that you will design the database by creating the structure for all tables but will not enter any data in any of the tables.
Pay attention to the following two points while creating the tables for the two databases:
a. Use “Short Text” as the data type for the Primary Key attribute in EVERY table. For other attributes, choose the data type by using your judgment / common sense about the type of data being stored there; for example, StudentFname will be “Short Text” type while UnitPrice will be “Currency” type. Likewise, OrderQty will be “Number” type.
b. DO NOT use “AutoNumber” datatype for ANY attribute including the primary keys.
2. Create the TWO relationship diagrams among the tables in the two database files created in step 1. Pay attention to the following points while creating the relationships among tables for the two databases:
a. Note that every table must have the Primary Key correctly setup for the relationship diagram to be accurate.
b. While connecting the primary key to a foreign key, please note that sometimes the names of the primary key and foreign key may be different. For example, in the Class_Enrollment_Database, “Term_Code” is the primary key in the Terms table and when we use it as a foreign key in the Class_Sections table, we call it CRN_Term. However, data type of primary key and foreign key must match. Understand the difference between the name of the attribute (for example, CRN) and its data type (for example, Short Text).
c. All the attributes in each table must be clearly visible with no extra white space AND no scroll bar.
d. Make sure you save Each database file by prefixing the current name of the file with your FirstName_LastName. For example in my case, the first database file will be saved as Vipin_Arora_Class_Enrollments.accdb.
3. For each database file, make a snip of the relationship diagram using the “snipping tool” just like we did earlier for exercise 1. Paste the snipped image onto a MS-Word file. You will have TWO images for relationship diagrams from the two databases. Below every image include a caption with a Figure number. Save the word file as your FirstName_LastName_Exercise2.
4. Double check everything before submitting the assignment. Use the following checklist:
a. All tables must have a primary key.
b. All the attributes in each table must be clearly visible with no extra white space. The scroll bar should not be visible.
c. The relationship diagrams look professional. You should have a “1” on one side and the “infinity symbol” on the other side of the arrow connecting the primary key with the foreign key for every relationship.
d. Every table must be connected with at least one other table.
e. There should not be any criss-crossing of the relationship lines connecting the tables.
f. All files are named as required.
5. Upload on Canvas the two database files and the MS-Word file with the snipped images. So, in total you will upload THREE files.
Once again, note that you are creating TWO blank databases and not entering any data in any table. In other words, you are creating a structure for the databases by connecting the tables using primary and foreign keys, such that only consistent data will be allowed to be entered.
