COMP3278B Assignment 3

The University of Hong Kong

Faculty of Engineering

Department of Computer Science

Academic Year 2019 – 2020 Semester 2

COMP3278B: Introduction to Database Management Systems

Assignment 3

Due: 23:59 21st April, 2020 (Tuesday)

Question 1: Writing SQL Statements and Converting to Relational Algebra (70%)

Consider the following tables for a database system of a library:

 Member (mID, mname, age, gender)

 Book (bID, bname, publisher)

 BorrowRecord (mID, bID, quantity)

Write down the SQL statements of each query and Convert them to Relational Algebra.

a) Find the bname of the Book(s) whose publisher is Pearson. [10%]

b) Find the mname of the FEMALE Member(s) above the age of 18. [10%]

c) Find the mname of EACH Member with the bname of the Book(s) that he/she has 

borrowed. [10%]

d) Find the mname of the Member(s) who has/have borrowed the Book(s) with the

quantity of 2. [10%]

e) Find the mname of the Member(s) and the bname of the Book(s) which has/have 

been borrowed with the quantity of 2. [10%]

f) Find the bname of the Book(s) that the Members Peter and Mary have borrowed.

[20%]

Question 2: Query Evaluation and Optimization (30%)

Consider the following tables for a database system of a bookstore:

 Member (mID, mname)

 Book (bID, bname)

 Bookstore (bsID, district)

 PurchaseRecord (mID, bID, bsID, quantity)

a) Write down TWO EQUIVALENT logical answers in relational algebra to find the 

mname of all Member(s) who has/have purchased a Book with the quantity of 4 

from the Bookstore located in Shatin. [20%]

b) With referring to your answers in Question 2(a), please indicate which one is more 

efficient with explanation. [10%]