Assessment item - Normalisation
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.
Rationale -
This assessment aligns with the following subject outcomes: be able to critically analyse a database design and apply normalisation theory and techniques;