( Database Design Normalization: functional dependency ) (1). Design the schema
ID: 3587250 • Letter: #
Question
(Database Design Normalization: functional dependency)
(1). Design the schema of relational tables for storing these patient-records, all tables in BCNF. Underline primary keys and show foreign keys using arrows.
(2). If patients' insurance data and primary physician data were changed very infrequently. Now, compromise the relational schema design accordingly so that the above mentioned queries run faster while you still do not have to store patient records more than once. Show your modified schema diagram.
patient-number-) pat-name, pat-phone, insurance-code, ins-company, ins-class, primary physician id, prim phys _name, prim phys phone insurance code ins-company, ins-class . * primary_physician_id -> prim phys_name, prim phys_phone patient _number, visit_time_date) -> visit physician_id, diagnosis, treatment, feeExplanation / Answer
given relational schema is:
Patient(patient_number, pat_name, pat_phone, insurance_code, ins_company, ins_class, primary_physician_id, prim_phys_name, prim_phys_phone, visit_time_date, visit_physician_id, diagnosis, treatment, fee)
Super key: The clouser of any attribute which retrieves all attributes exists in table is called super key.
By considering given Functional dependencies, Super key of given relation are:
{patient_number,visit_time_date}
So that minimal super key is {patient_number,visit_time_date} and also it is the candidate key.
Candidate key exixts in FD's, so that it is in BCNF but there are some other fd's violating rule of BCNF which are
{insurence_code}----> {insurence_code,ins_company,ins_class}
{Primary_physician_id}----->{prim_phys_name,prim_phys_phone}
By converting above relations into BCNF, the relational schema would be as follows:
Patient(Patient_number(pk),pat_name,pat_phone,insurence_code(fk),visit_time_date)
insurance(insurance_code(pk),ins_company_ins_class)
Primary_physician(Primary_physician_id(pk),prim_phys_name,prim_phys_phone,Patient_number(fk))
visi_physician(visit_physician_id(pk),diagnosis,treatment,fee,patient_number(fk))
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.