NORMALIZATION
Database normalization is a database schema design technique, by which
an existing schema is modified to minimize redundancy and dependency of
data.
Normalization split a large table into smaller tables and define relationships
between them to increases the clarity in organizing data.
First Normal Form (1NF)
A table is referred to as being in its First Normal Form if the atomicity of
the table is 1.
Atomicity states that a single cell cannot hold multiple values; it must
hold only a single attribute.
The First Normal Form disallows the multi-valued attribute, composite
attribute, and their combinations.
In the student record table, the course column has two values. However, it
does not follow the First Normal Form. You get the table below if you use the
First Normal Form in the above table.
Candidate Key
A candidate key is a set of one or more columns that can identify a record
uniquely in a table, and YOU can use each candidate key as a Primary Key.
Super Key
A super key is a set of more than one key uniquely identifying a table record.
The primary Key is a subset of the Super Key.
Partial dependency: If a non-prime attribute is determined by a part of
candidate key, then it is called partial dependency.
Second Normal Form (2NF)
The first condition for the table to be in the Second Normal Form is that the
table has to be in the First Normal Form. The table should not possess
partial dependency.
The Location table possesses a composite primary key cust_id, storeid. The
non-key attribute is store_location. In this case, store_location only depends
on storeid, which is a part of the primary key. Hence, this table does not
fulfill the second normal form.
To bring the table to the Second Normal Form, you need to split the table
into two parts. This will give you the tables below:
Third Normal Form (3NF)
For a table to satisfy the Third Normal Form, it should satisfy the following
two conditions:
1. It should be in the Second Normal Form.
2. And, the table should not have any Transitive Dependency.
Transitive Dependency:
Non-prime attributes (not part of the candidate key) should not
depend on other non-prime attributes in a table.
Therefore, a transitive dependency is a functional dependency in
which A → C (A determines C) indirectly because of A → B and B → C
(where it is not the case that B → A).
The Third Normal Form ensures the reduction of data duplication. It is also
used to achieve data integrity.
In the above student table, stu_id determines subid, and subid determines
sub. Therefore, stu_id determines sub via subid. This implies that the table
possesses a transitive functional dependency and does not fulfill the third
normal form criteria.
Now, to change the table to the third normal form, you need to divide the
table as shown below:
Boyce CoddNormal Form (BCNF)
Boyce Codd Normal Form is also known as 3.5 NF. It is the superior version
of 3NF and was developed by Raymond F. Boyce and Edgar F. Codd to
tackle certain types of anomalies that were not resolved with 3NF.
The first condition for the table to be in Boyce Codd Normal Form is that the
table should be in the third normal form. Secondly, every Right-Hand
Side (RHS) attribute of the functional dependencies should depend on
the super key of that particular table.
For example:
You have a functional dependency X → Y. In the particular functional
dependency, X has to be part of the super key of the provided table.
Consider the subject table below:
The subject table follows these conditions:
Each student can enroll in multiple subjects.
Multiple professors can teach a particular subject.
For each subject, it assigns a professor to the student.
In the above table, student_id and subject together form the primary key
because by using student_id and subject, you can determine all the table
columns.
Another important point is that one professor teaches only one subject, but
one subject may have two professors.
This exhibits a dependency between the subject and the professor, i.e., the
subject depends on the professor's name.
The table is in 1st Normal form as all the column names are unique, all
values are atomic, and all the values stored in a particular column are of the
same domain.
The table also satisfies the 2nd Normal Form, as there is no Partial
Dependency.
There is no Transitive Dependency; hence, the table satisfies the 3rd Normal
Form.
This table follows all the Normal forms except the Boyce Codd Normal Form.
As you can see, stuid and subject form the primary key, which means the
subject attribute is a prime attribute.
However, there exists yet another dependency - professor → subject.
BCNF does not follow in the table as a subject is a prime attribute, and the
professor is a non-prime attribute.
To transform the table into the BCNF, you will divide the table into two
parts. One table will hold stuid, which already exists, and the second table
will hold a newly created column profid.
The second table will have profid, subject, and professor columns, which
satisfies the BCNF.
Fourth Normal Form
o A relation will be in 4NF if it is in Boyce Codd normal form and has no
multi-valued dependency.
o For a dependency A → B, if for a single value of A, multiple values of B
exists, then the relation will be a multi-valued dependency.
STUDENT
SID COURSE HOBBY
21 Computer Dancing
21 Math Singing
34 Chemistry Dancing
74 Biology Cricket
59 Physics Hockey
STUDENT_COURSE STUDENT_HOBBY
SID COURSE
21 Computer SID COURSE HOBBY
21 Math 21 Computer Dancing
34 Chemistry 21 Math Singing
74 Biology 34 Chemistry Dancing
59 Physics 74 Biology Cricket
59 Physics Hockey
Fifth normal form (5NF)
o A relation is in 5NF if it is in 4NF and not contains any join
dependency and joining should be lossless.
o 5NF is satisfied when all the tables are broken into as many tables as
possible in order to avoid redundancy.
o 5NF is also known as Project-join normal form (PJ/NF).
SUBJECT LECTURER SEMESTER
Computer Anshika Semester 1
Computer John Semester 1
Math John Semester 1
Math Akash Semester 2
Chemistry Praveen Semester 1
In the above table, John takes both Computer and Math class for Semester
1 but he doesn't take Math class for Semester 2. In this case, combination of
all these fields required to identify a valid data.
Suppose we add a new Semester as Semester 3 but do not know about the
subject and who will be taking that subject so we leave Lecturer and Subject
as NULL. But all three columns together acts as a primary key, so we can't
leave other two columns blank.
So to make the above table into 5NF, we can decompose it into three
relations P1, P2 & P3:
P1
SEMESTER SUBJECT
Semester 1 Computer
Semester 1 Math
Semester 1 Chemistry
Semester 2 Math
P2
SUBJECT LECTURER
Computer Anshika
Computer John
Math John
Math Akash
Chemistry Praveen
P3
SEMESTER LECTURER
Semester 1 Anshika
Semester 1 John
Semester 1 John
Semester 2 Akash
Semester 1 Praveen