Transcript CS186: Introduction to Database Systems

CS 405G: Introduction to
Database Systems
26 Final exam review
Chen Qian
University of Kentucky
Final exam



Tuesday 05/05/14
10:30 – 12:00
This classroom.
7/20/2015
Chen Qian @ University of Kentucky
2
Read the mid-term review



ER diagram
Relational Algebra
SQL (important!)
7/20/2015
Chen Qian @ Univ of Kentucky
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
7/20/2015
Chen Qian @ Univ of Kentucky
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 non-trivial, but not completely non-trivial
Completely non-trivial FD: LHS ∩ RHS = ?
7/20/2015
Chen Qian @ University of Kentucky
5
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 {A1, A2, …} functionally determined by
Z (that is, Z -> A1 A2 …)
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
7/20/2015
Chen Qian @ Univ of Kentucky
Using attribute closure
Given a relation R and set of FD’s F

Does another FD X -> Y follow from F?



Is K a super key of R?



Compute X+ with respect to F
If Y  X+, then X -> Y follow from F
Compute K+ with respect to F
If K+ contains all the attributes of R, K is a super key
Is a super key K a key of R?

Test where K’ = K – { a | a K} is a superkey of R for all
possible a
7/20/2015
Chen Qian @ University of Kentucky
7
Rules of FD’s

Armstrong’s axioms




Reflexivity: If Y X, then X -> Y
Augmentation: If X -> Y, then (X,Z) -> (Y,Z) for any Z
Transitivity: If X -> Y and Y -> Z, then X -> Z
Rules derived from axioms


Splitting: If X -> YZ, then X -> Y and X -> Z
Combining: If X -> Y and X -> Z, then X -> YZ
7/20/2015
Chen Qian @ University of Kentucky
8
First Normal Form ( 1NF )

A relation is in first normal form if the domain of each
attribute contains only atomic values, and the value of
each attribute contains only a single value from that
domain.
7/20/2015
Chen Qian @ University of Kentucky
9
2nd Normal Form




An attribute A of a relation R is a nonprimary attribute if
it is not part of any key in R, otherwise, A is a primary
attribute.
R is in (general) 2nd normal form if every nonprimary
attribute A in R is not partially functionally dependent
on any key of R
How to identify 2NF
How to decompose relations
7/20/2015
Chen Qian @ University of Kentucky
10
Third normal form
• 3NF requires that there are no non-trivial
functional dependencies of non-key attributes on
something other than a superset of a candidate key.
• Recall: non-trivial FD means LHS has no
intersection with RHS.
• In summary, all non-key attributes are mutually
independent.
7/20/2015
Chen Qian @ University of Kentucky
11
BC normal form
• A table is said to be in the BCNF if and only if
it is in the 3NF and every non-trivial, leftirreducible functional dependency has a
candidate key as its determinant.
•How to find violations
•How to decompose relations
7/20/2015
Chen Qian @ University of Kentucky
12
Conflicts


precedence graph
conflict-serializable schedule
7/20/2015
Chen Qian @ University of Kentucky
13
Lock

exclusive lock and shared locks

Two-phase locking


Strict 2PL


All lock requests precede all unlock requests
Only release locks at commit/abort time
Examples
7/20/2015
Chen Qian @ University of Kentucky
14
Disk

Disk structure


Cylinder, platter, track, etc.
Random disk access

Seek time + rotational delay + transfer time
7/20/2015
Chen Qian @ University of Kentucky
15
File Storage



Heap file as a list or page directory
Record format
Page layout
7/20/2015
Chen Qian @ University of Kentucky
16
Tree-Structured Indexes: Introduction


Tree-structured indexing techniques support both range
selections and equality selections.
ISAM =Indexed Sequential Access Method

static structure; early index technology.

B+ tree: dynamic, adjusts gracefully under inserts and
deletes.

Operations: search, range query, insert, delete
(important!)

Understand the examples in the exercise slides!
7/20/2015
Chen Qian @ University of Kentucky
17
Hashing

Static hashing

Dynamic hashing


7/20/2015
Extensible and linear hashing
Understand examples in slides
Chen Qian @ University of Kentucky
18
Final Exam






1. True/False
2. Functional Dependency and Normal Forms
3. SQL
4. Index (B+ Tree)
5. Index (B+ Tree)
6. Storage
7/20/2015
Chen Qian @ University of Kentucky
19

Good luck and hope you enjoy this class!

Don’t forget:

You give me a grade
7/20/2015
Chen Qian @ University of Kentucky
20