Draw a Dependency Diagram To Show The Functional Dependencies In The Relation.

Question

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.

 

 

 

DEPENDENCY DIAGRAM

P_CODE

PNAME

P_PUR_DATE

SOLD_QTY

PRICE

I_NO

IDATE

VID

VNAME

DEPENDENCY DIAGRAM – 3NF

P_CODE

PNAME

P_PUR_DATE

PRICE

RELATIONAL SCHEMA

{P_CODE}  ———> {PNAME}

{ P_CODE }  ———> { PNAME, P_PUR_DATE, PRICE}

{VID}  ———> {VNAME}

{I_NO}  ———> {IDATE}

{P_CODE, VID, I_NO} ——-> {SOLD_QTY}

RELATIONAL SCHEMA DIAGRAM

DATABASE IN MYSQL WORKBENCH

 

 

ER DIAGRAM

 

0

Leave an answer

Browse
Browse