Normalization


Normalized data is data that is structured for optimal storage and use within a program.

Normalization eliminates redundancy by decomposing a table into two or more tables in higher normal form.

  • Most database designers will at least attempt to get data to the third normal form
    • First Normal Form (1NF)
      • eliminates redundant information in individual tables
      • Each set of related data will be stored in a dedicated table
      • Each table of related information will have a primary key assigned
    • Second Normal Form (2NF)
      • related information that is applicable to multiple tables will have its own table and will be associated through the use of a foreign key
    • Third Normal Form (3NF)
      • eliminates fields that do not depend on a key
      • only some designs go to the 3NF
    • fourth and fifth normal forms exist, but are less practical and rarely used

Not Normalized (Denormalized)

  • This first table contains student data that is not normalized
  • is a column for every tuition payment, each denoted with a tuition amount and number
  • This design is not very functional
    • requires every tuition payment that ever needs to be created to have a field name
StudentIDFirstNameLastNameTuition

Amt1
Tuition

Amt2
Tuition

Amt3
1000SusieBaker1,500.001,500.001,500.00
1001DanielBishop1,575.001,575.001,575.00
1002JohnBrown1,475.001,475.001,475.00

First Normal Form (1NF)

  • next table shows how we would normalize student data to the 1NF by creating a single student record in a table
  • We eliminate redundant data (the tuition payments) and store that related data in a separate table
StudentIDFirstNameLastName
1000SusieBaker
1001DanielBishop
1002JohnBrown

Second Normal Form (2NF)

  • this last table shows how we would normalize tuition payment data to the 2NF
  • we now use a dedicated PayID
    • rather than repeating the tuition payment amounts
  • the StudentID is how we identify the student in this second table
    • makes it the primary key
  • Once data has been normalized, relationships between the data can then be established
PayIDStudentIDTuitionAmt
25210001,500.00
25310011,575.00
25410021,475.00
25510001,500.00
25610011,575.00
25710021,475.00
25810001,500.00
25910011,575.00
26010021,475.00
  • we have different data sets that use the same information, like StudentID, FirstName, and LastName
    • Having that data be re-entered is what we mean by redundant data
  • In our new normalized model, we now have defined tables
  • Each of the tables contain student information:
    • one student table
    • one for tuition payments
    • another for data that can be associated to their StudentID
  • Now that the student data is stored in just one place,
    • we are likely to see fewer errors
    • and data accuracy will increase