UNIT-4:

LC1: Problems caused by redundancy, FD (definition), Armstrong ‘s axioms;

Redundancy:
Data redundancy in a Database Management System (DBMS) refers to the repetition of the
same data in multiple places within a database. It is a concern because it can lead to
inconsistencies, update anomalies, and increased storage requirements, impacting data
integrity and database performance.
Problems caused by redundancy:
1. Insertion Anomaly:
Insertion anomaly arises when you are trying to insert some data into the database, but you
are not able to insert it.
Example: If you want to add the details of the student in the above table, then you must know
the details of the department; otherwise, you will not be able to add the details because
student details are dependent on department details.
2. Deletion Anomaly:
Deletion anomaly arises when you delete some data from the database, but some unrelated
data is also deleted; that is, there will be a loss of data due to deletion anomaly.
Example: If we want to delete the student detail, which has student_id 2, we will also lose the
unrelated data, i.e., department_id 102, from the above table.
3. Updating Anomaly:
An update anomaly arises when you update some data in the database, but the data is partially
updated, which causes data inconsistency.
Example: If we want to update the details of dept_head from Jaspreet Kaur to Ankit Goyal for
Dept_id 104, then we have to update it everywhere else; otherwise, the data will get partially
updated, which causes data inconsistency.
Advantages of data redundancy in DBMS
o Provides Data Security: Data redundancy can enhance data security as it is difficult
for cyber attackers to attack data that are in different locations.
o Provides Data Reliability: Reliable data improves accuracy because organizations
can check and confirm whether data is correct.
o Create Data Backup: Data redundancy helps in backing up the data.

Disadvantages of data redundancy in DBMS

o Data corruption: Redundant data leads to high chances of data corruption.
 o Wastage of storage: Redundant data requires more space, leading to a need for more
storage space.
o High cost: Large storage is required to store and maintain redundant data, which is
costly.
How to reduce data redundancy in DBMS
We can reduce data redundancy using the following methods:
o Database Normalization: We can normalize the data using the normalization
method. In this method, the data is broken down into pieces, which means a large
table is divided into two or more small tables to remove redundancy. Normalization
removes insert anomaly, update anomaly, and delete anomaly.
o Deleting Unused Data: It is important to remove redundant data from the database as
it generates data redundancy in the DBMS. It is a good practice to remove unwanted
data to reduce redundancy.
o Master Data: The data administrator shares master data across multiple systems.
Although it does not remove data redundancy, but it updates the redundant data
whenever the data is changed.
n relational database management, functional dependency is a concept that specifies the
relationship between two sets of attributes where one attribute determines the value of
another attribute. It is denoted as X → Y, where the attribute set on the left side of the
arrow, X is called Determinant, and Y is called the Dependent.
What is Functional Dependency?
A functional dependency occurs when one attribute uniquely determines another attribute
within a relation. It is a constraint that describes how attributes in a table relate to each
other. If attribute A functionally determines attribute B we write this as the A→B.
Functional dependencies are used to mathematically express relations among database
entities and are very important to understanding advanced concepts in Relational
Database Systems.
Example:

dept_nam
roll_no name e dept_building

42 abc CO A4

43 pqr IT A3
 dept_nam
roll_no name e dept_building

44 xyz CO A4

45 xyz IT A3

46 mno EC B2

47 jkl ME B2

From the above table we can conclude some valid functional dependencies:
 roll_no → { name, dept_name, dept_building }→ Here, roll_no can determine values
of fields name, dept_name and dept_building, hence a valid Functional dependency
 roll_no → dept_name , Since, roll_no can determine whole set of {name, dept_name,
dept_building}, it can determine its subset dept_name also.
 dept_name → dept_building , Dept_name can identify the dept_building accurately,
since departments with different dept_name will also have a different dept_building

 More valid functional dependencies: roll_no → name, {roll_no, name} ⇢

{dept_name, dept_building}, etc.
Here are some invalid functional dependencies:
 name → dept_name Students with the same name can have different dept_name,
hence this is not a valid functional dependency.
 dept_building → dept_name There can be multiple departments in the same
building. Example, in the above table departments ME and EC are in the same
building B2, hence dept_building → dept_name is an invalid functional dependency.
 More invalid functional dependencies: name → roll_no, {name, dept_name} →
roll_no, dept_building → roll_no, etc.

Types of Functional Dependencies in DBMS

1. Trivial functional dependency
2. Non-Trivial functional dependency
3. Multivalued functional dependency
 4. Transitive functional dependency
1. Trivial Functional Dependency
In Trivial Functional Dependency, a dependent is always a subset of the determinant. i.e. If X
→ Y and Y is the subset of X, then it is called trivial functional dependency.
Symbolically: A→B is trivial functional dependency if B is a subset of A.
The following dependencies are also trivial: A→A & B→B
Example 1 :
 ABC -> AB
 ABC -> A
 ABC -> ABC
Example 2:

roll_no name age

42 abc 17

43 pqr 18

44 xyz 18

Here, {roll_no, name} → name is a trivial functional dependency, since the dependent name
is a subset of determinant set {roll_no, name}. Similarly, roll_no → roll_no is also an
example of trivial functional dependency.
2. Non-trivial Functional Dependency
In Non-trivial functional dependency, the dependent is strictly not a subset of the
determinant. i.e. If X → Y and Y is not a subset of X, then it is called Non-trivial functional
dependency.
Example 1 :
 Id -> Name
 Name -> DOB
Example 2:
roll_no name age

42 abc 17

43 pqr 18

44 xyz 18

Here, roll_no → name is a non-trivial functional dependency, since the dependent name is not
a subset of determinant roll_no. Similarly, {roll_no, name} → age is also a non-trivial
functional dependency, since age is not a subset of {roll_no, name}
3. Semi Non Trivial Functional Dependencies
A semi non-trivial functional dependency occurs when part of the dependent attribute (right-
hand side) is included in the determinant (left-hand side), but not all of it. This is a middle
ground between trivial and non-trivial functional dependencies. X -> Y is called semi non-
trivial when X intersect Y is not NULL.
Example:
Consider the following table:

