1. Consider the relational database given.

Give an expression in the relational algebra to express each of the

following queries:
employee (person name, street, city)
works (person name, company name, salary)
company (company name, city)
a. Find the names of all employees who live in city “Miami”.
b. Find the names of all employees whose salary is greater than $100,000.
c. Find the names of all employees who live in “Miami” and whose salary is greater than $100,000.

a. πperson_name(σcity=′Miami′(employee))
b. πperson_name(σsalary>100000(works))
c. πperson_name(σcity=′Miami′∧salary>100000(employee⋈works))
2. Consider the bank database. Give the expression in the relational algebra for each of the following queries.
branch(branch name, branch city, assets)
customer (customer name, customer street, customer city)
loan (loan number, branch name, amount)
borrower (customer name, loan number)
account (account number, branch name, balance)
depositor (customer name, account number)
a. Find the names of all branches located in “Chicago”.
b. Find the names of all borrowers who have a loan in branch “Downtown”.
c. Find the sum of loan amount sanctioned from the branch “Chicago”

a. πbranch name(σbranch city=′Chicago′(branch))

b. πcustomer name(σbranch name=′Downtown′(borrower⋈loan))
c. gsum (πamount(σbranch name=′Chicago′(loan)))
3. Consider the relational database. Give an expression in the relational algebra to express each of the
following queries:
employee (person name, street, city)
works (person name, company name, salary)
company (company name, city)

a. Find the names of all employees who work for “First Bank Corporation”.
b. Find the names and cities of residence of all employees who work for “First Bank Corporation”.
c. Find the names, street address, and cities of residence of all employees who work for “First Bank
Corporation” and earn more than $10,000.

a. πperson name(σcompany name=′FirstBankCorporation′(works))

b. πperson name, city(σcompany name=′FirstBankCorporation′(works⋈employee))
c. πperson name, street, city(σcompany name=′FirstBankCorporation′∧salary>10000
(works⋈employee))
4. Consider the bank database. Give an expression in the relational algebra for each of the following
queries:
branch(branch name, branch city, assets)
customer (customer name, customer street, customer city)
loan (loan number, branch name, amount)
 borrower (customer name, loan number)
account (account number, branch name, balance)
depositor (customer name, account number)
a. Find all loan numbers with a loan value greater than $10,000.
b. Find the names of all depositors who have an account with a value greater than $6,000.
c. Find the names of all depositors who have an account with a value greater than $6,000 at the “Uptown”
branch.
a. πloan number(σamount>10000(loan))
b. πcustomer name(σbalance>6000(depositor⋈account))
c. πcustomer name(σbalance>6000∧branch name=′Uptown′(depositor⋈account))
5. Write the following queries in Relational algebra, using the university schema.
department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester,year,building,room_number, time_slot_id)
teaches(ID,course_id, sec_id,semester,year)
a. Find the titles of courses in the Comp. Sci. department that have 3 credits.
b. Find the IDs of all students who were taught by an instructor named Einstein; make sure there are no
duplicates in the result.
c. Find the highest salary of any instructor.

a. πtitle(σdept_name=′Comp.Sci.′∧credits=3(course))
b. πID(σname=′Einstein′(instructor⋈teaches))
c. gmax (πsalary(instructor))
6. Write the following queries in Relational algebra, using the university schema.
department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester,year,building,room_number, time_slot_id)
teaches(ID,course_id, sec_id,semester,year)
a. Find all instructors earning the highest salary (there may be more than one with the same salary).
b. Find the enrolment of each section that was offered in Autumn 2009.
c. Find the lowest salary of any instructor.

a. πID, name, dept_name, salary(σsalary=max(salary)(instructor)(instructor))

b. πcourse_id, sec_id, semester, year, enrollment(σsemester=′Autumn′∧year=2009
(section)⋈enrolled))
c. gmin (πsalary(instructor))
7. Write the following queries in Relational algebra using the university schema.
department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester,year,building,room_number, time_slot_id)
teaches(ID,course_id, sec_id,semester,year)

a. Find the maximum enrolment, across all sections, in Autumn 2009.

