SQL

Background:

Premiere Products database team is working on storing the information contained in the order invoice form in a databases table. Looking at what data is captured in the order form, they came up with the following table description using DBDL (DataBase Design Language).

ORDER_INVOICE (InvoiceNum, Date, CustomerNum, CustomerName, Street, City, State, Zip, RepNum, RepFirstName, RepLastName, (ItemNum, Description, QtyOrdered, QuotedPrice))

Assumption:

-Each order transaction is identified uniquely by InvoiceNum. -Customer may order multiple times and an order may contain multiple items. -QuotedPrice may vary on the same ItemNum depending on the QtyOrdered.

Tasks to be completed:

1- Determine the level of normalization of this ORDER_INVOICE table. EXPLAIN WHY IN DETAIL. 2- Using functional dependency analysis and the definition of different levels of normalization (1NF, 2NF, etc.), show step by step how you would bring this table to the 3NF level. NOTE: This table is NOT a part of the existing Premiere Products database tables. Assume NO other tables exist at this point.

ORDER 21617 PREMIERE PRODUCTS DATE 10/23/2007 Heading CLISTOMER 608 Johnson's Department Store 372 Oxford Sheldon FL 3353 SALES REP 65 Juan Perez NUMBER PART DESCRIPTION NUMBER PRICE TOTAL ORDERED Order lines BV06 Home Gym CD52 Microwave oven 794.95 150.00 1589.90 600.00 Body Extensions Footing ORDERTOTAL >> 2189.90