Scribd
Upload
750 views

Relational Algebra - Questions With Solu PDF

The document describes relational algebra queries for a database schema containing tables for people's lives, works, located-in, and manages information. It provides the queries and solutions for: 1) Finding employees who work for City Bank. 2) Finding employees' names and cities who work for City Bank. 3) Finding employees' names, streets, and cities who work for City Bank and earn over $10,000. 4) Finding employees who live in the same city as the company they work for. 5) Finding persons who do not work for City Bank. 6) Finding employees who live in the same city and street as their manager.

Uploaded by

ALEX
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
750 views

Relational Algebra - Questions With Solu PDF

The document describes relational algebra queries for a database schema containing tables for people's lives, works, located-in, and manages information. It provides the queries and solutions for: 1) Finding employees who work for City Bank. 2) Finding employees' names and cities who work for City Bank. 3) Finding employees' names, streets, and cities who work for City Bank and earn over $10,000. 4) Finding employees who live in the same city as the company they work for. 5) Finding persons who do not work for City Bank. 6) Finding employees who live in the same city and street as their manager.

Uploaded by

ALEX
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

CS 2441: In-Class Exercises. Relational Algebra Queries.

Solutions

• lives(person-name,street,city)

• works(person-name, company-name,salary)

• located-in(company-name,city)

• manages(person-name,manager-name)

For the above schema (the primary key for each relation is denoted by the underlined
attribute), provide relational algebra expressions for the following queries:
Note: For notational convenience, I am using pname instead of person-name, cname
instead of company-name, and mname instead of manager-name.
1. Find the name of all employees (i.e., persons) who work for the City Bank company (which
is a specific company in the database).
πperson−name (σcname=′ City Bank′ (works))

2. Find the name and city of all employees who work for City Bank. Similar to previous query,
except we have to access the lives table to extract the city of the employee. The join condition
is the same person name in the two tables Lives and Works.
πlives.pname,lives.city (σ((cname=′ City Bank′ )∧(lives.pname=works.pname)) (works × lives))

3. Find the name, street and city of all employees who work for City Bank and earn more than
$10,000. Similar to previous query except an additional condition on salary attribute.
πlives.pname,street,city (σ((cname=′ City Bank′ )∧(lives.pname=works.pname)∧(salary>10000)) (works×lives))

4. Find all employees who live in the same city as the company they work for. For this query
we need to access the lives table to get city of the employee and the located-in table to get city
of the company; plus the works table to associate employee with their company. The selection
condition is then that the two cities are the same.

πlives.pname
(σ((locatedin.cname=works.cname)∧(located−in.city=lives.city)∧(lives.pname=works.pname))
(works × lives × locatedin))

5. Find all persons who do not work for City Bank. Can write this in multiple ways - one
solution is to use set difference:
πpname (works − (σ(cname=′ City Bank′ ) (works)))

6. Find all employees who live in the same city and on the same street as their manager. This
requires accessing lives table twice – once for finding city of employee and a second time for
finding city of manager. Therefore we need the rename operator – so access and rename lives
as mlives to indicate this is for information about where the manager lives. To find manager of
employee we need to look up the manages table.

1
πmanages.pname
(σ((lives.city=mlives.city)∧(lives.street=mlives.street)∧(manages.pname=lives.pname)∧(mname=mlives.pname))
(lives × manages × (ρmlives (lives))))

You might also like