Functional Dependencies

Functional dependencies are necessary because they:

 Define the logical structure of a relation.
 Expose redundancy and anomalies.
 Allow us to discover keys and constraints.
 Provide the basis for normalization (1NF–5NF).
 Guarantee consistent, reliable, efficient database design.
What is Functional Dependency (FD)?
 Let attributes X and Y are two subsets of attributes of relation R.
Student
X is called Determinant, and Y is called the Dependent
RollNo Name SPI BL X→Y
101 Raju 8 0
102 Mitesh 7 1 X Y
103 Jay 7 0 Determinant should be unique.

 For the given Student Relation schema, RollNo is a candidate key

 X = {RollNo} and Y={Name, SPI, BL}
 If the values of the X component of a tuple uniquely (or functionally) determine the values of
the Y component, then there is a functional dependency from X to Y.
 This is denoted by X → Y
 If t1[X]=t2[X], then t1[Y]=t2[Y] in any relation instance r(R)
 (i.e RollNo → Name, SPI, BL).
Diagrammatic representation of Functional Dependency (FD)
X→Y {X1, X2} → Y X → {Y1, Y2}

X Y X1 X2 Y X Y1 Y2

 Example
 Consider the relation Account(account_no, balance, branch). (X=Determinant ; Y =Dependent)
 account_no can determine balance and branch.
 So, there is a functional dependency from account_no to balance and branch.
 This can be denoted by account_no → {balance, branch}.

Arrow
account_no balance branch
diagram Incoming
edge
Relation Table: STUDENT (Think for a moment to identify FDs)
RollNo Name DeptID DeptName Phone

101 Alice CSE Computer 9991112222

Science
102 Bob ECE Electronics 8883334444
103 Charlie CSE Computer 7775556666
Science
104 David EEE Electrical Engg 6664445555

 Fact from Student relation = RollNo uniquely identifies a student’s details.

 Functional Dependency 1: RollNo → Name, DeptID, Phone

 Fact from Student relation = Each department ID has exactly one department name.
 Functional Dependency 2: DeptID → DeptName
Not Functional Dependencies (Not FDs)
RollNo Name DeptID DeptName Phone

101 Alice CSE Computer 9991112222

Science
102 Bob ECE Electronics 8883334444
103 Charlie CSE Computer 7775556666
Science
104 David EEE Electrical Engg 6664445555

 1. Name ↛ DeptID (FD does not holds for student relation)

 Fact from Student relation = A name may appear in multiple departments.

 2. Phone ↛ DeptName (FD does not holds for student relation)

 Fact from Student relation = Phone number does not logically determine department name.
Types of Functional Dependency (FD)
1. Fully Functional Dependency
2. Partial Functional Dependency
[Link] Functional Dependency (FD)
1. Fully Functional Dependency
 Determinants should have the minimal number of attributes necessary to maintain the functional dependency
with the attribute(s) on the right hand-side.
 This requirement is called fully functional dependency.
 In a relation, the attribute B is fully functional dependent on A if B is functionally dependent on A, but not on
any proper subset of A.
 Eg. {Roll_No, Semester, Department_Name} → SPI
 We need all three {Roll_No, Semester, Department_Name} to find SPI.
A B

Roll_No Semester Dept_Name SPI

Note:
• SPI – Student performance index depends on a specific student Roll_No belonging to specific department in a specific semester
• Cannot be determined by Roll_No alone or Dept_Name or Semester alone.
Partial Functional Dependency (FD)
 In a relation, the attribute B is partial functional dependent on A if B is functionally dependent on any proper
subset of A.
 If there is some attribute that can be removed from A and still the dependency holds then it is partial
functional dependency.
 Eg. {Roll_No, Department_Name} → SPI
 Roll_No is sufficient to find SPI, Department_Name is not required to find SPI.
Partial Functional Dependency (PFD)
 Definition: A non-key attribute depends only on part of a composite key.
Enrollment Relation
primary key is composite: {StudentID, CourseID}
StudentID CourseID StudentName CourseName

101 CSE101 Alice DBMS

102 CSE102 Bob Networks

103 CSE101 Charlie DBMS

