Transcript functional dependency

Database Design
and
Functional Dependencies
CIS 205, Fall 2004
Last edited September 11, 2004
By C. Herbert ©2004, all rights reserved
Database Keys
and Functional Dependencies
A database primary key is an example of what
mathematicians call a “functional dependency”.
We can think of a function as a black box with input
and output. If the box is a function, then each time
we put in a specific input, we get the same output.
INPUT
function
OUTPUT
CIS 205 Relational Model p.2
Database Keys
and Functional Dependencies
Consider the following black box:
Every time we input 3, we get 7 as the output.
3
7
CIS 205 Relational Model p.3
Database Keys
and Functional Dependencies
Every time we input 4, we get 9 as the output.
4
9
CIS 205 Relational Model p.4
Database Keys
and Functional Dependencies
In fact for any value X, every time we input that x,
we always get the same value Y as the output for
that input.
Such a black box would be a function.
X
function
Y
CIS 205 Relational Model p.5
Database Keys
and Functional Dependencies
Mathematicians us the notation y = f(x) to denote a
function. Think of x as the input and Y as the output.
For example, y = 2(x) + 1 is a function, because we
get a unique value of y for each x.
y = 2(3) +1
y=7
input 3  output 7
Y = 2(4) +1
y=9
input 4  output 9
CIS 205 Relational Model p.6
Database Keys
and Functional Dependencies
Here’s an example that is not a function.
Consider y = squareroot(x)
y x
y  16
y = squareroot(16)
y = 4 or -4
4 * 4 = 16
-4 * -4 = 16
input 16  output 4 or -4
Since we could get 4 or -4 as our output, this is not a
function. We don’t always get the same output for a
specific input.
CIS 205 Relational Model p.7
Database Keys
and Functional Dependencies
What’s this got to do with databases?
In order to look up data, our primary key must work
like a functional dependency.
For example, in a student record keeping system,
every time we put in your student number, we better
get your record.
your
student #
your
student record
CIS 205 Relational Model p.8
Database Keys
and Functional Dependencies
We could use the Y = f (x) notation from mathematics.
student record = f (student #)
Student record is a function of student number. Which
student record we get, depends on which student
number we input.
We have a functional dependency.
student #
function
student record
CIS 205 Relational Model p.9
Database Keys
and Functional Dependencies
In general,
Each instance is a function of the primary key.
instance = f (primary key)
primary
key
instance of
the entity
CIS 205 Relational Model p.10
Database Keys
and Functional Dependencies
So are the values of the attributes for that instance.
name = f (student number)
address = f (student number)
Major, GPA, etc. = f (student number)
It is the functional nature of the primary key that allows
us to locate specific information in a database.
CIS 205 Relational Model p.11