INFS1200/7900 Information Systems – Assignment 2 (7.5 Marks)
Due: 24 April 2020 @ 11:59 PM
The purpose of this task is to develop experience in producing an analysing Relational Mapping based on an
Entity Relationship Model diagram from a real-world scenario. This case study will also focus on the ability
to critique and analyse relational mapping and its effect on database implementation.
1. Task
The email correspondence on the pages below contains a continuation of the discussion between Dirt Road
Driving and an INFS1200 student creating a DBMS for them. Using the correspondence on the pages below,
complete the following tasks.
Note: The emails below contain an EER diagram. Please complete Assignment 2 using this EER
diagram below as your starting point, and not your own version that you submitted for Assignment 1.
Please ask your tutors for help if you require clarification on any aspects of the brief.
Part 1- Analysis and Discussion (2 Marks)
The incomplete relational schema mapping produced by the junior system administrator contains several
mistakes which could impact the functionality of this database system. Please find four mistakes in the
schema provided and for each:
1) Identify the mistake
2) Provide the correct mapping/foreign key constraint
3) Explain briefly how this error would negatively impact useability of the database (this must not
exceed 125 words)
Note: Do NOT provide “missing a table” as a mistake. You must only identify mistakes in the schema
from the correspondence below. An example has been provided below, do not use this example as one of
your identified mistakes.
Example
Mistake User[id, dob, fName, mName, lName]
Correction User[id, dob, fName, mName, lName]
Explanation If dob was included as part of the primary key for the User table, then two users with the
same id but a different dob could potentially exist in the database system. This would then
make it impossible to distinguish which user is allocated to a specific trip.
Part 2- EER Diagram to Relational Mapping (2.5 Marks)
Using the EER diagram provided in the correspondence, complete the relational mapping for this database
system. Note: You do not need to show each step in the mapping process, just providing the final
database schema and foreign keys is sufficient.
Part 3 – Relational Model and Database Systems (3 Marks)
In Peter Thompson’s second email (dated 15/4/2020), he introduced the relational schema for the backend
system begin used to manage Dirt Road Driving’s payroll. Using the schema and sample data provided
in that email please answer the following questions.
1) Give an example of
a. a super key
b. a minimal key
c. a foreign key
2) Provide a database operation which:
a. Would result in a domain constraint violation
b. Would result in a referential integrity constraint violation
Note: You must provide original examples, not copied identically from the case study.
3) Peter indicated they have been experiencing some issues with the backend of the payroll system.
However, they are unsure whether the issue exists with the UI system or if the operations themselves
are incorrect. For each database operation please answer the following:
a. Does this operation violate an integrity constraint? (Y / N)
b. If yes, state the type of constraint violated
c. If yes, briefly describe how the constraint was violated (this must not exceed 100 words)
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 2 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: 5/4/2020 02:58 PM
Subject: RE: Student Support for Industry Project
Hi Peter,
I hope you are having a nice weekend. I just wanted to update you regarding some progress which has been
made by our student teams for the Dirt Road Driving Industry project.
After analysing several students’ responses, I have attached the solution we feel would best suit your
current situation.
Please feel free to pass on any questions you or your team may have regarding the EER diagram. We really
hope this solution will work well for your business!
Your sincerely,
Elaine Smith
INFS1200 Student Representative
From:
To:
Date: 11/4/2020 09:12 AM
Subject: RE: Student Support for Industry Project
Hi Elaine,
On behalf of the dirt Road Driving team we’d like to thank you and your students so much for your help
with this project. We really appreciate it!
If it’s alright, would we be able to run another thing by your student team? One of our junior system
administrators has begun to map the EER diagram into a relational schema for future implementation. We’d
love it if you or your team would be able to provide some feedback on the work he has done so far. Please
find below his relational mapping so far:
Tables:
User[id, dob, fName, mName, lName]
Staff[id, dob, fName, mName, lName]
Vehicles[vin, make, model]
EmergencyContact[name, userID, email, phone]
Trip[userID, driverID, vin, bookingTime, startTime, endTime]
UserRatesDriver[userID, driverID, rating]
Driver[id, licence]
4WD[vin, make, model, rideHeight, wheelType]
StaffPhone[id, phone]
Foreign Keys:
EmergencyContact.userID references User.id
Trip.userID references User.id
Trip.driverID references Staff.id
Trip.vin references Vehicles.vin
UserRatesDriver.userID references User.id
UserRatesDriver.driverID references Driver.id
Driver.id references User.id
StaffPhone.id references Staff.id
4WD.vin references Vehicles.vin
Thank you and I look forward to your response.
Kind regards,
Peter Thompson
Director of Innovation | Dirt Road Driving
From:
To:
Date: 15/4/2020 08:09 PM
Subject: RE: Student Support for Industry Project
Hi Elaine,
I hope you are having a nice week! I just wanted to discuss the possibility of receiving some student support
for another database related project our company is undertaking.
In order to streamline our payroll system, last year we hired an external developer to produce an
independent payroll system for our administration staff. Recently however we have been experiencing
issues with the backend of the system and would like some help. Below is a basic schema of the system and
a sample of some of the data.
Employee[id, firstName, lastName, role]
Project[name, description, funding, projectLeader]
TimeLog[employeeID, projectName, date, hoursWorked, approved]
Project.projectLeader references Employee.id
TimeLog.employeeID references Employee.id
TimeLog.projectName references Project.name
Employee
id firstName lastName role
1919 Diluen Smith Developer
2014 Daniel Johnson Administration
2019 Annie Fang Developer
2020 Russell Turner Manager
Project
name description funding projectLeader
Website Setup Get a functional website setup 12000 2019
2020 Marketing Develop a marketing plan for 2020 40000 2020
TimeLog
employeeID projectName date hoursWorked approved
1919 Website Setup 2/1/2020 5 true
2014 Website Setup 1/1/2020 1 true
1919 Website Setup 2/2/2020 8 true
2019 Website Setup 2/2/2020 6 false
2020 2020 Marketing 2/1/2020 5 true
We have recently been having some issues with our UI system which allows us to interact with the database.
The following commands have been returning errors however we are unsure whether the issue exists with
the UI system or if the commands themselves are incorrect.
Update the tuple <”Website Setup”, “Get a functional website setup”, 12000, 2019> to <”Website Setup”,
“Get a functional website setup”, 20000, 1919> in the relation “Project”
Insert the tuple <2014, “Rebecca”, “Zhang”, “Administration”> in the relation “Employee”
Update the tuple <2020, “2020 Marketing”, 2/1/2020, 5, true> to <1919, “Overall Marketing”, 2/1/2020, 5,
true> in the relation “TimeLog”
Insert the tuple <, “Test”, “Test”, “Test”> in the relation “Employee”
Delete the tuple <2014, “Daniel”, “Johnson”, “Administration”> in the relation “Employee”
Would you please be able to help us determine if these operations should be being processed by the
backend system and if not, what the error is?
Thank you again for your help on this project! We really appreciate it!
Kind regards,
Peter Thompson
Director of Innovation | Dirt Road Driving