28.

29.

30.

31.

3 D 41964

Consider the following schema :

Suppliers (sid, sname, address) -
Parts (pid, pname, colour)

Catalog (sid, pid, cost)

The Catalog relation lists the prices charged for parts by Suppliers. Write SQL statements
for the following queries.

(a) Find the pnames of parts for which there is some suppliers.

(b) Find the pnames of parts supplied by ABC suppliers.

(c) Find the snames of suppliers who supply every red part.

(d) Find the sids of suppliers who supply a red part and a green part.
Consider the following relations :
Employees (ssn, name, lot)
Departments (did, dname, budjet)

Locations (address, capacity)
Works_In (ssn, did, address, since)

Works_In is a ternary relationship connecting entity sets Employee, Departments and
Locations. Draw an ER diagram to represent the above scenario. Also write an SQL definition
to create a table representing the Works_In relation with all necessary key consiraints.

(a) What are the major problems caused by redundancy?
> (b) Explain with example, Lossless-join decomposition. ೩
Write short notes on :
(a) Anomalies associated with interleaved execution of transactions.
(b) Theory of normalization.
(c) Stored procedures in DBMS.

(d) Aggregate functions in SQL.
(5 x 8 = 40 marks)