Normalization in DBMS

Normalization is a process for evaluating and correcting table structures to

minimize data redundancies, thereby reducing the likelihood of data
anomalies like insert anomaly, update anomaly and delete anomaly.

Normalization works through a series of stages called normal forms.

The first three stages are described as

First normal form (1NF),

Second normal form (2NF), and
Third normal form (3NF).

2
Denormalization produces a lower normal form;

THE NEED FOR NORMALIZATION

Normalization makes a table free from insert, update and delete

anomalies.

3
Consider the table: student

4
If we want to insert a student information, we have to repeat the
information about department, building and room number for each
student.

This is called Redundancy of information.

When we want to enter millions of entries in the database, so much

redundant data will be there.

This problem occurs when we take large schemas. (storing all the
information in only one table)

5
If we want to insert a student information, we have to repeat the
information about department, building and room number for each
student.

This is called Redundancy of information.

When we want to enter millions of entries in the database, so much

redundant data will be there.

This problem occurs when we take large schemas. (storing all the
information in only one table)

6
If we decompose this table into smaller tables, then we can reduce the
redundancy.

Data Anomalies

There are three types of anomalies: update, deletion and insertion

anomalies.

An insertion anomaly is the inability to add data to the database due to

absence of other data.

7
Example:
If we want to insert a new department ME to this table, we must
insert sid, sname and credits in the tuple. But if no students are enrolled
into the department, we can not insert the student information.

8
We can’t insert null value for Sid because Sid is the primary key that can
not be null.

So here, we can insert a department only if at least one student is

enrolled in that department.

This is a problematic situation with insert operation.

This is called Insert anomaly.

9
An update anomaly is a data inconsistency that results from data
redundancy and a partial update.

Ex: If CSE department is shifted from building B1 to C1.

Then, we want to update the Building and Room_no of CSE department.

So we must update these info. in all the tuples where Dept_name is “CSE”.

10
Here the last row of CSE is not updated.

There exists a data inconsistency that results from data redundancy and a
partial update. This is called update anomaly

11
A deletion anomaly is the unintended loss of data due to deletion of
other data.

Suppose we want to delete a student with sid 5. Then we have to delete

the entire tuple from the relation.

Automatically, the department information is also deleted. Ie, there will

be no information about the ECE department when the last student is
deleted from the table.

12
 How to solve the anomalies?

Suppose we decompose the student table into 2: student and department.

Here we removed the Redundancy. Dept_name is the primary key of

table Department, and foreign key of the table Student.
13
Denormalization:

Denormalization produces a lower normal form;

Sometimes we have to join multiple tables to get the results.

Eg: OLAP (Online Analytical Processing)

14
Normalization:

• Process of making the table free from insert, update and delete
anomalies.
• Saves space by reducing the redundant data or duplicate data.
• Simplifies the queries(when dividing large tables into smaller one)
• Minimizes Null values.
• Important for OLTP systems where insert, update and delete
anomalies exist very frequently by end users.
• It simplifies the database structures.
• Small tables – easily understand the schema.
• Searching will be faster.

15
 Functional dependency

A functional dependency is a constraint that specifies the relationship

between two sets of attributes where one set can accurately determine
the value of other sets.

It is denoted as X → Y, where X is a set of attributes that is capable of

determining the value of Y.

The attribute set on the left side of the arrow, X is called Determinant,
while on the right side, Y is called the Dependent.

Roll_no → Name means, Roll_no can determine Name.

16
Let R be the relation schema and X,Y be some sets of attributes over R.

X -> Y exists in relation R only if t1.X = t2.X and t1.Y = t2.Y

where t1, t2 are the tuples of relation R

which means that whenever the X values are same , corresponding Y

values must also be same, and reverse may not be same.

X -> Y is known as the functional dependency, which also means that X

functionally determines Y.

17
18
Check whether the given functional dependencies are valid or not

roll_no → name
name → dept_name
roll_no → dept_name
roll_no → dept_building
dept_building → dept_name
dept_name → dept_building
{roll_no, name} → {dept_name, dept_building}
name → roll_no
dept_building → roll_no

19
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.
20
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, For 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,

dept_building → roll_no, etc.

21
 Inference Rule (IR):

Armstrong’s axioms/properties of functional dependencies:

The Functional dependency has 6 types of inference rule:

1. Reflexivity (IR1) : X →X, Any attribute determines itself.

If Y is a subset of X, then X→Y holds by reflexivity rule.

For example, roll_no → roll_no

{roll_no, name} → name is valid.
{roll_no, name} → roll_no
22
2. Transitivity (IR2) : If X → Y and Y → Z are both valid dependencies, then
X→Z is also valid by the Transitivity rule.

For example,

if roll_no → dept_name & dept_name → dept_building,

then roll_no → dept_building is also valid.

23
3. Augmentation (IR3) : If X → Y is a valid dependency, then XZ → YZ is
also valid by the augmentation rule.

For example,

If roll_no → name is valid,

hence {roll_no, dept_name} → {name, dept_name} is also valid.

24
4. Union Rule (IR4) :

Union rule says, if X determines Y and X determines Z, then X must also

determine Y and Z.

If X → Y and X → Z then X → YZ

If roll_no → name and roll_no → dept_name

Then roll_no → {name, dept_name}

25
5. Decomposition Rule (IR5) or Splitting property :

Decomposition rule is also known as project rule. It is the reverse of union

rule.
This Rule says, if X determines Y and Z, then X determines Y and X
determines Z separately.

If X → YZ then X → Y and X → Z

If roll_no → {name, dept_name} then

roll_no → name and roll_no → dept_name

(note: you can split only RHS-dependent

26 not LHS-determinant)
6. Pseudo transitive Rule (IR6) :

In Pseudo transitive Rule, if X determines Y and YZ determines W, then XZ

determines W.

If X → Y and YZ → W
Then XZ → W

27
7. Composition(IR7) :

In Composition Rule, if X determines Y and A determines B, then XA

determines YB.

If X → Y and A → B
Then XA → YB

28
Types of Functional dependencies in DBMS:

• Trivial functional dependency

• Non-Trivial functional dependency

• Multivalued functional dependency

• Transitive functional dependency

29
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.

X → X is is also trivial functional dependency.

30
For example, 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.

31
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.

32
For Example,
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}

33
3. 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.

34
For Example,
Here, roll_no → {name, age} is a multivalued
functional dependency,

since the dependents name & age are not

dependent on each other

(i.e. name → age or age → name doesn’t exist !)

35
4. 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

36
For example
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.
37
 Attribute closure

If X is a set of attrbutes, X+ (Closure of X) contains set of attributes

determined by X.
Example:
Suppose R(A,B,C,D,E) is a relation having 5 attributes .
The functional dependencies which are present on these relation are:

A->B
B->C
C->D
D->E
38
Closure of A ie, A+ :

A->A (reflexivity) – add A to the closure

A->B – Add B to the closure
B->C – Add C to the Closure
C-> D – Add D to the Closure
D-> E – Add E to the Closure A+ ={ A,B,C,D,E}
A+ ={A} (using A->A)
={A,B} (using A->B)
={A,B,C} (using B->C)
={A,B,C,D} (using C->D)
={A,B,C,D,E} (using D->E)

39
Or we can find out A+ like this:

If A->B and B->C, we can say that A->C (transitivity)

A->A(reflexivity)

If A->C and C->D, we can say that A->D

If A->D and D->E, we can say that A->E

A->ABCDE (union property)

So A can determine A,B,C,D,and E and A+ = {A,B,C,D,E}

40
B+ = {B,C,D,E}

C+ = {C,D,E}

D+ = {D,E}

E+ = {E}

AD+ = {A,D,B,C,E}

CD+ = {C,D,E}

41
Example-

Consider a relation R ( A , B , C , D , E , F , G ) with the functional

dependencies-
A → BC
BC → DE
D→F
CF → G

Find out A+
D+
And { B , C }+
42
Consider the given functional dependencies-
AB → CD
AF → D
DE → F
C→G
F→E
G→A
Find out
(A) { CF }+
(B) { BG }+
(C) { AF }+
(D) { AB }+

43
 Minimal set of Functional Dependencies
