BCA and Semester III

Database Management System (BCA27103(T))

Class: BCA27103(T)
Academic Session: 2025-2026

Study Material
(DBMS- Module 4)
Contents
Contents .................................................................................................................................................. 1
1. Data Redundancy and Anomalies ................................................................................................... 3
1.1. What Are Anomalies? ............................................................................................................. 3
1.2. Why Do These Anomalies Occur? ........................................................................................... 4
2. Domain and Data Dependency ....................................................................................................... 4
2.1. Example of Domain Constraints .............................................................................................. 4
2.2. What is a Data Dependency? .................................................................................................. 4
2.3. Functional Dependency (FD) ................................................................................................... 4
2.4. Importance of Data Dependencies ......................................................................................... 5
3. Functional Dependencies (FDs)....................................................................................................... 5
3.1. What is a Functional Dependency?......................................................................................... 5
3.2. Types of Functional Dependencies ......................................................................................... 6
3.2.1. Trivial Functional Dependency ........................................................................................ 6
3.2.2. Non-Trivial Dependency.................................................................................................. 6
3.2.3. Full Functional Dependency ............................................................................................ 6
3.2.4. Partial Dependency ......................................................................................................... 6
3.2.5. Transitive Dependency.................................................................................................... 6
3.2.6. Identifying Functional Dependencies ............................................................................. 6
3.2.7. Use of Functional Dependencies..................................................................................... 7
4. Module 4 – Topic 4: Armstrong’s Axioms ....................................................................................... 7
4.1. What Are Armstrong’s Axioms? .............................................................................................. 7
4.2. Three Primary Axioms ............................................................................................................. 7
4.3. Derived (Secondary) Rules ...................................................................................................... 8
4.4. How Are Armstrong’s Axioms Used? ...................................................................................... 8
4.5. Why Are They Important?....................................................................................................... 9
5. Normalization and Normal Forms (1NF, 2NF, 3NF, BCNF) .............................................................. 9
5.1. What is Normalization?........................................................................................................... 9
5.2. First Normal Form (1NF) ......................................................................................................... 9
5.3. Second Normal Form (2NF)................................................................................................... 10
BCA and Semester III
Database Management System (BCA27103(T))
Class: BCA27103(T)
Academic Session: 2025-2026
5.4. Third Normal Form (3NF) ...................................................................................................... 11
5.5. Boyce-Codd Normal Form (BCNF) ......................................................................................... 11
5.6. Summary Table: .................................................................................................................... 12
6. Multivalued Dependencies and 4NF ............................................................................................. 12
6.1. What is a Multivalued Dependency (MVD)?......................................................................... 12
6.1.1. Key Characteristics: ....................................................................................................... 12
6.1.2. Example: Multivalued Dependency .............................................................................. 12
6.2. Why Are MVDs a Problem?................................................................................................... 13
6.3. Fourth Normal Form (4NF).................................................................................................... 13
6.4. How to Decompose for 4NF .................................................................................................. 13
6.5. Difference Between Functional Dependency and Multivalued Dependency ....................... 14
7. Canonical Cover, Dependency Preservation, and Lossless Decomposition.................................. 14
7.1. Canonical Cover (Minimal Cover) ......................................................................................... 14
7.1.1. Steps to Compute a Canonical Cover ............................................................................ 14
7.2. Dependency Preservation ..................................................................................................... 15
7.3. Lossless (Non-Loss) Decomposition ...................................................................................... 15
7.3.1. Why These Concepts Matter Together ......................................................................... 16
BCA and Semester III
Database Management System (BCA27103(T))
Class: BCA27103(T)
Academic Session: 2025-2026
1. Data Redundancy and Anomalies
Data redundancy occurs when the same piece of information is stored in multiple places
within a database. This often happens when a database is not properly normalized.
For example, in a poorly designed STUDENT_COURSE table:
StudentID StudentName CourseID CourseName
101 Alice C101 Database
101 Alice C102 Java
102 Bob C101 Database
In the table above:

● The student name “Alice” is stored multiple times.

● The course name “Database” is repeated for different students.

