Database Design – Normalization to 3NF

2NF: Remove Partial Dependencies

R = {A, B, C, D, E, F, G, H, I, J} includes partial dependencies.

D and E depend only on A, F depends only on B, G, H, I and J don't depend on the key (directly) at all.

R0 = {A, B, C}

R1 = {A, D, E, I, J}

R2 = {B, F, G, H}

R0, R1, and R2 contain no partial dependencies (or repeating groups) so they are 2NF. However R1 and R2 are still an issue, because they contain transitive dependencies.

3NF: Remove Transitive Dependencies

I and J depend on D, not on the key of R1. Therefore you need to further normalize R1 as follows:

R1 = {A, D, E, I, J}

R1a = {A, D, E}

R1b = {D, I, J}

Similarly, G and H depend only on F so R2 must be decomposed as follows:

R2 = {B, F, G, H}

R2a = {B, F}

R2b = {F, G, H}

Now all of your remaining relations (R0, R1a, R1b, R2a, R2b) are devoid of repeating groups, partial dependencies and transitive dependencies. That means your relations are in 3NF.

When you are looking at an relation that hasn't been normalized and a series of dependencies, you can often normalize by inspection just by recognizing what your primary keys are going to be. Any attribute or combination of attributes that functionally determine other attributes are going to end up as primary keys. Once you've got your primary keys defined, you just need to figure out which non-key attributes go with each key. This is obvious from the statement of what your functional dependencies are.

You need to learn the difference between repetition and redundancy. Sometimes a field value is repeated coincidentally. This kind of repetition cannot be normalized out.

Normalization is about studying the functional dependencies between key and non-key elements. It is not about putting everything that might occur twice into a table with a surrogate key.

For example, you say that a phone number may be used by multiple companies and have modeled phone number as an independent table. This would prevent update anomalies if when one company changed its number all of the other companies using that number changed theirs at the same time and in the same way. Does any of that actually make business sense? It doesn't to me.

Also, normalizing geography into a hierarchy is a questionable proposition. However, you haven't even normalized into a hierarchy. Is region ID not determined by country ID? If so, your LOCATION table is not 3NF. Similarly, are there postcodes that bridge states? I don't know the Australian system well enough, but I know that in Canada, the US and the UK this doesn't happen.

You've designed everything like its a star schema and company is the fact. Facts in star schemas are usually transactions, not static entities. Transactions don't tend to change their properties, because they usually represent something that actually happened and one doesn't generally go back in time to change history. Static entities live for a long time in your data (maybe forever) and change their properties fairly often. Star schema is not a good, efficient model for this type of data.

What you should do is run queries on the frequency distributions of each column and on combinations of columns that you think may be keys combined with columns that you know aren't keys. This will help you test your assumptions about which columns are truly able to act as keys. Then you should temper those findings with some common sense around what could potentially happen to your data in terms of changes to column values. Once you know your actual functional dependencies found in your data, then follow the relatively mechanical process of moving your relation to 3NF.