Using the schema, tables, and data you created in Exercise 1 (The picture below)

Question

Using the schema, tables, and data you created in Exercise 1 (The picture below):

Examine the candidate keys in each table.

Write ALTER TABLE statements for each table to create Primary Keys and Foreign Keys where applicable.

Test the statements in your schema to make sure they work.

Tables Customer ?len columns ? CUSCODE ? CUS-LNAME - CUS FNAME ? CUS-INITIAL ? CUS-AREACODE ? CUS-PHONE ? CUS-BALANCE Indexes Foreign Keys Triggers ?| | invoice ?len columns ? INVNUMBER ? CUSCODE ? INVDATE ? INVSUBTOTAL - - - - INV TAX ? INV TOTAL Indexes Foreign Keys Triggers ?- | line Columns ? INVNUMBER ? LINENUMBER ? PCODE ? LINEUNITS ? LINEPRICE ? LINE TOTAL Indexes Foreign Keys Triggers - - - - - ? product ?len columns ? PCODE ? PDESCRIPT ? P. INDATE ? P.Q0H ? PMIN ? PPRICE ? PDISCOUNT ? V-CODE Indexes Foreign Keys Triggers - - - - - vendo ? Columns ? VCODE ? V-NAME ? VCONTACT ? VAREACODE ? V PHONE - - - V STATE ? VORDER Indexes Foreign Keys Triggers -

Explanation / Answer

--Below syntax has been tested in MYSQL DB
--Customer
ALTER TABLE customer
ADD PRIMARY KEY(CUS_CODE)

--invoice
ALTER TABLE invoice
ADD PRIMARY KEY (INV_NUMBER);

ALTER TABLE invoice
ADD CONSTRAINT FK_CUS_CODE
FOREIGN KEY (CUS_CODE) REFERENCES customer(CUS_CODE);

--line
ALTER TABLE line
ADD PRIMARY KEY (LINE_NUMBER);

ALTER TABLE line
ADD CONSTRAINT FK_INV_NUMBER
FOREIGN KEY (INV_NUMBER) REFERENCES invoice(INV_NUMBER);

ALTER TABLE line
ADD CONSTRAINT FK_P_CODE
FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE);

--PRODUCT
ALTER TABLE product
ADD PRIMARY KEY (P_CODE);

ALTER TABLE product
ADD CONSTRAINT FK_IV_CODE
FOREIGN KEY (V_CODE) REFERENCES vendor(V_CODE);

--Vendor
ALTER TABLE vendor
ADD PRIMARY KEY (V_CODE);

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.