Understanding Database Normalization in ORM Language

1NF

One column could only contain one type of data (i.e. no array, no object)

Example: consider we have a table called users, which has one column telephones with data like this (111) 222-3333, (444) 555-6666, then it violates 1nf.

The approach to making the table to be compliant with 1NF is to save the telephone numbers into two columns, telephone1 and telephone2.

2NF

Each attribute of the object should only depend on the primary key. If the uniqueness of the object depends on other attributes, then it may be good to split the class into two or more classes.

Example: we now have a table called enrollments, which has the following fields: student_id, course_id, course_name, professor. Then it violates the 2nf.

Therefore, we have to extract the course info out of the table, now we have two tables, enrollments and courses.

Dependencies in the enrollments table:
(student_id, course_id) -> course_name

course_id -> course_name

3NF

There should be no transitive dependency on the non-primary attributes. If one attribute could be decided based on other attributes, it should be extracted to a new class.

Still considering the previous example, if the table courses has a column called office, then it violates the 3nf. because the attribute office depends on the column professor, not the course.

Dependencies in the course table:

course_id -> professor -> office
course_id -> office

BCNF

3NF addressed that the non-primary key attributes cannot have a transitive dependency, while BCNF further stated that the attribute should also not be dependent on the primary key. This usually occurs if there are multiple primary keys in the table.

ISBN, NAME, AUTHOR

ISBN -> NAME
(NAME, AUTHOR) -> ISBN

(NAME, AUTHOR) -> ISBN -> NAME


(NAME, AUTHOR) -> NAME

4NF

In a many-to-many relationship, if the attributes in the pivot table are not related, then it violates the 4nf relationship and should be separated into multiple many-to-many relationships.

5NF

Based on 4NF, Even if the attributes in the pivot table are related, they should be further separated.