$30.00
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.DS and list all the result tuples.
1
-
-
-
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.)
-
-
-
-
-
-
-
Find the names of all customers who have an account in the ‘Region12’ branch.
-
-
-
-
-
-
-
-
-
-
-
-
-
Find the names of all customers who have an account in a branch NOT located in the same city that they live in.
-
-
-
-
-
-
-
-
-
-
-
-
Find the branches that do not have any accounts.
-
-
-
-
-
-
-
-
-
-
Find the customer names who do not have any account in the ‘Region12’ branch.
-
-
-
-
-
-
-
-
-
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.
-
-
-
-
-
-
-
-
Find the customer names who have only one account.
-
-
-
-
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.)
-
-
Write the queries of Exercises 3. and 4. in SQL.
-
2