YT YouTube2Text

YouTube2Text → Transcripts → Relational Database Design (Contd.) - 2

Relational Database Design (Contd.) - 2 — Transcript

by Data Base Management System - IITKGP · 5,075 words · 878 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00[Music]
0:16welcome to module 18 of database
0:19management systems we have been
0:21discussing about
0:22relational database design this is the
0:25part three of that
0:27in the last
0:28module we discussed about
0:31the notion of functional dependency and
0:33decomposition based on that in the
0:35elementary level and certain bit of its
0:38theory
0:39in this
0:40current module we learnt
0:42different algorithms that use the
0:45functional dependencies and can make
0:48conclusions about the design or make
0:50changes to the design we will also try
0:53to understand the characterization for
0:56lossless
0:57joint decomposition and the notion of
1:00deter dependency preservation
1:03therefore this module will have these
1:05three ah topics ah algorithms for
1:07functional dependencies lossless joint
1:09depend decomposition and dependency
1:12preservation
1:13so first we start with the algorithms
1:15and i quickly reproduce
1:18where we had ended in the last module in
1:20terms of computing the closure of a set
1:23of attributes so if we have a relation
1:27having these attributes and a set of
1:29functional dependencies then for a given
1:32subset of attributes in this case a g we
1:35can iteratively compute the closure set
1:38when no further changes can be done
1:40and
1:41with using that we can make different
1:43conclusions for example if our question
1:46is whether
1:47a g can be a candidate key
1:50we would
1:52first like to check whether it is a
1:54super key that is whether its closure
1:56has all the attributes of r
1:58or
1:58and we would like to check if we have
2:00take a subset
2:02of a g if we take just as attribute a or
2:05attribute g whether
2:07the closure of that will actually work
2:09as a key or not
2:11so this algorithm of
2:14attribute closure turns out to be a very
2:16powerful one where as we have just seen
2:19it can be used for checking ah super
2:21keys candidate keys primary non primary
2:24attributes and so on it can be used for
2:27checking functional dependencies for
2:29example let us
2:31suppose that if we have to check that
2:33whether if a particular functional
2:35dependency
2:36alpha determines beta holds
2:39ah
2:40then or or rather in other words whether
2:43alpha determines beta is in the closure
2:45of the set of functional dependencies f
2:48then all that we need to do is to
2:50compute alpha plus that is a closure of
2:53the set of attributes on the left hand
2:55side of the dependency and check if beta
2:58is a subset of that if beta is a subset
3:00of that then i know that alpha
3:02determines beta actually holds
3:06so ah in in this manner it can also be
3:10used
3:11to compute the closure of ah the whole
3:14set of functional dependencies f so if
3:17we i mean at least at a ah rudimentary
3:20level we can think of that if we take
3:23any subset of
3:24the set of attributes and find the
3:28closure and then all attributes that
3:31belong to that closure set are actually
3:34functionally dependent and therefore
3:36those functional dependencies will exist
3:40now we move forward from
3:42there and talk about what is known as a
3:45canonical cover a set of functional
3:47dependencies
3:49may have a number of redundant
3:51dependencies also so we need to
3:52understand that because there are lot of
3:54dependencies which can be inferred from
3:57a certain set of dependencies for
3:59example if you look into this set you
4:01will easily understand that
4:03in this whole set if i actually have
4:05just this we will be able to by
4:08transitivity we will be able to conclude
4:10about a determining c so in that way a c
4:14a determining c is a redundant ah
4:17dependency
4:19so
4:20here i am just showing you some examples
4:22so for example say i have a set of
4:26functional dependencies as this
4:30as this set
4:32and i want to know whether
4:34i can
4:35replace it by a simpler set here
4:39where
4:40this
4:41particular attribute on the right hand
4:43side of this dependency may be
4:45extraneous
4:47so if i have to do that then what we
4:49need to perform is we need to show that
4:53ah
4:54given the ah set of
4:57functional dependencies the original set
5:01whether this can imply this set that is
5:04from this set of functional dependencies
5:06whether i can logically conclude the
5:09simplified set
5:10so
5:11using the rules we will need to do that
5:13i have worked that out here under the
5:15forward scheme
5:17and we would also need to establish that
5:20if i have the simplified set then can i
5:23go to the
5:25original set that was given so if the
5:29simplified set also logically implies
5:32the original set then we can say that
5:34these are in a way
5:36ah equivalent
5:38and therefore i would like to use the
5:40simpler set
5:42so there is a another
5:43example following here
5:46where
5:50i have a
5:52another set given where
5:55if you look into this i would like to
5:57check whether i can get rid of this c on
5:59the left hand side and as as it stands
6:03we can actually do that
6:05and here in this whole process i have
6:09shown it in terms of using the
6:11armstrong's axioms how you can prove
6:13this but what we can
6:16do to systematize this whole process we
6:19can again make use of the
6:23ah
6:23notion of ah
6:26closure of attributes and
6:28ah compute whether to these two sets are
6:31equivalent whether simplification can be
6:33done so we will say a cover is canonical
6:36if it is in a sense minimal
6:39and still equivalent to the original set
6:41of dependencies and we will formally
6:43introduce what is
6:45minimal before that lets ah just look at
6:47the same examples again so
6:51we are trying to show the forward
6:52direction in the first case
6:54and the reverse direction in the first
6:56case but the only difference that i
6:58wanted to highlight is in terms of
7:00showing that you do not need to really
7:03explore on the armstrong's axioms but
7:05what you can do is you can simply take
7:07the left hand side attribute and compute
7:10a closure and see whether the right hand
7:12side is included that is basically
7:14testing for whether the given ah
7:16functional dependency is actually
7:17implied
7:18similar things can be done to simplify
7:20the left hand side also so this is the
7:23other example that i showed and i am
7:25just showing you that how you conclude
7:27this based on the ah closure of
7:30attributes algorithm
7:33so now i can formally define ah these
7:36possible removal so if i can remove an
7:38attribute as i have shown i can remove
7:40it from the right hand side or i can
7:42remove it from the left hand side so if
7:44an attribute can be removed then it is
7:46called extraneous
7:48so if i
7:50have a a functional dependency
7:54lets say
7:55alpha functionally determines beta
7:58and i have an attribute a which belongs
8:01to alpha
8:02then we can check whether it is possible
8:04to remove
8:06a from alpha so to test that what we do
8:09is we form a new set by removing the
8:12original functional dependency and
8:14adding the new functional dependency
8:16where the left hand side does not have
8:18that a
8:19and if f logically implies this then
8:22certainly we can conclude that ah
8:26a on the left hand side of the
8:28functional dependency was extraneous
8:30similar thing can be done for checking
8:33if there is
8:34an extraneous attribute on the right
8:36hand side of a dependency and in this
8:39case ah naturally what we will need to
8:41do is we will need to work out
8:45the simpler set
8:47and then check whether f is implied by
8:51that
8:51because as as you can understand
8:54that if you are
8:56if you are making the left hand
8:58if you are removing an attribute from
9:00the left hand side then you are making
9:03your precondition
9:05softer
9:06so you need to see whether that is
9:08implied by the original set and on the
9:11other hand if you are removing something
9:13on the right hand side
9:14then
9:15you are making your consequence
9:18sampler so you need to understand
9:20whether that set implies the original
9:23set
9:25so
9:26if you look into that and obviously the
9:29other directions of this implication is
9:32not necessary to be proven because that
9:34will
9:35automatically follow because in the
9:37first case when i am removing an
9:39attribute extraneous attribute from the
9:41left hand side of an
9:43ah of a functional dependency naturally
9:45the set that i get that will always
9:47imply the original set because it is
9:49always possible to add additional
9:51attributes on the left hand side and so
9:54on
9:55so here are some examples worked out so
9:58here where i show that given us at ac
10:01and a b determining c ah b is actually
10:05extraneous because as you can see if i
10:07remove ah b then i get
10:09a determining c which is originally
10:11already there in the set you can
10:13establish that by computing the ah
10:16closure of the attribute set
10:18another example where we are trying to
10:21see an
10:22extraneous attribute on the right hand
10:24side so in this example c on the right
10:26hand side of the set
10:28ah a b determining c d is extraneous
10:31because it can be inferred even after ah
10:34because a b determining c can be
10:36inferred even after deleting this ah c
10:39from the right hand side
10:41so
10:42these are using this
10:44notion we can formalize a test for
10:47ah whether an attribute is extraneous so
10:51this is the formal steps of the step are
10:54given here but i am sure you have
10:56already understood through the example
10:59so given this a canonical cover
11:01ah of a set of functional dependencies f
11:05it is denoted by fc
11:07ah will mean that it is a set which is
11:10equivalent to f which means f will
11:12logically imply all dependencies in fc
11:14and fc will logically imply all
11:16dependencies in f
11:18no functional dependency in fc will
11:20contain any extraneous attribute so all
11:24of them will be required attributes
11:27and each left hand side of the
11:29functional dependency in fc must be
11:31unique
11:32so its a minimal set of functional
11:34dependencies ah please
11:36note on on these two core points a cover
11:39is canonical if it is a minimal set and
11:42it is an irreducible set so neither you
11:44can remove any dependency nor you can
11:46remove any extraneous attribute from
11:50this dependency set
11:55so here is the algorithm so i am not
11:57going through the steps of the algorithm
11:59you can go through that and convince
12:01yourself that it indeed computes the
12:03canonical cover
12:05and practice more on that
12:08so here i have shown an example where we
12:10want to compute the canonical cover
12:13here
12:14so first since all left hand sides have
12:17to be unique so first
12:19we
12:20combine
12:22two
12:24these two into in terms of ah a
12:26determining b c so it becomes a simpler
12:28set
12:29so a determining b is removed then
12:33i
12:34would ah
12:38check
12:40for a being extraneous in a b
12:42determining c
12:43and we find that it indeed is extraneous
12:46so
12:47ah because b determining c is already
12:49there you can do the formal test in
12:51terms of the closure so the set gets
12:53even simpler i will check if c is
12:56extraneous in a determining b c
12:58i find that it indeed is
13:01and again you can use transitivity to
13:03get here
13:04or ah can use the attribute closure and
13:08finally i get that the set of the
13:11original set f
13:13is covered by a canonical set where just
13:15you have a determining b and b
13:17determining c
13:19so this set is logically implied by the
13:22original set and this set can logically
13:24imply the original set and we will often
13:27use the canonical cover for simplicity
13:30and for ease of application
13:33naturally ah this is strongly using the
13:35underlying concept of equivalence of two
13:38sets of functional dependencies f and g
13:40they are equivalent if their closures
13:43are equal or in other words if f covers
13:46g or and g covers f that is f logically
13:51implies g and g logically implies f so
13:53this table shows you a different
13:55conditions where you can conclude
13:57whether
13:58ah f and g are equivalent sets of
14:01functional dependencies so they will
14:04have to both covers have to be true for
14:06the
14:07sets to be equivalent
14:10what next what i have done is we have
14:13put a
14:15a number of practice problems for ah
14:18various kind of things that you can do
14:20with functional dependencies the first
14:22set of problems find
14:24ah in first set of problems you have to
14:26find if a given functional dependency is
14:28implied from a set of functional
14:30dependencies
14:32so there are three problems where three
14:34function
14:35sets of functional dependencies are
14:37given and you are given to check
14:40one or more functional dependencies if
14:42it is implied from that set so use
14:46the attribute closure and the algorithm
14:48that you have discussed to practice
14:49these problems and
14:52become master of that
14:54you can also check if
14:57can find candidate key using the
14:59functional dependencies so sets are
15:01given your task would be to find the
15:03candidate keys
15:06you can also use ah the algorithms to
15:10find super keys for a given set of
15:12functional dependencies so do practice
15:13these problems
15:15you can find prime and non prime
15:17attributes using functional dependencies
15:19ah prime attributes ah
15:22are attributes that belong to any
15:24candidate key not necessarily the same
15:27candidate key all attributes that belong
15:29to some candidate key you take a set of
15:32set together and you call them as a
15:35prime attribute
15:36and non prime attributes are those that
15:38do not belong to any candidate key at
15:41all so here your task is to find the
15:44prime and non prime attributes using the
15:47sets of functional dependencies given
15:50you can check for equivalence for a pair
15:53of sets of functional dependencies there
15:55are a couple of problems given on that
15:58so please ah try them out
16:01and ah for here for the different sets
16:05you have to compute the minimal cover or
16:07the irreducible set
16:09or canonical cover of the set of
16:11functional dependencies
16:13so please practice on this problem so
16:16that you become comfortable with
16:18using this algorithms for dealing easily
16:21with the functional dependencies sets of
16:24functional dependencies individual
16:25functional dependencies and so on
16:27so after ah
16:29after this week is closed and your
16:31assignments are also done then we will
16:34publish the solutions for these practice
16:36problems as well
16:38next let me um take up ah
16:42little characterization of the concept
16:44that we had introduced earlier in terms
16:46of the lossless joint decomposition
16:49so in the lossless joint decomposition
16:51the problem is ah say that you have a
16:54relational scheme
16:56r
16:57and you are trying to divide that into
16:59two relational schemes r one and r two
17:02so both r one and r two are
17:04ah having a set of ah attributes
17:07and ah r
17:09naturally has the set of attributes
17:11which is a union of the attributes of r
17:13one and r two
17:14then
17:15is it possible that if i
17:19take a relation project it on the
17:21attributes of r 1 and on the attributes
17:24of r 2 the 2 relations that we get if i
17:27take a natural join of that do i get
17:29back r
17:31if i do then i say that i have a
17:34lossless join
17:35if i do not then i have lost some
17:37information due to this projection and
17:41recomputation of the original ah
17:43relation based using the natural join
17:47the requirement this requirement of
17:50lossless join decomposition
17:53is determined if
17:55at least one of the following
17:57dependencies exist in the closure set of
18:00f
18:01which what are the this is saying that
18:04if i do r one intersection r two that is
18:07the attributes which are common you will
18:09recall that when you do natural join it
18:12is this set of attributes which take
18:14part because these set of attributes
18:16will help you compute the join between
18:19ah
18:20projection on r1 and the projection on
18:23r2
18:24so if this intersection set of
18:25attributes uniquely determines r 1
18:29or
18:30it uniquely determines r 2 that is if
18:33the intersection set of attributes is a
18:36super key
18:37either in r 1 or in r 2 or both
18:41then we say that the lossle the join
18:44will be a lossless join
18:46note that this is a sufficient condition
18:48that which means that there are there
18:50could be some instances where this
18:53property is not satisfied yet the join
18:55is lossless but we need guarantees for
18:57our design
18:58so
18:59we
19:00make use of the fact that if one of
19:03these conditions are satisfied then it
19:04is a sufficient condition to say that
19:06the joint will
19:07mass join will necessarily be lossless
19:11so here i give you a quick ah example ah
19:15to show the idea so we have a supplier
19:18relationship here which has five
19:21attributes here is an instance of that
19:24and we know that these are the
19:26dependencies that that hold ah the
19:29supplier number determines the supplier
19:31name and the supplier city and supply
19:34number and product number together
19:36determines the quantity and we decompose
19:38them in this manner we have we put a
19:41supplier relationship where we have the
19:44number name city and quantity of
19:46supplier and then we have a parts
19:48relation where we just have a product
19:50name and the quantity and then we so
19:53this is uh the projected supplier
19:55relation instance this is a projected
19:57parts relation instance and then we take
19:59a natural join to reconstruct so we are
20:01taking a natural join to reconstruct and
20:03we get this relationship
20:05now our desire was that we must get back
20:08the original relation but if you compare
20:10you will find that this is not the case
20:13here we have
20:15one tuple here and we have another tuple
20:18here i have specifically highlighted
20:19them in red
20:21which were not there in the original
20:23relation they have come in because ah
20:26when i did the join
20:28naturally the join had to be performed
20:31on
20:32this common attribute quantity
20:35and based on that ah value so nick
20:39five nick ny
20:42ah
20:44the five nick
20:46ny
20:47ah
20:49then we have ten five nick n y ten
20:52ah this entry and we have two entries of
20:56ten and
20:57ten here
20:59so
21:00the combination of this with this
21:03where the product number is 20 is
21:06actually not present in the original
21:09ah instance of the relation and that is
21:12what shows up here a similar one exist
21:15here so we get extra tuples
21:18and and mind you though though we are
21:20actually getting extra tuple we say that
21:22this join is lossy because if you get
21:25extra tuple then you are losing
21:27information you are losing correctness
21:29so being loss is actually losing
21:30correctness
21:32so
21:32ah
21:34even though
21:35we have more tuples we will we say that
21:38this is a lossy joint and you can now go
21:41back and analyze the common attribute q
21:43t y
21:44is not a super key either in this or in
21:47this so
21:48r one intersection r two implying
21:51r one or implying r two is does not hold
21:56so it does not and in addition it also
21:59does not preserve this functional
22:00dependency because these are not no more
22:03determined
22:06now let us see is it ah so we we saw a
22:10case where the join
22:12the decomposition that we did and then
22:14the subsequent join that we performed
22:17did not prove to be a lossless join we
22:20lost information
22:22so let us take a take a look as to can
22:25we ah
22:27actually do a decomposition which will
22:30be lossless where we will not lose
22:31information
22:41so we take the same example
22:44the supplier but the decomposition the
22:47same set of at
22:48dependencies also but the decomposition
22:51is different now we have
22:53name number name and city in one
22:56ah supplier relation and
22:58supplier name
23:00number product number and quantity in
23:02the other parts relation
23:05and then we again go back and perform
23:07the join
23:08now we find that from the original
23:11relation this was the original relation
23:13this is the projected supplier relation
23:15these are projected parts relation and
23:18this is the natural join
23:20of
23:20these two relations so this is the
23:23natural join of these two relations and
23:25we find that they exactly match with the
23:28original relation so we have not lost
23:31any information we get it back so we say
23:34that the join is lossless
23:36and the reason we could guarantee that
23:38is because if you look into the set of
23:41functional dependencies you will find
23:44that
23:45s number the supplier number
23:47is a key in the supplier relationship
23:52because it functionally determines s
23:53name as well as s c t so r one
23:56intersection r two
23:58functionally determining r one is true
24:00here and therefore it actually ah gives
24:04you a lossless join it also preserves
24:07all the dependencies because if you look
24:10into these dependencies you can
24:12you can check for this dependency in
24:14this relation you can check for this
24:16dependency also in this relation
24:18and you can check for this dependency in
24:20also
24:21in the parts relation which is something
24:23which we were not able to do the in the
24:26last decomposition that we have so
24:28naturally this is a type of
24:30decomposition that we will prefer
24:34so
24:35here we have
24:37i have given some more examples which
24:39you can practice and i show that given a
24:42very simple schema having three
24:44attributes and two dependencies one
24:47decomposition into
24:49a b and b c is
24:52lossless joint decomposition whereas the
24:54other one a b and a c is a loss
24:56decomposition
24:59i have
25:00given a number of practice problems on
25:02nausea joint so that you can practice
25:05and become master of
25:06these kind of algorithms
25:10finally let me ah
25:11quickly go over the dependency
25:13preservation
25:14concept the dependency preservation is
25:19if you have a relation which you have
25:21decomposed
25:23into n different relations
25:26so if you decompose a relation into into
25:28a number of
25:30relations then naturally
25:32ah all functional dependencies you
25:33cannot check on all the relations
25:36because ah a dependency may involve
25:39attributes all of which may not be
25:41present in a particular ah
25:45decomposed relation that you have
25:47ah it may be distributed amongst ah
25:50different so
25:51when you do this decomposition
25:53you get for every relation you get a
25:56new set of a subset of functional
25:59dependencies so r i the decomposed
26:01relation
26:02ah r i the ith relation will have a
26:07hm
26:09set of dependencies f i which is a
26:12subset of the original set f
26:15and ah involves only the attributes
26:18which are exist in r i so
26:21ah the decomposition will be
26:23said to be dependency preserving if i
26:25can take the union of
26:28all these functional dependencies what
26:30is projected on r 1 on r 2 and r n f 1 f
26:332 f n if we can take union of
26:36and if we take f they must be equivalent
26:39sets so which we we know the
26:42we we know the requirement so
26:45equivalence mean that they will their
26:46covers will have to be equal if it is
26:49not then
26:50some there will be at least one
26:52dependency which you will not be able to
26:54check in any one of the projected
26:56relations and to be able to check that
26:58you will have to compute
27:00the natural join
27:02ah and that is as we know is a very
27:04expensive process and we would not be
27:06able to do that on a regular basis
27:12so here i have written down the
27:15algorithm to test if a decomposition
27:19ah actually preserve the dependency or
27:22not so i will not go through the steps i
27:25will leave that for you to understand
27:27but what i will do i will just ah show
27:30you a simple set of worked out example
27:33and
27:34reasoned on that
27:36so i show you two different methods of
27:39ah doing this so
27:41here we have a set of attributes given
27:44the
27:46dependencies that work in that and a
27:48particular decomposition
27:50so given the set of attributes and the
27:53decomposition
27:55if we project now if we project the set
27:58of functional dependencies and these are
27:59the sets that we get so on r one we have
28:03two dependencies on r two we have three
28:05dependence one dependency and or r three
28:07we have
28:09one dependency again
28:10so if we now think about ah the union of
28:14these and the closure for that then we
28:16can see that a
28:19these four dependencies which occur here
28:22and therefore i have struck them off in
28:25this set
28:26these four dependencies can be checked
28:28directly on the
28:30projected
28:31relations
28:33so that leaves us with three
28:35dependencies in the original set which
28:38cannot be checked on any one of r one r
28:41two or r three for example if you
28:43consider ah b c determining e
28:46then ah b exist on r one
28:50and c also exist on r one but e is not
28:52there so you cannot check the dependency
28:54on r one you cannot check that on r two
28:57because c and e do not exist and you
28:59cannot check them on r three check it on
29:01r three because none of them actually
29:03exist
29:04so what we will need for the dependency
29:07preservation to hold is
29:09the dependencies which are already
29:12existing the four dependencies that are
29:14struck off if they collectively
29:18can logically imply these dependencies
29:22so that they can be checked then
29:24we will be able to say that this is
29:27dependency preserving so what you do is
29:29something very very simple you ah you
29:31want to say you want to check whether
29:34this is preserved so you start with the
29:36left hand side and compute the closure
29:38the only difference you compute the
29:39closure first
29:41with the set of functional dependencies
29:43projected on r one that is f one
29:46the set closure set that you get you
29:48take that and compute its closure with
29:51respect to the second set of functional
29:53dependencies f two
29:56the closure that you get you take that
29:57and you
29:59compute the closure with such respect to
30:01the third set of functional dependencies
30:03which is on r three and that is your
30:06final closure set so this closure set
30:09includes the right hand side attribute e
30:12so we can conclude that bc indeed ah
30:15functionally will determine e and that
30:18relationship will be preserved because
30:20we have starting from bc we have seen
30:23that in every projected relation what
30:26all implied functional dependencies that
30:28can be checked which is what the meaning
30:30of the closer set of attributes are and
30:33since ah that set eventually has e we
30:37will know that this
30:38can be this will be preserved this set
30:41also has f so the other one will also be
30:44preserved so this is preserved this is
30:45preserved to check whether this
30:48dependency is preserved we need to again
30:50repeat the process and find whether e f
30:54ah belongs to the final closure set
30:56which it does and therefore we conclude
30:59that this decomposition is dependency
31:02preserving with the same example i will
31:05just ah show you a little different way
31:07of
31:09doing the same exercise i have not
31:11written down the
31:12algorithm for this in long hand but the
31:14example should
31:17be quite illustrative so we are what you
31:20do when you project you check if the if
31:23some dependency has ah multiple
31:26attributes on the
31:28left hand on the right hand side then
31:30you write them in a separately
31:33decomposed manner so it implies
31:36ah determines b c d
31:38is written in terms of three
31:40dependencies a implies b b implies c and
31:42c implies d
31:44so you make sure that all dependencies
31:46are written in a form where the right
31:47hand side has a single attribute
31:51then you compute what is known as the
31:54reverse functional dependencies that is
31:56you take the right hand side and compute
32:00whether
32:00the right hand side can imply the left
32:03hand side
32:04so
32:05i will just ah by show you one so in
32:08case the right hand side here is b
32:10so you have a b on the right hand side
32:12so you compute the closure with respect
32:14to f the original set not not the
32:16projected set of
32:18and you get b f
32:20so you know that
32:22this
32:24this inverse this reverse functional
32:26dependency which is ah
32:29b
32:30functionally determines a which is the
32:32reverse dependency cannot be inferred
32:36and you do this for each of the right
32:38hand side single attribute and check if
32:42ah sum
32:43um if
32:44the reverse dependencies can be inferred
32:46or not
32:47ah the interesting case occurs here
32:50where you if you try to do the closure
32:52of a you actually find that a determines
32:55bc
32:56which is a reverse of
32:58this functional dependency can be
33:00inferred but you do not consider that as
33:03a violation because it is you already
33:06have
33:07a determining b and a determining c so
33:10that logically implies that a determines
33:13b c so it is not
33:15a new
33:16violation that is getting imposed so
33:20with this your test for reverse
33:22functional dependencies is passed
33:24and then you finally check for whether
33:27the three dependencies which are not
33:29part of the projected set of
33:30dependencies you take the closure of the
33:33left hand side
33:34with respect to in this case the again
33:37the original set of functional
33:38dependencies not the projected one and
33:40check if the right hand side belongs
33:42there if they do then combined with
33:44these two strategies you say that the
33:46set of functional dependencies are
33:49preserved under this decomposition so ah
33:53this is the process to follow you can
33:55follow any one of the two approaches to
33:58solve
33:59ah and i have given some practice
34:02problems on dependency preservation
34:04which you should practice on
34:06to summarize we have studied the
34:08algorithms for properties of functional
34:11dependencies and we have understood the
34:13characterization and determination
34:16algorithm for lossless joint
34:18decomposition and for dependency
34:21preservation in a decomposition in the
34:24coming module we will make use of these
34:26and discuss about how to improve these
34:29designs relate of relational schemas
34:31through the use of different normal
34:33forms

About this transcript

This page contains the full transcript of Relational Database Design (Contd.) - 2 by Data Base Management System - IITKGP, generated from the public captions YouTube serves with the video. The transcript has 5,075 words across 878 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.