Consider the relation schema PATIENT_VISIT (Patient, Hospital, Doctor) and the relation instance given below:

In addition, suppose the following semantic rules exist.
€¢ Each patient may be a patient in several hospitals.
€¢ For each hospital, a patient may have only one doctor.
€¢ Each hospital has several doctors.
€¢ Each doctor uses only one hospital.
€¢ Each doctor treats several patients in one hospital.
a. What is the minimal cover of functional dependencies that exists in
b. What is (are) the candidate key(s) of PATIENT_VISIT?
c. If there is more than one candidate key, select a primary key from among the candidate keys.
d. Based on the primary key chosen, what normal form violations exist in PATIENT_VISIT?

Transcribed Image Text:

Patient Hospital Methodist St. Luke's Methodist St. Luke's Doctor D. Cooley mith Lee Marks D. Cooley W. Lowe R Duke