1. Encircle the right answer for the given description.
(4 marks)
How many copies of the book titled "The Lost Tribe" are owned by the library branch
whose name is "Sharpstown"?
a) SELECT No_Of_Copies
FROM BOOK, BOOK_COPIES, LIBRARY_BRANCH
WHERE Title='The Lost Tribe' AND BranchName='Sharpstown'
b) SELECT No_Of_Copies
FROM BOOK_COPIES, LIBRARY_BRANCH
WHERE Title='The Lost Tribe' AND BranchName='Sharpstown'
c) SELECT No_Of_Copies
FROM BOOK, BOOK_COPIES
WHERE Title='The Lost Tribe' OR BranchName='Sharpstown'
d) SELECT No_Of_Copies
FROM ((BOOK
INNER JOIN BOOK_COPIES ON BOOK.Book_id = BOOK_COPIES. Book_id)
INNER JOIN LIBRARY_BRANCH ON BOOK.Branch_id = LIBRARY_BRANCH. Branch_id);
2. Please provide the right answer for the given description. (4 marks)
How many copies of the book titled "The Lost Tribe" are owned by each library branch?
a) SELECT BranchId, NoOfCopies
FROM BOOK, BOOK_COPIES
WHERE Title='The Lost Tribe'
GROUP BY BranchId
b) SELECT BranchId, NoOfCopies
FROM BOOK, BOOK_COPIES
INNER JOIN BOOK_COPIES ON BOOK. Book_id = BOOK_COPIES. Book_id
WHERE Title='The Lost Tribe'
c) SELECT BranchId, NoOfCopies
FROM BOOK
INNER JOIN BOOK_COPIES ON BOOK. Book_id = BOOK_COPIES. Book_id
WHERE Title='The Lost Tribes'
GROUP BY BranchId
d) Both options a and c
3. Encircle the right answer for the given description. (4 marks)
Retrieve the names of all borrowers who do not have any books checked out.
a) SELECT Name
FROM BORROWER B
WHERE NOT EXIST ( SELECT *
FROM BOOK_LOANS L
WHERE B.CardNo = L.CardNo )
b) SELECT Name
FROM BORROWER B
WHERE CardNo NOT IN (SELECT CardNo FROM BOOK_LOANS );
c) SELECT *
FROM BOOK_LOANS AS BL
FULL OUTER JOIN BORROWER AS BW ON BL.CardNo = BW.CardNo
WHERE DateOut IS NULL
i. Only a
ii. Only b
iii. Only c
iv. Both options a and b
v. Both options a and c
vi. All of the above options
4. Guess the description of the below query (4 marks)
SELECT B.Title, R.Name, R.Address
FROM BOOK B, BORROWER R, BOOK_LOANS BL, LIBRARY_BRANCH LB
WHERE LB.BranchName='Sharpstown' AND LB.BranchId=BL.BranchId AND
BL.DueDate='today' AND BL.CardNo=R.CardNo AND BL.BookId=B.BookId
For each book that is loaned out from the "Sharpstown" branch and whose DueDate is today,
retrieve the book title, the borrower's name, and the borrower's address.
5. Write the query for the given description: Retrieve the names, addresses, and the
number of books checked out for all borrowers who have more than five books
checked out. (4 marks)
SELECT B.CardNo, B.Name, B.Address, COUNT(*)
FROM BORROWER B, BOOK_LOANS L
WHERE B.CardNo = L.CardNo
GROUP BY B.CardNo
HAVING COUNT(*) > 5
6. For each book authored (or co-authored) by "Stephen King", retrieve the title and
the number of copies owned by the library branch whose name is "Central". (Do it
using full outer join) (4 marks)
SELECT Title, No_Of_Copies, BranchName
FROM BOOK AS BK
FULL OUTER JOIN BOOK_COPIES AS BC ON BK.BookID = BC.BookID
FULL OUTER JOIN LIBRARY_BRANCH AS LB ON BC.BranchId = LB.BranchId
FULL OUTER JOIN BOOK_AUTHORS AS BA ON BK.BookID = BA.BookId
WHERE BranchName = 'Central'
AND AuthorName = 'Stephen King'
Question 1: Consider the given database instance and answer the following questions. If there are multiple
answers to a question, encircle all the correct options. [2+2+2+2]
Note: If there is only 1 correct option and you encircle multiple options then you’ll get zero marks.
Student
RegNo Fname Lname DNo
1001 Alice David 1
1002 Tom Antony 1
1003 Lucy Edward 3
Department
DNo Dname Building
1 SE A
2 EE B
3 BBA C
4 DS A
Instructor
TID Fname Lname Salary DNo
001 Matt LBlanc 300000 4
002 Methew Perry 200000 1
003 Jennifer Anniston 450000 1
004 Pheobe Buffay 320000 3
Which of the following update operation(s) will cause the violation of Referential Integrity constraint?
(i) Delete the DEPARTMENT tuple with DNo = 1.
(ii) Insert (004, ‘Mark’, ‘Stevens’, 28000, 5) into INSTRUCTOR.
(iii) Insert (4, ‘EEE’, B4) into DEPARTMENT.
(iv) Insert (null, ‘Anna’, ‘Smith’, 7) into STUDENT.
Which of the following update operation will cause the violation of Entity Integrity constraint?
(i) Insert (4, ‘EEE’, B4) into DEPARTMENT.
(ii) Insert (null, ‘Anna’, ‘Smith’, 7) into STUDENT.
(iii) Insert (004, ‘Mark’, ‘Stevens’, 28000, 5) into INSTRUCTOR.
(iv) Insert (1004, ‘Peter’, ‘Weston’, 2) into STUDENT.
Modify the DNo of STUDENT tuple with RegNo = 1002 to 3. This operation leads to the violation of which
of the following constraint?
(i) Referential Integrity Constraint
(ii) Entity Integrity Constraint
(iii) Domain Constraint
(iv) None of the above
Modify the DNo attribute of the INSTRUCTOR tuple with TID = 003 to 8. This operation will lead to the
violation of which of the following constraint?
(i) Referential Integrity constraint
(ii) Entity Integrity constraint
(iii) Domain Constraint
(iv) None of the above
Question 2: Consider the following ER diagram and answer the given questions. [2+2]
Which of the following is the possible mapping of weak entity type SHIP_MOVEMENT?
1. {Time_stamp, Longitude, Latitude}
2. {Date, Time, Latitude, Longitude}
3. {Sname, Date, Time, Latitude, Longitude}
4. {Sname, Time_stamp, Latitude, Longitude}
Which of the following is the possible mapping of weak entity type PORT_VISIT?
1. {Start_date, End_date}
2. {Sname, Start_date, End_date}
3. {Sname, Start_date, End_date}
4. {Pname,Start_date,End_date}
Question 3: Map the given EER diagram into relational schema using option “single relation
with one type attribute”. [3+1+1]
Vehicle(Vin, Price, Model, type, Engine_size, Tonnage, No_seats}
Sale (Date, Vin, Sid, Ssn)
Customer (Ssn, Name, City, State, Street)
Salesperson( Sid, Name)
1. Consider the following four relational algebra expressions over the database schema R(A, B),
S(A,B,C), T(B,D,E)
𝑄1 = 𝜎!"#,%&' %(𝑅 ⋈ 𝑆) ⋈ 𝑇-
𝑄2 = 𝜋( %𝜎)"# (𝑇 ⋈ 𝑆)-
𝑄3 = 𝜋( (𝑆) ⋈ 𝜋%,( %𝜎)"# (𝑇)-
𝑄4 = 2𝜎!"#,%&' (𝑅 ⋈ 𝑆)3 ⋈ 𝑇
Which ones of the following statements are correct? Please note that multiple statements can
be correct. In the following statements, equivalent means that queries return the same answer
when evaluated on any instance of the database. You much justify your answer for each correct
statement. No marks will be awarded without proper justification. [4 marks]
a. Q1 and Q2 are equivalent
b. Q3 and Q4 are equivalent
c. Q1 and Q2 are not equivalent
d. Q3 and Q4 are not equivalent
e. Q1 and Q4 are equivalent
f. Q2 and Q3 are equivalent
g. None of the above
h. Not enough information to answer the question
2. Which ones of the following relational algebra statements are correct? Please note that
multiple statements can be correct. In the following statements, equivalent means that queries
return the same answer when evaluated on any instance of the database. You much justify your
answer for each correct statement. No marks will be awarded without proper justification. [4
marks]
𝑄1 = 𝜋*+*,- 2𝜎,-./*0 2#33 (𝑀𝑜𝑣𝑖𝑒) 9 𝜎4*56+7"897:8 (𝑀𝑜𝑣𝑖𝑒)3
𝑄2 = 𝜋*+*,- 2𝜎,-./*02#33 (𝑀𝑜𝑣𝑖𝑒)3 9 𝜋*+*,- %𝜎4*56+7"897:8 (𝑀𝑜𝑣𝑖𝑒)-
𝑄3 = 𝜋*+*,- 2𝜎,-./*02#33 ∧4*56+7"897:8 (𝑀𝑜𝑣𝑖𝑒)3
a. Q1, Q2 and Q3 are equivalent
b. All queries are not equivalent
c. Q1 and Q2 are equivalent
d. Q1 and Q2 are not equivalent
e. Q1 and Q3 are equivalent
f. Q1 and Q3 are not equivalent
g. Q2 and Q3 are equivalent
h. Q2 and Q3 are not equivalent
i. None of above
3. Consider the following database schema:
a. LIKES (student, teacher)
b. ATTENDS (student, course)
c. OFFEREDBY (course, teacher)
The relation LIKES indicates the teachers liked by the students. The relation ATTENDS
depicts the courses attended by the students. The relation OFFEREDBY represents the courses
offered by the teachers.
Write a relational algebra query that answers the following question: Which students attends
only those courses that are only offered by the teachers they like? You can only use natural
join (⋈) difference (-) and project ( 𝜋) operator. No other operator is allowed. [5 marks]
𝜋4*56-.* (𝐿𝐼𝐾𝐸𝑆) − 𝜋4*56-.* % 𝜋4*56-.*,*-<=0-> (𝐴𝑇𝑇𝐸𝑁𝐷𝑆 ⊳⊲ 𝑂𝐹𝐹𝐸𝑅𝐸𝐷𝐵𝑌) − 𝐿𝑖𝑘𝑒𝑠 -
4. Given the relations R (A, B, C) and S (D, E), the output of the natural join 𝑅 ⋈ 𝑆 is equivalent
to the output of which (other) relational algebra operator? Justify your answer. [2 marks]
RxS
5. How to express the relational algebra operator intersection ( ⋂ ) on relations r and s using the
difference operator? Justify your answer with Venn diagram. [ 2 marks]
r- (r-s)
6. Write SQL statement that correspond to the following relational algebra expression. [3 marks]
𝑄 = 𝜎?-<>,@ABCD(F)) (𝐴𝑅𝑇𝐼𝐶𝐿𝐸𝑆)
SELECT year,COUNT(ID) FROM Articles GROUP BY year;
