Area: Databases

Language: SQL

Imagine you are hired as a consultant at a college administration office to design a relational database schema for them. They showed you a relational table they had designed based on a chart they were using to enter and maintain student records.

Student(student_number, stu_name, stu_phone, federalaid_code, ins_company, ins_class, primary_advisor_id, prim_adv_name, prim_adv_phone, visit_time_date, visit_advisor_id, diagnosis, treatment, fee)

When you looked at their sample data, you observed that the same student records would be inserted repeatedly in the current schema. So, you interviewed them and identified the functional dependencies as listed below.

student_number stu_name, stu_phone, federalaid_code, ins_company, ins_class, primary_advisor_id, prim_adv_name, prim_adv_phone

federalaid_code ins_company, ins_class

primary_advisor_id prim_adv_name, prim_adv_phone

{student_number, visit_time_date} visit_advisor_id, diagnosis, treatment, fee

(a) Design the schema of relational tables for storing these student-records, all tables in BCNF.

(b) The students' federal aid data and primary advisor data are not changed very often. Compromise the relational schema design accordingly so that the above mentioned queries run faster while you still do not have to store student records more than once. Show your modified schema diagram in the same manner as in the exercise a.