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
- First Normal Form (1NF)
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
| StudentID | FirstName | LastName | Tuition Amt1 | Tuition Amt2 | Tuition Amt3 |
|---|---|---|---|---|---|
| 1000 | Susie | Baker | 1,500.00 | 1,500.00 | 1,500.00 |
| 1001 | Daniel | Bishop | 1,575.00 | 1,575.00 | 1,575.00 |
| 1002 | John | Brown | 1,475.00 | 1,475.00 | 1,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
| StudentID | FirstName | LastName |
|---|---|---|
| 1000 | Susie | Baker |
| 1001 | Daniel | Bishop |
| 1002 | John | Brown |
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
| PayID | StudentID | TuitionAmt |
|---|---|---|
| 252 | 1000 | 1,500.00 |
| 253 | 1001 | 1,575.00 |
| 254 | 1002 | 1,475.00 |
| 255 | 1000 | 1,500.00 |
| 256 | 1001 | 1,575.00 |
| 257 | 1002 | 1,475.00 |
| 258 | 1000 | 1,500.00 |
| 259 | 1001 | 1,575.00 |
| 260 | 1002 | 1,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