Question

1. Consider the relation Treatment and FDs below. Describe, with examples, two potential issues that can arise with this design. (10 pts) Treatment(doctorID, doctorName, patientID, diagnosis) doctorID ->doctorName doctorID, patientID -> diagnosis 2. Prove that every two-attribute relation is in BCNF. (10 pts) 3. Prove that if relation R is in 3NF and every key is simple (i.e., a single attribute), then R is in BCNF. (10 pts)

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

Question 1:

Consider a relation given in the question

Treatment(doctorID,doctorName,patientID,diagnosis)

doctorID->doctorName

doctorID,patientID->diagnosis

• Here suppose a doctor can diagnosis a patient couple of times then every time need to insert a doctor name due to poor database design and that is not necessary.
• because doctorName is dependent on doctor ID and hence all the doctors details need to be stored in the separate table called as doctor.
• Also if want to find out the history of a patient about diagnosis date wise or year wise then its not possible because there no such a column like date which can store the date on which patient is diagnosed.
• The above treatment table if wants to be in the normalized form then below tables can be formed like
  • Patient :This table stores patient details like PatientID,PatientName,address,phone etc.
    • Schema :Patient (PatientID,PatientName,address,phone)
  • Doctor :This table stores doctor details such as DoctorID and DoctorName
    • Schema :Doctor(doctorID,doctorName)
  • Treatment :This table stores treatment details such as doctorId,patientId,diagnosis and date.
    • Schema :Treatment(doctorID,patientID,diagnosis,date)

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.