View Single Post
  #3 (permalink)  
Old March 26th, 2008, 12:19 AM
woodyz woodyz is offline
Friend of Wrox
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts

tblSongs meets the criteria for the third normal form, which in simple terms is generally accepted as more or less being:
1 - The table is in the second normal form
and 2 - All non primary key fields are dependent on the key, the whole key, and nothing but the key.

So, in tblSongs, SongTitle is dependent only on the primary key, and Artist Id is dependent only on the primary key.

Here is one way that table could break the 3NF:

- SongId
- SongTitle
- ArtistId
- ArtistLastName

In this case, ArtistLastName is not dependent on the primary key, but rather the ArtistId. One anomaly that can occur here is the update anomaly - this would happen where Artist could change their last name and then all records for that artist would have to be changed.

Having duplicate song titles or song titles that differ only in capitalization is not a violation of 3NF. It might be a violation of some business rule, but not of the 3NF. If you need to enforce that SongTitle must be unique and case insensetive then that is a type of constraint you would need to apply.

Your second table is also in 3NF, if we can assume the CustId is the primary key:


Their might be better ways to store this data, but if you only have those 3 fields in the table, and have no need for any other data, there is nothing wrong with a table as you have described.

Again, FullName is fully dependent on the primary key, and city is fully dependent on the primary key. In other words, FullName stays the same if you have to change the customer's City, and the city stays the same if the customer changes their FullName. Obviously, in "practical" use we wouldn't typically story the FullName, but rather the FirstName and LastName, and perhaps a middle name or initial and etc... each in their own fields, but that has nothing to do with 3NF, or any normal form, for that matter. That has to do with your domain, and what information you need for the Customer entity. There are lots of considerations about where you might want to store address info for the customer, but that is outside the scope of this discussion.

And here is a version of the table that would break 3NF:

Now we are storing information about the city in the customer table - where it obviously doesn't belong.

The essential goal of 3NF tables is to eliminate update, insertion, and deletion anomalies. In this case, you would have an update problem every time the CityMayor changes (which could happen with each election or recall), and you would have a deletion issue when you delete a customer who is the only customer in a certain city - now you have no way to look up the mayor for that city. Of course, no one would want to store their data this way... or so it would seem, but most of us old-timers have seen tables that have these problems.

So there you go.

Woody Z
How to use a forum to help solve problems
My blog... please visit
Reply With Quote