Normalisation
Normalisation
1. Using the table and data given below, normalise the table to 3NF.
2. Use correct dependency diagram/s to show each normalisation level.
3. Create an ERD to show the resultant tables at the 3NF level with all the attributes, Primary/Foreign keys, relationships, etc.
1. Book_ID Book_Title Bk_ Author Date_Published Publisher Publ_Location Borrower_Num D_Borrowed D_Returned
101 Management of Information Systems Laudon 2010 Pearson NSW AR258714 25/4/2014 4/5/2014
102 Intro to Information Systems Rainer 2012 Wiley QLD HC258321 10/2/2014 16/2/2014
103 Database systems Coronel 2011 Cengage VIC AR258714 25/4/2014 6/5/2014
104 Management Cole 2013 Pearson NSW KX25894 15/3/2014 23/3/2014
102 Intro to Information Systems Rainer 2012 Wiley QLD WC258568 20/2/2014 27/2/2014
103 Database systems Coronel 2011 Cengage VIC PS258963 25/5/2014 2/6/2014
102 Intro to Information Systems Rainer 2012 Wiley QLD BW258541 18/4/2014 26/5/2014

NOTE
Manually drawing the dependency diagram or the relational schema is NOT acceptable.

Online submission via Turnitin is required for this assignment. Details will be provided by your subject lecturer.

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 table 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