Database Management Systems

Normalization

Dr. Navanath Saharia

Indian Institute of Information Technology Senapati, Manipur
[email protected]

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 1 / 18
 Keys

What is key

b A key is a column or set of columns that uniquely identifies each row in a table.
- Super key: a super key is a combination of one or more columns that uniquely identifies
each row in a table. It may contain additional columns that are not necessary to
uniquely identify each row. Example
b Terms used interchangibly
Table ⇔ Relation
Column ⇔ Attribute
Row ⇔ Tuple

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 2 / 18
 Keys Type of Keys

Super Key
b A super key is a combination of one or more attribute that uniquely identifies each
tuple in a relation. It may contain additional columns that are not necessary to
uniquely identify each row.

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 3 / 18
 Keys Type of Keys

Super Key
b A super key is a combination of one or more attribute that uniquely identifies each
tuple in a relation. It may contain additional columns that are not necessary to
uniquely identify each row.
b student (rollNo, stdName, fathersName, mothersName, dob, gender,
address, emailID, enrollProgram, doa, aadharNo)
b List of super keys
- rollNo
- rollNo, emailID
- rollNo, stdName
- stdName, mothersName
- stdName, fathersName, mothersName
- stdName, mothersName, dob

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 3 / 18
 Keys Type of Keys

Super Key
b A super key is a combination of one or more attribute that uniquely identifies each
tuple in a relation. It may contain additional columns that are not necessary to
uniquely identify each row.
b student (rollNo, stdName, fathersName, mothersName, dob, gender,
address, emailID, enrollProgram, doa, aadharNo)
b List of super keys
- rollNo
- rollNo, emailID
- rollNo, stdName
- stdName, mothersName
- stdName, fathersName, mothersName
- stdName, mothersName, dob
b Sufficient but not necessary: All the attributes in a super key may not be necessary
to uniquely identify a tuple in a relation. Example: (stdName, fathersName,
mothersName) .
.
.
.
.
. . . . .
. . . .
. . . .
. . . .
. . . .
. . . . .
.
.
.
.
.
.
.
.
.

N. Saharia Database Management Systems 3 / 18

 Keys Type of Keys

Candidate Key

b A set of minimal attribute(s) that can identify each tuple uniquely in a given relation
is called as a candidate key.

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 4 / 18
 Keys Type of Keys

Candidate Key

b A set of minimal attribute(s) that can identify each tuple uniquely in a given relation
is called as a candidate key.
b student (rollNo, stdName, fathersName, mothersName, dob, gender,
address, emailID, enrollProgram, doa, aadharNo)
b List of Candidate keys
- rollNo
- emailID
- aadharNo
- stdName, mothersName

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 4 / 18
 Keys Type of Keys

Candidate Key

b A set of minimal attribute(s) that can identify each tuple uniquely in a given relation
is called as a candidate key.
b student (rollNo, stdName, fathersName, mothersName, dob, gender,
address, emailID, enrollProgram, doa, aadharNo)
b List of Candidate keys
- rollNo
- emailID
- aadharNo
- stdName, mothersName

b Sufficient and necessary: Attribute(s) in a Candidate key is/are necessary to uniquely

identify a tuple in a relation. Removing any attribute from the Candidate key fails to
identify the each tuple uniquely.
b A minimal super key is called as a candidate key . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 4 / 18
 Keys Type of Keys

Steps to count the super keys in a relational database

1 Determine the candidate keys: A candidate key is a minimal set of attributes that can
uniquely identify a record in the table. Identify all the candidate keys for the table.
2 Determine the non-candidate keys: A non-candidate key is any set of attributes that
is not a candidate key but can still uniquely identify a record in the table. Identify all
the non-candidate keys for the table.
3 Combine candidate and non-candidate keys: Combine each candidate key with each
non-candidate key to create a set of possible super keys.
4 Eliminate duplicates: Remove any duplicate sets of attributes from the list of possible
super keys.
5 Count the number of super keys: Count the number of remaining sets of attributes in
the list. This is the number of super keys for the table.
. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 5 / 18
 Keys Type of Keys

Example: Super key count

Consider the following table called Employees

empID firstName lastName deptID salary
100 John Lee IT 50000
101 Robert Doe HR 70000
102 Jim Johnson IT 55000
103 Sarah Lee Marketing 65000
104 Robert Brown IT 70000

