-1

I have the following statements to create my conceptual model:

  • EMPLOYEE belongs to one DEPARTMENT;
  • EMPLOYEE can work in many PROJECTs of his DEPARTMENT;
  • PROJECT is managed by only one DEPARTMENT;
  • DEPARTMENT can have many PROJECTs;

So 1 EMPLOYEE can work in N PROJECTs as long as those PROJECTs belongs to his DEPARTMENT. How can i guarantee that the EMPLOYEE and the PROJECT belongs to the same DEPARTMENT using a ternary relationship?

Making some test I found out that using a ternary relantionship I can have an EMPLOYEE in a PROJECT that does not belongs to the same PROJECT Example:

enter image description here

As shown in the image, the second and third line is allowed, but it is not valid considering that the PROJECT and the EMPLOYEE is from DEPARTMENT 1. The second line says that the EMPLOYEE is from DEPARTMENT 2 but it's previously define DEPARTMENT 1. The third line says PROJECT 1 is from DEPARTMENT 2 but it is also defined to be from DEPARTMENT 1 in the first line. What do i do to solve this problem?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Roni Castro
  • 1,968
  • 21
  • 40

2 Answers2

1

You said EMPLOYEE belongs to one DEPARTMENT and PROJECT is managed by only one DEPARTMENT but didn't model those requirements. Once you do, you can add foreign key constraints to WORK for (EMPLOYEE, DEPARTMENT) as well as (PROJECT, DEPARTMENT).

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • I thought it would be possible to do it in the conceptual model, but i believe this problem will be solved only when i create the constraints. Thks. – Roni Castro Jul 20 '15 at 01:15
-2

https://drive.google.com/file/d/0B9mDH6Q_ERUTNWZfaUx3RmpFSWEyOERmRnluaUprbUdUM2hJ/view?usp=sharing

It is the link for the ER diagram which I have drawn. The diagram corresponds to this question

  1. COMPANY has departments
  2. Department has name, number, manager Manager is an employee Manager has starting date Department has several locations
  3. Department controls projects Project has name, number, location
  4. Employee has name, social security, number, address, salary, sex, birthdate.
  5. Employee is in one department and works strictly on several projects belonging to that department
  6. Hours each employee works on each project
  7. Employee has supervisor
  8. Employee has dependents with name, sex, birthdate, and relationship to employee.
Subhra Mazumdar
  • 111
  • 3