0:02
[Music]
0:06
thank you
0:09
[Music]
0:12
Hello friends welcome back to our
0:13
channel so in the previous session we
0:15
have discussed about how to find the
0:18
candid keys and the super Keys using the
0:20
functional dependencies and the closures
0:22
so also we have seen a few examples in
0:24
the previous session and in this session
0:26
also we'll see two more examples of
0:29
finding the super keys in the candidate
0:31
keys
0:32
by using the functional dependencies and
0:34
attribute closures right so let us take
0:37
this relation first so a relation with
0:39
the five attributes a b c d and e with a
0:43
following the functional dependencies a
0:44
b tends to C C tends to D and B tends to
0:47
E now first let us find out the super
0:51
key so a Super Key is the closure of
0:56
set of attributes determines all the
0:59
attributes of relation that we call it
1:01
as a super T now
1:03
the possibility a
1:06
B C D and E
1:10
the closure of this one
1:12
is obviously a b c d and e
1:18
so this is one super key
1:20
this is it
1:22
Super K
1:23
because the closure of a b c d e the set
1:26
of this attribute closure will can
1:29
determine all the attributes now from
1:32
the functional dependencies if you know
1:34
a b if you know the attributes A and B
1:37
we can determine the attribute C so here
1:40
we can simply eliminate this one so a b
1:45
d e
1:47
write down the closure for this one so
1:49
first the self attributes can be stored
1:52
in the resultant set so a b d and e and
1:57
if you know A and B so this is a
1:58
resultant set and in this resultant set
2:00
we are having a and b so if you know A
2:02
and B we can get a c from the functional
2:04
dependency one so we can also determine
2:07
the C so which is also all the five
2:10
attributes right right so this will also
2:12
be the Super Key
2:14
then from the second functional
2:16
dependence if you know C we can remove D
2:19
I mean we can determine D so if you know
2:21
C we can determine D but here we don't
2:23
have the C right coming to the next one
2:26
if you know B we can determine e so we
2:28
can simply remove e now a b d the
2:34
closure of a b d will be so first the
2:37
self uh insertion a comma B comma D and
2:41
if you know a b we can get a c from the
2:44
first dependency and if you know C we
2:46
can get a d so we have C in our
2:48
resultant right so we can determine D
2:50
sorry
2:51
the t is already available okay if you
2:54
don't see we can available D is
2:56
available if you know B we can get a d e
2:58
if we have we are having B and we can
3:01
determine e so here also we can get all
3:03
the five attributes for this particular
3:06
closure so this is also a super name
3:11
now
3:13
is there any chance to reduce this uh
3:15
super key so according to the functional
3:18
dependency if you know a b we can get C
3:20
so there is no C here if you know C we
3:22
can determine D so here no C if you know
3:25
B we can determine e so here also there
3:28
is no B so this is the last one
3:31
but we can't say that ABD is itself is a
3:35
candid key so we have to find out all
3:37
the combinations of this one so we have
3:39
to find out the combinations of a b
3:41
closure
3:44
similarly b d closure
3:47
similarly a d closure
3:51
and also a closure
3:55
B closure
3:58
declosure so first let us assume that so
4:02
A B closure so a comma B will be the
4:04
attributes which can determine with the
4:06
help of a b attributes and if you know a
4:09
b we can get a c yes C if you know C we
4:13
can get a d yes we can get D now if you
4:17
know B we can get yes we can get e
4:20
so you can answer a b closure can
4:23
determine all the attributes all the
4:26
attributes so this will also be the
4:29
Super Key
4:30
this will also be the Super Key now b d
4:33
plus so b d closer of b d so B we can
4:37
determine B and we can determine D if
4:38
you're not B we can determine e
4:40
and any other
4:43
attributes we can determine no so this
4:46
is so only three attributes we can't
4:48
determine all the five so this is not a
4:49
Super Key
4:51
coming to a deep closure so we can
4:53
determine a and d by using a d plus
4:55
right A D closure so if you know a
4:59
no nothing happens if you're not D
5:01
nothing happens so
5:04
this is the only closure and this is
5:05
also not a Super K and coming to the a
5:08
plus so a can be determined by using a
5:11
and then if you know a any other
5:13
attribute no so this is the only
5:16
attribute we can determine so this is
5:18
also not a Super Key coming to the B if
5:20
you know B we can determine B if you
5:22
know B we can determine e if you know B
5:25
and E any other chances no so this is
5:28
also not a Super Key and D plus if you
5:31
know D we can determine D and if you
5:34
know D any other attribute no we can't
5:36
determine any other attribute with only
5:38
D so this is also not a super key so now
5:41
the super case
5:46
super case for this relation are
5:49
so a b c d e
5:56
a b d
6:03
e
6:07
a b d
6:10
comma
6:12
a b
6:14
right these are the super keys so
6:16
candidate key means we we have studied
6:18
that candidate key is nothing but a
6:20
minimal set of super key it's a minimal
6:22
set of super key so what is the minimal
6:24
set of this super key A B a b so
6:27
candidate key
6:31
candidate key is a minimal set of Super
6:33
Key
6:37
minimal set of
6:40
Super Key
6:43
so a b is a candid key
6:47
so we can't tell that a b itself is only
6:51
candidate key for this given relation so
6:53
from this what we have to do we have to
6:56
find out the prime attributes
6:59
Prime attributes so what are the prime
7:01
attributes so the attributes which are
7:04
involved in the candidate key are the
7:05
primary groups so here a comma B are the
7:08
prime attributes so what we have to do
7:10
the next so we have to check whether any
7:13
one of the Prime attribute is on rhs of
7:17
any one of the functional dependency
7:19
so A and B is there any functional
7:22
dependency with rhs of A and B no so no
7:28
not in
7:30
rhs
7:31
of any functional dependency
7:35
not in rhs of any functional dependency
7:37
so simply we can say a b
7:41
is only
7:43
3D
7:45
candidate
7:47
t a b is only candidate k right so first
7:52
we need to find the closures and after
7:54
that we have to find out all the super
7:55
Keys we have to identify all the super
7:57
keys from that we have to finalize the
8:00
candidate key and we we can't say that
8:02
that's the only candid key so we from
8:04
that particular candidate key we have to
8:06
find out all the prime attributes and
8:08
check whether any one of the Prime
8:09
attribute is a on rhs of any one of the
8:12
functional dependency if so just replace
8:15
that one with the corresponding LHS and
8:17
follow the same procedure and find out
8:19
again the candid key right but here
8:22
the prime attributes are A and B which
8:24
which are not in rhs of any functional
8:27
dependency rhs means this one so this is
8:30
the rhs so in the in all three
8:33
functional dependencies the rhs is not
8:35
equal to either A or B so obviously we
8:38
can say a b is the only candidate key
8:40
for the given relation a b c d e with
8:43
given functional dependencies
8:45
right so hope you're understood this one
8:47
so let us check one more problem on this
8:50
one so finding of super piece in candid
8:52
case
8:54
so let us take one more example so let
8:57
us find out the super keys and the
8:58
candid key for this particular relation
9:01
with the given functional dependencies
9:03
first let us find out the closure so a b
9:07
c d e h so this is a closure so which
9:12
can find the attributes a comma B comma
9:16
c d e and H so all the attributes all
9:20
the attributes so obviously this will be
9:22
the Super Key this will be the Super
9:25
King right next from the functional
9:28
dependencies you can see if you know a
9:30
we can
9:31
get the D okay if you know a we can get
9:34
the D so we can remove this
9:38
sorry B not a b it's a b
9:43
okay if you know here we can determine B
9:45
so
9:47
c d e and H
9:51
closure is a c
9:56
d e h and if you know here we can get a
10:00
B so we can add B here
10:02
right so from the next functional
10:05
dependency if you know BC we can get D
10:06
but here we are not having any B okay go
10:09
to the next one if you know e we can
10:11
determine C if you know e we can
10:13
determine C we can remove this one so a
10:16
d e h
10:19
closure
10:21
a
10:22
d e h and if you know e
10:28
so if you know here we can get a B
10:31
if you know e you can get C so all the
10:34
attributes have been done right so this
10:36
is also a Super K and this is also a
10:38
Super Key and for next functional
10:40
dependence if you know D we can get a if
10:43
you know D we can get a so you can
10:45
simply remove d a okay so b e n h
10:52
closure D comma e comma H and if you
10:57
know D we can get a so we can get a if
10:59
you know a we can get a B so we can get
11:02
a B if you know e we can get a c so we
11:04
can get a c so this is also a Super Key
11:07
this is also a Super Key so check
11:10
whether still we can reduce the super
11:13
key so from the functional dependencies
11:15
we can't reduce this one so write down
11:17
all the combinations here so we can't
11:19
say this is the minimum Super K among
11:21
all these four so this will be the
11:22
candidate key so because we have to find
11:24
out all the following I mean all the
11:27
possibilities right right so d e closure
11:31
so D it will can determine D comma e and
11:35
if you know D we can get a
11:38
and if you know e we can get a c
11:41
and here if you know here we can get a B
11:45
okay if you know b c we can get
11:49
okay if you know BC we can get d yes D
11:53
so a b c d e only this one so but it
11:57
can't ideally determine H okay the H
12:00
can't be determined here so this is not
12:02
a supernatural
12:04
next e h
12:07
e h
12:09
so we can determine e comma h
12:13
from here we can determine e comma H and
12:16
if you know e we can get a c
12:19
and if you know h
12:21
so no other combination right so only
12:23
the three so this is also not a super
12:25
key because this closure doesn't
12:27
determine all the attributes and here
12:30
next one d h
12:32
find out the closure of d h
12:34
so d h we can determine D and H if you
12:38
know D we can determine a if you know a
12:40
we can determine B
12:45
any other attribute we can determine no
12:48
right so this is also not a Super Key
12:51
now individually you have to find
12:52
whether single crochets so declosure
12:57
so we can determine D and with the D we
12:59
can determine a with a we can determine
13:02
B that's it there is no other
13:04
combination there I mean there is no uh
13:07
or no other attribute we can determine
13:08
by using d a b so if you want to
13:11
determine C we can we need to get a e
13:13
right there is no chance so this is also
13:16
not a Super Key and coming to the E
13:18
attribute so by using the E so we can
13:22
get e and if you know e we can get a c
13:25
that's it only two attributes so this is
13:27
also not a Super Key next h
13:30
H so there is no functional dependency
13:33
with the attribute H so only H can be
13:36
determined so this is also not a Super
13:38
Key now
13:41
the sofa key what are the super keys
13:43
from this
13:44
one right so the super keys are
13:50
a b c d e and H
13:55
okay next
13:59
a
14:01
c d
14:03
e and H
14:05
next
14:07
[Music]
14:11
d e and H so these are the supervised
14:15
these are the super case so from this
14:18
the candidate key
14:21
candidate key
14:24
is d e h because this is the minimal set
14:27
okay among that one this is the minimal
14:30
set because this is having only three
14:31
attributes this one four attributes this
14:33
one five attributes and this one six
14:34
attributes so the minimal set of surface
14:36
is d h so DH so whether we can decide
14:41
that d h is the only candidate key for
14:43
this given relation no so we have to
14:46
find out the prime attributes so what
14:47
are the prime attributes so this Pi
14:49
Prime attributes are the attributes
14:51
which are involved in the candidate key
14:53
so Prime attributes are d e and H what
14:57
we have to do we have to check whether
14:59
any one of the prime prime attribute
15:01
resides on rhs of any one of the
15:03
functional dependency so you can see d e
15:06
h d e h so this one
15:10
so BC tends to D is having
15:14
rhs
15:16
okay time Prime attribute as rhs so
15:19
Prime attribute as rhs so what we have
15:22
to do we have to replace so what we have
15:26
to do replace
15:32
okay so Prime attribute with
15:36
LHS with LHS
15:40
so what is a candid key d e h so we need
15:46
to replace this D
15:47
we need to replace this D with the
15:49
corresponding LHS LHS means this one so
15:52
a sorry
15:55
this one becomes
15:57
BC
15:58
e h
16:01
b c e h we are replacing d with
16:03
corresponding LHS so b c e h just find
16:08
out the closure for this one
16:11
so we can determine B we can determine C
16:13
we can determine e we can determine H
16:16
and if you know b c we can determine d
16:19
and if you're not D we can determine a
16:22
so
16:25
is it everything fine a b c d e h right
16:31
so this is also a Super Key this is also
16:34
a Super K right so we can add here
16:37
so B C E and A
16:40
what happens again reduce this one so we
16:42
have to repeat the same process so
16:44
reduce this one
16:45
so from here
16:50
anything we can reduce
16:52
so if you know e we can remove C if you
16:56
know e we can we can we can determine C
16:59
so C can be removed so simply we can use
17:02
b e and H so find out the closure for
17:06
this one and this is a Super Key okay
17:07
this one is a Super Key
17:10
b e h so go with a b e and H and if you
17:15
know e we can get a c
17:17
so now the resultant set we are having B
17:19
and C with the help of BC we can get a d
17:21
and if you know D we can get a so here
17:25
so this is also a Super Key
17:29
so
17:30
b e h is also a Super Key
17:34
again is there any chance of reducing
17:37
further of this particular beh
17:40
no now again find out the combinations
17:43
be
17:45
so right on the
17:47
this one b e so we can determine B comma
17:50
e and if you know e we can get a c if
17:53
you know b c we can get a d
17:57
which you know D we can get a
18:01
right what about H there is no h so
18:04
simply we can say this is not a Super K
18:06
and next one e and H
18:08
so find out the closure of e and H E and
18:12
H
18:13
and now if you know e we can get a c
18:15
and any other
18:18
attribute we can determine no so this is
18:21
also not a Super K next
18:23
b h
18:26
B and H so determine B and H and if you
18:30
know B and H no other attribute can be
18:33
determined so this is also not a Super K
18:35
now find out the closest for single
18:37
attribute so B find out the attribute of
18:41
B so we can find a b and if you know B
18:44
so no other attribute can be determined
18:46
so this is also not a Super Key next e
18:51
we can determine e and if you know e we
18:53
can get C only two attributes can be
18:55
determined for by using e so that's also
18:58
not a Super Key and H so there is no
19:01
functional dependency of H so only H can
19:04
be determined by using H so this is also
19:06
not a Super K
19:07
now what are the super Keys now this
19:11
so already we have seen the DH is a
19:14
candidate we have repeated the procedure
19:17
now what is the another uh candidate key
19:19
so this is also a Kennedy because b e h
19:22
is also a Super Key and it is also a
19:25
minimal set of super key because here we
19:27
are having three attributes and here
19:28
also we are having three attributes so
19:30
the second one is bgh
19:33
so shall we can conclude that there are
19:35
two candid Keys No so again we have to
19:38
repeat the same process again we have to
19:39
find out the prime attributes and again
19:42
you have to repeat the same process so
19:48
so I'll erase this one I will write this
19:51
one so here I'll write this is a Super
19:53
Key and this is also a Super Key
19:57
now
19:59
so here the prime attributes
20:02
what are the prime attributes
20:06
B
20:08
and H
20:10
b e n h now what we have to do so we
20:14
have to check whether any one of the
20:15
Prime attributes are in the rhs of any
20:18
one of the functional dependence
20:20
so b e h so you can observe here the
20:23
first functional dependency yes so a
20:26
tends to B
20:28
so here the prime attribute as rhs
20:32
so what we have to do replace Prime
20:34
attribute with LHS so we need to replace
20:38
Prime attribute
20:40
with
20:42
LHS so what will what is the prime
20:44
minister key so b e h
20:48
becomes Prime attribute a e h so we have
20:52
replaced B with corresponding LHS so now
20:55
again find out a e h
20:58
closure
21:00
a e h closure so from a h we can find a
21:04
e and H so if you know a we can get a B
21:08
so we can get a B here if you know B
21:12
uh if you know e we can get a c
21:15
if you know b c we can get a d
21:19
so you can observe all the six
21:20
attributes have been determined by using
21:23
so this is a Super Key so we can add
21:27
here let's say a e and H
21:31
right so
21:33
any any other chances to reduce this ah
21:38
candid Key by using any functional
21:40
dependency if you know e we can
21:42
determine C but C is not here if you
21:44
know a we can determine B but B is not
21:46
here so there is no functional
21:48
dependency of uh on H attribute right
21:51
now just repeat the process by writing
21:54
all the possibilities all the
21:55
combinations so find out the AE closure
21:59
so by using AE we can determine a comma
22:02
e and if you know a we can determine B
22:04
and if you know e we can determine C and
22:07
if you know b c we can determine D what
22:10
about H so there is no possibility so
22:12
this is not a super B
22:14
this is not a simple way then e and H
22:18
A and H so we can determine e value and
22:21
HRW
22:22
and if you know e we can determine C so
22:25
directly we can determine C so any other
22:28
attribute we can determine no so this is
22:31
also not a simple next a h
22:34
a h so we can determine a and H
22:39
and if you know yes we can determine B
22:41
so no other attribute can be determined
22:43
by using the for given functional
22:45
dependencies so this is also not a
22:47
common Super Key and find out the single
22:50
attribute closures a plus
22:53
so coming to the a plus we can determine
22:56
a and if you know a we can determine B
22:59
only two attributes we can determine but
23:01
this is also not a Super Key next
23:04
closure so by using e closure we can
23:07
determine e and only C
23:09
so here we have done e e and C so this
23:12
is also not a Super Key and H H closer
23:15
also we can determine only H other than
23:17
H go other attribute can be determined
23:19
so this is also not a super p so we can
23:22
simply say a a h is also a candid key
23:27
a e h is also a candid key
23:31
right so now
23:34
you can observe so shall we decide that
23:37
all the candidate keys are d e h b h and
23:39
a h no again we have to find out the
23:42
prime attributes so what are the prime
23:45
attributes here
23:46
so Prime attributes
23:52
so check whether any one of the Prime
23:55
attribute is a can is the on the rhs of
23:59
any functional dependency yes here it is
24:01
so
24:02
B tends to a so here it is a prime
24:06
attribute as rhs so what we have to do
24:10
replace
24:12
Prime attribute with corresponding LHS
24:16
so
24:17
a e h
24:21
becomes a l h i say d so d e h and shall
24:27
we repeat the same process it's not
24:29
required because d e h is already a
24:32
candid key
24:33
d e h is already a candid key so we need
24:36
not repeat the process we can stop here
24:38
so
24:40
the super case for this particular
24:42
relation are this one
24:43
so one two three four five six seven
24:46
super keys and among that one the
24:48
minimal set of super key is a candid key
24:50
so the minimal set of super keys are d a
24:53
h b h
24:56
so these are the candid keys of given
25:00
relation so first we need to find out
25:02
the one candidate key and then we have
25:05
to find out the prime attributes and we
25:06
have to check whether the prime
25:07
attributes are resides on the rhs of any
25:10
one of the functional dependency if so
25:12
replace the prime attribute plays with a
25:15
corresponding LHS and again repeat the
25:18
same process
25:19
right so until you've you got the
25:22
repeated candidate key so here we got a
25:25
DEH which is already a candid key so we
25:28
can stop the procedure here so this is
25:31
how we can find the super case and the
25:33
candid keys by using the functional
25:35
dependencies and the attribute closure
25:38
right so hope you understood this
25:39
procedure so if you are having any
25:41
doubts regarding this procedure feel
25:43
free to post your thoughts in the
25:44
comment section definitely I'll try to
25:45
clarify all your thoughts and if you
25:47
really enjoyed my session like my
25:49
session share my session with your
25:51
friends and don't forget to subscribe to
25:52
our Channel thanks for watching thank
25:55
you very much