0:00
Now, let's similarly motivate the
0:02
concept of multi-value dependencies, and fourth normal form.
0:05
It is actually a little bit more complicated, but it follows the same rough outline.
0:09
Now let's look at a different
0:10
portion of the information
0:11
about applying and let's suppose,
0:13
for now, that we're just concerned
0:14
about students, what colleges they're
0:17
applying to, and what high schools they went to.
0:19
We still have redundancy and update and deletion anomalies.
0:22
For example, a student who
0:24
applies to Stanford is going
0:25
to have that fact captured once
0:27
for every high school that they went to.
0:29
A student who went to
0:31
Palo Alto high School will have
0:33
that fact captured once for every college they apply to.
0:36
In addition, we get a kind of multiplicative effect here.
0:39
Because let's say a student
0:40
applies to C colleges
0:43
and they went to H
0:44
high schools; I know students don't
0:46
go to a lot of high schools
0:47
but let's suppose that this is one that had moved a lot.
0:49
In that case, we're going to have C times H, tuples.
0:53
What we'd really like to have
0:54
is something more on the
0:55
order of C plus H,
0:57
because then we'd be capturing each piece of information just once.
1:00
Now the interesting thing is that
1:01
the badness of this particular
1:03
design is not addressed by
1:05
Boyce-Codd Normal Form, in fact
1:06
this relation is in Boyce-Codd
1:08
Normal Form, because it has no functional dependencies.
1:11
It's not the case that every
1:13
instance of a social security
1:14
number is associated with a
1:16
single college name or a single high school.
1:19
As we will see later, if there
1:20
are no functional dependencies, then the
1:21
relation is automatically in Boyce-Codd
1:23
Normal Form, but it's not
1:25
in and fourth normal form.
1:27
So fourth normal form is associated with what are called multi-value dependencies.
1:31
When we specify a multi-value
1:33
dependency as we've done here
1:34
with the double arrow, what this
1:36
is saying is that if we
1:37
take a particular social security
1:39
number in the relation,
1:41
we will have every combination
1:43
of college names that are
1:45
associated with that social security
1:47
number with every high
1:48
school that's associated with that social security number.
1:51
We'll actually see that when
1:53
we have this multi-value dependency,
1:55
we automatically have this one, too.
1:57
I know it seems a bit complicated,
1:59
and we will formalize it completely,
2:00
but for now now just think
2:01
about the English statement that multi-valued dependency
2:04
is saying that we are going to
2:05
have every combination of those
2:07
two attributes and values
2:08
in those attributes for a given social security number.
2:10
In other words, those values
2:12
are really independent of each other.
2:15
So if we have that situation,
2:17
then what we should really do
2:18
is store each college name
2:20
and each high school for each
2:21
social security number one time,
2:23
and that's what fourth normal form will do for us.
2:26
Fourth normal form, similarly to
2:28
Boyce-Codd normal form, says if
2:30
we have a dependency, then the left hand side must be a key.
2:34
In this case, it's a multi-value dependency
2:36
we're looking at, so it's really
2:37
saying something different but the
2:38
basic idea is the same
2:40
which is that we want
2:41
only one tuple that has
2:44
each value that's appears on
2:45
the left hand side of a
2:47
multi-value dependency So let's
2:48
see what would happen, in this
2:49
example, if we use
2:51
our multi-value dependencies to decompose
2:54
the relation, based on the idea of fourth Normal Form.
2:57
Well it is the intuitive thing that happens.
2:59
We separate the information about
3:01
the college names that a student
3:02
applies to from the information
3:04
about the high schools themselves, and
3:05
then we'll see that that
3:06
we only store each fact
3:08
once and we do get
3:09
the behavior of having C
3:11
plus H tuples instead of having C times H tuples.
3:14
Like with functional dependencies and Boyce-Codd
3:16
Normal Form we'll be completely
3:18
formalizing all of this
3:19
reasoning and the definitions in later videos.