Examples: StudentID → StudentName

 StudentName depends only on StudentID, not the full key.

 CourseID → CourseName
 CourseName depends only on CourseID, not the full key.
Armstrong's axioms OR
Inference rules
Classification of FD
 Stated Functional dependencies → FDs which are specified, are called stated FDs.

 Inferred Functional dependencies → FDs which are derived, are called as inferred FDs.
Armstrong's axioms OR Inference rules (Properties of [Link])
 Armstrong's axioms are a set of rules used to infer (derive) all the functional dependencies on
a relational database.
Reflexivity Augmentation Self-determination
 If B is a subset of A  If A → B  If A → A
 then A → B  then AC → BC

Transitivity Pseudo Transitivity Decomposition

 If A → B and B → C  If A → B and BD → C  If A → BC
 then A → B and A →
 then A → C  then AD → C C

Union Composition
 If A → B and A → C  If A → B and C → D
 then A → BC  then AC → BD
Types of Functional Dependency (FD)
 1. Transitive Functional Dependency
 In a relation, if attribute(s) A → B and B → C, then A → C (means C is transitively depends on A via B).
Sub_Fac
Subject Faculty Age
DS Shah 35
DBMS Patel 32
DF Shah 35

 Eg: If Subject → Faculty & Faculty → Age

Then Subject → Age

 Therefore, as per the rule of transitive dependency: Subject → Age should hold, that makes sense because if
we know the subject name we can know the faculty’s age.
Transitive Dependency
 Definition: A dependency X → Z is transitive if X → Y and Y → Z hold.

Relation Table: EMPLOYEE

EmpID EmpName DeptID DeptName
201 Alice CSE Computer Science

202 Bob ECE Electronics

203 Charlie CSE Computer Science

Example in EMPLOYEE:
 EmpID → DeptID
 DeptID → DeptName
 EmpID → DeptName (transitive dependency)
 Causes redundancy and update anomalies.
Types of Functional Dependency (FD)

 3. Nontrivial Functional Dependency

 X → Y is nontrivial FD if Y is not a subset of X
 Eg. {Roll_No, Department_Name, Semester} → Student_Name
Non-Trivial Functional Dependency
 Definition:
 X → Y is non-trivial if Y is not a subset of X.

Relation Table: EMPLOYEE

EmpID EmpName DeptID DeptName

201 Alice CSE Computer Science

202 Bob ECE Electronics

203 Charlie CSE Computer Science

 Examples in EMPLOYEE:
 EmpID → EmpName
 DeptID → DeptName
 Important for schema design and normalization.
Types of Functional Dependency (FD)
 2. Trivial Functional Dependency
 X → Y is trivial FD if Y is a subset of X
 Eg. {Roll_No, Department_Name, Semester} → Roll_No
Trivial Functional Dependency
 Definition: X → Y is trivial if Y is a subset of X.

Relation Table: EMPLOYEE

EmpID EmpName DeptID DeptName
201 Alice CSE Computer Science

202 Bob ECE Electronics

203 Charlie CSE Computer Science

Examples:
 {EmpID, DeptID} → EmpID
 {EmpID, EmpName} → EmpName
 Always true, provides no new information
Closure of a set of FDs
What is closure of a set of FDs?
 In database theory, the closure of an attribute set F, written as F+, is the set of all attributes that
can be uniquely determined from F using the given set of functional dependencies.
 The set of functional dependencies (FDs) that is logically implied by F is called the closure of
F. It is denoted by F+.

 E.g.: If F = {A → B and B → C},

Then we can infer that A → C (by transitivity rule)
Why Closure is Needed?
 It’s a key tool to test keys, redundancy, and normalization.
 To find candidate keys (if X⁺ = all attributes, then X is a key).
 To check if a dependency is redundant.
 To test if one set of dependencies implies another.
 To guide normalization.
Closure of a set of FDs [Example]
 Suppose we are given a relation schema R(A,B,C,G,H,I) and the set of functional dependencies
are:
 F = (A → B, A → C, CG → H, CG → I, B → H)
▪ The functional dependency A → H is logical implied.

We have
A→B
Transitivity rule A→H
B→H
Closure of a set of FDs [Example]
 Suppose we are given a relation schema R(A,B,C,G,H,I) and the set of functional dependencies
