Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

Assessment item 3 Normalisation Value: 13% Due date: 21-Apr-2018 Return date: 15-May-2018 Submission method options Alternative submission method Task Table 1: Home Library...

1 answer below »
Assessment item 3
Normalisation
Value: 13%
Due date: 21-Apr-2018
Return date: 15-May-2018
Submission method options
Alternative submission method
Task
Table 1: Home Library

ISBNTitleAuthor_LastNameAuthor_FirstNamePublisherDateEditionMedia369852CosmosSaganCarlRandom House19801Book741258No SecretsSimonCarlyElektra19721CD654789Symphony No 3 Dur Eroica Op 55BeethovenLudwig 18051CD789654On the Decay of the Art of LyingTwainMarkProject Gutenberg18801eBook258963The Adventures of Sherlock HolmesConan DoyleArthurProject Gutenberg 1eBook125896The Divine ComedyAlighieriDanteProject Gutenberg 1eBook357951The Hitchhikers Guide to the GalaxyAdamsDouglasPan books19791Book852369The Return of the King, SoundtrackShoreHowardReprise20031CD831975Unseen AcademicalsPratchettTerryDoubleday20091Book
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 worth 5 marks. The marking criteria for this assignment is:

TASKSHD
DI

CR
PASS
FAIL
TASKS 1The 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 2The set of relations is in 3NF and accurately identifies all PKs and all the relevant attributesThe set of relations is in 3NF and accurately identifies all PKs and most of the relevant attributesThe set of relations is in 3NF identifies most PKs and some of the relevant attributesThe set of relations is mainly in 3NF and identifies some PKs and some of the relevant attributesThe set of relations is not in 3NF and identifies few or no PKs and few or none of the relevant attributesTASK 3The relational schema accurately describes each entity and correctly shows all of the referential integrity constraintsThe relational schema accurately describes each entity and correctly shows most of the referential integrity constraintsThe relational schema describes each entity and shows some of the referential integrity constraintsThe relational schema describes most entities and shows some of the referential integrity constraintsThe relational schema describes few entities and shows few or none of the referential integrity constraints

Requirements
Either save your diagrams in a PDF file or embed them in a Word document.
Don't use any other file formats
Please include your student ID, name and subject code in your submission.
Answered Same Day Apr 12, 2020

Solution

Navashree answered on Apr 17 2020
136 Votes
1. Dependency Diagram
From the given Home Li
ary table, following functional dependencies have been identified.
FD 1: ISBN Title, Author_LastName, Author_FirstName, Publisher, Edition, Media, Date
The dependency diagram of the unnormalized table is,
    ISBN
    Title
    Author_ LastName
    Author_ FirstName
    Publishe
    Date
    Edition
    Media
2. Normalization
Initially, the primary key can be the ISBN column for the initial table. Let it be R1. So, the schema is,
R1 (ISBN, Title, Author_FirstName, Author_LastName, Publisher, Date, Edition, Media)
Now, the relation R1 is in 1NF.
However, all non-prime attributes of R1 are not fully functionally dependent on the ISBN. Or, in...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here