Normalization h/w

5
votes

enter image description here

Was told to put that into UNF/1NF/2NF/3NF, is this correct?

Show the above data as a relation in UNF (unnormalised data).

Customer (CustomerID, FirstName, LastName, Address, City, Phone, State, Postcode,Qty, ProductNo, Description, Unit price, Total, Subtotal, Shipping, Tax Rate, Date, OrderNo.))

Show the data as a relation/s in 1NF. (Indicate any keys.)

Customer (CustomerID, FirstName, LastName, Address, City, state, Phone, State, Postcode) Product (ProductNo, Qty, Description, Unitprice, total, subtotal, shipping, Tax rate(s), CustomerID(FK).) Order (OrderNo, Date, ProductNo(FK).)

Show the data as a relation/s in 2NF. (Indicate any keys.)

Customer( CustomerID, FirstName, LastName, Address, City, Phone, State, Postcode) Product ( ProductNo, Qty, Description, UnitPrice, CustomerID(FK), Total(FK).) Order( OrderNo, Date, CustomerID(FK), ProductNo(FK).) Total(Total, subtotal, shipping, Tax Rates, ProductNo(FK),CustomerID(FK) )

Show the data as a relation/s in 3NF. (Indicate any keys.)

Customer (CustomerID, FirstName, LastName, Address, City, Phone, State, Postcode) Product (ProductNo, , Description, Unit Price. CustomerID(FK), Total(FK) ) Order (OrderNo, Date, CustomerID(FK).ProductNo(FK) ) Total(Total, subtotal, ProductNo(FK), CustomerID(FK) ) Shipping(Shipping, Tax Rates, Total(FK), OrderNo(FK) ) Qty( QtyID, Qty, ProductNo(FK), OrderNo(FK).)

3
sqldatabasenormalization

3 Answers

4
votes

It looks good to me, but you are missing one crucial piece of the design. You haven't defined any Primary Keys on your tables, although you have identified the foreign keys (use the foreign keys you have to work out the primary keys on each of the tables :)).

0
votes

An interesting thing about invoices....J Frompton orders a rake today, but some time in the future the price will change. However, that does not change the price Frompton payed today.

Once invoices are fulfilled, they really should be moved to a table that is 1NF.

0
votes

Show the above data as a relation in UNF (unnormalised data).

Customer (CustomerID, FirstName, LastName, Address, City, Phone, State, Postcode,Qty, ProductNo, Description, Unit price, Total, Subtotal, Shipping, Tax Rate, Date, OrderNo.))

No, that's not right. There doesn't seem to be any customer ID number on the invoice. Normalization doesn't involve introducing new attributes. As an unnormalized collection of attributes, labeling that list as "Customer" is premature.

Show the data as a relation/s in 1NF. (Indicate any keys.)

  • Customer (CustomerID, FirstName, LastName, Address, City, state, Phone, State, Postcode)
  • Product (ProductNo, Qty, Description, Unitprice, total, subtotal, shipping, Tax rate(s), CustomerID(FK).)
  • Order (OrderNo, Date, ProductNo(FK).)

Drop CustomerID. (See above.) I'm guessing that one of the candidate keys for the "Product" table is "ProductNo". If that's the case, why does that table include "CustomerID"?

Show the data as a relation/s in 2NF. (Indicate any keys.)

  • Customer( CustomerID, FirstName, LastName, Address, City, Phone, State, Postcode)
  • Product ( ProductNo, Qty, Description, UnitPrice, CustomerID(FK), Total(FK).)
  • Order( OrderNo, Date, CustomerID(FK), ProductNo(FK).)
  • Total(Total, subtotal, shipping, Tax Rates, ProductNo(FK),CustomerID(FK) )

2NF has to do with removing partial key dependencies. What partial key dependency did you identify that justified creating the table "Total"? (Hint: there isn't any justification for that.) Do this thought experiment (or build it in SQL): If "Total" is the primary key for the table "Total", what will you do if two orders result in the same total?

I'll stop there for now, because you've really gotten off on the wrong foot. You need to start with a list of all attributes, then identify the candidate keys and functional dependencies. Without starting there, you're unlikely to find 3NF.