A canonical cover is a simplified and reduced version of the given set of
functional dependencies.

Since it is a reduced version, it is also called as Irreducible set.

Characteristics-
Canonical cover is free from all the extraneous 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.
44
Need-

Working with the set containing extraneous functional dependencies

increases the computation time.

Therefore, the given set is reduced by eliminating the useless functional

dependencies.

This reduces the computation time and working with the irreducible set
becomes easier.

45
Algorithm

Step 1: Write the FD’s in such a way that the right hand side must contain
1 attribute.

Step 2: Find the Closure of Attributes.

Step 3: Check whether any left hand attributes can be reduced.

46
Example:

F= {X -> W, WZ -> XY, Y -> WXZ}

Step 1: Write the FD’s in such a way that the right hand side must contain
1 attribute.

F= {X -> W, WZ -> X, WZ -> Y, Y -> W, Y -> X, Y -> Z}

47
Step 2: Find the Closure of Attributes. And check whether the FD is
essential or not. X -> W

1. Consider X -> W WZ -> X

X+ = { X,W}
WZ -> Y
By ignoring X -> W
Y -> W
X+ = { X}.
So by ignoring X -> W, we can not derive W. Y -> X
So X -> W is essential.
Y -> Z

48
2. Consider WZ -> X X -> W

WZ + = { W,Z,X,Y} WZ -> X

By ignoring WZ -> X WZ -> Y

WZ + = { W,Z,X,Y} Y -> W

So by ignoring WZ -> X , we can derive the same closure. Y -> X

So WZ -> X is not essential and can be removed.
Y -> Z

49
2. Consider WZ -> Y
X -> W
WZ + = { W,Z,Y,X}

By ignoring WZ -> X
WZ -> Y
WZ + = { W,Z}
Y -> W
So by ignoring WZ -> Y
, we can not derive the same closure. Y -> X

So WZ -> Y is essential. Y -> Z

50
2. Consider Y -> W
X -> W
Y + = {Y,W,X,Z}

By ignoring Y -> W
WZ -> Y
Y + = {Y,X,Z,W}
Y -> W
So by ignoring Y -> W, we can derive the
same closure. Y -> X
So Y -> W is not essential and can be
removed. Y -> Z

51
2. Consider Y -> X
X -> W
Y + = {Y,X,Z,W}

By ignoring Y -> X
WZ -> Y
Y + = {Y,Z}

So by ignoring Y -> X, we can NOT derive the same closure. Y -> X

So Y -> X is essential. Y -> Z

52
2. Consider Y -> Z
X -> W
Y + = {Y,X,Z,W}

By ignoring Y -> Z
WZ -> Y
Y + = {Y,X,W}

So by ignoring Y -> Z, we can NOT derive the same closure. Y -> X

So Y -> Z is essential. Y -> Z

53
 So we removed all extraneous attributes, we get the
functional dependencies as X -> W

F= {X -> W, WZ -> Y, Y -> X, Y -> Z}

WZ -> Y

Y -> X

Y -> Z

54
Step 3: Check whether any left hand attributes can be reduced.

F= {X -> W, WZ -> Y, Y -> X, Y -> Z}

Consider the functional dependencies with more than one attribute on

the left hand side.

Consider WZ -> Y and check whether we can eliminate any one of the
attributes or not.

WZ+ = {W,Z,Y,X}, W+ = {W}, Z+ ={Z} .

W or Z alone can not determine the same closure. So we can not
eliminate any of the attributes on the LHS. So WZ -> Y is essential.
55
F= {X -> W, WZ -> Y, Y -> X, Y -> Z} is the minimum Functional dependency
or Minimum cover or Canonical cover of F= {X -> W, WZ -> XY, Y -> WXZ}

56
 First Normal Form (1NF)

For a table to be in the First Normal Form, it should follow the following
4 rules:

[Link] should only have single(atomic) valued attributes/columns.

[Link] stored in a column should be of the same domain.

[Link] the columns in a table should have unique names.

[Link] the order in which data is stored, does not matter.

57
Rule 1: Single Valued Attributes

Each column of your table should be single valued which means they should
not contain multiple values.

Student_ID Name Subjects

101 Rahul Math, Science

102 Anjali English

The Subjects column has multiple values ("Math, Science")

This is not atomic → Violates 1NF(Not in 1NF)
58
Rule 2: Attribute Domain should not change

In each column the values stored must be of the same kind or type.

Student_ID Name Age/Remarks

101 Rahul 18
102 Anjali Good Student

The column Age/Remarks stores mixed data types (a number and a text
comment - Not in 1NF)

59
Rule 3: Unique name for Attributes/Columns

This rule expects that each column in a table should have a unique name.
This is to avoid confusion at the time of retrieving data or performing any
other operation on the stored data.

Student_ID Name Name

101 Rahul Roy

Column names are not unique → Confusing and invalid (Not in 1NF)

60
 Rule 4: Order doesn't matters

This rule says that the order in which you store the data in your table
doesn't matter.

| ID | Name | Age | and | Name | Age | ID |

change the order of rows — it does not affect the database or normalization.

61
 roll_no name subject
101 Abin OS, CN
103 Ciya Java
102 Binu C, C++

Our table already satisfies 3 rules out of the 4 rules, as all our column
names are unique, we have stored data in the order we wanted to and
we have not inter-mixed different type of data in columns.

But out of the 3 different students in our table, 2 have opted for more
than 1 subject. And we have stored the subject names in a single column.
But as per the 1st Normal form each column must contain atomic value.

62
How to solve this Problem?

It's very simple, because all we have to do is break the values into
atomic values.

Here is our updated table and it now satisfies the First Normal Form
Roll_no name subject
101 Abin OS
101 Abin CN
103 Ciya Java
102 Binu C
102 Binu C++

63
 Student_ID Name Courses Contact Numbers

9876543210,
101 Rahul Math, Science
9123456789

102 Anjali English 9988776655

9876501234,
103 Kiran Math, English
9123009876

This is NOT in 1NF:

•Courses column has multiple values in one cell → Not atomic

•Contact Numbers also contains multiple phone numbers in one cell → Not atomic
64
Convert to 1NF: We break down multi-valued attributes into separate rows, so
each value becomes atomic.
Student_ID Name Course Contact Number

101 Rahul Math 9876543210

101 Rahul Math 9123456789

101 Rahul Science 9876543210

101 Rahul Science 9123456789

102 Anjali English 9988776655

103 Kiran Math 9876501234

103 Kiran Math 9123009876

103 Kiran English 9876501234

103 Kiran English 9123009876

65
By doing so, although a few values are getting repeated but values for
the subject column are now atomic for each record/row.

Using the First Normal Form, data redundancy increases, as there will be
many columns with same data in multiple rows but each row as a whole
will be unique.

66
 Second Normal Form

For a table to be in the Second Normal Form, it must satisfy two

conditions:

• The table should be in the First Normal Form.

• There should be no Partial Dependency. ie, every non-prime attribute is

fully dependent on the whole primary key (not just a part of it)

67
Example:
student_id subject_id student_name marks

101 S1 Rahul 80

101 S2 Rahul 70

102 S1 Anjali 90

102 S2 Anjali 60

Why this is NOT in 2NF

• Composite primary key: (student_id, subject_id)

• student_name depends only on student_id, not on the full composite key

•➤ Partial Dependency exists (Violates 2NF)

❌ This table is in 1NF, but not in 2NF68
This is Partial Dependency, where an attribute in a table depends only on
a part of the primary key and not on the whole key.

How to remove Partial Dependency?

There can be many different solutions for this, but our objective is to
decompose the table into two:

69
To Convert to 2NF: We remove partial dependency by decomposing the table into
two:
1. Student Table

student_id student_name
101 Rahul
102 Anjali

2. Marks Table

student_id subject_id marks

101 S1 80
101 S2 70
102 S1 90
102 S2 70 60
Now the Tables Are in 2NF

• All non-key attributes depend on the entire primary key

• Partial dependency removed

Design satisfies Second Normal Form (2NF)

71
Notes

• For a table to be in the Second Normal form, it should be in the First

Normal form and it should not have Partial Dependency.

• Partial Dependency exists, when for a composite primary key, any

attribute in the table depends only on a part of the primary key and
not on the complete primary key.

• To remove Partial dependency, we can divide the table, remove the

attribute which is causing partial dependency, and move it to some
other table where it fits in well.

72
 Third Normal Form (3NF)

A table is in Third Normal Form (3NF) if:

1. It is in Second Normal Form (2NF)

2. It has no transitive dependencies

(No non-key attribute should depend on another non-key attribute)

73
Initial Table: Marks Entry (In 2NF but Not in 3NF)

student_id subject_id marks exam_type max_marks

101 S1 80 Midterm 100

101 S2 70 Practical 50
102 S1 90 Midterm 100
102 S2 40 Practical 50

74
• This table is in 1NF

• This table is in 2NF

Why this table is in 2NF?

• Primary Key = (student_id, subject_id)

(Because each student can have multiple subjects, and marks are identified
by both)

75
 Depends on Depends on Depends on
Attribute Comment
student_id? subject_id? both?
Depends on the
specific student
marks ❌ ❌ ✅
and subject (full
key)

A student's exam
exam_type ❌ ❌ ✅ type is based on
subject taken
Depends only on
exam_type (not on
max_marks ❌ ❌ ❌
student or subject
76 directly) ❌
marks and exam_type depend on the full composite key → No partial
dependency

But max_marks depends on exam_type, which is a non-key attribute →

transitive dependency

So, the table satisfies 2NF, but ❌ not 3NF

77
Why Not in 3NF

There's a transitive dependency:

(student_id, subject_id) → exam_type

exam_type → max_marks

So, max_marks is indirectly dependent on the primary key

78
Transitive Dependency.
A transitive dependency occurs in a database table when a non-key
attribute depends on another non-key attribute, not directly on the
primary key.
A→B
B→C
Then, A → C is a transitive dependency
Where:
A is a candidate key (or part of the primary key)
B is a non-prime attribute
C is also a non-prime attribute
This violates the rules of Third Normal Form (3NF).

79
 How to remove Transitive Dependency

To Convert to 3NF:

We remove transitive dependency by splitting the table into two:

1. Score Table

student_id subject_id marks exam_type

101 S1 80 Midterm
101 S2 70 Practical
102 S1 90 Midterm
102 S2 40 Practical
80
[Link] Table

exam_type max_marks

Midterm 100
Practical 50

81
Now the Tables Are in 3NF

• All non-key attributes depend only on the key

• No transitive dependency

✅ Design satisfies Third Normal Form (3NF)

82
 Boyce-Codd Normal Form (BCNF)
Rules for BCNF

For a table to satisfy the Boyce-Codd Normal Form, it should satisfy the
following two conditions:

1. It should be in the Third Normal Form.

2. And, for any dependency A → B, A should be a super key.

(In simple words, 2 means, that for a dependency A → B, A cannot
be a non-prime attribute, if B is a prime attribute.)

83
Consider a table college enrolment table with columns student_id,
subject and professor.

student_id subject professor

101 Java [Link]
101 C++ [Link]
102 Java P.Java2
103 C# [Link]
104 Java [Link]

84
In the table :

One student can enrol for multiple subjects. For example, student with
student_id 101, has opted for subjects - Java & C++

For each subject, a professor is assigned to the student.

And, there can be multiple professors teaching one subject like we have
for Java.

student_id, subject together form the primary key, because using

student_id and subject, we can find all the columns of the table.

85
here, one professor teaches only one subject, but one subject may have
two different professors.

Hence, there is a dependency between subject and professor here, where

subject depends on the professor name.

86
This table satisfies the 1st Normal form because all the values are atomic,
column names are unique and all the values stored in a particular column
are of same domain.

This table also satisfies the 2nd Normal Form as their is no Partial
Dependency.

And, there is no Transitive Dependency, hence the table also satisfies

the 3rd Normal Form.

But this table is not in Boyce-Codd Normal Form.

87
Why this table is not in BCNF?

In the table above, student_id, subject form primary key, which means
subject column is a prime attribute.

But, there is one more dependency, professor → subject.

