7

I asked a similar question on SO and was advised to ask the type of question here. This is for a course on relational databases. A sample problem asks "how come every table in a relational database should be in First Normal Form" my first problem with this is isn't 3NF considered better so wouldn't the statement be false? My second problem with this is on SO people said that First Normal Form is good because it eliminates redundancy. I don't see how it does this because 1)all values are atomic 2)there is a primary key - neither of which eliminates redundancy.

I guess another way to ask it is why is it important for values to be atomic?

4 Answers 4

10

The primary importance of first normal form is not that it eliminates redundancy, but rather, it's that it eliminates repeating groups.

Instead of having multiple columns of the same kind of data in a record, (0NF) you remove the repeated information into a separate relation and represent them as rows. This is what constitutes 1NF.

Tables that have columns like: phone_1, phone_2, phone_3 or that contain list-oriented data like: 212-555-1212, 212-555-1234, 416-967-1111 violate 1NF.

1NF is important because it is much more flexible than 0NF while being much easier to use when inserting, updating and reading data. This is because every type of data element (e.g. customer phone number) has exactly one column in which to find it and that column has only one piece of data for each record. This means that you can use simple SQL statements to read or write individual data elements without having to parse delimited strings or use constructions like: where phone_1=@Number or phone_2=@Number or phone_3=@Number and so forth.

Regarding 1NF vs 3NF, the normal forms are cumulative. A table in 3NF is also in 1NF, so it is just as true to say that "Every table in a relational database should be in 1NF" as it is to say that "Every table in a relational database should be in 3NF." I would say both of these are true, but I would add "unless you have a really good, well considered reason to denormalize".

2

"I guess another way to ask it is why is it important for values to be atomic?"

I'm currently working with applications that have comma-separated values in varchar/text fields, and it's a pain in the ass. Among other things, in the application you have to do all kids of silly string splitting and converting to get the data on the correct form, and on the DB side you can't use indexes on the field, as all searches have to be on the form WHERE mycolumn LIKE '%search%'

There are other reasons as well, but those two come to mind first.

0

1NF does eliminate redundancy, and it also helps reduce or eliminate data consistency issues.

Assume you have a table of Invoices, which contains payment data as well as customer data.

The customer data is information like Name,Address,PhoneNumber, etc. If you duplicate this data on every row...

  • The data is presumably redundant (since you have the same details in every Invoice record for the same Customer
  • The data could potentially become inconsistent. If I have two different Address values for the same Customer, which one is correct?
  • The data is less logically/intuitively organized. If you want to send a customer a promotion, it would make more sense to check an Address table than to check the Invoice table for a current address.
0

First normal form means eliminating domains with relations as members - in other words eliminate columns which allow tables as values.

In the context of 1NF "atomic" just means any type which is not a relation because such values are atomic with regard to relational algebra. So saying values should be atomic is the same as saying relations should not have relations as values.

First normal form is necessary because relational data languages (in practice SQL) does not support nested tables. Nested tables also complicate the data model needlessly, since the same information can be expressed through foreign keys.

First normal form does not eliminate redundancy in data though. The higher normal forms eliminate different forms of redundancy, but this is not the case for 1NF.

The normal forms builds on each other, i.e. tables have to be in 1NF before they can be in 2NF and so on. So 3NF implies the database is also in 1NF.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.