Functional Dependency

1
Functional Dependency

• Presenter
• Usman Saeed

2
Definition

• Definition
• constraints on relations()
• characteristic of an attribute where values are
  determined by another attributes values
• A ? B if for every valid instance of A, that
  value of A uniquely determines the value of B
• Notation
• a?ß (a determines ß)
• (a?ß may take the form AB?C, A?BC, etc.)

3
Uses of Functional Dependencies

• To determine if a relation is in a Normal Form.
• To specify constraints on the set of legal
  relations (functional dependencies to focus on)
• To determine if a decomposition would cause data
  loss (R decomposed to R1 and R2 but, R1 X R2 ?
  R)

4
Normalization

• Database normalization is a process of removing
  redundant data from tables, to improve storage
  efficiency and data integrity.
• We can measure the efficiency of the databases
  using classifications called normal forms (or
  NF).
• Normalization generally involves splitting
  existing tables into multiple ones, which must be
  re-joined or linked each time a query is issued.

5
First Normal Form(1NF)

• Atomic Form A domain is Atomic if
• elements of that domain are indivisible
• Example A set of names is a non atomic value.
• A relation schema S is said to be in the first
  normal form if the domains of all the attributes
  of S are atomic.

6
Example Table
7
Example Table (Problems)

• In the Table, we have two violations of First
  Normal Form
• First, we have more than one Actor field,
• Second, our Genre field is multivalued (non
  atomic). With more than one value in a single
  field, it would be very difficult to search for
  all the movies on a given Genre.

8
Corrected Table(1 NF)
9
Second Normal Form

• A relation is in second normal form if it is in
  first normal form AND every nonkey attribute is
  fully functionally dependant on the primary key.
• Hence the table in my example is in violation of
  this rule because we have two rows for the same
  movie.
• This can be rectified by making separate tables
  for
• Genre and Actors.

10
Summary

• In general the 1nf is used to get rid of
  redundancies in the columns. As seen in the
  example.
• Secondly 2nf deals with redundancies in the rows.

11
Third Normal Form

• The relation has to be 2NF
• Third normal form (3NF) requires that there are
  no functional dependencies of non-key attributes
  on something other than a candidate key.

12
BCNF

• A relation R is said to be in BCNF if whenever X
  - A holds in R, and A is not in X, then X is a
  candidate key for R.
• The BCNF differs from the 3NF only when there are
  more than one candidate keys and the keys are
  composite and overlapping.

13
Example

• Schema enrol (sno, sname, cno, cname,
  date-enrolled)
• Let us assume that the relation has the following
  candidate keys
• (sno, cno) (sno, cname) (sname, cno) (sname,
  cname)

14

• The relation is in 3NF but not in BCNF because
  there are dependencies
• sno - snamecno - cname

15
Bibliography

• http//www.cs.jcu.edu.au/Subjects/cp1500/1998/Lect
  ure_Notes/normalisation/bcnf.html
• Professor Lees Notes
• Wikipedia

16
Thank you