This repetition of data causes unnecessary storage consumption and may lead to
inconsistencies if not managed properly.

1.1. What Are Anomalies?

Anomalies are unexpected or problematic situations that arise due to data redundancy in
unnormalized databases. These include:
1. Insertion Anomaly

Occurs when inserting a new record is problematic due to missing dependent data.
Example: You can't insert a new course until a student enrolls in it—because the course info
and student info are stored together.
2. Update Anomaly

Happens when changing data in one place requires the same update in multiple places.
Example: If “Database” is renamed to “DBMS”, you have to update it everywhere it appears;
missing any row causes inconsistency.
3. Deletion Anomaly

Occurs when deleting a record unintentionally removes useful information.

Example: If Alice drops all her courses and her row is deleted, her student information is
also lost.

1.2. Why Do These Anomalies Occur?

These anomalies are typically found in single-table designs where multiple concepts are
mixed—like storing both student and course data together.

They are a sign of poor database design, and the remedy is normalization, which splits data
into related tables to eliminate redundancy.
BCA and Semester III
Database Management System (BCA27103(T))
Class: BCA27103(T)
Academic Session: 2025-2026
2. Domain and Data Dependency
In relational databases, a domain refers to the set of valid values that an attribute (column)
can hold. It defines the type, format, and range of values an attribute is allowed to take.
For example:

● The domain of an attribute Age might be all integers between 0 and 120.

● The domain of Email could be any string that follows a valid email pattern.
Domains ensure data validity and consistency by preventing the entry of invalid or
incompatible values.

2.1. Example of Domain Constraints

Attribute Domain Example
Age INTEGER (0–120)
Gender {‘Male’, ‘Female’, ‘Other’}
Email Must match email format
DateOfBirth DATE format only

If someone tries to insert Age = -5 or Gender = 'Alien', a domain violation will occur.

2.2. What is a Data Dependency?

Data dependency describes a relationship between attributes in a relation, where the value
of one attribute determines the value of another.
The most fundamental type of data dependency in relational design is the Functional
Dependency (FD), which is the foundation of normalization.

2.3. Functional Dependency (FD)

A functional dependency occurs when the value of one attribute (or a set of attributes)
uniquely determines the value of another attribute.
Notation:

X → Y
This means: If two rows have the same value for attribute X, they must also have the same
value for attribute Y.
Example:

StudentID → Name
→ Knowing a StudentID lets you determine the corresponding Name.

2.4. Importance of Data Dependencies

BCA and Semester III
Database Management System (BCA27103(T))
Class: BCA27103(T)
Academic Session: 2025-2026
● Help in identifying primary keys and candidate keys.

● Play a key role in normalization (upcoming topics).

● Ensure data is logically consistent and correctly grouped.

Let’s now continue with Module 4 – Topic 3: Functional Dependencies (FDs) in Detail,
explained clearly and compactly for better understanding.

3. Functional Dependencies (FDs)

3.1. What is a Functional Dependency?
A Functional Dependency (FD) is a constraint between two sets of attributes in a
relation. It expresses a relationship where the value of one attribute (or a group of attributes)
uniquely determines the value of another attribute.
Notation:
X → Y
This means: If two tuples have the same value for X, they must have the same value for Y.
Example:

StudentID Name Dept

101 Alice CSE

101 Alice CSE

Here, StudentID → Name, Dept holds true. You can determine a student’s name and
department from their ID.

3.2. Types of Functional Dependencies

3.2.1. Trivial Functional Dependency
An FD is trivial if the dependent is a subset of the determinant.

● Example:
Roll, Name → Name
(Always true; doesn’t provide useful information)
3.2.2. Non-Trivial Dependency
Occurs when the right-hand side is not already part of the left-hand side.

● Example:
Roll → Name

3.2.3. Full Functional Dependency

BCA and Semester III
Database Management System (BCA27103(T))
Class: BCA27103(T)
Academic Session: 2025-2026
A dependency X → Y is fully dependent if removing any attribute from X causes the
dependency to break.

