Question 1 (Relational Alge
a) [25 points]
Consider the following relations. The primary keys are underlined. All attributes are of type string if not
indicated otherwise. [Hint: Use multi-step and multi-line queries to ease the formulation of queries. Use
the rename operator ρ to give intermediate query results a name (some textbooks use the equivalent ‘←’
notation). Aggregate functions as you can find them defined in some textbooks are not allowed.
• Student(sID, name, degree: integer, dID)
• Department(dID, name, address)
• Lecture(lID, name, degree: integer, pID, dID)
• Professor(pID, name, dID)
• Register(rID, sID, lID, Semester, score: integer)
(a) [5 points] Find the names of students who have registered every lecture of CISE department.
(b) [5 points] Find the students who got the highest score in the lecture ‘DB’ in 2021 Fall semester. Show
names and scores together.
(c) [5 points] Find the names of students in CISE department who never received a score less than 80.
(d) [5 points] Find the names of students who got more than 90 in the ‘DB’ lecture and less than 70 in the
‘Algorithm’ lecture.
(e) [5 points] Find the names of departments whose lectures were taught by professors of other depart-
ments.
2
Question 2 (Relational Alge
a) [30 points]
Consider the following relations. The primary keys are underlined. All attributes are of type string if not
indicated otherwise. [Hint: Use multi-step and multi-line queries to ease the formulation of queries. Use
the rename operator ρ to give intermediate query results a name (some textbooks use the equivalent ‘←’
notation). Aggregate functions as you can find them defined in some textbooks are not allowed.
• Book(bID, title, author, publisher, year, price)
• Customer(cID, name, SSN, gender, email)
• CreditCard(ccID, cID, number, exprDate)
• BookOrder(oID, bID, ccID, quantity: integer, date)
(a) [5 points] Find the titles of books that were ordered by the customers John or Jane. Assume John and
Jane are unique.
(b) [5 points] Find the dates of the orders when John’s credit card was used.
(c) [5 points] Find the names of publishers whose books are not sold yet.
(d) [5 points] Find the information of customers(name and email) who have credit cards that have been
never used. Show their never-used credit card numbers as well. Assume a customer can have multiple
credit cards. The customers have ordered before(with other credit cards).
(e) [5 points] Find the names of customers who have orders a ‘DB’ book more than once.
(f) [5 points] Find the information of the most expensive ‘DB’ book. List the title, author, publisher, year,
and price.
3
Question 3 (Relational Alge
a) [20 points]
Consider the following relations. The primary keys are underlined. All attributes are of type string if not
indicated otherwise. [Hint: Use multi-step and multi-line queries to ease the formulation of queries. Use
the rename operator ρ to give intermediate query results a name (some textbooks use the equivalent ‘←’
notation). Aggregate functions as you can find them defined in some textbooks are not allowed.
• Customer(cID, name, SSN, age: integer, gender, address, email)
• Gym(gID, name, location, withPool: [‘Y’ or ‘N’], price: integer, pet: [‘Y’ or ‘N’])
• Guest(gsID, name, cID, age: integer, gender, email)
• Member(cID, gID)
• GuestVisit(gsID, date)
(a) [5 points] Find the names and ages of customers who are members of every gym in Gainesville.
(b) [5 points] Find the names of gyms that satisfy the following conditions. 1. It allows a pet. 2. It
costs less than $50. 3. It does not allow a guest to its members (Assumption: At least one guest’s
information is stored if a gym allows guests.)
(c) [5 points] Find the names of members along with gym names whose gym has a pool and costs more
than $100.
(d) [5 points] Find the names of customers whose guests visit gyms more than once.
4
Question 4 (Relational Alge
a) [25 points]
The following questions let you think deeper about the concepts of the Relational Alge
a.
(a) [5 points] Let R be the schema of a relation R, and let A1, . . . ,An ⊆ R. Is the Relational Alge
a
expression πA1(πA XXXXXXXXXXπAn(R XXXXXXXXXXco
ect, in general? If yes, argue why. If not, argue why not. If
your answer is no, are there any restrictions that could make the statement true? If so, what are these
estrictions in mathematical notation?
(b) [5 points] Let R be the schema of a relation R, and let A⊆R. What is the condition in mathematical
notation such that πA(σF(R)) = σF(πA(R)) holds where F is assumed to be a co
ect predicate on R?
(c) [15 points] Let R(A,B,C), S(B,D), and T (A,C) be relations such that R has r tuples, S has s tuples,
and T has t tuples with r > 0, s > 0, and t > 0. The attributes A,B,C, and D are supposed to have the
same numerical data type. Consider the given Relational Alge
a expressions below and determine
the minimum number and the maximum number of possible tuples of each result relation. Explain
your answers.
(1) [5 points] πA, C(R)∩T
(2) [5 points] R 1R.B=S.B S
(3) [5 points] πB(πB(R)∪ πB(S)) (For this question, also consider two other possible numbers of
tuples beside the minimum and maximum number of tuples.)
5