UNIT –3

DBMS

Introduction to Database Normalization

Normalization is an important process in database design that helps improve

the database's efficiency, consistency, and accuracy. It makes it easier to
manage and maintain the data and ensures that the database is adaptable to
changing business needs.

• Database normalization is the process of organizing the attributes of

the database to reduce or eliminate data redundancy (having the same
data but at different places).
• Data redundancy unnecessarily increases the size of the database as
the same data is repeated in many places. Inconsistency problems also
arise during insert, delete, and update operations.
• In the relational model, there exist standard methods to quantify how
efficient a databases is. These methods are called normal forms and
there are algorithms to covert a given database into normal forms.
• Normalization generally involves splitting a table into multiple ones
which must be linked each time a query is made requiring data from
the split tables.
Why do we need Normalization?
The primary objective for normalizing the relations is to eliminate the below
anomalies. Failure to reduce anomalies results in data redundancy, which may
threaten data integrity and cause additional issues as the database increases.
Normalization consists of a set of procedures that assist you in developing an
effective database structure.
Types of anomalies
• Insertion Anomaly: This occurs when you cannot add a new record to a table
because you are missing other related, but required, data.
o Example: You can't add a new customer who has not yet made a
purchase, because the database design requires a customer to have an
order ID to be entered.
• Deletion Anomaly: This happens when deleting a record causes the
unintentional loss of other data that is not related to the deleted record.
o Example: If a table stores both customer and order information, deleting
the last order from a customer might also delete the customer's entire
record, even if you wanted to keep their information.
• Update Anomaly: This arises when a single piece of information that is repeated
in multiple rows is updated in one location but not all, leading to inconsistent
data.
o Example: If a customer's address is stored in every order they make, and
they move, you would have to update the address in every single order
record. If you miss one, the database will have different addresses for the
same customer.
Normal Forms in DBMS
Normal Description of Normal Forms
Forms

First
Normal A relation is in first normal form if every attribute in that relation is
Form single-valued attribute.
(1NF)

Second
A relation that is in First Normal Form and every non-primary-key
Normal
attribute is fully functionally dependent on the primary key, then the
Form
relation is in Second Normal Form (2NF).
(2NF)

Third A relation is in the third normal form, if there is no transitive

Normal dependency for non-prime attributes as well as it is in the second
Form normal form. A relation is in 3NF if at least one of the following
(3NF) conditions holds in every non-trivial function dependency X –> Y.

• X is a super key.
• Y is a prime attribute (each element of Y is part of some
candidate key).

Boyce-
For BCNF the relation should satisfy the below conditions
Codd
Normal • The relation should be in the 3rd Normal Form.
Form • X should be a super-key for every functional dependency (FD)
(BCNF) X−>Y in a given relation.

Fourth A relation R is in 4NF if and only if the following conditions are satisfied:
Normal • It should be in the Boyce-Codd Normal Form (BCNF).
Form • The table should not have any Multi-valued Dependency.
(4NF)

Fifth
A relation R is in 5NF if and only if it satisfies the following conditions:
Normal
Form • R should be already in 4NF.
(5NF) • It cannot be further non loss decomposed (join dependency).

Advantages of Normalization
• Normalization eliminates data redundancy and ensures that each piece
of data is stored in only one place, reducing the risk of data
inconsistency and making it easier to maintain data accuracy.
• By breaking down data into smaller, more specific tables, normalization
helps ensure that each table stores only relevant data, which improves
the overall data integrity of the database.
• Normalization simplifies the process of updating data, as it only needs
to be changed in one place rather than in multiple places throughout
the database.
• Normalization enables users to query the database using a variety of
different criteria, as the data is organized into smaller, more specific
tables that can be joined together as needed.
• Normalization can help ensure that data is consistent across different
applications that use the same database, making it easier to integrate
different applications and ensuring that all users have access to
accurate and consistent data.
Disadvantages of Normalization
• Normalization can result in increased performance overhead due to the
need for additional join operations and the potential for slower query
execution times.
• Normalization can result in the loss of data context, as data may be
split across multiple tables and require additional joins to retrieve.
• Proper implementation of normalization requires expert knowledge of
database design and the normalization process.
• Normalization can increase the complexity of a database design,
especially if the data model is not well understood or if the
normalization process is not carried out correctly.

