Instruction 1. You should do the assignments independently. Copying is not allow
ID: 3591435 • Letter: I
Question
Instruction 1. You should do the assignments independently. Copying is not allowed 2. Submit your assignment as a single word/PDF document on culearm. Queries (64 marks) Use both Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC) to express the following 12 queries and Query By Example (QBE) to express queries 1,2,4,5,7,8 based on the given suppliers-and-parts databases. You should include each question with its number, the two or three kinds of queries, and query results just for 11 and 12. SP Suppliers S# SNAME STATUS CITY Si Smith 20 London S P 300 S P2 200 SI P3 400 S P4 200 S PS100 SI P6 100 S2 P1 300 S2 P2 400 S3 P 300 S3 P2 200 S4 P2 200 S4 P3 300 S4 P4 400 P6 Cog Red 19.0 London S4 P5 500 $4 P6 600 Jones 30 Paris S3 Blake 30 Paris S4 Clark 20 London S5 Adarns 30 Athens Parts P# PNAME COLOR WEIGHT CITY PI Nut Red 12.0 London P2 BoltGreen 7.0 Paris P3 Screw Blue 17.0 Oslo P4 Serew Red 140 London PS Cam Blue2.0ParisExplanation / Answer
The all Query listed above are solved below;-
1.Select PNAME from Parts where P# IN (select P# from SP where S#=S5);
2.Select SNAME from Supplier where S# IN (select S# from SP where P# = P2);
3.Select SNAME ,sum(QTY) from Supplier INNER JOIN SP ON (Supplier.S#=SP.S#) group by S#;
4.Select SNAME ,sum(QTY) from Supplier INNER JOIN SP ON (Supplier.S#=SP.S#) group by S# having sum(QTY)<300;
5.Select SNAME from Supplier where S# IN (Select distinct S# from SP where P# IN(Select P# from Parts where Color=Green or Color=Bule));
7.Select SName from Supplier Inner Join SP ON Supplier.S#!=SP.S#;
8.SELECT s.sname FROM suppliers s INNER JOIN catalogue c
ON s.Sno = c.Sno
GROUP BY s.sname
HAVING COUNT(c.Pno) = (SELECT COUNT(Pno) FROM Parts)
9.Select Sname from Supplier where S# IN (Select S# from SP where P# IN [P1,P2,P3,P4,P5] Group by S#);
10.Select SName, P#, Qty from Supplier INNER JOIN Parts ON Part.S#=Supplier.S# Group by S#;
11.Select Sname from Supplier where S# IN [select distinct S# from Part where P# IN[P1,P2]];
12.Select Sname from Supplier where S# IN [select distinct S# from Part where P#=P1 OR P#=P2];