Introduction

Proper table design is a fundamental part of any database implementation. Improperly designed tables and joins are a source of anomalies - incorrect or misleading data. The following are examples of some of these anomalies.

Insertion Anomaly

Given the following table, we can have problems when inserting new tuples. (Assume that the primary key is {studentId, course}).

The tuple noted as having incorrect data (Bob Snord) shows the problem of keeping consistency: Mr. Snord's first name is wrong in the new tuple. Because we are using redundant data we increase the possibility of error creeping in. We are also not entering the department information, and all courses should be related to a department.

The two tuples noted as invalid (Anthony Aardvark and NULL NULL) show the difficulties that arise when we do not yet have all of the data to hand, but the table design requires it. In the first case we add a new student who is not yet taking a course, in the second a course that does not yet have any students. The empty fields must be left as NULL. The problem that arises is that both studentId and course are part of the primary key, and primary keys do not allow NULLs. Thus the last two tuples could not actually be inserted into the table.

The implication of this anomaly is that the table is poorly designed. Student and course data are associated but independent data. Students exist without courses, courses exist without students. Assigning them to the same table is poor design.

Deletion Anomaly

Given the following table, we can have problems when deleting new tuples. (Assume that the primary key is {studentId, course}).

If we delete the last student from a course, we lose the course information. For example, if we delete Zachary Zzap from HP202, the course disappears.

As noted in the Insertion Anomaly, this problem arises due to having associated but independent data in the same table.

Modification Anomaly

When data is repeated, we must implement proper design to avoid generating inconsistencies. For example: if the course instructor for CP102 changes, we must change this information in more than one tuple. If we miss a tuple then the data is no longer valid.

As noted in the Insertion and Deletion Anomalies, this problem arises due to having associated but independent data in the same table.

Spurious Tuples

If a join loses information (not necessarily tuples - in fact, invalid tuples may be added through a join), it is an invalid join. Valid joins are called non-additive (lossless) joins.

The following is an example of a join that produces spurious tuples:

is joined to the following table by their common field instructor.

by the SQL statement

              
SELECT * FROM studentInstructor AS si
  INNER JOIN courseInstructor AS ci
  ON si.instructor = ci.instructor

resulting in:

The light-green tuples are spurious tuples, i.e. they contain invalid information that arises out of joining together tuples that do not belong together.

The spurious tuples arise out of this join because of the poor design of the source tables. Students take courses and should be associated with courses, not with instructors. Since instructors are also associated with courses - and potentially with multiple courses - the join between these tables produce tuples that have no existence in reality. (If each instructor only taught one course then this problem wouldn't arise, but that's not the reality.)

The fix is to redesign the tables to properly reflect the real-world association amongst the data. Students should be associated with courses, not instructors, and a studentCourse table joined with the courseInstructor table would not have these spurious tuples. Of course this fix would work only if each course has only one instructor. Courses that have multiple sections and instructors would require another redesign, this time with tables that relate courses, sections, students, and instructors. Keeping track of course sections offered in different terms would require yet another redesign that tracks the terms as well. Complex data requires complex database design.