The Relational Model and Normalization - Database Processing: Fundamentals, Design, and Implementation - page 146

SKU_DATA_3
Figure 3-26
Sample Data for the
SKU_Data_3 relation
To normalize this table, we must transform this table into two or more tables that are in
BCNF. In this case, there are two problematic functional dependencies. According to the note
at the end of the procedure in Figure 3-19, we take the functional dependency whose determi-
nant is not a candidate key and has the largest number of columns first. In this case, we take
the columns of
Buyer S (Department, DeptBudgetCode)
and place them in a table of their own.
Next, we make the determinant the primary key of the new table, remove all columns
except Buyer from SKU_DATA_3, and make Buyer a foreign key of the new version of SKU_
DATA_3, which we will name SKU_DATA_4. We can also now assign SKU as the primary key
of SKU_DATA_4. The results are:
BUYER ( Buyer , Department, DeptBudgetCode)
SKU_DATA_4 ( SKU , SKU_Description, Buyer )
We also create the referential integrity constraint:
SKU_DATA_4.Buyer must exist in BUYER.Buyer
The functional dependencies from SKU_DATA_4 are:
SKU S (SKU_Description, Buyer)
SKU_Description S (SKU, Buyer)
Because every determinant of SKU_DATA_4 is also a candidate key, the relationship is
now in BCNF. Looking at the functional dependencies from BUYER we find:
Buyer S (Department, DeptBudgetCode)
Department S DeptBudgetCode
DeptBudgetCode S Department
BUYER is not in BCNF because neither of the determinants Department and DeptBudgetCode
are candidate keys. In this case, we must move (Department, DeptBudgetCode) into a table
of its own. Following the procedure in Figure 3-19 and breaking BUYER into two tables
(DEPARTMENT and BUYER_2) gives us a set of three tables:
DEPARTMENT ( Department , DeptBudgetCode)
BUYER_2 ( Buyer , Department )
SKU_DATA_4 ( SKU , SKU_Description, Buyer )