---

The relationships in the Company ER-model

---

• Our Preliminary Design Results (so far)
  • From the description of the company (see: click here ) we constructed the following preliminary model:

    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 First Name (simple attribute) Sex (simple attribute) Birth Date (simple attribute) Relationship to employee (simple attribute)

    What we have designed so far:

    Department( DName, DNumber, Managed-by(Employee, StartDate), {Locations}, Controls({Projects}) ) Project(PName, PNumber, Location) Employee(SSN, Name, Addr, Sal, Sex, BDate, Works-for(Department), Works-on({Proj, #hours}), Supervised-by(Employee), Cares-for({Dependent})) Dependent(FName, Sex, BDate, RelationToEmp) (Underlined attributes are key attributes)

  ---

  ---

  ---

  ---

• Identifying the relationships
  • After learning about relationships, you should now understand that:

    The "properties" highlighted in yellow (in the box above) will require another entity (I.e.: it involves another entity -- it is not a property that only belongs to one entity

  • In the ER model design:

    These "properties (or attributes)" are not attributes, but in fact they are: Relationships !!!!

    We must remove them from the entities:

    Department( DName, DNumber, Managed-by(Employee, StartDate), {Locations}, Controls({Projects}) ) Project(PName, PNumber, Location) Employee(SSN, Name, Addr, Sal, Sex, BDate, Works-for(Department), Works-on({Proj, #hours}), Supervised-by(Employee), Cares-for({Dependent})) Dependent(FName, Sex, BDate, RelationToEmp) Relationships removed: (they involves at least 2 entities) Department Managed-by Employee (started at StartDate) Department Controls Project Employee Works-for Department Employee Works-on Project (for #hours) Employee Supervised-by Employee Employee Cares-for Dependent // weak entity

    Note: I put the "StartDate" and "#hours" separately in quotes to highlight the entities that are involved in the relationships

    ---

    ---

    ---

    ---

  • The updated ER model:

    The entities in the ER-model has the follow attributes: Department( DName, DNumber, {Locations}) Project(PName, PNumber, Location) Employee(SSN, Name, Addr, Sal, Sex, BDate) Dependent(FName, Sex, BDate, RelationToEmp) Notice that every attribute pertains (belongs) to exactly (only) to that entity The relationships in the ER-model are: Manage(employee, department) --- employee manages a department (started at "StartDate") Control(department, project) --- department controls a project Works_for(employee, department) --- employee works for a department Works_on(employee, project) --- employee works on a project (for some #hours) Supervise(employeesupervisor, employeesupervisee) --- supervisor empl supervises a supervisee empl Care-for(employee, dependent) --- employee cares for a dependent

    Graphical representation: