Normalisation
Value: 13%
Due date: 04-Sep-2017
Return date: 26-Sep-2017
Submission method options
Alternative submission method
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.
Submission Method: Turnitin. Details will be provided by the lecturer during the session.