1st Normal Form
1. 1st Normal Form
To begin our database normalization journey, we will begin by introducing the first step of the process -- requiring each table to be in 1st Normal Form.2. Example: maintaining student records
Throughout the remainder of this chapter, a running example will be used. Imagine that a high school wants to maintain student records in a database. A first design of this database results in the creation of a student table. This table includes a PRIMARY KEY, a student name, courses, and a homeroom column. This design presents three potential problems. One problem derives from update errors. Another derives from insertion errors. And a third from deletion errors.3. Example: duplicated data after update
Using this table design, each student has multiple courses listed in a single column. A sample of records is shown here. If Susan wants to take Chemistry instead of Physics, the courses column must be updated.4. Example: duplicated data after update
However, enforcing that a course name appear only once within the column value would need to be handled outside of the data integrity mechanisms of the database.5. Example: duplicated data after update
A school staff member may not have realized that the change to Susan's courses had already been made, resulting in the Chemistry course being added again. In such a case, additional actions must be taken to ensure that this duplicated data does not surface in a student progress report or other downstream processes.6. Example: insertions with column restrictions
Notice the restriction that we placed on the courses column that the column be no greater than 50 characters in length. If Robert wants to pursue an ambitious course load including the three courses listed here as well as French Literature and Computer Science, his entire course list will exceed the column length.7. Example: insertions with column restrictions
It is possible to change the structure of the table to accommodate this change by increasing the column character limit or allowing unlimited characters. However, the fundamental problem comes from placing all courses into a single column.8. Example: data integrity impacted by deleting records
If a student wants to drop a course, deleting a data value from the courses column could fail before the value of the column is updated. If Thomas tries to drop Chemistry, the data value (which includes the remainder of his courses) must be altered.9. Example: data integrity impacted by deleting records
In the worst case, the failed deletion of the string "Chemistry" could cause the record of his other courses to be lost.10. Satisfying 1st Normal Form (1NF)
The issues raised in the previous example can be rectified by ensuring that the student table satisfies 1st Normal Form. The requirement of 1st Normal Form is that table values must be atomic meaning the value cannot be divided into smaller units.11. Example: student table satisfying 1NF
Designing a student table which satisfies 1st Normal Form can be accomplished by making the student id a simple integer rather than a PRIMARY KEY.12. Example: student table satisfying 1NF
Such a change allows the student id to be used in multiple records. A corresponding change involves changing the courses column into a column which represents a single course.13. Example: student table satisfying 1NF
Now, each course is represented by a single record. Altering the table, fixes the issues described previously.14. Example: student table satisfying 1NF
The resulting table has more rows, however, the records are more atomic.15. Example: student table satisfying 1NF
One additional change is also necessary to satisfy 1st Normal Form. The name column includes a value that can further be divided into the student's first and last names.16. Example: student table satisfying 1NF
Our final table definition has 5 total columns and satisfies 1st Normal Form.17. Example: student table satisfying 1NF
A sample of this table's data is shown here. You might feel that there is a lot of duplicated data in this version of our table. And that intuition is correct. Those concerns will be addressed later in this chapter.18. Let's practice!
For now, let's put our knowledge of 1st Normal Form to use.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.