Normalization

 The normalization process was first proposed by Codd in 1972 to improve the design of
relational databases.
 Normalization tests a relation schema step by step to check whether it satisfies different
normal forms, following a top-down approach.
 Codd initially introduced First, Second, and Third Normal Forms, and later Boyce–Codd
Normal Form was proposed as a stronger version of 3NF.
 Fourth Normal Form and Fifth Normal Form were later developed based on multivalued
dependencies and join dependencies.

 Normalization is a systematic process of organizing data in a database to reduce

redundancy and dependency. It helps in storing data efficiently by dividing large tables
into smaller, well-structured tables. The main purpose of normalization is to minimize
data redundancy and reduce insertion, update, and deletion anomalies in a database.

Anomalies in DBMS
1. Anomalies are problems that arise when a database table is poorly designed or not
normalized.
2. These anomalies lead to inconsistency and loss of data accuracy.
3. There are three types of anomalies: insertion anomaly, update anomaly, and deletion
anomaly.

Insertion Anomaly

 Insertion anomaly occurs when certain data cannot be inserted into a table without the
presence of some other data.
 This happens when all attributes are forced to be filled even if some information is not
available.
 For example, a new employee cannot be added if department details are not yet assigned.

Update Anomaly

 Update anomaly occurs when the same data is stored in multiple rows.
 If a value is updated in one row and not in others, it results in inconsistent data.
 This increases the chances of errors in the database.

Deletion Anomaly
  Deletion anomaly occurs when deleting a record also removes some important
information unintentionally.
 For example, deleting a department may also delete the details of employees working in
that department.

Functional Dependency
1. Functional dependency describes the relationship between two attributes in a relation.
2. It indicates that the value of one attribute uniquely determines the value of another
attribute.
3. It is represented as X → Y, which means X determines Y.
4. Functional dependency is useful in identifying keys and in normalization.

Types of Functional Dependency

Trivial Functional Dependency

 A functional dependency is called trivial when the right-hand side attribute is part of the
left-hand side.
 Such dependencies do not provide new information.
 Example: {EMP_ID, EMP_NAME} → EMP_ID.

Non-Trivial Functional Dependency

 A functional dependency is non-trivial when the right-hand side attribute is not a subset
of the left-hand side.
 These dependencies are important for database design.
 Example: DEPT_ID → DEPT_NAME.

Completely Non-Trivial Functional Dependency

 A functional dependency is completely non-trivial when there is no common attribute

between left and right sides.
 Example: ID → NAME.

Keys in DBMS
A key is an attribute or a combination of attributes that uniquely identifies a record in a table.
 • Prime attribute − An attribute, which is a part of the candidate-key, is known as a prime
attribute.
• Non-prime attribute − An attribute, which is not a part of the prime-key, is said to be a
non-prime attribute.

Super Key

 A super key is any set of attributes that can uniquely identify a tuple in a table.
 It may contain extra attributes.
 For Example, STUD_NO, (STUD_NO, STUD_NAME) etc.
 Adding zero or more attributes to candidate key generates super key.
 A candidate key is a super key but vice versa is not true.

Candidate Key

 A candidate key is a minimal super key with no redundant attributes.

 A table can have more than one candidate key.

Primary Key

 A relation can have more than one candidate key, but only one of them is selected as the
primary key.
 A primary key is a column or a group of columns that uniquely identifies each row in a
table.
 The primary key ensures that every record in the table is unique.
 Duplicate values are not allowed in a primary key column.
 A table can have only one primary key.

Alternate Key

 Alternate keys are candidate keys that are not chosen as the primary key.

Foreign Key

 A foreign key is a column that creates a relationship between two tables.

 It is used to link records in one table with records in another table.
 The main purpose of a foreign key is to maintain data integrity in the database.
 A foreign key references the primary key of another table.

Composite Key

 A composite key consists of two or more attributes used together to identify records
uniquely.

First Normal Form (1NF)

  A table is said to be in first normal form if all its attributes contain atomic values.
 Each attribute must store only a single value and not a set of values.
 Multivalued and composite attributes are not allowed in 1NF.

