Unformatted Attachment Preview
Assessment item 3
Normalisation
Value: 13%
Due date: 04-Sep-2017
Return date: 26-Sep-2017
Submission method options
Alternative submission method
Task
Prod_code
ES12345
MAK2347
KAR62705
AEC25874
ES2396
Prod_Name
Esky Soft
Cooler
Angle Grinder
High Pressure
Cleaner
Circular Saw
Water filter
Purchase_date Qty_sold Prod_price Inv_No
12/7/2016
60
19.50
B-5565
Inv_Date
17/7/2016
Vendor_ID
112
Vendor_Name
Innisfail
15/9/2016
13/10/2016
10
50
85
190
B-5570
B-5572
18/9/2016
18/9/2016
106
103
RC Tools
Ardonly
15/9/2016
20/8/2016
15
45
119
26.50
B-5570
B-5585
18/9/2016
25/8/2016
106
112
RC Tools
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
be able to critically Analysis identifies
analyse a database all PKs and
design and apply
functional, partial
normalisation theory and transitive
and techniques;
dependencies.
Accurately
identifies all 3NF
relations, including
PKs, relevant
attributes and
DI
CR
Analysis identifies Analysis identifies
PKs and functional, key PKs and
partial and transitive functional, partial
dependencies with and transitive
minor omissions.
dependencies.
Accurately
Identifies key 3NF
identifies 3NF
relations, including
relations, including PKs and relevant
PKs, relevant
attributes and
attributes and
referential integrity
PS
Analysis
identifies
key PKs and
functional
dependencies
and draws
out relevant
3NF
relations.
Creates an
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.
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.
constraints. Creates
an ERD that
represents identified
3NF relations and
includes the related
entities, PKs,
attributes,
relationships,
associative entities
(if applicable),
cardinalities and
optionalities.
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.
Assessment item 3
Normalisation
Value: 13%
Due date: 04-Sep-2017
Return date: 26-Sep-2017
Submission method options
Alternative submission method
Task
Prod_code
ES12345
MAK2347
KAR62705
AEC25874
ES2396
Prod_Name
Esky Soft
Cooler
Angle Grinder
High Pressure
Cleaner
Circular Saw
Water filter
Purchase_date Qty_sold Prod_price Inv_No
12/7/2016
60
19.50
B-5565
Inv_Date
17/7/2016
Vendor_ID
112
Vendor_Name
Innisfail
15/9/2016
13/10/2016
10
50
85
190
B-5570
B-5572
18/9/2016
18/9/2016
106
103
RC Tools
Ardonly
15/9/2016
20/8/2016
15
45
119
26.50
B-5570
B-5585
18/9/2016
25/8/2016
106
112
RC Tools
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
be able to critically Analysis identifies
analyse a database all PKs and
design and apply
functional, partial
normalisation theory and transitive
and techniques;
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.
DI
CR
Analysis identifies Analysis identifies
PKs and functional, key PKs and
partial and transitive functional, partial
dependencies with and transitive
minor omissions.
dependencies.
Accurately
Identifies key 3NF
identifies 3NF
relations, including
relations, including PKs and relevant
PKs, relevant
attributes and
attributes and
referential integrity
referential integrity constraints. Creates
constraints with
an ERD that
minor omissions.
represents identified
Creates an ERD that 3NF relations and
represents identified includes the related
3NF relations and entities, PKs,
includes all entities, attributes,
PKs, attributes,
relationships,
relationships,
associative entities
associative entities (if applicable),
(if applicable),
cardinalities and
cardinalities and
optionalities.
optionalities.
PS
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.