are:
 F = (A → B, A → C, CG → H, CG → I, B → H)
▪ The functional dependency CG → HI is logical implied.

We have
CG → H
Union rule CG → HI
CG → I
Closure of a set of FDs [Example]
 Suppose we are given a relation schema R(A,B,C,G,H,I) and the set of functional dependencies
are:
 F = (A → B, A → C, CG → H, CG → I, B → H)
▪ The functional dependency AG → I is logical implied.

We have
A→C Pseudo-
AG → I
CG → I transitivity rule
Closure of a set of FDs [Example]
 Suppose we are given a relation schema R(A,B,C,G,H,I) and the set of functional dependencies
are:
 F = (A → B, A → C, CG → H, CG → I, B → H)
▪ The functional dependency AG → I is logical implied.

We have
A→C Augmentation rule AG → CG

AG → CG
Transitivity rule AG → I
CG → I
Closure of a set of FDs [Example]
 Compute the closure of the following set F of functional dependencies FDs for relational
schema R = (A,B,C,D,E,F):
 F = (A → B, A → C, CD → E, CD → F, B → E)
▪ Find out the closure of F.

A→B&A→C Union Rule A → BC

CD → E & CD → F Union Rule CD → EF
A→B&B→E Transitivity Rule A→E
A → C & CD → E Pseudo-transitivity Rule AD → E
A → C & CD → F Pseudo-transitivity Rule AD → F

F+ = (A → BC, CD → EF, A → E, AD → E, AD → F)
Closure of a set of FDs [Example]
 Compute the closure of the following set F of functional dependencies FDs for relational
schema R = (A,B,C,D,E):
 F = (AB → C, D → AC, D → E )
▪ Find out the closure of F.

D → AC Decomposition Rule D→A&D→C

D → AC & D → E Union Rule D → ACE

F+ = (D → A, D → C, D → ACE)
Closure of attribute sets
What is a closure of attribute sets?
 Given a set of attributes α, the closure of α under F is the set of attributes that are functionally
determined by α under F.
 It is denoted by α+.

Algorithm
 Algorithm to compute α+, the closure of α under F
 Steps
1. result = α
2. while (changes to result) do
 for each β → γ in F do
▪ begin
• if β ⊆ result then result = result U γ
• else result = result
▪ end
Closure of attribute sets [Example]
 Consider the relation schema R = (A, B, C, G, H, I).
 For this relation, a set of functional dependencies F can be given as
F = {A → B, A → C, CG → H, CG → I, B → H}

A B C G H I

 Find out the closure of (AG)+.

 Closure of attribute sets [Example]
 Consider the relation schema R = (A, B, C, G, H, I).
 For this relation, a set of functional dependencies F can be given as
F = {A → B, A → C, CG → H, CG → I, B → H}
 Find out the closure of (AG)+.  Step 1.
Algorithm result = α => result of AG+ = AG
 Algorithm to compute α+, the closure of α under F
A→B A ⊆ AG result of AG+ = ABG
 Steps
1. result = α A→C A ⊆ ABG result of AG+ = ABCG
2. while (changes to result) do CG → H CG ⊆ ABCG result of AG+ = ABCGH
 for each β → γ in F do
▪ begin CG → I CG ⊆ ABCGH result of AG+ = ABCGHI
• if β ⊆ result then result = result U γ
• else result = result B→H B ⊆ ABCGHI result of AG+ = ABCGHI
▪ end
AG+ = ABCGHI
Candidate key
 A candidate key must satisfy two properties:
 Closure covers all attributes (i.e., it must functionally determine the whole relation).
 Minimality (no proper subset of it should also be a candidate key).
When we cannot remove any attribute from the key and still have it to function as a key.
Exercise 4: How to find candidate keys?
Given a Relation 𝑅 𝐴 𝐵 𝐶 𝐷 𝐸 𝐹 𝐺 𝐻
 Given, AB → C (FD1)
A B C D E F G H
BD → EF (FD2)
AD → G (FD3)
A→H (FD4)
 We can see that there is no incoming edge for A, B, and D. That means there is no other
attribute in the given FDs that can functionally determine A, B, or D.
 We know a candidate key must functionally determine all the attributes of a particular
