Exercise 2 Example: Split tables
The following flat table includes two entities Major and Student. Data fields of Major and
Coordinator provide information about the entity of Major. The data field of Major uniquely
identifies a Major. Data fields of Student ID, Student Name and GPA provide information about
the entity of student. The data field of Student ID uniquely identifies a student. Students are
allowed to take only one major while a major can be taken by many students.
Major Coordinator Student ID Student Name GPA
FINC Dr. F S031 Adam 2.8
FINC Dr. F S059 Ben 3.7
FINC Dr. F S111 Cao 3.1
FINC Dr. F S213 Don 2.5
MGMT Dr. M S621 Eva 3.7
CSCI Dr. C S261 Flower 3.2
CSCI Dr. C S703 Glen 2.1
CSCI Dr. C S771 Han 3.1
CSCI Dr. C S909 Ivan 3
(1) Split the table to reduce redundancy.
(2) Calculate the efficiency.
Answer:
(1)
Table: Major
Major Coordinator
FINC Dr. F
MGMT Dr. M
CSCI Dr. C
Table: Student
Major Student ID Student Name GPA
FINC S031 Adam 2.8
FINC S059 Ben 3.7
FINC S111 Cao 3.1
FINC S213 Don 2.5
MGMT S621 Eva 3.7
CSCI S261 Flower 3.2
CSCI S703 Glen 2.1
CSCI S771 Han 3.1
CSCI S909 Ivan 3
(2)
Efficiency = 42/45 = .9333
Exercise 2: Split tables
The following flat table includes two entities Major and Student. Data fields of Major and
Coordinator provide information about the entity of Major. The data field of Major uniquely
identifies a Major. Data fields of Student ID, Student Name and GPA provide information about
the entity of student. The data field of Student ID uniquely identifies a student. Students are
allowed to take more than one major while a major can be taken by many students.
Major Coordinator Student ID Student Name GPA
FINC Dr. F S031 Adam 2.8
FINC Dr. F S059 Ben 3.7
FINC Dr. F S111 Cao 3.1
FINC Dr. F S213 Don 2.5
MGMT Dr. M S621 Eva 3.7
CSCI Dr. C S261 Flower 3.2
CSCI Dr. C S703 Adam 2.1
CSCI Dr. C S771 Han 3.1
CSCI Dr. C S909 Don 3
Split the table to reduce redundancy.