Search code examples
mysqlsqldatabaseentity-relationshipcardinality

Can someone explain this cardinality participation constraint to me?

I'm reading my database book and it has this diagram:

enter image description here

I don't understand the second diagram, for "Employee - Works For - Department".

Is this diagram saying that at a MINIMUM only 1 employee, and a MAXIMUM of 1 employee can work for a MINIMUM of 1 department, and a MAXIMUM of many departments?

That doesn't make sense. Wouldn't this constrain of (1,1) limit the department to only having 1 employee working in it? Shouldn't it be this instead:

Employee (1, N) (1,N) Department

If someone can explain this to me, that would be great. I suspect I am reading this wrong.

Solution

  • In the second diagram, (1,1) indicates that each EMPLOYEE must appear once and only once in the relation. Since each tuple in the relation associates one EMPLOYEE with one DEPARTMENT, if an EMPLOYEE appears only once in the whole relation, they will be associated with only one DEPARTMENT in total. Hence, it means each EMPLOYEE works for a single DEPARTMENT.

    The (1,N) constraint indicates that each DEPARTMENT must appear at least once in the relation, but can appear multiple times. Each appearance will be associated with an EMPLOYEE. In other words, it means a DEPARTMENT must have at least one, but can have multiple EMPLOYEEs.

    For completeness, let me put the first diagram into the same terms. Each EMPLOYEE can appear once at most, which means an EMPLOYEE can manage one DEPARTMENT or none. Each DEPARTMENT must appear exactly once, so each DEPARTMENT must be managed by a single EMPLOYEE.