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

values of both SKU and SKU_Description are unique in the table. For example, the SKU value
100100 appears just once. Similarly, the SKU_Description value 'Half-dome Tent' occurs just
once. From this, it is tempting to conclude that values of determinants are always unique in a
relation. However, this is not true.
For example, Buyer is a determinant, but it is not unique in SKU_DATA. The buyer 'Cindy
Lo' appears in two different rows. In fact, for these sample data all of the buyers occur in two
different rows.
In truth, a determinant is unique in a relation only if it determines every other col-
umn in the relation. For the SKU_DATA relation, SKU determines all of the other columns.
Similarly, SKU_Description determines all of the other columns. Hence, they both are unique.
Buyer, however, only determines the Department column. It does not determine SKU or
SKU_Description.
The determinants in ORDER_ITEM are (OrderNumber, SKU) and (Quantity, Price).
Because (OrderNumber, SKU) determines all of the other columns, it will be unique in the rela-
tion. The composite (Quantity and Price) only determines ExtendedPrice. Therefore, it will not
be unique in the relation.
This fact means that you cannot find the determinants of all functional dependencies
simply by looking for unique values. Some of the determinants will be unique, but some will
not be. Instead, to determine if column A determines column B, look at the data and ask,
“Every time a value of column A appears, is it matched with the same value of Column B?” If
so, it can be a determinant of B. Again, however, sample data can be incomplete, so the best
strategies are to think about the nature of the business activity from which the data arise and
to ask the users.
Keys
The relational model has more keys than a locksmith. There are candidate keys, composite
keys, primary keys, surrogate keys, and foreign keys. In this section, we will define each of these
types of keys. Because key definitions rely on the concept of functional dependency, make sure
you understand that concept before reading on.
In general, a key is a combination of one or more columns that is used to identify particu-
lar rows in a relation. Keys that have two or more columns are called composite keys .
Candidate Keys
A candidate key is a determinant that determines all of the other columns in a relation. The
SKU_DATA relation has two candidate keys: SKU and SKU_Description. Buyer is a determi-
nant, but it is not a candidate key because it only determines Department.
The ORDER_ITEM table has just one candidate key: (OrderNumber, SKU). The other
determinant in this table, (Quantity, Price), is not a candidate key because it determines only
ExtendedPrice.
Candidate keys identify a unique row in a relation. Given the value of a candidate key,
we can find one and only one row in the relation that has that value. For example, given
the SKU value of 100100, we can find one and only one row in SKU_DATA. Similarly, given
the OrderNumber and SKU values (2000, 101100), we can find one and only one row in
ORDER_ITEM.
Primary Keys
When designing a database, one of the candidate keys is selected to be the primary key . This
term is used because this key will be defined to the DBMS, and the DBMS will use it as its pri-
mary means for finding rows in a table. A table has only one primary key. The primary key can
have one column, or it can be a composite.
In this text, to clarify discussions we will sometimes indicate table structure by showing
the name of a table followed by the names of the table's columns enclosed in parentheses.
When we do this, we will underline the column(s) that comprise the primary key. For example,
we can show the structure of SKU_DATA and ORDER_ITEM as follows:
SKU_DATA ( SKU , SKU_Description, Department, Buyer)
ORDER_ITEM ( OrderNumber , SKU , Quantity, Price, ExtendedPrice)