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
|
I'
|
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
I
I
|
1
1
1
|
eBook
eBook
eBook
|
|
258963
|
The Adventures of Sherlock Holmes
|
Conan Doyle
|
Arthur
|
Project Gutenberg
|
|
125896
|
The Divine Comedy
|
Alighieri
|
Dante
|
Project Gutenberg
|
|
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 I'
|
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: How to normalise database relations in 3 quick steps?
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;