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

Table
ITEM

Column
ItemNumber

Default Value
Surrogate key

ITEM Category None

ITEM ItemPrefix If Category = 'Perishable' then 'P'

If Category = 'Imported' then 'I'
If Category = 'One-off' then 'O'
Otherwise = 'N'

ITEM ApprovingDept If ItemPrefix = 'I' then

'SHIPPING/PURCHASING'
Otherwise = 'PURCHASING'

ITEM ShippingMethod If ItemPrefix = 'P' then 'Next Day'

Otherwise = 'Ground'

Figure 6-7
Sample Documentation for
Default Values

Data Constraints
Data constraints are limitations on data values. There are several different types. Domain
constraints limit column values to a particular set of values. For example, EMPLOYEE
.EmpCode could be limited to 'New Hire', 'Hourly', 'Salary', or 'Part Time'. Range constraints
limit values to a particular interval of values. EMPLOYEE.HireDate, for example, could be
limited to dates between January 1, 1990, and December 31, 2025.
An intrarelation constraint limits a column's values in comparison with other columns
in the same table. The constraint that EMPLOYEE.ReviewDate be at least three months after
EMPLOYEE.HireDate is an intrarelation constraint. An interrelation constraint limits a column's
values in comparison with other columns in other tables. An example for the CUSTOMER table is
that CUSTOMER.Name must not be equal to BAD_CUSTOMER.Name, where BAD_ CUSTOMER
is a table that contains a list of customers with credit and balance problems.
Referential integrity constraints, which we discussed in Chapter 3, are one type of inter-
relation constraint. Because they are so common, sometimes they are documented only when
they are not enforced. For example, to save work, a design team might say that every foreign
key is assumed to have a referential integrity constraint to the table that it references and that
only exceptions to this rule are documented.

Verify Normalization
The last task in step 1 of Figure 6-1 is to verify table normalization. When data models are
developed using forms and reports as guides, they generally result in normalized entities. This
occurs because the structures of forms and reports usually reflect how users think about their
data. Boundaries of a form, for example, often show the range of a functional dependency. If
this is hard to understand, think of a functional dependency as a theme. A well-designed form
or report will bracket themes using lines, colors, boxes, or other graphical elements. Those
graphical hints will have been used by the data modeling team to develop entities, and the
result will be normalized tables.
All of this, however, should be verified. You need to ask whether the resulting tables are in
BCNF and whether all multivalued dependencies have been removed. If not, the tables should
probably be normalized. However, as we discussed in Chapter 4, sometimes normalization is
undesirable. Thus, you should also examine your tables to determine if any normalized ones
should be denormalized.

Create Relationships

The result of step 1 is a set of complete, but independent, tables. The next step is to create
relationships. In general, we create relationships by placing foreign keys into tables. The way
in which this is done and the properties of the foreign key columns depend on the type of
relationship. In this section, we consider each of the relationships described in Chapter 5: