Database Reference
In-Depth Information
1. Create a table for each entity:
- Specify the primary key (consider surrogate keys, as appropriate)
- Specify candidate keys
- Specify properties for each column:
• Null status
• Data type
• Default value (if any)
• Data constraints (if any)
- Verify normalization
2. Create relationships by placing foreign keys
- Relationships between strong entities (1:1, 1:N, N:M)
- Identifying relationships with ID-dependent entities (intersection tables,
association patterns, multivalued attributes, archetype/instance patterns)
- Relationships between a strong entity and a weak but non-ID-dependent entity
(1:1, 1:N, N:M)
- Mixed relationships
- Relationships between supertype/subtype entities
- Recursive relationships (1:1, 1:N, N:M)
3. Specify logic for enforcing minimum cardinality:
- M-O relationships
- O-M relationships
- M-M relationships
Figure 6-1
Steps for Transforming a
Data Model into a Database
Design
Selecting the Primary Key
The selection of the primary key is important. The DBMS will use the primary key to facilitate
searching and sorting of table rows, and some DBMS products use it to organize table storage.
DBMS products almost always create indexes and other data structures using the values of the
primary key.
The ideal primary key is short, numeric, and fixed. EmployeeNumber in Figure 6-2 meets
all of these conditions and is acceptable. Beware of primary keys such as EmployeeName,
Email, (AreaCode, PhoneNumber), (Street, City, State, Zip), and other long character columns.
In cases like these, when the identifier is not short, numeric, or fixed, consider using another
candidate key as the primary key. If there are no additional candidate keys, or if none of them
is any better, consider using a surrogate key.
A surrogate key is a DBMS-supplied identifier of each row of a table. Surrogate key
values are unique within the table, and they never change. They are assigned when the row
is created, and they are destroyed when the row is deleted. Surrogate key values are the best
possible primary keys because they are designed to be short, numeric, and fixed. Because of
these advantages, some organizations have gone so far as to require that surrogates be used
for the primary key of every table.
Before endorsing such a policy, however, consider two disadvantages of surrogate keys.
First, their values have no meaning to a user. Suppose you want to determine the department
to which an employee is assigned. If DepartmentName is a foreign key in EMPLOYEE, then
when you retrieve an employee row, you obtain a value such as 'Accounting' or 'Finance'. That
value may be all that you need to know about department.
EMPLOYEE
EMPLOYEE
Figure 6-2
Transforming an Entity to a
Table
EmployeeNumber
EmployeeNumber
EmployeeName
Phone
Email
HireDate
ReviewDate
EmpCode
EmployeeName
Phone
Email
HireDate
ReviewDate
EmpCode
(a) EMPLOYEE Entity
(b) EMPLOYEE Table
 
Search WWH ::




Custom Search