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

As discussed earlier, this table has three functional dependencies:

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

Normalization Example 1: The “Step-by-Step” Method

Both SKU and SKU_Descripion are candidate keys. Logically, SKU makes more sense as the
primary key because it is a surrogate key, so our relation, which is shown in Figure 3-20, is:

SKU_DATA ( SKU , SKU_Description, Department, Buyer)

Checking the relation against Figure 3-4, we find that SKU_DATA is in 1NF.
Is the SKU_DATA relation in 2NF? A relation is in 2NF if and only if it is in 1NF and all non-
key attributes are determined by the entire primary key. Because the primary key SKU is a single
attribute key, all the non-key attributes are therefore dependent on the entire primary key.
Thus, the SKU_DATA relation is in 2NF.
Is the SKU_DATA relation in 3NF? A relation is in 3NF if and only if it is in 2NF and there are no
non-key attributes determined by another non-key attribute. Because we seem to have two non-key at-
tributes (SKU_Description and Buyer) that determine non-key attributes, the relation is not in 3NF!
However, this is where things get a bit tricky. A non-key attribute is an attribute that is
neither (1) a candidate key itself nor (2) part of a candidate key. SKU_Description, therefore, is
not a non-key attribute (sorry about the double negative). The only non-key attribute is Buyer!
Therefore, we must remove only the functional dependency

Buyer S Department

We will now have two relations:

SKU_DATA_2 ( SKU , SKU_Description, Buyer )
BUYER ( Buyer , Department)

Is SKU_DATA_2 in 3NF? Yes, it is—there are no non-key attributes that determine an-
other non-key attribute.
Is the SKU_DATA_2 relation in BCNF? A relation is in BCNF if and only if it is in 3NF and every
determinant is a candidate key. The determinants in SKU_DATA_2 are SKU and SKU_Description:

SKU S (SKU_Description, Buyer)
SKU_Description S (SKU, Buyer)

SKU_DATA

Figure 3-20
the SKU_Data relation