Consider the following tables and: Write the relational algebra statements to an
ID: 3752904 • Letter: C
Question
Consider the following tables and:
Write the relational algebra statements to answer EIGHTof the given queries, to be selected as follows:
3 from Part-1 (Easy)
4 from Part-2 (Moderate)
1 from Part-3 (challenging)
Your relational algebra statements must answer the query in general, and not based on the given instances of the tables. You may use ONLY the relational algebra operators that we discussed in class (Selection, Projection, Union, Intersection, Difference, Product, Join and Renaming).
Please re-state the query completely before you start answering it.
Based on the contents for the tables, what is the FINAL result of executing your relational algebra statements for each of the queries you selected? You need to show only the final relation (table).
If the FINAL result in (2) above contains no records, what records do you need to add to the tables or modify to show at least one single record in the final table? What is the FINAL result after you add these records?
Suppliers (SupplierNo, SName, Status, SCity)
Parts (PartNo, PName, Color, Weight, PCity)
Projects (ProjectNo, JName, JCity)
Shipments (SupplierNo, PartNo, ProjectNo, Quantity)
SupplierNo
SName
Status
SCity
S1
SMITH
20
LONDON
S2
JONES
10
PARIS
S3
BLAKE
30
PARIS
S4
CLARK
20
LONDON
S5
ADAMS
30
ATHENS
PartNo
PName
Color
Weight
PCity
P1
NUT
RED
12
LONDON
P2
BOLT
GREEN
17
PARIS
P3
SCREW
BLUE
17
ROME
P4
SCREW
RED
14
LONDON
P5
CAMERA
BLUE
32
PARIS
P6
WRENCH
RED
19
LONDON
P7
C-O-G
GREEN
12
ROME
ProjectNo
JName
JCity
J1
SORTER
PARIS
J2
PUNCH
ROME
J3
READER
ATHENS
J4
CONSOLE
ATHENS
J5
COLLATOR
LONDON
J6
TERMINAL
OSLO
J7
TAPE
LONDON
J8
DRUM
LONDON
SupplierNo
PartNo
ProjectNo
Quantity
S1
P1
J1
200
S1
P1
J4
700
S1
P3
J1
450
S1
P3
J2
210
S1
P3
J3
700
S2
P3
J4
509
S2
P3
J5
600
S2
P3
J6
400
S2
P3
J7
812
S3
P5
J6
750
S3
P3
J2
215
S3
P4
J1
512
S3
P6
J2
313
S4
P6
J3
314
S4
P2
J6
250
S4
P5
J5
179
S4
P5
J2
513
S5
P7
J4
145
S5
P1
J5
269
S5
P3
J7
874
S5
P4
J4
476
S5
P5
J4
529
S5
P6
J4
318
S5
P2
J4
619
Queries
Part-1
Get full part-details of all parts that are shipped to any project in LONDON.
Get supplier names for suppliers who shipped any part to the COLLATOR project.
Get all pairs of city names such that a supplier in the first city ships to a project in the second city.
Get supplier name, part name, and project name triples such that the indicated supplier, part and project are all co-located (in the same city).
Get part names for parts located in London and shipped to any project in LONDON.
Part-2
Get the supplier names for suppliers who did not ship any parts to any project in LONDON.
Get the supplier names of suppliers who only shipped parts that weigh less than 15 oz.
Get all the colors that are not shipped by the supplier S1.
Get the supplier names for the suppliers who did not ship any part that is green.
Get part numbers for parts that were shipped by a supplier in LONDON and that supplier ships only to projects that are located in LONDON.
Get project names for projects that were sent shipments by both suppliers S1 and S2.
Get project names for projects that were not sent any shipments either supplier S1 or S2.
Part-3
Get project names for projects using at least two parts from supplier S1.
Get part names for parts shipped by at least 2 different suppliers in LONDON.
Get the names of suppliers who shipped the part with maximum weight (compared to all other parts).
Get the suppliers details for suppliers who supplied parts to only one project.
SupplierNo
SName
Status
SCity
S1
SMITH
20
LONDON
S2
JONES
10
PARIS
S3
BLAKE
30
PARIS
S4
CLARK
20
LONDON
S5
ADAMS
30
ATHENS
Explanation / Answer
Queries
Part-1
Suppliers (SupplierNo, SName, Status, SCity)
Parts (PartNo, PName, Color, Weight, PCity)
Projects (ProjectNo, JName, JCity)
Shipments (SupplierNo, PartNo, ProjectNo, Quantity)
--Get full part-details of all parts that are shipped to any project in LONDON.
SELECT * FROM PARTS WHERE PartNo IN (SELECT Shipments.PartNo
FROM Shipments INNER JOIN PROJECTs ON Shipments.ProjectNo = PROJECTs.ProjectNo
WHERE PROJECTs.JCity='LONDON');
--Get supplier names for suppliers who shipped any part to the COLLATOR project.
SELECT SNAME FROM SUPPLIERS
WHERE SupplierNo NOT IN (SELECT SHIPMENTs.SupplierNo
FROM Shipments INNER JOIN PROJECTs
ON Shipments.ProjectNo = PROJECTs.ProjectNo
WHERE PROJECTs.JCity ='COLLATOR');
--Get all pairs of city names such that a supplier in the first city ships to a project in the second city.
SELECT DISTINCT s.SCITY,j.JCITY FROM Suppliers s INNER JOIN Shipments sh ON s.SupplierNo=sh.SupplierNo
INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo;
--Get supplier name, part name, and project name triples
--such that the indicated supplier, part and project are all co-located (in the same city).
SELECT DISTINCT s.SNAME,j.Jname,P.PName FROM Suppliers s
INNER JOIN Projects j ON s.SCITY=j.JCity
INNER JOIN Parts p ON s.Scity=p.pcity;
--Get part names for parts located in London and shipped to any project in LONDON.
SELECT p.PName FROM parts p INNER JOIN Projects pr ON p.pcity=pr.jcity
WHERE p.pcity='LONDON';
---Part-2
--Get the supplier names for suppliers who did not ship any parts to any project in LONDON.
SELECT s.SName FROM supplier s INNER JOIN shipments sh ON s.SupplierNo=sh.SupplierNo
INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo WHERE j.Jcity<>'LONDON';
--Get the supplier names of suppliers who only shipped parts that weigh less than 15 oz.
SELECT s.SName FROM supplier s INNER JOIN shipments sh ON s.SupplierNo=sh.SupplierNo
INNER JOIN parts p on sh.PartNo=p.PartNo
WHERE p.Weight<15;
--Get all the colors that are not shipped by the supplier S1.
SELECT p.Color FROM supplier s INNER JOIN shipments sh ON s.SupplierNo=sh.SupplierNo
INNER JOIN parts p on sh.PartNo=p.PartNo
WHERE s.SupplierNo<>'S1';
--Get the supplier names for the suppliers who did not ship any part that is green.
SELECT s.SName FROM supplier s INNER JOIN shipments sh ON s.SupplierNo=sh.SupplierNo
INNER JOIN parts p on sh.PartNo=p.PartNo
WHERE p.Color<>'Green';
--Get part numbers for parts that were shipped by a supplier in LONDON
--and that supplier ships only to projects that are located in LONDON.
SELECT P.PartNO FROM Suppliers s
INNER JOIN SHIPMENTs sh On s.SupplierNo=sh.SupplierNo
INNER JOIN Parts p ON sh.PartNo=p.PartNo
INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo
WHERE s.Scity = 'LONDON' and j.jcity = 'LONDON' and
s.SCITY not in (SELECT distinct Scity FROM suppliers WHERE scity <>'LONDON');
--Get project names for projects that were sent shipments by both suppliers S1 and S2.
SELECT j.JName FROM Suppliers s
INNER JOIN SHIPMENTs sh On s.SupplierNo=sh.SupplierNo
INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo
WHERE s.SupplierNo in ('S1','S2');
--Get project names for projects that were not sent any shipments either supplier S1 or S2.
SELECT j.JName FROM Suppliers s
INNER JOIN SHIPMENTs sh On s.SupplierNo=sh.SupplierNo
INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo
WHERE s.SupplierNo NOT in ('S1','S2');
--Part-3
Get project names for projects using at least two parts from supplier S1.
SELECT j.JName FROM Suppliers s
INNER JOIN SHIPMENTs sh On s.SupplierNo=sh.SupplierNo
INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo
WHERE s.SupplierNo = 'S1'
GROUP BY j.JName HAVING COUNT(s.SupplierNo)>=2;
--Get part names for parts shipped by at least 2 different suppliers in LONDON.
SELECT P.PName FROM Suppliers s
INNER JOIN SHIPMENTs sh On s.SupplierNo=sh.SupplierNo
INNER JOIN Parts p ON sh.PartNo=p.PartNo
WHERE s.Scity = 'LONDON'
GROUP BY P.PName HAVING COUNT(s.SupplierNo) >=2;
--Get the names of suppliers who shipped the part with maximum weight (compared to all other parts).
SELECT s.SName FROM Suppliers s
INNER JOIN SHIPMENTs sh On s.SupplierNo=sh.SupplierNo
INNER JOIN Parts p ON sh.PartNo=p.PartNo
GROUP BY s.SName HAVING p.Weight= (SELECT MAX(Weight) FROM Parts);
--Get the suppliers details for suppliers who supplied parts to only one project.
SELECT s.SName FROM Suppliers s
INNER JOIN SHIPMENTs sh On s.SupplierNo=sh.SupplierNo
INNER JOIN Projects j ON sh.ProjectNo=j.ProjectNo
WHERE s.SupplierNo = 'S1'
GROUP BY s.SName HAVING COUNT(j.ProjectNo)=1;