Student_I
D Course_ID Course_Name

101 CSE101 Computer Science

102 CSE102 Data Structures

103 CSE101 Computer Science

Functional Dependency:
{StudentID,CourseID}→CourseID
This is semi non-trivial because:
 Part of the dependent attribute (Course_ID) is already included in the determinant
({Student_ID, Course_ID}).
  However, the dependency is not completely trivial because {StudentID}→CourseID
is not implied directly.
4. Multivalued Functional Dependency
In Multivalued functional dependency, entities of the dependent set are not dependent on each
other. i.e. If a → {b, c} and there exists no functional dependency between b and c, then it is
called a multivalued functional dependency.
Example:

manuf_yea
bike_model r color

tu1001 2007 Black

tu1001 2007 Red

tu2012 2008 Black

tu2012 2008 Red

tu2222 2009 Black

tu2222 2009 Red

In this table:
 X: bike_model
 Y: color
 Z: manuf_year
For each bike model (bike_model):
1. There is a group of colors (color) and a group of manufacturing years (manuf_year).
2. The colors do not depend on the manufacturing year, and the manufacturing year does
not depend on the colors. They are independent.
3. The sets of color and manuf_year are linked only to bike_model.
That’s what makes it a multivalued dependency.
Armstrongs’s Axoms:
Prerequisite – Functional Dependencies
This article contains Armstrong’s Axioms and how Armstrong’s Axioms are used to decide
about the functional dependency on the database. We will be also learning about the
Secondary Rules and Armstrong Relations. We will learn each thing in detail. Before moving
ahead, you must have a knowledge of Functional Dependency.
Armstrong Axioms
The term Armstrong Axioms refers to the sound and complete set of inference rules or
axioms, introduced by William W. Armstrong, that is used to test the logical implication
of functional dependencies. If F is a set of functional dependencies then the closure of F,
denoted as F+, is the set of all functional dependencies logically implied by F. Armstrong’s
Axioms are a set of rules, that when applied repeatedly, generates a closure of functional
dependencies.
Axioms
 Axiom of Reflexivity: If A is a set of attributes and B is a subset of A, then A
holds B. If B⊆A then A→B. This property is trivial property.
 Axiom of Augmentation: If A→B holds and Y is the attribute set, then AY→BY also
holds. That is adding attributes to dependencies, does not change the basic
dependencies. If A→B, then AC→BC for any C.
 Axiom of Transitivity: Same as the transitive rule in algebra, if A→B holds
and B→C holds, then A→C also holds. A→B is called A functionally which
determines B. If X→Y and Y→Z, then X→Z.
Secondary Rules
These rules can be derived from the above axioms.
 Union: If A→B holds and A→C holds, then A→BC holds.
If X→Y and X→Z then X→YZ.
 Composition: If A→B and X→Y hold, then AX→BY holds.
 Decomposition: If A→BC holds then A→B and A→C hold.
If X→YZ then X→Y and X→Z.
 Pseudo Transitivity: If A→B holds and BC→D holds, then AC→D holds.
If X→Y and YZ→W then XZ→W.
 Self Determination: It is similar to the Axiom of Reflexivity, i.e. A→A for any A.
 Extensivity: Extensivity is a case of augmentation. If AC→A, and A→B,
then AC→B. Similarly, AC→ABC and ABC→BC. This leads to AC→BC.
Armstrong Relation
Armstrong Relation can be stated as a relation that is able to satisfy all functional
dependencies in the F+ Closure. In the given set of dependencies, the size of the minimum
Armstrong Relation is an exponential function of the number of attributes present in the
dependency under consideration.
Why Armstrong Axioms Refer to the Sound and Complete?
By sound, we mean that given a set of functional dependencies F specified on a relation
schema R, any dependency that we can infer from F by using the primary rules of Armstrong
axioms holds in every relation state r of R that satisfies the dependencies in F.
By complete, we mean that using primary rules of Armstrong axioms repeatedly to infer
dependencies until no more dependencies can be inferred results in the complete set of all
possible dependencies that can be inferred from F.
Advantages of Using Armstrong’s Axioms in Functional Dependency
 They provide a systematic and efficient method for inferring additional functional
dependencies from a given set of functional dependencies, which can help to
optimize database design.
 They can be used to identify redundant functional dependencies, which can help to
eliminate unnecessary data and improve database performance.
 They can be used to verify whether a set of functional dependencies is a minimal
cover, which is a set of dependencies that cannot be further reduced without losing
information.

How to Find Candidate Key from Functional Dependencies

Functional Dependency

 Functional dependency is the relations between two sets of attributes X and Y such
that if X -> Y, then all the same values of X there will be the same values of Y, or Y
can be identified by the values of X uniquely.

Candidate Key

 In a relational model, the set of those attributes which can be used to identify each
row uniquely is called super keys, and there can be a lot of super keys. Those super
keys which have no proper subset as any super key then it is called a candidate key.
 For example, in a relational model, we have three attributes as {A, B, C} and {AB}
can identify a complete table, and {A} itself can identify the whole table then AB and
A both will be super key, but A will be candidate key.

Closure

 Attribute closure of an attribute can be identified as the set of attributes that can be
functionally determined from it.
 Closures will be used to find out the candidate key using functional dependencies.
 Steps to find out the Candidate Key of a Relational Table using Functional Dependencies

Step 1:

 First, we will find out the essential and non-essential sets of attributes from the given
set of attributes. Those attributes which are dependent on other attributes are non-
essential attributes, and their values can be found out using essential attributes.
 So, all the essential attributes will definitely be part of our candidate key.

Step 2:

 We will combine all the essential attributes, and if they can determine all the
attributes (by finding their closure), then it will be the candidate key.

