BCNF Example 1Ĭonsider the following table ( St_Maj_Adv). A relation is in BCNF if, and only if, every determinant is a candidate key. Boyce-Codd normal form is a special case of 3NF. When a table has more than one candidate key, anomalies may result even though the relation is in 3NF.
Using FD as an abbreviation for full dependency is only used in Figure 12.1.) FD: full dependency (Note: FD typically stands for functional dependency.The abbreviations used in Figure 12.1 are as follows: To recap the normalization process for the School database, review the dependencies shown in Figure 12.1. StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade) The first step is to remove repeating groups, as discussed above.
Let’s look at the dependency diagram (Figure 12.1) for this example. Instructor (InstructorNo, InstructorName, InstructorLocation)Īt this stage, there should be no anomalies in third normal form.
Identify the new table that contains the course information.The only attribute that is fully dependent is grade. When examining the Student Course table, we see that not all the attributes are fully dependent on the PK specifically, all course information.The Student table is already in 2NF because it has a single-column PK.To move to 2NF, a table must first be in 1NF. If the relation has a composite PK, then each non-key attribute must be fully dependent on the entire PK and not on a subset of the PK (i.e., there must be no partial dependency or augmentation). The relation is automatically in 2NF if, and only if, the PK comprises a single attribute. To delete a student, we might also delete critical information about a course.įor the second normal form, the relation must first be in 1NF.When course information needs to be updated, we may have inconsistencies.To add a new course, we need a student.StudentCourse ( StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
StudentCourse ( StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade) How to update 1NF anomalies We will use the Student_Grade_Report table below, from a School database, as our example to explain the process for 1NF.
The PK of the new relation is a combination of the PK of the original relation plus an attribute from the newly created relation for unique identification. To normalize a relation that contains a repeating group, remove the repeating group and form two new relations. In the first normal form, only single values are permitted at the intersection of each row and column hence, there are no repeating groups. There are six normal forms, but we will only look at the first four, which are:īCNF is rarely used. Everything else should be derived from other tables. Ideally we only want minimal redundancy for PK to FK. Normal FormsĪll the tables in any database can be in one of the normal forms we will discuss next. This means that higher normal forms have less redundancy, and as a result, fewer update problems. Each normal form involves a set of dependency properties that a schema must satisfy and each normal form gives guarantees about the presence and/or absence of update anomalies. Normalization theory defines six normal forms (NF). Normalization theory draws heavily on the theory of functional dependencies.
This is created through an iterative process that involves identifying relevant entities, their attributes and their relationships. Use an entity relation diagram (ERD) to provide the big picture, or macro view, of an organization’s data requirements and operations. However, it is difficult to separate the normalization process from the ER modelling process so the two techniques should be used concurrently. Normalization should be part of the database design process.