Question #1 (Total: 24 pts): Tables of SUPPLIERS, PARTS, PROJECT and SHIPMENT are used for all...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Question #1 (Total: 24 pts): Tables of SUPPLIERS, PARTS, PROJECT and SHIPMENT are used for all the following QUERIES (1-4) SUPPLIERS PARTS Saum Sname SMITH JONES BLAKE 30 S1 $2 S3 $4 $5 Puum BASSIKRA JFARZAREA PROJECT CLARK ADAMS Proinum Projname SORTER PUNCH READER CONSOLE Status 22888 TAPE DRUM 20 10 20 30 Pname Color NUT RED GREEN 17 BOLT SCREW BLUE SCREW RED CAMERA BLUE 32 WRENCH RED 14 19 C-O-G GREEN 12 COLLATOR TERMINAL City LONDON PARIS PARIS LONDON ATHENS Weight 12 17 City PARIS ROME ATHENS ATHENS LONDON OSLO LONDON LONDON City LONDON PARIS ROME LONDON PARIS LONDON ROME SHIPMENT Snum $1 S1 2222222222KKESKKKKKKKKKK 52 52 $2 S3 S3 S4 S4 S5 S5 S5 S5 S5 S5 S5 S5 S5 $5 Puum Proinum Pl Pl P3 22222222222222222=2ZARS P3 P3 P3 P3 P3 P3 P5 P3 P4 P6 P6 P2 P5 P5 P7 Pl P3 P4 P5 P6 P2 JI ===AAZAXEXE=AASKAZKA==== J4 JI 12 J3 J4 J5 16 37 J6 12 JI 12 13 J6 15 12 J4 J5 37 J4 J4 J4 J4 Quantity 200 700 450 210 NULL 509 600 400 812 NULL 215 512 313 314 NULL 179 513 145 269 874 476 529 318 619 For each query, write a general SQL statement that will produce the correct output. (1) Get all the colors that are NOT shipped to Project J1. Your Answer: (2) Get supplier names for suppliers who didn't ship to SORTER project. Your Answer: (3) Get the names of the projects which did NOT receive shipments from ANY suppliers Your Answer: (4) Get the Project names that use at least 4 different Parts shipped by any suppliers. (HINT: S1 ships P1, P2 and P3 means that S1 can ship 3 different Parts.) Your Answer: Given the following Relation Schema (Grade Report) with a set of Functional Dependencies F Table: Grade Report Student In Student Name Major Course 168300458 Williams IT ITD320 168300458 Williams IT ITP310 543291073 Baker Accta ITD320 543291073 Baker Aceta Acct201 543291073 Baker Acta Mktg300 FDs={ Student ID Student Name, Major Course ID Course Title. Instructor Name. Instructor Location Student ID. Course ID Grade Instructor Name Instructor Location } Identify the following: Course Title Instructor Name Instructor Location Grade Database Imp A Programming B Database Imp Fund Accta, Intro Mktg Codd Parsons Codd Miller Bennett a) All the Candidate Keys (CKs) (Total: 4 pts): b) All the determinants. (Total:3 pts): B104 B317 B104 H310 B212 c) All Key & Non-Key attributes. (Total: 3 pts): d) All the attributes that are Fully Functional Dependent (FFD) on the CKS (Total: 3 pts): B A e) In what normal form is the table Grade Report? Explain why (Total: 3 pts): f) Split the table Grade Report into a set of 3NF tables. How did you arrive at your solution? (Total: 10 pts): Answer a) Answer b) Answer c) 3 Answer e) Answer f) Question #1 (Total: 24 pts): Tables of SUPPLIERS, PARTS, PROJECT and SHIPMENT are used for all the following QUERIES (1-4) SUPPLIERS PARTS Saum Sname SMITH JONES BLAKE 30 S1 $2 S3 $4 $5 Puum BASSIKRA JFARZAREA PROJECT CLARK ADAMS Proinum Projname SORTER PUNCH READER CONSOLE Status 22888 TAPE DRUM 20 10 20 30 Pname Color NUT RED GREEN 17 BOLT SCREW BLUE SCREW RED CAMERA BLUE 32 WRENCH RED 14 19 C-O-G GREEN 12 COLLATOR TERMINAL City LONDON PARIS PARIS LONDON ATHENS Weight 12 17 City PARIS ROME ATHENS ATHENS LONDON OSLO LONDON LONDON City LONDON PARIS ROME LONDON PARIS LONDON ROME SHIPMENT Snum $1 S1 2222222222KKESKKKKKKKKKK 52 52 $2 S3 S3 S4 S4 S5 S5 S5 S5 S5 S5 S5 S5 S5 $5 Puum Proinum Pl Pl P3 22222222222222222=2ZARS P3 P3 P3 P3 P3 P3 P5 P3 P4 P6 P6 P2 P5 P5 P7 Pl P3 P4 P5 P6 P2 JI ===AAZAXEXE=AASKAZKA==== J4 JI 12 J3 J4 J5 16 37 J6 12 JI 12 13 J6 15 12 J4 J5 37 J4 J4 J4 J4 Quantity 200 700 450 210 NULL 509 600 400 812 NULL 215 512 313 314 NULL 179 513 145 269 874 476 529 318 619 For each query, write a general SQL statement that will produce the correct output. (1) Get all the colors that are NOT shipped to Project J1. Your Answer: (2) Get supplier names for suppliers who didn't ship to SORTER project. Your Answer: (3) Get the names of the projects which did NOT receive shipments from ANY suppliers Your Answer: (4) Get the Project names that use at least 4 different Parts shipped by any suppliers. (HINT: S1 ships P1, P2 and P3 means that S1 can ship 3 different Parts.) Your Answer: Given the following Relation Schema (Grade Report) with a set of Functional Dependencies F Table: Grade Report Student In Student Name Major Course 168300458 Williams IT ITD320 168300458 Williams IT ITP310 543291073 Baker Accta ITD320 543291073 Baker Aceta Acct201 543291073 Baker Acta Mktg300 FDs={ Student ID Student Name, Major Course ID Course Title. Instructor Name. Instructor Location Student ID. Course ID Grade Instructor Name Instructor Location } Identify the following: Course Title Instructor Name Instructor Location Grade Database Imp A Programming B Database Imp Fund Accta, Intro Mktg Codd Parsons Codd Miller Bennett a) All the Candidate Keys (CKs) (Total: 4 pts): b) All the determinants. (Total:3 pts): B104 B317 B104 H310 B212 c) All Key & Non-Key attributes. (Total: 3 pts): d) All the attributes that are Fully Functional Dependent (FFD) on the CKS (Total: 3 pts): B A e) In what normal form is the table Grade Report? Explain why (Total: 3 pts): f) Split the table Grade Report into a set of 3NF tables. How did you arrive at your solution? (Total: 10 pts): Answer a) Answer b) Answer c) 3 Answer e) Answer f)
Expert Answer:
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these databases questions
-
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...
-
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...
-
What is the rate of change of the area of a circle (A = r 2 ) with respect to the radius when the radius is r = 3?
-
For a population with = 40 and = 7, find the z-score for each of the following X values. (You probably will need to use a formula and a calculator to find these values.) X = 45 X = 51 X 25 X = 41 X...
-
Fluid from a large reservoir at temperature to flows into a circular pipe of radius R. The pipe walls are wound with an electric-resistance coil which delivers heat to the fluid at a rate qw (energy...
-
The fastest baseball pitchers can throw the \(0.145-\mathrm{kg}\) ball at speeds of about \(45 \mathrm{~m} / \mathrm{s}\). Ignoring air resistance, what height must the ball be dropped from to hit...
-
The ledger of Rowen Company contains the following balances: Retained Earnings $30,000; Dividends $2,000; Service Revenue $47,000; Salaries and Wages Expense $27,000; and Supplies Expense $5,000....
-
Assuming a rate of return of 8%, calculate the monthly savings needed for education assuming that savings will continue until the children's college education is completed. Education Because of the...
-
Ulysses Inc. is a shipping company with $100 million in earnings before interest and taxes that is expected to have earnings growth of 10% for the next five years. At the end of the fifth year, you...
-
A game consists of rolling a die and receiving \(\$ 12\) if a one is rolled and nothing otherwise. What is the mathematical expectation?
-
Lymnozyme cures most infections in Koi fish caused by bacteria; in fact, it has been shown to be \(96 \%\) effective if used according to the directions. If 15 Koi with a bacterial infection are...
-
Answer the question in Problem 19 assuming that you are in the audience and want the probability that the contestant will win the car you have selected. Data from Problem 19 The following problem...
-
Consider the experiment of selecting three items (without replacement) from a sample space of 100 , of which 5 items are defective. Let \(A_{1}=\{\) first item selected is defective \(\}, A_{2}=\)...
-
Find the binomial probabilities in Problems 3-10. \(n=5, X=3, p=0.30\)
-
Andrew deals with several vendors and one of them is Navi Wholesale. Andrew often places orders for chocolate of different flavors at the same time. Each order is placed with one specific vendor. The...
-
Quadrilateral EFGH is a kite. Find mG. E H <105 G 50 F
-
Bev and Ken Hair have been married for 3 years. They live at 3567 River Street, Springfield, MO 63126. Ken is a full-time student at Southwest Missouri State University (SMSU) and Bev works as an...
-
Cedar Corporation has an S corporation election in effect. During the 2012 calendar tax year, the corporation had ordinary taxable income of $200,000, and on January 15, 2012, the corporation paid...
-
Amy is a calendar-year taxpayer reporting on the cash basis. Please indicate how she should treat the following items for 2012: a. She makes a deductible contribution to an IRA on April 15,...
-
Using the information presented in Exhibit 4, the quick ratio for SAP Group at 31 December 2017 is closest to: A . 1.00. B . 1.07. C . 1.17.
-
Using the information presented in Exhibit 14, the fi nancial leverage ratio for SAP Group at December 31, 2017 is c losest to: A . 1.50. B . 1.66. C . 2.00.
-
Th e quick ratio for Company A is closest to: A . 0.43. B . 0.57. C . 1.00.
Study smarter with the SolutionInn App