0:02
[Music]
0:06
thank you
0:09
[Music]
0:12
Hello friends welcome back to our
0:14
channel so in today's session we'll
0:16
discuss about one more normal form that
0:19
is a 2nf second normal form
0:32
so this is a one more level of
0:34
normalization in order to avoid the red
0:36
energies and overcome the anomalies so
0:38
in the previous session we have seen
0:40
about the one enough that is the first
0:41
normal form where the relation should
0:44
not contain the multiple values for a
0:46
single attribute so if there are any
0:48
multiple values for a single attribute
0:50
just we have we have to insert a new
0:52
rows and we have to avoid the multiple
0:55
values and coming to the second normal
0:58
form the input for the second normal
1:00
form
1:02
is relation
1:05
relation
1:07
with with 1 and F
1:10
so that is the first constraint that
1:12
means first condition so if you want to
1:14
check for the 2nf so the relation should
1:18
satisfy the 1nf condition okay so this
1:21
is also pointed
1:22
as a second normal form
1:24
of 2N right and the output
1:27
the output for this one is
1:30
relation
1:33
with a 2nf is a relation that means the
1:36
relation which satisfies the conditions
1:38
of two elements right now we will see
1:40
the conditions
1:43
conditions
1:44
need to be satisfied to say the whether
1:48
the relation is in 2nf or not right so
1:51
the first condition is
1:53
the relation
1:58
should be
2:00
in one minute so first we have to check
2:04
whether the relation is in one and a
2:05
formula so if not first we have to
2:07
achieve the 1nf that means we have to
2:09
make the relation in one another
2:12
that's the first condition the second
2:14
condition
2:15
relation
2:18
should not have
2:24
partial functional dependency
2:39
partial functional dependency right so
2:42
in the previous sessions we have
2:43
discussed about the functional
2:44
dependencies and in in that video we
2:46
have discussed about the two different
2:48
types of functional dependencies that is
2:50
a fully functional dependency and a
2:52
partial functional dependency right so I
2:55
will post the link in the description so
2:57
that first go through that one and then
2:59
you you'll get a clarity about the
3:01
partial functional dependency so at a
3:04
glance I will just recall the concept so
3:07
for example if any functional dependency
3:08
in this format some X comma y tends to J
3:13
so this we call it as a dependence and
3:15
this determinants and this is the
3:17
dependent so that means we can say Z is
3:20
functionally dependent on X comma y x
3:23
comma y right so that implies by knowing
3:27
X and Y values with the help of X and Y
3:30
values we can determine the Z value from
3:32
the relation right so
3:36
if x comma y tends to Z is a functional
3:39
dependency then
3:42
if it satisfies any one of this
3:44
condition that means if you know the
3:46
value of x if you can determine Z or if
3:49
you know the value of y if you determine
3:52
Z so any one functional dependency is
3:55
true that is a partial functional
3:57
dependency
3:59
okay so here we are removing one
4:02
attribute from the determinant and still
4:04
there is a functional dependency exist
4:06
that we call it is a partial functional
4:09
dependency right so that we have
4:10
discussed in the previous sessions I
4:12
will post the description so just go
4:14
through that one so this is the thing so
4:16
if any one of these functional
4:18
dependency if any one of this one is a
4:20
in functional dependency we can say it
4:22
is a partial functional dependency so we
4:24
have to find out this one and we have to
4:27
find whether there is a partial
4:28
functional dependency or not if so we
4:31
have to remove that partial dependency
4:33
by decomposing the relation into sub
4:36
relations
4:38
okay so the release two conditions to
4:41
satisfy whether the relation is in 2 and
4:44
F so that is the first the relation
4:46
should be in one NF and then the
4:48
relation should not have the partial
4:49
functional dependencies now let us check
4:53
with a small example
4:56
see let us take this example so there
4:59
are three attributes in this particular
5:00
relation so that is a student ID course
5:03
ID course fee right so first we have to
5:06
check whether there are a multi-valued
5:10
attribute this is multiple values for
5:12
any single attribute right
5:14
so you can observe for every column we
5:17
are having only one attribute so that
5:19
implies this is in this particular
5:22
relation
5:24
is in 1 and X 100. right so we have to
5:29
check whether this relation is in 2 and
5:31
a format so for that we have to find out
5:33
the functional dependencies and we have
5:34
to find out whether there are any
5:36
partial functional dependencies so if
5:38
there are any partial functional
5:39
dependency automatically we say that the
5:41
relation is not into n so that we have
5:43
to avoid that partial functional
5:45
dependencies that means we have to
5:46
remove the partial functional
5:47
dependencies so that this relation will
5:49
become the 2nm that means we have to
5:52
decompose this relation to the sub
5:54
relation so that it will be uh into NF
5:56
right now let us check with the
5:58
functional dependencies so I will take
6:00
the functional dependencies so student
6:03
ID tends to
6:05
course ID
6:08
and student ID tends to course fee
6:15
and
6:17
course ID tends to course fee
6:22
and
6:24
student ID comma course
6:28
ID
6:30
tends to cos Phi
6:34
student ID
6:36
course fee tends to cosine
6:41
course ID
6:42
right and then core Sufi
6:46
comma course ID tends to student ID
6:51
right so these are the possible cases
6:54
now we can check whether there is a
6:56
functional dependency or not for all the
6:58
things so functional dependency means on
7:00
the left hand side the determinant and
7:03
right hand side is the dependent whether
7:05
CID is dependence on the
7:08
functions dependent on the determinant
7:11
or not that means we have uh we have
7:14
studied about this functional dependency
7:15
in the previous session so we have to
7:17
check for one constraint that we have to
7:19
consider two random tuples so that the x
7:22
value or equal if both the tuples are
7:24
having the same x value the
7:26
corresponding y values also should be
7:27
same then only we can say there is a
7:29
functional dependency so once just go
7:31
through the functional dependency
7:33
concept video right
7:35
so here let us check student ID and
7:37
course ID student ID let us take a two
7:39
two plus one zero one and one zero one
7:41
so
7:43
one zero one let us write one zero one
7:45
and one zero one two two pulls x value
7:48
is one zero one one zero one and here we
7:50
are getting
7:51
Java as a course ID and here we are
7:54
getting C plus plus as a cosine so there
7:56
is an redundancy so we can't say this is
7:58
as a functional dependent
8:00
right so considering two different X
8:03
values there should be corresponding y
8:05
values then only we can say that it is a
8:07
functional dependence so here there is
8:09
no functional dependency
8:11
so this is not a functionally dependent
8:14
now let us take this one student ID and
8:16
course V student ID again you can check
8:18
for the first open and second to pull so
8:21
here I'll write it a numbering okay so
8:23
it will be understanding so consider the
8:26
first uh triple and the third triple so
8:29
again we are having one zero one and one
8:30
zero one so corresponding course fee one
8:33
zero one course fee is
8:36
some ten thousand and one or one two
8:40
thousand so here for the same x value we
8:43
are getting different y values so this
8:45
is also not functionally dependent so we
8:47
can't say this is a functionally
8:48
dependent right cancel this one course
8:51
ID and course V so consider see first
8:54
one and fifth one
8:57
so first one and fifth one we are having
8:59
the x value as Java so what is a
9:02
corresponding y value
9:04
here at 10 000 for the first triple and
9:07
coming to the fifth triple again it is a
9:09
ten thousand yes so both are X values
9:12
both X values are same and corresponding
9:14
y values is also same consider
9:17
yes so that's the only thing we have
9:20
that is repeated in the table Java and
9:22
Java so all the remaining are unique so
9:25
we can say this as a functionally
9:27
dependent okay so this is one functional
9:29
dependent
9:31
right and now let us consider these
9:34
things
9:35
about these functionality dependents
9:38
because
9:39
we have to eliminate the partial
9:41
functional dependency so when we will
9:43
get the partial functional dependency if
9:45
the determinant is having some set of
9:47
attributes then only we can say we can
9:49
we can say the partial functional
9:51
dependency and the full functional
9:53
dependency but here the determinant is
9:55
only one attribute so we can't say it is
9:58
it has a partial or full functional
10:00
dependency okay there will be no partial
10:02
dependency it's a full functional
10:04
dependency now coming to this one
10:06
student ID and course ID
10:09
combinedly determines the course fee
10:12
okay so don't uh think it has a two
10:16
different attributes okay two different
10:18
attributes given to the same thing so
10:20
this the meaning of this particular
10:22
functional dependency means by knowing
10:24
the student ID and course ID the
10:27
combination of student ID course ID it
10:30
should build the value of codes fee let
10:32
us check so 101 student ID Java
10:37
okay one zero two python one zero one C
10:40
plus plus one zero three c one zero
10:42
three Java one zero two JavaScript so
10:44
all are different
10:45
all are different all are unique there
10:48
is no repetition so we have to consider
10:49
these two student ID and course ID so is
10:52
there any relation I mean is there any
10:54
redundancy for these two one zero one
10:57
Java is there any one zero one Java
10:58
there is no thing one zero two python no
11:01
one zero one C plus plus no one zero C
11:05
no one zero three and Java one zero two
11:07
and five JavaScript there is no Revenue
11:10
so we simply we can say this is also a
11:12
functionally dependent
11:14
this is although also a functional
11:16
independent because by knowing these two
11:18
things we can get the course V Unity and
11:21
coming to the student ID and course
11:23
field
11:24
student ID and course fee check student
11:27
ID and course fee 101 and 10 000 is
11:30
there any reputation for one zero one
11:31
and ten thousand no no one zero two and
11:35
fifteen thousand
11:36
no one zero three and one one zero three
11:40
and ten thousand yes see one zero three
11:42
and ten thousand four and five so four
11:44
and five one zero three with a ten
11:46
thousand
11:48
one zero three and with ten thousand so
11:51
you can observe the fourth triple and
11:53
fifth triple fourth triple and fifth
11:55
triple one zero three ten thousand one
11:57
zero three ten thousand so here we are
11:59
having some C and here we are having
12:00
some job so same x value gives a
12:03
different y values so we can't consider
12:06
it as a functional dependence so this is
12:08
not a functional dependence just because
12:09
strike on so this is not this is not and
12:12
this is also not a functionally
12:14
dependent
12:20
ID so ten thousand in Java yes is there
12:24
any reputation yes 10 000 in Java so
12:26
first to pull and 50 triple so first
12:29
interval from first to pull what is the
12:31
thing course fee 10 000 and down
12:36
and
12:38
next to fifth table yes
12:43
also gives the same thing 10 000 in Java
12:47
so what about the Y values both the both
12:49
X values are same what about the Y
12:51
values so y values are so ten thousand
12:54
in Java gives it one zero one student
12:56
id101 and from the fifth triple again
12:59
ten thousand in Java gives the
13:01
student ID 103 so first the same x value
13:05
there are different y values so this is
13:07
also not correct this is also not
13:09
correct I mean this is also not a
13:11
functionally dependent now so among
13:14
these two we can consider the second one
13:16
because here we are having a set of
13:19
attributes in the x value that means a
13:21
determinants
13:22
okay in the determinant I mean in the
13:25
determinant we are having a multiple
13:27
values so this is like
13:29
X comma y tends to Z say x comma y tends
13:34
to Z so what we have done so we have to
13:37
check for X tends to J and Y tends to J
13:39
whether these two are functionally
13:41
dependent or not so if any one of these
13:44
things is a functionally dependent we
13:45
can say it is a partial functional
13:47
dependent so now let us check so from
13:49
this one let us check
13:52
student ID
13:54
gives a course fee
13:56
similarly course ID gives the course fee
14:00
so if any one of these functionally this
14:04
functionally dependent if this relation
14:07
is a functionally dependent then we can
14:08
say it is a partial functional student
14:10
ID tends to core speed student ID and
14:13
course fee so 101 10 000 a here one zero
14:17
one two thousand so this is not a
14:21
functionally dependent and coming to
14:22
this one course ID tends to course V
14:24
already it is a functionally dependent
14:26
right so here we are removing one
14:29
attribute from the determinant
14:31
we are removing one attribute from the
14:33
determinant
14:34
still
14:35
it exists the functionally dependent so
14:38
obviously it will be the partial
14:39
functional dependent so here we in this
14:43
case we are checking for both the cases
14:45
that means we are removing one attribute
14:47
and we are checking for the functionally
14:49
dependent so it satisfies the functional
14:51
Independence concept right then we can
14:53
say it is a partially functional
14:55
dependent so this relation is having
14:57
a partial functional dependency so that
15:00
means this is not in 2nf this is not in
15:04
to NF so how we can get this to NF so we
15:07
have to decompose this table in two
15:11
different relations okay we have to
15:14
decompose this to two different
15:16
relations so
15:18
this is a functionally dependent so
15:20
obviously we can say it is a partially
15:22
functionally dependent partially
15:24
functionally dependent so we can simply
15:26
say not in 2 n f not into NF right so
15:33
now we have to
15:35
divide or decompose the relation to two
15:39
different relations sub relations so
15:42
what are the sub relations where we got
15:44
the ah
15:45
this one so student ID and course ID is
15:51
one table
15:52
course ID and course fee is another
15:56
table tell me to the student ID so 101
16:02
Java
16:04
102
16:08
python
16:10
101
16:12
C plus plus
16:14
one zero three
16:16
c one zero three
16:20
Java one zero two
16:23
JavaScript
16:25
so this is One Direction this is only
16:27
one relation
16:30
okay we are decomposing the complete
16:32
relation into two sub relations this is
16:34
one relation and cosine
16:37
so Java
16:40
python
16:43
C plus plus c
16:47
JavaScript so here there will be no
16:50
redundancy you can observe so we are
16:52
having some six uh tuples but here we
16:55
are having only five to pull because
16:56
Java is repeated the same course field
17:00
value same course V value so which is a
17:02
10 000 and for this one python it is a
17:05
fifteen thousand and C plus plus it is a
17:08
two thousand
17:09
and for the C it is again ten thousand
17:13
and for JavaScript it is at twenty
17:16
thousand so you can observe here some
17:20
sort of redundancy has been reduced
17:24
right so here we are having some six
17:26
columns and here we are having some five
17:28
columns I mean five rows rows
17:31
right so like this we have to decompose
17:34
the complete relation into a sub
17:36
relation so now you can say these two
17:39
relations are in 2 NF because there are
17:41
no partial dependencies so again you can
17:44
check for one and F and 2 NF for this
17:46
particular sub relations if there are
17:47
more number of attributes and again you
17:49
have to check for one and F and 2 NF so
17:51
whether it is one and if then we have to
17:53
check for 2nf right so again you have to
17:55
check for partial dependencies whether
17:57
there are any partial dependencies in
17:58
this particular sub relation or not so
18:00
if there are any partial functional
18:02
dependencies automatically we have to
18:04
remove this partial functional
18:05
dependencies that means by decomposing
18:07
further decomposing the relation into
18:08
sub relations
18:10
so this is how we we have to uh satisfy
18:14
the relation with 2nf the first one the
18:17
relation should be in 1 and F the second
18:19
one the relation should not have any
18:21
partial functional dependencies
18:23
so once again I'm I'm saying that the
18:27
just view the links of a partial I mean
18:31
functional dependency Concepts so I will
18:33
give a complete playlist in the
18:34
description section go through that so
18:36
uh listen to the functional dependency
18:39
concept what is the functional
18:40
dependency and uh what is the full full
18:43
functional dependency partial functional
18:45
dependency types of functional
18:46
dependency and then it would be very
18:48
easy for you to understand this
18:50
particular normalizations concept so
18:52
either it is one enough two n f three
18:53
and a four n of etcetera etcetera so
18:56
Pastor visit the functional dependency
18:58
make just understand the concept of
19:00
functional dependency so it will be very
19:02
easy for you right so the link will be
19:05
given in the description so go through
19:06
that one before watching this video
19:09
right so hope you understood this one
19:11
and if you are having any doubts
19:12
regarding this uh two second normal form
19:14
so feel free to post your notes in the
19:16
comment section definitely I will try to
19:17
clarify all your thoughts if you really
19:19
enjoyed my session like my session share
19:21
my session with your friends and don't
19:22
forget to subscribe to our Channel
19:23
thanks for watching thank you very much