Atlantic Hospital Group The Atlantic Hospital Group wants the following collection of 24 data items (and ONLY these data items) to be implemented in...

Hello I need to revise my solution according to Teacher's Comment. Please see the attached Assignment and see my solution provided and fix my solution to meet teacher's requirement according to his comment.

My solution:

3NF Logical Data Model

Entities

PATIENT (HospitalID, SeqNumber) (k), PatientName, PatientAddress, RoomNumber, PrimaryDoctor, DateOfBirth,

DateAdmitted, ExaminationDate, BloodType, RH-factor, PintsAvailable, TimeOfExam)

DOCTOR (DoctorName, DoctorOfficeNumber)

INSURANCE (PolicyNumber, InsuranceCompanyAddress, InsuranceCompany)

MEALPLAN (PatientID, PMealPlan)

TEST (TestCode, TestDate, TimeOfTest, Result)

Relationships

EXAMINE: PATIENT M:M DOCTOR

COVERAGE: PATIENT 1:1 INSURANCE

HAS: PATIENT 1:1 MEALPLAN

UNDERGO: PATIENT M:M TEST

ORDER: DOCTOR 1:M TEST

Teacher's comment below:

PATIENT ((HospitalID,SeqNumber)(k), PatientName, PatientAddress, RoomNumber, PrimaryDoctor, DateOfBirth, DateAdmitted, ExaminationDate, BloodType, RHfactor, PintsAvailable, TimeOfExam)

PintsAvailable is not a fact about any patient. Fails 3NF test.

You only allow any patient to be examined ONCE during the entire hospital stay.  Endangers patient health and safety.

DOCTOR (DoctorName, DoctorOfficeNumber)

The key of this entity is not marked.

INSURANCE (PolicyNumber,Insurance, CompanyAddress, InsuranceCompany)

The key of this entity is not marked.  It cannot be PolicyNumber because that is not in the control of the hospital and might not be unique.

Whose policy number is it?

MEALPLAN(PatientID,PMealPlan)

The key of this entity is not marked.

What is "PatientID"?  What is "PMealPlan"? 

TEST(TestCode, TestDate, TimeOfTest, Result)

Endangers patient health and safety.  No way to tell who was tested.

EXAMINE : PATIENT M:M DOCTOR

Not good enough.  Must be able to tell exactly when the patient saw which doctor(s).  Look closer at fact statement F.

COVERAGE: PATIENT 1:1 INSURANCE

Reconsider this after fixing the key of the Insurance entity.

HAS: PATIENT 1:1 MEALPLAN

Please never use "Has" as a reln verb again.  It is so generic that is has no meaning for the reader.  "Meal plan is:" would be better here.

UNDERGO: PATIENT M:M TEST

Not good enough.  Must be able to tell exactly which test any patient received, when it was given, who ordered it, and the result of that test.  Look closer at fact statement I.

ORDER: DOCTOR 1:M TEST

Okay

You have both a Doctor entity and a field PrimaryDoctor in the Patient entity.  Isn't the primary doctor a doctor?

Please fix and resubmit.