Database Normalization
What is ‘normalization’?
Database normalization, or just normalization
• A process used for data modelling or database creation, where you organize your data and tables
so it can be added and updated efficiently.
• Done manually @ design time (no ‘click a button and do it’!)
Why Normalize a Database?
Make the database more efficient
Prevent the same data from being stored in more than one place
Prevent updates being made to some data but not others
Prevent data not being deleted when it is supposed to be, or from data being lost when it is not
supposed to be Ensure the data is accurate
Reduce the storage space that a database takes up
Ensure the queries on a database run as fast as possible
Without normalization, running the database can be slow and is almost guaranteed to be hard
to manage, update and keep accurate
Example 1
• Consider a student database
• You want to keep track of:
• The student name
• Program (Degree) Name
• Classes(s) taken
• Fees paid
• You could envision a simple spreadsheet (table) for holding that information …
Student ID Student Name Fees Paid Program Name Class 1 Class 2 Class 3
1 John Smith 200 Economics Economics 1 Biology 1
2 Maria Griffin 500 Computer Science Biology 1 Business Intro Programming 2
3 Susan Johnson 400 Medicine Biology 2
4 Matt Long 850 Dentistry
Example 1
• This creates some immediate problems
Student ID Student Name Fees Paid Program Name Class 1 Class 2 Class 3
1 John Smith 200 Economics Economics 1 Biology 1
2 Maria Griffin 500 Computer Science Biology 1 Business Intro Programming 2
3 Susan Johnson 400 Medicine Biology 2
4 Matt Long 850 Dentistry
• What happens when you want to add a new student, but their program is not known?
Student ID Student Name Fees Paid Program Name Class 1 Class 2 Class 3
….
5 Jim Kirk 0 ?
• You are adding incomplete data – which can cause problems when analyzing the data
• This is an ‘INSERT’ anomaly. You can’t add a student without also adding their classes
Example 2
• What if we want to modify some data?
Student ID Student Name Fees Paid Program Name Class 1 Class 2 Class 3
1 John Smith 200 Economics Economics 1 Biology 1
2 Maria Griffin 500 Computer Science Biology 1 Business Intro Programming 2
3 Susan Johnson 400 Medicine Biology 2
4 Matt Long 850 Dentistry
• We rename Biology 1 to ‘Intro to Biology’?
• We would have to search and locate EVERY instance of Biology 1 and make sure we update it
• This would be an ‘UPDATE’ anomaly. You can make a change, but fail to make ALL the necessary
changes
• It would be FAR better if we could only make ONE change, and cover everyone taking Biology
Example 3
• What if we want to remove some data?
Student ID Student Name Fees Paid Program Name Class 1 Class 2 Class 3
Student ID Student Name Fees Paid Program Name Class 1 Class 2 Class 3
1 John Smith 200 Economics Economics 1 Biology 1
1 John Smith 200 Economics Economics 1 Biology 1
2 Maria Griffin 500 Computer Science Biology 1 Business Intro Programming 2
2 Maria Griffin 500 Computer Science Biology 1 Business Intro Programming 2
3 Susan Johnson 400 Medicine Biology 2
4 Matt Long 850 Dentistry
4 Matt Long 850 Dentistry
• Susan Johnson drops out, and we delete the record …
• This would remove ALL traces of Biology 2
• This would be a ‘DELETE’ anomaly. Deleting one thing results in other data (that you want to keep)
being deleted
• You would want to delete the student record, but not wipe out the existence of the Class!!
Other issues
Student ID Student Name Fees Paid Program Name Class 1 Class 2 Class 3
1 John Smith 200 Economics Economics 1 Biology 1
2 Maria Griffin 500 Computer Science Biology 1 Business Intro Programming 2
3 Susan Johnson 400 Medicine Biology 2
4 Matt Long 850 Dentistry
What if they take 4 classes?
What if they switch programs?
What if you want to add a new program?
Add a new class?
…
Normalization as a design strategy …
● Introduced alongside the relational model in the early 70’s by Codd, et al.
○ Many different normal forms: UNF, 1NF, 2NF, 3NF, BCNF, 4NF, ETNF, 5NF, DKNF, 6NF.
○ Subtle differences between each.
Basic tenets:
• Independence of data tables
• Each Normalization Form (1NF, 2NF, 3NF) has increasing levels of independence
• There are more levels … but as a matter of practicality, most will stop @ 3NF
First Normal Form …
Student Name Fees Paid Date of Birth Program Name Class 1 Class 2 Class 3 Class 4 Teacher Name
Economics 1 Biology 1 James
John Smith 1800 04-Aug-91 Economics
(Business) (Science) Peterson
Computer Biology 1 Business Intro Programming 2 James
Maria Griffin 1500 10-Sep-92
Science (Science) (Business) (IT) Peterson
Biology 2
Susan Johnson 2000 13-Jan-91 Medicine Sarah Francis
(Science)
Matt Long 1400 25-Apr-92 Dentistry Shane Cobson
Does the combination of all columns make a unique row every single time?)No redundancy)
No. You could have people with the same name, classes etc. – might be rare, but could happen
What field can be used to uniquely identify the row?
Is this the student name? No. Class? No, this isn’t unique either. So we would need a Primary Key!
Updated design
Primary PK ID
Key 1NF Schema
Name
Fees Paid
DoB
Program Name
Class 1
Class 2
Class 3
Class 4
Teacher Name
2nd Normal Form
The primary key is student ID, which represents the student. Let’s look at each column:
The rule of second normal form on a
student name: Yes, this is dependent on the primary key. A different student ID
database can be described as: means a different student name.
fees paid: Yes, this is dependent on the primary key. Each fees paid value is for a
1. Fulfil the requirements of first normal single student.
form date of birth: Yes, it’s specific to that student.
2. Each non-key attribute must be class 1: No, this column is not dependent on the student. More than one student
functionally dependent on the primary can be enrolled in one class. If the class is removed, the student stays etc.
key class 2: As above, more than one class is allowed.
What does this mean? class 3: No, same rule as class 2.
class 4: No, same rule as class 2
teacher name: No, the teacher name is not dependent on the student.
program name: No, the program name is not dependent on the student.
We have a mix of Yes and No here. Some fields are dependent on the student ID, and
others are not.
Multiple Tables for 2NF
class ID class name
1 Economics 1 (Business)
2 Biology 1 (Science)
3 Business Intro (Business)
student ID student name fees paid date of birth 4 Programming 2 (IT)
5 Biology 2 (Science)
1 John Smith 200 04-Aug-91
teacher ID teacher name
1 James Peterson
2 Maria Griffin 400 10-Sep-92
2 Sarah Francis
3 Shane Cobson
3 Susan Johnson 800 13-Jan-91
program ID program name
4 Matt Long 600 25-Apr-92 1 Computer Science
2 Dentistry
3 Economics
But how do we connect all the data? 4 Medicine
Foreign Keys
class ID class name
Foreign key 1 Economics 1 (Business)
2 Biology 1 (Science)
3 Business Intro (Business)
stud prog 4 Programming 2 (IT)
ent ram student name fees paid date of birth
5 Biology 2 (Science)
ID ID
teacher ID teacher name
1 3 John Smith 200 04-Aug-91
1 James Peterson
2 Sarah Francis
2 1 Maria Griffin 400 10-Sep-92 3 Shane Cobson
3 4 Susan Johnson 800 13-Jan-91
program ID program name
1 Computer Science
4 2 Matt Long 600 25-Apr-92 2 Dentistry
3 Economics
4 Medicine
How to manage the other tables?
Questions: What about the program table?
How are teachers related to others?
Does a program have many students, or does
- A student can have one teacher for all classes a student have many programs?
- A subject could have a single teacher that
teaches that specific class
- Program could have a teacher that teaches all BOTH!
classes for the program
- Multiple teachers could teach a particular class This is a many-to-many relationship
DOES A TEACHER HAVE MANY CLASSES, or DOES
A CLASS HAVE MANY TEACHERS
Definitely the first statement is true
Side note: Data and mandatory fields
Consider this … (student_name)
PK First Name Last_Name Middle_Name Nickname
We separate out things like Courses, Addresses into separate tables – but what about just a student's name?
PK First Name Last_Name Middle_Name Nickname
1 James Kirk Tiberius Jim Valid
PK First Name Last_Name Middle_Name Nickname
1 Spock Valid?
Yes, it’s valid. You can’t ALWAYS have non-null values, no matter how
much you break down the tables
3NF
Third normal form is the final stage of the
most common normalization process. The rule A
for this is:
A determines B
• Fulfils the requirements of second
normal form B
• Has no transitive functional B determines C
dependency
C
What does this mean? What is a transitive
functional dependency?
It means that every attribute that is not the A B
primary key must depend on the primary key
and the primary key only. A determines B B determines C
B C
Example
Cust_I Name Acct_Nu Bank_co Bank Bank Bank Name
D mber de Name Code
1 John Smith 200004 400 Chase 303 BoA
2 Maria Griffin 545151 501 ESL 400 Chase
3 Susan Johnson 353453 400 Chase 501 ESL
4 Matt Long 784234 303 BoA
Cust_ID Name Acct_Number Bank_code
1 John Smith 200004 400
2 Maria Griffin 545151 501
Bank Name depends 3 Susan Johnson 353453 400
on bank_code 4 Matt Long 784234 303
Activity
Studen Student
Fees Paid Program Name Class 1 Class 2 Class 3 Instructor
t ID Name
Economics
1 John Smith 200 Economics Biology 1 Ada Lovelace
1
Computer Business
2 Maria Griffin 400 Biology 1 Programming 2 Michaelangelo
Science Intro
3 Susan Johnson 800 Medicine Biology 2 Chuck Darwin
4 Matt Long 600 Dentistry Drillbit Taylor
Form teams of 5 or 6
Work together to create a normalization DB design for the data-set above