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.
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:
The relationships between these entities can be summarized in plain terms:
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.