Database Anomalies and Dependencies

Functional, Multivalued and Join Dependencies

Learning Objectives

Understand different types of anomalies that occur in poorly designed databases
Learn about Functional Dependencies and their properties
Explore Multivalued Dependencies and their significance
Understand Join Dependencies and their role in database design
Apply knowledge to identify and resolve database design issues

Database Anomalies

Database anomalies are problems that occur in poorly designed databases due to redundancy and inconsistent dependencies. These anomalies can lead to data inconsistency, storage waste, and maintenance difficulties.

                Key Point: Anomalies arise when we try to store multiple types of information in a single table without proper normalization.
            

Types of Anomalies:

Insertion Anomaly - Cannot insert data without having other irrelevant data
Deletion Anomaly - Deleting a row causes loss of other important information
Update Anomaly - Updating information requires multiple changes, risking inconsistency

Insertion Anomaly

Definition

An insertion anomaly occurs when we cannot insert certain information into the database without also having to insert irrelevant or unknown information.

Example: Student Course Enrollment Table

Student_ID	Student_Name	Course_ID	Course_Name	Instructor
101	Alice Smith	CS101	Database Systems	Dr. Johnson
102	Bob Wilson	CS102	Data Structures	Dr. Brown

Problem: We cannot add a new course (CS103 - Algorithms, Dr. Davis) until at least one student enrolls in it. Similarly, we cannot add student information without enrollment data.

Deletion Anomaly

Definition

A deletion anomaly occurs when deleting a row results in the loss of information about other entities that we want to retain in the database.

Example: Using the Same Student Course Table

Student_ID	Student_Name	Course_ID	Course_Name	Instructor
101	Alice Smith	CS101	Database Systems	Dr. Johnson
103	Carol Davis	CS103	Algorithms	Dr. Davis

Problem: If Carol drops out and we delete her record, we lose all information about CS103 course and Dr. Davis, even though the course might still exist and be taught by Dr. Davis.

Update Anomaly

Definition

An update anomaly occurs when updating a single piece of information requires multiple rows to be updated, and failure to update all relevant rows results in data inconsistency.

Example: Multiple Enrollments

Student_ID	Student_Name	Course_ID	Course_Name	Instructor
101	Alice Smith	CS101	Database Systems	Dr. Johnson
101	Alice Smith	CS102	Data Structures	Dr. Brown
101	Alice Smith	CS103	Algorithms	Dr. Davis

Problem: If Alice Smith changes her name to Alice Johnson, we need to update multiple rows. If we miss updating any row, we'll have inconsistent data where the same student appears with different names.

Functional Dependencies

Definition

A Functional Dependency (FD) is a constraint that describes the relationship between attributes in a relation. We say that attribute Y is functionally dependent on attribute X if each value of X is associated with exactly one value of Y.

X → Y (read as "X determines Y" or "Y depends on X")

This means that for any two tuples t1 and t2 in a relation R:

If t1[X] = t2[X], then t1[Y] = t2[Y]

                Important: Functional dependencies are determined by the semantics of the data, not just by looking at current data instances.
            

Functional Dependencies - Examples

Example 1: Employee Table

Emp_ID	Emp_Name	Department	Salary	Manager
E001	John Smith	IT	75000	Alice Brown
E002	Jane Doe	HR	68000	Bob Wilson

Functional Dependencies:

Emp_ID → Emp_Name (Each employee ID uniquely determines employee name)
Emp_ID → Department (Each employee belongs to exactly one department)
Emp_ID → Salary (Each employee has exactly one salary)
Emp_ID → Manager (Each employee has exactly one direct manager)

Types of Functional Dependencies

1. Trivial Functional Dependency

A functional dependency X → Y is trivial if Y ⊆ X (Y is a subset of X)

Examples: AB → A, ABC → BC, A → A

2. Non-trivial Functional Dependency

A functional dependency X → Y is non-trivial if Y ⊄ X (Y is not a subset of X)

Examples: A → B, AB → C, Student_ID → Student_Name

3. Completely Non-trivial Functional Dependency

A functional dependency X → Y is completely non-trivial if X ∩ Y = ∅ (X and Y have no common attributes)

Examples: A → B, AB → CD, Student_ID → Student_Name

Armstrong's Axioms for Functional Dependencies

Armstrong's axioms provide a sound and complete set of inference rules for functional dependencies:

Primary Axioms:

Reflexivity: If Y ⊆ X, then X → Y
Augmentation: If X → Y, then XZ → YZ
Transitivity: If X → Y and Y → Z, then X → Z

Derived Rules:

Union: If X → Y and X → Z, then X → YZ
Decomposition: If X → YZ, then X → Y and X → Z
Pseudo-transitivity: If X → Y and WY → Z, then WX → Z

