Transforming Data Models into Database Designs - Database Processing: Fundamentals, Design, and Implementation - page 234

Figure 6-3(a) shows EMPLOYEE with a primary key of EmployeeNumber and a candidate,
or alternate, key of Email. In Figure 6-3(b), CustomerNumber is the primary key of CUSTOMER,
and both the composite (Name, City) and Email are candidate keys. In these diagrams, the
symbol AK n.m means the n th alternate key and the m th column of that alternate key. In the
EMPLOYEE table, Email is labeled AK1.1 because it is the first alternate key and the first col-
umn of that key. CUSTOMER has two alternate keys. The first is a composite of two columns,
which are labeled AK1.1 and AK1.2. The nomenclature Name (AK1.1) means that Name is the
first column of the first alternate key, and City (AK1.2) means that City is the second column of
the first alternate key. In CUSTOMER, Email is marked as AK2.1 because it is the first (and only)
column of the second alternate key.

Specifying Column Properties
The next step in the creation of a relation is to specify the column properties. Four properties
are shown in Figure 6-1: null status, data type, default value, and data constraints.

Null Status
Null status refers to whether the column can have a null value. Typically, null status is speci-
fied by using the phrase NULL if nulls are allowed and NOT NULL if not. Thus, NULL does
not mean that the column is always null; it means that null values are allowed. Because of
this possible confusion, some people prefer the term NULL ALLOWED rather than NULL.
Figure 6-4 shows the null status of each of the columns in the EMPLOYEE table.

By The Way The EMPLOYEE table in Figure 6-4 contains a subtlety. EmployeeNumber,
the primary key, is marked NOT NULL, but Email, the alternate key, is
marked NULL. It makes sense that EmployeeNumber should not be allowed to be null.
If it were, and if more than one row had a null value, then EmployeeNumber would not
identify a unique row. Why, however, should Email be allowed to have null values?
The answer is that alternate keys often are used just to ensure uniqueness. Marking
Email as a (possibly null) alternate key means that Email need not have a value, but, if it
has one, that value will be different from all other values of Email in the EMPLOYEE table.
This answer is dissatisfying because it means that alternate keys are not truly
alternate primary keys, and thus neither are they candidate keys ! Alas, that's the way it
is. Just know that primary keys can never be null but that alternate keys can be.

Data Type
The next step is to define the data type for each column. Unfortunately, each DBMS provides a
different set of data types. For example, to record currency values, Microsoft Access has a data
type called Currency and SQL Server has a data type called Money, but Oracle Database has no
data type for currency. Instead, with Oracle, you use the numeric data type for currency values.
A summary of data types for Oracle Database 11 g , SQL Server 2012, and MySQL 5.6 appears in
Chapter 7 as Figure 7-4.
If you know which DBMS you will be using to create the database, you can use that prod-
uct's data types in your design. Figure 6-5 illustrates the display of data types in a table using
the data types for SQL Server (e.g., datetime is an SQL Server data type).

EMPLOYEE

Figure 6-4
Table Display Showing Null
Status

EmployeeNumber: NOT NULL

EmployeeName: NOT NULL
Phone: NULL
Email: NULL (AK1.1)
HireDate: NOT NULL
ReviewDate: NULL
EmpCode: NULL