SQL and Relational Algebra Queries: Examples and Solutions
SQL and Relational Algebra Queries
Problem 1. Consider the relational database defined below:
create table branch (
branch_name varchar ( 3 0 ) ,
branch_city varchar ( 3 0 ) ,
assets numeric ( 1 4 , 2 ) ,
primary key ( branch_name ) ) ;
create table customer (
customer_ID char ( 1 0 ) ,
customer_name varchar ( 3 0 ) not null ,
customer_city varchar ( 3 0 ) ,
primary key ( customer_ID ) ) ;
create table loan (
loan_number char ( 1 0 ) ,
branch_name varchar ( 3 0 ) ,
amount numeric ( 1 1 , 2 ) ,
primary key ( loan_number ) ,
foreign key ( branch_name ) references branch ) ;
create table borrower (
customer_ID char ( 1 0 ) ,
loan_number char ( 1 0 ) ,
primary key ( customer_ID , loan_number ) ,
foreign key ( customer_ID ) references customer ,
foreign key ( loan_number ) references loan ) ;
Express in SQL each of the following queries:
(b) Find the ID of each customer who has more than 2 loans (i.e., 3 or more loans).
Answer:
select customer_ID
from borrower
group by customer_ID
having count ( loan_number ) > 2 ;
(c) Find the ID of each customer who does not have any loan (use either in or not in).
Answer:
select customer_ID
from customer
where customer_ID not in (
select customer_ID
from borrower
) ;
(e) Find the name of each branch that has a larger amount of assets than SOME other branch.
Answer:
select branch_name
from branch
where assets > SOME (
select assets
from branch
) ;
(f) Find, for each branch, the number of customers who have at least 1 loan managed by that branch.
Answer:
select branch_name , count ( distinct customer_ID )
from branch natural left outer join loan natural left outer join borrower
group by branch_name
We will also accept answers like the following:
select branch_name , count ( distinct customer_ID )
from loan natural join borrower
group by branch_name
(g) Find the ID of each customer who has the largest number of loans (i.e., each customer who has no fewer loans than any other customer).
Answer:
with temp ( customer_ID , loan_count ) as (
select customer_ID , count ( distinct loan_number )
from borrower
group by customer_ID )
select customer_ID
from temp
where loan_count >= all ( select loan_count from temp ) ;
Problem 2. Consider the relational database defined in Problem 1. Express in relational algebra each of the following queries:
(a) Find the ID of each customer who has more than 2 loans (i.e., 3 or more loans).
Answer:
π customer_ID ( loan_count>2 ( customer_ID G count(loan_number) as loan_count (borrower)))
(b) Find the ID of each customer who has the largest number of loans (i.e., each customer who has no fewer loans than any other customer).
Answer:
temp customer_ID G count(loan_number) as loan_count (borrower)
π customer_ID (temp 1 G max(loan_count) as loan_count (temp))
Problem 1. Consider the following relational database:
person ( person_id , person_name , street , city )
company ( company_name , founding_year )
company_location ( company_name , city )
works ( person_id , company_name , salary )
(a) Identify an appropriate primary key for each relation schema. Assume that (i) each person has a unique identifier, (ii) each company has a unique name, (iii) a company may have offices in multiple cities, (iv) a person may work for multiple companies, and (v) multiple people may work for a company.
Answer: The primary keys are as follows:
- person: {person_id}
- company: {company_name}
- company_location: {company_name, city}
- works: {person_id, company_name}
(b) Using the works relation schema, provide an example of a superkey which is not a candidate key. Justify why it is not a candidate key.
Answer: {person_id, company_name, salary} is a superkey for the works relation, but is not a candidate key because {person_id, company_name} is also a superkey (i.e., {person_id, company_name, salary} is not a minimal superkey).
(c) Given your choice of primary keys, identify all of the foreign keys. For each foreign key, specify both the referencing and referenced relations.
Answer:
foreign key referencing relation referenced relation
{company_name} company_location company
{person_id} works person
{company_name} works company
