Case Study 8: Dirt Road Driving (Payroll System — Anomalies and FDs)
Practice for 2026-04-27-database-design-guidelines-and-functional-dependencies. Dirt Road Driving’s Director of Innovation, Peter Thompson, has asked for student help auditing their payroll/finance system’s database schema — some staff feel the current design is inefficient.
The payroll schema
Note: this is Peter’s first schema email — it’s missing FDs for EmployeeHistory, TripExpenseAllocations and TravelInsuranceHistory (he sends a corrected, more complete version later, used in week11-tutorial-case-study-9-payroll-system). For Section 2 below, those FDs must be identified from the sample data instead.
Employee [id, firstName, lastName, role]
Project [name, description, funding, projectLeader]
Project.projectLeader references Employee.id
TimeLog [employeeID, projectName, date, hoursWorked, approved]
TimeLog.employeeID references Employee.id
TimeLog.projectName references Project.name
AssetUse [employeeID, assetID, timestamp, useDuration, assetType, purchaseDate, insuranceValue]
assetID → assetType, purchaseDate
assetType → insuranceValue
AssetUse.employeeID references Employee.id
Department [code, name, manager, buildingID, buildingName, buildingLocation, floor]
buildingID → buildingLocation, buildingName
buildingName → buildingLocation, buildingID
Department.manager references Employee.id
EmployeeHistory [employeeID, departmentCode, dateStarted, seniorityLevel, baseSalary, securityLevel]
EmployeeHistory.employeeID references Employee.id
EmployeeHistory.departmentCode references Department.code
TripExpenseAllocations [tripName, expenseType, quantity, organiser, startDate, endDate, location, allowance, restrictions, description]
TripExpenseAllocations.organiser references Employee.id
TravelInsuranceHistory [tripName, approved, insuranceLevel, description, maxCoverage, advisedPrecautions]
TravelInsuranceHistory.tripName references TripExpenseAllocations.tripNameSample data (relevant excerpts):
AssetUse
employeeID assetID timestamp useDuration assetType purchaseDate insuranceValue
2014 1200 20-12-2019 11:04:14 00:15:02 Vehicle 16-02-2019 20,000
2014 7900 24-12-2019 18:54:01 01:12:52 Vehicle 23-10-2019 20,000
2020 7901 01-01-2020 13:07:59 05:00:09 Power Tools 05-05-2019 1,000
2014 1200 01-01-2020 14:47:08 02:45:36 Vehicle 16-02-2019 20,000
Department
code name manager buildingID buildingName buildingLocation floor
MAK Marketing 2020 0302 Dumpling Building (-27.4907639, 152.9955379) 8
FIN Finance 2021 0302 Dumpling Building (-27.4907639, 152.9955379) 7
IT Computering 2023 2023 Hotpot Building (-27.4856679, 152.9898608) 2
EmployeeHistory
employeeID departmentCode dateStarted seniorityLevel baseSalary securityLevel
2020 FIN 13-09-2019 Junior 70,000 Limited
2023 IT 23-09-2020 Junior 70,000 Limited
1919 IT 05-11-2019 Junior 70,000 Limited
2014 FIN 07-11-2019 Junior 70,000 Limited
2019 IT 07-11-2019 Junior 70,000 Limited
2020 MAK 09-11-2019 Executive 80,000 Full Access
2021 FIN 18-11-2019 Senior 70,000 Full Access
2022 IT 19-11-2019 Junior 70,000 Limited
2023 IT 07-01-2020 Executive 80,000 Full Access
TripExpenseAllocations
tripName expenseType organiser startDate endDate location allowance restrictions
UQ Partnership Food 2023 02-02-2020 03-02-2020 Brisbane 200 No alcohol over $70
UQ Partnership Hotel 2023 02-02-2020 03-02-2020 Brisbane 500 Food service not included
UQ Partnership Transport 2023 02-02-2020 03-02-2020 Brisbane 100 Cannot use Uber or DiDi
Ride-share Marketing Conf. Hotel 2020 21-03-2020 28-03-2020 Sydney 500 Food service not included
Ride-share Marketing Conf. Transport 2020 21-03-2020 28-03-2020 Sydney 100 Cannot use Uber or DiDi
Investor Meeting Food 2021 27-03-2020 28-03-2020 Cairns 200 No alcohol over $70
TravelInsuranceHistory
tripName insuranceLevel description maxCoverage
UQ Partnership 4 Local travel without unsafe circumstances 1,000,000
Ride-share Marketing Conf. 1 Any travel with unsafe/govt-conflicting circs. 10,000
Investor Meeting 1 Any travel with unsafe/govt-conflicting circs. 10,000Section A — Anomalies
Provide and explain one example each of a modification, deletion, and insertion anomaly for the AssetUse and Department tables. (≤100 words each — the exercise must show practically* how each anomaly applies, not just restate the textbook definition.)*
AssetUse
Modification anomaly: updating the tuple <2014, 1200, 20-12-2019 11:04:14, 00:15:02, "Vehicle", 16-02-2019, 20,000> to <2014, 1200, 20-12-2019 11:04:14, 00:15:02, "Vehicle", 18-03-2019, 20,000> — since assetID → purchaseDate, and assetID = 1200 also appears in another row (with timestamp = 01-01-2020 14:47:08), that other row’s purchaseDate would also need updating to 18-03-2019, otherwise the table becomes inconsistent about when asset 1200 was purchased.
Deletion anomaly: deleting <2020, 7901, 01-01-2020 13:07:59, 05:00:09, "Power Tools", 05-05-2019, 1,000> — this is the only row recording asset 7901, so deleting it loses that asset’s assetType and purchaseDate entirely, even though those facts are logically about the asset, not about this particular use-event.
Insertion anomaly: to record a new asset that hasn’t been used yet, we’d need to insert <null, 2200, null, null, "Power Tools", 29-11-2019, 1,000> — but employeeID and timestamp form the primary key, so a null primary key violates entity integrity. A new asset can’t be entered until someone has used it.
Department
Modification anomaly: updating <"FIN", "Finance", 2021, 0302, "Dumpling Building", (-27.4907639, 152.9955379), 7>’s buildingLocation alone — since buildingID → buildingLocation, the other row with buildingID = 0302 (“MAK”) would also need updating, otherwise the two rows disagree about where building 0302 is.
Deletion anomaly: deleting <"IT", "Computering", 2023, 2023, "Hotpot Building", (-27.4856679, 152.9898608), 2> — this is the only row referencing buildingID = 2023, so deleting it loses the buildingName and buildingLocation facts for that building.
Insertion anomaly: recording a new building (buildingID = 2013, “Peking Duck Building”) that isn’t yet occupied by any department requires inserting <null, null, null, 2013, "Peking Duck Building", (-27.566544, 152.917845), null> — a null primary key (code), which violates entity integrity.
Section 2 — Functional dependencies and highest normal form
Based on the sample data above, identify all non-trivial functional dependencies (beyond the given primary keys) for the tables below, and their highest normal form. As the primary keys are given in the schema, ignore FDs for the primary key itself.
EmployeeHistory
FD: seniorityLevel → baseSalary, securityLevel
Every row sharing a seniorityLevel agrees on baseSalary and securityLevel — all six Junior rows have {70,000, Limited}; both Executive rows have {80,000, Full Access}.
Highest normal form: 2NF. This FD is a transitive dependency — its left-hand side (seniorityLevel) is not a superkey, and its right-hand side contains non-prime attributes, violating 3NF. (The LHS is also not a subset of the candidate key {employeeID, departmentCode}, so it doesn’t violate 2NF.)
TripExpenseAllocations
FDs: tripName → startDate, endDate, location, organiser; expenseType → allowance, restrictions
All three “UQ Partnership” rows agree on organiser/dates/location; expenseType = 'Food' (rows for “UQ Partnership” and “Investor Meeting”) always has allowance = 200 and the same restriction text.
Highest normal form: 1NF. The first FD is a partial dependency — its left-hand side (tripName) is a prime attribute (part of the candidate key {tripName, expenseType}) but not itself a superkey, and its right-hand side contains non-prime attributes, violating 2NF.
TravelInsuranceHistory
FD: insuranceLevel → description, maxCoverage
Both rows with insuranceLevel = 1 (“Ride-share Marketing Conference” and “Investor Meeting”) share the same description and maxCoverage = 10,000.
Highest normal form: 1NF. Same shape of violation as above — the left-hand side (insuranceLevel) is a prime attribute but not a superkey, and the right-hand side contains non-prime attributes, violating 2NF.