0:02
[Music]
0:06
thank you
0:09
[Music]
0:11
Hello friends welcome back to our
0:13
channel so in today's session we'll
0:15
discuss about one more normal form in
0:18
the normalization that is a third normal
0:20
form
0:21
we call it as a 3 in F
0:25
so which is a third normal form
0:33
so in the previous sessions we have
0:35
discussed about the first normal form
0:37
and the second normal form
0:38
and what's the input for this third
0:41
normal form
0:43
the input for third normal form is the
0:45
relation should be in 2nf
0:54
should be in
0:56
to NF so you need to refer the previous
0:58
sessions so how the relation can be into
1:02
NF right and if the relation should be
1:05
in 2 and F first the relation should be
1:07
in 1 and F so you need to follow the
1:09
normalization from the beginning right
1:12
so why we are moving to the
1:14
normalization so what is a one and F
1:17
what is a two NF and then you have to
1:19
refer this third normal form right what
1:22
is the output for this one
1:29
relation
1:31
in
1:32
PNF so when we call it as a a relation
1:35
should be in Creative what are the
1:37
conditions so what are the conditions
1:39
need to satisfy if the relation should
1:42
be in 3 NF the first and foremost
1:44
condition is
1:46
so
1:49
the relation
1:54
should be
1:55
in 2nd that's a normal uh one okay the
1:59
first condition and the second condition
2:03
there should be no transitive
2:05
dependencies
2:08
transitive dependencies
2:14
first
2:16
non-prime attributes
2:20
non-prime attributes
2:23
so we need to understand what is the
2:27
transitive dependency and what are the
2:29
non Prime attributes right so already we
2:32
have discussed the same in the previous
2:33
sessions in the functional dependency
2:35
concept right so once you have to go
2:37
through that particular video also so
2:39
for that purpose I'll post the complete
2:42
playlist in the description section so
2:44
visit that one so learn the functional
2:47
dependencies and what are the non-prim
2:49
attributes and the prime attributes so
2:51
that you can understand this video very
2:53
clearly so I will I will explain you at
2:56
a glance what is a transitive dependency
2:58
so this transitive dependency means for
3:01
example if you are having a any
3:03
functional dependencies like X Change to
3:05
Y and Y tends to Z then automatically he
3:09
extends to Z is also in functional
3:13
dependency so if both the functional
3:16
dependencies F extends to Y and Y tends
3:18
to Z then obviously this x tends to
3:22
check will also be the functional
3:23
dependency okay so we have to avoid such
3:26
things only for the non Prime attributes
3:29
okay not for all the attributes we have
3:31
to search for the non-k whether any non
3:33
Prime attribute is having this
3:35
transitive dependency now what is this
3:38
non-prime attribute
3:39
so these non-prime attributes are that
3:42
attributes
3:44
attributes of a candidate key
3:51
sorry attributes of
3:54
candidate key are called Prime
3:56
attributes and attributes
4:04
not in a candidate key attributes not in
4:07
a candidate key are called non-primatic
4:09
Goods so if the candidate key I mean if
4:12
the candidate key is having any
4:14
attributes those attributes we call as a
4:16
prime attributes so we have to identify
4:19
the non-prime attributes that means the
4:20
attributes which are not in a candid key
4:23
okay so there should not be the
4:26
transitive dependency for this
4:28
particular non-prime attribute and in
4:31
this another way and the another way we
4:34
can say it as so if x tends to Y is a
4:39
functional dependent so X should be
4:44
X should be
4:46
the primary key
4:50
and Y should be
4:53
y should be
4:55
part of
4:58
candidate key
5:01
candidate key
5:04
right so if Y is part of candidate key
5:08
we can call this y as a Prime attribute
5:12
we can call this y as a prime attribute
5:14
okay so X is a Super Key I I mean
5:17
primary key so primary key here means
5:19
with by knowing X we can get all the
5:22
details of all the other attributes the
5:25
details of all other attributes right so
5:27
these two are the major conditions need
5:29
to need to be satisfied
5:32
if the relation is in 3 and F if you
5:35
want to check whether the relation is in
5:37
three and a half the first one is should
5:39
be in 2nf and no transitive dependencies
5:42
for all the non Prime attributes so
5:44
first we need to find the non-prime
5:46
attributes and we have to identify
5:48
whether they are involving in the
5:50
transcript dependency or not so for this
5:52
I will take a small and simple example
5:54
so that you can understand these two
5:56
conditions uh clearly right
6:00
so let us take this example so this is a
6:03
relation so regarding the student enroll
6:06
for the course and this relation is
6:08
having the three attributes student ID
6:11
course and the fee course fee now you
6:15
can observe the functional dependencies
6:18
so
6:19
student ID
6:22
tends to
6:23
course
6:25
so is it a valid functional dependency
6:29
you can see
6:30
so there is a unique student ID so with
6:34
the help of student ID we can get both
6:36
course and fee okay once again I'm
6:39
repeating so there is no repetition of
6:41
student ID so one zero one two three
6:43
four five six seven eight so it is a
6:45
unit so obviously by knowing student ID
6:48
we can get all the details of remaining
6:50
columns so this is the one functional
6:54
dependency so student ID
6:57
determines both the course and fee
7:01
and similarly obviously student ID tends
7:05
to course
7:07
and student ID
7:09
tends to
7:11
feel
7:13
so obviously the remaining with the
7:16
combination of this one student ID comma
7:18
course will give the fee
7:22
student ID comma fee will give the
7:26
course
7:28
we'll give the course
7:30
right now
7:33
so obviously this will be the candid key
7:35
and also Super Key
7:37
right so candidate key is also the same
7:39
thing right Canada key is a minimal set
7:41
of super key and student ID is a Super
7:45
Key
7:46
student ID is a Super Key because by
7:49
knowing uh this super key
7:52
I mean this is a primary key okay
7:54
primary key or Super Key whatever it may
7:56
be
7:57
it's a primary key
8:00
so by knowing the student ID we can
8:02
determine both the values so this will
8:04
be the primary key so obviously the
8:07
minimal set of primary key is the
8:08
candidate key so there is only one
8:10
attribute so this is also
8:13
candidate
8:16
is also a candidate key
8:18
obviously student ID course is also a
8:21
candid key but we can't consider this
8:23
one because already the student ID is a
8:25
minimal setup for Prime uh primary key
8:27
so this will be the candidate key right
8:29
so next let us check with the one more
8:32
functional dependencies that course
8:34
determines the fee so whether we have to
8:37
check whether this functional dependency
8:38
is right or not so
8:41
according to our previous uh explanation
8:44
that means what is the functional
8:45
dependency we need to check one
8:47
condition so that is we have to
8:50
consider randomly two tuples and we have
8:52
to find the X values if both X values
8:55
are equal then automatically the
8:57
corresponding y values are also should
8:59
be equal then only we can say it as a
9:02
functionally dependent now course and
9:04
fee we can check so there are let us
9:06
take uh see this is one
9:09
two three four five six seven and eight
9:13
so consider one and
9:16
five so consider 1 and 5 so in both
9:20
X values are
9:24
Java
9:26
Java
9:28
corresponding columns
9:31
twenty thousand
9:34
and fifth one
9:36
twenty thousand
9:38
yes both X values are equal y values are
9:41
equal so we can say this has a
9:44
functionally dependent next
9:48
let us take one more
9:50
C let us take C there is any any other
9:53
uh Tuple with the c no go to the C plus
9:57
plus yes so there is another C plus
10:00
space
10:01
then the corresponding y value should be
10:03
equal so first for the third Tuple third
10:06
to pull the Y value is fifteen thousand
10:08
and for the eighth triple the Y value is
10:11
fifty thousand yes right next go with
10:13
the python
10:15
yes there is a one more column with the
10:18
same name corresponding y values both
10:20
are equal so we can consider course as a
10:22
fee of course it can determine the fee
10:27
right and you can observe here
10:31
what we have to do so we have to check
10:34
whether there are no transitive
10:36
dependencies for the non-prime attribute
10:38
so here you can see coarse is a non-prim
10:42
attribute because the candidate keys are
10:43
having only one attribute so that will
10:45
be the prime attribute so obviously this
10:48
will be the prime attribute
10:54
so obviously the course and fears are
10:56
non-prime attributes so here we are
10:58
having a functional dependency which is
11:00
a non-prime attribute non-prime
11:03
attribute right so obviously we can say
11:06
this relation is not in 300 because here
11:12
non-prime attribute
11:14
here we are having an on Prime attribute
11:16
and say as we have said that if x tends
11:20
to Y so X should be super key or a
11:24
primary key
11:25
and Y should be
11:27
part of candidate key
11:29
part of candidate key so here you can
11:33
observe course X is course course is not
11:35
a primary key
11:37
and Y Phi Phi is not a part of candid
11:40
key so obviously there is a problem and
11:43
in other words we can say see
11:47
with the help of student ID
11:49
we can get a course we can determine the
11:52
course
11:54
help of course we can determine Phi
11:57
which is a transition
12:01
into dependency
12:04
you can observe from student ID we can
12:07
determine the course and with the course
12:09
we can determine the fee and course is
12:11
not a part of candidate key so it is a
12:14
non-prime attribute right so obviously
12:17
there will be a transitive dependency so
12:19
we have to remove this transitive
12:21
dependency so we have to decompose this
12:24
table so that
12:26
this particular Foundation becomes false
12:29
there will be no transitive dependency
12:31
now how we can
12:34
decompose the first table will be
12:37
consists of two attributes one is a
12:39
student ID and a course
12:43
and the second table
12:47
with the course
12:49
and fee
12:51
so automatically there will be no
12:52
transitive dependency let us
12:55
write down this one okay hope you
12:58
understood this one so write down all
12:59
the functional dependencies I find find
13:01
out whether any functional dependent is
13:03
having the x value as a non-prime
13:06
attribute
13:07
now
13:09
after decomposing
13:13
we have to avoid that one so after
13:16
decomposing the student ID and course
13:19
will be the one table and a course and a
13:22
fee will be another table so one zero
13:25
one one zero two
13:27
three
13:28
four five six seven and eight so the
13:34
course will be
13:37
Java C C plus plus python
13:44
Java
13:46
python
13:48
Java and sequence plus so this is one
13:51
table
13:52
this is one table
13:58
right and the second table so this is a
14:01
table one
14:03
second double quotes So write down Java
14:07
C
14:08
plus plus
14:11
and python so for Java
14:13
the fee
14:15
twenty thousand see it is uh ten
14:18
thousand C plus plus it is a fifteen
14:21
thousand and python it is at twenty five
14:23
thousand
14:24
so this is a one more table
14:27
here you can observe there is no
14:29
redundancy in this table
14:31
so previously we are having some eight
14:34
tuples but here in the table one we are
14:36
having eight tuples and table two we are
14:37
having only four through points
14:40
so you can say this relation is not in 3
14:44
and F and this is in 300 because there
14:47
is no transdum dependencies
14:50
right so this is how we we have to
14:53
convert the non
14:56
third normal form to third normal form
15:00
right so once again I'm repeating so
15:02
third normal form in order to say the
15:04
relation is in 3 and F so we have to
15:07
check for two conditions one is whether
15:08
the whether the relation is in 2nf and
15:12
the relation doesn't have any non-prime
15:16
attributes as a transitive dependencies
15:19
right so I will post the complete
15:22
playlist in the description section so
15:24
just go through and understand what is
15:26
the functional dependencies and what are
15:28
the types of functional dependencies and
15:30
what is a one and F what is a two NF and
15:32
then this particular video will be very
15:35
clear for you
15:37
right so let's stop here and if you are
15:40
having any doubts regarding this one
15:41
feel free to post your doubts in the
15:42
comment section definitely will try to
15:44
clarify all your doubts and if you
15:46
really enjoyed my session like my
15:48
session share my session with your
15:49
friends and don't forget to subscribe to
15:51
our Channel thanks for watching thank
15:54
you very much