Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1121
  • Last Modified:

3rd Normal Form and NULLs

Somebody was telling me that if there are NULLs, then data can't be normalized to 3rd Normal Form.
0
lcor
Asked:
lcor
5 Solutions
 
jrb1Commented:
I'm not sure what they meant.  It has been argued that you can't even get to 1st normal form with NULLs.

http://www.dbdebunk.com/page/page/1665942.htm

If you agree with this, then you are correct.  Data with NULLs can't be normalized to 3rd normal form, because to get there you have to meet the conditions for 1st and 2nd normal form first.  However, I tend to disagree.  For example, you have a table called person.  It includes:

personid (primary key)
name
birthdate
deathdate

Now, since you have people in your table that are still living, what do we do with death date?  Do we allow nulls or not?  I say, YES.  If a person has not died, they do not have a value for date of death.  And yet, this table cannot be normalized any further.  So NULLS can be in a 3rd normal table.
0
 
billmercerCommented:
It depends on the definition of all the terms. How do you define NULL? How do you define Data? Whose definition of third normal form are you using?

If you're talking about taking a SQL table that contains NULL values and converting it to 3NF, you can argue that this is impossible, because SQL itself violates some of the basic principles of Codd's RDMBS definition. Strictly speaking it's impossible for a SQL database to be 3NF at all, regardless of the NULLs.
Here's an article with a quick overview of the model and some of the debates...
http://en.wikipedia.org/wiki/Relational_model

If you're using NULL simply to mean that there are some cases for which you don't know the value of a field, then the answer is no. You can create a fully normalized database in 3NF that does not use NULL-as-in-SQL-NULL within the tables, but still allows representation of NULL-as-in-missing-information values. Here's a PDF describing how this is done.
http://web.onetel.com/~hughdarwen/TheThirdManifesto/Missing-info-without-nulls.pdf

Jrb1, the response to your example from Mr Pascal and the Third Manifesto guys would probably be that there is no need for a NULL here, and the correct thing to do would be to store the date of death in a separate table with just the date and a primary key. The reasoning being that the lack of existence of a record in the related table is sufficient to indicate that there's no relevant information available. Their notion of the ultimate database is the so-called "Sixth Normal Form", and has only one field per table, plus a key. Personally, I think it should be carried even further, ultimately concluding in Eighth Normal Form (8NF), wherein each binary digit of data is stored in its own table. ;)

Of course to put this whole debate into perspective, while the ivory tower academics rage on over esoteric relational theory, their secretaries are probably keeping their appointments in flat files.
Some people have to get real work done with the tools available.
0
 
SkipFireCommented:
It's a rule of thumb, and just like many other rules it is subject to interpretation.  If a null can indicate something than it is fine.  Frequently I use NULLs in connection with dates and currencies as a way of indicating no value entered, which is often a valid state.  Just do what works for your situation.  How many people actually do addresses as 3nf?  I almost always have city + state + zip just because it is more convienent and it works for most situations.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
msrobertsCommented:
According to all definitions I have read and used, all attributes of a relation must contain vaules.  nulls are not allowed.  for the person example

personid (primary key)
name
birthdate
deathdate

is not in 1nf  because it would occasionally need a null in deathdate (not all persons are 6 feet under).  To get into 1NF we would need to make another table for deaths

eg.
TABLE: person
personid (primary key)
name
birthdate

TABLE: Dead
personid(primary key)
deathdate

this is in 1NF because all people have a name and all people have a birthdate.  all dead have a deathdate.  no nulls anywhere.  
this is also a bonus in that we only record deathdates for the dead.  and any personid not in dead is still alive.  

nb: this is also in 2NF and 3NF due to it's simplicity.

-- violence is the last refuge of the incompetent -- Issack Asimov
0
 
billmercerCommented:
Pragmatically speaking, rather than declaring NULLs to be anathema, it makes more sense to simply avoid them as much as possible. If it looks like you're going to have a lot of NULLs, you probably need a separate table for the field. But if unknown values are a rare exception, it may be both easier and neater to use NULLs. Why did Codd make systematic handling of NULLs a mandatory requirement for an RDBMS, if he didn't intend that they should ever be used for anything?

In practice, it probably makes more sense to think of normalization as a continuum rather than a binary state. This database is MORE normalized, that one is LESS. Purists probably disagree, but I tend to think in terms of what's  going to be used in the real world. If you're able to convince a user to split their flat file up, consider that a victory, and don't bemoan the fact that it's not in 3NF.

0
 
JankovskyCommented:
There is nothing with nulls within 3rd normal form.
Only violence could be at 5th normal form (may be).
Normalization could just reduce amount of null values in data (particularity influents normalization process), but, as has been said, it's not binary.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now