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.
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.
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.
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).
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.
The following relation is given for a fruit dealer.
In relation to this relation, give an example for the change, delete and insert anomaly.
- What is the cutoff of ONGC
- Is the meta description an SEO factor?
- How can a Catholic marry a Witness
- There is honor among thieves
- What makes common ground in general
- What is comparable to chloroform
- Who creates websites like Flipkart Snapdeal Amazon
- What will you never understand about Americans
- Saturn's moon Enceladus has oxygen
- How do I secure my WeChat account
- Where do IT companies find their customers
- Mechanical engineering is an evergreen industry
- What are the three functions of roots
- What color are fences in Germany
- Would you prefer Yale to Harvard
- What scares me
- Why is my dryer not heating
- What other animals besides humans have wars?
- I can blog from my smartphone
- What is our national park
- What does the equipment of the dental laboratory include
- Are atheists more intelligent than Christians
- What does it mean to have a palpable uterus
- Is H2O2 polar or non-polar