1 Determine the candidate keys: The empID is a candidate key because it is a unique identifier
for each employee.
2 Determine the non-candidate keys: No non-candidate keys.
3 Combine candidate and non-candidate keys: Only one candidate key
4 Eliminate duplicates: There are no duplicates to eliminate.
5 Count the number of super keys: There is only one super key for this. table, which is the empID
. . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 6 / 18
 Keys Type of Keys

Compute Candidate Key

Let R = (A, B, C, D, E, F) be a relation with

the given dependencies. Which of the following is C → F
a key for R? Also, determine the total number of E → A
candidate keys and super keys. CE → D
□ CD □ CE □ AE □ AC A → B

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 7 / 18
 Keys Type of Keys

Compute Candidate Key

Let R = (A, B, C, D, E, F) be a relation with

the given dependencies. Which of the following is C → F
a key for R? Also, determine the total number of E → A
candidate keys and super keys. CE → D
□ CD □ CE □ AE □ AC A → B

1 Compute prime attributes. (Check for attributes, which are not in the dependent side)
A B C D E F
× (E → A)

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 7 / 18
 Keys Type of Keys

Compute Candidate Key

Let R = (A, B, C, D, E, F) be a relation with

the given dependencies. Which of the following is C → F
a key for R? Also, determine the total number of E → A
candidate keys and super keys. CE → D
□ CD □ CE □ AE □ AC A → B

1 Compute prime attributes. (Check for attributes, which are not in the dependent side)
A B C D E F
× (E → A) × (A → B)

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 7 / 18
 Keys Type of Keys

Compute Candidate Key

Let R = (A, B, C, D, E, F) be a relation with

the given dependencies. Which of the following is C → F
a key for R? Also, determine the total number of E → A
candidate keys and super keys. CE → D
□ CD □ CE □ AE □ AC A → B

1 Compute prime attributes. (Check for attributes, which are not in the dependent side)
A B C D E F
× (E → A) × (A → B) ✓

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 7 / 18
 Keys Type of Keys

Compute Candidate Key

Let R = (A, B, C, D, E, F) be a relation with

the given dependencies. Which of the following is C → F
a key for R? Also, determine the total number of E → A
candidate keys and super keys. CE → D
□ CD □ CE □ AE □ AC A → B

1 Compute prime attributes. (Check for attributes, which are not in the dependent side)
A B C D E F
× (E → A) × (A → B) ✓ × (CE → D)

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 7 / 18
 Keys Type of Keys

Compute Candidate Key

Let R = (A, B, C, D, E, F) be a relation with

the given dependencies. Which of the following is C → F
a key for R? Also, determine the total number of E → A
candidate keys and super keys. CE → D
□ CD □ CE □ AE □ AC A → B

1 Compute prime attributes. (Check for attributes, which are not in the dependent side)
A B C D E F
× (E → A) × (A → B) ✓ × (CE → D) ✓

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 7 / 18
 Keys Type of Keys

Compute Candidate Key

Let R = (A, B, C, D, E, F) be a relation with

the given dependencies. Which of the following is C → F
a key for R? Also, determine the total number of E → A
candidate keys and super keys. CE → D
□ CD □ CE □ AE □ AC A → B

1 Compute prime attributes. (Check for attributes, which are not in the dependent side)
A B C D E F
× (E → A) × (A → B) ✓ × (CE → D) ✓ × (C → F)

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 7 / 18
 Keys Type of Keys

Compute Candidate Key

Let R = (A, B, C, D, E, F) be a relation with

the given dependencies. Which of the following is C → F
a key for R? Also, determine the total number of E → A
candidate keys and super keys. CE → D
□ CD □ CE □ AE □ AC A → B

1 Compute prime attributes. (Check for attributes, which are not in the dependent side)
A B C D E F
× (E → A) × (A → B) ✓ × (CE → D) ✓ × (C → F)
Thus, prime attribute of R is C and E

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 7 / 18
 Keys Type of Keys

Compute Candidate Key

Let R = (A, B, C, D, E, F) be a relation with

the given dependencies. Which of the following is C → F
a key for R? Also, determine the total number of E → A
candidate keys and super keys. CE → D
□ CD □ CE □ AE □ AC A → B

