Database Systems Homework #1

CS143: Database Systems
Homework #1
1. Suppose relation R(A, B, C) has the tuples:
A B C
3 2 1
4 2 3
4 5 6
2 5 3
1 2 6
and relation S(A, B, C) has the tuples:
A B C
2 5 3
2 5 4
4 2 3
3 2 1
Compute (R − S) ∪ (S − R), often called the “symmetric difference” of R and S. List all the
tuples in the result relation.
2. Suppose relation R(A, B) has the tuples:
A B
1 2
3 4
5 6
and relation S(B, C, D) has the tuples:
B C D
2 4 6
8 6 8
7 5 9
Compute R./R.A<S.C∧R.B<S.DS and list all the result tuples.
1
3. Assume the following database for this problem. The relations represent information on bank
branches:
Customer(customer-name, street, city)
Branch(branch-name, city)
Account(customer-name, branch-name, account-number)
The Customer relation has customer names and their addresses. The Branch Relation
has branch names and the city that a branch is located in. The Account relation represents
at which branch a customer has his/her accounts. We assume that customer names and
branch names are unique. We also assume that a customer may have multiple accounts in
one branch and the customer may have accounts in multiple branches.
Write an relational-algebra expression for each of the following queries. We can use only
the operators learned in the class.
(Hint: When a query is difficult to write, think of its complement.)
(a) Find the names of all customers who have an account in the ‘Region12’ branch.
(b) Find the names of all customers who have an account in a branch NOT located in the
same city that they live in.
(c) Find the branches that do not have any accounts.
(d) Find the customer names who do not have any account in the ‘Region12’ branch.
(e) Find the customer names who have accounts in all the branches located in ‘Los Angeles’.
You are not allowed to use the division operator directly for this question.
(f) Find the customer names who have only one account.
4. The relation Student(sid, GPA) captures the student-GPA information, where sid is the id
of a student and GPA is the student’s GPA. Write a relational algebra that finds the ids of
the students with the lowest GPA.
(Hint: When a query is difficult to write, think of its complement.)
2
5. Write the queries of Exercises 3. and 4. in SQL.