HN - Health Network
A hospital has id code, name, address, main phone number, and number of beds in the
hospital. For each hospital we need to know all its wards, doctors and all other staff, and
laboratories the hospital uses.
For each ward we store its id code, name, and number of beds in the ward.
It is essential to represent the information on staff of a given ward and all its patients.
Each employee is represented by employee number, name, duty (intern, nurse, etc.), shift
(morning, afternoon, night). For doctors we additionally store doctor number, area of
specialization, and the list of patients the doctor is actively attending.
For each patient we must know registration number, bed number in the ward, name, address,
birthdate, sex, SSN, all attending doctors, all tests with results, and diagnoses (multiple
conditions can be diagnosed for the same patient), any complications uncovered, and
precautionary information such as drug reactions or allergies, and treatment description
(medication name, dose, day/time given.)
Each diagnosis made for patients has a standard identifying code, standard name and a
statement of diagnosis which may involve details that differ from one patient to another.
A laboratory has lab number, name, address, and main phone number. Laboratories are not
parts of hospitals but every hospital contracts with some labs; possibly many labs serve the
same hospital, and one lab possibly serves several hospitals. We do not store information
about lab employees.
Tests are standardized across all labs. A test has identifying code, general type of test (blood
test, urine test, tissue test, X-ray, MRI, etc.) and name (Chest X-ray, left collar bone X-ray, etc.)
date performed, time performed, specimen order number (if relevant), current status of the test,
the description of the result of the test (a string instead of an X-ray image) and for what patient
the test has been ordered.
TASKS
Draw an ER diagram (you can draw by hand but neatly). Underline primary keys.
Mark total/partial participations and mark 1-1, 1-many and many-many relationships as such.
State any additional assumptions you have made on issues which were not settled by the
description of HN above.