Database management systems

1
Database management systems

• Functional Dependencies

2
Functional Dependencies

• Definition
• An integrity constraint between two sets of
  attributes
• Think of it as a many-to-one relationship between
  two sets of attributes

3
Functional dependencies

• Generalizes the concept of a key
• X ? Y
• X functionally determines Y
• Y is functionally determined by X
• FD X ? Y
• i.e., when two tuples agree on their value for
  the X domain, they agree on their value for the Y
  domain

4
Functional Dependencies

• Legal instances satisfy all ICs, including FDs
• FD are a property of the intension, not the
  extension
• Primary key is a particular case of a FD (but not
  all FDs are keys)
• F denotes the closure of F

5
Functional Dependency
Determinant
AB ? C
Dependent
6
Closure

• Set F of Functional Dependencies
• EmpProj SSN, Pnumber, Hours, Ename, Pname,
  Plocation
• F SSN ? Ename
• Pnumber ? Pname, Plocation
• SSN, Pnumber ? Hours
• Closure sets with respect to F
• SSN SSN, Ename
• Pnumber Pnumber, Pname, Plocation
• SSN, Pnumber SSN, Pnumber, Ename, Pname,
  Plocation, Hours

7
Attribute Closure

• Determine FDs that can easily be determined, then
  for a given dependency to see if its in the
  closure of a set F of FDs
• Repeatedly use Armstrongs Axioms to infer
  additional dependencies

8
Armstrongs Axioms

• 3 rules of inference can generate all FDs
• Reflexivity
• If X ?Y, then X ? Y
• Augmentation
• If X ? Y, then XZ ? YZ for any Z
• Transitivity
• If X ? Y and Y ? Z, then X ? Z
• But keep in mind 2 other rules that are useful
• Union
• If X ? Y and X ? Z, then X ? YZ
• Decomposition
• If X ? YZ, then X ? Y and X ? Z

9
Example

• A ? B, ABCD ? E, EF ? G, EF ? H, and ACDF ? EG
• First rewrite
• ACDF ? E and ACDF ? G
• Then consider ACDF ? G
• (implied by A ? B, ABCD ? E, and EF ? G)
• Delete it (and, in the same way, ACD ? E replaces
  ABCD ? E)
• Minimal Set A ? B, ACD ? E, ACDF ? G, and ACDF ?
  H.
• Single attribute on right, Minimize left, Delete
  redundant FDs

10
Another example

• A employee number, B department number, C
  managers employee number, D project number for
  a project unique to a manager, E department
  name, F time specified for a project
• A -gt BC, B -gt E, CD -gt EF
• Does AD-gtF hold?
• A-gt BC (given), A-gtC (decomposition), AD-gtCD
  (augmentation), CD-gtEF (given), AD-gtEF
  (transitivity), AD-gtF (decomposition)

11
Minimal Set

• Every dependency has a single attribute for its
  right side
• No X ? A in F can be replaced with Y ? A where Y
  is a subset of X and still have dependencies
  equivalent to F
• No dependency can be removed and still have a set
  of dependency equivalent to F

12
Cover

• Let S1 and S2 be two sets of FDs. If every FD
  implied by S1 is implied by S2 (S1?S2), S2 is a
  cover for S1.
• So, if FDs are enforced for S2, they are enforced
  for S1.
• If S1 is also a cover for S2, they are
  equivalent.

13
Minimal Set

• If the right-hand (dependent) side of every FD in
  S involved just one attribute, and
• if the left-hand (determinant) side of every FD
  in S is irreducible (cant discard another
  attribute without making a new set not equivalent
  to S - aka, changing the closure of S), and
• no FD in S can be discarded without changing the
  closure of S, then the set S of FDs is
  irreducible.

14
The other direction

• For P, the following FDs are part of the closure
  
• P -gtPname, P -gt Color, P -gt Weight, P -gt City
• The following FDs are not irreducible
• P -gt Pname, Color
• P -gt Weight
• P -gt City
• (right-hand side is not a single attribute)
• P, Pname -gt Color
• P -gt Pname
• P -gt Weight
• P -gt City
• (left-hand side can be further reduced without
  changing closure
• P -gt P
• P -gt Pname
• P -gt Color
• P -gt Weight
• P -gt City
• (first FD can be dropped without changing
  closure)

15
Functional Dependency from the SQL perspective
Things to consider If X functionally determines
Y, then Y is functionally dependent on X. SQL
Translation In a legal instance with one or more
determinant columns and one dependent columns, if
you create a SELECT statement with a WHERE clause
containing for some value in the determinant
columns, any tuples returned by the dependent
column in your FROM clause must always be the
same for all tuples. SELECT DependentColumn FROM
Table WHERE DeterminantColumn valueAND
DeterminantColumnN valueN
16
Normalization
Product Support Coverage
ProductNumber,Date -gt Time, EmployeeID,
PhoneNumber EmployeeID, Date, Time -gt
ProductNumber PhoneNumber, Date, Time -gt
EmployeeID, ProductNumber EmployeeID, Date -gt
PhoneNumber Date -gt PayRate PayRate -gt Withholding
17
Illustrate FDs

• ProductNumber,Date -gt Time, EmployeeID,
  PhoneNumber
• EmployeeID, Date, Time -gt ProductNumber
• PhoneNumber, Date, Time -gt EmployeeID,
  ProductNumber
• EmployeeID, Date -gt PhoneNumber
• Date -gt PayRate
• PayRate -gt Withholding

18
Decompose
19
Sample Set