relation. Therefore, for the above-mentioned example, the candidate key must contain
attributes A, B, and D.
Now at first find the closure of A, B and D.
Exercise 4
 Find closure of ABD⁺
 Start: ABD⁺ = {A, B, D}
 From FD1: AB → C ⇒ add C → {A, B, D, C}
 From FD2: BD → EF ⇒ add E, F → {A, B, D, C, E, F}
 From FD3: AD → G ⇒ add G → {A, B, D, C, E, F, G}
 From FD4 : A → H ⇒ add H → {A, B, D, C, E, F, G, H}
 Result: ABD⁺ = {A, B, C, D, E, F, G, H} = all attributes, so ABD is a Candidate key.
 Check is there any other candidate key? Ans: No.
 Because, in each of the other possible candidate keys, (ABD) must be present.
 ABD itself is a super key. So, we have only one way that we can add attributes with ABD. But if
we do so, it will become a super key.
 So in the given example, there is only one candidate key, i.e., (ABD).
Exercise 7: Find candidate keys
 R(A, B, C, D, E)
FD = { AB → CD (FD1)
D→A (FD2)
BC → DE (FD3) }
 Solution:
 Only B has no incoming edge. So, it will be in the candidate key.
 Now find closure of B
{B}⁺ = {B}. So, B is not a candidate key.
 Now, try with all the possible combinations (pairs):
 {AB}⁺ = {A, B, C, D, E} ⇒ It is a candidate key.
 {CB}⁺ = {C, B, D, E, A} ⇒ It is also a candidate key.
 {DB}⁺ = {D, B, A, C, E} ⇒ It is also a candidate key.
 {EB}⁺ = {E, B} ⇒ It is not a key.
 So, (AB), (BC), (BD) are the candidate keys. BE is not a key.
Exercise 7
 (AB), (BC), (BD) are the candidate keys. These are minimal sets of attributes whose closure
gives all attributes. BE is not a key.
 Try with other attributes:
So, we can add other attributes with this to check whether it can form a candidate key or not.
 {ABE} ⇒ It is already a key.
 {CBE} ⇒ It is also a key.
 {DBE} ⇒ It is also a key.
 So, we cannot add other attributes with BE as it will then become a superkey.
 Final list:
Candidate keys = (AB), (BC), and (BD).
What is decomposition?
 Decomposition is the process of breaking down given relation into two or more relations.
 Relation R is replaced by two or more relations in such a way that:
 Each new relation contains a subset of the attributes of R
 Together, they all include all tuples and attributes of R
 Types of decomposition
 Lossy decomposition
 Lossless decomposition (non-loss decomposition)
Lossy decomposition
 The decomposition of relation R into R1 and R2 is Customer
lossy when the join of R1 and R2 does not yield Ano Balance Bname
the same relation as in R. A01 5000 Rajkot
 This is also referred as lossy-join decomposition. A02 5000 Surat

 The disadvantage of such kind of decomposition Table-1 Table-2

is that some information is lost during retrieval Ano Balance Balance Bname
of original relation. A01 5000 Not Same 5000 Rajkot
 From practical point of view, decomposition A02 5000 5000 Surat
should not be lossy decomposition.
Customer
Customer
Ano Balance Bname
A B C
A01 5000 Rajkot
A01 5000 Surat
Table-1 Table-2
A02 5000 Rajkot
A B B C
A02 5000 Surat
Lossless decomposition
 The decomposition of relation R into R1 and R2 is Customer
lossless when the join of R1 and R2 produces the Ano Balance Bname
same relation as in R. A01 5000 Rajkot
 This is also referred as a non-additive (non-loss) A02 5000 Surat
decomposition. Table-1 Table-2
 All decompositions must be lossless. Ano Balance Ano Bname
A01 5000 Same A01 Rajkot
A02 5000 A02 Surat

Customer
Ano Balance Bname
A01 5000 Rajkot
A02 5000 Surat
Conditions to check decomposition is Lossless
 Rule 1: Union of both the sub relations must contain all the attributes that are present in the