Step 3:

 If a combination of essential attributes is not a candidate key, then we will try

different combinations of essential and non-essential attributes which are not subset of
each other to find out all the candidate keys.

Example 1:

 Let's suppose we have a set of attributes as S: {A, B, C, D} and functional

dependencies are:
 A -> B
 B -> C
 C -> A

Solution:

 In the above example, we have non-essential attributes as {A, B, C}, and the essential
attribute is {D}.
 So, D will be the part of the candidate key.
 Closure of D: {D}
 So, a combination of essential attributes is not able to find all the attributes, so we will
add non-essential attributes in different ways to find out the candidate keys.
 Closure of (AD) = {A, B, C, D} (using A->B and B->C)
 So, AD will be a candidate key.
 Closure of (BD) = {A, B, C, D} (using C->A and B->C)
 So, BD will be a candidate key.
 Closure of (CD) = {A, B, C, D} (using C->A and A->B)
 So, CD will be a candidate key.
 No other combination of attributes is not possible, so candidate keys are {AD, BD,
CD}

LC2: FD identification from relations, Equivalence of two FD sets;

FD identification from relations:

A functional dependency occurs when one attribute uniquely determines another attribute
within a relation. It is a constraint that describes how attributes in a table relate to each other.
If attribute A functionally determines attribute B we write this as the A→B.
Example:

dept_nam
roll_no name e dept_building

42 abc CO A4

43 pqr IT A3

44 xyz CO A4

45 xyz IT A3

46 mno EC B2

47 jkl ME B2

From the above table we can conclude some valid functional dependencies:
 roll_no → { name, dept_name, dept_building }→ Here, roll_no can determine values
of fields name, dept_name and dept_building, hence a valid Functional dependency
 roll_no → dept_name , Since, roll_no can determine whole set of {name, dept_name,
dept_building}, it can determine its subset dept_name also.
 dept_name → dept_building , Dept_name can identify the dept_building accurately,
since departments with different dept_name will also have a different dept_building

 More valid functional dependencies: roll_no → name, {roll_no, name} ⇢

{dept_name, dept_building}, etc.
Equivalence of two FD sets: or understanding the equivalence of Functional
Dependencies Sets (FD sets), the basic idea about Attribute Closure is given in this
article Given a Relation with different FD sets for that relation, we have to find out
whether one FD set is a subset of another or both are equal.
How To Find the Relationship Between Two Functional Dependency Sets?
Let FD1 and FD2 be two FD sets for a relation R.
1. If all FDs of FD1 can be derived from FDs present in FD2, we can say that FD2 ⊃
FD1.

2. If all FDs of FD2 can be derived from FDs present in FD1, we can say that FD1 ⊃
FD2.
3. If 1 and 2 both are true, FD1=FD2.
All these three cases can be shown using the Venn diagram:
Equivalence of Functional Dependency
Why We Need to Compare Functional Dependencies?
Suppose in the designing process we convert the ER diagram to a relational model and
this task is given to two different engineers. Now those two engineers give two different
sets of functional dependencies. So, being an administrator we need to ensure that we
must have a good set of Functional Dependencies. To ensure this we require to study
the equivalence of Functional Dependencies.
Advantages
 It can help to identify redundant functional dependencies, which can be eliminated to
reduce data redundancy and improve database performance.
 It can help to optimize database design by identifying equivalent sets of functional
dependencies that can be used interchangeably.
 It can ensure data consistency by identifying all possible combinations of attributes
that can exist in the database.
Disadvantages
 The process of determining the equivalence of functional dependencies can be
computationally expensive, especially for large datasets.
 The process may require testing multiple candidate sets of functional dependencies,
which can be time-consuming and complex.
 The equivalence of functional dependencies may not always accurately reflect the
semantic meaning of data, and may not always reflect the true relationships between
data elements.
Sample Questions
Q.1 Let us take an example to show the relationship between two FD sets. A relation
R(A,B,C,D) having two FD sets FD1 = {A->B, B->C, AB->D} and FD2 = {A->B, B->C,
A->C, A->D}
Step 1: Checking whether all FDs of FD1 are present in FD2
 A->B in set FD1 is present in set FD2.
 B->C in set FD1 is also present in set FD2.
 AB->D is present in set FD1 but not directly in FD2 but we will check whether we
can derive it or not. For set FD2, (AB)+ = {A, B, C, D}. It means that AB can
functionally determine A, B, C, and D. So AB->D will also hold in set FD2.

As all FDs in set FD1 also hold in set FD2, FD2 ⊃ FD1 is true.
Step 2: Checking whether all FDs of FD2 are present in FD1
 A->B in set FD2 is present in set FD1.
 B->C in set FD2 is also present in set FD1.
 A->C is present in FD2 but not directly in FD1 but we will check whether we can
derive it or not. For set FD1, (A)+ = {A, B, C, D}. It means that A can functionally
determine A, B, C, and D. SO A->C will also hold in set FD1.
 A->D is present in FD2 but not directly in FD1 but we will check whether we can
derive it or not. For set FD1, (A)+ = {A, B, C, D}. It means that A can functionally
determine A, B, C, and D. SO A->D will also hold in set FD1.

As all FDs in set FD2 also hold in set FD1, FD1 ⊃ FD2 is true.

Step 3: As FD2 ⊃ FD1 and FD1 ⊃ FD2 both are true FD2 =FD1 is true. These two FD
sets are semantically equivalent.
Q.2 Let us take another example to show the relationship between two FD sets. A relation
R2(A,B,C,D) having two FD sets FD1 = {A->B, B->C,A->C} and FD2 = {A->B, B->C,
A->D}
Step 1: Checking whether all FDs of FD1 are present in FD2
 A->B in set FD1 is present in set FD2.
 B->C in set FD1 is also present in set FD2.
 A->C is present in FD1 but not directly in FD2 but we will check whether we can
