0:02
[Music]
0:06
thank you
0:09
[Music]
0:12
Hello friends welcome back to our
0:13
channel so in today's session we'll
0:16
discuss about one more important Concept
0:17
in dbms that is a normalization
0:26
so this is a very important concept and
0:29
before
0:31
watching this video so just please visit
0:33
our previous videos about the functional
0:36
dependency concept because so if you
0:39
know that functional dependency concept
0:40
it would be very easy for you to
0:42
understand this normalization process so
0:44
in the previous sessions we have
0:45
discussed about the functional
0:46
dependency and the types of functional
0:48
dependencies the attribute closures and
0:51
how to find the candidate key and super
0:52
Keys using the functional dependencies
0:54
so I'll post the link in the description
0:56
the playlist Link in the description so
0:58
just go through that one and after that
1:00
you can watch this normalization concept
1:03
definitely it will be very easy for you
1:05
to understand this one now uh first let
1:09
us know what exactly this normalization
1:10
right so this normalization is a single
1:14
term so we can say it is a single term
1:16
that is optimizing
1:19
optimizing
1:22
relations
1:26
optimizing the relations so here the
1:28
relation means
1:30
table
1:32
right so optimizing the relations means
1:35
so just we are
1:38
optimizing the tables optimizing the
1:41
tables so why we are optimizing these
1:43
tables
1:44
right so
1:47
in order to avoid the redundancy
1:55
avoid the redundancy so why why we are
1:58
moving towards this normalization right
2:00
so
2:01
there are some drawbacks in our table
2:04
schema structure so we can avoid that
2:06
drawbacks so what are the drawbacks
2:09
means the first one avoid the redundancy
2:11
so the table May consist of redundancy
2:14
that means a duplicating duplication of
2:16
data so we we have to remove the
2:18
duplication of data at the same time
2:21
we have studied about the anomalies
2:25
anomalies right inside anomaly delete
2:28
anomaly and update anomaly so these are
2:30
the floss floss in the table schema so
2:34
we have to avoid these anomalies okay so
2:38
in order to avoid the redundancy and
2:40
anomalies
2:42
so one solution
2:45
one solution
2:47
is to op optimize optimize
2:52
the tables Optimizer tables or relations
2:55
so how we can optimize this optimization
2:58
can be done
3:00
with decomposition
3:03
decomposition so the table can be
3:06
decomposed into subtables so relation
3:08
will be decomposed into some relations
3:12
sub relations
3:14
and this process is known as a
3:16
normalization this process is known as a
3:19
normalization and this process will be
3:21
done in different phases in different
3:23
phases so the first one
3:27
one NF which we call it as a first
3:31
normal form
3:33
first normal form
3:36
and the next one 2nf which is a second
3:40
normal form
3:43
second normal form
3:45
so how we can achieve that relation into
3:47
this first normal form and second normal
3:49
form we'll see later
3:51
so the next one is a three and F
3:54
third normal form
4:00
kind of next one is a b c n f
4:10
voice code
4:12
normal form
4:17
the next one is 4 NF which is a fourth
4:20
normal form
4:27
and last one is a 5 NF which is a
4:30
fifth normal form
4:37
fifth
4:39
normal form so by using all these things
4:44
we can achieve them
4:46
normalization which we can achieve the
4:48
normalization
4:54
right
4:55
so 1 f 2 n f three enough DC and F 4 NF
4:58
and 500 so if you achieve
5:02
up to bcnf we can say the schema the
5:07
schema is
5:09
good or we can say the database is a
5:12
good design a database is a good design
5:15
right so
5:19
if you want to know whether the relation
5:22
is in 2 and F the automatically the
5:25
relation should be in one NF then only
5:27
we have to go with the 2nm so if you
5:29
want to check whether the relation is in
5:31
3 and F the first it should satisfy the
5:33
200 then only it should be in 3M and if
5:36
you if you if the relation is in BC
5:38
enough that that implies the relation
5:40
satisfies with the three and then right
5:43
so
5:44
what I mean elaborate this one so
5:48
how the relation can be in a first line
5:50
of 1 and F how the relation can be in
5:52
2nf how the relation can be in 3 n f b c
5:55
and a four n f n 500 so let us see the
5:58
introduction and we will elaborate each
6:01
and every normal form in our next
6:03
sessions so initially let us assume that
6:07
so I'm normalized data
6:12
unnormalized data
6:14
so unnormalized data means a single
6:17
table consists of all the attributes
6:19
only one table with all other attributes
6:22
so this will be
6:27
coming to the 1nf
6:29
1 and F only after
6:33
removing
6:37
repeating groups
6:42
repeating groups so if you in the table
6:45
if any one of the attribute is having a
6:47
multiple values so then we have to
6:50
remove all those repeating values okay
6:53
so after that after removing all the
6:57
repeating groups these unorialized data
7:00
will be
7:02
1 and F will be in 1 error so if our
7:05
relation does not have any repeating
7:07
groups then simply we can say the
7:09
relation is in 1 and F and coming to the
7:12
2ndf
7:16
2 NF so after 1 and F
7:19
so we need to find the
7:21
partial
7:23
functional dependency so in the types of
7:25
functional dependencies we have
7:27
discussed about the fully functional
7:28
dependency and the partial for
7:30
functional dependency so we have to
7:33
identify the partial functional
7:34
dependencies are available or not if so
7:36
we have to remove those partial
7:38
functional dependencies so remove
7:41
partial
7:45
dependency
7:48
so after removing the partial dependency
7:50
we can say the relation is in 2 NF
7:53
and before removing the partial
7:54
dependency first the relation should be
7:57
in 1 and F then only we have to remove
7:59
the partial dependency then only the
8:02
relation will be in 12 and after that
8:08
3 and F
8:09
so once the relation is in 2nf so we can
8:13
check for the condition of 3 NF right so
8:16
without the relation is into NF we can't
8:18
go for the 300. so first the relation
8:20
should be into an F then only we have to
8:22
check for 3 NF so after 2nf there will
8:26
be no repeating groups there will be no
8:28
partial dependencies
8:30
and next here we need to find the
8:32
whether there are any
8:35
trendancies or not so in the previous
8:37
sessions in the functional dependencies
8:39
we have seen about the trivial
8:41
non-trivial and functional transitive
8:44
transitive dependencies so here we need
8:47
to find whether there are any transitive
8:49
relationship that means transitive
8:51
dependencies so if so we have to remove
8:53
those transitive dependencies then only
8:56
the relation will be in 3 and F so
8:59
identify and remove
9:02
remove
9:06
transitive dependency
9:12
remove transitive dependency once you
9:15
remove the transitive dependency
9:16
automatically the debt the relation will
9:18
be in 3 and F so after that
9:23
bcnf
9:26
bcnf so
9:29
once if you want to check this bcnf make
9:32
sure that the relation is in 3nf so
9:35
otherwise we have we should not go for
9:37
the bcnf so if the relation is not in
9:39
3nf check for the 2nf so if if the
9:42
relation is not in two NF check for one
9:44
NF right so because
9:47
for checking for 2nf the relation should
9:49
be in one unit
9:52
the relation should be in one NF then
9:54
only we have to check for 2nf so in
9:57
order to check 3 and F the relation
9:58
should be into NF if in order to check
10:00
for BC and F the relation should be in
10:02
tnf and here so if you want if you want
10:06
to check for BC enough make sure that
10:08
the dependencies there are no repeating
10:10
groups in our attributes there are no
10:12
functional dependencies in our
10:14
functional dependency there are no
10:15
transitive dependencies in our
10:17
functional dependencies and here make
10:19
sure that
10:23
make sure
10:25
determinants
10:29
determinants
10:31
are
10:34
candidate key
10:38
dependent determinants are candidates
10:40
determinants means the LHS LHS in
10:44
functional dependency
10:46
okay lhsin functional dependent for
10:48
example if the
10:50
function dependencies extends to Y so
10:52
this is the determinant and this will be
10:54
the dependent right so this LHS should
10:58
be a candid key
11:00
make sure the determinants are a
11:02
candidate case
11:04
right so once it was done the relation
11:07
is in bcnf so if the relation is in B C
11:10
and F we can tell that the database
11:13
design is a good design
11:15
okay so after that
11:17
still there will be a chance of having
11:20
the redundancy even though the database
11:23
design is good and the relation is in
11:25
pcnf there might be a chance that a
11:28
table consists of the relation consists
11:30
of a redundancy okay so still we have we
11:33
can normalize it
11:35
so that is a 4 n f
11:38
4 n f
11:40
so here we need to find whether the
11:43
functional dependencies are having any
11:45
multi-value dependencies that's also one
11:47
type of functional dependency
11:48
so is there are any functional multi
11:51
value dependencies are there so if so we
11:53
have to remove those multi-value
11:55
dependencies so remove
12:05
multi-valued dependency
12:09
multi-value dependencies
12:12
okay okay so after removing the multi
12:14
value dependencies we can say that the
12:15
relation is in 4 NF so if the relation
12:17
is 4 NF definitely the relation will be
12:20
in bcnf if the relation is bcnf we can
12:23
say the relation is in 300.
12:25
so foreign afterwards
12:31
the next one is
12:33
5 and F the fifth normal form so we need
12:36
to identify the joint dependencies if
12:38
there are any joint dependencies so if
12:41
so we have to remove the joint
12:42
dependencies so here
12:48
remove
12:50
all the joint dependencies
13:05
right joint dependencies
13:08
after removing the joint dependencies we
13:10
can say the relation is in 5 NF 500
13:14
fifth normal form
13:16
fifth normal form right so this is the
13:20
process of normalization so first we
13:23
have to consider the unnormalized data
13:26
okay so in that we have to identify and
13:28
we have to remove all the repeating
13:30
groups so that it will be converted into
13:31
I mean it satisfies the 1 and of
13:34
one and a normal form the first normal
13:36
form and from that we have to remove the
13:39
partial dependencies so that it will be
13:40
in 2nf and after that we have to remove
13:42
the transitive dependencies so it will
13:44
be in 3 NS and make sure that
13:46
determinants are candid case so it will
13:48
be in bcnf so bcnf is somewhat strong
13:51
than the 3nf right both are almost
13:53
similar but it is somewhat strong for DC
13:57
and then so if any relation satisfies up
13:59
to the B C and F we can say the database
14:01
design is a good design okay still there
14:03
might be a chance of having the
14:05
redundancy so we're moving to the four
14:06
and F and 5nf by removing the
14:08
multi-value dependencies and removing
14:10
the joint dependencies
14:13
right and once again I'm repeating this
14:15
normalization process is mainly used to
14:18
optimize the table table schema okay the
14:21
structure of a table
14:23
so why we are moving why we are
14:25
optimizing means there might be a chance
14:27
of having the redundancy and the
14:29
anomalies so in the previous session we
14:31
have discussed about the
14:33
anomalies so Inception anomaly updation
14:36
anomaly and deletion anomalies so those
14:38
are the flaws okay floss in the table
14:41
design relation design
14:43
so we have in order to avoid those two
14:45
things that means a redundancy that
14:46
means a duplicate data as well as the
14:48
anomalies problem so we are moving
14:50
towards the normalization it will be uh
14:53
done in the different phases that is a
14:55
one enough to end of three and a b c
14:56
enough at four and a friend 500 so in
14:59
our next session we'll start with the
15:00
1nf right so we will go in detail
15:04
individually from the next sessions
15:07
so hope you understood this one so once
15:09
again I'm saying so just go through the
15:12
previous sessions the topics of
15:14
functional dependency the types of
15:16
functional dependency axioms and finding
15:19
the candid key and super Keys using the
15:22
functional dependencies so I'll post the
15:24
playlist Link in the description so go
15:26
through that afterwards just visit this
15:29
normalization process so that it will be
15:31
very easy for you to understand this
15:32
normalization process because all these
15:35
are the different types of dependencies
15:36
only
15:37
right so let's drop here so if you are
15:40
having any doubts regarding this one
15:41
feel free to post your notes in the
15:42
comment section definitely I will try to
15:44
clarify all your notes and if you really
15:46
enjoyed my session like my session share
15:48
my session with your friends and don't
15:49
forget to subscribe to our Channel
15:51
thanks for watching thank you very much