An Example of Database Normalization
Database normalization is a technique that helps in designing the schema of the database in an optimal manner so as to ensure the previous points.
The core idea of database normalization is to divide the tables into smaller subtables and store pointers to data rather than replicating it.
A Simple Example of Database Dormalization
Let’s assume that we are supposed to store the details of courses and instructors in a university.
Here is what a sample database could look like:
| course code |
course venue |
instructor name |
instructor’s phone number |
| CS101 |
Lecture Hall 20 |
Prof. George |
+1 70114821924 |
CS152 |
Lecture Hall 21 |
Prof. Atkins |
+1 7019272918 |
CS154 |
CS Auditorium |
Prof. George |
+1 7014821924 |
At first, this design seems to be good.
However, issues start to develop once we need to modify information.
For instance, suppose, if Prof. George changed his mobile number.
In such a situation, we will have to make edits in two places.
What if someone just edited the mobile number against CS101, but forgot to edit it for CS154?
This problem, however, can be easily tackled by dividing our table into two simpler tables:
Table 1 (instructor):
- instructor ID
- instructor name
- instructor mobile number
|
⇒ |
| instructor |
| instructor’s ID |
instructor’s name |
instructor’s number |
| 1 |
Prof. George |
+701 6514821924 |
| 2 |
Prof. Atkins |
+1 7019272918 |
|
Table 2 (course):
- course code
- course venue
- instructor ID
|
⇒ |
| course |
| course code |
course venue |
instructor ID |
| CS101 |
Lecture Hall 20 |
1 |
| CS152 |
Lecture Hall 21 |
2 |
| CS154 |
CS Auditorium |
1 |
|