b. Find the sections that had the maximum enrolment in Autumn 2009.
c. Find the highest salary of any instructor.

a. gmax (πenrollment(σsemester=′Autumn′∧year=2009(section)))
b. πcourse_id, sec_id, semester, year, enrollment(σenrollment=max(enrollment)(σsemester=′Autumn
′∧year=2009(section)))

c. gmax(πsalary(instructor))
8. Consider the bank database. Construct the following Relational algebra queries for this relational
database.
branch(branch name, branch city, assets)
customer (customer name, customer street, customer city)
loan (loan number, branch name, amount)
borrower (customer name, loan number)
account (account number, branch name, balance)
depositor (customer name, account number)

a. Find all customers who have an account at all the branches located in “Brooklyn”.
b. Find out the total sum of all loan amounts in the bank.
c. Find the names of all branches that have assets greater than those of at least one branch located in
“Brooklyn”.

a. πcustomer name(σbranch city=′Brooklyn′(branch))÷πcustomer name(σbranch city=′Brooklyn′

(branch))
b. gsum(πamount(loan))
c. πbranch name(σassets>any(σbranch city=′Brooklyn′(πassets(branch)))(branch))
9. Consider the employee database. Give an expression in Relational Algebra for each of the following queries.
employee (employee name, street, city)
works (employee name, company name, salary)
company (company name, city)
manages (employee name, manager name)
a. Find the names and cities of residence of all employees who work for “First Bank Corporation”.
b. Find the names, street addresses, and cities of residence of all employees who work for “First Bank
Corporation” and earn more than $10,000.
c. Find all employees in the database who do not work for “First Bank Corporation”.

a. πemployee name, city(σcompany name=′FirstBankCorporation′(works⋈employee))

SELECT e.employee_name, e.city FROM employee e INNER JOIN works w ON
e.employee_name = w.employee_name WHERE w.company_name = 'First
Bank Corporation';
b. πemployee name, street, city(σcompany name=′FirstBankCorporation′∧salary>10000
(works⋈employee))
SELECT e.employee_name, e.street, e.city FROM employee e
INNER JOIN works w ON e.employee_name = w.employee_name
WHERE w.company_name = 'First Bank Corporation' AND w.salary > 10000;
c. πemployee name(employee)−πemployee name(σcompany name=′FirstBankCorporation′(works))
SELECT employee_name
 FROM employee
WHERE employee_name NOT IN (
SELECT w.employee_name
FROM works w
WHERE w.company_name = 'First Bank Corporation'

10. Consider the employee database. Give the Relational Algebra expression for each of the following queries.
employee (employee name, street, city)
works (employee name, company name, salary)
company (company name, city)
manages (employee name, manager name)

a. Find all employees in the database who earn more than each employee of “Small Bank Corporation”.
b. Assume that the companies may be located in several cities. Find all companies located in every city in
which “Small Bank Corporation” is located.
c. Find the company that has the most employees.

a. πemployee name(works)−πemployee name(σcompany name=′SmallBankCorporation′(works))

b. πcompany name(σcity∈(πcity(σcompany name=′SmallBankCorporation′(company)))(company))
c. πcompany name(gmax(gcount(works)))
11. Consider the employee database. Give Relational Algebra expression for each of the following queries.
employee (employee name, street, city)
works (employee name, company name, salary)
company (company name, city)
manages (employee name, manager name)

a. Find the company that has the most employees.

b. Find those companies whose employees earn a higher salary, on average, than the average salary at “First
Bank Corporation”.
c. Find the names and cities of residence of all employees who work for “First Bank Corporation”.

a. πcompany name(gmax(gcount(works)))
b. πcompany name(σavg(salary)>avg(σcompany name=′FirstBankCorporation′(works))(works))
c. πemployee name, city(σcompany name=′FirstBankCorporation′(works⋈employee))
12. Write the following queries in Relational algebra using the university schema.
student (student-id, name, program)
department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester,year,building,room_number, time_slot_id)
teaches(ID,course_id, sec_id,semester,year)
a. Find the names of all students who have taken at least one Comp. Sci. course; make sure there are no duplicate
names in the result.
b. Find the IDs and names of all students who have not taken any course offering before Spring 2009.
c. For each department, find the maximum salary of instructors in that department. You may assume that every
department has at least one instructor.

