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.