Below is the what each Number requires as far as First through Fourth Normal For

Question

Below is the what each Number requires as far as First through Fourth Normal Form.

RepNum LastName FirstName Street State 724 Vinca Dr. Grove S41,317.00 0.08 S27,789.2S 0.06 Tian Sefton Janet267 Oakley St. Congaree CA 90097 1785 Tyler Ave. Northfield CA 90098 um CustomerName Street State PostalCode Balance CreditLimit RepNum $1,210.25 $7,500.00 15 Brookings Direct The Everything 452 Columbus Dr. Grove 342 Magee St. Congaree CA90097 $879.25 $7,500.00 30 Museum Store Cards and More 167 Hale St. $3,456.75 $15,000.00 45 $678.90 $7,500.00 30 S124.75 $7,500.00 45 S935.75 $7,500.00 15 3345 Devon Ave. Almondton CA 90125 Cricket Gift Shop 372 Oxford St.Grove Unique Gifts 786 Passmore St. Northfield CA 90098 All Season Gifts 382 Wildwood Ave. Fullton CA 90085 12 Rising Sun Ave. Congaree CA 90097 OrderNum OrderDate CustomerNum OrderNum NumOrdered Quoted Price 10/13/2015 260 10/15/2015 586 ItemNum Description 7 Giant Star Brain Teaser KD34 Pentominoes Brain Teaser Brain Teaser Wood Block Set (62 piece)

Explanation / Answer

1.The functional dependencies that exists in table 1 are:

1.ItemNum->{Description,OnHand,Category,Price}

2.OrderNum->{OrderDate,CustomerNum}

3.CustomerNum->{CustomerName,RepNum}

4.RepNum->{LastName,FirstName}

5.{ItemNum,OrderNum}->{NumOrdered,QuotedPrice}

1NF Normalization:

Item(ItemNum,Description,OnHand,Category,Price,OrderNum)

Order(OrderNum,OrderDate,CustomerNum,CustomerName,RepNum,LastName,FirstNAme,NumOrdered,QuotedPrice)

2/3NF

From Dependency 1 and 5 we see that ItemNum, Description, OnHand, category and Price are not fully dependent on Key{ItemNum,OrderNum} So according to 2NF

Item(ItemNum,Description,OnHand,Category,Price)

OrderLine(ItemNum,OrderNum,NumOrdered,QuotedPrice)

From functional dependency 2 and 3 there is a transitive dependency between OrderNum,CustomerNum .So the tables are:

Orders(OrderNum,OrderDate,CustomerNum)

Customer(CustomerNum,CustomerName,RepNum)

From functional dependency 3 and 4there is a transitive dependency between CustomerNum and RepNum.So the tables are:

Rep(RepNum,LastName,FirstName)

2.Invoice(InvoiceNum,CustomerNum,LastName,FirstName,Street,City,State,PostalCode,Date,(ItemNum,Description,Price,NumShipped))

The functional dependencies are:

1.InvoiceNum,CustomerNum->{ItemNum,Date}

2.CustomerNum->{LastName,FirstName,Street,City,State,PostalCode}

3.InvoiceNum,ItemNum->{Description,Price,NumShipped}

The tables in 2/3NF are

Customer(CustomerNum,LastName,FirstName,Street,City,State,PostalCode,Date)

Invoice(InvoiceNum,CustomerNum,ItemNum,Date)

Item(InvoiceNum,ItemNum,Description,Price,NumShipped)

3.Item(ItemNum,Description,Category,Price,(StorehouseNum,StorehouseName,OnHand),(SupplierNum,SupplierName,LeadTime))

The functional dependencies are

ItemNum->{Description,Category,Price}

StorehouseNum->{StorehouseName,OnHand}

SupplierNum->{SupplierName,LeadTime}

ItemNum->{SupplierNum}

ItemNum->{StorehouseNum}

1NF are:

Item(ItemNum,Description,Category,Price)

StoreHouse(StorehouseNum,StorehouseName,OnHand)

Supplier(SupplierNum,SupplierName,LeadTime)

In 4NF the tables are:

Item(ItemNum,Description,Category,Price)

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

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