● Example:
In RollNo, CourseID → Grade, if Grade depends on the full combination, it’s a full
dependency.
3.2.4. Partial Dependency
Occurs when a part of a composite key determines a non-key attribute.

● Example:
RollNo → StudentName in a relation with primary key (RollNo, CourseID).
Partial dependencies must be removed in Second Normal Form (2NF).
3.2.5. Transitive Dependency
Occurs when one attribute indirectly depends on another via a third.

● Example:
If RollNo → Dept and Dept → HOD, then RollNo → HOD is transitive.
Transitive dependencies must be removed in Third Normal Form (3NF).
3.2.6. Identifying Functional Dependencies
To identify FDs in a table:
1. Look for unique identifiers—these are likely the determinants (e.g., primary keys).
2. See what other attributes can be uniquely determined from them.
3. Look for patterns or repetitions in data values.

3.2.7. Use of Functional Dependencies

● Help determine primary keys and candidate keys

● Are essential for normalization (especially in 2NF, 3NF, BCNF)

● Assist in decomposing relations logically without data loss

4. Module 4 – Topic 4: Armstrong’s Axioms

4.1. What Are Armstrong’s Axioms?
Armstrong’s Axioms are a set of inference rules used to derive all valid functional
dependencies (FDs) from a given set. These axioms are sound and complete, meaning:

● They can derive only correct FDs (soundness)

● They can derive all correct FDs (completeness)

These rules are essential for:
BCA and Semester III
Database Management System (BCA27103(T))
Class: BCA27103(T)
Academic Session: 2025-2026
● Computing attribute closure

● Finding candidate keys

● Decomposing relations during normalization

4.2. Three Primary Axioms

1. Reflexivity

If Y is a subset of X, then:
X → Y
Example:
If X = {Roll, Name}, then X → Roll is valid.
2. Augmentation

If X → Y, then:
XZ → YZ
(You can add the same attributes to both sides.)
Example:
If Roll → Name, then Roll, Dept → Name, Dept
3. Transitivity

If X → Y and Y → Z, then:
X → Z
Example:
If Roll → Dept and Dept → HOD, then Roll → HOD

4.3. Derived (Secondary) Rules

Using the primary axioms, we can derive more rules:
1. Union Rule

If X → Y and X → Z, then:
X → YZ
2. Decomposition Rule

If X → YZ, then:
X → Y and X → Z
3. Pseudo-Transitivity

If X → Y and YZ → W, then:
XZ → W
These derived rules are helpful in simplifying and expanding sets of FDs during
normalization.
BCA and Semester III
Database Management System (BCA27103(T))
Class: BCA27103(T)
Academic Session: 2025-2026
4.4. How Are Armstrong’s Axioms Used?
The axioms are used to compute the closure of a set of attributes, denoted as X⁺, which is the
set of all attributes functionally dependent on X.
Example:
Given:

● A→B

● B→C
Then:

● A → C (By transitivity)

● A → B, C (By union)
Therefore:
A⁺ = {A, B, C}
This tells us what attributes can be determined from A.

4.5. Why Are They Important?

● Finding Keys: Helps identify which attributes can act as a candidate key.

● Testing Lossless Decomposition: Ensures no data is lost when splitting tables.

● Normalization: Used in identifying partial, full, and transitive dependencies.

Understood! Let’s now continue with Module 4 – Topic 5: Normalization and Normal
Forms, one at a time—just like previous modules.

5. Normalization and Normal Forms (1NF, 2NF, 3NF, BCNF)

5.1. What is Normalization?
Normalization is the process of organizing data in a relational database to eliminate
redundancy and improve data integrity. It involves decomposing large, complex tables into
smaller, well-structured ones while preserving the data and relationships.
The primary goals of normalization are:

● To reduce data redundancy (repetition)

● To prevent insertion, update, and deletion anomalies

● To maintain data consistency

BCA and Semester III
Database Management System (BCA27103(T))
Class: BCA27103(T)
Academic Session: 2025-2026
Normalization is achieved through a series of stages called Normal Forms (NF), each with
specific rules and conditions.

5.2. First Normal Form (1NF)