a. πname(σdept_name=′Comp.Sci.′(course⋈section⋈teaches⋈student))
b. πstudent-id, name(student−πstudent-id, name(σyear<2009 ∧ semester<′Spring′
(section⋈teaches⋈student)))
c. πdept_name, max_salary( gmax(salary)(instructor))
 13. Write the following queries in Relational algebra, using the university schema.
student (student-id, name, program)
department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester,year,building,room_number, time_slot_id)
teaches(ID,course_id, sec_id,semester,year)

a. For each department, find the maximum salary of instructors in that department. You may assume that
every department has at least one instructor.
b. Find the lowest, across all departments, of the per-department maximum salary computed by the preceding
query.

a. πdept_name, max_salary( gmax (salary) (instructor))

b. πdept_name, max_salary gmin(πmax_salary( gmax(salary)(instructor)))
14. Consider the insurance database. Construct the following Relational Algebra queries for this relational
database.
person (driver id, name, address)
car (license, model, year)
accident (report number, date, location)
owns (driver id, license)
participated (report number, license, driver id, damage amount)

a. Find the number of accidents in which the cars belonging to “John Smith” were involved.
b. Find the car with the license number “AABB2000” in the accident with report number “AR2197” to
$3000.

a. gcount(πreport number(σname=′JohnSmith′(person⋈owns⋈participated⋈accident)))
b. πlicense(σreport number=′AR2197′ ∧ damage amount≤3000(accident⋈participated))
15. Consider the bank database. Construct the following Relational algebra queries for this relational
database.
branch(branch name, branch city, assets)
customer (customer name, customer street, customer city)
loan (loan number, branch name, amount)
borrower (customer name, loan number)
account (account number, branch name, balance)
depositor (customer name, account number)
a. Find all customers of the bank who have an account but not a loan.
b. Find the names of all customers who live on the same street and in the same city as “Smith”.
c. Find the names of all branches with customers who have an account in the bank and who live in “Harrison”.

a. πcustomer name(depositor)−πcustomer name(borrower)

b. πcustomer name(σcustomer street=Smith street∧customer city=Smith city(customer×customer))
c. πbranch name(σcustomer city=Harrison(customer⋈depositor⋈account⋈branch))
16. Consider the employee database. Give Relational Algebra expression for each of the following queries.
employee (employee name, street, city)
works (employee name, company name, salary)
company (company name, city)
manages (employee name, manager name)
a. Find all employees in the database who live in the same cities and on the same streets as do their managers.
b. Find all employees who earn more than the average salary of all employees of their company.
c. Find the company that has the smallest payroll.

a. Πperson-name ((employee⋈ manages) (manager-name=employee2.person-name ∧ employee.street =employee2.street ∧ employee.city

1

=employee2.city)(ρemployee2 (employee)))

b. πemployee name(σsalary>avg_salary(employee⋈works))
c. πcompany name( gmin (payroll)(company⋈works))
17. Consider the bank database. Construct the following Relational algebra queries for this relational
database.

branch (branch_name, branch_city, assets)

customer (customer_name, customer_street, customer_city)
account (account_number, branch_name, balance)
loan (loan_number, branch_name, amount)
depositor (customer_name, account_number)
borrower (customer_name, loan_number)

a. Find the names of all customers who have a loan, an account, or both, from the bank
b. Find the names of all customers who have a loan at the Perryridge branch.
c. Find the names of all customers who have a loan at the Perryridge branch but do not have an
account at any branch of the bank.

a. πcustomer_name(depositor∪borrower)
b. πcustomer_name(σbranch_name=′Perryridge′(borrower))
c. πcustomer_name(σbranch_name=′Perryridge′(borrower)−depositor)
18. Consider the bank database. Construct the following Relational algebra queries for this relational
database.

branch (branch_name, branch_city, assets)

