EECS 647: Introduction to
Database Systems
Instructor: Luke Huan
Spring 2009
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Database Systems: Understanding Redundancy, Functional Dependency, and Decomposition - Pro, Study Guides, Projects, Research of Deductive Database Systems
A portion of lecture notes from a database systems course taught by luke huan at the university of kansas during spring 2009. The notes cover the topics of redundancy, functional dependency, and decomposition in database design. The instructor provides examples and explanations to help students understand the importance of eliminating redundancy and the role of functional dependencies in database design.
Typology: Study Guides, Projects, Research
Pre 2010
1 / 21
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download Database Systems: Understanding Redundancy, Functional Dependency, and Decomposition - Pro and more Study Guides, Projects, Research Deductive Database Systems in PDF only on Docsity!
EECS 647: Introduction to
Database Systems^ Instructor: Luke Huan
Spring 2009
2/23/^
Luke Huan Univ. of Kansas
Database Design
2/23/^
Luke Huan Univ. of Kansas
Motivation
z^ How do we tell if a design is bad, e.g.,^ WorkOn
( EID
,^ Ename
,^ PID, Pname, Hours
z^ This design has
redundancy
, because the name of an employee
is recorded multiple times, once for each project the employeeis taking
PnameB2B platformCRMCRMB2B platform
Ben Liu 9 1123
John Smith 10 1234
Ename John SmithSusan Sidhuk
Hours^3040
PID
EID
2/23/^
Luke Huan Univ. of Kansas
Why redundancy is bad?
z^ Waste disk space. z^ What if we want to perform update operations to therelation^ z^
INSERT an new project that no employee has beenassigned to it yet. z UPDATE the name of “John Smith” to “John L. Smith” z DELETE the last employee who works for a certainproject
PnameB2B platformCRMCRMB2B platform
Ben Liu 9 1123
John Smith 10 1234
Ename John SmithSusan Sidhuk
Hours^3040
PID
EID
2/23/^
Luke Huan Univ. of Kansas
Unnecessary decomposition
z^ Fine: join returns the original relation z^ Unnecessary: no redundancy is removed, and now
EID
is stored twice!
[email protected]@[email protected] Ben Liu 1123
John Smith 1234
Ename Susan Sidhuk EID (^1023) Ben Liu 1123
John Smith 1234
Ename Susan Sidhuk EID 1023
[email protected]@[email protected] EID 123411231023
2/23/^
Luke Huan Univ. of Kansas
Bad decomposition
Hours^3040 10 1023
PID
EID
PID
EID
Hours EID
2/23/^
Luke Huan Univ. of Kansas
Strategies
z^ Functional dependency z^ Decomposition z^ Normal forms
2/23/^
Luke Huan Univ. of Kansas
Functional dependencies
z^ A functional dependency (FD) has the form
X^ →
Y ,
where
X^ and
Y^ are sets of attributes in a relation
R
z^ 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 z^ t[X] = t^1
[X]^2 ⇒^ t^1 [Y] = t
[Y] 2
a
c
b
a
Z
Y
X
b
a
c
b
a
Z
Y
X
Must be “b”
Could be anything,e.g. d
d
b
a
c
b
a
Z
Y
X
2/23/^
Luke Huan Univ. of Kansas
Keys redefined using FD’s
Let^ attr
( R ) be the set of all attributes of
R , a set of
attributes
K^ is a (candidate) key for a relation
R^ if
z^ K^
→^ attr(
R) - K,
and
z^ That is,
K^ is a “super key”
z^ No proper subset of
K^ satisfies the above condition
z^ That is,
K^ is minimal (full functional dependent)
z^ Address
( street_address
,^ city
,^ state
,^ zip )
z^ {street_address, city, state, zip} z^ {street_address, city, zip} z^ {street_address, zip} z^ {zip}
Super keySuper key(Candidate) KeyNon-key
2/23/^
Luke Huan Univ. of Kansas
Rules of FD’s
z^ Armstrong’s axioms^ z^
Reflexivity: If
Y^ ⊆^
X , then
X^ →
Y
z^ Augmentation: If
X^ →
Y , then
XZ^ →
YZ^ for any
Z
z^ Transitivity: If
X^ →
Y^ and
Y^ →
Z , then
X^ →
Z
z^ Rules derived from axioms^ z^
Splitting: If
X^ →
YZ , then
X^ →
Y^ and
X^ →
Z
z^ Combining: If
X^ →
Y^ and
X^ →
Z , then
X^ →
YZ
2/23/^
Luke Huan Univ. of Kansas
Dealing with Nonkey Dependency: BCNF
z^ A relation
R^ is in Boyce-Codd Normal Form if
z^ For every non-trivial FD
X^!^ Y
in^ R
,^ X^ is a super key
z^ That is, all FDs follow from “key
!^ other attributes”
z^ When to decompose^ z^
As long as some relation is not in BCNF
z^ How to come up with a correct decomposition^ z^
Always decompose on a BCNF violation (details next) ) Then it is guaranteed to be a lossless join decomposition!
2/23/^
Luke Huan Univ. of Kansas
BCNF decomposition algorithm
z^ Find a BCNF violation^ z^
That is, a non-trivial FD
X^ →
Y^ in^
R^ where
X^ is not a
super key of
R
z^ Decompose
R^ into
R and^1
R , where^2
z^ R^1
has attributes
X^ ∪ Y
z^ R^2
has attributes
X^ ∪ Z , where
Z^ contains all attributes of
R^ that are in neither
X^ nor
Y^ (i.e.
Z = attr(R) – X – Y
)
z^ Repeat until all relations are in BCNF
2/23/^
Luke Huan Univ. of Kansas
19
Another example
WorkOn
( EID
,^ Ename
,^ PID
,^ hours
BCNF violation:
→^ EID
StudentID
,^ EID
) StudentGrade’
,^ Ename
,^ PID
,^ hours
BCNF
BCNF violation:
→^ Ename
StudentName
,^ Ename
) Grade
,^ PID
,^ hours
BCNF
BCNF
2/23/^
Luke Huan Univ. of Kansas
Exercise
z^ Property(Property_id#, County_name, Lot#, Area,Price, Tax_rate
z^ Property_id#
→^ County_name, Lot#, Area, Price, Tax_rate z County_name, Lot#
→^ Property_id
#, Area, Price,
Tax_rate z County_name
→^ Tax_rate
z^ area
→^ Price