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.

Requirements analysis

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.

Recent legislative initiative foresees a number of provisions affecting the structure and organization of the database. One of these provisions stipulates that the database must record the insurance status of patients and distinguish between insured and uninsured. For the insured, the costs of hospitalization and clinical examinations are covered by the (more than one) insurance funds in which the patient may be insured at a given time and which must be recorded for each insured patient. For the uninsured patients, the costs of hospitalization and clinical examinations resulting from hospital visits are covered by a special purpose allowance / benefit that has a unique identification code and description. An uninsured person is entitled to the allowance / benefit as long as s/he has no other type of insurance, while the right to the allowance is abolished as soon as the uninsured person changes insurance status (i.e. by becoming insured in at least one fund). Especially in the case of uninsured patients who make use of the benefit, the database must record the total nursing expense that is progressively billed to the uninsured.

On the occasion of the changes to the database due to the legislative initiative, it was deemed appropriate to make two further amendments. The first concerns the recording of the supervision of doctors in a hospital who are in the stage of obtaining their specialty by other doctors in the same hospital who are already qualified.

For this purpose, each hospital should assign one or more interns to qualified doctors and this relationship should be recorded in the database. The strict condition that must be observed is that doctors obtaining their specialty can only have one supervising doctor.

Finally, in an attempt to evaluate the services provided, the legislative initiative foresees the possibility for patients to post comments on the internet regarding their experience during a visit to a hospital. These comments are recorded with a serial number and the content chosen by the patient.