---

Analyzing the relationships in the Company ER-model

---

• Properties the Relationships in the Company ER-model
  • When we analyze the relationships, we must determine:

    The Cardinality Ratio constraints on the binary relationships Recall that cardinality ratio constraints is only valid on binary relationships The Participation constraints

    ---

  • Next Step in the ER-design process:

    Examine each relationship Determine its constraints

    This is an important step in designing the database because it will help use dictate WHERE (which file) to store the attribute that is used to represent the relationship.

  ---

  ---

  ---

  ---

• Constraints on the Manager relationship
  • Cardinality Ratio:
    1. ONE (manager) employee can manage at most ONE departments
    2. ONE deparment has ONE (manager) employee

    Employee:Department = 1:1

    ---

  • Participation Constraints:
    1. deparment: a department must have a manager employee ( total )
    2. employee : an employee need NOT manage any department ( partial)

    ---

  • Summary:

  ---

  ---

  ---

  ---

• What exactly is a "relationship" --- (a subset of cartesian product !!!)
  • I noticed that many students fail to comprehend what is exactly depicted by the relationship diagram:

    What exactly is represented by the squares inside the Manager set ???

    ---

    ---

  • How do we represent the Manager relationship:

    Suppose we have 3 employees and 2 departments: Suppose that: John manages the research department james manages the payroll department         Graphically, we draw lines in the diagram: Recall that a relationship is a subset of the cartesian product !!! So in reality, there a no lines !!! We actually have the following:

  ---

  ---

  ---

  ---

  ---

• Attributes of a Relationship

  • Yes, relationships can have attributes

  • Consider the attributes
    • Manager start date and

    in the description below that are highlighted with magenta:

    Departments Department Name (key attribute) Department number (key attribute) Manager (simple attribute) Manager start date (simple attribute) Locations (multi-valued attribute) Controlled Projects (multi-valued attribute) Projects Project Name (key attribute) Project number (key attribute) Location where project is performed (simple) Employees Employee Name (compound attribute - FName, MI, LName) SSN (key attribute) Address (compound attribute) Salary (simple attribute) Sex (simple attribute) Birth Date (simple attribute) Department (simple) Projects worked on by employee (multi-valued composite) {(Project, Hours)} Supervisor (simple) Dependents Employee (simple) Dependent First Name (simple attribute) Sex (simple attribute) Birth Date (simple attribute) Relationship to employee (simple attribute)

    ---

  • The Manager start date attribute provides some information about:
    relationship between a department and a employee

    (information is: begin date of the relationship)

    ---

  • The manager relationship:

    The dates attached to the relationship instances (squares) are the "starting date" of manager.

    ---

    ---

  • Note:

    The diagram above depicts the following sets:

  ---

  ---

  ---

  ---

• The constraints on the Control-project relationship
  • Cardinality Ratio:
    1. ONE deparment controls N project
    2. ONE project is controlled by ONE departments

    Department:Project = 1:N

    ---

    Comment:

    A trick that I use to decipher the ratio:

    ---

  • Participation Constraints:
    1. project : a project must have a controlling department ( total )
    2. department : a department need NOT manage any project ( partial)

    ---

  • Result:

  ---

  ---

  ---

  ---

• The constraints on the Works-for relationship
  • Cardinality Ratio:
    1. ONE employee works for ONE department
    2. ONE department has N employees

    Employee:Department = N:1

    ---

  • Participation Constraints:
    1. employee: a employee must have a "work for" department ( total )
    2. department : a department need NOT have any employees ( partial)

    ---

  • Result:

  ---

  ---

  ---

  ---

• The constraints on the Works-on relationship
  • Cardinality Ratio:
    1. ONE employee works on N projects
    2. ONE project is worked on by N employees

    Employee:Project = M:N

    ---

  • Participation Constraints:
    1. employee: a employee need NOT work on any project ( partial )
    2. project : a project need NOT have any employees working on it yet ( partial)

    ---

  • Result:

    ---

    NOTE:

    • Recall that the hour attribute provides some information about:
      relationship between a employee and a project

      (information is: duration of the relationship)

  ---

  ---

  ---

  ---

• The constraints on the Superviser relationship
  • This is something new:

    The Supervisor relationship is an relationship between two entities from the SAME entity set

  • To distinguish the two different entities , we assign them different roles :
    1. One employee is in the Supervisor role
    2. The other employee is in the Supervisee role

    ---

  • Cardinality Ratio:
    1. ONE supervisor employee supervises N employees
    2. ONE supervisee employee has ONE supervisor employee

    Supervisor Employee:Supervisee employee = 1:N

    ---

  • Participation Constraints:
    1. supervisor role: a employee need NOT manage any employee ( partial )
    2. supervisee role : a employee must have a supervisor ( total)

    ---

  • Result:

  NOTE: this is an example of a recursive relationship.

  ---

  ---

  ---

  ---

  ---

  ---

  ---

  ---

• The constraints on the Care-for relationship
  • Cardinality Ratio:
    1. ONE employee has N dependents
    2. ONE dependent belongs to ONE employees

    Employee:Dependent = 1:N

    ---

  • Participation Constraints:
    1. employee: a employee need NOT have any dependent ( partial )
    2. dependent : a dependent must belong to a employee ( total)

    ---

  • Result:

  ---

  ---

  ---

  ---

• Summary of the findings
  • The following diagram summarizes the findings above: