-
- To satisy 1NF - a table must have only atomic values. This means that no column, in any row, can have multiple values stored inside of it.
- In the following example you will see a table with 2 phone number values in the same column of the same row. This is NOT A GOOD DESIGN and DOES NOT follow 1NF.
Employee ID | First Name | Last Name | Telephone Number |
---|---|---|---|
1 | Jon | Jones | 985-841-3025 |
2 | Jane | Constantine | 219-776-4100 574-403-1659 |
4 | Marha | Fernandinho | 588-008-9773 |
- The following example show's that same table in 1NF.
Employee ID | First Name | Last Name | Telephone Number |
---|---|---|---|
1 | Jon | Jones | 985-841-3025 |
2 | Jane | Constantine | 219-776-4100 |
2 | Jane | Constantine | 574-403-1659 |
4 | Marha | Fernandinho | 588-008-9773 |
- For a table to be in 2NF - the table needs to first satisfy 1NF. It should also have no part of a candidate key (or combination of columns that that would have two distinct rows in a table ) that is dependant on any other part of that key. In the following example - the Company name and Origin contitute a candidate key, but origin is dependant on the company so this table is NOT IN 2NF.
CompanyID | Product | Full Product Name | Origin |
---|---|---|---|
Grant's | BB-time | Grant's down home bb-time garage door | Italy |
Grant's | UltraSteamer | Grant's Ultrasteamer Extraordinaire | Italy |
Crunchetizer | Snap Crackle | The Snap and Crackle Crunchy Clock | USA |
Kayoto | BY-TV-40 | Kayoto Bring Your Truck Vaccum 4gal | Japan |
Juice | Arm Defender | Juice Arm Defender V2.0 | Germany |
Juice | Leg Blocker | Juice Leg Blocker 3.3 | Germany |
- In order to satisfy 2NF we need to make 2 tables by removing origin from the original table.
Company | Product | Full Product Name |
---|---|---|
Grant's | BB-time | Grant's down home bb-time garage door |
Grant's | UltraSteamer | Grant's Ultrasteamer Extraordinaire |
Crunchetizer | Snap Crackle | The Snap and Crackle Crunchy Clock |
Kayoto | BY-TV-40 | Kayoto Bring Your Truck Vaccum 4gal |
Juice | Arm Defender | Juice Arm Defender V2.0 |
Juice | Leg Blocker | Juice Leg Blocker 3.3 |
Company | Origin |
---|---|
Grant's | Italy |
Grant's | Italy |
Crunchetizer | USA |
Kayoto | Japan |
Juice | Germany |
Juice | Germany |
- For a table to be in 3NF it first must be in 2NF. Then the table must also have non-prime attributes, or attribute that is never included in a candidate key, that is not transitively dependent on a key of the table. The following table DOES NOT FOLLOW 3NF because the the non-prime attribute Speaker Date of Birth is transitively dependent on the Conference and Year.
Conference | Year | Speaker | Speaker Date of Birth |
---|---|---|---|
Dinosaur JS | 2016 | Mike Pack | 25 July 1984 |
Rails Conf | 2015 | Sean Griffin | 28 September 1992 |
Nation JS | 2015 | Mike Pack | 25 July 1984 |
Dinosaur JS | 2015 | Charles Winn | 11 May 1979 |
- So, in order to normalize this table into 3NF, we need to create two tables. one with Conference, Year and Speaker - and the other with Speaker and Speaker Date of Birth.
Conference | Year | SpeakerID |
---|---|---|
Dinosaur JS | 2016 | 2 |
Rails Conf | 2015 | 1 |
Nation JS | 2015 | 2 |
Dinosaur JS | 2015 | 3 |
ID | Speaker | Speaker Date of Birth |
---|---|---|
1 | Sean Grunford | 28 September 1992 |
2 | Mike Party | 25 July 1984 |
3 | Charles Winn | 11 May 1979 |
- Sharif Ramadan's intro to 1NF, 2NF and 3NF databases
- Dev Shed - Introduction to Database Normalization
- Intro to normalization. Great blogpost with examples
- ERD's - A tool to help you organize and visualize DB tables
- How to make an ERD
- 1st normal form (1NF) - wikipedia
- 2nd normal form (2NF) - wikipedia
- 3rd normal form (3NF) - wikipedia