Which of these definitions explain 1NF?

Question

I found it vague when i'm trying to look for the definition of 1NF in google.

• Some of the sites like this one, says the table is in 1st normal form when it doesn't have any repetitive set of columns.

• Some others (most of them) says there shouldn't be multiple values of the same domain exist in the same column.

• and some of them says, all tables should have a primary key but some others doesn't talk about primary key at all !

can someone explain this for me ?

Answer 1

The simplest explanation I have found is this modified definition copied from here:

1st Normal Form Definition

A database is in first normal form if it satisfies the following conditions:

1) Contains only atomic values

2) There are no repeating groups

Answer 2

A relation is in first normal form if it has the property that none of its domains has elements which are themselves sets.

From E. F. Codd (Oct 1972). "Further normalization of the database relational model"

This really gets down to what it is about, but the guy who invented the relational database model.

When something is in the first normal form, there are no columns which themselves contain sets of data.

The wikipedia article on first normal form demonstrates this with a denormalized table:

Example1:

Customer
Customer ID | First Name | Surname   | Telephone Number
123         | Robert     | Ingram    | 555-861-2025
456         | Jane       | Wright    | 555-403-1659, 555-776-4100
789         | Maria      | Fernandez | 555-808-9633

This table is denormalized because Jane has a telephone number that is a set. Writing the table thus is still in violation of 1NF.

Example2:

Customer
Customer ID | First Name | Surname   | Telephone Number
123         | Robert     | Ingram    | 555-861-2025
456         | Jane       | Wright    | 555-403-1659
456         | Jane       | Wright    | 555-776-4100
789         | Maria      | Fernandez | 555-808-9633

The proper way to normalize the table is to break it out into two tables.

Example3:

Customer
Customer ID | First Name | Surname
123         | Robert     | Ingram
456         | Jane       | Wright
789         | Maria      | Fernandez

Phone
Customer ID | Telephone Number
123         | 555-861-2025
456         | 555-403-1659
456         | 555-776-4100
789         | 555-808-9633

---

Another way of looking at 1NF is as defined by Chris Date (from Wikipedia):

1. There's no top-to-bottom ordering to the rows.
2. There's no left-to-right ordering to the columns.
3. There are no duplicate rows.
4. Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
5. All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].

Example2 lacks a unique key which is in violation of rule 3. Example1 violates rule 4 in that the telephone number contains multiple values.

Only Example3 fills all those requirements.

Further reading:

• Simple Guide to Five Normal Forms in Relational Database Theory