Search code examples
sqldatabase-normalization3nf

Is this database in third normal form/3NF?

I know this is probably a stupid question to some but I'm required to have this database in 3NF but know very little about normalisation as our teacher has not covered it. Could someone give me a simple yes or no answer as to whether it is in 3NF and if it is not, suggest any changes. Thanks.

The Database

Solution

  • Simple answer No. Google transitive dependencies, or even just Google 3NF?

    Why is this the case? Because you have some columns that are dependant on other columns in the same table, where those columns aren't part of the primary key.

    For example, in your Customer Table you have Postcode and Town, but there is a relationship between the two, i.e. you couldn't have a Postcode for Paris without also having a Town of Paris. This is very weak transitive dependence, and most databases would have this without considering it bad practice, but I think this is enough to break 3NF.

    There's another place where it's a little less clear, but I am pretty sure you break 3NF. In your Payment Table you have Deposit Paid, Total Price, Amount Still To Pay, and Fully Paid. There's an argument that given Total Price and Deposit Paid you could determine Amount Still to Pay. There's a very strong argument that you could always determine Fully Paid from the other three "paid" columns.