Create a set of normalized relations given the invoice shown below. Start with a

ID: 3848221 • Letter: C

Question

Create a set of normalized relations given the invoice shown below. Start with a single table (could be in NNF) and then proceed to 1NF, 2NF and 3NF. If the first table is in NNF, explain why it is NNF and the steps to take it to 1NF. Do not draw a dependency diagram for NNF; only the relational schema is needed. At each subsequent NF show the relational schema and the dependency diagram for each table. Write out the dependencies. State clearly any (reasonable) assumptions you may make. Business Rules: The tax rate is based on the customer's state. All subtotals, the tax amount, and the invoice total are computed.

Explanation / Answer

Table under NNF

This table is under NNF as it will contain more than one companies information. Meaning if two companies have asked for same part it will show their name separated by user defined delimiter for our understanding we can use comma ‘,’ as delimiter. So it will show company1, company 2.

Invoice table Columns

Invoice #

Cust ID

Comnpany Name

Date

Address

Part #

Description

# Used

Price

Total

Tax Rate

Data/table under 1NF : As per First normal form no two rows can contain repeating information that means each row needs to be having different set of data (information). So in 1NF table would have two data rows one for “company 1” and second for “company 2”. Using 1NF increases data redundancy as table will contain 1 row for “company 1” and second row for “company 2”.

Date/table under 2NF : As second normal form or 2NF it would be required to have a separate table for company and we can match them using company name If two companies have asked for same part.

Data/table under 3NF : The third normal form says that every non prime element of table should depend on primary key and not on any other data element so in this case part table is necessary which can be joined with invoice table using part #. So for our example we can say that part “001” is required from part table and we can join invoice table and part table using part number. One important thing to keep in mind is that for any database to be in 3NF it should also be in 2NF first and it goes on.