derive it or not. For set FD2, (A)+ = {A, B, C, D}. It means that A can functionally
determine A, B, C, and D. SO A->C will also hold in set FD2.

As all FDs in set FD1 also hold in set FD2, FD2 ⊃ FD1 is true.
Step 2: Checking whether all FDs of FD2 are present in FD1
 A->B in set FD2 is present in set FD1.,
 B->C in set FD2 is also present in set FD1.
 A->D is present in FD2 but not directly in FD1 but we will check whether we can
derive it or not. For set FD1, (A)+ = {A,B,C}. It means that A can’t functionally
determine D.
  So A->D will not hold in FD1.

As all FDs in set FD2 do not hold in set FD1, FD2 ⊄ FD1.

Step 3: In this case, FD2 ⊃ FD1 and FD2 ⊄ FD1, these two FD sets are not semantically
equivalent.
LC3: Dependency preserving Decomposition, examples;
In a Database Management System (DBMS), dependency-preserving
decomposition refers to the process of breaking down a complex database schema into
simpler, smaller tables, such that all the functional dependencies of the original schema
are still enforceable without needing to perform additional joins.
This approach is crucial for database normalization as it minimizes redundancy, prevents
anomalies, and improves the efficiency of database queries. To achieve dependency-
preserving decomposition, algorithms like lossless join decomposition and dependency-
preserving decomposition are applied, ensuring that all original dependencies can be
represented directly in the decomposed tables.
Example:
Suppose R is a relational schema and F is the set of functional dependencies on R. If R is
decomposed into relations R1, R2, ………….…… Rn , each holding functional
dependencies F1, F2, …….……… Fn respectively. We can say, F` = F1 U F2 U ………..
… U Fn.
Now this decomposition will be considered as dependency preserving decomposition if

Every dependency in F is logically implied by F` i.e. F`+ = F+ It is obvious that F1 ⊆

and only if-

F+, F2 ⊆ F + and so on.

If we verify that F` is satisfied in R, we have verified that decomposition is dependency
preserving decomposition i.e. F1 U F2 = F.
Let’s say:
 The original relation R has a set of functional dependencies (FDs) called F.
 When we decompose R into R1 and R2, each gets its own FDs:
o f1: FDs in R1

o f2: FDs in R2

 The combined FDs from R1 and R2 are f1∪f2.

Now, there are three possible cases:

Case 1: f1∪f2=F
 This means the FDs from R1 and R2 together exactly match the original FDs F.
 Result: The decomposition is dependency-preserving because we haven’t lost any
FDs.
Example:
Original R:
| StudentID | CourseID | Instructor |
Functional Dependencies F:
 CourseID→Instructor
 StudentID,CourseID→Instructor
After decomposition:
1. R1(StudentID,CourseID): f1={StudentID,CourseID→Instructor}
2. R2(CourseID,Instructor): f2={CourseID→Instructor}

Here, f1∪f2=F.
The decomposition is dependency-preserving.

Case 2: f1∪f2⊂F

 This means some FDs from the original set F are missing in f1∪f2.
 Result: The decomposition is not dependency-preserving, as we’ve lost some FDs.
Example:
Original R:
| StudentID | CourseID | Instructor |
Functional Dependencies F:
 StudentID,CourseID→Instructor
 CourseID→Instructor
After decomposition:
1. R1(StudentID,CourseID): f1={StudentID,CourseID→Instructor}
2. R2(CourseID,Instructor): f2={}

Here, f1∪f2⊂F.
The FD CourseID→InstructorCourseID is missing.
The decomposition is not dependency-preserving.

Case 3: f1∪f2⊃F
 This means the FDs from R1R_1R1 and R2R_2R2 contain extra dependencies that
were not part of F.
 Result: This case is technically possible but uncommon. These extra dependencies
may not cause direct problems but could lead to inconsistencies or unexpected
behavior.
Example:
Original R:
| StudentID | CourseID | Instructor |
Functional Dependencies F:
 CourseID→Instructor
After decomposition:
1. R1(StudentID,CourseID): f1={CourseID→Instructor}
2. R2(CourseID,Instructor): f2={Instructor→CourseID}

Here, f1∪f2⊃F, as the FD Instructor→CourseID was added unnecessarily.

The decomposition has extra dependencies, which could lead to confusion but doesn’t
directly violate dependency preservation.
Key Concepts of Dependency Preserving Decomposition in DBMS
The key concepts of dependency-preserving decomposition include:
 Functional Dependency Preservation: This means that after decomposition, the
functional dependencies in the original schema must still hold true in the decomposed
schema.
 Lossless Join Property: The decomposition must allow for the original relation to be
reconstructed from the decomposed relations without any data loss, ensuring no
information is discarded.
 Normalization: The decomposition often aims to normalize the schema to higher
normal forms (like 3NF or BCNF), which further eliminates redundancy and
dependency anomalies.
 Minimal Redundancy: By ensuring the decomposition preserves functional
dependencies, it minimizes data redundancy and helps in avoiding data anomalies.
 If we decompose a relation R into relations R1 and R2, all dependencies of R must be
part of either R1 or R2 or must be derivable from combination of functional
dependencies(FD) of R1 and R2
 Suppose a relation R(A,B,C,D) with FD set {A->BC} is decomposed into R1(ABC)
and R2(AD) which is dependency preserving because FD A->BC is a part of
R1(ABC).
Theory
 Consider a schema R(A,B,C,D) and functional dependencies A->B and C->D which
is decomposed into R1(AB) and R2(CD)
This decomposition is dependency preserving decompostion because
 A->B can be ensured in R1(AB)
 C->D can be ensured in R2(CD)

Example:
Let a relation R(A,B,C,D) and set a FDs F = { A -> B , A -> C , C -> D} are given.
A relation R is decomposed into –
R1 = (A, B, C) with FDs F1 = {A -> B, A -> C}, and
R2 = (C, D) with FDs F2 = {C -> D}.

F' = F1 ∪ F2 = {A -> B, A -> C, C -> D}

