Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 25th, 2008, 09:23 PM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
Default Academic vs practical design question

Consider two tables:

tblSongs
-SongID (primary key)
-SongTitle
-ArtistID (foreign key)

tblArtists
-ArtistID
-ArtistName

Is tblSongs normalized? In 3NF? It is possible to wind up with this anomoly:

SongID SongTitle ArtistID
131 Lifes Good 5387
221 lifes Good 5387
258 Lifes Good 5387

To cast more light on my question consider a customer table where the social security number is not allowed so the primary key is some arbitrary unique value

tlbCustomers
-CustID
-FullName
-City

The same question applies...is this normalized? If it is not, you'd have to have tables for the FullName and City, but that would be a lot of work and unwieldy. What would an academic do? A practical designer? I'm very curious.


Reply With Quote
  #2 (permalink)  
Old March 25th, 2008, 10:01 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Yes I would say tblSongs is in 3NF since both SongTitle and ArtistID don't rely on one another to be related to SongID.

Now if you were to add a forth column in there, say albumnTitle, this would break 3NF because albumnTitle would become dependant on SongID but only because it is related to an artist which is then in turn related to a SongID.

First I would say no, your customer table is not in 3NF because the value of City relies on who the person is which in turn relates the value to CustID so your assumption is correct, you would need to break out a table for city. I can't speak for an academic but normally what I do is break a "Person" table out into at least 3 tables:

Personal Information (Name, SSN, DOB)
AddressInformation(Shipping Information & Billing Information) <--Each address would be 1 row within the table
ContactInformation(PhoneNumbers, Email, etc)

Also I will normally have ancilliary lookup tables for States, Gender, etc.

Make sense?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========
Reply With Quote
  #3 (permalink)  
Old March 26th, 2008, 12:19 AM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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:

tlbSongs
- 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:

tlbCustomers
-CustID
-FullName
-City

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:
tlbCustomers
-CustID
-FullName
-City
-CityMayor

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
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit
Reply With Quote
  #4 (permalink)  
Old March 26th, 2008, 12:50 AM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
Default

My customer example is meant to be almost trivial to make it easy to discuss....

It seems the two replies I got disagree about whether

tblCustomers
-CustID (prim key)
-FullName
-City

is in 3NF. I have really had to use my brain to evaluate this (which is the point of course).

From the first reply: “no, your customer table is not in 3NF because the value of City relies on who the person is”

From the second reply: “Your second table is also in 3NF… 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.”

I 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.

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?
Reply With Quote
  #5 (permalink)  
Old March 26th, 2008, 06:44 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

E.F. Codd would say that the table tblCustomers does not even meet 1NF since it lacks atmoicity.

FullName should be FirstName and LastName,

possibly Salutation, FirstName, MiddleNameorInitial, LastName, Suffix



mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #6 (permalink)  
Old March 26th, 2008, 06:45 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Or "atomicity." D-ooh!

mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #7 (permalink)  
Old March 26th, 2008, 10:56 AM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by mmcdonal
 E.F. Codd would say that the table tblCustomers does not even meet 1NF since it lacks atmoicity.

FullName should be FirstName and LastName,

possibly Salutation, FirstName, MiddleNameorInitial, LastName, Suffix



mmcdonal

Look it up at: http://wrox.books24x7.com
I don't know what Codd would say, but I disagree. If the full name is sufficient for the abstraction of what the application expects from a customer entity, then it is atomic. For example, would this be an atomic StreetAddress field:
"1234 Grand Ave."
Or would you have to break it into AddressNumber, AddressStreetName, and AddressStreetExtenstion?

In other words, it depends on the meaning of the data, and that cannot be determined by merely looking at the data in a field, but requires knowing the domain of the application.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit
Reply With Quote
  #8 (permalink)  
Old March 26th, 2008, 11:01 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You are correct in pointing out my incorrect assumption. I assumed that since it was a customer table, that a last name index would be required.

mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #9 (permalink)  
Old March 26th, 2008, 11:07 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

I think this is one of those topics where you are bound to have varying degress of opinions but I am always up for a well educated discussion.

While Woody makes the argument that City is reliant on the Primary Key value in the table I would tend to disagree. Can't you make the argument that its a transitive relationship because the city (presumably the city where the person lives) relies on the FullName which then relies on the Primary Key?

Further I think the overall question is from a practical standpoint (e.g. application development) what is functional. I am sure Codd would utterly rip me apart if he saw some of my Database designs but there comes a point that if you normalize to far your application preformance can suffer because of the Joins necessary to get a "complete" record.

-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========
Reply With Quote
  #10 (permalink)  
Old March 26th, 2008, 11:37 AM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
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:
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
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
design question androoo Classic ASP Databases 1 November 13th, 2004 09:41 AM
design question androoo General .NET 1 November 13th, 2004 09:40 AM
design question androoo .NET Web Services 1 November 13th, 2004 09:39 AM
Design Question flyin General .NET 4 July 21st, 2004 03:59 PM
Design question? qadeer05 SQL Server 2000 4 May 3rd, 2004 10:17 AM



All times are GMT -4. The time now is 06:57 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.