Types of Functional dependencies in DBMS

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

roll_no name dept_name 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.
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.
Read more about What is Functional Dependency in DBMS ?

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

bike_model manuf_year 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.

In this case these two columns are said to be multivalued dependent on

bike_model. These dependencies can be represented like this:

Read more about Multivalued Dependency in DBMS.

5. Transitive Functional Dependency

In transitive functional dependency, dependent is indirectly dependent on

determinant. i.e. If a → b & b → c, then according to axiom of transitivity, a →
c. This is a transitive functional dependency.

Example:

enrol_no name dept building_no

42 abc CO 4

43 pqr EC 2

44 xyz IT 1

45 abc EC 2

Here, enrol_no → dept and dept → building_no. Hence, according to the axiom
of transitivity, enrol_no → building_no is a valid functional dependency. This is
an indirect functional dependency, hence called Transitive functional
dependency.

6. Fully Functional Dependency

In full functional dependency an attribute or a set of attributes uniquely

determines another attribute or set of attributes. If a relation R has attributes
X, Y, Z with the dependencies X->Y and X->Z which states that those
dependencies are fully functional.

Read more about Fully Functional Dependency.

7. Partial Functional Dependency

In partial functional dependency a non key attribute depends on a part of the

composite key, rather than the whole key. If a relation R has attributes X, Y, Z
where X and Y are the composite key and Z is non key attribute. Then X->Z is
a partial functional dependency in RBDMS.

Each candidate key is a minimal set of attributes that uniquely identify a row in a table. The
solving strategy is:

• Find all essential attributes (not present as a right-hand side in any functional
dependency).
• These attributes must be part of every candidate key.
• Combine essential attributes and check closures to cover all other attributes. If not,
try combinations with non-essentials.

Example 1:
Relation R(A, B, C) with FDs: A → B, B → C

• Essential attribute is A (not on RHS).

 • A+ = {A, B, C} (using both FDs).
• Candidate key = {A}.

Example 2:
R(A, B, C, D) with FDs: AB → C, C → D

• Essential attributes: A, B (A and B not on RHS).

• (AB)+ = {A, B, C, D}
• Candidate key = {A, B}.

Example 3:
R(A, B, C, D, E, F) with FDs: C → F, E → A, EC → D, A → B

• Essential attributes: C, E
• (CE)+ = {C, E, F}, E → A ⇒ {A}, EC → D ⇒ {D}, A → B ⇒ {B}
• (CE)+ = {A, B, C, D, E, F}
• Candidate key = {C, E}.

Example 4:
R(A, B, C, D, E) with AB → C, C → D, B → E

• Essential attributes: A, B (neither on RHS)

• (AB)+ = {A, B, C, D, E}
• Candidate key = {A, B}.

Example 5:
R(E, F, G, H, I, J, K, L, M, N) with FDs: EF → G, F → I J, EH → K L, K → M, L → N

• Essential attributes: E, F, H
• (EFH)+ = {E, F, H, G, I, J, K, L, M, N}
• Candidate key = {E, F, H}.

Example 6:
R(A, B, C) with FDs: A → B, B → C, C → A

• All attributes appear on both sides; try any single attribute.

• (A)+ = {A, B, C}
• Candidate keys = {A}, {B}, {C}.

Example 7:
R(A, B, C, D) with FDs: AB → C, C → D, D → B
 • Essential: A (only one never RHS)
• (A, C)+ = {A, C, D, B} (so AC is candidate)
• Check others: (A, D)+, (A, B)+, etc..

Example 8:
R(A, B, C, D) with FDs: A → B, B → C, C → D

• Essential: A
• (A)+ = {A, B, C, D}
• Candidate key = {A}.

Question 1
Given:
R(A, B, C)
FDs: A → B, B → C

Solution:

• 1NF: Assume all are atomic.

• 2NF: {A} is a key (A+ = ABC). No partial dependency.
• 3NF: Check transitive dependency: A → B, B → C, so A → C transitively (violates
3NF).
o Decompose into R1(A, B) [A → B] and R2(B, C) [B → C].
• BCNF: In R2, B → C (B is not a superkey for R2), so B is promoted as key.
o R2 remains as is, both in BCNF.

Question 2
Given:
R(A, B, C, D)
FDs: AB → C, C → D

Solution:

