Assignment 2 2018 S2 Solution PDF

Preview

Summary

Assignment 2 Solution 2018...

Description

COMP2400/6240 - Relational Databases Assignment 3: Relational Algebra, Query Processing and Optimization Due date: 23:59, 15 October 2018 Question 1 Consider the following database schema for a university.

4 Marks

BOOK = {BookID, Title, Publisher} PK: {BookID} AUTHOR = {BookID, AuthorID, Name} PK: {BookID, AuthorID} FK: [BookID] ⊆ BOOK[BookID] STUDENT = {StudentID, Name, Address, Phone, DoB} PK: {StudentID} LIBRARY = {BranchID, BookID} PK: {BranchID, BookID} FK: [BookID] ⊆ BOOK[BookID] LOAN = {BookID, StudentID, BranchID, StartDate, DueDate} PK: {BookID, StudentID, BranchID} FK: [BookID] ⊆ BOOK[BookID], [StudentID] ⊆ STUDENT[StudentID], [BranchID,BookID] ⊆ LIBRARY[BranchID,BookID] Answer the following questions using relational algebra expressions. You are encouraged to use relational algebra expressions to represent intermediate results if needed.

1.1 Find all the libraries which have at least one book written by John Tolkien. List their BranchIDs of these libraries. (0.5 Mark) Ans = πBranchID (LIBRARY ⊲⊳ σ Name=′ John

T olkien′ AU T HOR)

1.2 Find all the students who have borrowed the book titled “The Two Towers”. List their StudentIDs and phone numbers. (0.5 Mark) Ans = πStudentID,P hone (ST UDEN T ⊲⊳ LOAN ⊲⊳ σ title=′ T he

T wo T owers′ BOOK )

1.3 Find all the students who have never borrowed any books. List their StudentIDs and names.

(0.5 Mark)

• First get the IDs of all the students who have never borrowed a book R1 = πStudentID ST U DEN T − πStudentID LOAN • Secondly, join those IDs back to student to get their names Ans = πName,StudentID (R1 ⊲⊳ ST U DEN T ) 1.4 Find pairs of students who have borrowed the same book from the same library. List their StudentIDs. (1 Mark) ϕ = ((R1.BookID = R2.BookID) ∧ (R1.StudentID < R2.StudentID) ∧ (R1.BranchID = R2.BranchID)) 1

Ans = πR1.StudentID,R2.StudentID (ρR1 (LOAN ) ⊲⊳ϕ ρR2 (LOAN )) 1.5 Find all students who always borrow books from the same library. List their StudentIDs.

(1 Mark)

• Get all students who borrow from multiple libraries ϕ = ((R1.StudentID = R2.StudentID) ∧ (R1.BranchID 6= R2.BranchID)) R1 = πR1.StudentID (ρR1 (LOAN ) ⊲⊳ϕ ρR2 (LOAN )) • Remove the students who borrow at multiple libraries from all students who use libraries Ans = πStudentID LOAN − R1

Question 2 Question 2

6 Marks

Consider the following database of hotel booking system. CUSTOMER = {CustomerID, Name, Phone} PK: {CustomerID} EMPLOYEE = {EmployeeID, Name, Phone} PK: {EmployeeID} HOTEL = {HotelName, RoomNo, Phone} PK: {HotelName, RoomNo} BOOKING = {CID, EID, HotelNo, RoomNo, Date, Price} PK: {CID, EID, HotelNo, RoomNo, Date} FK: [CID] ⊆ CUSTOMER[CustomerID], EID ⊆ EMPLOYEE[EmployeeID], ⊆ HOTEL[HotelNo, RoomNo] 2.1 Evaluate the relational algebra expressions over the following sample relations, and show your answer to each question as a table that includes the attribute names and tuples.

CUSTOMER CustomerID Name 001 002 003

Weiss Benioff Cogman

HotelName HYATT HYATT ABODE ABODE

Phone

666-666-111 666-666-222 666-666-333

EMPLOYEE EmployeeID Name Phone 001 Strauss 444-444-001 002 Cogman 666-666-333

CID

EID

001 003 002 002

001 001 001 002

HOTEL RoomNo 101 102 101 102

BOOKING HotelName RoomNo HYATT HYATT ABODE HYATT

For example, πEmployeeID,N ame EMPLOYEE will generate the following tables: 2

Phone 555-555-101 555-555-102 777-777-101 777-777-102

101 102 101 102

Date

Price

29-09-2018 29-09-2018 29-09-2018 30-09-2018

200 250 150 200

EmployeeID 001 002

Name Strauss Cogman

(a) σ price150) (BOOKING × EMPLOYEE × CUSTOMER)) (2 Mark) The original query tree is, 3

πCU ST OM ER.N ame,EM P LOY EE.Name,Date

σ (CI D=CustomerID)∧(EID=EmployeeID)∧(P rice>150)

×

×

BOOKING

CUSTOMER

EMPLOYEE

The optimised query tree is, πCU ST OM ER.N ame,EM P LOY EE.Name,Date

⊲⊳CI D=CustomerID

πCI D,N ame,Date

πCustomerID,N ame

⊲⊳EID=EmployeeID

CUSTOMER

πCI D,EI D,Date

πEmployeeID,N ame

σ P rice>150

EMPLOYEE

BOOKING

(b) πBHC 1.CID,BHC 2.CID,BHC 1.Date (σ (BHC 1.CID...