Refining The Entity-Relationship Diagram

This section discusses four basic rules for modeling relationships

Entities Must Participate In Relationships

Entities cannot be modeled unrelated to any other entity. Otherwise, when the model was transformed to the relational model, there would be no way to navigate to that table. The exception to this rule is a database with a single table.

Resolve Many-To-Many Relationships

Many-to-many relationships cannot be used in the data model because they cannot be represented by the relational model. Therefore, many-to-many relationships must be resolved early in the modeling process. The strategy for resolving many-to-many relationship is to replace the relationship with an association entity and then relate the two original entities to the association entity. This strategy is demonstrated below Figure 6.1 (a) shows the many-to-many relationship:

Employees may be assigned to many projects.
Each project must have assigned to it more than one employee.

In addition to the implementation problem, this relationship presents other problems. Suppose we wanted to record information about employee assignments such as who assigned them, the start date of the assignment, and the finish date for the assignment. Given the present relationship, these attributes could not be represented in either EMPLOYEE or PROJECT without repeating information. The first step is to convert the relationship assigned to to a new entity we will call ASSIGNMENT. Then the original entities, EMPLOYEE and PROJECT, are related to this new entity preserving the cardinality and optionality of the original relationships. The solution is shown in Figure 1B.

Figure 1: Resolution of a Many-To-Many Relationship

Notice that the schema changes the semantics of the original relation to

employees may be given assignments to projects
and projects must be done by more than one employee assignment.

A many to many recursive relationship is resolved in similar fashion.

Transform Complex Relationships into Binary Relationships

Complex relationships are classified as ternary, an association among three entities, or n-ary, an association among more than three, where n is the number of entities involved. For example, Figure 2A shows the relationship

Employees can use different skills on any one or more projects.
Each project uses many employees with various skills.

Complex relationships cannot be directly implemented in the relational model so they should be resolved early in the modeling process. The strategy for resolving complex relationships is similar to resolving many-to-many relationships. The complex relationship replaced by an association entity and the original entities are related to this new entity. entity related through binary relationships to each of the original entities. The solution is shown in Figure 2 below.

Figure 2: Transforming a Complex Relationship

Eliminate redundant relationships

A redundant relationship is a relationship between two entities that is equivalent in meaning to another relationship between those same two entities that may pass through an intermediate entity. For example, Figure 3A shows a redundant relationship between DEPARTMENT and WORKSTATION. This relationship provides the same information as the relationships DEPARTMENT has EMPLOYEES and EMPLOYEEs assigned WORKSTATION. Figure 3B shows the solution which is to remove the redundant relationship DEPARTMENT assigned WORKSTATIONS.

Figure 3: Removing A Redundant Relationship

Summary

The last two sections have provided a brief overview of the basic constructs in the ER diagram. The next section discusses Primary and Foreign Keys.