Databases : Functional Dependencies

1
Databases Functional Dependencies

• 2007, Fall
• Pusan National University
• Ki-Joune Li

2
Properties of Table (or Relation)

• We should carefully observe the properties of
  table
• Functional Dependency
• Key
• Decomposition of Table

3
Definition of Functional Dependency

• FD (Functional Dependency) on a Relation R
• iff A1 A2 A3 An ? B where A1 , A2 , A3 , ,
  An , B are attributes of R
• A set of attributes A1 A2 A3 An functionally
  determines B
• More than one Bs
• A1 A2 A3 An ? B1
• A1 A2 A3 An ? B2
• A1 A2 A3 An ? Bk
• A1 A2 A3 An ? B1 B2 Bk

A1 A2 A3 An
B1 B2 B3 Bk
4
Functional Dependency Example

• A Relation
• Movies (title, year, length, filmType,
  studioName, starName)
• (title year) ? length
• (title year) ? filmType
• (title year) ? studioName
• (title year) ? length filmType studioName
• ? (title year) ? starName more than one star in
  a film

5
Key

• Given a relation R
• A set of one or more attributes A1, A2, A3, ,
  An is a KEY iff
• the set functionally determines all other
  attributes and
• no proper subset of A1, A2, A3, , An
  functionally determines other attributes
  (Minimal)
• Primary Key
• If a relation has more than one keys, a key is
  defined as primary key
• Super Key
• a set of attributes containing a key
• No minimality condition
• Example
• Movies (title, year, length, filmType,
  studioName, starName)
• What are keys ?

6
How to discover keys

• From E-R Diagram Underlined Attributes
• It means that keys are defined based on the
  understanding of the real world
• Example Movies (title, year, length, filmType,
  studioName, starName)
• (year, starName) is not key if a star can make
  more than one film per year
• (year, starName) is a key if a star is allowed to
  make only one film per year
• Relation (A1, A2, B) for relationship between R1
  and R2
• One-One
• One-Many
• Many-One
• Many-Many

7
Rules about Functional Dependencies

• Functional Dependency
• An important property of Relation (or Table)
• Some interesting properties or rules of FD
• Transitive Rule
• A ? B and B ? C then A ? C
• Splitting/Combining Rule
• A1 A2 A3 An ? B1, A1 A2 A3 An ? B2, , A1 A2 A3
  An ? Bk
• iff A1 A2 A3 An ? B1 B2 Bk
• Trivial FD Rule Given a FD A1 A2 A3 An ? B
• FD is trivial if B is one of A1 A2 A3 An
  really trivial
• FD is Completely non-trivial B is not in A1 A2
  A3 An

8
Rules about Functional Dependencies

• Trivial Dependency Rule
• A1 A2 An ? B1 B2 Bm is equivalent to A1 A2
  An ? C1 C2 Ck if C1 C2 Ck ? B1 B2 Bm
  and for any C ? C1 C2 Ck , C ? A1 A2 An
  
• Example (year, title) ? (studioName, year),
  (year, title) ? studioName

Unnecessary
B1 B2 B3 Bm
A1 A2 A3 An
C1 C2 C3 Ck
9
Closure of Attributes

• Closure A1 A2 An
• A1 A2 An is a set of attributes and S is a
  set of FD
• Closure of A1 A2 An under FS set of
  attributes B such that
• A1 A2 An ? B
• Example
• Given attributes A, B, C, D, E, and F
• S A B ? C, B C ? A D, D ? E, and C F ? B
• What is the closure of A, B ?