A relation is in 1NF if:
1. All attributes contain only atomic (indivisible) values.
2. Each record is unique (i.e., has a primary key).
3. There are no repeating groups or arrays in any column.
Example: Unnormalized Table

StudentID Name Subjects

101 Alice DBMS, Java

102 Bob Python

Here, Subjects is a multivalued field.

Conversion to 1NF

StudentID Name Subject

101 Alice DBMS

101 Alice Java

102 Bob Python

Now the table is in 1NF.

5.3. Second Normal Form (2NF)

A relation is in 2NF if:
1. It is already in 1NF, and
2. Every non-prime attribute is fully functionally dependent on the entire primary
key.
This form mainly concerns tables with composite keys. A partial dependency occurs when
a non-prime attribute depends on only part of a composite key.
Example

Consider a relation:
ENROLL(StudentID, CourseID, StudentName, CourseName)
Assuming the composite key is (StudentID, CourseID):

● StudentName depends only on StudentID

● CourseName depends only on CourseID

BCA and Semester III
Database Management System (BCA27103(T))
Class: BCA27103(T)
Academic Session: 2025-2026
→ Partial dependency exists → Not in 2NF.
Decomposition into 2NF:

● STUDENT(StudentID, StudentName)

● COURSE(CourseID, CourseName)

● ENROLL(StudentID, CourseID)

Now, each table is in 2NF with full functional dependencies.

5.4. Third Normal Form (3NF)

A relation is in 3NF if:
1. It is in 2NF, and
2. No transitive dependency exists between non-prime attributes.

Transitive Dependency: If A → B and B → C, then A → C is transitive.

Example
STUDENT(StudentID, Name, DeptName, HOD)
● StudentID → DeptName

● DeptName → HOD
→ Transitive dependency exists: StudentID → HOD
Decomposition into 3NF:

● STUDENT(StudentID, Name, DeptName)

● DEPARTMENT(DeptName, HOD)

Now, transitive dependency is removed.

5.5. Boyce-Codd Normal Form (BCNF)

A stricter version of 3NF.
A relation is in BCNF if:

● For every non-trivial functional dependency X → Y,

X must be a super key.
Example (Violation of BCNF):
TEACHER(TeacherID, Subject, HOD)
Let’s assume:

● One subject has only one HOD (Subject → HOD)

● One teacher can teach only one subject (TeacherID → Subject)

BCA and Semester III
Database Management System (BCA27103(T))
Class: BCA27103(T)
Academic Session: 2025-2026
But Subject is not a super key → violates BCNF.
BCNF Decomposition:
● SUBJECT(Subject, HOD)

● TEACHER(TeacherID, Subject)

5.6. Summary Table:

Normal
Requirement Eliminates
Form

1NF Atomic values, no repeating groups Multivalued fields

Full functional dependency on whole primary

2NF Partial dependencies
key

No transitive dependency on non-prime

3NF Transitive dependencies
attributes

Anomalies from non-superkey

BCNF Every determinant is a candidate key
dependencies

6. Multivalued Dependencies and 4NF

6.1. What is a Multivalued Dependency (MVD)?
A multivalued dependency occurs in a relation when one attribute determines multiple
independent values of another attribute, and those values are not dependent on other
attributes in the relation.
It is denoted as:
A ⇉ B
This means: for a given value of A, there is a set of values for B, independent of other
attributes.
6.1.1. Key Characteristics:
● An MVD is not a functional dependency.

● It often occurs when two or more independent one-to-many relationships exist

with the same determinant.
6.1.2. Example: Multivalued Dependency
Consider a relation:
STUDENT(StudentID, Language, Hobby)
Where:

● A student can know multiple languages.

BCA and Semester III
Database Management System (BCA27103(T))
Class: BCA27103(T)
Academic Session: 2025-2026
● A student can have multiple hobbies.

● But language and hobby are independent.

StudentID Language Hobby

101 English Music

101 English Drawing

101 French Music

101 French Drawing

Here, StudentID ⇉ Language and StudentID ⇉ Hobby

→ Language and Hobby are independent multivalued facts about the same student.