Example: Relation EMPLOYEE is not in 1NF because of multi-valued attribute

EMP_PHONE.

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385, UP
9064738238

20 Harry 8574783832 Bihar

12 Sam 7390372389, Punjab

8589830302

• The decomposition of the EMPLOYEE table into 1NF has been shown below:

EMP_I EMP_NAM EMP_PHONE EMP_STAT

D E E

14 John 7272826385 UP

14 John 9064738238 UP

20 Harry 8574783832 Bihar

12 Sam 7390372389 Punjab

12 Sam 8589830302 Punjab

Second Normal Form (2NF)

 A table is in second normal form if it is already in first normal form.
 In 2NF, all non-key attributes must be fully functionally dependent on the entire primary
key.
 Partial dependency is removed in second normal form.
 A relation schema R is in 2NF if every nonprime attribute A in R is fully functionally
dependent on the primary key of R.
  Example

 The functional dependency {SSN, PNUMBER} → HOURS is a full dependency

because neither SSN alone nor PNUMBER alone can determine HOURS.
 The functional dependency {SSN, PNUMBER} → ENAME is a partial dependency
because SSN alone determines ENAME.
 Due to this partial dependency, the non-prime attribute ENAME violates the rules of
Second Normal Form (2NF).
 Similarly, the non-prime attributes PNAME and PLOCATION also violate 2NF
because they are partially dependent on the primary key {SSN, PNUMBER}.
 To remove these violations, the relation EMP_PROJ is decomposed into three smaller
relations EP1, EP2, and EP3, each of which satisfies Second Normal Form.

Third Normal Form (3NF)

 A table is in third normal form if it is in second normal form.
 In 3NF, no non-prime attribute should be transitively dependent on the primary key.
 A functional dependency X → Z in a relation schema R is a transitive dependency if
there is a set of attributes Y that is neither a candidate key nor a subset of any key of R,
and both X → Y and Y → Z hold.
 This normal form helps in further reducing redundancy and improving data integrity.

• A relation is in third normal form if it holds atleast one of the following conditions for
every non-trivial function dependency X → Y.
• 1. X is a super key.
• 2. Y is a prime attribute, i.e., each element of Y is part of some candidate key.
The dependency SSN → DMGRSSN is a transitive dependency through DNUMBER because SSN
→ DNUMBER and DNUMBER → DMGRSSN, where DNUMBER is not a key of the EMP_DEPT
relation.
This dependency is undesirable because DMGRSSN depends on a non-key attribute, which leads to
redundancy in the EMP_DEPT table.

To remove these violations, the relation EMP_DEPT is decomposed into two smaller relations
ED1 and ED2, each of which satisfies Third Normal Form.

Boyce–Codd Normal Form (BCNF)

1. Boyce–Codd Normal Form is a stricter version of third normal form.
2. Every relation in BCNF is also in 3NF; however, a relation in 3NF is not necessarily in
BCNF.
3. A relation schema R is in BCNF if whenever a nontrivial functional dependency X → A
holds in R, then X is a superkey of R.
4. BCNF removes certain anomalies that are not handled by 3NF.
  The functional dependency FD5 violates BCNF in the LOTSIA relation because AREA
is not a super key.

 Although FD5 satisfies Third Normal Form (3NF) since COUNTY_NAME is a prime
attribute, this condition is not allowed in BCNF.

 To satisfy BCNF, the LOTSIA relation is decomposed into two relations called
LOTSIAX and LOTSIAY.

 However, this decomposition causes the loss of functional dependency FD2 because the
related attributes are separated into different tables.

Multivalued Dependency
 Multivalued dependency occurs when two or more attributes are independent but depend
on the same attribute.
 It leads to unnecessary repetition of data in a table.
 Multivalued dependency violates fourth normal form.
 Multivalued dependencies are a consequence of first normal form (lNF), which disallows
an attribute in a tuple to have a set of values.

Example: Suppose there is a bike manufacturer company which produces two colors(white
and black) of each model every year.

