Assessment item 3NormalisationValue:15%Due date:11-Sep-2015Return date:02-Oct-2015Submission method optionsAlternative submission method
Task
Table 1: GRADE REPORT relation
| Student_ID | Student_Name | Campus_Address | Major | Course_ID | Course_title | Instructor_Name | Instructor_Location | Grade |
| 16830058 |
Williams |
Albury |
BIT |
ITC114 |
Database Sys |
Peter |
B104 |
HD |
| 16830058 |
Williams |
Albury |
BIT |
ITC211 |
Sys Analysis |
John |
B115 |
D |
| 543291073 |
Baker |
Wagga |
Acct |
ITC200 |
Database Mgt |
David |
H310 |
D |
| 543291073 |
Baker |
Wagga |
Acct |
Acc11 |
Fund Acct |
William |
A120 |
C |
| 543291073 |
Baker |
Wagga |
Acct |
Mkg21 |
Intro Mkt |
Mike |
A112 |
P |
Using Table 1: GRADE REPORT relation above
1. Draw a dependency diagram to show the functional dependencies in the relation
2. Decompose GRADE REPORT into a set of 3NF relations
3. Draw a relational schema for your 3NF relations and show the referential integrity constraints
NOTE
Manually drawing the dependency diagram or the relational schema is NOT acceptable.
Rationale
1. This assessment item is designed to test your understanding of more advanced database design, your ability to use an iterative approach to modelling, and to examine data problems due to redundancy.
2. Specifically you are required to apply the normalisation process to existing data in order to minimise redundancies and data anomalies.
Marking criteria
The marking criteria for this assignment are shown below:
| Task |
HD |
DI |
CR |
PS |
| Draw a dependency diagram to show the functional dependencies in the relation |
The dependency diagram accurately identifies and correctly shows all PKs and all functional, partial, and transitive dependencies |
The dependency diagram accurately identifies and correctly shows all PKs and most functional, partial, and transitive dependencies |
The dependency diagram identifies and shows most PKs and some functional, partial, and transitive dependencies |
The dependency diagram identifies and shows few PKs and few functional, partial, and transitive dependencies |
| Decompose GRADE REPORT into a set of 3NF relations |
The set of 3NF relations accurately identifies and correctly shows all PKs and all the relevant attributes |
The set of 3NF relations accurately identifies and correctly shows all PKs and most of the relevant attributes |
The set of 3NF relations identifies and shows most PKs and some of the relevant attributes |
The set of 3NF relations identifies and shows few PKs and few of the relevant attributes |
| Draw a relational schema for your 3NF relations and show the referential integrity constraints |
The relational schema accurately identifies and correctly shows all of the referential integrity constraints |
The relational schema accurately identifies and correctly shows most of the referential integrity constraints |
The relational schema identifies and shows some of the referential integrity constraints |
The relational schema identifies and shows few of the referential integrity constraints
|