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

This notation indicates that SKU is the primary key of SKU_DATA and that (OrderNumber,
SKU) is the primary key of ORDER_ITEM.
In order to function properly, a primary key, whether it is a single column or a composite
key, must have unique data values inserted into every row of the table. While in fact this may
seem obvious, it is significant enough to be named the entity integrity constraint , and is a
fundamental requirement for the proper functioning of a relational database.
By The WAy What do you do if a table has no candidate keys? In that case, define the
primary key as the collection of all of the columns in the table. Because
there are no duplicate rows in a stored relation, the combination of all of the columns
of the table will always be unique. Again, although tables generated by SQL manipula-
tion may have duplicate rows, the tables that you design to be stored should never be
constructed to have data duplication. Thus, the combination of all columns is always a
candidate key.
Surrogate Keys
A surrogate key is an artificial column that is added to a table to serve as the primary key.
The DBMS assigns a unique value to a surrogate key when the row is created. The assigned
value never changes. Surrogate keys are used when the primary key is large and unwieldy. For
example, consider the relation RENTAL_PROPERTY:
RENTAL_PROPERTY ( Street , City , State/Province , Zip/PostalCode , Country , Rental_Rate)
The primary key of this table is (Street, City, State/Province, Zip/PostalCode, Country). As we
will discuss further in Chapter 6, for good performance a primary key should be short and, if
possible, numeric. The primary key of RENTAL_PROPERTY is neither.
In this case, the designers of the database would likely create a surrogate key. The struc-
ture of the table would then be:
RENTAL_PROPERTY ( PropertyID , Street, City, State/Province, Zip/PostalCode, Country,
Rental_Rate)
The DBMS can then be used to assign a numeric value to PropertyID when a row is created (exactly
how this is done depends upon which DBMS product is being used). Using that key will result in bet-
ter performance than using the original key. Note that surrogate key values are artificial and have
no meaning to the users. In fact, surrogate key values are normally hidden in forms and reports.
Foreign Keys
A foreign key is a column or composite of columns that is the primary key of a table other
than the one in which it appears. The term arises because it is a key of a table foreign to the
one in which it appears. In the following two tables, DEPARTMENT.DepartmentName is the
primary key of DEPARTMENT and EMPLOYEE.DepartmentName is a foreign key. In this text,
we will show foreign keys in italics:
DEPARTMENT ( DepartmentName , BudgetCode, ManagerName)
EMPLOYEE ( EmployeeNumber , EmployeeLastName, EmployeeFirstName, DepartmentName )
Foreign keys express relationships between rows of tables. In this example, the foreign key
EMPLOYEE.DepartmentName stores the relationship between an employee and his or her
department.
Consider the SKU_DATA and ORDER_ITEM tables. SKU_DATA.SKU is the primary key of
SKU_DATA, and ORDER_ITEM.SKU is a foreign key.
SKU_DATA ( SKU , SKU_Description, Department, Buyer)
ORDER_ITEM ( OrderNumber , SKU , Quantity, Price, ExtendedPrice)