1 of 22

1st, 2nd & 3rd Normal Form

A beginner's guide

2 of 22

1st Normal Form

All rows unique and atomic

3 of 22

First Normal Form

  • All rows must be unique (no duplicate rows)
  • Each cell must only contain a single value (not a list)
  • Each value should be non divisible (can't be split down further)

4 of 22

Problem 1 - All rows must be uniquely identifiable

Customer Name

Order

Bob Jones

Burger, Fries, Coke

Fred Jones

Nuggets, Lemonade, Fries

Bob Jones

Burger, Fries, Coke

5 of 22

Problem 1 - All rows must be uniquely identifiable

​

Identical Rows

Customer Name

Order

Bob Jones

Burger, Fries, Coke

Fred Jones

Nuggets, Lemonade, Fries

Bob Jones

Burger, Fries, Coke

6 of 22

Solution - add an order ID as a primary key

OrderID

Customer Name

Order

1

Bob Jones

Burger, Fries, Coke

2

Fred Jones

Nuggets, Lemonade, Fries

3

Bob Jones

Burger, Fries, Coke

7 of 22

Problem 2 - Each cell must only contain a single value

OrderID

Customer Name

Order

1

Bob Jones

Burger, Fries, Coke

2

Fred Jones

Nuggets, Lemonade, Fries

3

Bob Jones

Burger, Fries, Coke

8 of 22

Problem 2 - Each cell must only contain a single value

Multiple Values!

OrderID

Customer Name

Order

1

Bob Jones

Burger, Fries, Coke

2

Fred Jones

Nuggets, Lemonade, Fries

3

Bob Jones

Burger, Fries, Coke

9 of 22

Solution Create a separate table with order items

OrderID

Customer Name

1

Bob Jones

2

Fred Jones

3

Bob Jones

OrderID

Item

1

Burger

1

Fries

1

Coke

2

Nuggets

2

Lemonade

2

Fries

3

Burger

3

Fries

3

Coke

10 of 22

Problem 3 - All data must be atomic (non-divisible)

OrderID

Customer Name

1

Bob Jones

2

Fred Jones

3

Bob Jones

OrderID

Item

1

Burger

1

Fries

1

Coke

2

Nuggets

2

Lemonade

2

Fries

3

Burger

3

Fries

3

Coke

11 of 22

Problem 3 - All data must be atomic (non-divisible)

OrderID

Customer Name

1

Bob Jones

2

Fred Jones

3

Bob Jones

OrderID

Item

1

Burger

1

Fries

1

Coke

2

Nuggets

2

Lemonade

2

Fries

3

Burger

3

Fries

3

Coke

12 of 22

Solution

OrderID

First Name

Last Name

1

Bob

Jones

2

Fred

Jones

3

Bob

Jones

OrderID

Item

1

Burger

1

Fries

1

Coke

2

Nuggets

2

Lemonade

2

Fries

3

Burger

3

Fries

3

Coke

13 of 22

2nd Normal Form

No Partial Dependencies

14 of 22

Second Normal Form

  • Database must be in First Normal Form
  • Non partial dependency - All non-prime attributes should be fully functionally dependent on the candidate key

15 of 22

Example - No partial dependency

This table is not in 2NF because the course Fee is only dependent on the primary key to determine the course fee, you can do it with just one column (course ID)

Student ID

Course ID

Course Fee

1

1

500

1

2

1000

2

4

200

2

3

750

3

5

1000

3

3

750

Composite Key

16 of 22

Example - No partial dependency

This table is not in 2NF because the course Fee is only dependent on the primary key to determine the course fee, you can do it with just one column (course ID)

Student ID

Course ID

Course Fee

1

1

500

1

2

1000

2

4

200

2

3

750

3

5

1000

3

3

750

Composite Key

17 of 22

Example - No partial dependency

Student ID

Course ID

1

1

1

2

2

1

2

3

3

5

3

3

Composite Key

Course ID

Course Fee

1

500

2

1000

3

750

4

200

5

1000

6

300

Student Courses

Course Fees

18 of 22

3rd Normal Form

No Transitive Dependencies

19 of 22

Third Normal Form

  • Database must be in First & Second Normal Form
  • No transitive dependency - All fields must only be determinable by the primary/composite key, not by other keys

20 of 22

Third Normal Form

Tournament Name

Year

Winner

Winner's DOB

Indiana Invitational

1998

Al Fredrickson

21 July 1975

Cleveland Open

1999

Bob Albertson

28 September 1968

Des Moines Masters

1999

Al Fredrickson

21 July 1975

Indiana Invitational

1999

Chip Masterson

14 March 1977

Composite Key

Table Source: https://en.wikipedia.org/wiki/Third_normal_form

21 of 22

Third Normal Form

Tournament Name

Year

Winner

Winner's DOB

Indiana Invitational

1998

Al Fredrickson

21 July 1975

Cleveland Open

1999

Bob Albertson

28 September 1968

Des Moines Masters

1999

Al Fredrickson

21 July 1975

Indiana Invitational

1999

Chip Masterson

14 March 1977

Composite Key

Table Source: https://en.wikipedia.org/wiki/Third_normal_form

22 of 22

Third Normal Form

Tournament

Year

Winner

Indiana Invitational

1998

Al Fredrickson

Cleveland Open

1999

Bob Albertson

Des Moines Masters

1999

Al Fredrickson

Indiana Invitational

1999

Chip Masterson

Winner

Date of birth

Chip Masterson

14 March 1977

Al Fredrickson

21 July 1975

Bob Albertson

28 September 1968

​

Tournament Winners

Winners DOBs

Table Source: https://en.wikipedia.org/wiki/Third_normal_form

Composite Key