Problems with ER Model
This article will study the two main types of connection traps and how to solve such problems in ER models.
The ER model is incomplete as any other data model. It is necessary to understand the reason for its incompleteness because only then can we develop approaches to overcome the model's limitations. Sometimes, a problem known as a connection trap occurs due to the misinterpretation of the meaning of certain relationships.
There are two main types of connection models are:
Both Fan and Chasm traps occur when it becomes impossible to retrieve all the necessary information needed from the entire diagram.
1) Fan Traps
An ER model that represents a relationship between entity occurrences is ambiguous. Such a model is said to be in a fan trap. A fan trap may exist where two or more one-to-many relationships fan out from the same entity. The structure is of the form many to one/ one to many. To understand the fan trap problem, let us consider an example of a company where each department can have offices in different cities.
Each employee is associated with a single department because each employee works in only one department. However, the departments are associated with multiple locations because departments can have offices in difficult cities. To understand the problem more clearly, let us examine some occurrences of relationships using a value for the primary key attributes of EMPLOYEE, DEPARTMENT, AND LOCATION entities.
A problem arises if we want to determine which employee works at which location. For example. If we want to know in which city does Vikram works.
We can only determine that the employee Vikram works in either Amritsar or Mumbai from the current structure. The inability to answer the question specifically is because of mutual exclusion of Employee and Location entity occurrences with department entity.
We can resolve this fan trap by reconstructing the ER model so that they now represent the correct association between these entities.
With this reconstruction, we can now answer where employee Vikaram works? From the above, it is clear that employee Vikram works at the Amritsar location and in the accounting department.
2) Chasm Traps
A chasm trap is another problem with the ER model, which occurs when a pathway does not exist in all the related entities. A chasm trap may exist where there are one or more relationships with a minimum multiplicity of zero forming part of the pathway between related entities.
To understand the chasm trap, consider the following entities and their corresponding relationship.
A problem arises when we want to know which lockers are available in a branch. Here, we consider an example of a bank where a single branch has one or more customers who avail zero or more lockers to keep their belongings. We also assume that a customer uses not all customers avail locker and not all lockers. Now let us examine some occurrences of the relationships using a value for the primary key attributes of Bank_branch, Customer, and Locker entities to understand the problem more clearly.
Suppose we want to know which bank's branch locker L212 is available. We will be unable to find the answer as locker L212 has not yet been allocated. The inability to answer these questions is a result of a chasm trap. It would be considered a loss of information.
To solve this problem, we need to identify the missing relationship that relates the bank_branch entity with the locker entity it offers. This ensures that all the times the lockers associated with each bank_branch are known, including those lockers that have yet not been allocated. The ER diagram on adding this relationship would look like
Now let us examine the occurrences of the relationships using the values for the primary key attribute of the given entities.