Understanding Database Normalization in ORM Language
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,
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:
professor. Then it violates the 2nf.
Therefore, we have to extract the course info out of the table, now we have two tables,
Dependencies in the enrollments table: (student_id, course_id) -> course_name course_id -> course_name
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
Dependencies in the course table: course_id -> professor -> office course_id -> office
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
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.
Based on 4NF, Even if the attributes in the pivot table are related, they should be further separated.