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:16
have discussed about one normal form
0:18
that is a third normal form
0:19
and in this session we'll discuss about
0:21
one more normal form that is a b c and F
0:24
Boys called normal form
0:29
DC and India so which is called Boys
0:33
called normal form
0:39
boys
0:41
usually this bcnf is also known as a 3.5
0:44
normal formula
0:46
so it is also known as 3.5 normal Bond
0:53
so what are the conditions need to
0:55
satisfy if we want to say the relation
0:58
is in b b c enough so here are the
1:00
conditions need to be satisfied
1:03
the first one
1:05
relation
1:08
should be
1:15
in
1:17
CNF relation should be in 3 NF and the
1:21
second one
1:23
the second one
1:25
for all functional dependencies
1:30
for all functional dependencies
1:37
foreign
1:41
so X should be
1:49
primary key
1:52
primary or not so much
1:58
Super Key
2:00
or
2:02
candidate
2:04
candidity so for all the functional
2:06
dependencies Let It Be extends to Y so
2:09
it should satisfy that X should be
2:12
either super king or a candid k then we
2:15
can say that relation is in BC and F
2:19
right so first the relation should be in
2:21
3 and F so then only we have to go with
2:23
the second condition if the relation is
2:25
is in 3 NF we can go with this one okay
2:28
if any relation is in BC enough we can
2:31
say the relation is in 3 NF but if any
2:33
relation is in 3 NF we can't say that
2:35
relation is in BC and F right so this is
2:39
a
2:40
stronger than the 3nf so bcnf is
2:43
stronger than the three enough and still
2:44
uh it will reduce some sort of anomalies
2:48
and redundancies which are available in
2:50
the 3nf right so uh we'll
2:55
firstly first you have to know what is
2:57
the 3nf and how this uh
3:00
relation satisfies the 3nf so I will
3:03
completely give the link of playlist in
3:06
the description section go through that
3:07
so get an idea about functional
3:10
dependencies what is the functional
3:12
dependency and how the relation should
3:14
be entry enough and everything and then
3:16
you will understand this session very
3:20
clearly
3:21
right so let us take one example and I
3:24
will show you how we have to convert the
3:27
3nf to the bcnf okay how we can achieve
3:30
the bcnf right
3:33
so let us take this example insert this
3:36
relation so this relation consists of
3:38
three attributes that is a student
3:41
course and a tutor
3:43
right so here we are having a different
3:45
relation so first we have we have to
3:47
check whether this relation is in 3nf
3:49
format so if it is in 3nf then only we
3:52
have to proceed with the bcmf so what is
3:54
the 3nf so they should not be transitive
3:57
dependencies for the non-prime
3:59
attributes so first we need to find the
4:01
non-prime attributes for this particular
4:03
relation and in order to in order to
4:05
find the non-frame attributes we have to
4:07
find the prime attributes in order to
4:08
find the prime attributes so we have to
4:10
find the candidate keys of this relation
4:13
so for finding the candidates is first
4:15
we have to write down the functional
4:17
dependencies because even though if you
4:19
want to check for BC and if we need to
4:21
know the functional dependencies so
4:23
first let us see the functional
4:25
dependencies let us write on all the
4:27
possibility possible dependencies and
4:29
we'll check whether which are the valid
4:31
functional dependencies and which are
4:33
not a valid functional dependencies so I
4:35
will represent this student as yes
4:37
course as C and and shooter as a tea
4:42
right so first let us write down the
4:45
functional dependencies so one
4:47
possibility s tends to C
4:50
s tends to T that means student course
4:54
student tutor
4:56
and also coarse tutor and one more one
5:01
yes student come a course tends to tutor
5:05
student comma tutor gives the course and
5:10
course and tutor gives the student so
5:14
these are the possibilities
5:15
first we will check whether which are
5:17
the valid functional dependencies so
5:19
these are the possible functional
5:20
dependencies first coming to the student
5:23
to course according to our definition of
5:26
functional dependency consider two
5:28
random tuples and if there is a common x
5:32
value the corresponding y values should
5:34
also be same then only we can say it as
5:36
a functional dependency so student can
5:39
determine the course so student can
5:41
determine the course consider the first
5:44
one and second one one zero one it gives
5:46
a Java and here one zero one gives a
5:49
python so one student we are having a
5:52
multiple courses so we can't consider
5:54
this one so x value is same that y value
5:57
is different x value is same this is x
5:59
value this is y value x value is same
6:02
for two tuples but we are having a
6:04
different y values so this is not a
6:06
valid functional dependency coming to
6:08
the student tutor here also you can see
6:11
x value student and Y value Total 1 0 1
6:14
Sandeep and one zero one solving so this
6:16
is also not possible this is also not
6:20
possible so cos tends to tutor
6:24
so Java Sandeep
6:27
Java Sandeep yes
6:30
but what about the thing
6:33
python Sardi and python so this is also
6:36
not possible
6:37
okay let us assume with the T tends to C
6:40
okay so here also you can see C tends to
6:43
yes T tends to yes so if you know cos we
6:47
can get a student details so for Java
6:49
101 Java one or two this is not possible
6:53
tutor tends to student Twitter Sandeep
6:56
one zero one that's Sunday one zero two
6:58
this is also not possible and total two
7:01
course
7:06
python so there is different X values
7:09
but the same y values that's not no
7:11
problem okay for different extra
7:13
different X values we can get the same y
7:15
values but for the same X values we need
7:17
to get the same y value so this is
7:20
possible this is a valid
7:22
functional dependency is a valid
7:25
functional dependency so coming to here
7:28
student enforce give a tutor student and
7:31
course one-on-one Java so is there any
7:33
repetition for this one one or one Java
7:35
no so one or two one or one python there
7:38
is no limitation one or two Javas no
7:40
repetition one or two python no
7:41
repetition there is no repetition on x
7:43
value so obviously it will be functional
7:46
dependent next s tends to T gives us C
7:49
that means a student along with the
7:51
tutor gives a course student along with
7:53
the tutor one zero one Sandeep is there
7:55
any 101 something here one zero two
7:58
Sunday so that's different one zero one
8:00
sarathi so there is here one zero one
8:01
something this is a different so there
8:04
is no duplicates so this is also
8:06
possible
8:07
coming to the course and tutor gives the
8:09
student course and tutor gives the
8:11
student so course and tutor Javas and if
8:14
you're also Java
8:16
okay so course and tutor
8:19
Java Sandeep and Java both we are having
8:22
a duplicates but here y values are one
8:24
or one and one or two both are different
8:26
so this doesn't forms the functional
8:28
dependency so here's some value
8:30
functional dependencies are so s comma C
8:34
that means a student along with the
8:36
course can determine the tutor and the
8:40
student along with the tutor can
8:42
determine the course and the tutor alone
8:46
can determine the course so these are
8:48
the a few functional dependencies we can
8:50
we can say that these are the functional
8:53
dependencies for this particular
8:54
relation right so once you just practice
8:57
so write down all the possibilities and
8:59
check out that one so you'll get these
9:01
three as a functional dependencies so
9:03
the first step is we got the functional
9:04
dependencies the next one we have to
9:06
check whether the relation is in 3 NF or
9:09
not because for checking for bcnf first
9:12
we need to check whether the relation is
9:13
in 3 and F or not the only way you can
9:15
go with the vcnf so what is the
9:19
3nf so if for a functional dependency
9:22
that I mean for all the non-prime
9:24
attributes there should not be there is
9:27
a transitivity functional dependency so
9:30
for that we have to find the candidate
9:32
Keys what are the candidate keys so here
9:34
in order to find the candidate keys
9:36
right on the procedure in the previous
9:38
session we have solved many problems to
9:40
finding the super keys and the candid
9:42
keys so I will post the link of complete
9:45
playlist in the description in that
9:46
playlist you can find the video and just
9:49
visit that video and you will be
9:51
understanding this concept right so
9:53
first
9:55
so student course tutor will give
10:01
all the three so this is a closure you
10:04
can say this is a closure right so from
10:06
the functional dependencies if you know
10:08
t
10:08
that means if you know the tutor we can
10:11
get the course so you can just remove
10:14
the course so student along with the
10:17
tutor can determine student total and if
10:21
you're not tutor we can get a course
10:25
so is there any possibility to reduce
10:27
this one no from this functional
10:30
dependency there is no if you're not a
10:32
student and course we can get a tutor
10:34
but here there is no course only we are
10:36
having a student okay so this is the one
10:40
candidate key so one super key one super
10:43
key because we're using the student and
10:45
tutor we are getting all the three
10:47
attributes but we can't say that this is
10:49
a candid key so we need to check all the
10:52
possibilities find out the exclosure and
10:55
the T closure
10:56
so for exclusion if you know yes we can
10:59
determine yes and if you know yes alone
11:01
we can't determine any other so this is
11:04
not as candid key
11:06
and t plus t plus so that means if you
11:10
know T we can determine T if you know T
11:13
we can determine C and if you know both
11:15
T and C we can't determine any any other
11:17
so this is also not a super key so we
11:19
can determine we can say that s and t
11:21
are the candidate key so here I will
11:23
write a candidate key
11:28
so one candid key is student and
11:33
tutor so we can say student and tutor
11:36
are the same candidate keys so we have
11:39
to check whether these are the prime
11:42
attributes okay the prime attributes the
11:44
prime attributes are s comma T we need
11:46
to check whether these Prime attributes
11:48
are on the right hand side of the any
11:50
relation or not so we are having one
11:52
prime attribute as a right hand side of
11:55
the functional dependency so replace
11:57
this one with this one so you can
12:00
replace this one so s comma in place of
12:03
T we can give s comma C which means s is
12:06
already there so we can give C
12:08
right and again find out the closure
12:11
whether we can find out or not yes comma
12:14
C if you know a student and a course we
12:16
can get a t so this is a Super Key we
12:19
can say this is a Super Key and minimize
12:21
this one so yes so in by using yes we
12:25
can get only yes by using C we can get a
12:28
c and if by knowing C we only the C can
12:30
be determined so this is this is this is
12:32
not a Super K so s comma C
12:35
again it's a candidate key again find
12:38
out the prime attributes the prime
12:40
attributes are s comma C so find out
12:43
whether these Prime attributes are
12:44
residing on the right hand side yes we
12:46
are having both the things so consider
12:49
any one so consider the third one so
12:51
replace this C with T so s t so s t is
12:56
already a candid key student and a tutor
12:59
along with a candid key so you can shop
13:01
here so our relation is having a two
13:04
candidate keys so just go the procedure
13:06
by following the previous sessions so
13:09
you can understand the procedure so from
13:12
that procedure by following that
13:14
procedure we have find out the candid
13:16
keys are s comma T and S comma C and
13:19
what we have to check whether the
13:20
non-prime attributes what are the
13:22
non-prime attributes so
13:25
as right here so non-prime attributes
13:27
are the attributes which are not
13:29
available in the candid key so you can
13:32
see from this one
13:35
we are getting all the things there is
13:37
no
13:38
Prime attributes non-prim attributes
13:40
okay no non-primatic words so obviously
13:43
there will be no three three I mean it
13:46
follows the 3nf there is no transitivity
13:48
dependency for the given relation
13:50
because we are not having any non Prime
13:52
attributes all these are the prime
13:54
attributes yes T and C we are having the
13:56
three attributes and all the three are
13:58
available in the candidate key so there
14:01
is no Prime non-prime attributes so in
14:03
order to check whether the relation is
14:04
in 3 and F we have to check there should
14:06
be no transitivity dependency for a
14:09
non-prime attribute but here we are not
14:11
having any non Prime attributes
14:12
obviously the relation will be in three
14:14
and F so no non Prime attribute so
14:17
relation
14:20
easy
14:22
three and a half now we have to check
14:24
for the this channel whether it is in
14:25
bcnf or not so what we have to do we
14:28
have to check whether for all the
14:31
functional dependencies X should be the
14:34
Super key or a prime or a candid key so
14:37
for this functional dependency you can
14:38
see s comma C which is a candid key
14:42
yes
14:44
right and here tends to C here it is X
14:47
and it is y so X is not a super key or a
14:50
part of candidate key oh sorry it's not
14:53
a candid key not a part okay it's not a
14:55
candid key so we have to
14:58
so here you can see tends to C so T is
15:02
not a
15:05
candidate key
15:07
candidate key so
15:11
relation
15:15
is not in
15:18
B C and F so if if for example for these
15:22
two things s comma C tends to T so here
15:26
it is X comma y x tends to Y so X should
15:29
be a Super Key or a candid key
15:32
see
15:38
so this is a valid
15:40
second one yes comma tends to C so here
15:44
you can see this is e x and this is y so
15:47
X is again it's a Super K or candidate
15:50
key yes comma T is a candidate yes
15:53
yes so coming to this one X comma Y and
15:56
X is not a candid K obviously we can say
15:58
the relation is not in b c l f so if the
16:02
relation is not in BC enough we have to
16:04
decompose the relation
16:06
okay we can have to decompose the
16:08
relation so that it it satisfies the
16:11
bcmf so how the relation should be
16:15
decomposed
16:16
and this relation can be divided into
16:18
two two relations so one with
16:26
student and a tutor
16:35
so student 101
16:41
Sandeep 101
16:47
Saturday 102
16:52
Sandeep and one zero two
16:58
subject
17:00
and the second relation
17:04
course
17:06
and a tutor
17:08
so course
17:09
is Java
17:13
python
17:17
so Java
17:19
is
17:21
python
17:23
with saradi
17:26
and python
17:35
so this is a relation one and relation
17:39
two
17:40
so here in this relation we found one
17:43
functional dependency would which
17:45
doesn't satisfies the bcnf condition so
17:48
if for all the functional dependencies X
17:51
tends to Y axis should be a Super Key or
17:53
a candidate key which violates this one
17:54
so that's why we have to decompose the
17:57
thing to different sub relations
18:00
so this is called the B C and F so
18:05
it is somewhat stronger than the 3nf
18:09
third normal form so if any relation
18:11
which satisfies till this bcnf then we
18:14
can say the data Sky the database scheme
18:17
is a very good design
18:19
okay the design of a scheme is very good
18:22
right so hope you understood about this
18:25
bcnf and if you are having any doubts
18:27
regarding this one feel free to post
18:28
your notes in the comment section
18:29
definitely I will try to clarify all
18:30
your doubts if you really enjoyed my
18:32
session like my session share my session
18:34
with your friends and don't forget to
18:35
subscribe to our Channel thanks for
18:37
watching thank you very much