View Single Post
Old March 26th, 2008, 11:37 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

quote:Originally posted by williamlove
I am leaning towards this table not being in 2NF and therefore not in 3NF. woodyz do you now concur, or upon further review do you still disagree?
I do not concur. If we can agree that the primary key is the CustId then we are not considering 2NF at all. 2NF deals with composite primary keys. If we can agree on that, then lets see where we can get.

quote:Originally posted by williamloveI think the ambiguity comes from the interpretation of what is actually happening if the FullName is changed. If it results from the same individual changing their name, then the city stays the same. In this case City is only dependent on the CustID and it does not violate 2NF.

But if the record for a given CustID is changed so that the same CustID is used, but a different person with a different name is used, then in general the city would have to change. So in any case other than a person changing their name, City is dependent on FullName and this violates 2NF.
Let's agree that the Customer table contains customer entities. In that case, a record in the Customer table always refers to the same customer, even if they have changed their name, or moved to another city. In this case you would never use the same number to later refer to a different customer. By definition the CustId is the unique identifier for a customer entity, and the FullName is fully dependent on the primary key. I hope we can agree on that.

So, now comes the part where I will change my earlier statement. I think that 3NF is not being followed in the case of the city. City does not belong in this table - we want a City table for storing the city names and refer to the city from the customer table via a foreign key. This is not unreasonable from an academic point of view, and in common practice, customer address info is often stored in a separate table (there are many things to consider about how we will store address info so we won't cover that here). However, even dealing with this as just a city and not considering the rest of the address as in your simplified example, this will still be a 3NF problem if we expect a City itself to need to have it's name changed, or if we want to generate reports based on how many customers are in each of the cities we are interested in. In the first case, for example, if we have customers in Boston, and Boston decides to change its name to New Boston, then we have to change the city name in every record that currently has the city set to "Boston". In the second case, if we want to do a monthly report of the number of customers in each of the cities in our database - we could never have a city with "0" customers. A city does not exist in our database if we don't have a customer in that city. SO... For that reason, we could say that the table is NOT in 3NF until we get the city out of there and use a foreign key in its place.

So I have changed my mind. The city presents a problem.

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