1 Compute prime attributes. (Check for attributes, which are not in the dependent side)
A B C D E F
× (E → A) × (A → B) ✓ × (CE → D) ✓ × (C → F)
Thus, prime attribute of R is C and E
2 Find the closure of C and E, i.e. {CE}+

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 7 / 18
 Keys Type of Keys

Compute closure

C → F
E → A
CE → D
A → B
Find the closure of C and E, i.e. {CE}+ from the above dependencies.
{CE}+ = { C , E }
= { C , E , F} Using C → F
= { A , C , E , F} Using E → A
= { A , C , D , E , F} Using CE → D
= { A , B , C , D , E , F } Using A → B
Thus, CE can determine all attributes of R. So, only possible candidate key is CE
. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 8 / 18
 Keys Type of Keys

How to count minimum number of super keys?

Given, R = (A, B, C, D, E, F)

Super key may contain additional attribute(s) that are not necessary to uniquely identify each row,
whereas, candidate key is the minimal super key. Removing any attribute from the Candidate key
fails to identify the each tuple uniquely.

C and E are the prime attribute. To identify a tuple uniquely, the prime attributes must exists in
the super key. Out of 6 attributes in relation R, 4 attributes are none-prime. Thus, to identify a
tuple uniquely, 24 ways we can arrange the non-prime attributes along with prime attributes.