original relation R.
 Thus, R₁ ∪ R₂ = R (with all attributes)
 Rule 2: Intersection of both the sub relations must not be empty.
 Thus, R₁ ∩ R₂ ≠ ∅ (There should be some common attribute for joining)
 Rule 3: Intersection of both the sub relations must be a super key of either R₁ or R₂ or both.
 Thus, R₁ ∩ R₂ = Super key of R₁ or R₂
Exercise-01:
 Consider a relation schema R(A, B, C, D) with the functional dependencies A → B and C → D.
Determine whether the decomposition of R into R₁(A, B) and R₂(C, D) is lossless or lossy.
 Solution: Relation: R(A, B, C, D) and FDs: A → B, C → D
 Decomposition: R₁(A, B) and R₂(C, D)
 As per rule 1: R₁(A,B) ∪ R₂(C,D) = R’(A,B,C,D)
 Table is lossless if R₁ ∪ R₂ = R (with all attributes)
 As per rule 2: But R₁(A,B) ∩ R₂(C,D) = ∅
 Table is lossless only if R₁ ∩ R₂ ≠ ∅, but in our case this is not satisfied
 As per rule 3: Table is lossless if R₁ ∩ R₂ = Super key of R₁ or R₂
 ​But, With an empty intersection, there is no attribute set that is super key
 Since the conditions are not satisfied, the relation undergoes lossy decomposition
Exercise 02
 Determine whether the decomposition of R(A, B, C, D) into R₁(A, B), R₂(B, C), and R₃(B, D)
is lossless or lossy.
 Solution:
 Step 1: Check union condition (Rule 1)
 Since there are 3 tables, We split the relation stepwise:
 For ease, Lets Combine R₁(A, B) and R₂(B, C) → call it R′(A, B, C).
 So now decomposition is R′(A, B, C) and R₃(B, D).
 Union: R′ ∪ R₃ = {A, B, C} ∪ {B, D} = {A, B, C, D} = R (Condition is satisfied)
 Step 2: Check intersection condition (Rule 2)
 Intersection: R′ ∩ R₃ = {A, B, C} ∩ {B, D} = {B} ≠ ∅ (Condition is satisfied)
 Step 3: Superkey condition (Rule 3)
 We need to prove (R′ ∩ R₃) = {B} to be a superkey for either R′ or R₃.
 First lets explore R’: For R′(A, B, C):
 Find the Closure of B:
B⁺ = {B, C, D}
 It is obvious that we can determine only B, C and D from B, But not A. It doesn’t computes all attributes of R’.
Missing A ⇒ So B is not a superkey for R′
 Next lets explore R3: For R₃(B, D):
 Closure of B:
B⁺ = {B, C, D}.
Covers all attributes of R₃ (B, D)
So {B} is a superkey for R₃. (Condition is satisfied)

 Conclusion:
 Since the intersection {B} is a superkey of one relation (R₃), the decomposition is lossless.
Minimal Cover
 Canonical cover of functional dependency is sometimes also referred to as “Minimal Cover”.
 Canonical cover is free from all the extraneous (unnecessary) functional dependencies.
 The closure of canonical cover is same as that of the given set of functional dependencies.
 Canonical cover is not unique and may be more than one for a given set of functional
dependencies.
What is Minimal Cover?
 Process of simplifying the set of functional dependencies
 Steps to Find Minimal Cover
 Rule1: Should have Singleton attributes in Right Hand Side [RHS]
 Rule2: Identify and remove Extraneous Attributes from LHS
 Rule3: Remove redundant dependencies
Rule 1: Singleton attributes in Right Hand Side [RHS]
 Given a FD 1: AB -> CD
 Rule 1: FD should have only Singleton attributes in Right Hand Side [RHS]
 The give FD is not satisfying the rule 1 criteria
 Remedy:
 The above functional dependency should be decomposed to singleton attributes in the RHS as
below.
 FD 1a: AB -> C and
 FD 1b: AB -> D
Rule2: Identify and remove Extraneous Attributes
 Extraneous Attributes - (Extra/Unwanted)
 If an LHS of FD has more than one attribute then check whether there exists an extraneous
attribute. If so, remove it.
 If an attribute doesn’t have any meaning in LHS of the FD then it is called as extraneous, and
