Answer to Problem 3.1

In situation #1 you can see from the first line that Don sold Sam the Cobra. In situation #2, looking at buys you can see that Sam did actually buy a Cobra (second line). Looking at buys from you can see that both Don and Sharon sold Sam cars. Looking at sells you can see that both Don and Sharon have sold Cobras. So the answer is either Don or Sharon sold Sam the Cobra. This is not good enough. This demonstrates that having three binary relationships does not capture the same information that one ternary relationship does.

It might be asserted (and has been by a former student) that the ambiguity in the problem is a result of the data base keeping information about car types (Cobra, Mustang, etc.) instead of actual cars (Cobra VIN=32, Cobra VIN=33, etc.). This is the case and I'd like to demonstrate why here.

Suppose that the four cars in this data base are numbered 1, 2, 3, 4. We're going to try to answer the same question, “Who sold Sam the Cobra?”, using just the binary relationships but with information about numbered cars rather than the car types that is used above. The Buys relationship shows that Sam bought cars 1 and 2. The Sells relationship will show which sales person sold car #2 (the Cobra). It does not say to whom, but we already know that Sam bought car #2. So without even consulting the Buys from relationship, we know who sold Sam the Cobra.

This is a good observation but does not change the essential point. Breaking down a ternary relationship into its component binary relationships will sometimes result in a loss of information. It will always result in a loss of data if at least one of the entity types is a type of thing (e.g., a car or skill) as opposed to a specific thing (e.g., an actual car).

The question also remains: Why break up a ternary relationship into its component binary relationships if the ternary relationship captures what's really going on in the world? A customer does buy a car from a salesperson. That's really how we think about it and how it really occurs. It's not: a customer buys a car, a car is sold by a sales person, and a customer buys from a sales person. The real world event involves three entities. Why not construct the data base to reflect this reality?

Return to advanced concepts in ER modeling.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License