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)