And while subject is a prime attribute, professor is a non-prime attribute,

which is not allowed by BCNF.

88
To make this relation(table) satisfy BCNF, we will decompose this table
into two tables, student table and professor table.

Student Table
student_id p_id
101 1
101 2

Professor Table
p_id professor subject
1 [Link] Java
2 [Link] C++

Now, this relation satisfy Boyce-Codd

89 Normal Form
Fourth Normal Form (4NF)

Fourth Normal Form comes into picture when Multi-valued Dependency

occur in any relation.

Rules for 4th Normal Form

For a table to satisfy the Fourth Normal Form, it should satisfy the
following two conditions:

• It should be in the Boyce-Codd Normal Form.

• And, the table should not have any Multi-valued Dependency.

90
 What is Multi-valued Dependency?
A table is said to have multi-valued dependency, if the following conditions are
true,

• For a dependency A → B, if for a single value of A, multiple value of B exists,

then the table may have multi-valued dependency.

• Also, a table should have at-least 3 columns for it to have a multi-valued

dependency.

• And, for a relation R(A,B,C), if there is a multi-valued dependency between

A and B, then B and C should be independent of each other.

If all these conditions are true for any relation(table), it is said to have multi-
valued dependency.
91
Example

Below we have a college enrolment table with columns s_id, course and
hobby.

s_id course hobby

1 Science Cricket
1 Maths Hockey
2 C# Cricket
2 Php Hockey

92
student with s_id 1 has opted for two courses, Science and Maths, and has
two hobbies, Cricket and Hockey. this can lead to some problems.

The two records for student with s_id 1, will give rise to two more records,
as shown below, because for one student, two hobbies exists, hence along
with both the courses, these hobbies should be specified.
s_id course hobby
1 Science Cricket
1 Maths Hockey
1 Science Hockey
1 Maths Cricket

93
And, in the table above, there is no relationship between the columns
course and hobby.

They are independent of each other.

So there is multi-value dependency, which leads to un-necessary

repetition of data and other anomalies as well.

94
How to satisfy 4th Normal Form?
To make the above relation satify the 4th normal form, we can decompose
the table into 2 tables.
CourseOpted Table

s_id course
1 Science
1 Maths
2 C#
2 Php

95
And, Hobbies Table,

s_id hobby
1 Cricket
1 Hockey
2 Cricket
2 this relation satisfies the fourthHockey
Now normal form.

A table can also have functional dependency along with multi-valued

dependency. In that case, the functionally dependent columns are moved
in a separate table and the multi-valued dependent columns are moved
to separate tables.
96
Fifth Normal Form (5NF) :

Any relation in order to be in the fifth normal form must satisfy the
following conditions:

• It must be in Fourth Normal Form (4NF).

• It should have no join dependency and also the joining must be lossless.

In the fifth normal form the relation must be decomposed in as many sub-
relations as possible so as to avoid any kind of redundancy and there must
be no extra tuples generated when the sub-relations are combined
together by using natural join.

97
5NF is also known as Project Join Normal Form (PJNF).

Thus if natural join is performed on all the three relations then there will
be no extra tuples.

Hence R1, R2 and R3 are in fifth normal form (5NF).

98
Spurious Tuples are those rows in a table, which occur as a result of joining
two tables in wrong manner. They are extra tuples (rows) which might not
be required.

If relation is denoted by R, and its decomposed relations are denoted by

R1, R2, R3….Rn, then, condition for not getting any Spurious Tuple is
denoted by,

R1 ⨝ R2 ⨝ R3 .... ⨝ Rn = R
Whereas condition for getting Spurious Tuples is denoted by,

R ⊂ R1 ⨝ R2 ⨝ R3 .... ⨝ Rn

99
Example-1:
Example to check if given relation contains Spurious Tuples.
Let R be Relation, and R1 and R2 be relations which we get after
decomposing R.

100
Thus if natural join is performed on all these relations then there will be no extra tuples.
Hence R1, R2 are in fifth normal form (5NF). 101
102
103
 SURROGATE KEY CONSIDERATIONS
A composite primary key is too cumbersome to use as the number of
attributes grows.

