you are at >> normalization >>Boyce-Codd normal form
Boyce-Codd normal form
This is a form of supplementing 3NF . Relation is at BCNF if:- for each non-trivial dependency between subsets of relation, set which is deteriminant is also identifying set of thw ralation.
- every attribute is functional dependent only from primary key, so there can't exist any transitive dependencies
Example
Each student can choose few subjects and lecturer can only conduct one subject.student | subject | lecturer |
---|---|---|
Zając | math | Różański |
Zima | math | Graczyk |
Jakubczak | math | Graczyk |
Zima | physics | Kolasa |
Jańska | physics | Bednarz |
Zygmunt | physics | Bednarz |
As there may be a primary key field student + subject, so the key clearly identifies the name of operator. Another candidate for the primary key can be couple student + lecturer. With this second option is not met 3rd normal form, because student + lecturer do not determine the name of the subject, and the name of the course depends only on the names of lecturers. For this reason, as a candidate for the primary key should be taken fields student + subject
There is a problem here. Suppose you want to assign the names of leading to the relevant subjects before the start of subscribing for courses. We can not do this because the student's name is part of the priamry key, and for this reason, when you insert a new row of the table you must insert the name of the student.
Anomaly can also occur in case if there is a need to change the name of lecurer. Then you will need to insert a new name to many records at once.
To deal with this problems this table should be brought to Boyce-Codd NF. To do that there will be created 3 tables :
student | subject |
---|---|
Zając | math |
Zima | math |
Jakubczak | math |
Zima | physics |
Jańska | physics |
Zygmunt | physics |
student | lecturer |
---|---|
Zając | Różański |
Zima | Graczyk |
Jakubczak | Graczyk |
Zima | Kolasa |
Jańska | Bednarz |
Zygmunt | Bednarz |
subject | lecturer |
---|---|
math | Różański |
math | Graczyk |
physics | Kolasa |
physics | Bednarz |