Search code examples
databasedatabase-normalization

Database Normalization (3NF) - Foreign keys

After trying to understand the 3NF, I've come into a little issue that I am not sure I understand correctly.

The problem occurs in a structural component as follows:

s = {
Product(ProductID, ProductName, ProductDescription, SupplierName, SupplierAddress, SupplierID)
}

F = {
ProductID -> ProductName, ProductDescription
SupplierID -> SupplierName, SupplierAddress
}

Of course, by default, I'll move both the SupplierName and SupplierAddress to a different entity / table. The issue comes to in the form of SupplierID. I don't know if it is okay to keep the SupplierID in the Product table / entity.

The thing is, SupplierID doesn't depend on the ProductID (as it's written within F). I believe that putting it into a 3rd table (as follows) would be fine.

S = {
  Products(ProductID, ProductName, ProductDescription),
  Suppliers(SupplierID, SupplierName, SupplierAddress),
  PS(ProductID, SupplierID)
}

However, would the 3NF be still ok if I had put SupplierID within the Products table (not have the "PS" table at all).

Solution

  • The decomposition in three relations is correctly in 3NF, while if you put SupplierID in the Product table you obtain a relation which is not even in 2NF.

    In fact, in a relation:

    R1(ProductDescription, ProductID, ProductName, SupplierID)

    since the two following two dependencies hold:

     ProductID → ProductName
 ProductID → ProductDescription

    the (only) candidate key of the relation is (ProductID, SupplierID). This means that the two previous dependencies violate the 3NF, since the left side is not a superkey and the right hand side is a non prime attribute.