Study the scenario and complete the questions that follow:

Lethabo Company

Lethabo company is conducting an inventory analysis based on the provided table below. They are interested in understanding the sales patterns and performance of different products and vendors.

ATTRIBUTE NAMESAMPLE VALUESAMPLE VALUESAMPLE VALUESAMPLE VALUESAMPLE VALUEINV_NUM211347211347211347211348211349PROD_NUMAA-E3422QWQD-300932XRU-995748GAA-E3422QWGH-778345PSALE_DATE15-Jan-202315-Jan-202315-Jan-202315-Jan-202316-Jan-2023PROD_LABELPotary Zander0.25-in. drill bitBand sawPotary ZanderPower drillVEND_CODE211211309211157VEND_NAMEAlwaysWin, Inc.AlwaysWin, Inc.BeGood, Inc.AlwaysWin, Inc.ToughGo, Inc.QUANT_SOLD18121PROD_PRICER49.95R3.54R39.99R49.95R87.75

Source: Coronel, Morris, & Rob. (2013).

a. Write the relational schema, draw its dependency diagram, and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that an invoice number references more than one product. (Hint: This table uses a composite primary key.)

(12 Marks)

b. Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you created.

(16 Marks)

c. Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also identify the normal forms for each table structure you created.

(12 Marks)

2Based on the Invoice table provided in Question 2, answer the following questions:

2a. Retrieve the products with a total sales amount greater than the average sales amount of all products.

(5 Marks)

2b. Retrieve the vendor names and the average quantity sold per invoice for vendors who have more than one product in the invoice table.

(5 Marks)