customer (customer_name, customer_street, customer_city)
account (account_number, branch_name, balance)
loan (loan_number, branch_name, amount)
depositor (customer_name, account_number)
borrower (customer_name, loan_number)

a. Find the largest account balance

b. Find the name of all customers who have a loan at the bank and the loan amount
c. Find the names of all customers who have a loan and an account at bank.

a. gmax(πbalance(account))
b. πcustomer_name, amount(loan⋈borrower)
c. πcustomer_name(σcustomer_name∈(depositor∩borrower)(customer))
 19. Consider the bank database. Construct the following Relational algebra queries for this relational
database.

branch (branch_name, branch_city, assets)

customer (customer_name, customer_street, customer_city)
account (account_number, branch_name, balance)
loan (loan_number, branch_name, amount)
depositor (customer_name, account_number)
borrower (customer_name, loan_number)
a. Find all customers who have an account from at least the “Downtown” and the Uptown” branches.
b. Find all customers who have an account at all branches located in Brooklyn city.
c. Find the smallest account balance
a. πcustomer_name(σbranch_city=′Downtown′∨branch_city=′Uptown′
(branch⋈account⋈depositor))
b. πcustomer_name(σbranch_city=′Brooklyn′(branch⋈account⋈depositor))÷πcustomer_name
(σbranch_city=′Brooklyn′(branch⋈depositor))
c. gmin(πbalance(account))

20. Consider the bank database. Construct the following Relational algebra queries for this relational
database.
member(memb no, name, age)
book(isbn, title, authors, publisher)
borrowed(memb no, isbn, date)

a. Find the names of members who have borrowed any book published by “McGraw-Hill”.
b. Find the names of members who have borrowed all books published by “McGraw-Hill”.
c. For each publisher, find the names of members who have borrowed more than five books of that publisher.
a. πname(σpublisher=′McGraw−Hill′(book⋈borrowed⋈member))
b. πname(member−πname(σpublisher=′McGraw−Hill′(book⋈borrowed⋈member)))
c. πpublisher, name(σcount>5(group by publisher, name(book⋈borrowed⋈
member)))
21. Consider the Library database. Construct the following Relational algebra queries for this relational
database.
member(memb no, name, age)
book(isbn, title, authors, publisher)
borrowed(memb no, isbn, date)

a. For each publisher, find the name and membership number of members who have borrowed more
than five books of that publisher.
b. Find the average number of books borrowed per member. Take into account that if an member does
not borrow any books, then that member does not appear in the borrowed relation at all.

a. πmemb no, name(σcount>5(group by memb no, name(book⋈borrowed⋈

member)))
 b. gavg(count(book⋈borrowed⋈member))

22. Consider the bank database. Construct the following Relational calculus for this relational database.

branch (branch_name, branch_city, assets)

customer (customer_name, customer_street, customer_city)
account (account_number, branch_name, balance)
loan (loan_number, branch_name, amount)
depositor (customer_name, account_number)
borrower (customer_name, loan_number)

a. Find the loan number for each loan of an amount greater than 6000
b. Find the names of all customers who have a loan and an account at the bank
c. Find the names of all customers who have a loan at the Mexico branch, but no account at any branch
of the bank

a. {loan_number∣∃loan(loan_amount(loan)>6000∧loan_number=
loan_number(loan))}
b. {customer_name∣∃loan(borrower(customer_name,loan_number)∧
∃account(depositor(customer_name,account_number)))}
c. {customer_name∣∃loan(borrower(customer_name,loan_number)∧
branch_city(Mexico,branch)∧¬∃account(depositor(customer_name,
account_number)))}

23. Consider the employee database. Give Relational Algebra expression for each of the following queries.
employee (employee name, street, city)
works (employee name, company name, salary)
company (company name, city)
manages (employee name, manager name)

a. Find the names of all employees who manage other employees:

b. Find the names of all employees who do not manage any other employees:
c. Find the names of employees who work for a company in 'NewYork

