Chapter :-4 (English)

1. Functional Dependencies (FDs)

Functional Dependencies (FDs)

A functional dependency describes a relationship between attributes (columns) in a relational database table.

Definition

A functional dependency X → Y means:

If two rows have the same value for attribute set X, they must also have the same value for attribute set Y.

In other words, X determines Y.

Examples

1.   In Student(ID, Name, Major):

o   ID → Name, Major

§  The ID uniquely determines the student's name and major.

2.   In Employee(EmpID, Department, Manager):

o   Department → Manager

§  Each department has one manager.

Advantages of Functional Dependencies

1. Basis for Normalization

FDs are the foundation for database normalization (1NF → BCNF).
They help identify how attributes relate, allowing designers to eliminate redundancy and anomalies.

2. Reduce Data Redundancy

By using FDs to guide normalization, you minimize duplicate data, saving storage and improving consistency.

3. Prevent Update, Insert, and Delete Anomalies

FDs ensure the schema is structured so that:

• Updating data does not require multiple inconsistent changes.
• Inserting partial information is less problematic.
• Deleting a row doesn’t remove essential data.

4. Improve Data Integrity

FDs enforce deterministic relationships (e.g., StudentID → StudentName) ensuring accurate and reliable data.

5. Improve Query Efficiency (in well-designed schemas)

Though sometimes normalization adds joins, FDs can help design schemas that balance efficiency and correctness.

Disadvantages of Functional Dependencies

1. Difficult and Time-Consuming to Discover

FDs must be identified accurately, which often requires deep knowledge of business rules.
Incorrect or missing FDs lead to poor schema design.

2. Over-Normalization Can Hurt Performance

Strictly applying FDs to normalize to high normal forms can lead to:

• Many small tables
• More joins
• Slower queries in read-heavy systems

3. Not Always Obvious or Expressible

Some dependencies are:

• Business-rule-driven (not inherent in data)
• Hard to formalize (e.g., cross-field semantic rules)
• Changing over time

4. FD Theory Doesn’t Handle All Constraints

FDs cannot express:

• Multi-valued dependencies
• Temporal rules
• Conditional or exception-based constraints

Other constraint types (MVDs, TGDs, CHECK constraints, etc.) must be used.

5. Potential for Misapplication

If designers use FDs incorrectly:

• They may split tables unnecessarily
• Or fail to split when needed

Both lead to inefficient or inconsistent databases.

2. Normalization

Normalization

Normalization is a database design technique used to organize data into tables in such a way that redundancy is reduced and data integrity is improved.
It divides large, unstructured tables into smaller, well-structured ones based on functional dependencies.

Advantages of Normalization

1. Reduces Data Redundancy

Stores data only once, avoiding duplication.

2. Prevents Anomalies

Helps avoid:

• Update anomalies
• Insert anomalies
• Delete anomalies

3. Improves Data Integrity

Data becomes consistent and reliable because each fact is stored in only one place.

4. Saves Storage Space

Eliminating repeated data reduces the size of the database.

5. Easier Maintenance

Changes are applied in one place, making updates simple and error-free.

 Disadvantages of Normalization

1. More Tables to Manage

Normalization splits data into many tables, increasing complexity.

2. Slower Queries (More Joins)

Retrieving data may require several JOIN operations, which can slow performance.

3. Harder for Beginners

Understanding 1NF, 2NF, 3NF, BCNF, etc. can be challenging.

4. Not Ideal for Analytical Systems   

Data warehouses often use denormalization because highly normalized data is slower for large read operations.

3. Normalization

4. Normal Forms in DBMS

5. Main Normal Forms (NF) in Normalization

Main Normal Forms (NF) in Normalization

1. First Normal Form (1NF)

Definition:

• No repeating groups
• All values are atomic (single-valued)

ü Advantages:

• Eliminates duplicate columns
• Makes data easier to search and sort
• Ensures a consistent structure

ü Disadvantages:

• May still contain redundancy
• Anomalies (update, insert, delete) can still occur

  2.  Second Normal Form (2NF)

Definition:

• Must be in 1NF
• No partial dependency (non-key attributes must depend on the whole primary key)

ü Advantages:

• Reduces redundancy in tables with composite keys
• Removes partial dependency problems
• Reduces insert/update anomalies further

ü Disadvantages:

• Increases number of tables
• More joins needed

 3. Third Normal Form (3NF)

Definition:

• Must be in 2NF
• No transitive dependency (non-key attribute depending on another non-key attribute)

ü Advantages:

• Removes most redundancy
• Minimizes anomalies significantly
• Produces well-structured and efficient tables

ü Disadvantages:

• More tables → more complexity
• May slow down queries that need to combine data

4. Boyce–Codd Normal Form (BCNF)

Definition:

• Stronger version of 3NF
• For every Functional Dependency X → Y, X must be a superkey

ü Advantages:

• Eliminates all anomalies not fixed by 3NF
• Ensures a very high level of data consistency
• Ideal for mission-critical systems

ü Disadvantages:

• Can cause excessive splitting of tables
• Might require many joins, reducing performance
• Harder to design and understand

 Table form