BCA and Semester III

Database Management System (BCA27103)

Class: BCA27103
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)
Class: BCA27103
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)
Class: BCA27103
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:
Student StudentNa Course CourseNa
ID me ID me
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.
BCA and Semester III
Database Management System (BCA27103)
Class: BCA27103
Academic Session: 2025-2026
They are a sign of poor database design, and the remedy is normalization, which splits data
into related tables to eliminate redundancy.

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)
{‘Male’, ‘Female’,
Gender
‘Other’}
Email Must match email format
DateOfBir
DATE format only
th

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:
BCA and Semester III
Database Management System (BCA27103)
Class: BCA27103
Academic Session: 2025-2026
StudentID Name
Knowing a StudentID lets you determine the corresponding Name.

2.4. Importance of Data Dependencies

● 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:

Student Na De
ID me pt

Alic CS
101
e E

Alic CS
101
e E

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)
BCA and Semester III
Database Management System (BCA27103)
Class: BCA27103
Academic Session: 2025-2026
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

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

BCA and Semester III
Database Management System (BCA27103)
Class: BCA27103
Academic Session: 2025-2026
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:

● 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
:

2. Augmentation

If X Y, then:
XZ YZ
(You can add the same attributes to both sides.)
Example
:

3. Transitivity

If X Y and Y Z, then:
X Z
Example
:

4.3. Derived (Secondary) Rules

Using the primary axioms, we can derive more rules:
1. Union Rule
BCA and Semester III
Database Management System (BCA27103)
Class: BCA27103
Academic Session: 2025-2026
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.

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.
BCA and Semester III
Database Management System (BCA27103)
Class: BCA27103
Academic Session: 2025-2026
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

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

Student Na
Subjects
ID me

Alic DBMS,
101
e Java

102 Bob Python

Here, Subjects is a multivalued field.

Conversion to 1NF

Student Na Subje
ID me ct

Alic DBM
101
e S

Alic
101 Java
e

Pytho
102 Bob
n
BCA and Semester III
Database Management System (BCA27103)
Class: BCA27103
Academic Session: 2025-2026
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

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
StudentID HOD
BCA and Semester III
Database Management System (BCA27103)
Class: BCA27103
Academic Session: 2025-2026
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)

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

2NF Partial dependencies
primary key

No transitive dependency on non-prime

3NF Transitive dependencies
attributes

Anomalies from non-superkey

BCNF Every determinant is a candidate key
dependencies
BCA and Semester III
Database Management System (BCA27103)
Class: BCA27103
Academic Session: 2025-2026
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.

● A student can have multiple hobbies.

● But language and hobby are independent.

Student Langua
Hobby
ID ge

101 English Music

Drawi
101 English
ng

101 French Music

Drawi
101 French
ng

Here, StudentID ⇉ Language and StudentID ⇉ Hobby

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:
BCA and Semester III
Database Management System (BCA27103)
Class: BCA27103
Academic Session: 2025-2026
● 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)

Now, each MVD is in its own table. This eliminates redundancy and satisfies 4NF.

6.5. Difference Between Functional Dependency and

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

Denoted by X→Y X⇉Y

Type of One-to-one or many-to- One-to-many (independent

relationship one values)

Causes
Yes Yes
anomalies?

Normalized by 2NF, 3NF, BCNF 4NF

BCA and Semester III
Database Management System (BCA27103)
Class: BCA27103
Academic Session: 2025-2026
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

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.

BCA and Semester III
Database Management System (BCA27103)
Class: BCA27103
Academic Session: 2025-2026
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).

● 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
BCA and Semester III
Database Management System (BCA27103)
Class: BCA27103
Academic Session: 2025-2026