Search code examples
sqldatabaseentity-relationshipcardinality

Cardinality of a relationship

I've created an Entity Relationship Diagram for a Database of an hospital where there is a relationship 'Work' between 'Doctor' and 'Department'. This relationship has the key attribute 'Date' in addition to the identifiers of the relations 'Doctor' and 'Department'.

The problem is that in general a doctor can work in more departments (for example cardiology, surgery, pediatrics...) but only in one a day (for example if the doctor in the date X works in cardiology, he can't work in other departments in the same date).

What is the cardinality of this relationship?

  • DOCTOR ---(1,1)--WORK--(1,N)--- DEPARTMENT OR
  • DOCTOR ---(1,N)--WORK--(1,N)--- DEPARTMENT

scilicet, the attribute 'Date' affects cardinality?

Solution

  • The cardinalities are the fololowing:

    DOCTOR ---(1,N)--WORK--(N,1)--- DEPARTMENT

    regardless of presence of date attribute.

    That is basically how (N,N) relationship containing extra attributes is presented using an extra entity (WORK in your case).

    The date attribute affects only the key of WORK entity, but not cardinalities of relationships.