Example Application:

Given: A → B, B → C, A → D

We can derive: A → C (by transitivity), A → BCD (by union)

Multivalued Dependencies

Definition

A Multivalued Dependency (MVD) X ↠ Y exists in relation R if, for each value of X, there is a set of values of Y that are independent of the values of the remaining attributes in R.

X ↠ Y (read as "X multi-determines Y")

Formal definition: X ↠ Y holds in relation R if whenever two tuples exist in R that agree on X, then we can swap their Y values and the resulting tuples will also be in R.

                Key Insight: MVDs arise when we have two or more independent multi-valued attributes that depend on the same key.
            

Multivalued Dependencies - Example

Example: Employee Skills and Languages

Employee	Skill	Language
John	Java	English
John	Java	Spanish
John	Python	English
John	Python	Spanish

Multivalued Dependencies:

Employee ↠ Skill (John's skills are independent of languages)
Employee ↠ Language (John's languages are independent of skills)

Problem: This creates redundancy - we need all combinations of skills and languages for each employee.

Properties of Multivalued Dependencies

Key Properties:

Trivial MVD: X ↠ Y is trivial if Y ⊆ X or XY = R
Complementation: If X ↠ Y, then X ↠ (R - X - Y)
Every FD is an MVD: If X → Y, then X ↠ Y
MVD and FD interaction: If X ↠ Y and Z ⊆ Y, and X → Z, then X → Y

Inference Rules for MVDs:

Reflexivity: If Y ⊆ X, then X ↠ Y
Augmentation: If X ↠ Y, then XZ ↠ YZ
Transitivity: If X ↠ Y and Y ↠ Z, then X ↠ (Z - Y)
Union: If X ↠ Y and X ↠ Z, then X ↠ YZ
Intersection: If X ↠ Y and X ↠ Z, then X ↠ (Y ∩ Z)

Join Dependencies

Definition

A Join Dependency (JD) is a constraint that specifies that a relation should be decomposed into multiple relations such that the original relation can be reconstructed by joining these decomposed relations.

⋈{R1, R2, ..., Rn} (read as "join dependency on R1, R2, ..., Rn")

A relation R satisfies a join dependency ⋈{R1, R2, ..., Rn} if R = R1 ⋈ R2 ⋈ ... ⋈ Rn, where each Ri is the projection of R onto a subset of attributes.

Important: Every MVD implies a JD, but not every JD is implied by an MVD.

Join Dependencies - Example

Example: Supplier-Part-Project Relation

Supplier	Part	Project
S1	P1	J1
S1	P2	J2
S2	P1	J1

Possible Join Dependencies:

⋈{(Supplier, Part), (Part, Project), (Supplier, Project)}
⋈{(Supplier, Part), (Supplier, Project)}

Meaning: The original relation can be reconstructed by joining the projected relations without loss of information.

Relationships Between Dependencies

Dependency Hierarchy

Functional Dependencies (FDs)

↓ (Every FD is an MVD)

Multivalued Dependencies (MVDs)

↓ (Every MVD implies a JD)

Join Dependencies (JDs)

Key Relationships:

Every FD is an MVD, but not every MVD is an FD
Every MVD implies a JD, but not vice versa
5NF is concerned with eliminating JDs that are not implied by keys

Normal Forms and Dependencies

1NF to 5NF Overview

First Normal Form (1NF)

All attributes contain atomic values, no repeating groups

Second Normal Form (2NF)

In 1NF + no partial dependencies (non-prime attributes fully functionally dependent on PK)

Third Normal Form (3NF)

In 2NF + no transitive dependencies

Boyce-Codd Normal Form (BCNF)

In 3NF + all determinants are candidate keys

Fourth Normal Form (4NF)

In BCNF + no non-trivial MVDs not implied by candidate keys

Fifth Normal Form (5NF)

In 4NF + no non-trivial JDs not implied by candidate keys

Summary

Anomalies

Insertion Anomaly: Cannot insert data due to missing attributes
Update Anomaly: Inconsistent data after updates
Deletion Anomaly: Loss of unrelated data when deleting

Dependencies

Functional Dependencies (FDs): X → Y
Multivalued Dependencies (MVDs): X ↠ Y
Join Dependencies (JDs): ⋈{R1, R2, ..., Rn}

Normalization

The process of removing anomalies by decomposing relations while preserving dependencies.

Higher normal forms (up to 5NF) eliminate more types of anomalies.

Thank You!

For questions or clarifications, please contact:

Dr. Mohsin Dar

mohsin.dar@ddn.upes.ac.in

References:

Database System Concepts by Silberschatz, Korth, and Sudarshan
Fundamentals of Database Systems by Elmasri and Navathe
Database Design and Relational Theory by C.J. Date