Question

DRAW AN ER DIAGRAM TO MAP IT OUT:

The Problem: The database you design should be for the following scenario: OHIP wants to keep track of it's doctors and patients and hospitals. For the doctors, you must keep track of the doctor's license number. The license number is 4 character alphanumeric, for example 4RG2. Also, you must keep track of the doctor's first name (up to 20 letters), last name(up to 20 letters), specialty (up to 30 letters) and the date they became licensed. For the patients, keep track of the patients OHIP (9 digits) number, first name (20 letters) and last name(20 letters). For the hospitals, keep track of the hospitals name (up to 20 letters), city (up to 20 letters), province (2 letters) and number of beds. Each hospital has a unique hospital code assigned by the OHIP office that is 3 letters long. Finally, you need to keep track of the following information: • What hospital does the doctor work out of (a doctor can only work out of one hospital and MUST have a hospital assigned to him/her) • Which patient is assigned to which doctor. A patient could be seeing more than one doctor but must have at least one doctor. A doctor has many patients. Some doctors might not have any patients. One doctor is the head of a hospital, every hospital has one current head doctor, keep track of the current head doctor and the date that the doctor started at head. A doctor, if he/she is head, can only be head of one hospital.

Answer 1

(Pnumber) (Pfname) da li toy de Arame des Jame Pinang Patient Assigned to Doctor speciality (doclicanum code licit work Hale Hcode) (Krame) Hermes Gty (Province) Hisdale (Hulic num

Attribute Used for Patient table

P_number : Patient Number(PRIMARY KEY)

P_fname : Patient First name.

P_lname : Patient last name

doc_lic_num : Doctor Licence number.

Attribute Used for Doctor table

doc_lic_num : Doctor Licence number.(PRIMARY KEY).

doc_fname : Doctor First Name

doc_lname : Doctor last Name

speciality : Speciality

lic_date : Doctor Licence Date

H_Code : Hospital Code.(Foreign Key)

Attribute Used for Hospital table

H_Code : Hospital Code.(Primary Key)

H_name : Hospital Name.

city : City

province : Province

no_of_beds : Number of Beds.

H_Sdate : Head Start Date

H_lic_num : Head Licence Number.