INFS1200/7900 Information Systems – Assignment 3 (7.5 marks)
Due (revised): Thursday 14 May 2020 @ 11:59 PM
The purpose of this task is to foster understanding of functional dependencies, normalisation and their
practical application in modern database systems. You will also develop experience regarding how to apply
normalisation theories in an industry setting.
1. Task
Using the correspondence on the pages below, complete the following tasks. Please ask your tutors for help
if you require clarification on any aspects of the brief.
Note: The emails below contain a database schema including functional dependencies. Please complete
Assignment 3 using the schema below as your starting point, and not your own version that you submitted
for earlier Assignments.
Part 1- Analysis and Discussion (3 marks)
In the correspondence presented below, there has been some contentions raised regarding the efficiency of
Dirt Road Driving’s new payroll/finance backend system. Based on his correspondence (including
Attachments 1 2) complete the following task. Provide and explain two examples of each of the following:
• Modification anomaly
• Deletion anomaly
• Insertion anomaly
In doing so, make sure you give at least one example based on each of the five tables:
• AssetUse,
• Department,
• EmployeeHistory,
• TripExpenseAllocations,
• TravelInsuranceHistory.
That is, six examples in total, covering the five tables, so one table will be the subject of two examples.
Your explanation must not simply explain the textbook definition of a certain anomaly, instead it must show
practically how it applies to this table. Explanations must also be succinct and at most should not exceed
100 words. An example of the format required has been provided below based of your textbook’s example
schema (Ch 14, Figure 14.3 14.4):
Example Schema – Modification Anomaly
Anomaly example Update the tuple <123456789, 1, 32.5, “Smith, John B.”, “ProductX”,
“Bellaire”> to <123456789, 1, 32.5, “Smith, John B.”, “ProductAAAAA”,
“Bellaire”> in the relation “EMP_PROJ”
Explanation Based on the functional dependencies provided Pname is specific for
each Pnumber. Hence, changing this tuples “ProductX” to “ProductAAA”
would mean that one other tuple containing “ProductX” would also have
to be updated, otherwise the tables tuples would be inconsistent.
Part 2 – BCNF Normalisation (2.5 marks)
Using Attachments 1 2 from the correspondence below, decompose (if needed) the following five relations
into BCNF. You should also clearly state any new tables which are created in the process.
Tables: AssetUse, Department, EmployeeHistory, TripExpenseAllocations, TravelInsuranceHistory
Part 3 –3NF Normalisation (2 marks)
Using Attachments 1 2 from the correspondence below, decompose (if needed) the relations Department
and TripExpenseAllocations into 3NF. You should also clearly state any new tables which are created in the
process.
2. Submission
Submissions will be done via Blackboard. Marking will be done through an electronic marking tool called
Gradescope, which will also be used for providing feedback.
Please use the supplied answer template for all answers. Your work must fit in the predefined sections
or it will not be marked
Submit your assignment electronically via the provided link on the INFS1200/INFS7900 Blackboard site
under the Assessment folder.
3. Marking
The parts of Assignment 3 have marks as indicated, totalling 7.5 marks (of 30 marks for all four assignments).
4. Plagiarism
The University has strict policies regarding plagiarism. Penalties for engaging in unacceptable behaviour
can range from cash fines or loss of grades in a course, through to expulsion from UQ. You are required to
read and understand the policies on academic integrity and plagiarism in the course profile (Section 6.1).
If you have any questions regarding acceptable level of collaboration with your peers, please contact either
the lecturer or your tutor for guidance. Please do not post assignment questions on public discussion forums.
Correspondence:
From:
To:
Date: 17/4/2020 04:15 PM
Subject: RE: Student Support for Industry Project
Hi Elaine,
Thank you for sending back those student responses regarding our ERR mapping and payroll system. I have
had a meeting with our IT department, and they were extremely impressed by the high quality of work
from your student cohort.
Actually, during the course of that meeting my IT staff presented me with another potential project they
would like your student team’s feedback on. Unfortunately, it is again related to our “wonderful”
payroll/finance system. One of our system administrators feels that the current database schema for the
payroll system is inefficient and as such needs to be changed. However, some of the developers feel that
the current backend is fine and that no changes are necessary. Would your student teams be able to
complete an audit of our payroll system and suggest some changes if necessary?
I have attached a larger sample of our payroll database schema (including functional dependencies) and
more sample data. I have not included functional dependencies which can be determined from the table’s
primary keys.
Please let me know if you student teams require any further information. Also, please pass on our sincere
appreciations to them for their help!
Kind regards,
Peter Thompson
Director of Innovation | Dirt Road Driving
SEE NEXT PAGE FOR ATTACHMENTS
Attachment 1: Payroll Schema
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]
seniorityLevel → baseSalary, securityLevel
EmployeeHistory.employeeID references Employee.id
EmployeeHistory.departmentCode references Department.code
TripExpenseAllocations[tripName, expenseType, quantity, organiser, startDate, endDate, location,
allowance, restrictions, description]
tripName → startDate, endDate, location, organiser
expenseType → allowance, restrictions
TripExpenseAllocations.organiser references Employee.id
TravelInsuranceHistory[tripName, approved, insuranceLevel,description, maxCoverage,
advisedPrecautions]
insuranceLevel → description, maxCoverage
TravelInsuranceHistory.tripName references TripExpenseAllocations.tripName
Attachment 2: Payroll Sample Data
Employee
id firstName lastName role
1919 Diluen Smith Developer
2014 Daniel Johnson Administration
2019 Annie Fang Developer
2020 Russell Turner Manager
2021 Kate Angus Manager
2022 Tracy Pine Developer
2023 Mark Yun Manager
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
2020 3202 02-01-2020
07:41:56
04:37:28 Power Tools 09-10-2019 1,000
2019 7202 02-01-2020
11:53:21
01:34:03 Vehicle 12-11-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
SAMPLE DATA CONTINUES ON NEXT PAGE
EmployeeHistory
employeeID departmentCode dateStarted senorityLevel 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 quantity organiser startDate endDate location allowance restrictions description
UQ Partnership Food 1 2023 02-02-
2020
03-02-
2020
Brisbane 200 No alcohol
over $70
Meeting for
IT project
UQ Partnership Hotel 1 2023 02-02-
2020
03-02-
2020
Brisbane 500 Food
service not
included
Hotel for
regional
investor
UQ Partnership Transport 1 2023 02-02-
2020
03-02-
2020
Brisbane 100 Cannot user
Uber
or DiDi
Taxi from
airport to
hotel
Ride-share
Marketing
Conference
Hotel 7 2020 21-03-
2020
28-03-
2020
Sydney 500 Food
service not
included
Weeklong
marketing
conference
Ride-share
Marketing
Conference
Transport 14 2020 21-03-
2020
28-03-
2020
Sydney 100 Cannot user
Uber
or DiDi
Taxi to and
from
conference
Investor
Meeting
Food 3 2021 27-03-
2020
28-03-
2020
Cains 200 No alcohol
over $70
Meeting for
Investor
SAMPLE DATA CONTINUES ON NEXT PAGE
TravelInsuranceHistory
tripName insuranceLevel approved description maxCoverage advisedPrecautions
UQ
Partnership
4 true Local (or inner state)
travel without
circumstances deemed
unsafe
1,000,000 Use safe transport to attend
Ride-share
Marketing
Conference
1 true Any travel with
circumstances deemed
to be unsafe or in
conflict with
government
recommendation
10,000 Follow all government direction
regarding social distancing
during COVID-19
Investor
Meeting
1 true Any travel with
circumstances deemed
to be unsafe or in
conflict with
government
recommendation
10,000 Follow all government direction
regarding social distancing
during COVID-19, additionally
watch for dangerous wildlife