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 parts which are supplied by DELL. Note that DELL is a supplier name.

Find the ids of suppliers which supply both parts M001, and M102. Note that M001 and M102 are ids for parts.

Find the ids of parts that are supplied by at least two different suppliers.

Find the names of suppliers which have supplied all parts.

Explanation / Answer

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

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---