Search code examples
databasedatabase-normalization3nf

Need help in normalizing a table upto 3NF?

I want to normalize the table So far i have done this:

is it my 3Nf correct?

Order(orderNo,orderDate,customerId)

Customer(customerId,customerName,customerCurrentAddress,customerPermanentAddress)

Product(productId,productName,Qty,categoryId)

Category(categoryId,categoryName)

Sub-Category(subCatId,subCatName,categoryId)

Given table here: https://drive.google.com/file/d/1F7cQjjxz9rnY6RHaGtZXuwVGFEHBVgNo/view

Solution

  • You have to make a junction table between Order and Product in order to keep track of orders that contain more than one product.

    Order(orderNo,orderDate,customerId)

Order_Details(orderNo,productId,Qty)

Customer(customerId,customerName,customerCurrentAddress,customerPermanentAddress)

Product(productId,productName,Qty,categoryId)

Category(categoryId,categoryName)

Sub-Category(subCatId,subCatName,categoryId)

    Base on the definition from Wiki: a table is in 2NF if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the table. Most of your tables only have 2 columns, so they satisfied this. For Customer(customerId,customerName,customerCurrentAddress,customerPermanentAddress), the candidate key is customerId, and the other columns completely depend on the whole candidate key, then it's OK.

    For 3NF, Wiki said: all the attributes in a table are determined only by the candidate keys of that table and not by any non-prime attributes. As you see, your tables are all satisfied.