Normalisation
Value: 45%
Due date: 04-Sep-2017
Return date: 26-Sep-2017
Task
Prod_code Prod_Name Purchase_date Qty_sold Prod_price Inv_No Inv_Date Vendor_ID Vendor_Name
ES12345 Esky Soft Cooler 12/7/2016 60 19.50 B-5565 17/7/2016 112 Innisfail
MAK2347 Angle Grinder 15/9/2016 10 85 B-5570 18/9/2016 106 RC Tools
KAR62705 High Pressure Cleaner 13/10/2016 50 190 B-5572 18/9/2016 103 Ardonly
AEC25874 Circular Saw 15/9/2016 15 119 B-5570 18/9/2016 106 RC Tools
ES2396 Water filter 20/8/2016 45 26.50 B-5585 25/8/2016 112 Innisfail
Using above table
1. Draw a dependency diagram to show the functional dependencies in the relation
2. Decompose the relation into a set of 3NF relations
3. Draw a relational schema for your 3NF relations and show the referential integrity constraints
4. Create an ERD that represents your 3NF relations

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 or the ERD 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 3.25 marks. The marking criteria for this assignment is:

Criteria HD DI CR PS
be able to critically analyse a database design and apply normalisation theory and techniques; Analysis identifies all PKs and functional, partial and transitive dependencies. Accurately identifies all 3NF relations, including PKs, relevant attributes and referential integrity constraints. Creates an ERD that represents all 3NF relations and includes all entities, PKs, attributes, relationships, associative entities (if applicable), cardinalities and optionalities. Analysis identifies PKs and functional, partial and transitive dependencies with minor omissions. Accurately identifies 3NF relations, including PKs, relevant attributes and referential integrity constraints with minor omissions. Creates an ERD that represents identified 3NF relations and includes all entities, PKs, attributes, relationships, associative entities (if applicable), cardinalities and optionalities. Analysis identifies key PKs and functional, partial and transitive dependencies. Identifies key 3NF relations, including PKs and relevant attributes and referential integrity constraints. Creates an ERD that represents identified 3NF relations and includes the related entities, PKs, attributes, relationships, associative entities (if applicable), cardinalities and optionalities. Analysis identifies key PKs and functional dependencies and draws out relevant 3NF relations. Creates an ERD that represents identified 3NF relations and includes the related entities, PKs, attributes, relationships, cardinalities and optionalities.
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.