• 1NF: Assume atomic.

• 2NF: Candidate key is AB (AB+ = ABCD), no partial dependency.
 • 3NF: C → D means C (not a key) determines D; transitive.
o Decompose into R1(C, D) and R2(A, B, C).
• BCNF: In R1, C is a key; in R2, AB is a key. Both in BCNF.

Question 3
Given:
R(A, B, C, D)
FDs: AB → C, C → D, D → B

Solution:

• 1NF: Assume atomic.

• 2NF: Candidate key AB.
• 3NF: C → D and D → B cause transitive dependencies (B, D depend on C).
o Decompose:
▪ R1(C, D, B), FDs: C → D, D → B
▪ R2(A, C)
• BCNF: In R1, D → B: D is not a superkey for R1.
o Split R1 into R1a(D, B) and R1b(C, D). Final BCNF relations: (A, C), (C, D), (D,
B).

Question 4
Given:
R(A, B, C, D)
FDs: A → B C, C → D

Solution:

• 1NF: Atomic.
• 2NF: A is the only key. No partial dependency.
• 3NF: C → D, C is not a key, D is non-prime → violates 3NF.
o Decompose: (C, D), (A, B, C)
• BCNF: (C, D): C is key; (A, B, C): A is key. Both in BCNF.
Question 5
Given:
R(A, B, C, D, E)
FDs: A → B, B → C, C → D, D → E

Solution:

• 1NF: Assume atomic.

• 2NF/3NF: A is key, but B → C, C → D, D → E create a chain (all non-prime attributes
depend on other non-prime attributes).
• Decompose stepwise:
o R1(B, C) [B → C]
o R2(C, D) [C → D]
o R3(D, E) [D → E]
o R4(A, B)
• All are in BCNF (each determinant is a key for its relation).

Question 6
Given:
R(A, B, C)
FDs: A → B, B → A, B → C

Solution:

• 1NF: Atomic.
• 2NF/3NF: (A, B)+ = ABC, so {A}, {B} are both candidate keys.
• B → C is fine since B is a key. Table is in 3NF and BCNF.

Question 7
Given:
R(A, B, C, D)
FDs: A → B, B → C, C → D, D → A

Solution:

• 1NF: Atomic.
 • 2NF/3NF: Any single attribute is a candidate key via chain dependencies.
• All FDs have a key on LHS — already in BCNF.

Question 8
Given:
R(A, B, C, D, E)
FDs: AB → C, C → DE, D → B

Solution:

• 1NF: Atomic.
• 2NF: Key: AB. C → DE and D → B cause transitive dependencies.
• 3NF/BCNF Decompose:
o R1(C, D, E)
o R2(D, B)
o R3(A, B, C)
• In R2, D is key (D → B).
• In R1, C is key (C → D, C → E).
• In R3, AB is key.

Example 1
Relation: R(A, B, C, D)
FDs: A → B, B → C

Decomposition:

• R1(A, B)
• R2(B, C)
• R3(A, D)

Lossless Join:

• The common attribute B is a key in R2; using the "key-attribute intersection" rule,
decomposition is lossless.
Dependency Preserving:

• A → B is in R1.
• B → C is in R2.
• However, transitive A → C is not covered directly—it needs to be checked via joins.
• Result: Dependency preserving for FDs given (A → B, B → C), but not for A → C (if it
is also required).

Example 2
Relation: R(A, B, C, D, E)
FDs: AB → C, C → D, D → E

Decomposition:

• R1(A, B, C)
• R2(C, D)
• R3(D, E)

Lossless Join:

• Decompose on C and D, which act as keys in their respective subrelations. The

decompositions are lossless at each step as intersection is always a key.

Dependency Preserving:

• AB → C is in R1.
• C → D is in R2.
• D → E is in R3.
• All FDs are present directly in at least one relation.
• Result: Dependency preserving.

Example 3
Relation: R(A, B, C, D)
FDs: AB → C, C → D, D → A

Decomposition:
 • R1(A, B, C)
• R2(C, D)

Lossless Join:

• C is a key for R2, so the decomposition is lossless.

Dependency Preserving:

• AB → C is preserved in R1.
• C → D is preserved in R2.
• D → A is lost: D and A never appear together in any decomposed relation; it cannot
be checked by inspecting a single relation.
• Result: Not dependency preserving.