Consider the following schema: Parts(pid: string, pname: string, description: st

Question

Consider the following schema:

Parts(pid: string, pname: string, description: string, color: string)
Departments(did: string, dname:string, address: string)
Suppliers(sid:string,sname:string, address:string)
Order(did:string, sid: string, pid:string, time:string, quantity:real, price:real)

The key fields are underlined, and the domain of each field is listed after the field name. Thus pid is the key for Part, did is the key for Department, sid is the key for Supplier, and did, sid,and pid together form the key for Order. Write the following queries in Relational algebra.

Find the names of Suppliers.

Find the parts that are red.

Find the names and addresses of departments.

Find the names and addresses of departments that have ordered a red part.

Find the names of parts which are ordered by the

Explanation / Answer

1. SELECT P.pname FROM Parts P, Catalog C, Suppliers S WHERE P.pid = C.pid AND C.sid = S.sid AND S.sname = ‘Acme Widget Suppliers’ AND NOT EXISTS ( SELECT * FROM Catalog C1, Suppliers S1 WHERE P.pid = C1.pid AND C1.sid = S1.sid AND S1.sname ‘Acme Widget Suppliers’ ) 2. SELECT DISTINCT C.sid FROM Catalog C, Parts P WHERE C.pid = P.pid AND P.color = ‘Red’ 3. SELECT M.ename FROM Emp M WHERE M.sid IN (SELECT M2.sid FROM Emp M2, Dept D WHERE M2.sid = D.managerid AND D.budget < 5000000 EXCEPT SELECT M3.sid FROM Emp M3, Dept D2 WHERE M3.sid = D2.managerid AND D2.budget

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---