0:01
Hello friends welcome back to our channel so in today's session we'll discuss about how to find the super case
0:07
and the candid keys from the for the given relation with a functional dependencies
0:13
right so in the previous session we have discussed about the closure how to calculate the closure for an attribute
0:19
or a set of attributes so now we need to find so finding
0:28
super case and
0:35
candidate keys so if you find the Super K obviously
0:40
we'll get the candid key because the candid keys are the minimal set of super keys so first of all if we find the
0:46
number of Super Keys available for the relation from that information we can simply get the candidate Keys what are
0:53
the different candidate Keys available right so first what is the Super Key how
0:58
we can find the super key from the functional dependencies so
1:04
super key when we call it as a Super Key Super Key means if
1:09
a closed so which we have discussed in the previous session closure I will post the
1:14
link in the description so just go through that so how to find the closure so if closure of
1:20
attributes attribute or set of attributes
1:28
if the closure of attribute or set of attributes
1:35
determines all the attributes of relation
1:42
all the attributes of relation
1:49
then we can call it as a super king then we can call it as a super glue so for example if a relation is having the
1:56
variables I mean attributes a comma B comma C okay and there are some functional
2:02
dependencies and if the closer a b a b so we can also find out the closure
2:08
a b right so if you find the closure of a b which can determine all the
2:14
attributes of a relation all the attributes of a relation then simply we
2:19
can say this a b as a Super Key simply we can say a b as a Super K so
2:26
for that what we have to do we have to find out the closure for all possibilities so for example if you go
2:32
with the three attributes we need to find the closure for a sorry a b c next a b
2:42
C AC so we need to find the closure for all
2:49
the possibilities we need to find the closure for all the possibilities so that if any set of attribute the closure
2:58
for any set of attribute determines all the three attributes of the given relation that will be considered as a
3:04
Super Key and from that the minimal set of super key so we can sort out all the super keys and from
3:13
that super case we can find out the minimal set of super Keys okay for
3:18
example if this is a Super Key because all the three attributes will
3:24
determine the three attributes and if this is also a Super Key
3:29
and this is also a Super King then the number of Super keys will be
3:35
ABC a b b okay so I I have not given any
3:40
functional dependency just I am saying the example so with the help of functional dependencies we are going to
3:46
find out the closures and if the closure contains all these three attributes then we can call it as a Super Key so
3:53
let us take the Super keys are ABC okay then a b
4:00
then B how can we say because a b c closure is determining all the three
4:09
attributes a b closer determines all the three attributes so closure will be
4:16
dependent on the functional dependencies right so be closure also can determine
4:21
both the three values so these three will be called as a super Cube now what
4:27
will be the candidate key what will be the candidate key so obviously the minimal set of super key
4:33
so the minimal set of these attributes is this one
4:39
so B will be the candidate key B will be the candid key so one relation can have
4:46
a single candid key or a multiple candidates okay one candidate key or multiple
4:54
candidate keys so now the first step what is the first step we have to
4:59
calculate means we have to find out the super keys we have to find out the super case the
5:06
second step we need to find the minimal set of super key that's a candidate key
5:12
and the third step we have to check whether the relation is having only one candidate key or a
5:18
multiple so if so we have to find out the other candidate keys so that we will discuss
5:26
now so how to find the super key with the help of a functional dependencies so here we are having only three attributes
5:33
so it would be very easy for calculating all the combinations so if our relation is having more than
5:39
three attributes for example so d e so there are five attributes then the
5:46
combinations will be very more right so in such case how to find the
5:51
super key so here itself will minimize the super key right right so that will
5:56
explain just now so let us take one example with a more number of attributes not the simple attributes with a more
6:02
number of attributes let us take r a relation with the attributes a B c d and
6:11
e there are five attributes so we need to find the super key for all for this particular relation so for that we have
6:18
to find the closure for all the combinations so first we need to find out the combination for five attributes
6:25
next four attribute combination three attribute combination two attribute combination and one attribute
6:30
combination so there will be much more accommodations will be there so
6:36
let us take the functional dependencies the functional dependencies are let us
6:41
assume a tends to B and C tends to be and D tends to e so
6:48
these are the functional dependencies these are the functional dependencies now we need to find the super case so
6:55
one Super K for any relation is uh getting the closure of all the attributes so I will write that one a
7:02
[Music] so the closure of all the attributes
7:08
will be the Super Key obviously the Super Key because
7:14
the closure of a will be having a as in the resultant cell closure of B will be
7:19
having b as a resultant set right the self attribute will be added in the resultant set so a b c d e can determine
7:27
a comma B comma C comma D so obviously this will be the Super key because this
7:34
closure determines all the attributes of a relation so these are the attributes
7:40
right so this is a Super Key now from the functional dependencies you can see
7:47
if you know the value of J we can get b okay so in the previous session we have
7:54
discussed about the closures how to find the closure from the functional dependency so the link will be posted in
8:00
the description so just go through that okay so if you know a we can determine B
8:05
so in the here in this Super K see both A and B are existed
8:11
so if you know here we can get a B here so simply you can remove this B
8:17
right so I will write here the super keys so one super key is a b c d e this
8:24
is one Superior now a c d e the closure for this one a c d e
8:34
so write down the self a c d e now if
8:39
you know a from the functional dependence if you know a we can determine B so obviously we can get a b
8:46
and if you know C we can get a d so already D is available if you know D we
8:51
can get e already e is available here so so this is also a Super K because a c d
8:57
e determines all the attributes of relation so a b c d e so all the
9:03
attributes are there so obviously this is also a Super Key so we can't say this is a candid key so still we can minimize
9:11
so we have to check whether we can minimize the super key right so the next one a c d e is also a Super T is also a
9:21
superview then from the functional dependencies if you know here we can get a B so we have
9:27
remote B and if you know C we can determine d if you know C we can determine D but
9:33
here both are there C and D so you can remove D here
9:39
because if you know C we can remove the okay so a c e the closure of a c e find
9:48
out this one so the self attributes a comma C comma e and if you know a we can
9:54
get a B from the first functional dependency and if you know C we can get a D from the second functional dependency so you
10:01
can observe here all the five attributes are there so by using a c e we can get
10:06
all the five attributes so obviously this will be the Super Key is also a Super Key
10:12
but we can't decide this is a only super key still we can
10:18
try to reduce the super key right so here if you know D we can reduce e we can
10:27
determine e but here we know we don't have a d okay we have a here so that we
10:33
can determine B we have a c we can determine D and here uh we don't have D
10:38
so up to here we we can't minimize this one okay we can't minimize this one
10:43
right but we can't say that this is a candid key we can't say that this is a candidate
10:49
key because the combinations can also determine all the attributes so try to
10:57
find out the closure for all the combinations now we have reduced so previously we are having around five
11:03
attributes here so the combinations will be more okay so here now we are checking the
11:10
combinations for three attributes so let us take the three so three attributes we got a Super
11:15
Key now two attributes write down the closure for two attributes AC Plus
11:21
C plus and a e plus
11:30
right so if you know J we can get a B so first
11:35
let us write the self attributes a comma C if you know a we can write a b
11:41
if you know C we can get a d now the resultants that we are having a
11:47
c b d so among that what we can get
11:53
so so we are having D okay we are having D
11:59
so we can get e
12:06
right so this is also a super View this is also a Super K so a c is also a
12:13
Super K because by knowing a AC self uh attributes we have to add to the result
12:19
and say if you know a we can get a B if you know C we can get a d
12:25
and if you are not D because we have to check with the resultant now we are having D here so we if you know d e we
12:31
can also retrieve e right so this is also Super K because all the five attributes have
12:38
uh determined by using this AC now coming to the C so c and e if you know C we can get a d
12:48
and if you know D we can get a e so we are now in the resultant set we are having c e d so any other attribute
12:55
we can find out b or a no so this c e closure can get only the three
13:03
attributes so this is not a Super Key this is not a super Q and coming to the
13:10
next one a e a l e
13:15
so here the self attributes a e then C by knowing here we can get a B
13:25
now we are having a e b so no other attribute can be found here
13:30
so this is also not a super clear so you can observe
13:36
here a c e is again minimized to AC so AC is also the super key so these are
13:42
the super keys super keys
13:47
right so only this one is a Super Key
13:53
right so we can't say that so this is a candid key is it a candid key we can't
13:58
say that because we have to find the closures for single attribute also so till now we have discussed about the two
14:06
two attribute closure so here the three attribute closure is a Super Key in order to minimize we have
14:12
written all the combinations two attribute closures now we have to write on the closure of a
14:19
okay see
14:26
e so a closure if you know a we can determine a if you know a we can get a B
14:33
so no other attribute can be determined so this is not a super key if you know C
14:38
we get a c if you know C we can get a d if you know D we can get e so no other
14:44
attribute can be determined so this is also not a super key if you know e so only we can get a e
14:50
so this is also not a Super Key now we can decide that AC so among the super
14:56
keys this is the minimal set of Super Key among the super case this is a
15:02
minimum set of super key so once again I am repeating so first let us take the all the attributes the closure of all
15:07
the attributes so that it can determine all the attributes so which gives the Super Key now from the functional
15:14
dependencies so just try to reduce the number of attributes so up to here we
15:19
can reduce and from these attributes we can't reduce so we can't say this is the minimal set
15:26
of super key so still we can we have to check for the combinations different combinations so a c c e a e so two
15:33
attribute combinations and a single attribute combinations so among this one again we got the Super Key AC is a Super
15:41
Key because the closure of AC determines all the attributes of relation now after finding all the super
15:49
case we can say AC is a candidate key so this is the second step so first step is
15:55
finding the super case we have done and the second step is finding the candidate Keys among the super Q which is a
16:01
minimal set of Super K so among all the super case this is a minimal set so we can say AC is a candid key and the third
16:08
step how we can decide this is the only candid key whether they can be multiple candidate
16:16
keys right so we how can we say this is our only candid key so for that purpose
16:21
we need to find the prime attributes we need to find the prime attributes so
16:27
after this one after finding the candidate key we have to make sure that that's the only candid key or there are
16:34
many more candidate keys right so for that find out the prime attributes
16:41
so which one we call it as a prime attributes so these the prime attributes are the attributes which involved in the
16:48
candidate key so which involved in the OR which available
16:55
which are available in the candidate key
17:03
candidate key so are the prime attributes so here the prime the candidate key
17:17
is a AC AC or yes AC is a candidate key so the prime attributes
17:26
the prime attributes are a comma C
17:32
the prime attributes are a comma C now in order to find whether the relation is
17:40
having a multiple candidate case so check whether
17:47
to check for
17:53
more candidate keys
17:58
to check for more candidate Keys the first one
18:04
check whether
18:09
any one of Prime attributes
18:21
are on rhs of
18:27
any functional dependency
18:38
see I am removing this one
18:48
so check whether any one of the Prime attributes are on rhs of any functional dependency so here the prime attributes
18:54
are A and C so we need to check whether a or C are on r h s so this is rhs
19:01
and this is the LHS so we need to find out whether a or C are available on rhs
19:08
so if not
19:13
if not so
19:18
there is only one candidate key
19:28
there is only one candidate key so if the prime attributes are not
19:35
available on the rhs of any functional dependency simply we can say there is only one candidate key so in this
19:41
example you can observe a c are the prime attributes and none of these
19:47
attributes are on the rhs of any functional dependency so that implies we can say for this particular relation
19:54
AC is a only candidate key only candidate key there
20:01
are no multiple candidate keys so if yes if yes
20:11
if yes right if yes then
20:17
then replace
20:27
Prime attribute replace the prime attribute
20:33
in candidate key
20:38
in candidate key with corresponding
20:48
LHS of functional dependency so obviously for example here AC
20:56
right for example there is a functional dependency D tends to C okay this is a functional dependency or
21:03
D tends to a okay D tends to a
21:09
lot of State oh sorry so there is a d tends to see now AC are
21:16
the prime attributes if you see is having or I mean c is available on the rhs of this one just take the candidate
21:24
key AC and replace the prime attribute once the prime attribute here C so
21:29
replace this one with the corresponding a h s that means what is the LHS here D
21:35
so a d a d okay A D next after that what we
21:42
have to do repeat finding
21:51
Super Key and candidate key repeat finding Super K and Candy key for
21:58
the new candidate right so hope you understood this one the first step we need to find the super
22:04
keys so that's the same so we have to find out the close-up with the help of
22:09
closure we will find in the complete super keys after that we are finding the minimal set of super key that's a
22:16
candidate key so from that candidate key we are finding the prime attributes so
22:21
we have to check whether these Prime attributes resides on the rhs of any one of the functional dependencies if so
22:28
if so so we have to replace the prime attribute in the candidate key with the corresponding LHS of functional
22:34
dependency and follow the same process again find out the super key from that particular attributes and again find out
22:40
the candid key repeat the same process right and if not
22:45
simply ensure that there is only one candidate key there is only one
22:50
candidate So In our relation in our example the can the super keys
22:56
are this one the candidate key is AC the prime attributes are A and C so we have to check whether a and C available on
23:03
the rhs of any functional dependency but there are no uh a or C available in rhs
23:09
so obviously we can say that AC is the only candidate AC is the only candidate
23:16
key right so hope you understood now we'll take one more example which will obey
23:24
this one right okay see consider second example take the
23:30
relation r with the four attributes x y z and W so the functional dependencies are extends to y y times to Z J tends to
23:38
X now what we have to do we have to find out the super keys right so I'll find
23:43
out the super key for this one so I'll consider x y z w closer so which will
23:49
give all the values x y z w so this will be the Super Key
23:55
so this will be the Super Q then so from the functional dependencies you can say
24:01
if you are having X the Y can be determined so both X and Y are available in the
24:08
closure so you can just remove the Y so it is minimized x z w so which is
24:14
again a closure we can find the closure so the self attributes can be added into
24:20
the resultant set x z w and if you know X we can determine y so y so again
24:28
by using x z y closure we can determine all the attributes of a relation so
24:33
again it is a super game next if you know why we can get a z but here we are not having y if you know Z
24:40
we can get X if you know Z we can get X so
24:47
here you can remove X so z w
24:52
z w closer so it will give the self Z comma W and if you know Z we can get X
24:59
so X can be determined now X is available in the resultant side so if you know X we can get a y so y can also
25:06
be determined so all the four attributes we can be determined by using z w so again this is a Super Key
25:12
so still shall we shall we remove the any other attribute from this JW so we
25:18
can't remove this one so now we have to so we we can't say that JW is a candid
25:24
key so once again we have to minimize this one so we have to go with the
25:29
multiple combinations so apply the jet closure and check whether it forms a Super Key
25:36
so Z if you know Z we can get X and if you know X we can get Y and if
25:43
you know y we can get a z that's it so w is not here we can't determine W so this
25:48
is not a super e and coming to the W plus
25:54
so w can be determined and other than W no other attribute can be determined so this is also not a super key so here the
26:01
super keys are here the super keys are
26:10
X z w x z w
26:17
z w so these are all the super keys so among these super case which one is the
26:24
candid key the minimum set minimum set so this will be the candidate key
26:31
candidate key right so but we can't finalize that this
26:37
is the only candid key so there might be a possibility that there are many more candidates other than this one so we
26:43
have to find that one so for that what we have to do we have to check with prime attributes
26:53
Prime attributes the prime attributes are the attributes involved in the candidate key so Z and W are the primary
27:01
Z and W are the prime attributes
27:07
what we have to do we have to check whether Z or W available on the rhs so
27:13
to check for more candidate Keys check whether every one of the primary Prime attribute are on rhs of any functional
27:20
dependency so here Z and W so you can observe here yes Z is available on the
27:26
right hand side Z is available on the rhs right so
27:32
this one yes replace the prime attribute in the candid key so what's the candidate here z w so what we have to do
27:45
so yes it is in rdhs
27:50
in rhs so what we have to do so z w so Z
27:56
should be replaced with replaced with corresponding LHS so what's the
28:03
corresponding LHS y so y w
28:08
y w right Z is replacing with y y w so what we have to do again repeat finding
28:15
the super key for the category so now we have to consider this one and we have to find out the super key so again find out
28:22
the closure for y w c y
28:27
if you know y we can get sorry Y and W we can get if you know why we can get a
28:33
z so if you know Z we can get X yes so this is a Super Key this is a
28:42
Super Key so obviously JW is a candid key that fix other than ZW
28:47
is there are any other candid case that we are finding so for that we are checking whether the prime attribute is
28:53
available in the rhs if yes we are just replacing the rhs I mean the prime
29:00
attribute in the candidate key with the corresponding LHS so in this functional dependency it exists right so we have
29:08
replaced the can the prime attribute in the candid key with a corresponding LHS
29:13
y so YW so repeat the same process whatever the process we are doing repeat
29:18
the same process y w so we have done Super Key still we can
29:24
just find out whether we can minimize or not so write down all the combinations write down the closure for y
29:30
write down the close up for y so if you know why we can get uh Z if you know Z we can get
29:36
X so w can't be determined so this is not a Super Key and also W
29:42
plus is equal to so we can get only W other than w we can't determine any
29:48
other attribute so this is also not a Super Key now y w is a candidate key and
29:53
we can't uh decide y w is the only candidate key again from this we have to
29:59
find out the prime attributes what are the prime attributes
30:05
Prime attributes are Y and W so here y w is a
30:13
YW is a candidate key so Prime attributes are y comma W again
30:19
check for this one so Y and W whether they are on rhs yes Y is in rhs what we
30:26
have to do replace on rhs
30:31
so result is yes so replace so y w is replaced with
30:40
corresponding LHS that means x w x w find out repeat the same process
30:46
find out the closure for x w so if you know x w we can get X comma W
30:53
if you know X we can get a y we can if you know why we can get a z so this is a Super Key
30:59
and find out the closure for X Plus so if you know X we can get a y we can
31:04
know if you know y we can get Z so but we can't determine W so this is not a
31:10
Super K similarly I'm managing this one
31:18
similarly so W plus closure if you know w we can
31:24
get only the w we can't determine x y j so this is not the Super Key now the candid key
31:31
is a x w d X double b
31:38
so is it completed no so from this again we need to find the prime attributes
31:45
which are X and W so we need to check whether these attributes are on rhs
31:52
okay rhs yes here it satisfies replace this one so x w x is replaced with z w
32:00
so z w is already a candidate key JW is already a candidate key so you can
32:05
stop the process now we can decide the candidate keys
32:11
the candid keys for the given relation are z w after that y w after that
32:22
so these are the candid keys for the given relation so very simple process
32:28
okay so the first step we need to find out the super keys from that we have to find out the
32:34
candid key and from that candidate key we need to check whether the relation consists of a single candidate key or a
32:41
multiple candidate keys if it is a single candidate key so how we can say it is having a single
32:47
candidate key so to for check so this is a process we need to check whether any one of the Prime attributes are on rhs
32:54
of the any functional dependency so if not we can decide that is only one
32:59
candidate key if yes we have to replace the prime attribute in the candidate key with corresponding LHS of functional
33:05
dependency and repeat the same process repeat the same process identify the super keys and identify the candid key
33:11
again identify the primary books and again check whether the prime attributes are available on the rhs if so again you
33:17
have to replace the prime attribute of the candidate key with the corresponding
33:22
lhsn continue the process until you get the same candidate the candidate repeated candidate
33:29
right so this is the process to find the super case in the candidate case of any
33:36
relation with respect to the functional dependencies right so hope you understood this
33:42
process if you are having any doubt regarding this one feel free to post your doubts in the comment section definitely I'll
33:48
try to clarify all your doubts and if you really enjoyed my session like my session share my session with your
33:53
friends and don't forget to subscribe to our Channel thanks for watching thank you very much