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

Related posts: