This assignment will be marked out of 10. It will count for 10% of the nal grade. Below you will nd 3
questions to reach this score. Marks are assigned for the process of nding a solution, not only for the result.
Hence, include all essential ideas and steps that are necessary to derive a solution.
Instructions:
• This assignment should be done by a group of two students, or individually if you prefer. COMP2400
students can only be paired with other COMP2400 students. COMP6240 students can only be paired
with other COMP6240 students. You can choose a dierent group partner (as for Assignment 2) if you
prefer.
• You must submit the following le on Wattle before the due date:
{ \CODE-u1234567-u7654321.pdf” (replace CODE with COMP2400 or COMP6240, and replace
u1234567 and u7654321 with your UID and your group partner’s UID, respectively). Make sure
you only upload the PDF le, not a Word or text le.
• For each group, you must submit a single copy of your assignment on Wattle under either your UID
or your partner’s UID.
• 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 nd yourself in a situation beyond your control that you believe signicantly eects an assessment,
you should follow the ANU’s special consideration 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 signicant discrepancy between the two forms of assessment, it will be automatically treated as
a case of suspected academic misconduct.
Question 1 4 Marks
Consider the following database schema for a university library.
BOOK = fBookID, Titleg
PK: fBookIDg
AUTHOR = fBookID, AuthorID, Nameg
PK: fBookID, AuthorIDg
FK: [BookID] BOOK[BookID]
STUDENT = fStudentID, Name, Address, Phoneg
PK: fStudentIDg
LOAN = fBookID, StudentID, StartDate, DueDateg
PK: fBookID, StudentIDg
FK: [BookID] BOOK[BookID], [StudentID] STUDENT[StudentID]
1
Answer the following question using relational algebra expressions. You are encouraged to use relational algebra
expressions to represent intermediate results if needed.
1.1 Find all students who have borrowed at least one book. List their StudentIDs and names. (0.5 Mark)
1.2 Find all the students who have borrowed any book(s) written by George Martin. List their StudentIDs and
phone numbers. (0.5 Mark)
1.3 Find all the authors whose book(s) has/have never been borrowed by any student. List their AuthorIDs
and names. (0.5 Mark)
1.4 Find all the students who have only borrowed the book \the Fellowship of the Ring”. List their StudentIDs.
(0.5 Mark)
1.5 Find all pairs of authors who have co-authored at least one book (i.e., both of them are authors of the same
book). List all pairs of their AuthorIDs and the title of the book that they co-authored. (1 Mark)
1.6 Find all students who have borrowed at most 1 book. List their StudentIDs and names. (1 Mark)
Question 2 6 Marks
Consider the following database of ight booking system.
CUSTOMER = fCustomerID, CustomerName, Phoneg
PK: fCustomerIDg
EMPLOYEE = fEmployeeID, EmployeeNameg
PK: fEmployeeIDg
FLIGHT = fFlightNo, Date, Origin, Destinationg
PK: fFlightNo, Dateg
BOOKING = fCustomerID, EmployeeID, FlightNo, Date, Priceg
PK: fCustomerID, EmployeeID, FlightNo, Dateg
FK: [CustomerID] CUSTOMER[CustomerID], [EmployeeID] EMPLOYEE[EmployeeID], [FlightNo, Date]
FLIGHT[FlightNo, Date]
CUSTOMER
CustomerID CustomerName Phone
101 Peter 811-649-322
202 John 822-531-989
303 Miranda 833-674-282
FLIGHT
FlightNo Date Origin Destination
A01 30-09-2017 Canberra Sydney
A02 30-09-2017 Sydney Perth
A03 29-09-2017 Canberra Melbourne
A04 29-09-2017 Melbourne Sydney
EMPLOYEE
EmployeeID EmployeeName
11 Sylvain
22 Jens
BOOKING
CustomerID EmployeeID FlightNo Date Price
101 11 A04 29-09-2017 500
101 22 A01 30-09-2017 200
202 22 A03 29-09-2017 400
303 22 A01 30-09-2017 200
2
2.1 Evaluate the relational algebra expressions over the given sample relations, and show each of your answer
as a table that includes the attribute names and tuples. For example, such a table can be shown as follows:
CustomerID EmployeeID FlightNo Date Price
101 11 A04 29-09-2017 500
101 22 A01 30-09-2017 200
202 22 A03 29-09-2017 400
303 22 A01 30-09-2017 200
(a) BOOKING ./ CUSTOMER ./ FLIGHT (0.5 Mark)
(b) BOOKING ./(BOOKING:FlightNo=FLIGHT:F lightNo)_(BOOKING:Date=FLIGHT:Date) FLIGHT (0.5
Mark)
(c) (BOOKING ./ (CustomerID0;EmployeeID0;F lightNo0;Date;P rice0)(BOOKING)) ./ FLIGHT (0.5 Mark)
(d) EmployeeID;Date;BOOKING:CustomerID(BOOKING./BOOKING:CustomerID<CUSTOMER:CustomerIDCUSTOMER)
(0.5 Mark)
2.2 Optimize the following two 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 that correspond to
queries before and after your optimisation.
(a) BOOKING:CustomerID;Origin;P rice(BOOKING:FlightNo=FLIGHT:F lightNo
(BOOKING:CustomerID=CUSTOMER:CustomerID(BOOKING FLIGHT CUSTOMER))) (2 Mark)
(b) R1:CustomerID(R1:CustomerID=R2:CustomerID((R1:Date6=R2:Date)^(R1:Origin=0Canberra0)^(R2:Origin=0Canberra0)
(R1(BOOKING ./ FLIGHT ./ CUSTOMER) R2(BOOKING ./ FLIGHT ./ CUSTOMER))))
(2 Mark)
+++++
3
Leave A Comment