Database Reference
In-Depth Information
If a natural key does not exist for an entity, it is common to create a primary key column that will
be unique and accessible to users. The primary keys in the Premiere Products database (RepNum,
CustomerNum, OrderNum, and PartNum) were created to serve as the primary keys. A column that you
create for an entity to serve solely as the primary key and that is visible to users is called an artificial key.
The final type of primary key, which is called a surrogate key (or a synthetic key), is a system-generated
primary key that is usually hidden from users. When a DBMS creates a surrogate key, it is usually an auto-
matic numbering data type, such as the Access AutoNumber data type. For example, suppose you have the
following relation for customer payments:
187
Payment (CustomerNum, PaymentDate, PaymentAmount)
Because a customer can make multiple payments, CustomerNum cannot be the primary key. Assuming
it is possible for a customer to make more than one payment on a particular day, the combination of
CustomerNum and PaymentDate also cannot be the primary key. Adding an artificial key, such as
PaymentNum, means you would have to assign a PaymentNum every time the customer makes a payment.
Adding a surrogate key, such as PaymentId, would make more sense because the DBMS will automatically
assign a unique value to each payment. Users do not need to be aware of the PaymentId value, however.
Database Design Language (DBDL)
To carry out the design process, you must have a mechanism for representing tables and keys. The standard
notation you have used thus far for representing tables is fine, but it does not go far enough
there is no way
to represent alternate, secondary, or foreign keys. Because the information-level design method is based on
the relational model, it is desirable to represent tables with the standard notation. To do so, you will add
additional features capable of representing additional information. One approach to doing this is called Data-
base Design Language (DBDL). Figure 6-1 shows sample DBDL documentation for the Employee table.
—
Employeeƒ(
EmployeeNum
,ƒLastName,ƒFirstName,ƒStreet,ƒCity,ƒState,ƒZip,
ƒƒƒƒƒƒWageRate,ƒSocSecNum,ƒDepartmentNum)
ƒƒƒƒƒƒAKƒƒƒSocSecNum
ƒƒƒƒƒƒSKƒƒƒLastName
ƒƒƒƒƒƒFKƒƒƒDepartmentNumƒ ƒDepartmentƒ
FIGURE 6-1
DBDL for the Employee table
In DBDL, you represent a table by listing all columns and then underlining the primary key. Below the
table definition, you list any alternate keys, secondary keys, and foreign keys, using the abbreviations AK, SK,
and FK, respectively. For alternate and secondary keys, you can list the column or collection of columns by
name. In the case of foreign keys, however, you must also represent the table whose primary key the foreign
key must match. In DBDL, you write the foreign key followed by an arrow pointing to the table that the for-
eign key identifies.
The rules for defining tables and their keys using DBDL are as follows:
Tables (relations), columns (attributes), and primary keys are written by first listing the table
name and then, in parentheses, listing the columns that make up the table. The column(s) that
make up the primary key are underlined.
Alternate keys are identified by the abbreviation AK, followed by the column(s) that make up
the alternate key.
Secondary keys are identified by the abbreviation SK, followed by the column(s) that make up
the secondary key.
Foreign keys are identified by the abbreviation FK, followed by the column(s) that make up the
foreign key. Foreign keys are followed by an arrow pointing to the table identified by the foreign
key. When several tables are listed, a common practice places the table containing the foreign
key below the table that the foreign key identifies, if possible.
Figure 6-1 shows that there is a table named Employee, containing the columns EmployeeNum,
LastName, FirstName, Street, City, State, Zip, WageRate, SocSecNum, and DepartmentNum. The primary
key is EmployeeNum. Another possible primary key is SocSecNum, which is listed as an alternate key.