• It becomes difficult to create a suitable foreign key when the related

table uses a composite primary key.

• A composite primary key makes it more difficult to write search routines.

There will also be problems when the key attribute might simply have
too much descriptive content

When, for whatever reason, the primary key is considered to be

unsuitable, designers use surrogate keys.
104
• At the implementation level, a surrogate key is a system-defined
attribute generally created and managed via the DBMS.

• Usually, a system-defined surrogate key is numeric, and its value is

automatically incremented for each new row.

105
Consider an example :
Suppose we have two tables of two different schools having the same
column registration_no , name and percentage , each table having its own
natural primary key, that is registration_no.
Table of school A –
registration_no name percentage

210101 Harry 90

210102 Maxwell 65

210103 Lee 87

210104 Chris 76

106
Table of school B –

registration_no name percentage

CS107 Taylor 49

CS108 Simon 86

CS109 Sam 96

CS110 Andy 58

107
 Now, suppose we want to merge the details of both the schools in a single
table .
surr_no registration_no name percentage
Resulting table will be –
1 210101 Harry 90

2 210102 Maxwell 65

3 210103 Lee 87

4 210104 Chris 76

5 CS107 Taylor 49

6 CS108 Simon 86

7 CS109 Sam 96

8 CS110 Andy 58

This additional column of surr_no is the surrogate

108 key of the table.
Advantages of the surrogate key :

As there is no direct information related with the table, so the changes are
only based on the requirements of the application.

Performance is enhanced as the value of the key is relatively smaller.

The key value is guaranteed to contain unique information .

As it holds smaller constant values , this makes integration of the table

easy .
Enables us to run fast queries (as compared to the natural primary key)

109
Improving the Design of a Database

We can improve the databases ability to provide information and to

enhance the operational characteristics. The following six concepts help to
boost a databases design.

1 Evaluate Primary Key Assignments

2 Evaluate Naming Conventions
3 Refine Attribute Atomicity
4 Identify New Attributes
5 Identify New Relationships
6 Refine Primary Keys as Required for Data Granularity

110
Evaluate Primary Key Assignments

When you're adding specific entities to a database, it's important to

understand the overall concept of how these entities are affecting the
rest of the database.

You will need to focus on specific entities, usually the Primary Keys, in
order to make a successful and reliable database.

Inputting information can be very repetitive when doing it consistently,

making it more prone for errors leading to referential integrity violations.

111
A surrogate key could be used in this instance by creating tables through
primary keys.

A surrogate key, is an artificial PK introduced by the designer with the

purpose of simplifying the assignment of primary keys to tables.

Surrogate keys are usually numeric, they are often automatically

generated by the DBMS, they are free of semantic content (they have no
special meaning), and they are usually hidden from the end users.

112
Evaluate Naming Conventions

The purpose of revising naming conventions is to provide a more accurate

set of details or to help split up a broad entity/attribute into more of
these categories.

113
Refine Attribute Atomicity

An atomic attribute is a category to which it can no longer be divided into

smaller categories.

However having an attribute displaying atomicity is can be used in the

same group but revised enough to get specific information.

Using the example of User Address; the address is composed of various

bits of information such as the specific address itself, the city, state, and
zip code. You can easily add atomicity to this example by displaying
User Address, User City, User State, and User Zip all in separate areas.

114
Identify New Attributes

When creating attributes that affect one another, it's important to

understand the links they may have to other unknown attributes.

115
Identify New Relationships

One of the main goals of a database is to reduce the number of undesired

data duplications.

It is important as a designer to lace the right attributes in the right tables

through the normalization process.

116
Refine Primary Keys as Required for Data Granularity

Obviously data that is created in the lower levels of the database (Atomic)
and sub-levels are not as important as the higher, broader attributes.
However using the granularity of these low key atomic values will help
keep the database more detailed and specific on what needs to be
categorized where. An example of this could involve assigning the number
of hours an individual drives. However this attribute isn't clear enough by
forgetting to state the specifics of the time periods the individual drives
such as daily, weekly, monthly and so on. Be sure to focus on explaining
these details clearly so if you're arranging a report you can sort by more
specific details.

117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142