prime attribute)
Minimum super key count = 2(non

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 9 / 18
 Keys Type of Keys

Primary and Alternate Key

Super Key → uniquely identification

Super Key

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 10 / 18
 Keys Type of Keys

Primary and Alternate Key

Super Key → uniquely identification

Super Key
Candidate Key → minimal super key
Candidate Key

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 10 / 18
 Keys Type of Keys

Primary and Alternate Key

Super Key → uniquely identification

Super Key
Candidate Key → minimal super key
Candidate Key

Primary Key Primary Key

b A candidate key used to uniquely
identify each row is called as a primary
key.
b Primary key: rollNo
b Primary key can improve performance
by making it easier for the database
engine to search, sort, and retrieve data
from table. . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 10 / 18
 Keys Type of Keys

Primary and Alternate Key

Super Key → uniquely identification

Super Key
Candidate Key → minimal super key
Candidate Key

Primary Key Primary Key

b A candidate key used to uniquely Alternate Key
identify each row is called as a primary
key.
b Primary key: rollNo
b Primary key can improve performance Alternate Key
by making it easier for the database b Alternate Key = Candidate - Primary key
engine to search, sort, and retrieve data b List of Alternate keys: emailID, aadharNo,
from table. (stdName, mothersName)
. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 10 / 18
 Normalization Normalization

Normalization

b Normalization is a process of decomposing unsatisfactory bad relations by breaking

up their attributes into smaller relations.
b Used to achieve properties such as,
- minimizing redundancy
- minimizing the insertion, deletion, and update anomalies
b A top-down approach. Also, known as relational design by analysis
b Normal Forms: Condition using keys and functional dependencies of a relation to
certify whether a relation schema is in a particular normal form.
b Normalization is carried out in practice so that the resulting designs are of high
quality and meet the desirable properties
b DE-normalization is a process of storing the join of higher normal form relations as a
base relation, which is in a lower normal form.
. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 11 / 18
 Normalization Normalization

Outcome of normalization

b Normalization procedure provides database designers with:

- A formal framework for analyzing relation schemas based on their keys and on the
functional dependencies among their attributes.
- A series of normal form tests that can be carried out on individual relation schemas so
that the relational database can be normalized to any desired degree.

b The normal form of a relation refers to the highest normal form condition that it
meets, and hence indicates the degree to which it has been normalized

b First three normal forms are known as Codd’s normal forms.

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 12 / 18
 Normalization Normalization

First Normal Form

b 1NF is based on atomic property of attributes. Thus, only atomic (single value)
attribute are permissible
b Non-permissible
- Multi-valued, composite and their combination
- Meta relation: relation within relation

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 13 / 18
 Normalization Normalization

First Normal Form

b 1NF is based on atomic property of attributes. Thus, only atomic (single value)
attribute are permissible
b Non-permissible
- Multi-valued, composite and their combination
- Meta relation: relation within relation
b A relation is in 1NF if all the attributes are indivisible

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 13 / 18
 Normalization Normalization

First Normal Form

b 1NF is based on atomic property of attributes. Thus, only atomic (single value)
attribute are permissible
b Non-permissible
- Multi-valued, composite and their combination
- Meta relation: relation within relation
b A relation is in 1NF if all the attributes are indivisible
b Techniques to achieve 1NF for such relation
- Remove the attribute (multi-valued, composite and their combination) that violates 1NF
and create a new relation with the primary key

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 13 / 18
 Normalization Normalization

First Normal Form

b 1NF is based on atomic property of attributes. Thus, only atomic (single value)
attribute are permissible
b Non-permissible
- Multi-valued, composite and their combination
- Meta relation: relation within relation
b A relation is in 1NF if all the attributes are indivisible
b Techniques to achieve 1NF for such relation
- Remove the attribute (multi-valued, composite and their combination) that violates 1NF
and create a new relation with the primary key
- Expand the attribute so that there will be a separate tuple in the original relation
making expanded attribute and the existing primary key as new primary key.

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 13 / 18
 Normalization Normalization

First Normal Form

b 1NF is based on atomic property of attributes. Thus, only atomic (single value)
attribute are permissible
b Non-permissible
- Multi-valued, composite and their combination
- Meta relation: relation within relation
b A relation is in 1NF if all the attributes are indivisible
b Techniques to achieve 1NF for such relation
- Remove the attribute (multi-valued, composite and their combination) that violates 1NF
and create a new relation with the primary key
- Expand the attribute so that there will be a separate tuple in the original relation
making expanded attribute and the existing primary key as new primary key.
/ Drawback: Introduces redundancy

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 13 / 18
 Normalization Normalization

First Normal Form

b 1NF is based on atomic property of attributes. Thus, only atomic (single value)
attribute are permissible
b Non-permissible
- Multi-valued, composite and their combination
- Meta relation: relation within relation
b A relation is in 1NF if all the attributes are indivisible
b Techniques to achieve 1NF for such relation
- Remove the attribute (multi-valued, composite and their combination) that violates 1NF
and create a new relation with the primary key
- Expand the attribute so that there will be a separate tuple in the original relation
making expanded attribute and the existing primary key as new primary key.
/ Drawback: Introduces redundancy
- If a maximum number of values is known for the attribute, then replace the violating
attribute with atomic attribute.
. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 13 / 18
 Normalization Normalization

First Normal Form

b 1NF is based on atomic property of attributes. Thus, only atomic (single value)
attribute are permissible
b Non-permissible
- Multi-valued, composite and their combination
- Meta relation: relation within relation
b A relation is in 1NF if all the attributes are indivisible
b Techniques to achieve 1NF for such relation
- Remove the attribute (multi-valued, composite and their combination) that violates 1NF
and create a new relation with the primary key
- Expand the attribute so that there will be a separate tuple in the original relation
making expanded attribute and the existing primary key as new primary key.
/ Drawback: Introduces redundancy
- If a maximum number of values is known for the attribute, then replace the violating
attribute with atomic attribute.
/ Drawback: Introduces null values . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 13 / 18
 Normalization Normalization

Second Normal Form

b 2NF is based on the concept of full functional dependency

- A functional dependency X → Y is a full functional dependency if removal of any
attribute A from X means that the dependency does not hold any more. That is X – A
does not functionally determine Y. For example:
{empID, projID} → {hours} is a full functional dependency. Neither
{empID} → hours} nor
{projID} → hours} hold

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 14 / 18
 Normalization Normalization

Second Normal Form

b 2NF is based on the concept of full functional dependency

- A functional dependency X → Y is a full functional dependency if removal of any
attribute A from X means that the dependency does not hold any more. That is X – A
does not functionally determine Y. For example:
{empID, projID} → {hours} is a full functional dependency. Neither
{empID} → hours} nor
{projID} → hours} hold
- A functional dependency X → Y is a partial functional dependency if some attribute A
can be removed from X and the dependency still holds; that is, for some (X – A) → Y.
For example: {empID, PAN} → {firstName, middleName, lastName}. Both
{empID} → {firstName, middleName, lastName} or
{PAN} → {fName, mName, lName} holds

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 14 / 18
 Normalization Normalization

Second Normal Form

b 2NF is based on the concept of full functional dependency

