How can anomalies be eliminated through normalization



The relation shown in the picture consists of the student number SNr, the surname and first name of a student, the student's tutor with abbreviation and email address as well as the student's points in the specified subject.

The relation contains redundant data, that is, data is unnecessarily stored multiple times. This can be seen most clearly in the tutor's email address. Redundancy must be avoided because it requires more storage space and in particular involves the risk of inconsistencies. Data are inconsistent if they are contradictory, for example if different email addresses are given to the tutor.

Change anomaly

If the name of the student Felix Meier is to be corrected in Felix Meyer and this is only done for the first entry, for example, the table then contains inconsistent data. One speaks of a change anomaly. The anomaly can occur because there are two records for Felix Meyer, one for English and one for mathematics. The two data records for Felix Meyer come from the fact that the table is not in the second normal form, because the name of a student is only dependent on the student number SNr, but not on the entire primary key of the table.

Deletion anomaly

If the data record of the student Anne Berger is deleted in the table, the data of the tutor Hillgärtner will also be deleted. This means that data is lost that should not actually be deleted at all. This is known as an erase anomaly. It stems from the fact that this table violates the third normal form, because the tutor data is transitively dependent on the student number. If the tutor data is stored in a separate tutor relation, the deletion anomaly cannot occur. If you were to delete Anne Berger's data record, the information that Hil is the abbreviation for her tutor would also be gone, but the data record about the teacher Hillgärtner would remain.

Insertion anomaly

If the new student Maria Brehm is to be entered in the table with the tutor Boos, no information can be given for the subject and the points. But since the attribute subject is part of the primary key in this relation, we then have an incomplete primary key. This is known as an insert anomaly. It comes from the fact that the relation does not satisfy the second normal form. In the second normal form, the partial dependencies are eliminated. The student could easily move into the student relation (SNr, Surname, first name, abbreviation).


Lesson-tutor relation
a) Enter a suitable primary key for the above relation.
b) Analyze the functional dependencies occurring in the relation and represent them graphically.
c) What is the difference to the lesson-teacher relation in the chapter normal forms?
d) normalize the relation, d. H. break them down into relations that satisfy the third normal form.
e) Give reasons that the above anomalies can no longer occur after normalization.

Fruit merchant
The following relation is given for a fruit dealer.

In relation to this relation, give an example for the change, delete and insert anomaly.

Last modified: 2014/05/01 13:18 by