xuebaunion@vip.163.com
3551 Trousdale Rkwy, University Park, Los Angeles, CA
留学生论文指导和课程辅导
无忧GPA:https://www.essaygpa.com
工作时间:全年无休-早上8点到凌晨3点
微信客服:xiaoxionga100
微信客服:ITCS521
COMP2400/6240 - Relational Databases
Assignment 2 (Database Theory)
Due date: 23:59, 12 October 2020
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 assess-
ment 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 be-
tween the two forms oexpressionsf assessment, it will be automatically treated as a case
of suspected academic misconduct.
Question 1 4 Marks
A sports center aims to setup a database to maintain the following information.
The database stores the name, the address and a unique ID of each employee. The employees are classified
into three categories: trainers, receptionists and administrators. Each trainer has one or more specialties
(e.g., swimming, tennis and squash etc). Each receptionist can be reached through a landline phone
number. Each receptionist must be supervised by one administrator and a administrator may supervise
multiple receptionists. Each member of this sports center is identified by their membership id and the
database also stores their names and emails. Each week a trainer may offer regular training classes that
members can participate in. Each training class should focus on one of the specialities of the corresponding
trainer and must have a class size no more than 10. Each training class from a trainer is referred to by the
day of the week (i.e., Monday, Tuesday, . . . ) because each trainer offers at most one training class per day.
After a member participated in a training class, the member may submit feedback to a receptionist and the
feedback contains a rating from 1 (very unsatisfied) to 5 (very satisfied), a date and detailed description.
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 3 Marks
Consider the relation schema R={A, B, C, D, E} and the following set Σ of FDs:
• A → B
• AB → C
• BC → AE
• DE → AB
2.1 What are the candidate keys of R? Justify your answer (i.e., include the main steps used for finding
the candidate keys). (1 Mark)
2.2 Find a minimal cover of Σ. Justify your answer (i.e., include the main steps used for finding a minimal
cover). (2 Mark)
Question 3 2 Marks
Consider the relation schema Appointment={Customer, Branch, Date, Time, Staff, Room} and the fol-
lowing set Σ of FDs:
• Customer, Branch, Date, Time → Staff, Room
• Branch, Date, Time, Room → Customer
• Branch, Date, Room → Staff
• Staff, Date → Branch, Room
• Staff → Branch
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 6 Marks
The following table contains the relational algebra operators covered in our course:
σϕR Selection by condition ϕ
piA1,...,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
2
Consider the following relation schemas:
Student={SID, Name, College, Address, Phone} with the primary key {SID},
Course={CourseNo, CourseName, Semester} with the primary key {CourseNo, Semester},
Lecturer={LID, Name, College, Email, CourseNo, Semester} with the primary key {LID, CourseNo,
Semester} and the foreign key: [CourseNo,Semester]⊆Course[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. (3 Mark)
[a] List the CourseNo of courses without any tutors in ‘S2 2020’? (1 Mark)
[b] List the names of students who have been enrolled in a same course for at least two times (i.e., in
different semesters). (1 Mark)
[c] List the emails of lecturers who had taught exactly two courses in ‘S1 2020’. (1 Mark)
4.2 Optimize the following relational algebra queries (Your marks will depend on how well you present the
key ideas of query optimization in your answer). In addition to this, draw the query trees correspond
to queries before and after your optimisation. (3 Mark)
[a] piLID,SID(σ(Student.Name=Lecturer.Name)∧(Student.College=‘CECS′)(Lecturer× Student)) (1.5
Mark)
[b] piSID(σ(College=‘CECS′)∨(Status=‘withdrawn′)(Course ./ Enrol ./ Student)) (1.5 Mark)
+++++
3