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.

The table design in question:

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

1. Even though the field names in this table resemble the contents of the Premiere Products database, you need to ASSUME this is the only table you are using. If the tables in the Premiere Products are available, there is no reason to design this table many of the fields (data) in this table are already available in those tables. That is why you need to make this assumption.

2. To solve this problem, you must carefully FOLLOW the following STEPS in writing your answer:

a. You need to first tell me whether the table is normalized or un-normalized along with the reason for your answer. Simply saying normalized or not will not suffice.

b. You must show how you will take this table to 3NF BUT you CANNOT show what the table(s) will look like when they get to that level. You must first show what the table looks like when it is in 1NF using DBDL (DataBase Design Language) notations. You must also explain WHY you believe it is in 1NF. You will then take the table to 2NF using functional dependency as the analysis tool. Again, provide the EVIDENCE for why you are saying the table is in 2NF. Finally provided the similar process to take it to 3NF along with the evidence.

3. When you analyze functional dependency of different fields, you may see certain fields appearing in multiple functional dependencies. In that case, you should evaluate which functional dependency that field most naturally belongs to. You should not use the same field in multiple tables unless it is necessary to complete the relationship with the table it is supposed to be connected. You need to make a logical choice by understanding the relationships among the tables as you move the original table toward 3NF by subdividing the data into multiple set of tables. This is a LOGICAL process and you cannot just do this mechanically.

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