6.2. Why Are MVDs a Problem?

Just like functional dependencies cause anomalies and redundancy, multivalued
dependencies can lead to:

● Repetition of combinations

● Unnecessary data duplication

● Insert, update, and delete anomalies

To remove MVD-based redundancy, we apply Fourth Normal Form (4NF).

6.3. Fourth Normal Form (4NF)

A relation is in 4NF if:
1. It is in Boyce-Codd Normal Form (BCNF), and
2. It contains no non-trivial multivalued dependencies.
Trivial MVD:

An MVD X ⇉ Y is trivial if:

● Y ⊆ X, or

● X ∪ Y = all attributes of the relation

Non-trivial MVDs must be removed via decomposition.

6.4. How to Decompose for 4NF

In the earlier STUDENT example, we can split the relation into two:
1. STUDENT_LANG(StudentID, Language)
2. STUDENT_HOBBY(StudentID, Hobby)
BCA and Semester III
Database Management System (BCA27103(T))
Class: BCA27103(T)
Academic Session: 2025-2026
Now, each MVD is in its own table. This eliminates redundancy and satisfies 4NF.

6.5. Difference Between Functional Dependency and

Multivalued Dependency
Feature Functional Dependency (FD) Multivalued Dependency (MVD)

Denoted by X→Y X⇉Y

Type of relationship One-to-one or many-to-one One-to-many (independent values)

Causes anomalies? Yes Yes

Normalized by 2NF, 3NF, BCNF 4NF

7. Canonical Cover, Dependency Preservation, and Lossless

Decomposition
7.1. Canonical Cover (Minimal Cover)
A canonical cover (also called a minimal cover) is a simplified version of a set of
functional dependencies (FDs) that is logically equivalent to the original set. It contains:

● No redundant dependencies

● No redundant attributes on either side of any dependency

Simplifying FDs into canonical form is essential for:

● Efficient query processing

● Reliable normalization

● Easier schema decomposition

7.1.1. Steps to Compute a Canonical Cover
1. Split RHS: Ensure all FDs have a single attribute on the right-hand side.

o e.g., A → BC becomes A → B and A → C

2. Remove Extraneous Attributes from LHS

o e.g., If AB → C and A → C already exists, B is redundant.

3. Eliminate Redundant Dependencies

o If A → C can be derived from other FDs, it is redundant and can be

removed.

7.2. Dependency Preservation

BCA and Semester III
Database Management System (BCA27103(T))
Class: BCA27103(T)
Academic Session: 2025-2026
When a relation is decomposed into smaller relations during normalization, we want to
ensure that all original functional dependencies can still be enforced without joining the
decomposed tables.
Definition:

A decomposition is dependency preserving if the union of FDs in the decomposed relations

is logically equivalent to the original set of FDs.
Why It Matters:

● It allows enforcing constraints on individual tables without costly joins.

● Ensures data integrity is not lost in decomposition.

7.3. Lossless (Non-Loss) Decomposition

A decomposition of a relation is lossless if no data is lost when we split the table and then
join it back.
Definition:

A decomposition is lossless if:

R1 ⨝ R2 = R
Where:

● R is the original relation

● R1 and R2 are the decomposed parts

Lossless Decomposition Condition (for 2 relations):

A decomposition of R into R1 and R2 is lossless if:

R1 ∩ R2 → R1 or R1 ∩ R2 → R2
Example:

Given:
R(StudentID, CourseID, Marks)
Decompose into:

● R1(StudentID, CourseID)

● R2(CourseID, Marks)
The intersection is CourseID. If CourseID → Marks holds, the decomposition is lossless.
7.3.1. Why These Concepts Matter Together
When we normalize a database:

● We want to eliminate redundancy and anomalies (achieved via functional

dependencies and normal forms).
BCA and Semester III
Database Management System (BCA27103(T))
Class: BCA27103(T)
Academic Session: 2025-2026
● But we also want to ensure:
o No information is lost (lossless)
o All constraints remain checkable (dependency preservation)
o The FD set is simplified (canonical cover) for efficient analysis