VERY IMPORTANT : -- Do not create any data items or write any ideas into this pr
ID: 3867399 • Letter: V
Question
VERY IMPORTANT:
-- 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 requirements A through J below.
What to submit:
-- Provide either a text file or Word doc file with your finished 3NF LDM solution in the email/text format (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.
-- Please do not submit any bubble chart or E-R diagram.
===========================================
The New England Hospital Group wants the following collection of 25 data items (and ONLY these data items) to be implemented in a database to manage its member hospitals.
Blood-Type, Date-Admitted, Date-of-Birth, Doctor-Name, Doctor's-Office-Phone,
Examination-Date, Hospital-ID, Insurance-Co.-Address, Insurance-Company,
Lab-Technician, Meal-Plan, Ordered-By, Patient-Address, Patient-Number,
Patient-Name, Pints-Available, Policy-Number, Primary-Doctor, Result,
RH-Factor, Room-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 patient 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), which lab technician completed the test, 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.)
Explanation / Answer
PATIENT(Patient-Number(K),Patient-Name,Patient-Address,Room-Number,Date-Admitted,Policy-Number,Date-Of-Birth,Examination-Date,Primary-Doctor)
HOSPITAL(Hospital-ID(K),Blood-Type,RH-Factor,Pints-Available)
TEST(Test-Code(K),Test-Date,Time-Of-Test,Result,Ordered-By,Lab-Technician,Time-Of-Exam)
DIET(Patient-Number(K),Meal-Plan)
INSURANCE(Insurance-Company(K),Meal-Plan)
DOCTOR(Doctor-Name(K),Doctor's-Office-Phone)
ADMIT : HOSPITAL 1:M PATIENT
EXAMINE : DOCTOR 1:M PATIENT
APPLY : TEST 1 : 1 PATIENT
ORDERBY : TEST 1: 1 PATIENT
TELL: DOCTOR 1: 1 TEST
PLAN : PATIENT 1: 1 MEAL-PLAN
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.