1
Relational Database Design Theory
CPS 116
Introduction to Database Systems
2
Announcements (Thu. Sep. 15)
Homework #1 due next Tuesday
Webbased submission preferred over hard copies
Watch your email for announcements of “refreshes”
of
/home/dbcourse/
on your virtual machine
3
Motivation
How do we tell if a design is bad, e.g.,
StudentEnroll
(
SID
,
name
,
CID
)?
This design has redundancy, because the name of a student is
recorded multiple times, once for each course the student is taking
• Update, insertion, deletion anomalies
How about a systematic approach to detecting and
removing redundancy in designs?
Dependencies, decompositions, and normal forms
SID
name
CID
142
Bart
CPS116
142
Bart
CPS114
857
Lisa
CPS116
857
Lisa
CPS130
…
…
…
4
Functional dependencies
A functional dependency (FD) has the form
X
→
Y
,
where
X
and
Y
are sets of attributes in a relation
R
X
→
Y
means that whenever two tuples in
R
agree
on all the attributes in
X
, they must also agree on
all attributes in
Y
X
Y
Z
a
b
c
a
b
?
…
…
…
Must be
b
Could be anything
5
FD examples
Address
(
street_address
,
city
,
state
,
zip
)
street_address
,
city
,
state
→
zip
zip
→
city
,
state
zip
,
state
→
zip
?
This is a trivial FD
Trivial FD: LHS
⊇
RHS
zip
→
state
,
zip
?
This is nontrivial, but not completely nontrivial
Completely nontrivial FD: LHS
Å
RHS =
∅
6
Keys redefined using FD’s
A set of attributes
K
is a key for a relation
R
if
K
→
all (other) attributes of
R
That is,
K
is a “super key”
No proper subset of
K
satisfies the above condition
That is,
K
is minimal
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
2
7
Reasoning with FD’s
Given a relation
R
and a set of FD’s
F
Does another FD follow from
F
?
Are some of the FD’s in
F
redundant (i.e., they follow
from the others)?
Is
K
a key of
R
?
What are all the keys of
R
?
8
Attribute closure
Given
R
, a set of FD’s
F
that hold in
R
, and a set of
attributes
Z
in
R
:
The closure of
Z
(denoted
Z
+
) with respect to
F
is
the set of all attributes {
A
1
,
A
2
, …} functionally
determined by
Z
(that is, Z
→
A
1
A
2
…)
Algorithm for computing the closure
Start with closure =
Z
If
X
→
Y
is in
F
and
X
is already in the closure, then
also add
Y
to the closure
Repeat until no more attributes can be added
9
A more complex example
StudentGrade
(
SID
,
name
,
email
,
CID
,
grade
)
SID
→
name
,
email
email
→
SID
SID
,
CID
→
grade
(Not a good design, and we will see why later)
10
Example of computing closure
F
includes:
SID
→
name
,
email
email
→
SID
SID
,
CID
→
grade
{
CID
,
email
}
+
= ?
email
→
SID
Add
SID
; closure is now {
CID
,
email
,
SID
}
SID
→
name
,
email
Add
name
,
email
; closure is now {
CID
,
email
,
SID
,
name
}
SID
,
CID
→
grade
Add
grade
; closure is now all the attributes in
StudentGrade
11
Using attribute closure
Given a relation
R
and set of FD’s
F
Does another FD
X
→
Y
follow from
F
?
This is the end of the preview.
Sign up
to
access the rest of the document.
 Spring '09
 Database normalization, Sid, CID, sid sid, email SID SID

Click to edit the document details