0

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 ?

Shnd
  • 1,846
  • 19
  • 35
  • [Normalization in database management system](https://stackoverflow.com/a/40640962/3404097) – philipxy Jul 17 '17 at 10:06
  • Does this answer your question? [Normalization in database management system](https://stackoverflow.com/questions/40623169/normalization-in-database-management-system) – philipxy Feb 08 '22 at 01:38

2 Answers2

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:

  • Thank you @MichaelT, i've read the wikipedia 1NF article, but it is not as explicit as it seems. look at your example 2 table. The article doesn't mentioned directly that this is 1NF or not ! It just says you should break it in 2 tables. – Shnd May 12 '14 at 05:22
  • When Codd said "sets" there he meant "relations"--as evidenced by "themselves" & his presentation of "normalization"--the sets are sets of tuples with associated headings. But his writing & math were sloppy. PS The Wikipedia article is not much use because it is confused in typical ways. All those properties are properties of relations by definition, so they don't disguish 1NF relations. Example 2 certainly has a CK (namely all columns) & no duplicates, like every relation. Example 1 doesn't have "multiple values" for a "row-and-column intersection" if it is an example of a relation. – philipxy Oct 05 '19 at 01:32
0

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

armadadrive
  • 963
  • 2
  • 11
  • 42
  • actually it's not that simple. just imagine that if you can find just 2 columns with same values for couple of rows, then they should have their own table. then if you want to do this for all of your tables with thousands of rows, it can be really problematic. – Shnd May 11 '14 at 20:05
  • @Shnd That's not what ["repeating groups"](https://stackoverflow.com/a/26952821/3404097) means. Not that it has a single clear meaning. Codd 1970 says "attribute and repeating group in present database terminology are roughly analogous to simple domain and nonsimple domain, respectively"--meaning, non-relational doman & relational domain. (So form Codd's point of view this answer's 1 & 2 say the same thing.) – philipxy Oct 05 '19 at 01:49