NormalisationValue:13%Due date:22-Apr-2018Return date:15-May-2018Submission method optionsAlternative submission method
Task
Table 1: Home Library
ISBN |
Title |
Author_LastName |
Author_FirstName |
Publisher |
Date |
Edition |
Media |
|---|
369852 |
Cosmos |
Sagan |
Carl |
Random House |
1980 |
1 |
Book |
741258 |
No Secrets |
Simon |
Carly |
Elektra |
1972 |
1 |
CD |
654789 |
Symphony No 3 Dur Eroica Op 55 |
Beethoven |
Ludwig |
|
1805 |
1 |
CD |
789654 |
On the Decay of the Art of Lying |
Twain |
Mark |
Project Gutenberg |
1880 |
1 |
eBook |
258963 |
The Adventures of Sherlock Holmes |
Conan Doyle |
Arthur |
Project Gutenberg |
|
1 |
eBook |
125896 |
The Divine Comedy |
Alighieri |
Dante |
Project Gutenberg |
|
1 |
eBook |
357951 |
The Hitchhikers Guide to the Galaxy |
Adams |
Douglas |
Pan books |
1979 |
1 |
Book |
852369 |
The Return of the King, Soundtrack |
Shore |
Howard |
Reprise |
2003 |
1 |
CD |
831975 |
Unseen Academicals |
Pratchett |
Terry |
Doubleday |
2009 |
1 |
Book |
Using the Home library relation above:
1. Draw a dependency diagram to show the functional dependencies that exist in this relation.
2. Decompose the Home Library relation into a set of 3NF relations.
3. Draw the Relational Schema for each of these 3NF relations and show the referential integrity constraints that apply.
NOTE
For guidance on how to normalise database relations from a dependency diagram, watch this very short YouTube video: https://www.youtube.com/watch?v=vkUyuLLgmwQ
Manually drawing the dependency diagram, or the set of 3NF relations or the relational schema is NOT acceptable.
Rationale
This assessment aligns with the following subject outcomes:
- be able to critically analyse a database design and apply normalisation theory and techniques;
Marking criteria
Each task is worth5 marks. The marking criteria for this assignment is:
TASKS
|
HD
|
DI
|
CR
|
PASS
|
FAIL
|
TASKS 1 |
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 dependenciesrelations and includes the related entities, PKs, attributes, relationships, associative entities (if applicable), cardinalities and optionalities. |
The dependency diagram identifies and shows few or no PKs and few or no functional, partial, and transitive dependencies and optionalities. |
TASK 2 |
The set of relations is in 3NF and accurately identifies all PKs and all the relevant attributes |
The set of relations is in 3NF and accurately identifies all PKs and most of the relevant attributes |
The set of relations is in 3NF identifies most PKs and some of the relevant attributes |
The set of relations is mainly in 3NF and identifies some PKs and some of the relevant attributes |
The set of relations is not in 3NF and identifies few or no PKs and few or none of the relevant attributes |
TASK 3 |
The relational schema accurately describes each entity and correctly shows all of the referential integrity constraints |
The relational schema accurately describes each entity and correctly shows most of the referential integrity constraints |
The relational schema describes each entity and shows some of the referential integrity constraints |
The relational schema describes most entities and shows some of the referential integrity constraints |
The relational schema describes few entities and shows few or none of the referential integrity constraints |
Requirements
Either save yourdiagrams in aPDF file or embedthem in a Word document.
Don't use any other file formats
Please include your student ID, name and subject code inyour submission.