Supplier S PRODUCT P PROJECTS J S# SNAME S1 SMITH S2 JONES S3 BLAKE 54 CLARK...
Fantastic news! We've Found the answer you've been seeking!
Question:
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.
Expert Answer:
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
Related Book For
Data Modeling and Database Design
ISBN: 978-1285085258
2nd edition
Authors: Narayan S. Umanath, Richard W. Scammel
Posted Date:
Students also viewed these programming questions
-
Austin sues ITC Classmate for his injuries. He claims past and future lost wages, medical expenses, and emotional pain and suffering. ITC Classmate and Austin agree to settle Austin's claims prior to...
-
A company develops a new product at a cost of $400,000. It is anticipated that the product will experience high demand for a period of four years. Annual production of the product for the next four...
-
Suppose PFT is considering implementing a new energy efficiency measure (EEM). The EEM involves sensors that cost $4761 in total to install, and are expected to last 15 years. Savings are identical...
-
Matching Financial Statement Items to Financial Statement Categories According to its annual report, P&Gs more than 250 brands include Pampers, Tide, Ariel, Always, Whisper, Pantene, Bounty,...
-
On September 1, 2012, Southwest Airlines borrows $40 million, of which $8 million is due next year. Show how Southwest Airlines would record the $40 million debt on its December 31, 2012, balance...
-
If a magnifying glass gives an angular magnification of 1.5 x when viewed with relaxed eyes, what is the power of the lens?
-
Graphic analysis of cost-volume data Luxury Cars, Inc. imports a variety of cars targeted for the high-priced end of automobile market. Over the last three years, the company's sales volume has...
-
Do you agree with David Ogilvy that the primary function of advertising is selling? How does that fit with the three advertising objectives of informing, persuading, and reminding? These days, there...
-
Allocate the support department costs to the production departments using sequential method. Allocate the support department with the highest department cost first
-
Kai is the president of Zebra Antiques. An employee, Reese Francis, is due a raise. Reeses current benefit analysis is as follows: Compute the benefit analysis assuming: 3 percent increase in pay. ...
-
"In what circumstances, in what kind of case and at what stage should DR be imposed?" Please discuss, comment how compulsory dispute resolution could affect different types of ADR. Indicate in your...
-
Nelson Industries manufactures a part for a type of aircraft engine that is becoming obsolete. The sales history for the last 10 years is as follows: Year 11 Dec- Dec- Dec- Dec- Dec- Dec- Dec- Dec-...
-
A 100-seat restaurant serving dinner only is preparing an operating budget for the coming year. The average check is $18.50, with an average seat TO of 1.75 and the restaurant operates 300 days/yr....
-
What is the future value in 27 years of an ordinary annuity cash flow of $704 every quarter of a year at the end of the period, at an annual interest rate of 8.89 percent per year, compounded...
-
Which of the following statements is correct: The quantum entropy is upper bounded by the logarithm of the quantum system's space dimension. Quantum key distribution is not secure against coherent...
-
A primary benefit of a good bookkeeping systems is the compilation of financial statements including the OA) Angel Investment report. O B) Equity Investment report. OC) Income Statement. OD) Accrual...
-
22. Traveling at a rate of 60 mph, Angie took one hour to get to the city this morning. Coming home by the same route, she was able to travel at only one- third of her morning rate because of...
-
Halley's comet travels in an ellipti- cal orbit with a = 17.95 and b = 4.44 and passes by Earth roughly every 76 years. Note that each unit represents one astronomical unit, or 93 million miles. The...
-
Consider the ER diagram of Figure 5.5. State meaningful semantics for additional binary relationships among the entity types in the diagram and update the ER diagram accordingly with full...
-
Explain why data redundancy exists for the attributes Discount and Location in the STOCK table in Figure 7.1c.
-
The series of tasks in this exercise is based on the following ER diagram and its associated logical schema. a. Write appropriate CREATE TABLE statements for the logical schema. Be sure to define all...
-
Computing unit cost for department and for completed units} with beginning inventory Robert E. Lee Company has two production departments. Blending had 1,000 units in process at the beginning of the...
-
Identifying cost flows in process cost system} List in columnar form the transactions and the accounts debited and credited to reflect the flow of costs through a process cost accounting system for...
-
Departmental cost work sheet analysis; cost of production} summary, three departments, no beginning inventories Goode Manufacturing Co. has three departments and uses the process cost system of...
Study smarter with the SolutionInn App