so, F' = F.
And so, F'+ = F+.
Thus, the decomposition is dependency preserving decomposition.

LC4: Lossless Join Verification:

Lossless Decomposition:
Lossless decomposition is a technique of breaking down a table into smaller tables
without losing data. It is a key part of database design that helps maintain data integrity
and accuracy.
A decomposition is said to be lossless decomposition if it satisfies the following
conditions
1. The Union of Attributes of R1 and R2 must be equal to the attribute of R. Each
attribute of R must be either in R1 or in R2.
Att(R1) U Att(R2) = Att(R)
2. The intersection of Attributes of R1 and R2 must not be NULL.
Att(R1) ∩ Att(R2) ≠ Φ
3. The common attribute must be a key for at least one relation (R1 or R2)
Att(R1) ∩ Att(R2) -> Att(R1) or Att(R1) ∩ Att(R2) -> Att(R2)
Example 1:
 For Example, A relation R (A, B, C, D) with FD set{A->BC} is decomposed into
R1(ABC) and R2(AD) which is a lossless join decomposition as:
1. First condition holds true as Att(R1) U Att(R2) = (ABC) U (AD) = (ABCD) = Att(R).
2. Second condition holds true as Att(R1) ∩ Att(R2) = (ABC) ∩ (AD) ≠ Φ
3. The third condition holds as Att(R1) ∩ Att(R2) = A is a key of R1(ABC) because A-
>BC is given.
Then we can say that the above decomposition is a lossless decomposition
Example 2:
For Example, A relation R (A, B, C, D) with FD set{A->BC} is decomposed into R1(AB)
and R2(cD) which is a lossless join decomposition as:
First condition holds true as Att(R1) U Att(R2) = (AB) U (CD) = (ABCD) = Att(R).
Second condition holds true as Att(R1) ∩ Att(R2) = (AB) ∩ (CD)= Φ. Here the
condition fails. Hence the above decomposition is not a lossless decomposition.
Lossy decomposition:
s the name suggests, lossy decomposition means when we perform join operation on the
sub-relations it doesn't result to the same relation which was decomposed. After the join
operation, we always found some extraneous tuples. These extra tuples genrates difficulty
for the user to identify the original tuples.
Example:
We have a relation R(A, B, C)

A B C

1 2 1

2 5 3

3 3 3

Now , we decompose it into sub-relations R1 and R2

R1(A, B)
A B

1 2

2 5

3 3

R2(B, C)

B C

2 1

5 3

3 3

Now After performing join operation

A B C

1 2 1

2 5 3

2 3 3

3 5 3

3 3 3
LC5: First normal form, partial dependency, second normal Form;
Normalization:
In Database Management Systems (DBMS), normalization is the process of organizing data
to reduce redundancy and improve data integrity by structuring data into smaller, well-
organized tables with relationships between them.
Normal Forms:
Normalization follows a series of rules called normal forms (1NF, 2NF, 3NF, etc.) that guide
the structuring of data.
 First Normal Form (1NF): Ensures that each column contains atomic values (single,
indivisible values).
 Second Normal Form (2NF): Builds upon 1NF and requires that all non-key
attributes are fully dependent on the primary key.
 Third Normal Form (3NF): Further refines 2NF by eliminating transitive
dependencies, meaning that non-key attributes should not be dependent on other non-
key attributes.
 Boyce-Codd Normal Form (BCNF): A stricter version of 3NF that ensures every
determinant is a candidate key.
 Fourth Normal Form (4NF): Addresses multi-valued dependencies.
 Fifth Normal Form (5NF): Addresses join dependencies
First Normal Form
 First Normal Form (1NF) ensures that each column in a table contains single,
indivisible values, and each row is unique. A table violates 1NF if it has composite
or multi-valued attributes, such as multiple phone numbers in one cell. 1NF
simplifies data, making it consistent and easy to manage.

table is in 1 NF if:
 There are only Single Valued Attributes.
 Attribute Domain does not change.
 There is a unique name for every Attribute/Column.
 The order in which data is stored does not matter.
Rules for First Normal Form (1NF) in DBMS
To follow the First Normal Form (1NF) in a database, these simple rules must be
followed:
1. Every Column Should Have Single Values
Each column in a table must contain only one value in a cell. No cell should hold
multiple values. If a cell contains more than one value, the table does not follow
1NF.
 Example: A table with columns like [Writer 1], [Writer 2], and [Writer 3] for the
same book ID is not in 1NF because it repeats the same type of information
(writers). Instead, all writers should be listed in separate rows.
2. All Values in a Column Should Be of the Same Type
Each column must store the same type of data. You cannot mix different types of
information in the same column.
 Example: If a column is meant for dates of birth (DOB), you cannot use it to store
names. Each type of information should have its own column.
3. Every Column Must Have a Unique Name
Each column in the table must have a unique name. This avoids confusion when
retrieving, updating, or adding data.
 Example: If two columns have the same name, the database system may not know
which one to use.
4. The Order of Data Doesn’t Matter
In 1NF, the order in which data is stored in a table doesn’t affect how the table
works. You can organize the rows in any way without breaking the rules.

Example:
Consider the below COURSES Relation :
In the above table, Courses has a multi-valued attribute, so it is not in 1NF. The
Below Table is in 1NF as there is no multi-valued attribute.

In Conclusion, First Normal Form (1NF) is a key idea in

relational database architecture. It guarantees that data is organized to facilitate data
processing, remove redundancy, and support data integrity. 1NF establishes the
foundation for more complex normalization strategies that further improve the
correctness and efficiency of database systems by imposing atomic values and
forbidding recurring groupings inside rows.

Second Normal Form

A relation is in 2NF if it is in 1NF and any non-prime attribute (attributes which are
not part of any candidate key) is not partially dependent on any proper subset of any
candidate key of the table. In other words, we can say that, every non-prime
attribute must be fully dependent on each candidate key.
A functional dependency X->Y (where X and Y are set of attributes) is said to be
in partial dependency, if Y can be determined by any proper subset of X.

