The Atlantic Hospital Group wants the following collection o

The Atlantic Hospital Group wants the following collection of 24 data items (and ONLY these data items) to be implemented in a database to manage its member hospitals.

Create a 3NF LDM for the Atlantic Hospital Group that satisfies the requirements listed below.

Blood Type, Date Admitted, Date of Birth, Doctor Name, Doctor\'s Office Phone,
Examination Date, Hospital ID, Insurance Co. Address, Insurance Company,
Meal Plan, Ordered By, Patient Address, Patient Name, Pints-Available,
Policy Number, Primary Doctor, Result, RH-Factor, Room Number,
Seq Number, Test Code, Test Date, Time of Exam, Time of Test


The following are the business rules, definitions, requirements and relationships between data items:

A: Since names are not unique, each patient is assigned a patient code consisting of a two letter ID for the particular hospital of the hospital group (e.g. \"MA\" for the Boston Massachusetts hospital) followed by a sequential identification number within that hospital (e.g. 010410). So, a patient in the Boston unit would have a patient code like \"MA010410\".

B: The hospitals have private rooms (1 patient), semi-private (2 or 3 patients), and wards (a lot of patients). Once assigned a room, the patient stays there for the entire hospital stay.

C: Each patient has a primary (main) doctor. Every morning, that doctor\'s office receives a list of all patients in the hospital for whom the doctor is the primary physician. Assume that Doctor names are unique.

D: To keep an adequate supply of blood on hand, the hospital\'s blood bank tracks the kind of blood needed by each patient. Blood is classified by a blood type (\"A\", \"B\", \"AB\", or \"O\") together with an RH factor (positive or negative). A person has only one class of blood, e.g. \"O positive\". For each class of blood (type with RH), they track the number of pints that are immediately available in the hospital.

E: Upon check-in to the hospital, each patient is asked for evidence of health insurance coverage. The hospital administration wants a daily report listing, by insurance company, each patient\'s code and name, the patient\'s insurance policy number, and the date of admission to the hospital.

F: During the course of the hospital stay, the patient may be seen by the primary doctor and various specialists (also doctors). The hospital maintains record of all patients a doctor sees. The hospital also tracks all doctors that examine a patient and when each examination occurred. More than one doctor can see a patient at the same time (in the same examination).

G: A doctor may examine the same patient many times during a hospital stay, even on the same day.

H: In cooperation with the patient\'s doctors, the hospital dietitian develops a meal plan (diet) for each patient. Meal plans are recorded by the patient\'s code. Since the allowed food can change frequently as a patient\'s condition changes, the meal plan should be stored separately from other patient information. To avoid mistakes, only one meal plan is stored for a patient in the computer, and old meal plans are filed on paper off-line.

I: When examining a patient, a doctor can order laboratory tests to be performed. The doctor specifies the standard test code for each test needed. An instance of a test applies to one patient. The resulting lab report for each test includes the patient, the doctor who ordered the test, the specific examination when the doctor ordered the test, when the test was performed (which is usually different than when the examination occurred), and the result of the test.

J: A patient may be given the same kind of lab test many times during the hospital stay, even on the same day. (Each test instance must be separately ordered by a doctor.) It is vital to the health of the patient that the doctors are able to tell all test results apart. (Mixed test results can endanger the patient and quickly lead to malpractice lawsuits.)

Note: - Do not create any data items or write any ideas into this problem that are not stated here explicitly.

- Be sure that your solution satisfies all of the statements A through J above.

- Provide a solution with your finished 3NF LDM solution (i.e. entities like
\"Entity1 ((keydata1,keydata2)(K),nonkey1,nonkey2,...)\" and       
relationships like \"Verb: Entity1 1:M Entity2\" ), with one Entity or Reln per line.

Solution

3NF Logical Data Model

Entities

Relationships