a. πemployee name(manages)
b. πemployee name(employee−manages)
c. πemployee name(σcity=′NewYork′(works⋈company))
24. Consider the bank database. Construct the following Tuple Relational calculus for this relational
database.
branch (branch_name, branch_city, assets)
customer (customer_name, customer_street, customer_city)
account (account_number, branch_name, balance)
loan (loan_number, branch_name, amount)
depositor (customer_name, account_number)
borrower (customer_name, loan_number)
 a. Find the names of all customers having a loan from the New York branch, and the cities in which they
live.
b. Find the names of all customers who have an account at all branches located in Brooklyn
c. Find the names of all customers who have a loan and an account at the bank

a. {c.customer_name,c.customer_city∣borrower(c.customer_name,
l.loan_number)∧loan(l.loan_number,b.branch_name,_)∧
branch(b.branch_name,′NewYork
′,_)∧customer(c.customer_name,_,c.customer_city)}
b. {c.customer_name∣∀b(branch(b.branch_name,′Brooklyn′,_)→
∃a(account(c.customer_name,a.account_number,_)))}
c. {c.customer_name∣∃d(depositor(c.customer_name,d.account_number)∧∃b(b
orrower(c.customer_name,b.loan_number)))}
25. Consider the insurance database. Construct the following Relational Algebra queries for this relational
database.
person (driver id, name, address)
car (license, model, year)
accident (report number, date, location)
owns (driver id, license)
participated (report number, license, driver id, damage amount)
a. Find the model of the car met with accidents in which the cars belonging to “Alex”.
b. Find the driver id with the license number and date who makes damage amount greater than $3000.

a. πmodel(σdriver_id="Alex"(owns⋈participated⋈car))
b. πdriver_id, license, date(σdamage_amount>3000(participated))
26. Consider the bank database. Construct the following Domain Relational calculus for this relational
database.

branch (branch_name, branch_city, assets)

customer (customer_name, customer_street, customer_city)
account (account_number, branch_name, balance)
loan (loan_number, branch_name, amount)
depositor (customer_name, account_number)
borrower (customer_name, loan_number)

a. Find the loan_number, branch_name, and amount for loans of over $1200
b. Find the names of all customers who have a loan of over $1200
c. Find the names of all customers who have a loan from the Perryridge branch and the loan amount.

a. {⟨loan_number,branch_name,amount⟩∣loan(loan_number,branch_name,
amount)∧amount>1200}
b. {customer_name∣∃loan_number,branch_name,amount(borrower
(customer_name,loan_number)∧loan(loan_number,branch_name,
amount)∧amount>1200)}
 c. {customer_name∣∃loan_number,amount(borrower(customer_name,
loan_number)∧loan(loan_number,"Perryridge",amount))}

27. Consider the Library database. Construct the following Relational algebra queries for this relational
database.
member(memb no, name, age)
book(isbn, title, authors, publisher)
borrowed(memb no, isbn, date)
a. Find the names of members who borrowed a book with 'Database' in its title
b. Find the titles of books borrowed by members under the age of 18.
c. Find the names of members who borrowed a book published by 'Pearson’.

a. πname(σtitle contains ′Database′(member⋈borrowed⋈book))

b. πtitle(σage<18(member⋈borrowed⋈book))
c. πname(σpublisher=′Pearson′(member⋈borrowed⋈book))
28. Consider the bank database. Construct the following Domain Relational calculus for this relational
database.

branch (branch_name, branch_city, assets)

customer (customer_name, customer_street, customer_city)
account (account_number, branch_name, balance)
loan (loan_number, branch_name, amount)
depositor (customer_name, account_number)
borrower (customer_name, loan_number)

a. Find the names of all customers having a loan, an account, or both at the Perryridge branch
b. Find the names of all customers who have an account at all branches located in Brookly

a. {x.customer_name∣∃y,z(depositor(x.customer_name,y.account_number)∨b
orrower(x.customer_name,z.loan_number))∧(y.branch_name="Perryridge
"∨z.branch_name="Perryridge")}
b. {x.customer_name∣∀y(branch(y.branch_name,"Brooklyn",_)→
∃z(depositor(x.customer_name,z.account_number)∧z.branch_name=
y.branch_name))}
29.
Ans: JFTT

30.

Ans: 1