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

with referential integrity constraints:

PAYMENT.StudentID must exist in STUDENT.StudentID

and

PAYMENT.Activity must exist in ACTIVITY.Activity

These tables are in BCNF and will have no anomalies from functional dependencies. The
sample data for the normalized tables are shown in Figure 3-25.

Normalization example 5
Now consider a normalization process that requires two iterations of step 3 in the procedure
in Figure 3-19. To do this, we will extend the SKU_DATA relation by adding the budget code of
each department. We call the revised relation SKU_DATA_3 and define it as follows:

SKU_DATA_3 (SKU, SKU_Description, Department, DeptBudgetCode, Buyer)

Sample data for this relation are shown in Figure 3-26.
SKU_DATA_3 has the following functional dependencies:

SKU S (SKU_Description, Department, DeptBudgetCode, Buyer)
SKU_Description S (SKU, Department, DeptBudgetCode, Buyer)
Buyer S (Department, DeptBudgetCode)
Department S DeptBudgetCode
DeptBudgetCode S Department

Of the five determinants, both SKU and SKU_Description are candidate keys, but Buyer,
Department and DeptBudgetCode are not candidate keys. Therefore, this relation is not in BCNF.

STUDENT

Figure 3-25
the Normalized StUDENt,
aCtIVItY, and PaYMENt
relations

ACTIVITY

PAYMENT