Example-1: Consider the table below.

  There are many courses having the same course fee. Here, COURSE_FEE cannot
alone decide the value of COURSE_NO or STUD_NO.
 COURSE_FEE together with STUD_NO cannot decide the value of COURSE_NO.
 COURSE_FEE together with COURSE_NO cannot decide the value of STUD_NO.
 The candidate key for this table is {STUD_NO, COURSE_NO} because the
combination of these two columns uniquely identifies each row in the table.
 COURSE_FEE is a non-prime attribute because it is not part of the candidate
key {STUD_NO, COURSE_NO}.
 But, COURSE_NO -> COURSE_FEE, i.e., COURSE_FEE is dependent on
COURSE_NO, which is a proper subset of the candidate key.
 Therefore, Non-prime attribute COURSE_FEE is dependent on a proper subset of the
candidate key, which is a partial dependency and so this relation is not in 2NF.
To convert the above relation to 2NF, we need to split the table into two tables such as : Table
1: STUD_NO, COURSE_NO Table 2: COURSE_NO, COURSE_FEE.
Now, each table is in 2NF:
 The Course Table ensures that COURSE_FEE depends only on COURSE_NO.
  The Student-Course Table ensures there are no partial dependencies because it only
relates students to courses.
NOTE: 2NF tries to reduce the redundant data getting stored in memory. For instance, if
there are 100 students taking C1 course, we don’t need to store its Fee as 1000 for all the 100
records, instead, once we can store it in the second table as the course fee for C1 is 1000.
What is Partial Dependency?
A functional dependency denoted as X→Y where X and Y are an attribute set of a relation, is
a partial dependency , if some attribute A∈X can be removed and the dependency still holds.
For example, if you have a functional dependency X→Y, where X is a composite candidate
key (made of multiple columns), and you can remove one column from X, but the
dependency still works, then it’s a partial dependency.
Let’s say X={A,B} and A→Y still holds true even without B. This means Y doesn’t really
depend on the full key {A,B}, but only on part of it (A). This is called a partial dependency. It
often leads to redundancy and is something we fix when we normalize a table to Second
Normal Form (2NF).

In conclusion, Second Normal Form (2NF) helps make databases more organized by
removing partial dependencies. It reduces duplicate data, prevents errors, and ensures data is
stored accurately. Following 2NF makes it easier to manage, update, and retrieve information
from your database.
LC6: Transitive dependency, third normal form, Motivation for BCNF;
Third Normal Form
A relation is in Third Normal Form (3NF) if it is already in Second Normal Form (2NF) and
does not have transitive dependencies for non-prime attributes.
Below mentioned is the basic condition that must be hold in the non-trivial functional
dependency X -> Y:
X is a super key.
or
Y is a prime attribute (each element of Y is part of some candidate key).
If A->B and B->C are two FDs then A->C is called transitive dependency.
The normalization of 2NF relations to 3NF involves the removal of transitive dependencies.
If a transitive dependency exists, we remove the transitively dependent attribute(s) from the
relation by placing the attribute(s) in a new relation along with a copy of the determinant.
Consider the examples given below.
Example : Consider the below Relation,
In the relation CANDIDATE given above:
 Functional dependency Set: {CAND_NO -> CAND_NAME, CAND_NO -
>CAND_STATE, CAND_STATE -> CAND_COUNTRY, CAND_NO ->
CAND_AGE}
 So, Candidate key here would be: {CAND_NO}
 For the relation given here in the table, CAND_NO -> CAND_STATE and
CAND_STATE -> CAND_COUNTRY are actually true. Thus, CAND_COUNTRY
depends transitively on CAND_NO. This transitive relation violates the rules of being
in the 3NF. So, if we want to convert it into the third normal form, then we have to
decompose the relation CANDIDATE (CAND_NO, CAND_NAME, CAND_STATE,
CAND_COUNTRY, CAND_AGE) as:
CANDIDATE (CAND_NO, CAND_NAME, CAND_STATE, CAND_AGE)
STATE_COUNTRY (STATE, COUNTRY).
What is Transitive Dependency?
A transitive dependency occurs when a non-key attribute depends on the another non-key
attribute rather than directly on the primary key. For instance, consider a table with the
attributes (A, B, C) where A is the primary key and B and C are non-key attributes. If B
determines C then C is transitively dependent on the A through B. This can lead to data
anomalies and redundancy which 3NF aims to eliminate by the ensuring that all non-key
attributes depend only on the primary key.
Conclusion
In conclusion, a crucial stage in database normalization is Third Normal Form (3NF). It deals
with transitive dependencies and improves data integrity through effective information
organization. 3NF ensures that non-key properties only depend on the primary key ,
removing redundancy and helping to create a well-organized and normalized relational
database model
BCNF
BCNF (Boyce-Codd Normal Form) is a advanced version of Third Normal Form where one
column depends on another, unless the first column is a candidate key (a column or
combination of columns that can uniquely identify a row). The basic condition for any
relation to be in BCNF is that it must be in Third Normal Form.
In simpler words, if any column in the table determines another column’s value, the
determining column must be unique (a candidate key). BCNF fixes any issues left by 3NF
We have to focus on some basic rules that are for BCNF:
1. Table must be in Third Normal Form.
2. In relation X->Y, X must be a super-key in a relation.
Rules for BCNF
Rule 1: The table should be in the 3rd Normal Form.
Rule 2: X should be a super-key for every functional dependency (FD) X−>Y in a given
relation.
Note: To test whether a relation is in BCNF, we identify all the determinants and make sure
that they are candidate keys.
To determine the highest normal form of a given relation R with functional dependencies, the
first step is to check whether the BCNF condition holds. If R is found to be in BCNF, it can
be safely deduced that the relation is also in 3NF, 2NF, and 1NF. The 1NF has the least
restrictive constraint – it only requires a relation R to have atomic values in each tuple. The
2NF has a slightly more restrictive constraint.
The 3NF has a more restrictive constraint than the first two normal forms but is less
restrictive than the BCNF. In this manner, the restriction increases as we traverse down the
hierarchy.
We are going to discuss some basic examples which let you understand the properties of
BCNF. We will discuss multiple examples here.
Example 1
Consider a relation R with attributes (student, teacher, subject).

