Database Design and SQL Assignment
DBS201 Assignment 2
Due Friday, April 04th by midnight
10% of final mark
Your group must consist of 2 or 3 people You will develop a logical database design for the following description shown later (Page 3 etc). Submissions done by a single person will receive a 20% penalty.
Required for submission:
One: (60 marks) Printed 3NF solution for each user view in this assignment
a) You are to show each step in your solution: UNF, 1NF, 2NF, 3NF .
b) You are to clearly mark Primary and Foreign Keys as shown in the sample below:
Entity1[ column1 (PK), column2, column3, column4 (FK), column5 ]
Entity2[ column4 (PK) , column 6, …….. ]
Two: (10 marks)
Merged Logical design in 3NF format for the solution One. The merged logical design is also referred to as the Final Logical 3NF Design.
1. Merging is accomplished by grouping all attributes of 3NF entities that have the identical Primary Key into a single consolidated entity.
2. After merging, make the attribute names consistent.
3. You may also need to eliminate some new transitive entities as some entities may have gone back to 2NF.
4. At this stage you should closely inspect any resulting entiites that have a concatenated Primary Key to ensure that they truly represent a “Many-to-Many relation. When the relation is actually a “One-to-Many” relation, you must correct the Primary Key by removing one or more columns from the PK and including them as non-key attribute(s).
5. Check that all necessary Bridge Entities exist and create them if needed.
Three: (10 marks) An ERD that corresponds to your Final 3NF Design (solution Two).
The ERD may be printed or drawn by hand. Show only the name of the Entity, the PK, all Foreign Keys and lines joining the Entities. You should use the Crow’s Foot method.
Four:
Due Friday, April 04th by midnight
10% of final mark
Your group must consist of 2 or 3 people You will develop a logical database design for the following description shown later (Page 3 etc). Submissions done by a single person will receive a 20% penalty.
Required for submission:
One: (60 marks) Printed 3NF solution for each user view in this assignment
a) You are to show each step in your solution: UNF, 1NF, 2NF, 3NF .
b) You are to clearly mark Primary and Foreign Keys as shown in the sample below:
Entity1[ column1 (PK), column2, column3, column4 (FK), column5 ]
Entity2[ column4 (PK) , column 6, …….. ]
Two: (10 marks)
Merged Logical design in 3NF format for the solution One. The merged logical design is also referred to as the Final Logical 3NF Design.
1. Merging is accomplished by grouping all attributes of 3NF entities that have the identical Primary Key into a single consolidated entity.
2. After merging, make the attribute names consistent.
3. You may also need to eliminate some new transitive entities as some entities may have gone back to 2NF.
4. At this stage you should closely inspect any resulting entiites that have a concatenated Primary Key to ensure that they truly represent a “Many-to-Many relation. When the relation is actually a “One-to-Many” relation, you must correct the Primary Key by removing one or more columns from the PK and including them as non-key attribute(s).
5. Check that all necessary Bridge Entities exist and create them if needed.
Three: (10 marks) An ERD that corresponds to your Final 3NF Design (solution Two).
The ERD may be printed or drawn by hand. Show only the name of the Entity, the PK, all Foreign Keys and lines joining the Entities. You should use the Crow’s Foot method.
Four: