Hi all, this is actually an assignment I was given for my database paper. I would really appreciate some advice from someone on this forum.
Here is the ERD I created for my assignment:
http://i53.tinypic.com/m9vg38.jpg
The problem I have is the relationship between EMPLOYEE and ASSIGNMENT
What I have understood from the problem description is:
Employees may not be double booked (in relation to assignment)
Many employees can work on the same assignment
In the assignment entity, EMP_ID must be included (straight from the problem description)
So my problem is:
In order for EMP_ID FK value to be displayed in the ASSIGNMENT entity (at least in Visio) I have to have the relationship like this - EMP (1:M) ASSIGN
When I change the relationship to ASSIGN (1:M) EMP
The ASSIGN_ID appears as an FK value in the EMPLOYEE entity and nothing from EMPLOYEE appears in ASSIGNMENT.
The only thing that I can think of (as novice as I am) is to make the relationship (what it is supposed to be) ASSIGN (1:M) EMP
and just add an attribute in the ASSIGNMENT entity for EMP_ID (but wouldn't this mean that referential integrity wont be in action and the value is open to mistakes by the end user?)
Do I have any other options? Am I even looking at this relationship correctly, I don't know!
Thank you for taking the time to read my problem and once again, *any* advice is greatly appreciated.