COMP2400/6240 – Relational Databases
Assignment 2 (Database Theory)
Due date: 23:59, 12 October 2021
Instructions:
• This assignment must be done individually (no group work).
• This assignment will count for 15% of the final grade. Marks are assigned for the process of finding
a solution, not only for the result. Hence, include all essential ideas and steps that are necessary to
derive a solution.
• You must submit a single PDF file named as “u1234567.pdf” (replace u1234567 with your UID). Make
sure you only upload a PDF file, not a Word or text file.
• You should try your best to type the solutions. The scanned images of handwritten texts and equations
can be unreadable for marking. As for the EER diagram, you are highly recommended to export a
JPEG file from TerraER and include it in the PDF file.
• Late submission is not granted under any circumstance. You will be marked on whatever
you have submitted at the time of the deadline. Please take careful note of deadlines and
adhere to them. Of course, if you find yourself in a situation beyond your control that
you believe significantly affects an assessment, you should follow the ANU’s special considera-
tion process (http://www.anu.edu.au/students/program-administration/assessments-exams/special-
assessment-consideration).
• Plagiarism will attract academic penalties in accordance with the ANU guidelines. A student in this
course is expected to be able to explain and defend any submitted assessment item. The course
convener can conduct or initiate an additional interview about any submitted assessment item for any
student. If there is a significant discrepancy between the two forms oexpressionsf assessment, it will
be automatically treated as a case of suspected academic misconduct.
Question 1 4 Marks
ACTScooter is a newly established company that offers scooter rental services in Canberra. The employees
at ACTScooter are classified into the three non-overlapping categories, administrators, distributors and
technicians. An administrator helps a customer to register their unique credit card number and home
address at ACTscooter. The work type of administrators can be either remote, onsite or hybrid. Each
distributor helps to move scooters around and can be reached through a mobile phone number. Each
technician has a qualification level, conducts periodic inspections for the scooters and records the date and
feedback of every inspection for a scooter. Each ACTScooter employee can be uniquely identified by their
employee number. ACTScooter also stores the brand, the manufacture year and an internal distinct ID
for each scooter. Every ride is booked by exactly one customer. The database should allow customers to
retrieve the detailed information about each of their rides, including a unique reference number, the scooter
ID, the fare, the start time and end time, the origin and the destination. Once a ride is completed, the
customer’s credit card will be automatically charged by ACTScooter.
Your task is to design an Enhanced Entity Relationship (EER) diagram for the above database, which
should include entities, relationships, attributes and constraints wherever appropriate (you can make more
assumptions if necessary).
You also need to identify the requirements that cannot be captured in an EER-diagram.
1
Question 2 5 Marks
Consider the relation schema R = {A,B,C,D,E} and the following set Σ of FDs:
• AB → C
• BC → A
• C → DE
• DE → B
2.1 What are the candidate keys of R? Justify your answer (i.e., include the main steps used for finding
the candidate keys). (2 Mark)
2.2 Find a minimal cover of Σ and include the main steps used for finding a minimal cover. (2 Mark)
2.3 Consider another set Σ1 of FDs on R:
• AB → CDE
• C → AB
• DE → B
Are Σ and Σ1 equivalent or not? Justify your answer. (1 Mark)
Question 3 2 Marks
Consider the relation schema Appointment={Patient, GP, Date, Time, Clinic, Room} and the following
set Σ of FDs:
• Patient, Clinic, Date → Time
• Patient → GP
• GP → Clinic
• Clinic, Date, Time, Room → Patient
• Patient, Date, Time → Clinic, Room
Is the above relation schema Appointment in BCNF? If not, identify a BCNF decomposition for Ap-
pointment. You need to include the main steps used for identifying the BCNF decomposition. Check if
this BCNF decomposition is dependency preserving. (2 Mark)
Question 4 4 Marks
The following table contains the relational algebra operators covered in our course. You should only use
these operators to answer the following questions.
2
σϕR Selection by condition ϕ
πA1,…,AnR Projection onto the set of attributes {A1 . . . , An}
ρR′ (A1,…,An)R Renaming the relation name to R
′
and attribute names to A1, . . . , An
ρR′R Renaming the relation name to R
′
ρ(A1,…,An)R Renaming the attribute names to A1, . . . , An
R1 ∪R2 Union of two relations R1 and R2
R1 ∩R2 Intersection of two relations R1 and R2
R1 −R2 Difference of two relations R1 and R2
R1 ×R2 Cartesian product of two relations R1 and R2
R1 ./ϕ R2 Join of two relations R1 and R2 with the join condition ϕ
R1 ./ R2 Natural join of two relations R1 and R2
ϕ1
∧
ϕ2 condition ϕ1 AND condition ϕ2
ϕ1
∨
ϕ2 condition ϕ1 OR condition ϕ2
Consider the following relation schemas:
Student={SID, Name, Degree, College, Address, Phone} with the primary key {SID},
Course={CourseNo, College, Semester} with the primary key {CourseNo, Semester},
Tutor={TID, Email, CourseNo, Semester} with the primary key {TID, CourseNo, Semester} and
the foreign keys: [CourseNo,Semester]⊆Course[CourseNo,Semester] and [TID]⊆Student[SID],
Enrol={SID, CourseNo, Semester, Unit, Status} with the primary key {SID, CourseNo, Semester}
and the foreign keys: [CourseNo,Semester]⊆Course[CourseNo,Semester] and [SID]⊆Student[SID].
4.1 Answer the following questions using relational algebra queries. You should only use the relational
algebra operators in the above table. You are encouraged to use relational algebra expressions to
represent intermediate results if needed. (2 Mark)
[a] List the phone numbers of students who studied COMP2400 in ‘S2 2020’ and became a tutor for
COMP2400 in ‘S2 2021’. (1 Mark)
[b] List the TIDs of tutors who had tutored exactly one course in ‘S2 2021’. (1 Mark)
4.2 Optimise the following relational algebra query (Your marks will depend on how well you optimise
the query in your solution). Additionally, draw the query trees of the query before and after your
optimisation. (2 Mark)
πSID,Address,Phone(σCourseNo=‘COMP2400′((Course ./ Enrol) ./Enrol.SID=Student.SID Student))
(2 Mark)
+++++
3