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 Normalization
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 |
|
A policeman pulls a man over for speeding and
asks him to get out of the car.
After looking the man over he says,
“Sir, I could not help but notice your eyes are bloodshot.
Have you been drinking?”
The man gets really indignant and says,
“Officer, I could not help but notice your eyes are glazed.
Have you been eating doughnuts?”
|