Normalization Exercise Resources

I would say that you are pretty close to be fair - and I think (though it is a personal opinion) that 3NF is more important at an educational level that it is when it comes to the real world. I say this because it is not always logical or practical to obtain perfect 3NF, this could be due to an applications design or the performance of the queries etc etc...

That being said, the best thing I could give you to remember 3NF is a quote a friend once told me (who read it in a book to be fair!) and that is this: Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key. - I think the person who said it originally was Bill Kent.

So on that basis, there are some minor improvements you could make; let's take your city table for example. You have an ID, Title and Post Code; well if you think about it, there are many Post Codes per City, and your design would mean that for each new Post Code you would need to add the ID and Title again - thus breaking the requirements for 3NF. For this you could have an additional table called PostCodes which could contain an ID, Name and City columns, you could then reference your city this way - which is better. The same goes for your Client Info table which has an address field - but it's possible that an address can be shared among many people, as can phone numbers, email addresses etc etc you get the picture!

The best way to remember it is this - if there is a possibility that you have to duplicate information just to satisfy your design - then you either have a special requirement (which is possible - like I said "real world") or your design is wrong - you need to judge it on a case by case basis.

I hope that this helps you.

The page you linked to is, quite simply, wrong.

It makes no sense for a ZIP code to be a primary key in an address table. You would expect to have multiple addresses in the same ZIP code, as you said. ZIP codes cross city, county, and state lines which makes it uniquely poor as a key (postal codes in other countries may not be as problematic but data modelers always have to worry about the worst case). Plus, ZIP codes change over time for an address which is not something you want in your primary keys.

In general, given that addresses tend not to have anything that works as a natural primary key, your address table will need a synthetic primary key (address_id) that has no meaning and simply acts as a primary key. Databases have different ways to generate synthetic primary keys, sequences and auto-incrementing columns are common approaches.