Handbook of Relational Database Design / Edition 1

Chapter 12: Translate the Logical Data Integrity

Chapter 12 completes the translation process. It examines implementation options for enforcing business rules about

• Entities-Step RDD4
• Relationships-Step RDD5
• Attributes-Step RDD6

STEP RDD4: DESIGN FOR BUSINESS RULES ABOUT ENTITIES

As part of logical data modeling, you identified two types of business rules about entities. These rules define the primary and alternate keys for an entity.

To review, a primary key is an attribute or minimal set of attributes that uniquely identifies an occurrence of a given entity. The primary key must exist for all occurrences of the entity. That is, it must never contain null values. Enforcing the properties of primary key is important in your relational implementation if you are to achieve a faithful and complete translation of your logical data model.

RULE RDD4.1

Enforce logical properties (uniqueness, minimality, and disallowance of nulls) of the entity's primary key through the relational implementation.

Different relational database products provide various mechanisms for enforcing these logical properties. We describe options that may be available, in order of preference and with associated advantages and disadvantages.

OPTION 1 FOR ENFORCING PRIMARY KEY PROPERTIES: DATA DEFINITION LANGUAGE (DDL)

As an example of option 1, the following syntax represents a proposed extension- to the ANSI SQL standard to support primary key specification:

CREATE TABLE CUSTOMER (BRANCH-CODE...;

NUMBER...,

NAME...,

ADDRESS...,

CREDIT-RTNG-C0DE...,

PRIMARY KEY (BRANCH-CODE,NUMBER))

Ideally, a PRIMARY KEY clause should be mandatory. It is not mandatory in the proposed ANSI SQL extension, thereby allowing for compatibility with the base SQL standard and upward compatibility in product implementations A mandatory PRIMARY KEY clause would reflect the rule that ever y entity must have a primary key. Since a table represents an entity (or at least-as you shall see-a derivation of one or more entities), every table likewise should have a primary key. The primary key guarantees a method of identifying a particular row in a table.

By definition, a primary key must be unique and nonnull. Moreover, since it is a minimal set of identifying attributes, if it is composite, each component must be nonunique. (That is, if one component is unique, then the other components are not necessary to guarantee uniqueness. Thus, The combination of components is not a minimal set of identifying attributes.) The PRIMARY KEY syntax ideally should activate automatic enforcement of these properties by the DBMS. (In fact, the proposed ANSI SQL PRIMARY KEY clause implies only uniqueness, not existence and not minimality.)

Advantages of a DDL PRIMARY KEY specification include:

• Option is easy to implement
• Implementation mechanism is transparent to users
• Integrity checks on uniqueness, minimality, and nulls disallowance (if automated by the PRIMARY KEY clause) are difficult or impossible to circumvent; that is, a user cannot override these restrictions without altering the table definition (e.g., via an SQL ALTER TABLE command)
• Option eliminates manual efforts by users to enforce primary key properties (i.e., you need not rely on the user to "do it correctly")

Be sure you understand the distinction between the primary key of your table and a primary index or other primary access mechanism. You define the primary key and enforce its properties to preserve data integrity. The primary key implies nothing about access patterns. In other words, whether or not a user ever references the primary key in query selection criteria (e.g., in the SQL WHERE clause), you still must define and enforce primary key properties as part of ensuring validity of data values.

On the other hand, you may later (in Chapters 13 and 14) define a primary index or other primary access mechanism (such as clustering or hashing). The primary or favored access mechanism represents the most efficient way of accessing the table for most requests. Thus, it typically reflects access requirements rather than data integrity rules. Sometimes, the primary access mechanism is built around the primary key, but only when access by primary key is frequent or important. If access by primary key is infrequent or unimportant, the primary access mechanism is built around other columns (e.g., foreign keys, nonkeys, or partial primary keys).

OPTION 2 FOR ENFORCING PRIMARY KEY PROPERTIES: DOMAIN DEFINITION TECHNIQUES

You can enforce at least some primary key properties (uniqueness, disallowance of nulls) through a product's domain capabilities. Options and examples for enforcing domains are discussed as part of Step RDD6. For primary keys, you will typically use DDL to specify nonnull, and either DDL or a unique index to specify uniqueness. Automated enforcement of nonuniqueness of primary key components (in the case of a composite primary key) may not be possible (see discussion of enforcing nonuniqueness under Step RDD6). Therefore, be sure the user has defined a minimal primary key in the first place.

Advantages of using domain techniques to enforce primary keys are the same as those identified for option 1. Disadvantages include the following:

• Each property of the primary key must be separately defined and enforced
• Use of indexes to enforce uniqueness introduces potential performance and administrative overhead (more details are discussed under option 2 for Step RDD6)
• Use of custom-tailored code, if required (e.g., to enforce minimality for a composite primary key) requires programming resources and may introduce performance overhead

Determine how optimally to enforce primary key properties for your specific DBMS. If you have not yet selected a relational DBMS product, evaluate the relative effectiveness of alternative products in enforcing primary key properties. Lack of automated support, via DDL or unique indexes, may be a compelling factor discouraging selection of a given product. . . .