SQL Background: Premiere Products database team is working on storing the inform

Question

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

Explanation / Answer

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

The table is not normalized in any form as attributes (ItemNum, Description, QtyOrdered, QuotedPrice) are not atomic.

The table is in First Normal form after making all attributes atomic.

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

2

Now the tables needs to be in 2NF and 3NF by making all nonkey attributes fully functionally dependent on the primary key and removing transitive dependencies.

Functional dependencies

InvoiceNum -> Date

CustomerNum -> CustomerName

CustomerNum -> Street

CustomerNum -> City

CustomerNum -> State

CustomerNum -> Zip

RepNum -> RepFirstName

RepNum ->RepLastName

ItemNum -> Description

ItemNum -> QtyOrdered

ItemNum -> QuotedPrice

Normalized Tables

ORDER_INVOICE (InvoiceNum, Date)

CUSTOMER (CustomerNum, CustomerName, Street, City, State, Zip)

REP(RepNum, RepFirstName, RepLastName)

ITEM( ItemNum, Description, QtyOrdered, QuotedPrice)

underlined are primary keys

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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