TAE-2
Q6. Consider the relational database given below, where the primary keys are underlined.
Give an expression in the relational algebra and SQL 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)
CREATE TABLE employee(person_name varchar(20), street varchar(50),city varchar(20));
CREATE TABLE works (person_name varchar(20), company_name varchar(20), salary int);
CREATE TABLE company(company_name varchar(20),city varchar(20));
CREATE TABLE manages(person_name varchar(20), manager_name varchar(20));
INSERT INTO employee VALUES('Sara', 'Nayak-1', 'Pune');
INSERT INTO employee VALUES('Harry', 'Chawk', 'Nagpur');
INSERT INTO employee VALUES('Neha', 'Abcd', 'Mumbai');
INSERT INTO employee VALUES('Shruti', 'SSS-1', 'Banglore');
INSERT INTO employee VALUES('John', 'Xyz-1', 'Banglore');
INSERT INTO employee VALUES('Ali', 'Abc-1', 'Chennai');
INSERT INTO employee VALUES('Mahi', 'NAIK-1', 'Pune');
INSERT INTO employee VALUES('Smith', 'Kathiya', 'Delhi');
INSERT INTO employee VALUES('Nick', 'NAIK-1', 'Pune');
SELECT * FROM employee;
INSERT INTO works VALUES('Sara', 'First Bank Corporation ', 700000);
INSERT INTO works VALUES('Harry', 'Infosys', 900000);
INSERT INTO works VALUES('Neha', 'PQR', 600000);
INSERT INTO works VALUES('Shruti', 'InfoCepts', 500000);
INSERT INTO works VALUES('John', 'TCS', 1200000);
INSERT INTO works VALUES('Ali', 'First Bank Corporation ', 700000);
INSERT INTO works VALUES('Mahi', 'Small Bank Corporation', 100000);
INSERT INTO works VALUES('Smith', 'Small Bank Corporation', 4000000);
INSERT INTO works VALUES('Nick', 'First Bank Corporation ', 6000000);
SELECT * FROM works;
INSERT INTO company VALUES('First Bank Corporation', 'Pune');
INSERT INTO company VALUES('Infosys', 'Nagpur');
INSERT INTO company VALUES('PQR', 'Hyderabad');
INSERT INTO company VALUES('InfoCepts', 'Banglore');
INSERT INTO company VALUES('TCS', 'Mumbai');
INSERT INTO company VALUES('First Bank Corporation ', 'Pune');
INSERT INTO company VALUES('Small Bank Corporation', 'Banglore');
INSERT INTO company VALUES('Small Bank Corporation', 'Chennai');
INSERT INTO company VALUES('First Bank Corporation', 'Pune');
SELECT * FROM company;
INSERT INTO manages VALUES('Sara', 'Mahi');
INSERT INTO manages VALUES('Harry', 'XYZ');
INSERT INTO manages VALUES('Neha', 'PQR');
INSERT INTO manages VALUES('Shruti', 'Mahi');
INSERT INTO manages VALUES('John', 'Shruti');
INSERT INTO manages VALUES('Ali', 'Smith');
INSERT INTO manages VALUES('Mahi', 'Nick');
INSERT INTO manages VALUES('Smith', 'POQ');
INSERT INTO manages VALUES('Nick', 'Neha');
SELECT * FROM manages;
i. Find the names of all employees in this database who live in the same city as the company
for which they work.
Ans: Relational algebra: π person-name (σ employee.city = company.city Ʌ employee.person-name = works.person-name
Ʌworks.company-name=company.company-name (employee⋈works⋈company))
SQL: select distinct E.person_name
from employee E, works W, company C
where E.city = C.city and E.person_name = W.person_name and W.company_
name = C.comapny_name;
ii. Find the names of all employees who live in the same city and on the same street as do their
managers.
Ans: Relational algebra: π e.person-name (σ e1.person-name = manages.person-name Ʌ manager–name = m.person-name
( (employee) × m(employee) ⋈ manages))
e.street = m.street Ʌ e.city = m.city e
SQL: select distinct e1.person_name
from employee E, manages, employee m
where e1.person_name=manages.person_name and manager_name = m.person_
name and e.street = m.street and e.city = m.city;
iii. Find the names of all employees in this database who do not work for First Bank
Corporation.
Ans: Relational algebra: π person-name (σcompany-name ≠ ' First Bank Corporation ' (works))
iv. Find the names of all employees who earn more than every employee of Small Bank
Corporation.
Ans: Relational algebra: π person-name (works) – ( π person-name (σw1.salary ≤ w2.salary ∧ w2.company-name
= 'Small Bank Corporation') (ρw1(works) ⋈ (ρw2(works)))))
v. Assume the companies may be located in several cities. Find all companies located in every
city in which Small Bank Corporation is located
Ans: Relational algebra: company ÷ π city (σcompany-name= 'Small Bank Corporation' (Company))
Q7. Consider the following relational schema
Employee (empno,name,office,age)
Books(isbn,title,authors,publisher)
Loan(empno, isbn,date)
Write the following queries in relational algebra.
i. Find the names of employees who have borrowed a book Published by 1 McGraw-Hill?
Ans: Relational algebra: π name (σpublisher = 'McGraw-Hill' (Books ⋈ (Employee ⋈ Loan)))
ii. Find the names of employees who have borrowed all books Published by McGraw-Hill?
Ans: Relational algebra: r← π isbn (σpublisher = 'McGraw-Hill' (Books))
s← π name, isbn(Employee ⋈ Loan)
Answer ← r ÷ s
iii. Find the names of employees who have borrowed more than five different books published by
McGraw-Hill?
Ans: Relational algebra: π name (σpublisher = 'McGraw-Hill' Ʌ count-distinct (Loan.isbn) as isbn > 5(Employee
⋈ Books ⋈ Loan))
iv. For each publisher, find the names of employees who have borrowed?
Ans: Relational algebra: π name (σ Loan.isbn=Books.isbn(Employee ⋈ Books ⋈ Loan)))
Q8. Consider the following relational schemes for a library database:
Book (Title, Author, Catalog_no, Publisher, Year, Price)
Collection (Title, Author, Catalog_no)
the following are functional dependencies: 2
a. Title Author --> Catalog_no
b. Catalog_no --> Title Author Publisher Year
c. Publisher Title Year --> Price
d. Assume {Author, Title} is the key for both schemes.
Apply the appropriate normal form for Book and Cancellation?
Ans: It is given that {Author , Title} is the key for both schemas.
The given dependencies are :
{Title, Author} --> {Catalog_no}
Catalog_no --> {Title , Author , Publisher , Year }
{Publisher , Title , Year} --> {Price)
First , let's take schema Collection ( Title , Author , Catalog_no ) :
{Title , Author} --> Catalog_no
(Title, Author} is a candidate key and hence super key also and by definition
of BCNF this is in BCNF.
Now , let's see Book (Title , Author , Catalog_no , Publisher, Year , Price ) :
{Title , Author}* --> {Title , Author , Catalog_no , Publisher , Year , Price}
{Catalog_no}*--> {Title , Author , Publisher, Year , Price , Catalog_no}
So candidate keys are : Catalog_no , {Title , Author}
But in the given dependencies , {Publisher , Title , Year} --> Price , which has Transitive
Dependency. So , Book is in 2NF.
Q9. Employee (Emp_no, Name, Emp_city)
Company (Emp_no, Company_name, Salary)
create table Employee(Emp_no int, Name varchar(20), Emp_city varchar(10));
create table Company(Emp_no int, Company_name varchar(20), Salary int);
INSERT INTO Employee (Emp_no, Name, Emp_city ) VALUES (1001, 'Amstrong', 'NY');
INSERT INTO Employee (Emp_no, Name, Emp_city ) VALUES (1002, 'Mark', 'WT');
INSERT INTO Employee (Emp_no, Name, Emp_city ) VALUES (1003, 'John', 'GT');
INSERT INTO Employee (Emp_no, Name, Emp_city ) VALUES (1004, 'Armin', 'ST');
INSERT INTO Employee (Emp_no, Name, Emp_city ) VALUES (1005, 'Berminn', 'QT');
INSERT INTO Employee (Emp_no, Name, Emp_city) VALUES (1006, 'ARG', 'RT');
INSERT INTO company (Emp_no, Company_name, Salary ) VALUES (1001,'XYZ',22000);
INSERT INTO company (Emp_no,Company_name, Salary ) VALUES (1002,'MPX',12000);
INSERT INTO company (Emp_no,Company_name, Salary ) VALUES (1003,'XYZ', 10000);
INSERT INTO company ( Emp_no,Company_name, Salary ) VALUES ( 1004,'ABC', 9000);
INSERT INTO company (Emp_no,Company_name, Salary ) VALUES (1005,'INT', 14000);
INSERT INTO company (Emp_no,Company_name, Salary ) VALUES (1006,'XYZ', 15000);
SELECT * FROM Employee;
SELECT * FROM Company;
i. Write a SQL query to display Employee name and company name.
Ans: SQL: select Name, Company_name,
from Employee natural join Company;
ii. Write a SQL query to display employee name, employee city ,company name and salary of
all the employees whose salary >10000
Ans: SQL: select Name, Emp_city, Company_name, salary
from Employee natural join Company
where salary>10000;
iii. Write a query to display all the employees working in ‘XYZ’ company.
Ans: SQL: select Name
from Employee natural join Company
where Company_name='XYZ';
Q10. Consider the following Relational Database.
Student (roll_no, name,city,marks,c_no)
Course (c_no,cname,fees)
Construct Queries into Relational algebra.
i. List Student Details enrolled for ‘BBA (C.A)’ Course.
Ans: Relational algebra: π roll_no, name,city,marks,c_no (σ cname = 'BBA (C.A) ' (Student ⋈ Course))
ii. List the Course having fees < 20000
Ans: Relational algebra: π cname (σ fees = 20000 (Course))
iii. Display all students living in either ‘Nasik’ or ‘Pune’ city.
Ans: Relational algebra: π name (σ city = 'Nashik' (Student)) Ս π name (σ city = 'Pune' (Student))
iv. Display Course detail for student ‘Gaurav Sharma’.
Ans: Relational algebra: π c_no, cname, fees (σ name = 'Gaurav Sharma' (Student ⋈ Course))
Q11. Consider relational database:
Supplier (Supno, sname, supaddress)
Item (Itemno, Iname, stock)
Supp-Item (Supno, Itemno, rate)
Write relational algebraic expression for the following
i. List all suppliers from ‘Varanasi’ city who supplies PISTON.
Ans: Relational algebra: π sname (σ supaddress='Varanasi' Ʌ Iname= 'PISTON' (Supplier⋈Supp-
Item⋈Item))
ii. Display all suppliers supply PISTON RINGS
Ans: Relational algebra: π sname (σ Iname= 'PISTON RING' (Supplier ⋈ Supp-Item ⋈ Item))
iii. List all suppliers supplying DOOR Lock from ‘Jaipur’ city
Ans: Relational algebra: π sname (σ Iname='DOOR LOCK' Ʌ supaddress= 'Jaipur' (Supplier ⋈ Supp-
Item⋈Item))