BIKE_MODE MANUF_YEA COLO

L R R

M2011 2008 White

M2011 2008 Black

 M3001 2013 White

M3001 2013 Black

M4006 2017 White

M4006 2017 Black

 Here columns COLOR and MANUF_YEAR are dependent on BIKE_MODEL and

independent of each other.
 In this case, these two columns can be called as multivalued dependent on
BIKE_MODEL. The representation of these dependencies is shown below:

BIKE_MODEL → → MANUF_YEAR

BIKE_MODEL → → COLOR

 This can be read as "BIKE_MODEL multidetermined MANUF_YEAR" and

"BIKE_MODEL multidetermined COLOR".

Fourth Normal Form (4NF)

1. A relation will be in 4NF if it is in Boyce Codd normal form and has no multi-valued
dependency.
2. For a dependency A → B, if for a single value of A, multiple values of B exists, then the
relation will be a multi-valued dependency.

3. Example
STU_ID COURSE HOBBY

21 Computer Dancing

21 Math Singing

34 Chemistry Dancing

74 Biology Cricket

59 Physics Hockey
 • The given STUDENT table is in 3NF, but the COURSE and HOBBY are two
independent entity. Hence, there is no relationship between COURSE and HOBBY.
• In the STUDENT relation, a student with STU_ID, 21 contains two
courses, Computer and Math and two hobbies, Dancing and Singing.
• So there is a Multi-valued dependency on STU_ID, which leads to unnecessary repetition
of data.
• So to make the above table into 4NF, we can decompose it into two tables:

STUDENT_COURSE

• STU_ID • COURSE

• 21 • Computer

• 21 • Math

• 34 • Chemistry

• 74 • Biology

• 59 • Physics

STUDENT_HOBBY

STU_ID HOBBY

21 Dancing

21 Singing

34 Dancing

74 Cricket

59 Hockey

Join Dependency
1. Join dependency occurs when a table can be recreated by joining two or more smaller
tables.
2. It is a generalization of multivalued dependency.
Fifth Normal Form (5NF / PJNF)
1. Fifth normal form is also called project-join normal form.

A relation R is in 5NF if and only if it satisfies following conditions:

• R should be in 4NF (no multi-valued dependency exists).
• It cannot undergo lossless decomposition (join dependency)

Example: Consider the relation R below having the schema R(supplier, product, consumer). The
primary key is a combination of all three attributes of the relation.

TABLE1
SUPPLIER PRODUCT CONSUMER

S1 P1 C1
S1 P2 C1
S2 P1 C1
S3 P3 C3

 The table is not in Fifth Normal Form (5NF) because it has a join dependency,
meaning it can be decomposed into smaller tables whose natural join recreates the
original table.

 Since this join dependency exists, the table can be further decomposed without losing
information, which violates the condition of 5NF.

 To achieve 5NF, the table is decomposed into Table 2, Table 3, and Table 4.

 The decomposed tables satisfy 5NF because they have no multivalued dependency and cannot
be decomposed further.

 Fifth Normal Form is rarely used in practice and is mainly a theoretical concept in database
design.

Table 2
SUPPLIER PRODUCT

S1 P1
S1 P2
 S2 P1
S3 P3

Table 3
CONSUMER PRODUCT

C1 P1
C1 P2
C3 P3

Table 4
SUPPLIER CONSUMER

S1 C1
S2 C1
S3 C3

Types of Normal Forms

Normal Form Description

1NF A relation is in 1NF if it contains an atomic value.

2NF A relation will be in 2NF if it is in 1NF and all non-key attributes are fully
functional dependent on the primary key.
3NF A relation will be in 3NF if it is in 2NF and no transition dependency exists.

Boyce Codd BCNF is stricter than 3NF. A relation will be in BCNF if it is in 3NF and
Normal Form for every functional dependency X->Y, X should be the super key of the
(BCNF) table
4NF A relation will be in 4NF if it is in Boyce Codd normal form and has no
multi-valued dependency.
5NF A relation is in 5NF if it is in 4NF and not contains any join dependency and
joining should be lossless.