Search code examples
mysqlsqldatabasenormalizationdatabase-normalization

Did I normalize correctly?

user view

Doing some practice questions and just wanted to know if I normalized correctly. Thanks.

Un-normalized Table

INVOICE [Invoice#(pk), InvoiceDate, Sales, Cust#,
          (EquipClass, EquipClassDesc, Equip#, EquipDesc, EquipCharge, EquipQTY)]

1NF Tables

INVOICE [Invoice#(pk), InvoiceDate, Sales, Cust#]
Equipment [ Invoice#(pk), Equip#,(pk), EquipClass, EquipClassDesc,
            EquipDesc, EquipCharge, EquipQTY)]

2NF Tables

INVOICE [Invoice#(pk), InvoiceDate, Sales, Cust#]
Equipment [Equip#,(pk), EquipClass, EquipClassDesc, EquipDesc, EquipCharge]
INVOICE_Equipment [Invoice#(pk) (FK), Equip# (pk) (FK), EquipQTY]

3NF Tables

INVOICE [Invoice#(pk), InvoiceDate, Salesperson#(fk)]
Salesperson [Salesperson#(pk), SalespersonName]
Invoice_SalesPerson [Invoice#(pk)(fk), Salesperson#(pk)(fk), Cust#]
Equipment [Equip#,(pk), EquipClass(fk), EquipDesc, EquipCharge,]
Equipment_Class [EquipClass(pk), EquipClassDesc]
INVOICE_Equipment [Invoice#(pk) (FK), Equip# (pk) (FK), EquipQTY]
Solution

  • Since the Unnormalized, 1NF and 2NF tables never mention a sales person, there's no basis for the splits and additions that occur as you progress from 2NF to 3NF in the prior schemas.

    Additionally, an invoice is more probably associated with a customer and a salesperson directly (it would have independent FK references to the customer table (which isn't identified in the schema) and the salesperson table). The Invoice_SalesPerson table looks spurious, and the removal of customer number from Invoice looks dubious. It could be done the way you've shown, but I'd never mark it as correct without a clear, verbose, cogent explanation of why that was necessary (and I'd still be sceptical that it was not necessary).