0:00
Finally, let's come back to our example.
0:01
And I think when we start writing
0:02
functional dependencies for our actual
0:04
relations it'll give you a good idea of what they're really capturing.
0:07
So let's write a few
0:09
functional dependencies for our student
0:10
relation based on what we
0:11
expect to be true in
0:12
the real world in the data that we're capturing in the relation.
0:16
So here's a first example, Social
0:18
Security number functionally determines S-name, the student's name.
0:22
So what we say, we have
0:23
multiple tuples about a
0:25
particular student and they have
0:26
the same social security number, say
0:28
two tuples about student
0:31
123, we're expecting them to
0:32
have the same name in fact,
0:33
we're requiring them to have the same name.
0:35
And presumably because 1 to
0:37
3 is sort of identifying the
0:38
student that would be
0:40
a natural functional dependency that
0:41
would hold in this case and
0:43
similarly we would expect social security
0:45
number to determine address, although
0:47
we're already making an assumption
0:48
about the real world here, if
0:50
we have this particular functional dependency,
0:52
then we're saying a student doesn't move.
0:54
They don't have multiple addresses.
0:55
Every tuple that describes that
0:57
student by their social security
0:59
number will have the same address.
1:01
Let's go to the high school and see what might be going on there.
1:04
So I mentioned that the
1:05
high school code, what I'm
1:07
trying to capture there is
1:08
a unique code for each
1:10
high school that might be filled
1:11
in college application then we
1:13
would expect the high school code
1:14
to determine the high school name.
1:16
Every time we have the particular
1:18
high school code, maybe for different
1:19
students, it would have the same
1:20
name and also it would have the same city.
1:23
So that's an example of a
1:25
functional dependency with two attributes on the right hand side.
1:28
Now, let's look at one
1:30
that's a little more complicated, which
1:31
is one that has two attributes
1:33
on the left hand side instead.
1:35
That actually turns out to be a more interesting case.
1:37
In fact, in this particular case,
1:39
we can probably reverse the arrow
1:41
and have a functional dependency in the other direction.
1:44
If we have a combination of
1:45
high school name and high school
1:46
city, I'm going to
1:48
assume that's unique, that there aren't,
1:50
there's never two high
1:51
schools with the same name in the same city.
1:53
And if that's the case,
1:54
if that's unique, then we would
1:56
expect a functional dependency to the high school code.
1:58
Any time we have the same
2:00
name and city, we're talking about
2:01
the same high school so we should have the same code.
2:04
What other examples do we have?
2:06
If we assume that there's one
2:07
GPA for each student then
2:09
we'd have the social security number
2:11
determines the GPA and we
2:13
already talked about GPA determines
2:15
priority and another
2:17
example, actually if we
2:19
put these two together we should
2:20
see well if we have the
2:21
same social security number twice we should have the same priority.
2:25
And you may be thinking
2:26
well, that's kind of a
2:27
transitive rule if it takes these two and produces that one.
2:29
And indeed it is.
2:30
And we'll talk about rules for functional dependencies later.
2:32
And there may be more in this case.
2:35
Now let's take a look at functional dependencies for our apply relation.
2:39
Actually, this one is a
2:40
little trickier, it's even possible
2:42
there are no functional dependencies at all.
2:44
It really depends on the real world data, the real world constraints.
2:48
One possibility for example is
2:50
that every college has a
2:51
particular single date on which it receives its application.
2:55
So if that were the case, then
2:56
we'd have the college name determines the date.
2:59
In other words, every application
3:01
for a particular college must have the same date.
3:04
Another constraint might be that
3:06
students are only allowed to
3:07
apply to a single major at each college they apply to.
3:11
So if that were the case, this
3:12
is another one with two attributes
3:14
on the left hand sid, we'd
3:15
say that the social security number,
3:17
together with the college, implies the major.
3:20
In other words, we cannot have
3:22
a student and college combination
3:24
with two different majors and that captured that constraint.
3:28
Maybe we have a constraint
3:29
that students are only allowed to apply to colleges in one state.
3:33
That seems rather unlikely, but I
3:34
was struggling to find functional
3:36
dependencies for this case.
3:38
In that case, we'd have this
3:39
function dependency, again, saying a
3:41
student could only apply to colleges in a single state.
3:43
For the apply relation specifically, again,
3:46
it's really the real world constraints
3:48
that drive which functional dependencies hold for the relation.
3:50
But it's important to understand those
3:52
constraints so they can be
3:53
translated to functional dependencies, which
3:55
then can drive a good relational design.