In this task, you will perform normalization on a crude table so that it could be stored as a relational database.
The staff at ‘Franklin Consulting’ have to routinely visit their clients in other cities. Franklin have a fleet of cars available for staff travels. During their trips, the staff sometimes need to fill up the car fuel. Upon returning from the trip, the staff claim that expenses back by providing the fueling receipt and some other essential information. The accountant records all of that information in a spreadsheet. Below are the spreadsheet column headers and a sample of data from each column.
In this task, you will perform normalization on a crude table so that it could be stored as a relational database.
The staff at ‘Franklin Consulting’ have to routinely visit their clients in other cities. Franklin have a fleet of cars available for staff travels. During their trips, the staff sometimes need to fill up the car fuel. Upon returning from the trip, the staff claim that expenses back by providing the fueling receipt and some other essential information. The accountant records all of that information in a spreadsheet. Below are the spreadsheet column headers and a sample of data from each column.
Assessment item 3 – Normalisation
back to top
Value: 15%
Due Date: 03-May-2020
Return Date: 25-May-2020
Submission method options: Alternative submission method
TASK
back to top
In this task, you will perform normalization on a crude table so that it could be stored as a relational database.
The staff at ‘Franklin Consulting’ have to routinely visit their clients in other cities. Franklin have a fleet of cars available for staff travels. During their trips, the staff sometimes need to fill up the car fuel. Upon returning from the trip, the staff claim that expenses back by providing the fueling receipt and some other essential information. The accountant records all of that information in a spreadsheet. Below are the spreadsheet column headers and a sample of data from each column.
Column Name |
Example Data |
Trip ID |
4129 |
Staff name |
Sarah James |
Car details |
Toyota Land Cruiser 2015 |
License Plate |
1CR3KT |
Odometer reading |
25,067 |
Service station |
Coles Express |
Station address |
27 Queen St, Campbelltown, NSW 2560 |
Fill up time |
30 Jan 2020, 2:45 pm |
Fuel type |
Unleaded 95 |
Quantity litres |
55 |
Cost per litre |
$1.753 |
Total paid |
$96.42 |
Given the information in above table,
1. Draw a dependency diagram to show the functional dependencies existing between columns. State any assumptions you make about the data and the attributes shown in the table. (3 marks)
2. Show the step by step process of decomposing the above table into a set of 3NF relations. (5 marks)
3. Review the design of your 3NF relations and make necessary amendments to define proper PKs, FKs and atomic attributes. Any additional relations may also be defined at this stage. Make sure all of your attributes conform to the naming conventions. (3 marks)
4. Draw the Crow’s Foot ERD to illustrate your final design. (4 marks)
For guidance on how to normalise database relations from a dependency diagram, watch this very short video:
https://www.youtube.com/watch?v=vkUyuLLgmwQ
All dependency diagrams and ERD must be created using a drawing tool. Hand-drawn diagrams are NOT acceptable.
RATIONALE
back to top
This assessment task will assess the following learning outcome/s:
· be able to model complex business rules using Entity Relationship Diagrams (ERDs).
·
be able to apply normalisation techniques to optimise a database design.
MARKING CRITERIA AND STANDARDS
back to top
Criteria |
HD |
DI |
CR |
PS |
be able to apply normalisation techniques to optimise a database design. |
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
back to top
Export your diagrams as an image and include them in your assignment Word document.