How to determine Cardinality Constraint

[kay21]

Hi everyone,

Can someone please explain how to determine cardinality ratio for a relationship.

For example;

In a database for a hospital there is an entity Doctor and Patient. One or more doctors will treat a patient.

So, is the cardinality ratio between Doctor and Patient is M:1 (one or more doctors will treat a patient) or M:N, many Doctor treats many Patient since there will be many patients in the hospital.

Thank you.

[griswolf]

In most hospitals it would be M:N of course. So you would have a doctor_patient table that associates any doctor with any patient.

What else does it need to store? (hint: Do you want a new row every time the doctor sees the patient?).

How do you handle patients who are doctors? ... and the answer is not 'carefully' ;)

[kay21]

Hi Griswolf,

There is a Medical_History table that is linked to the Patient table that will have an entry every time the patient is treated by the doctor.

So basically, the cardinality ratio is determined by how many instances are connected to instances in another table, correct?

Thanks.

[kay21]

Hi Griswolf,

Please comment on my real world entity identification and the resulting EER Diagram.

Tell me if I have represented all the entities and their cardinality ratios correctly in the EER Diagram.

Thank you

[kay21]

Hi Griswolf,

Please comment on my real world entity identification and the resulting EER Diagram.

Tell me if I have represented all the entities and their cardinality ratios correctly in the EER Diagram.

Thank you

[kay21]

Forgot to attached the Entity list.

EMPLOYEE (Employee_SSN, Employee_Name, Designation, Salary)

PATIENT (Patient_SSN, Patient_Name, Disease)

MEDICAL_HISTORY (Medical_History_ID, History)

CONTACT (Contact ID, Email, TelNo)

ADDRESS (Address ID, Street, City, State, Zip and Region)

DOCTOR (Doc_ID, Specialty)

NURSE (Nurse_ID, Department)

PRESCRIPTION (Prescription_ID, Medication_Name, Instruction)

WARD (Ward ID, Ward_Name, Main_Treatment, Bed_Number)

TREATMENT (Medical_History_ID, Treatment_ID, Treatment_Date, Treatment_Location, Fee, Result)

PHARMACY (Pharmacy_ID, Pharmacy_Name, Pharmacy_TelNo)

PATIENT_ADMISSION (Admission_ID, Admission_Date, Discharge_Date)

[debasisdas]

How you determine CONTACT and ADDRESS belongs to whom ?

Don't you think both should be merged into a single one.

[kay21]

I thought I'll have the primary keys of CONTACT and ADDRESS as foreign keys in PATIENT, EMPLOYEE and PHARMACY

How you determine CONTACT and ADDRESS belongs to whom ?

Don't you think both should be merged into a single one.

[debasisdas]

why not merge both address and contact, and add a flag to define the contact type .

[kay21]

Please elaborate. By merging do you mean both entities be represented as one entity like below.

ADDRESS_CONTACT (ADDCON_ID, Street, City, State, Zip, Region, Email and TelNo)

Please also explain what you mean by "add a flag to define the contact type"

why not merge both address and contact, and add a flag to define the contact type .

[debasisdas]

Yes.

You can add a filed contact_type and store values like 1,2,3...
1--contact is doctors
2--contact is a nurse
3--contact is a pharmacist.....

etc

[kay21]

So my ADDRESS_CONTACT entity would look like this

ADDRESS_CONTACT (ADDCON_ID, Street, City, State, Zip, Region, Email, TelNo, Contact_Type)

Could you please check if the rest of my cardinality ratios in the ER Diagram is correct.

Thank you.

Yes.

You can add a filed contact_type and store values like 1,2,3...
1--contact is doctors
2--contact is a nurse
3--contact is a pharmacist.....

etc

[griswolf]

This all looks pretty good to me.

I disagree with the idea of pushing a flag into contact information: A nurse can be a patient, a doctor can run a pharmacy as part of his clinic, and you don't want to have to store all the possible flags. Rather associate the contact type with the relationship, not the person: Between this person A and that person B, there is a relationship and that relationship is of type "patient-doctor" or whatever.

[kay21]

Thank you all for your inputs and for clearing my doubts