Help with Database Design Assignment

[kay21]

Hi everyone,

I need some help with identifying real world entities and their attributes for a hospital scenario. I have listed below the entities and attributes that I could think of. Please comment or give suggestions on how I can improve it.

EMPLOYEE (<strong>Employee_SSN</strong>, Address_ID, Contact_ID,  Employee_Name, Designation, Salary)

PATIENT (<strong>Patient_SSN</strong>, Address ID, Contact_ID  Patient_Name, Disease)

MEDICAL_HISTORY (<strong>Patient_SSN</strong>, Treatment_ID, History)

CONTACT  (<strong>Contact ID</strong>, Email, TelNo)

ADDRESS (<strong>Address ID</strong>, Street, City, State, Zip and Region)

DOCTOR (<strong>Doc_ID</strong>, Doc_Name, Department)

PRESCRIPTION (<strong>Prescription_ID</strong>, Patient_SSN, Medication_Name, Prescription_Type, Frequency, Instruction, Expiry_Date, Stock)

WARD (<strong>Ward ID</strong>, Ward_Name, Main_Treatment, Nurse_In_Charge, Number_Of_Bed)

TREATMENT (<strong>Treatment_ID</strong>, Treatment_Type, Treatment_Date, Treatment_Location, Fee, Result)

PHARMACY (<strong>Pharmacy_ID</strong>, Pharmacy_Name, Pharmacy_Location, Pharmacy_TelNo)

PATIENT_ADMISSION (<strong>Patient_SSN</strong>, Ward_ID, Admission_Date, Discharge_Date, Admission_Reason, Employee_SSN)

I need to also identify a multi-valued attribute, a weak entity, recursive relationship, Super/ subtype entities, a ternary relationship, relationship with cardinality ratio 1:1, 1:M, N:M.

So far I have only identified a multi-valued attribute which is Pharmacy_TelNo in PHARMACY, a weak entity which is MEDICAL_HISTORY and a recursive relationship which is Nurse_In_Charge in EMPLOYEE. As for the other I have not identified them yet.

Please have a look and tell me if what I have done so far is correct.

Thank you so much

Regards.

[Momerath]

Employee, patient, doctor and pharmacy all 'have an' address, so I'd just have an address ID in their tables linking to the address table.

I would have admission -> treatment be a 1:M relationship, and History be a 1:M to admission. Prescription would be Treatment -> Prescription in a 1:M.

A doctor is an employee and either could be a patient.

An N:M relationship would be patient to address. A patient might have multiple addresses (home, work, vacation home, moved, etc.) and multiple people might live at the same address.

[kay21]

Thank you Momerath for your help.

Can I link the Doctor entity to Employee entity since Doctor would be an Employee of the hospital?

DOCTOR (Doc_ID, Employee_SSN, Doc_Name, Department)

Should I remove the Doc_name attribute since the Employee_SSN would have the name attribute?

Employee, patient, doctor and pharmacy all 'have an' address, so I'd just have an address ID in their tables linking to the address table.

So there is no 1:1 relationship in this design?I would have admission -> treatment be a 1:M relationship, and History be a 1:M to admission. Prescription would be Treatment -> Prescription in a 1:M.

A doctor is an employee and either could be a patient.

An N:M relationship would be patient to address. A patient might have multiple addresses (home, work, vacation home, moved, etc.) and multiple people might live at the same address.

[Momerath]

Yes, I'd remove name since it's just duplicate data (from Doctor).

There could be several 1:1s. Doctor to treatment. Patient to Ward. Nurse in charge to ward. It depends on how you want your hospital to work.

[kay21]

Ok, if I want to have a 1:1 relationship between Nurse_In_Charge and WARD then I would need to make Nurse_In_Charge an entity and remove it from WARD, because currently Nurse_In_Charge is an attribute in WARD. Is that assumption correct?

Yes, I'd remove name since it's just duplicate data (from Doctor).

There could be several 1:1s. Doctor to treatment. Patient to Ward. Nurse in charge to ward. It depends on how you want your hospital to work.

[Momerath]

Yes