Skip to main content

ER diagram: hospital management

Short answer

Entities: Staff (overtime, rate) ISA Doctor and HA; Patient (req_treatment); Ward; Drugs. Relationships: Doctor assigned to Patient (1:N), HA attends Patient (M:N), Patient treated in Ward (N:1), Ward associated to HA (1:N), Patient takes Drugs (M:N) with attributes no_of_time, length.

Draw an ER diagram for a hospital. [The question text is not printed; the page gives only the label and the solution diagram: Staff (attributes overtime, rate) is specialized by ISA into HA and Doctor; part-time staff are paid; Doctor is assigned to Patient; HA attends Patient; Patient (attribute req_treatment) is treated in a Ward; Ward is associated to HA; Patient takes Drugs (attributes no_of_time, length).]

The hospital ER diagram uses a specialization (ISA) hierarchy and several relationships with attributes.

Entities and attributes

  • Staff (staff_id, name, overtime, rate): part-time staff are paid by rate.
  • HA (health assistant) and Doctor: subclasses of Staff (ISA).
  • Patient (patient_id, name, req_treatment).
  • Ward (ward_no, name).
  • Drugs (drug_id, name).

Relationships

  • Doctor is assigned to Patient (1 : N).
  • HA attends Patient (M : N).
  • Patient is treated in Ward (N : 1).
  • Ward is associated to HA (1 : N).
  • Patient takes Drugs (M : N) with attributes no_of_time and length.

ER diagram

        (overtime)(rate)
              |
           [Staff]
              |
            (ISA)
            /   \
        [Doctor] [HA]-N--<associated>--1--[Ward]
           |       |                         |
           1       M                         1
      <assigned> <attends>              <treated_in>
           |       |                         |
           N       N                         N
           +----[Patient]--------------------+
                   |  (req_treatment)
                   M
                <takes>--(no_of_time, length)
                   |
                   N
                [Drugs]

Staff attributes are inherited by both Doctor and HA, so they are stored once in Staff.

✓ Answer checked by Claude Opus 5.5 (high effort)

Related questions

All Database Management System chapter 2 questions →