Database Management: Instructions [20pts] Given the following INVOICE table structure, assume that the table does not co

[answerhappygod]

Database Management:
Instructions [20pts]
Given the following INVOICE table structure, assume that thetable does not contain repeating groups and that an invoice numbermay contain more than one product. Also, assume any given productis supplied by a single vendor but a vendor can supply manyproducts.
Table 1: Sample INVOICE RECORDS

Database Management Instructions 20pts Given The Following Invoice Table Structure Assume That The Table Does Not Co 1 (27.92 KiB) Viewed 32 times

1. Please write the relational schema, and underline thePK. [5 pts]
InvoiceRecord(.............................................................................................)
2. Please draw the dependency diagram and identify alldependencies, including all partial and transitive dependencies.The following first symbol can be used to draw full dependency andthe second symbol can be used to draw partial or transitivedependency. Copy those symbols and resize them as you see fit toadd the dependencies. If arrows overlap, change the color. [15pts]

Database Management Instructions 20pts Given The Following Invoice Table Structure Assume That The Table Does Not Co 2 (7.47 KiB) Viewed 32 times

**Example on how to do it: This following picture isjust an sample. Try to follow this**

Database Management Instructions 20pts Given The Following Invoice Table Structure Assume That The Table Does Not Co 3 (121.62 KiB) Viewed 32 times

INV NUM PROD NUM SALE_DATE 211347 AA-E3422QW 15-Jan-10 211347 QD-300932X 15-Jan-10 211347 RU-995748G 15-Jan-10 211348 AA-E3422QW 15-Jan-10 211349 GH-778345P 16-Jan-10 PROD_LABEL Rotary sander 0.25-in. drill bit Band saw Rotary sander Power drill VEND_COD E 211 211 309 211 157 VEND_NAME QUANT_SOLD PROD_PRICE NeverFail, Inc. 1 NeverFail, Inc. 8 BeGood, Inc. 1 NeverFail, Inc. 2 ToughGo, Inc. 1 $49.95 $3.45 $39.99 $49.95 $87.75
INV_NUM PROD_NUM SALE_DATE PROD_LABEL Pick one(Partial/trans) VEND_COD E VEND_NAME QUANT_SOLD PROD_PRICE
PROJ_NUM PROJ_NAME EMP_NUM Evergreen Evergreen Evergreen Evergreen Evergreen Amber Wave 15 15 15 15 15 18 18 18 18 22 22 22 22 22 25 25 25 25 25 25 25 Amber Wave Amber Wave Amber Wave Rolling Tide Rolling Tide Rolling Tide Rolling Tide Rolling Tide Starflight Starflight Starflight Starflight Starflight Starflight Starflight Dependency Diagram JOB_CLASS Elect. Engineer Database Designer Database Designer Programmer Systems Analyst Applications Designer General Support Systems Analyst DSS Analyst Database Designer Systems Analyst Applications Designer Clerical Support Programmer Programmer Systems Analyst Database Designer Applications Designer Systems Analyst General Support DSS Analyst PROJ_NUM → PROJ_NAME JOB CLASS CHG HOUR 103 101 105 106 102 114 118 104 112 105 104 113 111. 106 107 115 101 114 108 118 112 EMP NAME June E. Arbough John G. News Alice K. Johnson " William Smithfield David H. Senior Annelise Jones James J. Frommer Anne K. Ramoras Partial dependency Darlene M. Smithson Alice K Johnson Anne K. Ramoras Delbert K. Joenbrood Geoff B. Wabash William Smithfield Maria D. Alonzo Travis B. Bawangi John G. News* Annelise Jones Step 1: Identify the dependencies: Note the PK is: (PROJ_NUM, EMP_NUM) PROJ_NUM, EMP_NUM → PROJ_NAME, EMP_NAME, JOB CLASS, CHG HOUR, HOURS Ralph B. Washington James J. Frommer Darlene M. Smithson EMP_NUM EMP_NAME, JOB CLASS, CHG HOUR Step 2: Identify which ones are 'partial' and which ones 'transitive'. Step 3: Draw the dependency diagram: CHG HOUR HOURS 84.50 105.00 105.00 35.75 Transitive dependency 96.75 48.10 18.36 96.75 45.95 105.00 96.75 48.10 26.87 Partial dependencies 35.75 35.75 96.75 105.00 48.10 96.75 18.36 45.95 23.8 19.4 35.7 12.6 23.8 24.6 45.3 32.4 44.0 64.7 48.4 Partial dependencies: depending on part of a composite PK 23.6 22.0 12.8 24.6 45.8 56.3 33.1 Transitive dependencies: depending on a key that is not PK 23.6 30.5 41.4 PRO NUM PROJ_NAME EMP NUM EMP_NAME JOB_CLASS CHG_HOUR HOURS