Example of Relational Algebra

Example of Relational Algebra 

• Definition: Relational algebra is a procedural query language which is used to access database tables to read data in different ways.

Example 1.13.1 Consider following databases reserves(sid, bid, day) sailors (sid, sname, rating, age) boats (bid,bname,color)

(i) Find the names of sailors who have reserved boat number 103

(ii) Find the names of sailors who have reserved a red boat

(iii) Find the id of sailors with age over 20 who have not reserved red boat

(iv) Find the names of sailors who have reserved at least one boat

Solution:

(i)(IIsname((σbid-103 Reserves)   Sailors)

(ii) (IIsname((σcolor='red' Boats)   Reserves   Sailors)

(iii) (Πsid((σage>20 Sailors) – Πsid((σcolor='red'Boats)   Reserves)

(iv) (IIsname (Sailors  Reserves)

Example 1.13.2 Consider the following expressions, which use the result of a redational algebra operation as the input to another operation. For each expression explain in words what the expression does:

a) σyear ≥2009(takes)  Student  b) σyear ≥2009(takes)  Student c) ΠID,name, course-id(Student   takes)

Solution:

a. Select each student who takes at least one course in 2009, display the student information along with the information about what the courses the student took.

b. Select each student who takes at least one course in 2009, display the student information along with the information about what the courses the student took but the selection must be before join operation.

c. Display the ID, Name and Course_id of all the students who took any course in the university.

Example 1.13.3 Consider following 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)

i) Find the names of all branches located in "Chennai".

ii) Find the names of all borrowers who have a loan in branch "ABC".

Solution:

i) Π branch_name(σbranch_city ='Chennai')(branch))

ii) Π customer_name(σbranch_name='ABC') (borrower    loan))

Example 1.13.4 author (author_id, first_name, last_name)

author_pub(author_id, pub_id, author_position)

book(book_id, book_title, month, year, editor)

pub(pub_id, title, book_id)

(i) Give the relational algebra expression that returns names of all the authors that are book editors

(ii) Give the relational algebra expression that returns names of all the authors that are not book editors

(iii) Write a relational algebra expression that returns the names of all authors who have at least one publication in the database.

Solution:

i) (Пfirst_name,last_name(author author_id = editor    book)

ii) (Пfirst_name,last_name((Пauthor_id (author) - Пeditor (book)) × author)

iii) (first_name,last_name (author × author_pub)

Example 1.13.5 Consider the following schema:

Supplier(sid, sname, address)

Parts(pid, pname, color)

Cataloge(sid,pid,cost)

Write the relational algebraic queries for the following:

i) Find the sids of supplier who supply some red or some green parts

ii) Find the sids of supplier who supply every red or some green parts

iii) Find the pids of parts supplied by at least two different suppliers

Solution:

i) ρ(R1, Пsid (Πpidσcolor='red'Parts)  Cataloge))

ρ(R2, II sid (Π pid σcolor=’green’Parts)  Cataloge))

R1 U R2

ii) ρ(R1, IIsid, pid Cataloge) / (IIpidσcolor='red'Parts))

ρ(R2, Пsid ((Πpidσcolor-'red' Parts)   Cataloge))

R1 U R2

iii) ρ(R1, Cataloge)

ρ(R2, Cataloge)

(Π R1.pidσR1.pid=R2.pid R1.sid!=R2.sid (R1× R2)

Example 1.13.6 Consider the relational database

employee (person-name, street, city)

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

company (company-name, city)

manages (person-name, manager-name)

where primary keys are underlined.

(a) Find the names of all employees who work for First Bank Corporation

(b) Find the names, street address, and cities of residence of all employees who work for First Bank Corporation and earn more than 200,000 per annum.

(c) Find the names of all employees in this database who live in the same city as the company for which they work.AU: Dec.-06, Marks 8

Solution:

a) II person-name (σcompany-name="First Bank Corporation" (Works))

b) II person-name, street, city(σcompany-name = "First Bank Corporation" ^ salary> 200000 (works  employee))

c) Π person-name(works   employee   company))

Example 1.13.7 Solve the queries for the following database using relational algebra branch (branch-name, branch-city, assets)

customer (customer-name, customer-street, customer-only)

account (account-number, branch-name, balance)

loan (loan-number, branch-name, amount)

depositor (customer-name, account-number)

borrower (customer-name, loan-number)

(1) Find all loans over $1200

(2) Find the loan number for each loan of an amount greater than $1200

(3) Find the names of all customers who have a loan, an account, or both, from the bank

(4) Find the names of all customers who have a loan and an account at bank.

(5) Find the names of all customers who have a loan at the Perryridge branch.

(6) 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.

(7) Find the names of all customers who have a loan and an account at the Perryridge branch.

Solution:

1) σamount>1200 (loan))

2) II loan-number(σamount>1200 (loan))

3) II customer-name(borrower)U II customer-name(depositor)

4) II customer-name (borrower)∩ II customer-name(depositor)

5) Πcustomer-name(σbranch-name="Perryridge"(σborrower.loan-number-loan.loan-number(borrower   loan)))

6) IIcustomer-name(σbranch-name="Perryridge"(σborrower.loan-number-loan.loan-number(borrower   loan))) - II customer-name(depositor)

7) IIcustomer-name(σbranch-name="Perryridge"(σborrower.loan-number-loan.loan-number(borrower    loan)) U IIcustomer-name(depositor))

Example 1.13.8 Consider the relational database as given below. Give an expression in relational algebra to express each of the following queries

Employee (person-name, street, city)

Works (person-name, company-name, salary)

Company (company-name, city)

Manages (person-name, manager-name)

(a) Find the names of all employees in this database who live in the same city as the company for which they work.

(b) Find the names, street address, and cities of residence of all employees who work for HCL Corporation and earn more than $10,000 per annum.

Solution:

1) II person-name(Works   Employee   Company))

2) II person-name, street, city (σcompany-name ="HCL"∩salary > 10000 (Works   Employee))

Example 1.13.9 Consider following Schema and represent given statements in relation algebra form

*Branch(branch_name, branch_city)

*Account(branch_name, acc_no, balance)

*Depositor(customer_name, acc_no)

(i) Find out list of customer who have account at 'abc' branch

(ii) Find out all customer who have account in 'Ahemedabad' city and balance is greater than 10,000

(iii) Find out list of all branch name with their maximum balance

Solution:

i) II customer_name(σbranch_name='abc' (Account   Depositor))

ii) IIcustomer_name(σbranch_city='ahemedabad,balanace>10000(Branch Account   Depositor))

iii) branch_name σmax(balance) (Account)

Example 1.13.10 Consider the following relational database, where the primary keys are underlined. Give an expression in the relational algebra to express each of the following queries:

employee(ssn,name,dno,salary,hobby,gender) department(dno,dname,budget,location,mgrssn)

works_on(ssn,pno)

project(pno,pname,budget,location,goal)

1) List all pairs of employee names and the project numbers they work on

2) List out department number, department name and department budget

3) List all projects that Raj Yadav works on by project name

4) List the names of employee who supervise themselves

Solution:

1) II name,pno(employee  works_on)

2) II dno,dname,budget(department)

3) RajYadav ← σname='Raj Yadav (employee)

Result ← П  pname(RajYadav   works_on  project))

4) Insufficient data

Example 1.13.11 Consider following relational database, where the primary keys are underlined. Give an expression in the relational algebra to express each of the following queries:

course(course-id, title, dept_name, credits)

 instructor(id,name,dept_name,salary)

section(course-id,sec-id,semester,year,building,room-no,time_slot_id)

teaches (id, course-id, sec-id, semester, year)

1. Find the names of all instructors in the physics department.

2. Find all the courses taught in the fall 2009 semester but not in Spring semester

3. Find the names of all instructors in Com.Sci.department together with course titles of all the courses that the instructors teach.

4. Find the average salary in each department.

Solution:

(1) IIname(σdept_name='physics'(instructor))

(2) IIcourse-id(σsemester-'Fall' year-2009 (section)) - II course-id (σsemester-'Sprint'(section))

(3) II name,title(σdept_name='Comp.Sci.department(course  instructor  teaches))

(4) dept_name  Javg salary (instructor)

Example 1.13.12 Consider the following Schema:

Suppliers (sid:integer,sname:string,address:string)

Parts(pid:integer, pname:string, color:string)

Catalog(sid:integer, pid: integer, cost:real) teaches))

The key fields are underlined and the domain of each field is listed after the field name. Therefore sid is the key for Suppliers, pid is the key for Parts and sid and pid together form the key for Catalog. The Catalog relation lists the prices charged for parts by suppliers. Write the following queries in relational algebra:

Find the sids of suppliers who supply some red or green part.

Find the sids of suppliers who supply some red part or are at 221 Packer Street.

Find the pids of parts supplied by at least two different suppliers.

Solution:

1) ρ (R1, Пsid ((Пpidσcolor='red' Parts)  Catalogue))

     ρ (R2, Пsid((Пpidσcolor='red' Parts)   Catalogue))

     R1 U R2

2) ρ (R1, Пsid ((Пpidσcolor='red' Parts)   Catalogue))

     ρ (R2, Пsid σaddress = '221 PackerStreet'Suppliers)

3) ρ (R1,Cataloge)

     ρ(R2,Cataloge)

(IIR1.pid σRI.pid = R2.pid R1.sid != R2.sid(R1 × R2)