Normalisation is the process of splitting relations into well-structured relations that allow users to insert, delete, and update tuples without introducing database inconsistencies. Without normalisation many problems can occur when trying to load an integrated conceptual model into the Database Management System (DBMS). These problems arise from relations that are generated directly from user views are called anomalies. There are three types of anomalies: update, deletion and insertion anomalies.
|
Employee_ID |
Name |
Department |
Student_Group |
| 143 | H. Wright | Corporate Comms | Sci Phi Lib |
|
123 |
J. Longfellow |
Accounting |
dBeta Alpha Psi |
|
234 |
B. Rech |
Marketing |
Marketing Club |
|
345 |
B. Rech |
Marketing |
Management Club |
|
456 |
A. Bruchs |
CIS |
Technology Org. |
|
567 |
A. Bruchs |
CIS |
Beta Alpha Psi |
Update anomaly
An update anomaly is a data inconsistency that results from data redundancy and a partial update. For example, each employee in a company has a department associated with them as well as the student group they participate in.
If A. Bruchs’ department name has to change it must be updated at least 2 times or there will be inconsistent data in the database.
Deletion anomaly
A deletion anomaly is the unintended loss of data due to deletion of other data. For example, if the H. Wright leaves the company the Corporate Comms department would cease to exist. This is why all attributes should be fully dependent on the primary key.
Insert anomaly
An insertion anomaly is the inability to add data to the database due to absence of other data. For example, if a new department is formed you would need a new employee who worked in it before you could add the department.
Update, deletion, and insertion anomalies are very undesirable in any database. Anomalies are avoided by the process of normalization.