remove it.
 Given a set of F = {A -> B, AB -> C, D -> AC, D -> E}
 AB -> C (LHS of FD has 2 attributes) Recall the axiom: Reflexivity
 A+ = {ABC } [Since A->B, AB-> C]  If B is a subset of A
 B+ = {B} [Reflexivity] B is a part of A+ set  then A → B

 B is an extraneous attribute in AB->C

 Remove B from AB->C
 New F={ A->B, A->C, D->AC, D->E}
Rule3: Remove redundant dependencies
Remove redundant dependencies - Exercise
Consider R(A,B,C,D,E) F={ A->B, A->C, D->AC, D->E}
1. Lets Remove A->B and find the attribute closure for A using F={ A -> B, A->C, D->AC, D->E}
 Find A+ closure set
 A+ = {AC} [Since A->C]
 Here if we don’t consider A->B, B cannot be found in A+.
 So A->B cannot be a redundant dependency.

2. Remove A->C and find the attribute closure for A F={ A->B, A -> C, D->AC, D->E}
 A+ = {AB} [Since A->B]
 Here if we don’t consider A->C, C cannot be found in A+.
 So A->C cannot be a redundant dependency.
Rule3: Remove redundant dependencies
F={ A->B, A->C, D->A, D->C, D->E} ---- Applied Singleton (Decomposition) procedure for D → AC
3. Remove D->A and find the attribute closure for D F={ A->B, A->C, D -> A, D->C, D->E}
 D+ = {DCE} [Since D->C, D->E]
 Here if we don’t consider D->A, A cannot be found in D+.
 So D->A cannot be a redundant dependency.
4. Remove D->C and find the attribute closure for D F={ A->B, A->C, D->A, D -> C, D->E}
 D+ = {DAEBC} [Since D->C, D->E, A->B, A->C]
 Here if we don’t consider D->C, Could be found in D+.
 So D->C is the redundant dependency and should be removed.
 5. Remove D->E and find the attribute closure for D F={ A->B, A->C, D->A, D->C, D -> E}
 D+ = {DACB} [Since D->A, A->C, A->B]
 Here if we don’t consider D->E, E cannot be found in D+.
 So D->E cannot be a redundant dependency.
Exercise
 Consider a relation R(A,B,C,D) having some attributes and below are mentioned functional
dependencies.
 FD1: B → A FD2: AD → C FD3: C → ABD
 1. Rule 1: FD should have only Singleton attributes in RHS
 FD3 has non singleton attributes FD3: C → ABD
 Decompose it FD1: B → A FD2: AD → C FD3a: C → A FD3b: C → B FD3c: C → D
 2. Rule 2: Remove extraneous attributes in LHS
 FD2 has non singleton attributes in LHS FD2: AD → C
 Find closure of A+ = A
 Find closure of D+ = D
 In this case, attribute A can only determine A and D can only determine D. Hence no extraneous
attributes are present to remove
 Thus same FD will remain
 3a. Rule 3: Remove redundant functional dependencies
 Analyze the FDs FD1: B → A FD2: AD → C FD3a: C → A FD3b: C → B FD3c: C → D
 Consider FD1, FD3b shows
 FD1: B → A
 FD3a: C → B
C→B
B→A
Transitivity rule C→A
 Apply transitivity rule
 IF C → B & B → A
 THEN C → A

 Therefore FD1: B→ A; FD2: C→ B; FD3: AD→ C; FD4: C→ D

3b. Rule 3: Remove redundant functional dependencies

 Club FD2 and FD 4
 Minimal cover of R(ABCD) = {B→A, C → BD, AD → C}
Exercise
Consider R(A,B,C,D,E)
F = {A -> B, AB -> C, D -> AC, D -> E}. Find the minimal cover.
 Minimal Cover is attained by applying
 Rule 1) Singleton Attributes in RHS F = {A -> B, AB -> C, D -> A, D->C, D -> E}
 Minimal Cover will be after removing
 Rule 2) Extraneous Attributes - - F = {A -> B, A -> C, D -> A, D->C, D -> E}
 Rule 3) Redundant Dependencies -- F = {A -> B, A -> C, D -> A, D -> E}
What is an anomaly in database design?
 Anomalies are problems that can occur in poorly planned, un-normalized database where all
