Database Reference
In-Depth Information
Observe:
The principle of BCNF is very simple but profound. By being guided by it,
you can actually bypass obtaining 1NF, 2NF and 3NF relations, and move directly into
a set of BCNF relations. Adopting this approach will significantly simplify the analysis
process. Moreover, in most practical situations, you will not be required to normalize
beyond BCNF. This approach will be further clarified in the next chapter.
4.9 The Fourth Normal Form
The fourth normal form (4NF) relates to the situation where mutually independent, but
related attributes form a relation and the inefficient arrangement causes duplication and
hence modification anomalies. Consider the database file,
CTT-Schedule
, representing
course-teacher-text combinations in an educational institution. Assume the following:
a.
A course can be taught by several teachers.
b.
A course can require any number of texts.
c.
Teachers and texts are independent of each other i.e. the same
texts are used irrespective of who teaches the course.
d.
A teacher can teach several courses.
Figure
4-3
provides some sample data for the purpose of illustration.
Figure 4-3.
CTT-Schedule File
Note that the theory so far, does not provide a method of treating such a situation,
except flattening the structure (by making each attribute part of the primary key) as
shown below:
R9
{Course, Teacher, Text} PK[Course, Teacher, Text]
Since
R9
is keyed on all its attributes, it is in BCNF. Yet, two potential problems are
data redundancy and modification anomalies (the former leading to the latter). In our
example, in order to record that Calculus II is taught by both Professor B and Professor C,
four records are required. In fact, if a course is taught by p professors and requires n texts,
the number of records required to represent this situation is p*n. This is extraordinary,
and could prove to be very demanding on storage space.
