0627-0630

Previous Table of Contents Next

Page 627

poor requirements definition will most likely result in poor or inadequate design, because these requirements provide the foundation for the later phases of design, including the logical and physical data models.

The Logical Model

A common way to represent the logical model is through an entity-relationship (E-R) diagram. For the purposes of this type of model, an entity is defined as a discrete object for which items of data are being stored, and a relationship refers to an association between two entities.

In the contact manager example, there are five main entities for which data is being stored:

• Individuals (with whom contacts are made)
• Addresses
• Phone Numbers
• Contacts (communications with individuals)
• Employees

The relationships between these entities can be summarized in plain terms:

• Employees have access to zero, one, or many individuals.
• Individuals have one or many addresses.
• Individuals have one or many phone numbers.
• Employees make zero, one, or many contacts.
• Individuals have zero, one, or many contacts.

These entities and their relationships can be represented graphically by an E-R diagram, as shown in Figure 25.1.

Figure 25.1. E-R diagram for the
contact manager sample
application.

NOTE

Observe how the one to one-or-many and one-to-zero-one-or-many relationships are represented. One-to-one and one-to-zero-or-one relationships can be represented using similar notation.

Page 628

Listing entities and relationships might seem to be a simplistic approach to modeling the application's data, but it is often a good first step. In larger applications with hundreds of entities, these models can become extremely complex.

The model diagrammed in Figure 25.1 can be taken a step further by defining attributes for each entity. An entity's attributes are the individual items of data to be stored that relate specifically to the object. The attributes for each entity in the example are listed in Table 25.2.

Table 25.2. Simple entity and attribute definitions.

Employees	Individuals	Contacts
Employee Number	Last Name	Contact Date
User ID	First Name	Contacted By
	Middle Initial	Contact Reason
	Company	Contact Method
	Individual Notes	Contact Type
	Company Notes	Contact Notes
Addresses	Phone Numbers
Address Line 1	Phone Number
Address Line 2	Phone Type
Address Line 3
City
State
Zip Code
Address Type

Note that several items of information are missing. The audit information mentioned in the functional specification is omitted. This can be handled by adding a Last Update User ID and Last Update Date/Time Stamp attribute to each entity. More important, there are attributes missing that are required to relate entities to each other. These data items will be handled somewhat differently because they are not "natural" attributes belonging to a specific entity.

This is a highly abstract view of the data, concerned only with broad categories of data (entities) and the logical relationships between them. The E-R model, although good at representing basic data concepts, is not of much use when it comes to physical implementation. The relational model helps bridge this gap.

Page 629

The relational model is characterized by its use of keys and relations, among other things. The term relation in the context of relational database theory should not be confused with a relationship. A relation can be viewed as an unordered, two-dimensional table, where each row is distinct. Relationships are built between relations (tables) through common attributes. These common attributes are called keys.

There are several types of keys, and they sometimes differ only in terms of their relationships to other attributes and relations. A primary key uniquely identifies a row in a relation, and each relation can have only one primary key, even if more than one attribute is unique. In some cases, it takes more than one attribute to uniquely identify each row in a relation. The aggregate of these attributes is called a concatenated key, or a composite key. In other cases, a primary key must be generated. The entity Individuals in the preceding example illustrates this point. Although it might be likely, there is no guarantee that the combination of the entity's attributes will be unique. A new attribute should be created based on generated values to make Individuals a relation. This can be accomplished in Oracle through the use of a SEQUENCE.

Another type of key, called a foreign key, exists only in terms of the relationship between two relations. A foreign key in a relation is a nonkey attribute that is a primary key (or part of the primary key) in another relation. This is the shared attribute that forms a relationship between two relations (tables). Primary and foreign key relationships are illustrated in Table 25.3.

Referring back to the example, the entities' attributes can be extended to fulfill the audit requirements and make the model relational. Note that the ID attribute is a generated primary key in each relation in which it appears. The reasons for this will be explained in further detail in the following section on performance considerations.

Table 25.3. Entity attributes (relational model).

Employees	Individuals
Employee Number (PK)	ID (PK)
User ID	Last Name
Last Update User ID	First Name
Last Update Date/Time	Middle Initial, Company, Contact Type
	Assigned Employee (FK), Individual Notes
	Company Notes
	Last Update User ID
	Last Update Date/Time
Addresses	Phone Numbers
ID (PK)	ID (PK)
Individual ID (FK)	Individual ID (FK)

 continues 

Page 630

Table 25.3. continued

Addresses	Phone Numbers
Address Line 1	Phone Number
Address Line 2	Phone Type
Address Line 3	Last Update User ID
City
Last Update Date/Time
State
Zip Code
Address Type
Last Update User ID
Last Update Date/Time

Contacts

ID (PK)

Individual ID (FK)

Contacted By (FK)

Contact Date

Contact Reason

Contact Method

Contact Notes

Last Update User ID

Last Update Date/Time

NOTE

(PK) indicates that the attribute is the primary key of the relation. (FK) indicates that the attribute is a foreign key.

There are numerous limitations and redundancies in this model. For example, if one individual works for more than one company, he or she must be stored as two separate individuals to be associated with both companies. Redundancies are also introduced when multiple contacts share the same address and phone number.

Previous Table of Contents Next