Description

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:

• 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.D}S and list all the result tuples.

1. 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.)

1. 1. 3. 5. 3. 2. 1. Find the names of all customers who have an account in the ‘Region12’ branch.

1. 1. 3. 5. 3. 2. 2. Find the names of all customers who have an account in a branch NOT located in the same city that they live in.

1. 1. 3. 5. 3. 2. Find the branches that do not have any accounts.

1. 1. 3. 5. 3. Find the customer names who do not have any account in the ‘Region12’ branch.

1. 1. 3. 5. 3. 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.

1. 1. 3. 5. 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.)

4. 5. Write the queries of Exercises 3. and 4. in SQL.

2