FD: { (student, Teacher) -> subject, (student, subject) -> Teacher, (Teacher) -> subject}
 Candidate keys are (student, teacher) and (student, subject).
 The above relation is in 3NF (since there is no transitive dependency). A relation R is
in BCNF if for every non-trivial FD X->Y, X must be a key.
 The above relation is not in BCNF, because in the FD (teacher->subject), teacher is
not a key. This relation suffers with anomalies −
 For example, if we delete the student Tahira , we will also lose the information that
N.Gupta teaches C. This issue occurs because the teacher is a determinant but not a
candidate key.
R is divided into two relations R1(Teacher, Subject) and R2(Student, Teacher).
LC7:BCNF, Multivalued dependency, Fourth normal form.;
Fourth Normal Form
Fourth Normal Form contains no non-trivial multivalued dependency except candidate key.
The basic condition with Fourth Normal Form is that the relation must be in BCNF.
The basic rules are mentioned below.
1.It must be in BCNF.
2. It does not have any multi-valued dependency.
Two of the highest levels of database normalization are the fourth normal form (4NF) and the
fifth normal form (5NF). Multivalued dependencies are handled by 4NF, whereas join
dependencies are handled by 5NF.
If two or more independent relations are kept in a single relation or we can say multivalue
dependency occurs when the presence of one or more rows in a table implies the presence of
one or more other rows in that same table. Put another way, two attributes (or columns) in a
table are independent of one another, but both depend on a third attribute. A multivalued
dependency always requires at least three attributes because it consists of at least two
attributes that are dependent on a third.
For a dependency A -> B, if for a single value of A, multiple values of B exist, then the table
may have a multi-valued dependency. The table should have at least 3 attributes and B and C
should be independent for A ->> B multivalued dependency.
Example:

Person Mobile Food_Likes

9893/942
Mahesh Burger/Pizza
4

Ramesh 9191 Pizza

Person->-> mobile,
Person ->-> food_likes
This is read as “person multi determines mobile” and “person multi determines food_likes.”
Note that a functional dependency is a special case of multivalued dependency. In a
functional dependency X -> Y, every x determines exactly one y, never more than one.
Fourth Normal Form (4NF)
The Fourth Normal Form (4NF) is a level of database normalization where there are no non-
trivial multivalued dependencies other than a candidate key. It builds on the first three normal
forms (1NF, 2NF, and 3NF) and the Boyce-Codd Normal Form (BCNF). It states that, in
addition to a database meeting the requirements of BCNF, it must not contain more than one
multivalued dependency.
Properties
A relation R is in 4NF if and only if the following conditions are satisfied:
1. It should be in the Boyce-Codd Normal Form (BCNF).
2. The table should not have any Multi-valued Dependency.
A table with a multivalued dependency violates the normalization standard of the Fourth
Normal Form (4NF) because it creates unnecessary redundancies and can contribute to
inconsistent data. To bring this up to 4NF, it is necessary to break this information into two
tables.
Example: Consider the database table of a class that has two relations R1 contains student
ID(SID) and student name (SNAME) and R2 contains course id(CID) and course name
(CNAME).
Table R1
SID SNAME

S
A
1

S
B
2

Table R2

CID CNAME

C1 C

C2 D

When their cross-product is done it resulted in multivalued dependencies.

Table R1 X R2

SID SNAME CID CNAME

S
A C1 C
1

S
A C2 D
1

S
B C1 C
2

S
B C2 D
2

Multivalued dependencies (MVD) are:

SID->->CID; SID->->CNAME; SNAME->->CNAME
oin Dependency
Join decomposition is a further generalization of Multivalued dependencies. If the join of R1
and R2 over C is equal to relation R then we can say that a join dependency (JD) exists,

