Online College Courses for Credit

+
ERD Connection Traps

ERD Connection Traps

Rating:
(0)
Author: Sophia Tutorial
Description:

Recall basic features of two types of connection traps possible in entity relationship models.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores connection traps that can be created in entity-relationship models.

The two common connection traps in entity-relationship modules include the chasm and fan traps. The chasm trap can occur when we have two one-to-many joins that converge on a single table and the query includes aggregate data from both leaf tables. As a result, you can run into a situation where the result from multiple rows are returned from the tables when the query is being processed. This type of issue can occur due to the join between the leaf tables as those valued would be multiplied potentially resulting in incorrect results.

In an entity-relationship model, this can occur when the existence of a relationship is seen in the ERD but in reality, there isn’t actually any connection. For example, if we have a situation where you have a group that has students and the group works on projects. The relationship, in this case, would set it such that not all projects are worked on by students as they may be handled externally. By having this type of model, if any project is not associated with a student, it cannot be associated with the group. So, if we have a situation where a group without students is assigned to a project (perhaps the students have not been assigned to the group yet), we could not actually create that link. To work around that scenario, we would have to create a link between the group and the project directly rather than depend on the middle relationship to include the student.

The fan trap occurs when you have two one-to-many joins that follow a parent-child form. If you try to aggregate both measures simultaneously, you will probably get incorrect results. For example, in our PostgreSQL database, we have customer, invoice, and invoice_line. If we attempted to aggregate the sum using the total from the invoice as well as the sum of the quantity * the unit_price at the same time. Let us see what would happen:

SELECT customer.customer_id, sum(total) as "SUM of Total", sum(quantity*unit_price) as "SUM of quantity times unit_price"
FROM customer, invoice, invoice_line
WHERE customer.customer_id = invoice.customer_id AND invoice.invoice_id = invoice_line.invoice_id
GROUP BY customer.customer_id;

File:11539-5300-1.png

You would have expected that the sum of the values to be the same. The sum of the quantity * unit_price is calculated correctly, but the sum of the total is not calculated correctly. This is because we get a value for every invoice_id that occurs in the invoice_line which will inflate the results for each invoice_id listed.

With an entity-relationship model, it can be a different scenario where you may have the relationship being ambiguous where you can have two or more one-to-many relationships that fan out from the same entity set. For example, if we have a scenario where the bank has a 1:M connection with a person and the bank also has a 1:M relationship with a clerk, we have two one-to-many relationships that come from the bank table. If we wanted to identify which clerk helped which person, we could not do that as they aren’t linked in that way and we do not have the means to store that. If we have that situation, we could reconstruct the relationship to have the bank have a 1:M relationship with the clerk. Then the clerk could have a 1:M relationship with the person. This eliminates the fan trap.


summary
The chasm and fan traps are common issues that are specific to various scenarios.