- A functional dependency X → Y is a full functional dependency if removal of any
attribute A from X means that the dependency does not hold any more. That is X – A
does not functionally determine Y. For example:
{empID, projID} → {hours} is a full functional dependency. Neither
{empID} → hours} nor
{projID} → hours} hold
- A functional dependency X → Y is a partial functional dependency if some attribute A
can be removed from X and the dependency still holds; that is, for some (X – A) → Y.
For example: {empID, PAN} → {firstName, middleName, lastName}. Both
{empID} → {firstName, middleName, lastName} or
{PAN} → {fName, mName, lName} holds
b A relation schema R is in 2NF if every non prime attribute A in R is fully
functionally dependent on the primary key of R
. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 14 / 18
 Normalization Normalization

Third Normal Form

b 3NF is based on the concept of transitive dependency

- Transitive dependency occurs when an attribute is functionally dependent on another
attribute, indirectly through a third attribute. If X, Y, and Z are attributes of relation
R, such that X → Y, and Y →Z, then Z is transitively dependent on X. For example: if
PAN → empID, and
empID → {fName, mName, lName} then
PAN → {fName, mName, lName}

b A relation schema R is in 3NF if it satisfies 2NF and no non prime attribute of R is

transitively dependent on the primary key.

b A relation schema R is in 3NF if whenever a FD X → A holds in R, then either:

- X is a superkey of R, or
- A is a prime attribute of R
. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 15 / 18
 Normalization Normalization

Boyce-Codd Normal Form

b BCNF is based on triviality of functional dependency.
- A non-trivial dependency exists when a non-key attribute (an attribute that is not part
of the primary key) is functionally dependent on another non-key attribute

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 16 / 18
 Normalization Normalization

Boyce-Codd Normal Form

b BCNF is based on triviality of functional dependency.
- A non-trivial dependency exists when a non-key attribute (an attribute that is not part
of the primary key) is functionally dependent on another non-key attribute
b Every relation in BCNF is also in 3NF, however, the other way is not true. Example.
The following relation in 3NF. If you wanted to assign a taID to a course before any
students enrolled, you couldn’t because studentID is part of the primary key, where
candidate key sets are {courseID, studentID} and {studentID, taID}
courseID studentID taID
CS208 101 TA01
CS218 101 TA01
CS208 102 TA02

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 16 / 18
 Normalization Normalization

Boyce-Codd Normal Form

b BCNF is based on triviality of functional dependency.
- A non-trivial dependency exists when a non-key attribute (an attribute that is not part
of the primary key) is functionally dependent on another non-key attribute
b Every relation in BCNF is also in 3NF, however, the other way is not true. Example.
The following relation in 3NF. If you wanted to assign a taID to a course before any
students enrolled, you couldn’t because studentID is part of the primary key, where
candidate key sets are {courseID, studentID} and {studentID, taID}
courseID studentID taID
CS208 101 TA01
CS218 101 TA01
CS208 102 TA02
b A relation is in BCNF if and only if at least one of the following conditions are met
for each FD A → B
- A is a superkey
- It is a trivial functional dependency. . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 16 / 18
 Normalization Normalization

Fourth Normal Form

b 4NF is based on multi-valued dependency.

- Multivalued dependency exists between attributes A and B if for every combination of

values of attributes A, there are one or more sets of values for attribute B that can occur
independently.

- If a relation R has attributes A, B, and C, and there is a multivalued dependency

between A and B, then for every value of A, there can be multiple values of B that are
independent of each other

b A relation is said to be in 4NF, if the relation is in BCNF and there are no

multi-valued dependencies.
. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 17 / 18
 Normalization Normalization

Fifth Normal Form

b 5NF is based on join dependency.

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 18 / 18
 Normalization Normalization

Fifth Normal Form

b 5NF is based on join dependency.

- Join dependency exists in a relation R if and only if R can be expressed as a natural join
of two or more other relations, such that the projection of R on any of its subsets is the
natural join of the projections of the other relations on that subset.

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 18 / 18
 Normalization Normalization

Fifth Normal Form

b 5NF is based on join dependency.

- Join dependency exists in a relation R if and only if R can be expressed as a natural join
of two or more other relations, such that the projection of R on any of its subsets is the
natural join of the projections of the other relations on that subset.

b A relation is said to be in 5NF, if the relation is in 4NF and there are no cyclic
dependencies.

b This is also called cyclic dependency. A cyclic dependency can occur only when
multi-field primary key consisting of three or more fields.

. . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
N. Saharia Database Management Systems 18 / 18