B, C, D). Alternatively, R1 and R2 are a lossless decomposition of R. A JD ⋈ {R1, R2, …,

where R1 and R2 are the decomposition R1(A, B, C) and R2(C, D) of a given relations R (A,

Rn} is said to hold over a relation R if R1, R2, ….., Rn is a lossless-join decomposition. The
*(A, B, C, D), (C, D) will be a JD of R if the join of joins attribute is equal to the relation R.
Here, *(R1, R2, R3) is used to indicate that relation R1, R2, R3 and so on are a JD of R. Let
R is a relation schema R1, R2, R3……..Rn be the decomposition of R. r( R ) is said to satisfy
join dependency if and only if

Joint Dependency
Example:
Table R1

Company Product

C1 Pendrive

C1 mic

C2 speaker

C2 speaker

Company->->Product
Table R2

Agent Company

Aman C1

Aman C2
Agent Company

Moha
C1
n

Agent->->Company
Table R3

Agent Product

Aman Pendrive

Aman Mic

Aman speaker

Moha
speaker
n

Agent->->Product

Table R1⋈R2⋈R3

Company Product Agent

Pendriv
C1 Aman
e

C1 mic Aman

C2 speaker speaker

C1 speaker Aman

Agent->->Product
LC8: Triggers:
A trigger is a stored procedure in a database that automatically invokes whenever a special
event in the database occurs. By using SQL triggers, developers can automate tasks,
ensure data consistency, and keep accurate records of database activities. For example, a
trigger can be invoked when a row is inserted into a specified table or when specific table
columns are updated.
In simple words, a trigger is a collection of SQL statements with particular names that are
stored in system memory. It belongs to a specific class of stored procedures that are
automatically invoked in response to database server events. Every trigger has a table
attached to it.
Key Features of SQL Triggers:
 Automatic Execution: Triggers fire automatically when the defined event occurs (e.g.,
INSERT, UPDATE, DELETE).
 Event-Driven: Triggers are tied to specific events that take place within the database.
 Table Association: A trigger is linked to a specific table or view, and operates
whenever changes are made to the table’s data.
Syntax
create trigger [trigger_name]
[before | after]
{insert | update | delete}
on [table_name]
FOR EACH ROW
BEGIN
END;
Key Terms
 trigger_name: The name of the trigger to be created.
 BEFORE | AFTER: Specifies whether the trigger is fired before or after the triggering
event (INSERT, UPDATE, DELETE).
 {INSERT | UPDATE | DELETE}: Specifies the operation that will activate the trigger.
 table_name: The name of the table the trigger is associated with.
 FOR EACH ROW: Indicates that the trigger is row-level, meaning it executes once
for each affected row.
 trigger_body: The SQL statements to be executed when the trigger is fired.
Types of SQL Triggers
Triggers can be categorized into different types based on the action they are associated with:
1. DDL Triggers
The Data Definition Language (DDL) command events such
as Create_table, Create_view, drop_table, Drop_view, and Alter_table cause the DDL
triggers to be activated. They allow us to track changes in the structure of the database. The
trigger will prevent any table creation, alteration, or deletion in the database.
Example:
CREATE TRIGGER prevent_table_creation
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
PRINT 'you can not create, drop and alter table in this database';
ROLLBACK;
END;
2. DML Triggers
The Data manipulation Language (DML) command events that begin with Insert, Update,
and Delete set off the DML triggers. DML triggers are used for data validation, ensuring that
modifications to a table are done under controlled conditions.
Example:
CREATE TRIGGER prevent_update
ON students
FOR UPDATE
AS
BEGIN
PRINT 'You can not insert, update and delete this table i';
ROLLBACK;
END;
3. Logon Triggers
These triggers are fired in response to logon events. Logon triggers are useful for monitoring
user sessions or restricting user access to the database. As a result, the PRINT
statement messages and any errors generated by the trigger will all be visible in the SQL
Server error log. Authentication errors prevent logon triggers from being used. These triggers
can be used to track login activity or set a limit on the number of sessions that a given login
can have in order to audit and manage server sessions.
Example:
CREATE TRIGGER track_logon
ON LOGON
AS
BEGIN
PRINT 'A new user has logged in.';
END;

Triggers are tied to a specific table and can run either before or after the event.
There are 6 types of Triggers.
1. BEFORE INSERT Trigger
Runs before a new row is inserted into a table. It can modify the new data or stop the
operation if conditions aren’t met.
CREATE TRIGGER before_insert_user
BEFORE INSERT ON users FOR EACH ROW
BEGIN -- Set the creation date to the current timestamp
SET NEW.created_at = NOW();
-- Ensure the email is in lowercase
SET NEW.email = LOWER(NEW.email);
END;
INSERT INTO users (name, email) VALUES ('John Doe', 'John@Example.COM');
SELECT * FROM users;
2. AFTER INSERT Trigger
Runs after a new row is inserted. Perfect for logging or updating related tables.
CREATE TRIGGER after_insert_user
AFTER INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO user_logs(user_id, action, action_time) VALUES (NEW.id, 'INSERT',
NOW());
END;
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
SELECT * FROM user_logs;
3. BEFORE UPDATE Trigger
Runs before updating a row. Useful for logging old values or applying automatic changes.
CREATE TRIGGER before_update_user
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN -- Update the updated_at timestamp
SET NEW.updated_at = NOW();
END;
UPDATE users SET name = 'Alice Johnson' WHERE id = 1;
SELECT * FROM users WHERE id = 1;
4. AFTER UPDATE Trigger
Runs after updating a row. Great for logging or synchronizing data.
CREATE TRIGGER after_update_user
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
INSERT INTO user_logs(user_id, action, action_time) VALUES (OLD.id, 'UPDATE',
NOW());
END;
UPDATE users SET email = 'alice.j@example.com' WHERE id = 1;
SELECT * FROM user_logs;
5. BEFORE DELETE Trigger
Runs before a row is deleted. Useful for archiving data or preventing accidental deletions.
CREATE TRIGGER before_delete_user
BEFORE DELETE ON users
FOR EACH ROW
BEGIN -- Archive the deleted user
INSERT INTO deleted_users(id, name, email, deleted_at) VALUES (OLD.id, OLD.name,
OLD.email, NOW());
END;
DELETE FROM users WHERE id = 1;
SELECT * FROM deleted_users;
6. AFTER DELETE Trigger
Runs after a row is deleted. Ideal for cleanup tasks or logging deletions.
CREATE TRIGGER after_delete_user
AFTER DELETE ON users
FOR EACH ROW
BEGIN INSERT INTO user_logs(user_id, action, action_time) VALUES (OLD.id,
'DELETE', NOW());
END;
DELETE FROM users WHERE id = 2;
SELECT * FROM user_logs;

Advantages of Triggers
 Data Integrity: Triggers help enforce consistency and business rules, ensuring that
data follows the correct format.
 Automation: Triggers eliminate the need for manual intervention by automatically
performing tasks such as updating, inserting, or deleting records when certain
conditions are met.
 Audit Trail: Triggers can track changes in a database, providing an audit trail
of INSERT, UPDATE, and DELETE operations.
 Performance: By automating repetitive tasks, triggers improve SQL query
performance and reduce manual workload.
SQL triggers are a powerful feature for automating and enforcing rules in our database
management system. Whether we’re ensuring data integrity, automating updates, or
preventing unauthorized changes, triggers can save time and improve consistency
in database operations. With various types of triggers available, including DML
triggers, DDL triggers, and logon triggers, they can be tailored to a wide range of use cases.
Understanding and implementing SQL triggers effectively is important for maintaining
a strong and efficient database.