Database Anomalies

Database anomalies are inconsistencies in data caused by operations such as updates, insertions, or deletions. Inconsistencies can occur when a record is held in numerous locations and not all copies are updated.

Anomalies in a database can be caused by excessive redundancy or poorly built tables.

Type of Anomalies in DBMS

• Insertion Anomalies
• Deletion Anomalies
• Update Anomalies

Insertion Anomaly
This occurs when specific data cannot be added into a database due to the presence of other unnecessary data. For example, suppose a new student wants to be added to a database but they are not yet enrolled in any courses, and the database architecture requires a course enrollment for each student record.

Deletion Anomaly 
Occurs when deleting data unintentionally causes the loss of other valuable data. For example, if a course is deleted from a database, which removes all students affiliated with that course, vital student information may be lost.

Update Anomaly
Occurs when a single data alteration needs to be performed numerous times to ensure consistency. For example, if an employee's address is recorded in numerous tables, altering the address necessitates amending it in each table where it appears, potentially leading to inconsistencies if any instance is overlooked.

Functional Dependency

The functional dependency is a relationship that exists between two attributes. It typically exists between the primary key and non-key attribute within a table.

X   →   Y 

The left side of FD is known as a determinant, the right side of the production is known as a dependent.

For example:

Assume we have an employee table with attributes: Emp_Id, Emp_Name, Emp_Address.

Here Emp_Id attribute can uniquely identify the Emp_Name attribute of employee table because if we know the Emp_Id, we can tell that employee name associated with it.

Functional dependency can be written as:

Emp_Id → Emp_Name   
We can say that Emp_Name is functionally dependent on Emp_Id.

Types of Functional dependency

1. Trivial functional dependency

• A → B has trivial functional dependency if B is a subset of A.
• The following dependencies are also trivial like: A → A, B → B

Example:

• Consider a table with two columns Employee_Id and Employee_Name.  
• {Employee_id, Employee_Name}   →    Employee_Id is a trivial functional dependency as   
• Employee_Id is a subset of {Employee_Id, Employee_Name}.  
• Also, Employee_Id → Employee_Id and Employee_Name   →    Employee_Name are trivial dependencies too.  

2. Non-trivial functional dependency

• A → B has a non-trivial functional dependency if B is not a subset of A.
• When A intersection B is NULL, then A → B is called as complete non-trivial.

Example:

• ID   →    Name,  
• Name   →    DOB