Suggested Solution to ABC Manufacturing Company

Suggested Solution to ABC Manufacturing Company

1.Steps For each User View:

a)Write a relation for the user view.

b)Normalize the relation to 1NF, then to 2NF, then to 3NF

c) Merge the resulting tables that have identical keys.

Begin Now:

Let’s begin with the third user view: the Product Explosion Report.

[ProdCode, ProdDesc, (Part#, PartDesc, QtyReq)] 0NF

I) [ProdCode, ProdDesc] 1NF,2NF,3NF

[ProdCode, Part#, PartDesc, QtyReq] 1NF

II)[Part#, PartDesc]2NF,3NF

III)[ProdCode, Part#, QtyReq]2NF,3NF

Note:Tables I, II, III are in 3NF because there are no transitive dependencies.

Now User View 2: Products Inventory Report

V)[ProdCode, ProdDesc, QOH, QBO,ItemPrice]UNF, 1NF, 2NF, 3NF

______

Now User View 1: ABC Manufacturing Order Form

Discussion on PK selection:

We notice that every field on the user view is functionally dependant on ORDER# or is in a group that is functionally dependant on ORDER#. So we can use ORDER#.

Notice that “Filled” is a calculated quantity (= Qty – Backordered). Other calculated fields that will not be included in the tables are: Amount, Total,

Discount Amt and Amount Owing.

0NF

[O#, Date,Cname, CAD1, CAD2, CPC, SAD1, SAD2,Ph1,Ph2,Disc,(Item#,ProdCode, ProdDesc,Qty, QBO, ItemPrice)]

1NF – Eliminate Repeating Groups

VI)[O#, Date, Cname, CAD1, CAD2, CPC, SAD1, SAD2,Ph1,Ph2,Disc]

[O#,ProdCode, Item#, ProdDesc,Qty, QBO, ItemPrice]

Note: We could also have chosen Item# as the PK of the repeating group.

2NF – Eliminate Partial Dependencies

VII)[ProdCode, ProdDesc, ItemPrice]

VIII)[O#, ProdCode, Item#, Qty, QBO]

3NF – Eliminate Transitive Dependencies

Table (I) above has transitive dependencies

[O#, Date, Cname, CAD1, CAD2, CPC, SAD1, SAD2,Ph1,Ph2,Disc]

Although Date is determined by O#, the other non-key attributes (CAD1,CAD2,CPC, SAD1, SAD2,Ph1,Ph2, and Disc) are determined by a non-key attribute: Cname.

IX)[O#, Date, CustID(FK)] Use Foreign Key CustID(FK)

Create a CUSTID to ensure trhe uniqeness of the Primary Key

X)[CustID, Cname, CAD1, CAD2, CPC, SAD1, SAD2,Ph1,Ph2,Disc]

Now we merge the tables from the three user views:

I) [ProdCode, ProdDesc]

II)[Part#, PartDesc]

III)[ProdCode, Part#, QtyReq]

IV)[ProdCode, ProdDesc, ItemPrice]

V)[Date, ProdCode , QOH, QBO]

VII)[ProdCode, ProdDesc, ItemPrice]

VIII)[O#, ProdCode, Item#, Qty, QBO]

IX)[O#, Date, CustID(FK)]

X)[CustID, Cname, CAD1, CAD2, CPC, SAD1, SAD2,Ph1,Ph2,Disc]

Tables I, IV, and VII have exactly the same primary key, so we will include all the non-key attributes on one table, no other tables can be merged.

XI)[ProdCode, ProdDesc, ItemPrice]

II)[Part#, PartDesc]

III)[ProdCode, Part#, QtyReq]

V)[Date, ProdCode , QOH, QBO]

VIII)[O#, ProdCode, Item#, Qty, QBO]

IX)[O#, Date, CustID(FK)]

X)[CustID, Cname, CAD1, CAD2, CPC, SAD1, SAD2,Ph1,Ph2,Disc]

Now we must name the tables:

PRODUCT[ProdCode, ProdDesc, ItemPrice]

PART[Part#, PartDesc]

PARTS-NEEDED[ProdCode, Part#, QtyReq]

STOCK[Date, ProdCode , QOH, QBO]

ORDER-LINE[O#, ProdCode, Item#, Qty, QBO]

ORDER[O#, Date, CustID(FK)]

CUSTOMER[CustID, Cname, CAD1, CAD2, CPC, SAD1, SAD2,Ph1,Ph2,Disc]

------