This project requires you to create a database design. Your design will be documented in a set of Entity-Relationship diagrams. Draw a set of Entity-Relationship diagrams to model the following scenario.
OHIP wants to track its' doctors, patients and hospitals.
For the doctors, your design must be able to store:
- the doctor's unique license number
- the doctor's first name
- the doctor's last name
- the doctor's specialty
- the date the doctor became licensed
For the patients, your design must be able to store:
- the patient's unique OHIP number
- the patient's first name
- the patient's last name
- the patient's address
- the patient's date of birth
For the hospital, your design must be able to store:
- the hospital's name
- the city where the hospital is located
- the province where the hospital is located
- the number of beds in the hospital
- a unique hospital code number (an H followed by 3 digits, for example H001).
Finally, you need to store the following relationships:
- Depending on their specialty, a doctor will be given privileges in the any number of hospitals in their area. Not all of the area doctors will be given hospital privileges. A hospital will always have any number of doctors assigned privileges.
- When a patient is admitted to the hospital, they will always be assigned to a doctor. A patient
can be seen by more than one doctor and a doctor can have more than one patient that they are attending to. Since the assignments relate to the doctor's area of specialty, a doctor may not always have patients assigned to them. One doctor will be given the position of the Chief Doctor at the Hospital. A doctor will only be the Chief Doctor for one hospital at a time. Every hospital must have a Chief Doctor. Not all doctors in the area will be a Chief Doctor for a Hospital in the area. When a serious medical condition arises, a patient will be admitted to the closest hospital. A hospital may have many patients, but a patient will only be admitted to one hospital. All hospitals will have patients that have been admitted and are waiting to be treated. An individual is not considered a patient of the hospital unless they have been admitted to the hospital and are waiting to be treated.