Supplier S PRODUCT P PROJECTS J S# SNAME S1 SMITH S2 JONES S3 BLAKE 54 CLARK...

Transcribed Image Text:

Supplier S PRODUCT P PROJECTS J S# SNAME S1 SMITH S2 JONES S3 BLAKE 54 CLARK S5 ADAMS P# PNAME P1 NUT P2 BOLT P3 SCREW P4 SCREW P5 CAM P6 COG J# JNAME J1 SORTER PUNCH J2 J3 READER J4 CONSOLE J5 COLLATOR J6 TERMINAL J7 TAPE STATUS CITY 20 LONDON 10 PARIS 30 PARIS 20 LONDON 30 ATHENS COLOR RED GREEN BLUE RED BLUE RED CITY PARIS ROME ATHENS ATHENS LONDON OSLO LONDON WEIGHT CITY 12 17 PARIS 17 ROME 14 12 PARIS 19 LONDON LONDON LONDON SPJ S# P# J# QTY 200 S1 P1 1 S1 P1 J4 700 400 200 200 500 600 400 800 100 200 500 300 300 200 100 500 100 200 1000 2000 800 S2 P3 J1 S2 P3 J2 S2 P3 J3 S2 P3 J4 S2 P3 J5 S2 P3 J6 S2 P3 J7 S2 PS J2 S3 P3 J1 S3 P4 J2 S4 P6 J3 S4 P6 J7 S5 P2 J2 S5 P2 J4 S5 P5 J5 S5 P5 J7 S5 P6 J2 S5 P1 J4 S5 P3 J4 S5 P4 J4 S5 P5 J4 S5 P6 J4 400 500 D S A sample database comprising of Supplier (S), Parts(P) and Projects(J). s P SPJ . { St, SNAME, STATUS, CITY) PRIMARY KEY { SE > (P, PNAME, COLOR, WEIGHT, CITY > PRIMARY KEY { P# > (JI, JNAME, CITY} PRIMARY KEY (J# ) ( St. P#, J, QTY') PRIMARY KEY (St. P, JI ) FOREIGN KEY S REFERENCES S FOREIGN KEY ( PI ) REFERENCES P FOREIGN KEYJ REFERENCES J 7.13 Get full details of all projects. 7.14 Get full details of all projects in London. 7.15 Get supplier numbers for suppliers who supply project JI. 7.16 Get all shipments where the quantity is in the range 300 to 750 inclusive. 7.17 Get all part-color/part-city pairs. Note: Here and subsequently, the term "all" means "all cur rently represented in the database," not "all possible." 7.18 Get all supplier-number/part-number/project-number triples such that the indicated supplier part, and project are all colocated (i.e., all in the same city). 7.19 Get all supplier-number/part-number/project-number triples such that the indicated supplier part, and project are not all colocated. 7.20 Get all supplier-number/part-number/project-number triples such that no two of the indicate supplier, part. and project are colocated. 7.21 Get full details for parts supplied by a supplier in London. 7.22 Get part numbers for parts supplied by a supplier in London to a project in London. 7.23 Get all pairs of city names such that a supplier in the first city supplies a project in the secon city. 7.24 Get part numbers for parts supplied to any project by a supplier in the same city as tha project. 7.25 Get project numbers for projects supplied by at least one supplier not in the same city. 7.26 Get all pairs of part numbers such that some supplier supplies both the indicated parts. 7.27 Get the total aumber of projects supplied by supplier S1. 7.28. Get the total quantity of part P1 supplied by supplier S1. 7.29 For each part being supplied to a project, get the part number, the project number, and the cor- responding total quantity. 7:30 Get part numbers of parts supplied to some project in an average quantity of more than 350. 7.31 Get project names for projects supplied by supplier S1. 7.32 Get colors of parts supplied by supplier S1. 7.33 Get part numbers for parts supplied to any project in London. 7.34 Get project numbers for projects using at least one part available from supplier S1. 7.35 Get supplier numbers for suppliers supplying at least one part supplied by at least one sup- plier who supplies at least one red part. 7.36 Get supplier numbers for suppliers with a status lower than that of supplier SI. 7.37 Get project numbers for projects whose city is first in the alphabetic list of such cities. 7.38 Get project numbers for projects supplied with part PI in an average quantity greater than the greatest quantity in which any part is supplied to project J1. 7.39 Get supplier numbers for suppliers supplying some project with part P1 in a quantity greater than the average shipment quantity of part PI for that project. 7.40 Get project numbers for projects hot supplied with any red part by any London supplier. 7.41 Get project numbers for projects supplied entirely by supplier SI. 7.42 Get part numbers for parts supplied to all projects in London. 7.43 Get supplier numbers for suppliers who supply the same part to all projects. 7.44 Get project numbers for projects supplied with at least all parts available from supplier S1. 7.45 Get all cities in which at least one supplier. part. or project is located. 7.46 Get part numbers for parts that are supplied either by a London supplier or to a London project. 7.47 Get supplier-number/part-aumber pairs such that the indicated supplier does not supply the indicated nart. Pasin I. the Write the queries as mentioned below:- 7.13-7.24 in Relational Algebra and TRC. 7.25 -7.47 in SQL. Supplier S PRODUCT P PROJECTS J S# SNAME S1 SMITH S2 JONES S3 BLAKE 54 CLARK S5 ADAMS P# PNAME P1 NUT P2 BOLT P3 SCREW P4 SCREW P5 CAM P6 COG J# JNAME J1 SORTER PUNCH J2 J3 READER J4 CONSOLE J5 COLLATOR J6 TERMINAL J7 TAPE STATUS CITY 20 LONDON 10 PARIS 30 PARIS 20 LONDON 30 ATHENS COLOR RED GREEN BLUE RED BLUE RED CITY PARIS ROME ATHENS ATHENS LONDON OSLO LONDON WEIGHT CITY 12 17 PARIS 17 ROME 14 12 PARIS 19 LONDON LONDON LONDON SPJ S# P# J# QTY 200 S1 P1 1 S1 P1 J4 700 400 200 200 500 600 400 800 100 200 500 300 300 200 100 500 100 200 1000 2000 800 S2 P3 J1 S2 P3 J2 S2 P3 J3 S2 P3 J4 S2 P3 J5 S2 P3 J6 S2 P3 J7 S2 PS J2 S3 P3 J1 S3 P4 J2 S4 P6 J3 S4 P6 J7 S5 P2 J2 S5 P2 J4 S5 P5 J5 S5 P5 J7 S5 P6 J2 S5 P1 J4 S5 P3 J4 S5 P4 J4 S5 P5 J4 S5 P6 J4 400 500 D S A sample database comprising of Supplier (S), Parts(P) and Projects(J). s P SPJ . { St, SNAME, STATUS, CITY) PRIMARY KEY { SE > (P, PNAME, COLOR, WEIGHT, CITY > PRIMARY KEY { P# > (JI, JNAME, CITY} PRIMARY KEY (J# ) ( St. P#, J, QTY') PRIMARY KEY (St. P, JI ) FOREIGN KEY S REFERENCES S FOREIGN KEY ( PI ) REFERENCES P FOREIGN KEYJ REFERENCES J 7.13 Get full details of all projects. 7.14 Get full details of all projects in London. 7.15 Get supplier numbers for suppliers who supply project JI. 7.16 Get all shipments where the quantity is in the range 300 to 750 inclusive. 7.17 Get all part-color/part-city pairs. Note: Here and subsequently, the term "all" means "all cur rently represented in the database," not "all possible." 7.18 Get all supplier-number/part-number/project-number triples such that the indicated supplier part, and project are all colocated (i.e., all in the same city). 7.19 Get all supplier-number/part-number/project-number triples such that the indicated supplier part, and project are not all colocated. 7.20 Get all supplier-number/part-number/project-number triples such that no two of the indicate supplier, part. and project are colocated. 7.21 Get full details for parts supplied by a supplier in London. 7.22 Get part numbers for parts supplied by a supplier in London to a project in London. 7.23 Get all pairs of city names such that a supplier in the first city supplies a project in the secon city. 7.24 Get part numbers for parts supplied to any project by a supplier in the same city as tha project. 7.25 Get project numbers for projects supplied by at least one supplier not in the same city. 7.26 Get all pairs of part numbers such that some supplier supplies both the indicated parts. 7.27 Get the total aumber of projects supplied by supplier S1. 7.28. Get the total quantity of part P1 supplied by supplier S1. 7.29 For each part being supplied to a project, get the part number, the project number, and the cor- responding total quantity. 7:30 Get part numbers of parts supplied to some project in an average quantity of more than 350. 7.31 Get project names for projects supplied by supplier S1. 7.32 Get colors of parts supplied by supplier S1. 7.33 Get part numbers for parts supplied to any project in London. 7.34 Get project numbers for projects using at least one part available from supplier S1. 7.35 Get supplier numbers for suppliers supplying at least one part supplied by at least one sup- plier who supplies at least one red part. 7.36 Get supplier numbers for suppliers with a status lower than that of supplier SI. 7.37 Get project numbers for projects whose city is first in the alphabetic list of such cities. 7.38 Get project numbers for projects supplied with part PI in an average quantity greater than the greatest quantity in which any part is supplied to project J1. 7.39 Get supplier numbers for suppliers supplying some project with part P1 in a quantity greater than the average shipment quantity of part PI for that project. 7.40 Get project numbers for projects hot supplied with any red part by any London supplier. 7.41 Get project numbers for projects supplied entirely by supplier SI. 7.42 Get part numbers for parts supplied to all projects in London. 7.43 Get supplier numbers for suppliers who supply the same part to all projects. 7.44 Get project numbers for projects supplied with at least all parts available from supplier S1. 7.45 Get all cities in which at least one supplier. part. or project is located. 7.46 Get part numbers for parts that are supplied either by a London supplier or to a London project. 7.47 Get supplier-number/part-aumber pairs such that the indicated supplier does not supply the indicated nart. Pasin I. the Write the queries as mentioned below:- 7.13-7.24 in Relational Algebra and TRC. 7.25 -7.47 in SQL.

Answer rating: 100% (QA)

Solutions Step 1 Here are the SQL queries for each of the given tasks 713 Get full details of all projects SELECT FROM J Explanation This query retrieves all rows and columns from the Projects table J ... View the full answer