Page 102 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 6: Relational Language and Database Design

Notes

Task Discuss Entity-Diagram relationship.

6.9 ER Design Issues

The following are the ER design issues:
1. Use entry sets attributes
2. Use of Entity sets or relationship sets
3. Binary versus entry relationship sets
4. Aggregation versus ternary relationship.

6.9.1 Use of Entity Sets versus Attributes

Consider the entity set employee with attributes employee-name and telephone-number. It can
easily be argued that a telephone is an entity in its own right with attributes telephone-number
and location (the office where the telephone is located). If we take this point of view, the
employee entity set must be redefined as follows:
1. The employee entity set with attribute employee-name.

2. The telephone entity set with attributes telephone-number and location.
3. The relationship set imp-telephone, which denotes the association between employees
and the telephones that they have.

The first difference between these two definitions of an employee is that, every employee has
precisely one telephone number associated with him. In the second case, however, the definition
states that employees may have several telephone numbers (including zero) associated with
them.

6.9.2 Use of Entity Sets versus Relationship Sets

In order to clarify whether an object is best expressed by an entity set or a relationship set,
assume that a bank loan is modeled as an entity. An alternative is to model a loan as a relationship
between customers and branches, with loan-number and amount as descriptive attributes. Each
loan is represented by a relationship between a customer and a branch.
If every loan is held by exactly one customer and customer is associated with exactly one branch,
the design where a loan is represented as a relationship, maybe satisfactory. However, with this
design, we cannot represent conveniently a situation in which several customers hold a loan
jointly. We must define a separate relationship for each holder of the joint loan. Then, we must
replicate the values for the descriptive attributes loan-number and amount in each such
relationship. Each such relationship must have the same value for the descriptive attributes
loan-number and amount. Two problems arise as a result of the replication:
1. The data are stored multiple times, wasting storage space; and
2. Updates potentially leaves the date in an inconsistent state, where the values differ in two
relationships for attributes that are supposed to have the same value.

LOVELY PROFESSIONAL UNIVERSITY 95