---

Importance of getting the model right....

---

• A supplier/project/part database description
  • The following is a lesson/warning in getting the model RIGHT

    ---

  • Consider the following description of a simple supplier/project/part database:

    A construction company performs a number of construction projects A project has name, duration, budget and uses parts supplied by some supplier. A part has name, weight and color A supplier has name, address, phone number

  ---

  ---

• Entities
  • Projects
  • Supplier
  • Part

  ---

• ER-Diagrams of the supplier/project/part database
  • Consider the following two ER-diagrams:

    ---

    Model 1:

    ---

    Model 2:

    ---

  • Questions:

    Which model will capture the above description ? Are these two models equivalent (= same) ??? If not, what does each model represent ?

  ---

• Answer....
  • To answer the above question, consider the representation of the following facts:

    Supplier s1 suppliers part p1 to project pj1 Supplier s1 suppliers part p2 to project pj2 Supplier s2 suppliers part p1 to project pj2 Supplier s2 suppliers part p2 to project pj1

    ---

    In Model 1, we will have the following entity instances:

    Supplier Part Project ------------- ------------- ------------- s1 p1 pj1 s2 p2 pj2

    and the following relationship instances:

    Supply Uses Inventory ---------------- ---------------- --------------- (s1, pj1) (pj1, p1) (s1, p1) (s1, pj2) (pj1, p2) (s1, p2) (s2, pj1) (pj2, p1) (s2, p1) (s2, pj2) (pj2, p2) (s2, p2)

  • Schematically:

    ---

    ---

    In Model 2, we will have the same entity instances:

    Supplier Part Project ------------- ------------- ------------- s1 p1 pj1 s2 p2 pj2

    But the relationship instances are different:

    Supply -------------------- (s1, p1, pj1) (s1, p2, pj2) (s2, p1, pj2) (s2, p2, pj1)

  • Schematically:

    

    ---

    ---

    ---

  • The Difference in the models

    The "Supply" relationship in Model 1 represents the fact that "a (specific) supplier is supplying ("has") to a (specific) project" (with any part): The "Supply" relationship in Model 2 represents the fact that:   a (specific) suppliers is supplying a (specific) part to a (specific) project": This model captures the above description !!!

    ---

    ---

    ---

    ---

  • Which model you need to use depends on what you want to represent !!!

    But DO be aware that "drawing an extra line" in a ER-diagram has profound consequences....

  ---

  ---

  ---

  ---

• Power of a ternary relationship
  • Power of a model (= representation):

    A model is used to represent information on the reality A model (= representation) is more powerful if it can store more items of information

  • Fact:

    A ternary relationship can store more information items than any (fixed) number of binary relationships Explanation: Suppose there are N entity instances stored in each of the entity types supplier, part and project: The maximum number of information items stored in the binary relationship Supply between Supplier and Project is N2: This is also true for the other 2 binary relationships. Therefore, the maximum number of information items stored this model: is at most 3 × N2 On the other hand, the maximum number of information items stored in the ternary relationship Supply between Supplier, Part and Project is N3: Since N3 > 3 × N2, one ternary relationship can store more information items than any (fixed) number of binary relationships

  ---

  ---

  ---

  ---

• Importance of using the most suitable data model
  • Suppose we want to store information on:

    Which supplier can supply which part Which part is used in which project Which supplier supplies to which project

    we should use the following ER-model to represent the information:

    Reason:

    The content of relationships provide ready information to answers of questions such as: How many different parts does supplier s1 have in his inventory (Look in the Inventory relationship and count the number of occurences of s1)

  • On the other hand, answering

    How many different parts does supplier s1 have in his inventory

    is more complicated using the ternary relationship:

    You must remove duplicated facts when you count !!!

    (E.g.: (s1, p1, pj1) and (s1, p1, pj2) says the same thing with regard to the posed query: that supplier s1 has p1 in his/her inventory)

  ---

  ---

  ---

  ---

• Bottom line
  • Which model you use depends on what information you need to store

    ---

  • Always use the most suitable model (which may not be the most powerful model !!!)