[Script Info]
Title: 
[Events]
Format: Layer, Start, End, Style, Name, MarginL, MarginR, MarginV, Effect, Text
Dialogue: 0,0:00:00.04,0:00:01.01,Default,,0000,0000,0000,,This video continues the topic
Dialogue: 0,0:00:01.01,0:00:03.09,Default,,0000,0000,0000,,of relational design, talking specifically
Dialogue: 0,0:00:03.09,0:00:07.02,Default,,0000,0000,0000,,about multivalued dependencies and Fourth Normal Form.
Dialogue: 0,0:00:07.02,0:00:08.08,Default,,0000,0000,0000,,I know I've reminded you many times
Dialogue: 0,0:00:08.08,0:00:10.09,Default,,0000,0000,0000,,about relational designed by decomposition,
Dialogue: 0,0:00:10.09,0:00:12.06,Default,,0000,0000,0000,,so let me do it very quickly.
Dialogue: 0,0:00:12.06,0:00:14.07,Default,,0000,0000,0000,,The designer specifies mega relations
Dialogue: 0,0:00:14.07,0:00:15.01,Default,,0000,0000,0000,,with all the information they
Dialogue: 0,0:00:15.01,0:00:18.01,Default,,0000,0000,0000,,want to store and properties of the data.
Dialogue: 0,0:00:18.01,0:00:19.07,Default,,0000,0000,0000,,The system decomposes the mega
Dialogue: 0,0:00:19.07,0:00:21.07,Default,,0000,0000,0000,,relations into smaller relations that
Dialogue: 0,0:00:21.07,0:00:25.04,Default,,0000,0000,0000,,have good properties -- no anomalies and no lost information.
Dialogue: 0,0:00:25.04,0:00:27.04,Default,,0000,0000,0000,,When we have functional dependencies, as
Dialogue: 0,0:00:27.04,0:00:28.09,Default,,0000,0000,0000,,are properties of the data, we
Dialogue: 0,0:00:28.09,0:00:30.09,Default,,0000,0000,0000,,get Boyce-Codd Normal form, and
Dialogue: 0,0:00:30.09,0:00:32.00,Default,,0000,0000,0000,,then when we add to the
Dialogue: 0,0:00:32.00,0:00:34.07,Default,,0000,0000,0000,,functional dependencies multi-value dependencies
Dialogue: 0,0:00:34.07,0:00:36.04,Default,,0000,0000,0000,,we get fourth normal form.
Dialogue: 0,0:00:36.04,0:00:38.05,Default,,0000,0000,0000,,And the specification of multi-
Dialogue: 0,0:00:38.05,0:00:40.05,Default,,0000,0000,0000,,value dependencies and decomposition into
Dialogue: 0,0:00:40.05,0:00:43.04,Default,,0000,0000,0000,,Fourth Normal Form is the topic of this video.
Dialogue: 0,0:00:43.04,0:00:44.10,Default,,0000,0000,0000,,As a reminder from earlier,
Dialogue: 0,0:00:44.10,0:00:46.09,Default,,0000,0000,0000,,Fourth Normal Form is stronger
Dialogue: 0,0:00:46.09,0:00:48.06,Default,,0000,0000,0000,,than Boyce-Codd Normal Form so
Dialogue: 0,0:00:48.06,0:00:50.01,Default,,0000,0000,0000,,if we have here all of
Dialogue: 0,0:00:50.01,0:00:51.10,Default,,0000,0000,0000,,the relations that are in Boyce-
Dialogue: 0,0:00:51.10,0:00:54.02,Default,,0000,0000,0000,,Codd normal form, some subset
Dialogue: 0,0:00:54.02,0:00:59.10,Default,,0000,0000,0000,,of those are also in fourth normal form.
Dialogue: 0,0:00:59.10,0:01:01.06,Default,,0000,0000,0000,,When we have functional dependencies, we can guarantee Boyce Codd normal
Dialogue: 0,0:01:01.06,0:01:03.00,Default,,0000,0000,0000,,form and then when we add
Dialogue: 0,0:01:03.00,0:01:04.09,Default,,0000,0000,0000,,multi-value dependencies that's what
Dialogue: 0,0:01:04.09,0:01:06.01,Default,,0000,0000,0000,,allows us to narrow down to
Dialogue: 0,0:01:06.01,0:01:08.06,Default,,0000,0000,0000,,the stronger property of fourth normal form.
Dialogue: 0,0:01:08.06,0:01:10.01,Default,,0000,0000,0000,,So let's start with an example.
Dialogue: 0,0:01:10.01,0:01:13.02,Default,,0000,0000,0000,,We have information about students applying to colleges.
Dialogue: 0,0:01:13.02,0:01:15.10,Default,,0000,0000,0000,,The student is identified by their social security number.
Dialogue: 0,0:01:15.10,0:01:17.06,Default,,0000,0000,0000,,They may apply to several colleges
Dialogue: 0,0:01:17.06,0:01:18.01,Default,,0000,0000,0000,,and in this video we're not going
Dialogue: 0,0:01:18.01,0:01:20.01,Default,,0000,0000,0000,,to have college states, just college names.
Dialogue: 0,0:01:20.01,0:01:22.06,Default,,0000,0000,0000,,We'll assume they're unique.
Dialogue: 0,0:01:22.06,0:01:24.02,Default,,0000,0000,0000,,And then the student may have hobbies.
Dialogue: 0,0:01:24.02,0:01:25.04,Default,,0000,0000,0000,,And they may apply, as I've said,
Dialogue: 0,0:01:25.04,0:01:26.10,Default,,0000,0000,0000,,to several colleges and have several
Dialogue: 0,0:01:26.10,0:01:29.09,Default,,0000,0000,0000,,hobbies, but let's assume for now those are independent.
Dialogue: 0,0:01:29.09,0:01:33.06,Default,,0000,0000,0000,,So do we have any functional dependencies for this relation?
Dialogue: 0,0:01:33.06,0:01:35.04,Default,,0000,0000,0000,,Actually we don't have any all.
Dialogue: 0,0:01:35.04,0:01:37.01,Default,,0000,0000,0000,,The social security number does
Dialogue: 0,0:01:37.01,0:01:38.00,Default,,0000,0000,0000,,not determine the college name
Dialogue: 0,0:01:38.00,0:01:41.05,Default,,0000,0000,0000,,or the hobby or anything in the other direction.
Dialogue: 0,0:01:41.05,0:01:43.04,Default,,0000,0000,0000,,With no functional dependencies, the
Dialogue: 0,0:01:43.04,0:01:44.07,Default,,0000,0000,0000,,only key for the relation
Dialogue: 0,0:01:44.07,0:01:47.05,Default,,0000,0000,0000,,then is all attributes of the relation.
Dialogue: 0,0:01:47.05,0:01:50.02,Default,,0000,0000,0000,,So is this relation in Boyce-Codd Normal Form?
Dialogue: 0,0:01:50.02,0:01:51.01,Default,,0000,0000,0000,,As you might remember from the
Dialogue: 0,0:01:51.01,0:01:52.08,Default,,0000,0000,0000,,previous video, Boyce-Codd
Dialogue: 0,0:01:52.08,0:01:54.04,Default,,0000,0000,0000,,Normal Form says all functional
Dialogue: 0,0:01:54.04,0:01:56.04,Default,,0000,0000,0000,,dependencies have a key on the left hand side.
Dialogue: 0,0:01:56.04,0:01:59.09,Default,,0000,0000,0000,,Well, since we have no functional dependencies then the answer is yes.
Dialogue: 0,0:01:59.09,0:02:02.07,Default,,0000,0000,0000,,It is in Boyce-Codd normal form.
Dialogue: 0,0:02:02.07,0:02:04.08,Default,,0000,0000,0000,,On the other hand do we think this is a good design?
Dialogue: 0,0:02:04.08,0:02:08.01,Default,,0000,0000,0000,,I'm going to say no this is not a good design Why not?
Dialogue: 0,0:02:08.01,0:02:09.10,Default,,0000,0000,0000,,Well, let's suppose that somebody
Dialogue: 0,0:02:09.10,0:02:12.06,Default,,0000,0000,0000,,applies to five colleges
Dialogue: 0,0:02:12.06,0:02:14.09,Default,,0000,0000,0000,,and they have, say, six hobbies.
Dialogue: 0,0:02:14.09,0:02:16.02,Default,,0000,0000,0000,,Then to have all
Dialogue: 0,0:02:16.02,0:02:18.00,Default,,0000,0000,0000,,combinations of colleges and
Dialogue: 0,0:02:18.00,0:02:19.10,Default,,0000,0000,0000,,hobbies that would yield 30
Dialogue: 0,0:02:19.10,0:02:22.09,Default,,0000,0000,0000,,tuples in the relation and clearly that's not a good idea.
Dialogue: 0,0:02:22.09,0:02:24.06,Default,,0000,0000,0000,,We'd rather separate the college
Dialogue: 0,0:02:24.06,0:02:27.05,Default,,0000,0000,0000,,and hobbies if they are independent.
Dialogue: 0,0:02:27.05,0:02:29.10,Default,,0000,0000,0000,,So the separation of independent facts
Dialogue: 0,0:02:29.10,0:02:31.01,Default,,0000,0000,0000,,is what fourth normal form is about.
Dialogue: 0,0:02:31.01,0:02:34.02,Default,,0000,0000,0000,,And now let's get a little bit more formal.
Dialogue: 0,0:02:34.02,0:02:36.01,Default,,0000,0000,0000,,Like functional dependencies, multivalued dependencies
Dialogue: 0,0:02:36.01,0:02:38.07,Default,,0000,0000,0000,,are specified based on knowledge
Dialogue: 0,0:02:38.07,0:02:40.02,Default,,0000,0000,0000,,of the real world constraints on
Dialogue: 0,0:02:40.02,0:02:42.00,Default,,0000,0000,0000,,the data being captured and all
Dialogue: 0,0:02:42.00,0:02:43.06,Default,,0000,0000,0000,,instances of a relation
Dialogue: 0,0:02:43.06,0:02:47.01,Default,,0000,0000,0000,,with a multivalued dependency must adhere to the dependency.
Dialogue: 0,0:02:47.01,0:02:50.08,Default,,0000,0000,0000,,Now let's define exactly what a multi value dependency is.
Dialogue: 0,0:02:50.08,0:02:52.05,Default,,0000,0000,0000,,For relation R we write
Dialogue: 0,0:02:52.05,0:02:54.03,Default,,0000,0000,0000,,a multi value dependency using
Dialogue: 0,0:02:54.03,0:02:56.05,Default,,0000,0000,0000,,a double headed arrow and
Dialogue: 0,0:02:56.05,0:02:58.08,Default,,0000,0000,0000,,we say 'A' multi determines 'B'.
Dialogue: 0,0:02:58.08,0:03:00.07,Default,,0000,0000,0000,,In this case, again, with 'A'
Dialogue: 0,0:03:00.07,0:03:03.00,Default,,0000,0000,0000,,and 'B' possibly being sets of attributes, so that would be A
Dialogue: 0,0:03:03.00,0:03:05.04,Default,,0000,0000,0000,,one through A N
Dialogue: 0,0:03:05.04,0:03:06.06,Default,,0000,0000,0000,,and B one through B
Dialogue: 0,0:03:06.06,0:03:10.07,Default,,0000,0000,0000,,M, which I'm abbreviating with A bar and B bar.
Dialogue: 0,0:03:10.07,0:03:12.06,Default,,0000,0000,0000,,So let me write the formal definition of A multi
Dialogue: 0,0:03:12.06,0:03:14.04,Default,,0000,0000,0000,,determines B. Again using first
Dialogue: 0,0:03:14.04,0:03:15.09,Default,,0000,0000,0000,,order logic similarly to what
Dialogue: 0,0:03:15.09,0:03:19.07,Default,,0000,0000,0000,,we did with functional dependencies but this one's a bit more complicated.
Dialogue: 0,0:03:19.07,0:03:21.03,Default,,0000,0000,0000,,It says for all tuples T and
Dialogue: 0,0:03:21.03,0:03:23.02,Default,,0000,0000,0000,,U that are in relation
Dialogue: 0,0:03:23.02,0:03:25.06,Default,,0000,0000,0000,,R, if T with
Dialogue: 0,0:03:25.06,0:03:28.04,Default,,0000,0000,0000,,the attributes A of T equal U
Dialogue: 0,0:03:28.04,0:03:31.02,Default,,0000,0000,0000,,for the attributes A of U. Again these are lists of attributes.
Dialogue: 0,0:03:31.02,0:03:33.08,Default,,0000,0000,0000,,So if the two tuples agree on
Dialogue: 0,0:03:33.08,0:03:36.01,Default,,0000,0000,0000,,their A values then, and
Dialogue: 0,0:03:36.01,0:03:37.07,Default,,0000,0000,0000,,remember for functional dependencies it was
Dialogue: 0,0:03:37.07,0:03:40.03,Default,,0000,0000,0000,,simple we just said they agreed on their B values.
Dialogue: 0,0:03:40.03,0:03:41.08,Default,,0000,0000,0000,,But now it gets more complicated.
Dialogue: 0,0:03:41.08,0:03:44.01,Default,,0000,0000,0000,,We're going to say that there exists
Dialogue: 0,0:03:44.01,0:03:48.00,Default,,0000,0000,0000,,a third tuple V in R that has the following properties.
Dialogue: 0,0:03:48.00,0:03:50.01,Default,,0000,0000,0000,,V has the same A
Dialogue: 0,0:03:50.01,0:03:51.09,Default,,0000,0000,0000,,values as T and
Dialogue: 0,0:03:51.09,0:03:53.05,Default,,0000,0000,0000,,U. So V sub A equals
Dialogue: 0,0:03:53.05,0:03:57.00,Default,,0000,0000,0000,,T sub A, furthermore V
Dialogue: 0,0:03:57.00,0:03:59.09,Default,,0000,0000,0000,,has its B value,
Dialogue: 0,0:03:59.09,0:04:03.07,Default,,0000,0000,0000,,okay, drawn from T
Dialogue: 0,0:04:03.07,0:04:06.00,Default,,0000,0000,0000,,so it's equal there.
Dialogue: 0,0:04:06.00,0:04:08.04,Default,,0000,0000,0000,,And finally it has its
Dialogue: 0,0:04:08.04,0:04:10.03,Default,,0000,0000,0000,,rest, so those are
Dialogue: 0,0:04:10.03,0:04:11.06,Default,,0000,0000,0000,,all the attributes other than
Dialogue: 0,0:04:11.06,0:04:15.07,Default,,0000,0000,0000,,A and B equal to U rest.
Dialogue: 0,0:04:15.07,0:04:19.06,Default,,0000,0000,0000,,Okay, so that's a mouthful but let's look at that pictorially.
Dialogue: 0,0:04:19.06,0:04:21.00,Default,,0000,0000,0000,,So here's our relation R and
Dialogue: 0,0:04:21.00,0:04:22.01,Default,,0000,0000,0000,,we'll have the set of
Dialogue: 0,0:04:22.01,0:04:23.07,Default,,0000,0000,0000,,attributes A, the set of
Dialogue: 0,0:04:23.07,0:04:26.03,Default,,0000,0000,0000,,attributes B and the rest of the attributes.
Dialogue: 0,0:04:26.03,0:04:28.07,Default,,0000,0000,0000,,And now let's make some tuples.
Dialogue: 0,0:04:28.07,0:04:29.07,Default,,0000,0000,0000,,So let's say that this
Dialogue: 0,0:04:29.07,0:04:31.01,Default,,0000,0000,0000,,is tuple T and this
Dialogue: 0,0:04:31.01,0:04:32.05,Default,,0000,0000,0000,,is tuple U. And we
Dialogue: 0,0:04:32.05,0:04:33.09,Default,,0000,0000,0000,,said that T and U
Dialogue: 0,0:04:33.09,0:04:35.06,Default,,0000,0000,0000,,agree on their A values.
Dialogue: 0,0:04:35.06,0:04:37.05,Default,,0000,0000,0000,,So they have the same A values
Dialogue: 0,0:04:37.05,0:04:39.09,Default,,0000,0000,0000,,and then they don't have to have the same B values.
Dialogue: 0,0:04:39.09,0:04:41.04,Default,,0000,0000,0000,,So we'll call the first one B-1
Dialogue: 0,0:04:41.04,0:04:43.03,Default,,0000,0000,0000,,and the second one B-2 and
Dialogue: 0,0:04:43.03,0:04:45.01,Default,,0000,0000,0000,,then for the rest we'll call this R-1 and R-2.
Dialogue: 0,0:04:45.01,0:04:48.04,Default,,0000,0000,0000,,So what the multi -value dependency
Dialogue: 0,0:04:48.04,0:04:49.07,Default,,0000,0000,0000,,says is that we have a
Dialogue: 0,0:04:49.07,0:04:51.10,Default,,0000,0000,0000,,third tuple, V and
Dialogue: 0,0:04:51.10,0:04:53.07,Default,,0000,0000,0000,,V again has the same
Dialogue: 0,0:04:53.07,0:04:55.06,Default,,0000,0000,0000,,A and it has
Dialogue: 0,0:04:55.06,0:04:57.04,Default,,0000,0000,0000,,its B value from tuple
Dialogue: 0,0:04:57.04,0:04:59.04,Default,,0000,0000,0000,,T. So it has B-1,
Dialogue: 0,0:04:59.04,0:05:01.03,Default,,0000,0000,0000,,but it has its rest value
Dialogue: 0,0:05:01.03,0:05:03.03,Default,,0000,0000,0000,,from tuple U, so then
Dialogue: 0,0:05:03.03,0:05:05.06,Default,,0000,0000,0000,,it has R-2 here.
Dialogue: 0,0:05:05.06,0:05:06.08,Default,,0000,0000,0000,,So again what we're saying is
Dialogue: 0,0:05:06.08,0:05:08.02,Default,,0000,0000,0000,,that if we have these first
Dialogue: 0,0:05:08.02,0:05:09.04,Default,,0000,0000,0000,,two tuples T and U,
Dialogue: 0,0:05:09.04,0:05:11.01,Default,,0000,0000,0000,,then we also have tuple
Dialogue: 0,0:05:11.01,0:05:13.06,Default,,0000,0000,0000,,V. Now let me do something a little tricky.
Dialogue: 0,0:05:13.06,0:05:14.08,Default,,0000,0000,0000,,Let me swap the roles of
Dialogue: 0,0:05:14.08,0:05:16.02,Default,,0000,0000,0000,,T and U and show
Dialogue: 0,0:05:16.02,0:05:18.03,Default,,0000,0000,0000,,that we also with this definition,
Dialogue: 0,0:05:18.03,0:05:19.06,Default,,0000,0000,0000,,are guaranteed to have a
Dialogue: 0,0:05:19.06,0:05:21.01,Default,,0000,0000,0000,,fourth tuple and we'll call
Dialogue: 0,0:05:21.01,0:05:23.05,Default,,0000,0000,0000,,that fourth tuple W. By
Dialogue: 0,0:05:23.05,0:05:24.01,Default,,0000,0000,0000,,swapping the roles of
Dialogue: 0,0:05:24.01,0:05:28.04,Default,,0000,0000,0000,,T and U, W has again the same A value.
Dialogue: 0,0:05:28.04,0:05:29.01,Default,,0000,0000,0000,,Now it will take its B
Dialogue: 0,0:05:29.01,0:05:30.09,Default,,0000,0000,0000,,value from U and that
Dialogue: 0,0:05:30.09,0:05:32.05,Default,,0000,0000,0000,,will give us B2, and
Dialogue: 0,0:05:32.05,0:05:35.00,Default,,0000,0000,0000,,we'll take its rest
Dialogue: 0,0:05:35.00,0:05:37.09,Default,,0000,0000,0000,,value from T and that gives us R1.
Dialogue: 0,0:05:37.09,0:05:40.00,Default,,0000,0000,0000,,So what we can see
Dialogue: 0,0:05:40.00,0:05:41.04,Default,,0000,0000,0000,,here is that when we
Dialogue: 0,0:05:41.04,0:05:43.04,Default,,0000,0000,0000,,have the first two tuples
Dialogue: 0,0:05:43.04,0:05:45.08,Default,,0000,0000,0000,,that have this particular combination of
Dialogue: 0,0:05:45.08,0:05:47.04,Default,,0000,0000,0000,,B values and rest values,
Dialogue: 0,0:05:47.04,0:05:48.10,Default,,0000,0000,0000,,it tells us we
Dialogue: 0,0:05:48.10,0:05:51.08,Default,,0000,0000,0000,,must have the other combinations as well.
Dialogue: 0,0:05:51.08,0:05:54.00,Default,,0000,0000,0000,,We must have B1 with R2,
Dialogue: 0,0:05:54.00,0:05:55.01,Default,,0000,0000,0000,,and B2 with R1.
Dialogue: 0,0:05:55.01,0:05:56.01,Default,,0000,0000,0000,,What it's really saying
Dialogue: 0,0:05:56.01,0:05:58.04,Default,,0000,0000,0000,,is those B values and
Dialogue: 0,0:05:58.04,0:06:02.03,Default,,0000,0000,0000,,the rest values are independent of each other and we'll have all combinations.
Dialogue: 0,0:06:02.03,0:06:03.01,Default,,0000,0000,0000,,So that might get you thinking
Dialogue: 0,0:06:03.01,0:06:06.06,Default,,0000,0000,0000,,back to our colleges and hobbies.
Dialogue: 0,0:06:06.06,0:06:09.01,Default,,0000,0000,0000,,Incidentally, sometimes multi-value dependencies
Dialogue: 0,0:06:09.01,0:06:12.04,Default,,0000,0000,0000,,are called tuple generating dependencies.
Dialogue: 0,0:06:12.04,0:06:14.04,Default,,0000,0000,0000,,And that's because the definition is
Dialogue: 0,0:06:14.04,0:06:16.07,Default,,0000,0000,0000,,is about having additional tuples
Dialogue: 0,0:06:16.07,0:06:18.02,Default,,0000,0000,0000,,when you have some existing tuples,
Dialogue: 0,0:06:18.02,0:06:20.04,Default,,0000,0000,0000,,unlike functional dependencies which
Dialogue: 0,0:06:20.04,0:06:23.08,Default,,0000,0000,0000,,just talk about the relationships among existing tuples.
Dialogue: 0,0:06:23.08,0:06:25.02,Default,,0000,0000,0000,,So let's go back to our example.
Dialogue: 0,0:06:25.02,0:06:28.01,Default,,0000,0000,0000,,Now we have students applying to colleges and having hobbies.
Dialogue: 0,0:06:28.01,0:06:31.04,Default,,0000,0000,0000,,Those are independent facts about the student.
Dialogue: 0,0:06:31.04,0:06:33.09,Default,,0000,0000,0000,,We'll write our multi-value dependency
Dialogue: 0,0:06:33.09,0:06:36.00,Default,,0000,0000,0000,,as 'social security number multi
Dialogue: 0,0:06:36.00,0:06:37.07,Default,,0000,0000,0000,,determine C name' and
Dialogue: 0,0:06:37.07,0:06:39.02,Default,,0000,0000,0000,,now lets use some example
Dialogue: 0,0:06:39.02,0:06:42.09,Default,,0000,0000,0000,,data to see our definition and how it works here.
Dialogue: 0,0:06:42.09,0:06:44.04,Default,,0000,0000,0000,,Here's our apply relation with the
Dialogue: 0,0:06:44.04,0:06:46.01,Default,,0000,0000,0000,,social security number, the college
Dialogue: 0,0:06:46.01,0:06:48.05,Default,,0000,0000,0000,,name and the hobby.
Dialogue: 0,0:06:48.05,0:06:51.07,Default,,0000,0000,0000,,Let's suppose that we have
Dialogue: 0,0:06:51.07,0:06:53.02,Default,,0000,0000,0000,,a student, 123 who's applied
Dialogue: 0,0:06:53.02,0:06:57.03,Default,,0000,0000,0000,,to Stanford and plays the trumpet.
Dialogue: 0,0:06:57.03,0:06:59.02,Default,,0000,0000,0000,,Now, let's suppose that same
Dialogue: 0,0:06:59.02,0:07:01.06,Default,,0000,0000,0000,,student, 123, has applied
Dialogue: 0,0:07:01.06,0:07:04.06,Default,,0000,0000,0000,,to Berkeley and plays tennis.
Dialogue: 0,0:07:04.06,0:07:07.03,Default,,0000,0000,0000,,So what our multivalued dependency
Dialogue: 0,0:07:07.03,0:07:08.05,Default,,0000,0000,0000,,says, and let's make this
Dialogue: 0,0:07:08.05,0:07:10.06,Default,,0000,0000,0000,,tuple T and tuple U,
Dialogue: 0,0:07:10.06,0:07:12.02,Default,,0000,0000,0000,,is that there's a further tuple
Dialogue: 0,0:07:12.02,0:07:14.01,Default,,0000,0000,0000,,V. V takes the
Dialogue: 0,0:07:14.01,0:07:15.09,Default,,0000,0000,0000,,same social security number and
Dialogue: 0,0:07:15.09,0:07:18.01,Default,,0000,0000,0000,,it takes the first value
Dialogue: 0,0:07:18.01,0:07:20.01,Default,,0000,0000,0000,,for the college name and
Dialogue: 0,0:07:20.01,0:07:22.04,Default,,0000,0000,0000,,the second for the hobby.
Dialogue: 0,0:07:22.04,0:07:24.05,Default,,0000,0000,0000,,It says if we have
Dialogue: 0,0:07:24.05,0:07:26.02,Default,,0000,0000,0000,,a 123 playing trumpet at Stanford
Dialogue: 0,0:07:26.02,0:07:28.01,Default,,0000,0000,0000,,and tennis at Berkeley, then that
Dialogue: 0,0:07:28.01,0:07:31.00,Default,,0000,0000,0000,,same person will be playing tennis at Stanford.
Dialogue: 0,0:07:31.00,0:07:34.07,Default,,0000,0000,0000,,Furthermore, I show that the same definition will generate automatically.
Dialogue: 0,0:07:34.07,0:07:36.02,Default,,0000,0000,0000,,A fourth tuple with the
Dialogue: 0,0:07:36.02,0:07:39.07,Default,,0000,0000,0000,,other combination which would be Berkley and Trumpet.
Dialogue: 0,0:07:39.07,0:07:40.06,Default,,0000,0000,0000,,By the way one thing you
Dialogue: 0,0:07:40.06,0:07:41.08,Default,,0000,0000,0000,,might notice here is that
Dialogue: 0,0:07:41.08,0:07:43.09,Default,,0000,0000,0000,,we also have the multivalued
Dialogue: 0,0:07:43.09,0:07:47.04,Default,,0000,0000,0000,,dependency, social security number multi determines hobby.
Dialogue: 0,0:07:47.04,0:07:48.08,Default,,0000,0000,0000,,This is actually one of
Dialogue: 0,0:07:48.08,0:07:51.00,Default,,0000,0000,0000,,the rules for multivalued dependency saying
Dialogue: 0,0:07:51.00,0:07:53.02,Default,,0000,0000,0000,,that when you have A determines
Dialogue: 0,0:07:53.02,0:07:55.06,Default,,0000,0000,0000,,B, then you, A multidetermines
Dialogue: 0,0:07:55.06,0:07:57.01,Default,,0000,0000,0000,,B, then you also have A
Dialogue: 0,0:07:57.01,0:07:58.07,Default,,0000,0000,0000,,multi determines rest and we'll
Dialogue: 0,0:07:58.07,0:08:01.08,Default,,0000,0000,0000,,see some rules for multivalued dependencies later.
Dialogue: 0,0:08:01.08,0:08:03.00,Default,,0000,0000,0000,,Let's look quickly at a
Dialogue: 0,0:08:03.00,0:08:04.03,Default,,0000,0000,0000,,modification of our example where
Dialogue: 0,0:08:04.03,0:08:07.03,Default,,0000,0000,0000,,the real world assumptions about the data are different.
Dialogue: 0,0:08:07.03,0:08:10.07,Default,,0000,0000,0000,,So we still have exactly the same relation with the same attributes.
Dialogue: 0,0:08:10.07,0:08:12.00,Default,,0000,0000,0000,,But let's suppose that we don't
Dialogue: 0,0:08:12.00,0:08:14.07,Default,,0000,0000,0000,,want to reveal every hobby to every college.
Dialogue: 0,0:08:14.07,0:08:15.09,Default,,0000,0000,0000,,Maybe we'll decide that we don't
Dialogue: 0,0:08:15.09,0:08:16.10,Default,,0000,0000,0000,,want Stanford to know that
Dialogue: 0,0:08:16.10,0:08:18.00,Default,,0000,0000,0000,,we're a surfer or Berkeley
Dialogue: 0,0:08:18.00,0:08:20.07,Default,,0000,0000,0000,,to know that we're on the speech and debate team.
Dialogue: 0,0:08:20.07,0:08:22.02,Default,,0000,0000,0000,,So if that's the case, then
Dialogue: 0,0:08:22.02,0:08:25.04,Default,,0000,0000,0000,,what multivalued dependencies do we have in this relation?
Dialogue: 0,0:08:25.04,0:08:27.01,Default,,0000,0000,0000,,We actually have none.
Dialogue: 0,0:08:27.01,0:08:29.08,Default,,0000,0000,0000,,And we don't have any functional dependencies either by the way.
Dialogue: 0,0:08:29.08,0:08:31.10,Default,,0000,0000,0000,,And is this a good design?
Dialogue: 0,0:08:31.10,0:08:33.06,Default,,0000,0000,0000,,Well, actually I would argue yes.
Dialogue: 0,0:08:33.06,0:08:35.05,Default,,0000,0000,0000,,In this case, this design
Dialogue: 0,0:08:35.05,0:08:36.10,Default,,0000,0000,0000,,is a good one because
Dialogue: 0,0:08:36.10,0:08:38.00,Default,,0000,0000,0000,,we're not going to have that
Dialogue: 0,0:08:38.00,0:08:40.02,Default,,0000,0000,0000,,multiplicative effect of information.
Dialogue: 0,0:08:40.02,0:08:41.08,Default,,0000,0000,0000,,Every tuple that we have
Dialogue: 0,0:08:41.08,0:08:43.06,Default,,0000,0000,0000,,in the applied relation will
Dialogue: 0,0:08:43.06,0:08:46.06,Default,,0000,0000,0000,,be an independent piece of important information.
Dialogue: 0,0:08:46.06,0:08:48.03,Default,,0000,0000,0000,,Let's look at one more example
Dialogue: 0,0:08:48.03,0:08:51.06,Default,,0000,0000,0000,,before we go on to talk about properties of multivalued dependencies.
Dialogue: 0,0:08:51.06,0:08:53.02,Default,,0000,0000,0000,,I've extended the apply relation
Dialogue: 0,0:08:53.02,0:08:55.00,Default,,0000,0000,0000,,now to not only include colleges
Dialogue: 0,0:08:55.00,0:08:56.06,Default,,0000,0000,0000,,and hobbies but also the
Dialogue: 0,0:08:56.06,0:08:58.02,Default,,0000,0000,0000,,date of application to a
Dialogue: 0,0:08:58.02,0:09:01.00,Default,,0000,0000,0000,,college, and the major or majors that are being applied for.
Dialogue: 0,0:09:01.00,0:09:04.08,Default,,0000,0000,0000,,Let's continue to assume that hobbies are revealed to college selectively.
Dialogue: 0,0:09:04.08,0:09:06.04,Default,,0000,0000,0000,,We don't need to have same
Dialogue: 0,0:09:06.04,0:09:10.04,Default,,0000,0000,0000,,hobbies for each college that a student applies to.
Dialogue: 0,0:09:10.04,0:09:12.03,Default,,0000,0000,0000,,Secondly, lets assume that
Dialogue: 0,0:09:12.03,0:09:13.07,Default,,0000,0000,0000,,we restrict students to apply
Dialogue: 0,0:09:13.07,0:09:15.01,Default,,0000,0000,0000,,only once to each college,
Dialogue: 0,0:09:15.01,0:09:15.08,Default,,0000,0000,0000,,but what I what we mean
Dialogue: 0,0:09:15.08,0:09:17.07,Default,,0000,0000,0000,,by that is just on one day.
Dialogue: 0,0:09:17.07,0:09:19.00,Default,,0000,0000,0000,,A student can still apply
Dialogue: 0,0:09:19.00,0:09:21.03,Default,,0000,0000,0000,,to multiple majors at a
Dialogue: 0,0:09:21.03,0:09:24.02,Default,,0000,0000,0000,,single college and to different majors at different colleges.
Dialogue: 0,0:09:24.02,0:09:26.00,Default,,0000,0000,0000,,Let's also assume that majors
Dialogue: 0,0:09:26.00,0:09:29.08,Default,,0000,0000,0000,,are independent of hobbies, which seems to make sense.
Dialogue: 0,0:09:29.08,0:09:30.10,Default,,0000,0000,0000,,It takes some thinking to come
Dialogue: 0,0:09:30.10,0:09:32.05,Default,,0000,0000,0000,,up with the right functional and
Dialogue: 0,0:09:32.05,0:09:34.00,Default,,0000,0000,0000,,multivalued dependencies to capture these
Dialogue: 0,0:09:34.00,0:09:36.00,Default,,0000,0000,0000,,constraints, but here they are.
Dialogue: 0,0:09:36.00,0:09:37.06,Default,,0000,0000,0000,,The first one when we
Dialogue: 0,0:09:37.06,0:09:38.10,Default,,0000,0000,0000,,say that we reveal hobbies to
Dialogue: 0,0:09:38.10,0:09:40.07,Default,,0000,0000,0000,,college selectively is actually
Dialogue: 0,0:09:40.07,0:09:42.00,Default,,0000,0000,0000,,the absence of a multivalued
Dialogue: 0,0:09:42.00,0:09:45.06,Default,,0000,0000,0000,,dependency on hobbies and colleges.
Dialogue: 0,0:09:45.06,0:09:46.07,Default,,0000,0000,0000,,The second one says as
Dialogue: 0,0:09:46.07,0:09:47.08,Default,,0000,0000,0000,,we apply once to each
Dialogue: 0,0:09:47.08,0:09:49.01,Default,,0000,0000,0000,,college, or on one particular
Dialogue: 0,0:09:49.01,0:09:51.06,Default,,0000,0000,0000,,day to each college, so
Dialogue: 0,0:09:51.06,0:09:52.08,Default,,0000,0000,0000,,that would say that when
Dialogue: 0,0:09:52.08,0:09:54.09,Default,,0000,0000,0000,,we have a particular student
Dialogue: 0,0:09:54.09,0:09:57.06,Default,,0000,0000,0000,,and a particular college that always
Dialogue: 0,0:09:57.06,0:09:59.04,Default,,0000,0000,0000,,going to have the same date,
Dialogue: 0,0:09:59.04,0:10:00.08,Default,,0000,0000,0000,,so any two tuples for
Dialogue: 0,0:10:00.08,0:10:04.04,Default,,0000,0000,0000,,a student and college combination will be on the same date.
Dialogue: 0,0:10:04.04,0:10:06.03,Default,,0000,0000,0000,,The last dependency that
Dialogue: 0,0:10:06.03,0:10:08.06,Default,,0000,0000,0000,,we will have involves the independence
Dialogue: 0,0:10:08.06,0:10:09.01,Default,,0000,0000,0000,,of the majors that are
Dialogue: 0,0:10:09.01,0:10:11.05,Default,,0000,0000,0000,,being applied for and the
Dialogue: 0,0:10:11.05,0:10:13.03,Default,,0000,0000,0000,,hobbies that a student has, so
Dialogue: 0,0:10:13.03,0:10:14.08,Default,,0000,0000,0000,,we'll write that as the
Dialogue: 0,0:10:14.08,0:10:16.07,Default,,0000,0000,0000,,multivalue dependency social security
Dialogue: 0,0:10:16.07,0:10:18.09,Default,,0000,0000,0000,,number, plus college name,
Dialogue: 0,0:10:18.09,0:10:22.03,Default,,0000,0000,0000,,plus date, multidetermines major,
Dialogue: 0,0:10:22.03,0:10:24.02,Default,,0000,0000,0000,,and remember what that's saying
Dialogue: 0,0:10:24.02,0:10:25.08,Default,,0000,0000,0000,,is that major, for a
Dialogue: 0,0:10:25.08,0:10:27.07,Default,,0000,0000,0000,,given student, college, and
Dialogue: 0,0:10:27.07,0:10:29.07,Default,,0000,0000,0000,,date the majors that
Dialogue: 0,0:10:29.07,0:10:31.06,Default,,0000,0000,0000,,they apply for are independent
Dialogue: 0,0:10:31.06,0:10:33.05,Default,,0000,0000,0000,,of what we call the rest,
Dialogue: 0,0:10:33.05,0:10:36.02,Default,,0000,0000,0000,,which in this case is the hobbies.
Dialogue: 0,0:10:36.02,0:10:37.03,Default,,0000,0000,0000,,So, you might take some time to
Dialogue: 0,0:10:37.03,0:10:38.08,Default,,0000,0000,0000,,look at the formal definitions of
Dialogue: 0,0:10:38.08,0:10:41.00,Default,,0000,0000,0000,,functional dependencies, multivalue dependencies, and
Dialogue: 0,0:10:41.00,0:10:42.00,Default,,0000,0000,0000,,maybe write out some sample
Dialogue: 0,0:10:42.00,0:10:44.00,Default,,0000,0000,0000,,data to convince yourself that
Dialogue: 0,0:10:44.00,0:10:45.07,Default,,0000,0000,0000,,these are the dependencies that
Dialogue: 0,0:10:45.07,0:10:49.07,Default,,0000,0000,0000,,are capturing the assumptions that we make about the real world.
Dialogue: 0,0:10:49.07,0:10:51.00,Default,,0000,0000,0000,,Like with functional dependencies we
Dialogue: 0,0:10:51.00,0:10:53.02,Default,,0000,0000,0000,,have a notion of trivial dependency
Dialogue: 0,0:10:53.02,0:10:55.01,Default,,0000,0000,0000,,those that always hold we
Dialogue: 0,0:10:55.01,0:10:57.06,Default,,0000,0000,0000,,also have some rule for multi valued dependencies.
Dialogue: 0,0:10:57.06,0:10:59.02,Default,,0000,0000,0000,,The definition for a trivial
Dialogue: 0,0:10:59.02,0:11:01.04,Default,,0000,0000,0000,,multi valued dependency A multi
Dialogue: 0,0:11:01.04,0:11:03.02,Default,,0000,0000,0000,,determines B is in
Dialogue: 0,0:11:03.02,0:11:05.09,Default,,0000,0000,0000,,this case, that either B
Dialogue: 0,0:11:05.09,0:11:08.04,Default,,0000,0000,0000,,is a subset of A,
Dialogue: 0,0:11:08.04,0:11:10.01,Default,,0000,0000,0000,,or A union B
Dialogue: 0,0:11:10.01,0:11:12.01,Default,,0000,0000,0000,,are all attributes, a multi-value
Dialogue: 0,0:11:12.01,0:11:16.09,Default,,0000,0000,0000,,dependency is non-trivial if that's not the case.
Dialogue: 0,0:11:16.09,0:11:17.07,Default,,0000,0000,0000,,So let's take the look at
Dialogue: 0,0:11:17.07,0:11:20.09,Default,,0000,0000,0000,,why these multi-value dependencies are trivial.
Dialogue: 0,0:11:20.09,0:11:22.04,Default,,0000,0000,0000,,So let's start with the first
Dialogue: 0,0:11:22.04,0:11:24.01,Default,,0000,0000,0000,,case where we have our
Dialogue: 0,0:11:24.01,0:11:26.04,Default,,0000,0000,0000,,attributes A and the rest
Dialogue: 0,0:11:26.04,0:11:28.00,Default,,0000,0000,0000,,and then attributes B are a
Dialogue: 0,0:11:28.00,0:11:29.04,Default,,0000,0000,0000,,subset of A so lets
Dialogue: 0,0:11:29.04,0:11:31.06,Default,,0000,0000,0000,,say that these are attributes B.
Dialogue: 0,0:11:31.06,0:11:33.07,Default,,0000,0000,0000,,So what are definition of multi-valued
Dialogue: 0,0:11:33.07,0:11:34.08,Default,,0000,0000,0000,,dependencies says that when we
Dialogue: 0,0:11:34.08,0:11:37.04,Default,,0000,0000,0000,,have the same values for
Dialogue: 0,0:11:37.04,0:11:39.02,Default,,0000,0000,0000,,A in two tuples, so
Dialogue: 0,0:11:39.02,0:11:40.08,Default,,0000,0000,0000,,here A and A, then
Dialogue: 0,0:11:40.08,0:11:42.07,Default,,0000,0000,0000,,we have every combination of the
Dialogue: 0,0:11:42.07,0:11:44.00,Default,,0000,0000,0000,,B values and the rest,
Dialogue: 0,0:11:44.00,0:11:45.08,Default,,0000,0000,0000,,well obviously we do since
Dialogue: 0,0:11:45.08,0:11:47.00,Default,,0000,0000,0000,,the B's are subsets of the
Dialogue: 0,0:11:47.00,0:11:49.01,Default,,0000,0000,0000,,A's here, the B values
Dialogue: 0,0:11:49.01,0:11:50.01,Default,,0000,0000,0000,,are going to be the same as
Dialogue: 0,0:11:50.01,0:11:52.08,Default,,0000,0000,0000,,well and we clearly have every combination.
Dialogue: 0,0:11:52.08,0:11:55.06,Default,,0000,0000,0000,,For the other case of trivial multi-value dependencies.
Dialogue: 0,0:11:55.06,0:11:57.03,Default,,0000,0000,0000,,We have A and B together
Dialogue: 0,0:11:57.03,0:11:58.05,Default,,0000,0000,0000,,being all attributes of the
Dialogue: 0,0:11:58.05,0:11:59.10,Default,,0000,0000,0000,,relation, so in that
Dialogue: 0,0:11:59.10,0:12:02.00,Default,,0000,0000,0000,,case, there's no rest, so
Dialogue: 0,0:12:02.00,0:12:04.01,Default,,0000,0000,0000,,clearly we have every combination
Dialogue: 0,0:12:04.01,0:12:06.04,Default,,0000,0000,0000,,of values of A and
Dialogue: 0,0:12:06.04,0:12:09.09,Default,,0000,0000,0000,,B and rest, because there's no rest to combine with.
Dialogue: 0,0:12:09.09,0:12:11.06,Default,,0000,0000,0000,,Like with functional dependencies there are
Dialogue: 0,0:12:11.06,0:12:14.02,Default,,0000,0000,0000,,a whole bunch of rules that hold for multi-valued dependencies.
Dialogue: 0,0:12:14.02,0:12:15.04,Default,,0000,0000,0000,,We will just talk about three of
Dialogue: 0,0:12:15.04,0:12:16.05,Default,,0000,0000,0000,,them, and the first one is
Dialogue: 0,0:12:16.05,0:12:18.05,Default,,0000,0000,0000,,the most important and interesting,
Dialogue: 0,0:12:18.05,0:12:20.01,Default,,0000,0000,0000,,and that's the rule that says
Dialogue: 0,0:12:20.01,0:12:21.08,Default,,0000,0000,0000,,if we have a functional dependency
Dialogue: 0,0:12:21.08,0:12:23.08,Default,,0000,0000,0000,,from A to B then we
Dialogue: 0,0:12:23.08,0:12:26.02,Default,,0000,0000,0000,,also have a multi-valued dependency
Dialogue: 0,0:12:26.02,0:12:27.07,Default,,0000,0000,0000,,from A to B. And I'm
Dialogue: 0,0:12:27.07,0:12:29.02,Default,,0000,0000,0000,,gonna go ahead and prove that rule
Dialogue: 0,0:12:29.02,0:12:32.00,Default,,0000,0000,0000,,for you again because this is an important one.
Dialogue: 0,0:12:32.00,0:12:33.03,Default,,0000,0000,0000,,I'm going do this proof using a
Dialogue: 0,0:12:33.03,0:12:35.00,Default,,0000,0000,0000,,template for the relation similar to
Dialogue: 0,0:12:35.00,0:12:37.10,Default,,0000,0000,0000,,the what I did with rules for functional dependencies.
Dialogue: 0,0:12:37.10,0:12:38.09,Default,,0000,0000,0000,,So let's say we have our
Dialogue: 0,0:12:38.09,0:12:40.07,Default,,0000,0000,0000,,A attributes, our B attributes,
Dialogue: 0,0:12:40.07,0:12:42.05,Default,,0000,0000,0000,,and our rest, and what
Dialogue: 0,0:12:42.05,0:12:43.10,Default,,0000,0000,0000,,we need to prove, to prove
Dialogue: 0,0:12:43.10,0:12:46.00,Default,,0000,0000,0000,,the multi-value dependencies, is when
Dialogue: 0,0:12:46.00,0:12:46.09,Default,,0000,0000,0000,,there are tuples T and
Dialogue: 0,0:12:46.09,0:12:48.05,Default,,0000,0000,0000,,U with a certain form,
Dialogue: 0,0:12:48.05,0:12:51.07,Default,,0000,0000,0000,,there exists a tuple V of another form.
Dialogue: 0,0:12:51.07,0:12:54.04,Default,,0000,0000,0000,,So let's fill in some values first for the tuples.
Dialogue: 0,0:12:54.04,0:12:55.04,Default,,0000,0000,0000,,So Let's say that we
Dialogue: 0,0:12:55.04,0:12:56.01,Default,,0000,0000,0000,,have A and A here,
Dialogue: 0,0:12:56.01,0:12:58.06,Default,,0000,0000,0000,,that's what we need for the
Dialogue: 0,0:12:58.06,0:13:00.03,Default,,0000,0000,0000,,equality of the A values.
Dialogue: 0,0:13:00.03,0:13:02.05,Default,,0000,0000,0000,,Then we have B1 and R1,
Dialogue: 0,0:13:02.05,0:13:05.02,Default,,0000,0000,0000,,and we have B2 and
Dialogue: 0,0:13:05.02,0:13:06.08,Default,,0000,0000,0000,,R2, and in order
Dialogue: 0,0:13:06.08,0:13:09.04,Default,,0000,0000,0000,,to prove this multi-value dependency,
Dialogue: 0,0:13:09.04,0:13:10.05,Default,,0000,0000,0000,,I need to prove that there
Dialogue: 0,0:13:10.05,0:13:12.01,Default,,0000,0000,0000,,exists a tuple V that has
Dialogue: 0,0:13:12.01,0:13:13.09,Default,,0000,0000,0000,,the same A value that it
Dialogue: 0,0:13:13.09,0:13:16.00,Default,,0000,0000,0000,,has B1 from tuple T
Dialogue: 0,0:13:16.00,0:13:18.07,Default,,0000,0000,0000,,and R2 from Tuple U,
Dialogue: 0,0:13:18.07,0:13:20.00,Default,,0000,0000,0000,,and what I have in order
Dialogue: 0,0:13:20.00,0:13:21.09,Default,,0000,0000,0000,,to prove that is the fact
Dialogue: 0,0:13:21.09,0:13:23.05,Default,,0000,0000,0000,,that we have a functional dependency from
Dialogue: 0,0:13:23.05,0:13:25.03,Default,,0000,0000,0000,,A to B. Because we
Dialogue: 0,0:13:25.03,0:13:27.00,Default,,0000,0000,0000,,have the functional dependencies and because
Dialogue: 0,0:13:27.00,0:13:29.02,Default,,0000,0000,0000,,T and U have the same A value.
Dialogue: 0,0:13:29.02,0:13:30.05,Default,,0000,0000,0000,,What that tells us is
Dialogue: 0,0:13:30.05,0:13:33.08,Default,,0000,0000,0000,,that B1 equals B2 here.
Dialogue: 0,0:13:33.08,0:13:35.08,Default,,0000,0000,0000,,And so if B1 equals B2
Dialogue: 0,0:13:35.08,0:13:37.05,Default,,0000,0000,0000,,then we know that this value
Dialogue: 0,0:13:37.05,0:13:39.07,Default,,0000,0000,0000,,B1 here is equivalent
Dialogue: 0,0:13:39.07,0:13:41.05,Default,,0000,0000,0000,,to B2 and in order
Dialogue: 0,0:13:41.05,0:13:43.00,Default,,0000,0000,0000,,to prove the existence of this
Dialogue: 0,0:13:43.00,0:13:44.04,Default,,0000,0000,0000,,tuple well we have that tuple
Dialogue: 0,0:13:44.04,0:13:46.03,Default,,0000,0000,0000,,here already and we're done.
Dialogue: 0,0:13:46.03,0:13:47.07,Default,,0000,0000,0000,,So you might check that again,
Dialogue: 0,0:13:47.07,0:13:48.07,Default,,0000,0000,0000,,but what that says is
Dialogue: 0,0:13:48.07,0:13:50.02,Default,,0000,0000,0000,,using the knowledge of a
Dialogue: 0,0:13:50.02,0:13:52.01,Default,,0000,0000,0000,,functional dependency we can prove
Dialogue: 0,0:13:52.01,0:13:53.09,Default,,0000,0000,0000,,that we always have a corresponding
Dialogue: 0,0:13:53.09,0:13:56.03,Default,,0000,0000,0000,,multivalued dependency there are
Dialogue: 0,0:13:56.03,0:13:57.05,Default,,0000,0000,0000,,a couple more rules for
Dialogue: 0,0:13:57.05,0:13:58.10,Default,,0000,0000,0000,,multivalued dependencies that you can
Dialogue: 0,0:13:58.10,0:14:01.01,Default,,0000,0000,0000,,prove for yourself if you're so inclined.
Dialogue: 0,0:14:01.01,0:14:02.08,Default,,0000,0000,0000,,The first one is the intersection
Dialogue: 0,0:14:02.08,0:14:03.08,Default,,0000,0000,0000,,rule, it says that if
Dialogue: 0,0:14:03.08,0:14:06.04,Default,,0000,0000,0000,,we have A multi determines
Dialogue: 0,0:14:06.04,0:14:08.02,Default,,0000,0000,0000,,B and A multi determines
Dialogue: 0,0:14:08.02,0:14:10.04,Default,,0000,0000,0000,,C, then we have
Dialogue: 0,0:14:10.04,0:14:13.01,Default,,0000,0000,0000,,A multi determines B
Dialogue: 0,0:14:13.01,0:14:15.05,Default,,0000,0000,0000,,intersects C. The transitive
Dialogue: 0,0:14:15.05,0:14:18.06,Default,,0000,0000,0000,,rule is slightly different than from exact transitivity.
Dialogue: 0,0:14:18.06,0:14:19.08,Default,,0000,0000,0000,,What it says is if
Dialogue: 0,0:14:19.08,0:14:22.00,Default,,0000,0000,0000,,we have A multi determine B,
Dialogue: 0,0:14:22.00,0:14:24.02,Default,,0000,0000,0000,,and we have B multi determines
Dialogue: 0,0:14:24.02,0:14:26.06,Default,,0000,0000,0000,,C then we have
Dialogue: 0,0:14:26.06,0:14:28.09,Default,,0000,0000,0000,,A multi determined not
Dialogue: 0,0:14:28.09,0:14:31.00,Default,,0000,0000,0000,,C exactly but C minus
Dialogue: 0,0:14:31.00,0:14:32.02,Default,,0000,0000,0000,,B. \NAnd you might work
Dialogue: 0,0:14:32.02,0:14:33.08,Default,,0000,0000,0000,,some examples because it yourself
Dialogue: 0,0:14:33.08,0:14:35.03,Default,,0000,0000,0000,,why we don't have just
Dialogue: 0,0:14:35.03,0:14:36.09,Default,,0000,0000,0000,,A multi determines B and
Dialogue: 0,0:14:36.09,0:14:40.00,Default,,0000,0000,0000,,to subtract the attributes for B, although it's fairly complicated.
Dialogue: 0,0:14:40.00,0:14:41.07,Default,,0000,0000,0000,,So again these rules can
Dialogue: 0,0:14:41.07,0:14:42.09,Default,,0000,0000,0000,,be proven and there are many
Dialogue: 0,0:14:42.09,0:14:44.07,Default,,0000,0000,0000,,other rules of multivalued dependencies
Dialogue: 0,0:14:44.07,0:14:48.04,Default,,0000,0000,0000,,that you can read about in any of the readings provided on our website.
Dialogue: 0,0:14:48.04,0:14:50.02,Default,,0000,0000,0000,,By the way, regarding rules, let's
Dialogue: 0,0:14:50.02,0:14:51.02,Default,,0000,0000,0000,,come back to the fact
Dialogue: 0,0:14:51.02,0:14:54.01,Default,,0000,0000,0000,,that every functional dependency is a multivalued dependency.
Dialogue: 0,0:14:54.01,0:14:55.07,Default,,0000,0000,0000,,So we can use another Venn diagram.
Dialogue: 0,0:14:55.07,0:14:57.05,Default,,0000,0000,0000,,This is different than our previous one.
Dialogue: 0,0:14:57.05,0:14:59.00,Default,,0000,0000,0000,,We can list all of our
Dialogue: 0,0:14:59.00,0:15:01.01,Default,,0000,0000,0000,,multivalued dependencies here and the
Dialogue: 0,0:15:01.01,0:15:02.08,Default,,0000,0000,0000,,functional dependencies are a
Dialogue: 0,0:15:02.08,0:15:04.06,Default,,0000,0000,0000,,subset of those, so what
Dialogue: 0,0:15:04.06,0:15:06.02,Default,,0000,0000,0000,,that tells us is if we
Dialogue: 0,0:15:06.02,0:15:07.06,Default,,0000,0000,0000,,ever have a rule that applies
Dialogue: 0,0:15:07.06,0:15:10.02,Default,,0000,0000,0000,,for multivalued dependencies here, that
Dialogue: 0,0:15:10.02,0:15:12.04,Default,,0000,0000,0000,,will cover the entire Ven diagram
Dialogue: 0,0:15:12.04,0:15:15.02,Default,,0000,0000,0000,,and so that rule will apply for functional dependencies as well.
Dialogue: 0,0:15:15.02,0:15:16.09,Default,,0000,0000,0000,,So every rule for MVDs is
Dialogue: 0,0:15:16.09,0:15:19.04,Default,,0000,0000,0000,,also a rule for functional dependencies.
Dialogue: 0,0:15:19.04,0:15:21.01,Default,,0000,0000,0000,,On the other hand if we
Dialogue: 0,0:15:21.01,0:15:22.00,Default,,0000,0000,0000,,have a rule that applies
Dialogue: 0,0:15:22.00,0:15:24.05,Default,,0000,0000,0000,,for functional dependencies that rule
Dialogue: 0,0:15:24.05,0:15:26.01,Default,,0000,0000,0000,,does not necessarily have to
Dialogue: 0,0:15:26.01,0:15:29.00,Default,,0000,0000,0000,,apply all multivalued dependencies because
Dialogue: 0,0:15:29.00,0:15:32.01,Default,,0000,0000,0000,,it might be specialized just for this portion of the Venn diagram.
Dialogue: 0,0:15:32.01,0:15:35.00,Default,,0000,0000,0000,,So an example of such a rule is the splitting rule.
Dialogue: 0,0:15:35.00,0:15:36.08,Default,,0000,0000,0000,,The splitting rule is a
Dialogue: 0,0:15:36.08,0:15:38.06,Default,,0000,0000,0000,,rule that applies to functional dependencies,
Dialogue: 0,0:15:38.06,0:15:41.04,Default,,0000,0000,0000,,but does not always apply to multivalued dependencies.
Dialogue: 0,0:15:41.04,0:15:45.05,Default,,0000,0000,0000,,And again you could work an example to convince yourself of that fact.
Dialogue: 0,0:15:45.05,0:15:45.06,Default,,0000,0000,0000,,Woo.
Dialogue: 0,0:15:45.06,0:15:47.04,Default,,0000,0000,0000,,So after all that set up
Dialogue: 0,0:15:47.04,0:15:48.10,Default,,0000,0000,0000,,of multivalue dependencies, we're finally
Dialogue: 0,0:15:48.10,0:15:51.00,Default,,0000,0000,0000,,ready to talk about fourth normal form.
Dialogue: 0,0:15:51.00,0:15:52.00,Default,,0000,0000,0000,,The definition of fourth normal
Dialogue: 0,0:15:52.00,0:15:55.04,Default,,0000,0000,0000,,form looks very similar to the one for Boyce-Codd normal form.
Dialogue: 0,0:15:55.04,0:15:56.07,Default,,0000,0000,0000,,Says we take a relation and
Dialogue: 0,0:15:56.07,0:15:57.08,Default,,0000,0000,0000,,we take now a set of
Dialogue: 0,0:15:57.08,0:15:59.06,Default,,0000,0000,0000,,multivalued dependencies for that
Dialogue: 0,0:15:59.06,0:16:01.00,Default,,0000,0000,0000,,relation and the relation
Dialogue: 0,0:16:01.00,0:16:03.01,Default,,0000,0000,0000,,is in fourth normal form if
Dialogue: 0,0:16:03.01,0:16:04.09,Default,,0000,0000,0000,,every non-trivial
Dialogue: 0,0:16:04.09,0:16:07.00,Default,,0000,0000,0000,,multivalued dependency has on
Dialogue: 0,0:16:07.00,0:16:09.03,Default,,0000,0000,0000,,it's left hand side a key.
Dialogue: 0,0:16:09.03,0:16:10.01,Default,,0000,0000,0000,,Remember for functional dependencies it
Dialogue: 0,0:16:10.01,0:16:12.01,Default,,0000,0000,0000,,looks exactly the same except
Dialogue: 0,0:16:12.01,0:16:16.00,Default,,0000,0000,0000,,we have the functional dependency all here instead of multivalued dependencies.
Dialogue: 0,0:16:16.00,0:16:17.04,Default,,0000,0000,0000,,So, let's see exactly what fourth
Dialogue: 0,0:16:17.04,0:16:20.00,Default,,0000,0000,0000,,normal form telling us and why it's a good thing.
Dialogue: 0,0:16:20.00,0:16:21.05,Default,,0000,0000,0000,,So we have A, B, and
Dialogue: 0,0:16:21.05,0:16:23.08,Default,,0000,0000,0000,,rest as usual and let's
Dialogue: 0,0:16:23.08,0:16:27.02,Default,,0000,0000,0000,,suppose that we have a non trivial multivalued dependency.
Dialogue: 0,0:16:27.02,0:16:28.08,Default,,0000,0000,0000,,So that's telling us that
Dialogue: 0,0:16:28.08,0:16:30.04,Default,,0000,0000,0000,,if we have 2 tuples, T
Dialogue: 0,0:16:30.04,0:16:32.01,Default,,0000,0000,0000,,and U and we'll
Dialogue: 0,0:16:32.01,0:16:33.06,Default,,0000,0000,0000,,put in some values for B
Dialogue: 0,0:16:33.06,0:16:35.04,Default,,0000,0000,0000,,and the rest, then we're going
Dialogue: 0,0:16:35.04,0:16:37.09,Default,,0000,0000,0000,,to have the combination of those, as well.
Dialogue: 0,0:16:37.09,0:16:39.01,Default,,0000,0000,0000,,So, that's kind of the proliferation of
Dialogue: 0,0:16:39.01,0:16:40.08,Default,,0000,0000,0000,,tuples we get when we
Dialogue: 0,0:16:40.08,0:16:43.05,Default,,0000,0000,0000,,squish independent facts in the same relation.
Dialogue: 0,0:16:43.05,0:16:44.09,Default,,0000,0000,0000,,But, if the left
Dialogue: 0,0:16:44.09,0:16:47.03,Default,,0000,0000,0000,,side is a key, so if
Dialogue: 0,0:16:47.03,0:16:48.10,Default,,0000,0000,0000,,the A attributes are
Dialogue: 0,0:16:48.10,0:16:50.03,Default,,0000,0000,0000,,a key here then we won't have
Dialogue: 0,0:16:50.03,0:16:51.06,Default,,0000,0000,0000,,those 2 tuples and will
Dialogue: 0,0:16:51.06,0:16:54.03,Default,,0000,0000,0000,,never have to worry about the proliferation.
Dialogue: 0,0:16:54.03,0:16:55.09,Default,,0000,0000,0000,,Now, remember that I said fourth
Dialogue: 0,0:16:55.09,0:16:58.09,Default,,0000,0000,0000,,normal form implies Boyce-Codd Normal Form.
Dialogue: 0,0:16:58.09,0:16:59.08,Default,,0000,0000,0000,,Or if you prefer it in
Dialogue: 0,0:16:59.08,0:17:02.04,Default,,0000,0000,0000,,Venn diagram format, Fourth
Dialogue: 0,0:17:02.04,0:17:04.04,Default,,0000,0000,0000,,Normal Form is stronger than
Dialogue: 0,0:17:04.04,0:17:07.09,Default,,0000,0000,0000,,Boyce-Codd Normal Form and let's see why that's the case.
Dialogue: 0,0:17:07.09,0:17:09.00,Default,,0000,0000,0000,,If we have a fourth
Dialogue: 0,0:17:09.00,0:17:10.03,Default,,0000,0000,0000,,normal form and we want
Dialogue: 0,0:17:10.03,0:17:11.10,Default,,0000,0000,0000,,to show that we're in Boyce-Codd normal
Dialogue: 0,0:17:11.10,0:17:13.02,Default,,0000,0000,0000,,form, then we have to
Dialogue: 0,0:17:13.02,0:17:14.07,Default,,0000,0000,0000,,show that if we have
Dialogue: 0,0:17:14.07,0:17:17.04,Default,,0000,0000,0000,,a functional dependency then
Dialogue: 0,0:17:17.04,0:17:19.02,Default,,0000,0000,0000,,the left hand side A is a key.
Dialogue: 0,0:17:19.02,0:17:21.08,Default,,0000,0000,0000,,That would tell us we're in Boyce-Codd normal form.
Dialogue: 0,0:17:21.08,0:17:22.09,Default,,0000,0000,0000,,Well, if we have a functional
Dialogue: 0,0:17:22.09,0:17:24.02,Default,,0000,0000,0000,,dependency, we had a rule
Dialogue: 0,0:17:24.02,0:17:25.01,Default,,0000,0000,0000,,that tells us we also have
Dialogue: 0,0:17:25.01,0:17:27.08,Default,,0000,0000,0000,,the multivalued dependency and then
Dialogue: 0,0:17:27.08,0:17:30.08,Default,,0000,0000,0000,,since we're in fourth normal form, we get that A as a key.
Dialogue: 0,0:17:30.08,0:17:32.07,Default,,0000,0000,0000,,So again, fourth normal form
Dialogue: 0,0:17:32.07,0:17:35.01,Default,,0000,0000,0000,,implies Boyce-Codd normal form.
Dialogue: 0,0:17:35.01,0:17:35.10,Default,,0000,0000,0000,,Now let's take a look at
Dialogue: 0,0:17:35.10,0:17:38.06,Default,,0000,0000,0000,,the decomposition of algorithm into fourth normal form.
Dialogue: 0,0:17:38.06,0:17:40.04,Default,,0000,0000,0000,,It's extremely similar to the
Dialogue: 0,0:17:40.04,0:17:42.03,Default,,0000,0000,0000,,BCNF decomposition algorithm.
Dialogue: 0,0:17:42.03,0:17:43.08,Default,,0000,0000,0000,,The input is a relation.
Dialogue: 0,0:17:43.08,0:17:45.04,Default,,0000,0000,0000,,A set of functional dependencies
Dialogue: 0,0:17:45.04,0:17:46.08,Default,,0000,0000,0000,,and multi value dependencies and we
Dialogue: 0,0:17:46.08,0:17:48.00,Default,,0000,0000,0000,,need to separate them because
Dialogue: 0,0:17:48.00,0:17:50.08,Default,,0000,0000,0000,,we use the functional dependencies to find keys.
Dialogue: 0,0:17:50.08,0:17:52.06,Default,,0000,0000,0000,,The output is a decomposition
Dialogue: 0,0:17:52.06,0:17:54.08,Default,,0000,0000,0000,,of R into good relations,
Dialogue: 0,0:17:54.08,0:17:56.01,Default,,0000,0000,0000,,in this case fourth normal form,
Dialogue: 0,0:17:56.01,0:17:58.04,Default,,0000,0000,0000,,and it's a good decomposition in
Dialogue: 0,0:17:58.04,0:18:02.02,Default,,0000,0000,0000,,the sense that reassembling the relations gives you back the original.
Dialogue: 0,0:18:02.02,0:18:03.00,Default,,0000,0000,0000,,As with Boyce-Codd normal form
Dialogue: 0,0:18:03.00,0:18:05.02,Default,,0000,0000,0000,,we start by computing keys using
Dialogue: 0,0:18:05.02,0:18:07.01,Default,,0000,0000,0000,,the functional dependencies, and then
Dialogue: 0,0:18:07.01,0:18:08.10,Default,,0000,0000,0000,,we repeat the decomposition process
Dialogue: 0,0:18:08.10,0:18:11.04,Default,,0000,0000,0000,,until all of our relations are in fourth normal
Dialogue: 0,0:18:11.04,0:18:12.04,Default,,0000,0000,0000,,form.
Dialogue: 0,0:18:12.04,0:18:14.04,Default,,0000,0000,0000,,Just as with functional dependencies
Dialogue: 0,0:18:14.04,0:18:15.08,Default,,0000,0000,0000,,in BCNF, we pick a relation
Dialogue: 0,0:18:15.08,0:18:17.08,Default,,0000,0000,0000,,that has a violating dependency, in
Dialogue: 0,0:18:17.08,0:18:19.08,Default,,0000,0000,0000,,this case a multi-value dependency, and
Dialogue: 0,0:18:19.08,0:18:22.01,Default,,0000,0000,0000,,we split the relation based on that dependency.
Dialogue: 0,0:18:22.01,0:18:24.03,Default,,0000,0000,0000,,So we create one relation that
Dialogue: 0,0:18:24.03,0:18:25.09,Default,,0000,0000,0000,,has the attributes of the dependency
Dialogue: 0,0:18:25.09,0:18:27.03,Default,,0000,0000,0000,,and another relation that has
Dialogue: 0,0:18:27.03,0:18:30.07,Default,,0000,0000,0000,,the left-hand side of the dependency and the rest of the attributes.
Dialogue: 0,0:18:30.07,0:18:31.08,Default,,0000,0000,0000,,After that, we need to
Dialogue: 0,0:18:31.08,0:18:33.08,Default,,0000,0000,0000,,compute the functional dependencies for
Dialogue: 0,0:18:33.08,0:18:35.07,Default,,0000,0000,0000,,the decomposed relation and the
Dialogue: 0,0:18:35.07,0:18:39.04,Default,,0000,0000,0000,,multi-value dependencies for it, and then we can compute the keys.
Dialogue: 0,0:18:39.04,0:18:41.08,Default,,0000,0000,0000,,Now finding these multi-value
Dialogue: 0,0:18:41.08,0:18:45.05,Default,,0000,0000,0000,,dependencies is actually a fairly complex process.
Dialogue: 0,0:18:45.05,0:18:47.04,Default,,0000,0000,0000,,Usually it's very intuitive,
Dialogue: 0,0:18:47.04,0:18:48.04,Default,,0000,0000,0000,,but I'm going to refer you
Dialogue: 0,0:18:48.04,0:18:50.09,Default,,0000,0000,0000,,to the readings to read about the algorithm itself.
Dialogue: 0,0:18:50.09,0:18:51.10,Default,,0000,0000,0000,,And in fact, it can be
Dialogue: 0,0:18:51.10,0:18:53.04,Default,,0000,0000,0000,,so complicated in the general
Dialogue: 0,0:18:53.04,0:18:56.00,Default,,0000,0000,0000,,case that some of the readings don't even provide the algorithm.
Dialogue: 0,0:18:56.00,0:18:59.03,Default,,0000,0000,0000,,But again, in general, it's very intuitive.
Dialogue: 0,0:18:59.03,0:19:01.09,Default,,0000,0000,0000,,Our first example is going to be very fast to do.
Dialogue: 0,0:19:01.09,0:19:03.08,Default,,0000,0000,0000,,As you remember, this example has
Dialogue: 0,0:19:03.08,0:19:05.09,Default,,0000,0000,0000,,one multi-value dependency - social
Dialogue: 0,0:19:05.09,0:19:07.07,Default,,0000,0000,0000,,security number determines college name,
Dialogue: 0,0:19:07.07,0:19:09.06,Default,,0000,0000,0000,,multi determines college name -
Dialogue: 0,0:19:09.06,0:19:12.00,Default,,0000,0000,0000,,and it has no keys other than all of the attributes.
Dialogue: 0,0:19:12.00,0:19:13.00,Default,,0000,0000,0000,,So obviously, this is a
Dialogue: 0,0:19:13.00,0:19:16.00,Default,,0000,0000,0000,,violating multi value dependency,
Dialogue: 0,0:19:16.00,0:19:18.02,Default,,0000,0000,0000,,and so we decompose into two
Dialogue: 0,0:19:18.02,0:19:21.02,Default,,0000,0000,0000,,relations, we'll call them A1 and A2.
Dialogue: 0,0:19:21.02,0:19:23.02,Default,,0000,0000,0000,,The first one has the attributes
Dialogue: 0,0:19:23.02,0:19:24.09,Default,,0000,0000,0000,,of the multivalue dependency, the
Dialogue: 0,0:19:24.09,0:19:26.08,Default,,0000,0000,0000,,social security number and
Dialogue: 0,0:19:26.08,0:19:28.00,Default,,0000,0000,0000,,the college name, and the second
Dialogue: 0,0:19:28.00,0:19:29.06,Default,,0000,0000,0000,,one has the left hand
Dialogue: 0,0:19:29.06,0:19:31.09,Default,,0000,0000,0000,,side multivalued dependency as well
Dialogue: 0,0:19:31.09,0:19:35.03,Default,,0000,0000,0000,,as all the remaining attributes, which in this case is the hobby.
Dialogue: 0,0:19:35.03,0:19:37.04,Default,,0000,0000,0000,,These two decomposed relations actually
Dialogue: 0,0:19:37.04,0:19:39.05,Default,,0000,0000,0000,,have no FDs and no
Dialogue: 0,0:19:39.05,0:19:41.03,Default,,0000,0000,0000,,MVDs so in that
Dialogue: 0,0:19:41.03,0:19:42.09,Default,,0000,0000,0000,,case we're definitely in 4th
Dialogue: 0,0:19:42.09,0:19:44.06,Default,,0000,0000,0000,,normal form and we're done
Dialogue: 0,0:19:44.06,0:19:46.05,Default,,0000,0000,0000,,with the decomposition and I think
Dialogue: 0,0:19:46.05,0:19:47.06,Default,,0000,0000,0000,,we can agree that this looks like
Dialogue: 0,0:19:47.06,0:19:49.10,Default,,0000,0000,0000,,a good schema for the data at hand.
Dialogue: 0,0:19:49.10,0:19:52.07,Default,,0000,0000,0000,,Our second example is quite a bit more complicated.
Dialogue: 0,0:19:52.07,0:19:54.03,Default,,0000,0000,0000,,Remember in this example we
Dialogue: 0,0:19:54.03,0:19:55.07,Default,,0000,0000,0000,,have that the social
Dialogue: 0,0:19:55.07,0:19:57.08,Default,,0000,0000,0000,,security number and college name
Dialogue: 0,0:19:57.08,0:19:59.06,Default,,0000,0000,0000,,functionally determine date.
Dialogue: 0,0:19:59.06,0:20:01.02,Default,,0000,0000,0000,,That means we have each student
Dialogue: 0,0:20:01.02,0:20:03.04,Default,,0000,0000,0000,,applies to each college on a specific date.
Dialogue: 0,0:20:03.04,0:20:05.05,Default,,0000,0000,0000,,And secondly, we assume that
Dialogue: 0,0:20:05.05,0:20:08.04,Default,,0000,0000,0000,,majors that were being applied for were independent of hobbies.
Dialogue: 0,0:20:08.04,0:20:10.07,Default,,0000,0000,0000,,So we have social security number,
Dialogue: 0,0:20:10.07,0:20:13.05,Default,,0000,0000,0000,,college name and date
Dialogue: 0,0:20:13.05,0:20:15.08,Default,,0000,0000,0000,,multi determines the major.
Dialogue: 0,0:20:15.08,0:20:19.09,Default,,0000,0000,0000,,And incidentally that would mean it multi determines the hobby too.
Dialogue: 0,0:20:19.09,0:20:21.06,Default,,0000,0000,0000,,Once again, we have no keys
Dialogue: 0,0:20:21.06,0:20:24.04,Default,,0000,0000,0000,,for the relation, except for all attributes.
Dialogue: 0,0:20:24.04,0:20:26.09,Default,,0000,0000,0000,,So we have both a violating
Dialogue: 0,0:20:26.09,0:20:28.04,Default,,0000,0000,0000,,functional dependency in this case
Dialogue: 0,0:20:28.04,0:20:31.07,Default,,0000,0000,0000,,and we have a violating multivalue dependency.
Dialogue: 0,0:20:31.07,0:20:33.06,Default,,0000,0000,0000,,Let's use the multivalue dependency for
Dialogue: 0,0:20:33.06,0:20:35.10,Default,,0000,0000,0000,,our first decomposition step.
Dialogue: 0,0:20:35.10,0:20:38.06,Default,,0000,0000,0000,,So we'll create A1 and A2.
Dialogue: 0,0:20:38.06,0:20:43.03,Default,,0000,0000,0000,,A1 will contain all the attributes of our multivalued dependency.
Dialogue: 0,0:20:43.03,0:20:45.00,Default,,0000,0000,0000,,And then A2 will contain
Dialogue: 0,0:20:45.00,0:20:47.08,Default,,0000,0000,0000,,all the remaining attributes along with
Dialogue: 0,0:20:47.08,0:20:51.08,Default,,0000,0000,0000,,the left hand side of our multivalue dependency.
Dialogue: 0,0:20:51.08,0:20:55.03,Default,,0000,0000,0000,,And that turns out to be all of the attributes except the major.
Dialogue: 0,0:20:55.03,0:20:56.02,Default,,0000,0000,0000,,Now let's look at our
Dialogue: 0,0:20:56.02,0:20:57.08,Default,,0000,0000,0000,,decomposed relations and see
Dialogue: 0,0:20:57.08,0:20:58.07,Default,,0000,0000,0000,,what we have in terms of
Dialogue: 0,0:20:58.07,0:21:02.01,Default,,0000,0000,0000,,functional dependencies and multi-value dependencies for them.
Dialogue: 0,0:21:02.01,0:21:03.02,Default,,0000,0000,0000,,So after the decomposition, we don't
Dialogue: 0,0:21:03.02,0:21:05.06,Default,,0000,0000,0000,,have any more multivalued dependency but
Dialogue: 0,0:21:05.06,0:21:07.07,Default,,0000,0000,0000,,our functional dependency actually applies
Dialogue: 0,0:21:07.07,0:21:09.01,Default,,0000,0000,0000,,to both of the decomposed
Dialogue: 0,0:21:09.01,0:21:11.01,Default,,0000,0000,0000,,relations and we still
Dialogue: 0,0:21:11.01,0:21:13.05,Default,,0000,0000,0000,,don't have a key on the left hand side.
Dialogue: 0,0:21:13.05,0:21:17.03,Default,,0000,0000,0000,,So we need to decompose further based on the first functional dependency.
Dialogue: 0,0:21:17.03,0:21:19.10,Default,,0000,0000,0000,,So let's start by decomposing A1.
Dialogue: 0,0:21:19.10,0:21:22.01,Default,,0000,0000,0000,,We'll turn A1 into A3
Dialogue: 0,0:21:22.01,0:21:24.07,Default,,0000,0000,0000,,and A4, and A3 will have
Dialogue: 0,0:21:24.07,0:21:28.00,Default,,0000,0000,0000,,the functional dependency, all three attributes.
Dialogue: 0,0:21:28.00,0:21:29.05,Default,,0000,0000,0000,,And then A4 will have the
Dialogue: 0,0:21:29.05,0:21:30.08,Default,,0000,0000,0000,,left side of the functional
Dialogue: 0,0:21:30.08,0:21:32.09,Default,,0000,0000,0000,,dependency and the remaining
Dialogue: 0,0:21:32.09,0:21:35.09,Default,,0000,0000,0000,,attributes, which in this case is the major.
Dialogue: 0,0:21:35.09,0:21:37.02,Default,,0000,0000,0000,,So now we're finished with A1
Dialogue: 0,0:21:37.02,0:21:39.03,Default,,0000,0000,0000,,and we have a similar problem with A2.
Dialogue: 0,0:21:39.03,0:21:41.03,Default,,0000,0000,0000,,And so we decompose A2
Dialogue: 0,0:21:41.03,0:21:43.10,Default,,0000,0000,0000,,similarly, although we'll discover
Dialogue: 0,0:21:43.10,0:21:45.08,Default,,0000,0000,0000,,that A3 is the same relation in
Dialogue: 0,0:21:45.08,0:21:48.02,Default,,0000,0000,0000,,the decomposition of A2 as we got with A1.
Dialogue: 0,0:21:48.02,0:21:50.00,Default,,0000,0000,0000,,So we actually only add one
Dialogue: 0,0:21:50.00,0:21:52.05,Default,,0000,0000,0000,,more relation now, which is A5.
Dialogue: 0,0:21:52.05,0:21:54.03,Default,,0000,0000,0000,,That contains the social
Dialogue: 0,0:21:54.03,0:21:55.05,Default,,0000,0000,0000,,security number and the college
Dialogue: 0,0:21:55.05,0:21:56.05,Default,,0000,0000,0000,,name from the left side of
Dialogue: 0,0:21:56.05,0:21:58.00,Default,,0000,0000,0000,,the functional dependency and the
Dialogue: 0,0:21:58.00,0:22:00.03,Default,,0000,0000,0000,,hobby, which is the remaining attribute.
Dialogue: 0,0:22:00.03,0:22:04.09,Default,,0000,0000,0000,,And then we cross out A2.
Dialogue: 0,0:22:04.09,0:22:06.00,Default,,0000,0000,0000,,Now the only functional dependencies are multi-value dependencies we have
Dialogue: 0,0:22:06.00,0:22:08.00,Default,,0000,0000,0000,,left do have a key on the left-hand side.
Dialogue: 0,0:22:08.00,0:22:10.05,Default,,0000,0000,0000,,I'll let you verify that for yourself.
Dialogue: 0,0:22:10.05,0:22:11.09,Default,,0000,0000,0000,,And these three relations are our
Dialogue: 0,0:22:11.09,0:22:14.03,Default,,0000,0000,0000,,final decomposition into 4th normal form.
Dialogue: 0,0:22:14.03,0:22:15.06,Default,,0000,0000,0000,,And I think you will agree
Dialogue: 0,0:22:15.06,0:22:17.00,Default,,0000,0000,0000,,that this is a good design
Dialogue: 0,0:22:17.00,0:22:19.06,Default,,0000,0000,0000,,again for the data at hand.
Dialogue: 0,0:22:19.06,0:22:21.00,Default,,0000,0000,0000,,So let's wrap up this long
Dialogue: 0,0:22:21.00,0:22:24.06,Default,,0000,0000,0000,,unit on on dependencies and normal forms with a quick summary.
Dialogue: 0,0:22:24.06,0:22:27.03,Default,,0000,0000,0000,,If we have a relation RABC, a
Dialogue: 0,0:22:27.03,0:22:29.03,Default,,0000,0000,0000,,functional dependency from A
Dialogue: 0,0:22:29.03,0:22:30.01,Default,,0000,0000,0000,,to B tells us that
Dialogue: 0,0:22:30.01,0:22:32.03,Default,,0000,0000,0000,,when we have the same A values,
Dialogue: 0,0:22:32.03,0:22:34.01,Default,,0000,0000,0000,,we have the same B values,
Dialogue: 0,0:22:34.01,0:22:36.06,Default,,0000,0000,0000,,and the Boyce-Codd normal form tells
Dialogue: 0,0:22:36.06,0:22:38.06,Default,,0000,0000,0000,,us to factor that...those attributes
Dialogue: 0,0:22:38.06,0:22:40.04,Default,,0000,0000,0000,,into their own relation so that
Dialogue: 0,0:22:40.04,0:22:43.08,Default,,0000,0000,0000,,we don't repeat that relationship over and over.
Dialogue: 0,0:22:43.08,0:22:45.07,Default,,0000,0000,0000,,For multi-value dependencies, let's say
Dialogue: 0,0:22:45.07,0:22:47.10,Default,,0000,0000,0000,,we have the relation RABCD,
Dialogue: 0,0:22:47.10,0:22:49.06,Default,,0000,0000,0000,,and if we have
Dialogue: 0,0:22:49.06,0:22:51.06,Default,,0000,0000,0000,,the multi-value dependency A multi
Dialogue: 0,0:22:51.06,0:22:53.08,Default,,0000,0000,0000,,determines B, what that tells
Dialogue: 0,0:22:53.08,0:22:55.08,Default,,0000,0000,0000,,us is that we have every combination
Dialogue: 0,0:22:55.08,0:22:57.04,Default,,0000,0000,0000,,for a given A of B
Dialogue: 0,0:22:57.04,0:22:59.03,Default,,0000,0000,0000,,values and CD values
Dialogue: 0,0:22:59.03,0:23:01.04,Default,,0000,0000,0000,,- we called those rest earlier -
Dialogue: 0,0:23:01.04,0:23:03.04,Default,,0000,0000,0000,,and when we have that multiplicative effect
Dialogue: 0,0:23:03.04,0:23:05.04,Default,,0000,0000,0000,,of combinations, again, we take
Dialogue: 0,0:23:05.04,0:23:07.01,Default,,0000,0000,0000,,the A and B attributes and
Dialogue: 0,0:23:07.01,0:23:08.00,Default,,0000,0000,0000,,we put them in a separate
Dialogue: 0,0:23:08.00,0:23:09.03,Default,,0000,0000,0000,,relation so that we can
Dialogue: 0,0:23:09.03,0:23:10.06,Default,,0000,0000,0000,,separate out those facts from
Dialogue: 0,0:23:10.06,0:23:15.01,Default,,0000,0000,0000,,the independent fact of A and its CD values.
Dialogue: 0,0:23:15.01,0:23:17.00,Default,,0000,0000,0000,,Finally, in the design process,
Dialogue: 0,0:23:17.00,0:23:19.00,Default,,0000,0000,0000,,multi-value dependencies are something
Dialogue: 0,0:23:19.00,0:23:22.02,Default,,0000,0000,0000,,we add to functional dependencies, only they're stronger.
Dialogue: 0,0:23:22.02,0:23:23.09,Default,,0000,0000,0000,,So fourth normal form is
Dialogue: 0,0:23:23.09,0:23:27.01,Default,,0000,0000,0000,,a stronger property than Boyce-Codd normal form.
Dialogue: 0,0:23:27.01,0:23:29.00,Default,,0000,0000,0000,,Now usually this design process
Dialogue: 0,0:23:29.00,0:23:30.09,Default,,0000,0000,0000,,works very well and is
Dialogue: 0,0:23:30.09,0:23:34.02,Default,,0000,0000,0000,,very intuitive for many schemas, I hope for the examples that I gave here.
Dialogue: 0,0:23:34.02,0:23:35.00,Default,,0000,0000,0000,,But there are actually a few
Dialogue: 0,0:23:35.00,0:23:37.04,Default,,0000,0000,0000,,shortcomings to using Boyce-Codd
Dialogue: 0,0:23:37.04,0:23:38.08,Default,,0000,0000,0000,,Normal Form or Fourth Normal Form
Dialogue: 0,0:23:38.08,9:59:59.99,Default,,0000,0000,0000,,and we'll cover those in the next video.