the data are stored in one table.
 There are three types of anomalies that can arise in the database because of redundancy are
 Insert anomaly
 Delete anomaly
 Update / Modification anomaly
Insert anomaly
 Consider a relation Emp_Dept(EID, Ename, City, DID, Dname, Manager) _____
EID as a primary key

Emp_Dept
EID Ename City DID Dname Manager
An insert anomaly occurs when certain attributes
1 Raj Rajkot 1 CE Shah
cannot be inserted into the database without the
2 Meet Surat 1 CE Shah presence of another attribute.
NULL NULL NULL 2 IT NULL
Want to insert new department detail (IT)
 Suppose a new department (IT) has been started by the organization but initially there is no
employee appointed for that department.
 We want to insert that department detail in Emp_Dept table.
 But the tuple for this department cannot be inserted into this table as the EID will have NULL
value, which is not allowed because EID is primary key.
 This kind of problem in the relation where some tuple cannot be inserted is known as insert
anomaly.
How to deal with insert anomaly
Emp_Dept Emp Dept
EID Ename City DID Dname Manager EID Ename City DID DID Dname Manager
1 Raj Rajkot 1 CE Shah 1 Raj Rajkot 1 1 CE Shah
2 Meet Surat 1 CE Shah 2 Meet Surat 1 2 IT Dave
3 Jay Baroda 2 IT Dave 3 Jay Baroda 2 3 EC NULL
NULL NULL NULL 3 EC NULL
Want to insert new New department EC
department detail can be inserted
(IT) without NULL in PK
EID

Such type of anomalies in the database design can be solved by using normalization.
Delete anomaly
 Consider a relation Emp_Dept(EID, Ename, City, DID, Dname, Manager) EID as a primary key
Emp_Dept
EID Ename City DID Dname Manager A delete anomaly exists when certain attributes are
1 Raj Rajkot 1 CE Shah lost because of the deletion of another attribute.
2 Meet Surat 1 CE Shah
Want to delete (Jay)
3 Jay Baroda 2 IT Dave employee's detail

 Now consider there is only one employee in some department (IT) and that employee leaves
the organization.
 So we need to delete tuple of that employee (Jay).
 Consequence: But in addition to that information about the department also deleted.
 This kind of problem in the relation where deletion of some tuples can lead to loss of some
other data not intended to be removed is known as delete anomaly.
How to deal with delete anomaly
Emp_Dept Emp Dept
EID Ename City DID Dname Manager EID Ename City DID DID Dname Manager
1 Raj Rajkot 1 CE Shah 1 Raj Rajkot 1 1 CE Shah
2 Meet Surat 1 C.E Shah 2 Meet Surat 1 2 IT Dave
3 Jay Baroda 2 IT Dave 3 Jay Baroda 2

Want to delete Jay employee's detail without Jay employee's detail

deleting Dname of IT deleted without problem

Such type of anomalies in the database design can be solved by using normalization.
Update anomaly
 Consider a relation Emp_Dept(EID, Ename, City, Dname, Manager) EID as a primary key
Emp_Dept
EID Ename City Dname Manager An update anomaly exists when one or more
1 Raj Rajkot CE Shah records (instance) of duplicated data is updated,
2 Meet Surat CE Shah but not all.
3 Jay Baroda Computer Shaah
Want to update manager
4 Hari Rajkot IT Dave of CE department

 Suppose the manager of a (CE) department has changed, this requires that the Manager in all
the tuples corresponding to that department must be changed to reflect the new status.
 If we fail to update all the tuples of given department, then two different records of employee
working in the same department might show different Manager lead to inconsistency in the
database.
How to deal with update anomaly
Emp_Dept Emp Dept
EID Ename City DID Dname Manager EID Ename City DID DID Dname Manager
1 Raj Rajkot 1 CE Shah 1 Raj Rajkot 1 1 CE Shah
2 Meet Surat 1 C.E Shah 2 Meet Surat 1 2 IT Dave
3 Jay Baroda 2 IT Dave 3 Jay Baroda 2 3 EC NULL
NULL NULL NULL 3 EC NULL

Such type of anomalies in the database design can be solved by using normalization.