Database Reference
In-Depth Information
You must irst ind the rows that contain the value Lee as the value for FacultyName. In Table 3.1,
there are two rows with Lee as the faculty name. hen, you need to change the value Lee to Boyd
for the rows that contain the student with ID 11. his is called an update anomaly. For a large table,
you may end up changing the values in thousands of rows. A full table search has to be performed
to locate the rows that need to be updated. It is time consuming. Update anomalies can slow down
database performance signiicantly. You should avoid update anomalies as much as possible.
Insertion anomalies
: Suppose you want to add information about a new course called Internet
computing. You cannot do so unless a class has been scheduled for the Internet computing course,
and some students have already enrolled in at least one class of that course. Without the infor-
mation about students and classes, the new row cannot be inserted into the table. his type of
anomaly is called an insertion anomaly.
Deletion anomalies
: Suppose that you want to remove the student with ID 16 from the
table CLASS_REGISTRATON_INFO. After the row is deleted, you lose the course information
about Info-Systems and the information about the class 1004. his type of anomaly is called a
deletion anomaly.
With the knowledge of functional dependency, we can detect what causes a modiication
anomaly. he table CLASS_REGISTRATON_INFO has a combination key (StudentID,
Course, ClassID). hat is
(StudentID, Course, ClassID)
→
StudentName, FacultyID, FacultyName, Grade, Prerequisite
In the above functional dependency, the nonkey column FacultyID is determined by StudentID,
which is only a part of the combination key. Such a dependency is called a
partial dependency
. It
is also true that Prerequisite depends on Course, which is only a part of the combination key. With
the existence of two partial dependencies, when you modify the information about a student, you
must modify the corresponding course information and vice versa. To solve the problem, we should
break up the table by removing the data that are related to the course from the current table. After the
breakup, one table only contains the information related to courses, and the other one contains the
information related to students and classes. he two resulting tables are shown in Tables 3.2 and 3.3.
he COURSE table contains course names and prerequisites. he column Course is the pri-
mary key. Since no student is involved, duplicated rows are no longer needed to match a student
with multiple courses. For the table STUDENT_CLASS, the combination of StudentID and
ClassID can be used as the primary key. You can represent the two tables by the notation com-
monly used for relations as shown below:
STUDENT_CLASS(
StudentID
, StudentName, FacultyID, FacultyName, Grade,
ClassID
)
COURSE(
Course
, Prerequisite)
Table 3.2
COUR
SE Table
CourseName
Prerequisite
VB
None
Database
VB
E-Commerce
Database
Info-Systems
Database
