Question

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)

Answer 1

Check the explanation

Step-by-step explanation:

(1.) The functional dependencies based on assumptions are as follows:-

Trip determines which staff was there in the trip, the car used for the trip, the quantity of fuel used in the trip, the fill up time and the total paid of the trip.

Service station refers to its address

Car details consist of the details of license plate, odometer reading and the fuel type used in the car

fuel type and the fill up time will help to determine the cost of the fuel at that time

cost of the fuel and the quantity of fuel used will determine the total paid

From the above assumptions the functional dependencies are as follows:-

Trip ID -> Staff Name, Car Details, Service Station, Quantity, Fill Up Time, Total Paid

Service Station -> Station Address,

Car Details -> License Plate, Odometer reading, Fuel Type

Fuel Type, Fill up Time -> Cost per litre

Cost per Litre, Quantity -> Total Paid

(2.) Initially the relation is as follows:-

R (Trip ID, Staff Name, Car Details, License Plate, Odometer Reading, Service Station, Station Address, Fill up Time, Fuel type, Quantity, Cost per Litre, Total Paid)

For 1 NF there should not be composite attributes,in the above relation Staff Name and Station Address are composite attributes therefore it will be decomposed as follows:-

R (Trip ID, Staff First Name,Staff Last Name, Car Details, License Plate, Odometer Reading, Service Station, Street, City, State, Zip Code, Fill up Time, Fuel type, Quantity, Cost per Litre, Total Paid)

For 2 NF there should not be partial dependency that is non prime attribute should not dependent upon any 1 prime attribute of candidate key, since in the given relation there is only 1 candidate key which is only Trip ID, therefore the relation is already in 2 NF.

For 3 NF there should not be transitive dependency that is non prime attribute should not dependent upon the other non prime attribute, if it is present then the relation will be decomposed into sub relations, the given relation is not in 3 NF therefore it will be decomposed as follows:-

R1 (Trip ID, Staff First Name, Staff Last Name, Car Details, Service Station, Quantity, Fill up time, Total paid)

R2 (Service Station, Street, City, State, Zip Code)

R3 (Car Details, License Plate, Odometer reading, Fuel Type)

R4 (Fuel Type, Fill up Time, Cost per litre)

R5 (Cost per Litre, Quantity, Total Paid)

(3.) After the 3 NF we add the primary and foreign key constraints as follows:-

R1 (Trip ID, Staff First Name, Staff Last Name, Car Details, Service Station, Quantity, Fill up time, Total paid)

R2 (Service Station, Street, City, State, Zip Code)

R3 (Car Details, License Plate, Odometer reading, Fuel Type)

R4 (Fuel Type, Fill up Time, Cost per litre)

R5 (Cost per Litre, Quantity, Total Paid)

In the above relational schema the primary keys are represented with bold and foreign keys by italic

(4.) The ER diagram of the above relational schema using crow's foot notation are as shown in the attached image below:-