Normalization
1NF
• A relation will be 1NF if it contains an atomic value.
• It states that an attribute of a table cannot hold multiple
values. It must hold only single-valued attribute.
• First normal form disallows the multi-valued attribute,
composite attribute, and their combinations.
2NF
3NF
BCNF
• BCNF is the advance version of 3NF. It is stricter than
3NF.
• A table is in BCNF if every functional dependency X → Y,
X is the super key of the table.
• For BCNF, the table should be in 3NF, and for every FD,
LHS is super key.
4NF
• The given STUDENT table is in 3NF, but the COURSE and
HOBBY are two independent entity. Hence, there is no
relationship between COURSE and HOBBY.
• In the STUDENT relation, a student with
STU_ID, 21 contains two
courses, Computer and Math and two
hobbies, Dancing and Singing. So there is a Multi-
valued dependency on STU_ID, which leads to
unnecessary repetition of data.
5NF
• 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:
Lossy and lossless decomposition