Question

Question 1. For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None.

Question 2. Do the tables exhibit entity integrity? Answer yes or no, and then explain your answer.

Question 3. Do the tables exhibit referential integrity? Answer yes or no, and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key.

Question 4. Describe the type(s) of relationship(s) between CUSTOMER and INVOICE.

Question 5. Describe the type(s) of relationship(s) between PRODUCT, LINE and INVOICE.

Question 6. Create the ERD to show the relationships among PRODUCT, LINE, and INVOICE.

Relational diagram CUSTOMER UNE INVOICE PK,FK1 Iny Number HOPK Line Number PK Cus Code Cus_LName Cus_FName Cus_Initial Cus Area Code Cus Phone Cus Balance PK Iny Number FH-04 FK1 Cus_Code Inv_Date FK2 P_Code Line_Units Line_Price PRODUCT VENDOR PK P Code PK V Code -- P_Descript V_Name P_InDate O---OH V_Contact P_QOH V_AreaCode V Phone P_Price P Discount V_State V_Order FKI V_Code P Min V ORDER Y N Table name: CUSTOMER QUS_000E CUS_LNAME CUS_FNANE CUS_NMAL CUS_AREACODE CUS_PHONE CUS_BALANCE 10010 Romas AI A 515 844-2573 1001 Durre Long K 713 10012 Smith Kathy w 515 894-2285 345 88 10013 Olowak Paul F 515 8942180 10014 Orlando Myron 515 222-1672 10015 O Brian A B 713 442 3331 0.00 10016 Brown Jsme G 515 297-1229 221.19 10017 Wllans George 515 290 2550 758.98 10010 Friss Anne G 713 302.7105 218.56 10019 Smith K 815 297 3809 0.00 Table name: VENDOR V_CODE V NANE Y CONTACT V_AREACOCE 21225 Byron, Inc. Smitheon 515 24226 Supert.co, he Flushing 904 21231 DIE Soply Singh 615 21344 Gomez Bros. Ortega 515 22567 Dome Supply Smith 901 23119 Randsets Lid Andersen 90 24004 Bracken Bros. Browning 615 24288 CHUVA, Inc. Hakford 615 25443 BR, the Smith 904 25501 Dame Supolec Slythe 615 25695 Rubicon Systems Orton 904 V PHONE V STATE 223-3234 TIN 215-8995 FL 228-3245 TN 889-2546 KY 678-1419 GA 670-3998 GA 228.1410 TN 898-1234 TIN 227.0093 FL 8903529 TN 455-0092 FL N N Y N Y N N Y LIE UNITS LINE_PRICE 1 Table name: INVOICE W_NUMBER CUS_CODE INV_DATE 1001 10014 18-Jan-18 100g 10011 16-18 10012 16-01-18 10011 17-Jan-18 1005 10018 17.1.18 1000 10014 17-Jan-18 1002 10015 17-Jan-18 10011 17-11-18 1 2 1 Table name: LINE NV_NUMBER UNE_NUMBER P_CODE 1001 1 13-02.P2 1001 2 23105 HB 1002 1 54778-ZT 1003 1 223 BOPD 1003 2 1545-002 10m 3 13.A2.P2 1004 + 54728.2T 4004 222109. HB 1005 1 PVC ZORI 1COB 1.10277 2 2232/QTY 1000 9 2310S-HD 1008 4 89 VREO 1001 1 13.02.P2 1000 2 54778-21 1000 1 PVC23CRT 1008 2 WR3/TT3 1000 3 23109 HD 1499 499 90 567 Table name: PRODUCT P-CODE P_DESCEPT P_INDATE P_00H PYNP_PRICE P_DISCOUNTY_000E 11GERS1 Power painter 15 pal, 3.nozzle 08-NOV-17 8 5 109.99 0.00 25595 13-22172 7.25-n. pwr. Sen bide 13-Dec-17 32 15 14.1 0.05 21346 14-0113 9.00 hrs 13 NOV 17 18 12 17:49 0.00 21344 1548-002 Hd.cloth, 14-in, 22:50 15. J10 15 B 39.95 0.00 23119 1968-V Hd.ch 10 h 3:50 15.18 5 4309 0.00 23110 2282/OTY B Daw, 12-n. blade S0-Dec-17 5 109.92 0.05 ZZERINE BSD, n biede 24-06.17 6 5 99.87 0.0 2238/OPD BSD Cards dril, 1.2 20--18 12 5 38.95 0.05 25585 23109H0 Cowhere 10 0.10 21225 23114-A Sledger, 12 02 Jan-18 8 5 1440 0.06 54770-2T FO-talle, 1.0-n fine 15-Dec-17 20 4.99 0.00 21346 O VREO Hout chan. 18 h. 07 Feb 18 11 5 256 99 005 24288 PVC23DRT PVC, 3.5-n, B. 20 Ft 10 180 75 5.87 0.00 SM-18277 1.25-ih letalserews 01-18 172 75 5.98 0.00 21225 SW.23116 25.in. wd. screw,50 20.Feb. 18 234 100 8.45 0.00 21231 WRITT3 lining, 4x8x16", Sinesh 17. Jer 13 18 5 119.95 0.10 25535 1 5 3 2 12 s 1 1 1 2 1 5 S 1 10992 258.99 14.99 4.99 567 119.95

Answer 1

Question 1:

Please find below the primary key and foreign key for the tables:

Table	Primary key	Foreign key
CUSTOMER	Cus_Code	NA
INVOICE	Inv_Number	Cus_Code from CUSTOMER(Cus_Code)
VENDOR	V_Code	NA
PRODUCT	P_Code	V_Code from VENDOR(V_Code)
LINE	(Inv_Number, Line_Number)	1. Inv_Number from INVOICE(Inv_Number) 2. P_Code from PRODUCT(P_Code)

Question 2:

All the tables shared in the ERD exhibit entity integrity as:

1. All the tables must have a unique value for primary key identified in question 1.

2. The tables are not allowed to have null value in the primary key.

Question 3:

Please fine below the tables and corresponding referential integrity:

Table	Has referential integrity	Details
CUSTOMER	NA	NA
INVOICE	Yes	Each invoice will have a reference to a Customer which is identified by a Cus_Code. A Customer can have many INVOICE, but an INVOICE should be related to one CUSTOMER.
VENDOR	NA	NA
PRODUCT	Yes	Each prouct has a reference to Vendor in Vendor table as it is supplied by a VENDOR.
LINE	Yes	LINE table stores the products included in the invoice along with the quantity of poduct. It has below references: 1. Line will have one INVOICE reference Inv_Code which states that for which invoice this line is added. 2. Each LINE will store a PRODUCT reference which will state that for which product this line is created.

Question 4.

CUSTOMER can have none or many INVOICES whereas an INVOICE must have exactly one CUSTOMER for it. Thus this is a One to Many relationship between CUSTOMER and INVOICE.

Question 5:

A PRODUCT is included in none or many LINE records. An INVOICE can have none or many LINE. Below are the relationships between the entities:

PRODUCT to LINE is a One to Many relationship.

LINE TO INVOICE is a Many to One relationship.

Question 6: ERD as below:

PRODUCT - INVOICE PKP Code HH PK Inv_number P_Description LINE Inv_Date P_inDate PK Line_Number P_QOH PK,FK1 Inv_Number P_Min FK2 P_Code P_Price Line_Units P_Discount Line_Price