Lecture 3

Higher-Level Normal Forms

1- The Boyce – Codd Normal Form (BCNF)
A table is in (BCNF) when every determinant in the table is a candidate key. Most designers consider the BCNF to be a special case of the 3NF, so how can a table be in the 3NF and not are in BCNF?
A table is in 3NF when it is in 2NF and there are no transitive dependencies. But what about a case in which a non-key attribute is the determinant of a key attribute? That condition does not violate 3NF, yet it fails to meet the BCNF requirements because BCNF requires that every determinant in the table be a candidate key.
This functional dependency:
A+B ?C, D
C ? B


The condition C ? B indicates that a non-key attribute determines part of the primary key, and that dependency is not transitive, thus, the table structure in figure above meets the 3NF requirements. Yet the condition C ? B causes the table to fail to meet the BCNF requirements.
To convert the table structure above into table structures that are in 3NF and BCNF, first change the primary key to A+C. That is an appropriate action because the dependency C ? B means that C is, in effect, a superset partial dependency C? B. next, follow the standard decomposition procedures to produce the result shown in figure bellow





To see how this procedure can be applied to an actual problem, examine the sample data in table below:


The table above reflects the following conditions:
• Each CLASS_CODE identifies a class uniquely. This condition illustrates the case in which a course might generate many classes.
• A student can take many classes.
• A staff member can teach many classes but each class is taught by only one staff member.
The structure shown in table above is reflected in Panel A of figure bellow:
STU_ID + STAFF_ID ? CLASS_CODE, ENROLLL_GRADE
CLASS_CODE ? STAFF_ID


Panel A has a major problem. For example, if a different staff member is assigned to teach class 32456 two rows will require updates thus producing an update anomaly. And if student 135 drops class 28458 information about how taught that class is lost, thus, producing a deletion anomaly the solution to the problem is in panel B.

Note:
Remember that a table is in BCNF when every determinant in that table is a candidate key. Therefore, when a table contains only one candidate key, 3NF and BCNF are equivalent.
2- Fourth Normal Form (4NF)
You might encounter poorly designed database, or you might be asked to convert spreadsheets into a database format in which multiple multivalued attributes exist, for Example, consider the possibility that an employee can have many assignments and can also be in multiple service organizations



There is a problem with the tables in figure above, That is, the tables contain two sets of independent mulivalued dependencies. (One employee can have many service entries and many assignment entries) the tables are likely to contain quite a few null values, in fact the tables do not even have a viable candidate key. (The EMP_NUM values are not unique, so they cannot be PKs. No combination of the attributes in table versions 1 and 2 can be used to create a PK because some of them contain nulls).
Version 3 at least has a PK, but it is composed of all of the attributes in the table, in fact, version 3 meets 3NF requirements, yet it contains many redundancies that are clearly undesirable.
The solution is to eliminate the problems caused by independent multivalued dependencies. You do this by creating the ASSIGNMENT and SERVICE_V1 tables depicted in figure bellow note that neither the ASSIGNMENT nor the SERVICE_V1 table contains independent multivalued dependencies those tables are said to be in 4NF. If you fallow the proper design procedures illustrated, you shouldn’t encounter the previously described problem. Specifically, the discussion of 4NF is largely academic if you make sure that your tables conform to the following two rules:
1. All attributes must be dependent on the primary key, but they must be independent of each other.
2. No row may contain two or more multivalued facts about an entity.


Note:
A table is in 4NF when it is in 3NF and has no multiple sets of multivalued dependencies.