Question 3. Consider the following relation: R (Doctor#, Patient#, Date, Diagnos

Question

Question 3. Consider the following relation: R (Doctor#, Patient#, Date, Diagnosis, Treat-code, Charge) In this relation, a tuple describes a visit of a patient to a doctor along with a treatment code and daily charge. Assume that diagnosis is determined (uniquely) for each patient by a doctor. Assume that each treatment code has a fixed charge (regardless of patient). (i) What are the functional dependencies that can be inferred from the above? (ii) Is this relation in 2NF? Justify your answer and decompose if necessary. (iii) Argue whether further normalization to 3NF is necessary, and if so, perform it.

Explanation / Answer

The Answer for your question is given below clearly

From the question’s text, we can infer the following functional dependencies:

{Doctor#, Patient#, Date}->{Diagnosis, Treat_code, Charge}
{Treat_code}->{Charge}
Because there are no partial dependencies, the given relation is in 2NF already. This however is not 3NF because the Charge is a nonkey attribute that is determined by another nonkey attribute, Treat_code. We must decompose further:

R (Doctor#, Patient#, Date, Diagnosis, Treat_code)
R1 (Treat_code, Charge)

We could further infer that the treatment for a given diagnosis is functionally dependant, but we should be sure to allow the doctor to have some flexibility when prescribing cures

Hope This Helps, if you have any doubts Please comment i will get back to you, thank you and please thumbs up

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.