Regional Hospital Information System case study

Consider that you are part of the development team for a regional hospital management information system. Your task is to develop the database of the information system. The database should record data about the regional hospitals that offer care to patients by carrying out clinical examinations (tests) that are diagnosed by doctors working at the hospitals.

Each hospital is represented in the database by a unique code (CODE_NOS) that uniquely identifies the hospital, the name of the hospital (NAME_NOS), the city in which it is located (CITY_NOS), the contact telephone number (PHONE_NOS) and the address of (ADDRESS_NOS).

Citizens come to the hospital and at their first visit they are registered by recording a unique code (CODE_ATH), last name (LAST_NAME_ATH), first name (NAME_ATH), an address (ADDRESS_ATH) and a contact phone number (PHONE_ATH). Each unique patient code (ASTH_CODE) is retained for all subsequent patient visits to the hospital. Each patient’s visit a hospital is recorded with a unique visit code (CODE_EPI) and the date of the visit (DATE_EPI) of the patient at the hospital. Obviously, hospitals receive many patient visits, each of which corresponds to a patient. Also, each patient may have made one or more visits to the hospital.

During patients visits to a hospital, one or more medical examinations (or tests) may be necessitated. Each test has a unique code (CODE_EXE) and a description (DESCRIPTION_EXE). Examples of tests are 'GENERAL BLOOD TESTS', 'ARTHROSCOPY', 'EEG', etc. When the test results (RESULT_EXE) of a patient are known, they are recorded in the database and based on these results the patient is diagnosed. A diagnosis has a unique code (DIA_CODE) and a description (DIA_DESCRIPTION) and it is linked to a patient visit to the hospital. Moreover, diagnoses are made by doctors working in the hospital. When a specific diagnosis is registered for a visit, the date of the diagnosis (DATE_DAY) and some comments (COMMENTS_DAY) must be recorded. Indicative examples of diagnoses are 'CHOLESTEROL-TRIGLYCERIDES', 'ARTHRITIS', 'NON-SHORT-CIRCUITED NEURONS', etc. Finally, for the doctors working in the region's hospitals, the unique code (CODE_IAT_CODE), the last name of the doctor (SUNAMED_IAT), his name (NAME_IAT), an address (ADDRESS_IAT) and his contact telephone number (PHONE_IAT). It should be noted that a doctor can work in only one hospital in the district, but a hospital can employ many doctors.