Skip to content

Instantly share code, notes, and snippets.

@Carmer

Carmer/db_normalization_and_erds.md

Last active March 29, 2016 18:49
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Carmer/f9e060bf1ac30e3ab7b3 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Carmer/f9e060bf1ac30e3ab7b3 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
db_normalization_and_erds.md

Database Normalization Put Simply

  • 1NF - First Normal Form:

    • 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

  • 2NF - Second Normal Form

  • 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

  • 3NF - Third Normal Form

  • 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

Database Modeling links and resources

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment