Functional dependencies and normalization

 Functional dependencies play a key role in
differentiating good database designs from database
design. A functional dependency is a type of constraint
that is a generalization of the notion a key
Functional dependencies

Different definitions to define FD
 FD's are constraints on well-formed relations and represent a formalism
on the infrastructure of relation.
Definition: A functional dependency (FD) on a relation schema R is
a constraint X → Y, where X and Y are subsets of attributes of R.
Definition: an FD is a relationship between an attribute "Y" and a
determinant (1 or more other attributes) "X" such that for a given value
of a determinant the value of the attribute is uniquely defined.
i. X is a determinant
ii. X determines Y
iii. Y is functionally dependent on X
iv. X → Y
v. X →Y is trivial if Y Í X

 Definition: An FD X → Y is satisfied in an
instance r of R if for every pair of tuples, t and s:
if t and s agree on all attributes in X then they must
agree on all attributes in Y
A key constraint is a special kind of functional
dependency: all attributes of relation occur on the
right-hand side of the

 The determination of functional dependencies is an
important part of designing databases in the relational
model, and in database normalization and
de normalization.
 The functional dependencies, along with the attribute
domains, are selected so as to generate constraints that
would exclude as much data inappropriate to the user
domain from the system as possible.

 Functional depending set S is irreducible if the set has
three following properties:
 Each right set of a functional dependency of S contains only
one attribute.
 Each left set of a functional dependency of S is irreducible.
It means that reducing any one attribute from left set will
change the content of S (S will lose some information).
 Reducing any functional dependency will change the
content of S.

Properties of functional
dependencies
 The most important properties are Armstrong's
axioms, which are used in database normalization:
 Subset Property (Axiom of Reflexivity):
 If Y is a subset of X, then X → Y
 Augmentation (Axiom of Augmentation):
 If X → Y, then XZ → YZ
 Transitivity (Axiom of Transitivity):
 If X → Y and Y → Z, then X → Z

 From these rules, we can derive these secondary rules:
 Union:
 If X → Y and X → Z, then X → YZ
 Decomposition:
 If X → YZ, then X → Y and X → Z
 Pseudotransitivity:
 f X → Y and WY → Z, then XW → Z

Closure of set of FD
The closure is basically the full set of values that can be
determined from a set of known values for a given
relationship using its functional dependencies. You
use Armstrong's axioms to provide a proof - i.e.
Reflexivity, Augmentation, Transitivity.
Given R and F a set of FD’s that holds in R: The closure
of F in R (denoted F+) is the set of all FD’s in that are
logically implied by F

Closure of Attribute Set
 Given a set  of attributes of R and a set of functional dependencies F,
we need a way to find all of the attributes of R that are functionally
determined by . This set of attributes is called the closure of  under
F and is denoted +. Finding + is useful because:
 if + = R, then  is a superkey for R
 if we find + for all  R, we've computed F+ (except that we'd need to
use decomposition to get all of it).
 An algorithm for computing +:
result :=  repeat
temp := result
for each functional dependency    in F do
if   result then
result := result  
until temp = result

Algorithm for computing a
canonical cover

Normalization
 Normalization is a systematic way of ensuring that
a database structure is suitable for general-purpose
querying and free of certain undesirable
characteristics—insertion, update, and deletion
anomalies—that could lead to a loss of data
integrity

Normal forms
 The normal forms (abbrev. NF) of relational database
theory provide criteria for determining a table's degree
of vulnerability to logical inconsistencies and
anomalies.
 The normal forms are applicable to individual tables;
to say that an entire database is in normal form n is to
say that all of its tables are in normal form n.

Types of NF
 First Normal Form (1NF)
 Second Normal Form (2NF)
 Third Normal Form (3NF)
 Boyce-Codd Normal Form (BCNF)
 Fourth Normal Form (4NF)
 Fifth Normal Form (5NF) etc...

First Normal Form (1NF)
 A table is in 1NF if and only if it is "isomorphic to some
relation", which means, specifically, that it satisfies the
following five conditions:
 There's no top-to-bottom ordering to the rows.
 There's no left-to-right ordering to the columns.
 There are no duplicate rows.
 Every row-and-column intersection contains exactly one
value from the applicable domain (and nothing else).
 All columns are regular [i.e. rows have no hidden components
such as row IDs, object IDs, or hidden timestamps].
 Violation of any of these conditions would mean that the
table is not strictly relational, and therefore that it is not in
1NF.

Second Normal Form (2NF)
 Consider atable that is in first normal form (1NF) must
meet additional criteria if it is to qualify for second normal
form.
 Specifically: a 1NF table is in 2NF if and only if, given any
candidate key K and any attribute A that is not a
constituent of a candidate key, A depends upon the whole
of K rather than just a part of it.
 In slightly more formal terms: a 1NF table is in 2NF if and
only if all its non-prime attributes are functionally
dependent on the whole of a candidate key. (A non-prime
attribute is one that does not belong to any candidate key.)

 Consider a table describing employees' skills:
Employees' Skills
Employee Skill Current Work
Location
Jones Typing 114 Main Street
Jones Shorthand 114 Main Street
Jones Whittling 114 Main Street
Bravo Light Cleaning 73 Industrial Way
Ellis Alchemy 73 Industrial Way
Ellis Flying 73 Industrial Way
Harrison Light Cleaning 73 Industrial Way

 Neither {Employee} nor {Skill} is a candidate key for the
table.
 This is because a given Employee might need to appear
more than once (he might have multiple Skills), and a given
Skill might need to appear more than once (it might be
possessed by multiple Employees).
 Only the composite key {Employee, Skill} qualifies as a
candidate key for the table.
 The remaining attribute, Current Work Location, is
dependent on only part of the candidate key, namely
Employee.
 Therefore the table is not in 2NF.

 A 2NF alternative to this design would represent the
same information in two tables: an "Employees" table
with candidate key {Employee}, and an "Employees'
Skills" table with candidate key {Employee, Skill}:
Employees
Employee Current Work Location
Jones 114 Main Street
Bravo 73 Industrial Way
Ellis 73 Industrial Way
Harrison 73 Industrial Way
Employees' Skills
Employee Skill
Jones Typing
Jones Shorthand
Jones Whittling
Bravo Light Cleaning
Ellis Alchemy
Ellis Flying
Harrison Light Cleaning

Third Normal Form(3NF)
 The third normal form (3NF) is a normal form used
in database normalization.
 3NF was originally defined by E.F. Codd in 1971.
 Codd's definition states that a table is in 3NF if and
only if both of the following conditions hold:
 The relation R (table) is in second normal form (2NF)
 Every non-prime attribute of R is non-transitively
dependent (i.e. directly dependent) on every candidate
key of R.

 Basics of 3NF:
 A non-prime attribute of R is an attribute that does
not belong to any candidate key of R.
 A transitive dependency is a functional dependency in
which X → Z (X determines Z) indirectly, by virtue of
X → Y and Y → Z (where it is not the case that Y → X).
OR
In other words
 A table is in the third normal form if it is the
second normal form and there are no non-key
columns dependant on other non-key columns that
could not act as the primary key.

 Example
An example of a 2NF table that fails to meet the requirements of 3NF
is:
Tournament Winners
Tournament Year Winner Winner Date of
Birth
Indiana Invitational 1998 Al Fredrickson 21 July 1975
Cleveland Open 1999 Bob Albertson 28 September 1968
Des Moines Masters 1999 Al Fredrickson 21 July 1975
Indiana Invitational 1999 Chip Masterson 14 March 1977

 Because each row in the table needs to tell us who won
a particular Tournament in a particular Year, the
composite key {Tournament, Year} is a minimal set of
attributes guaranteed to uniquely identify a row.
 That is, {Tournament, Year} is a candidate key for the
table.

Tournament Winners
Tournament Year Winner
Indiana Invitational 1998 Al Fredrickson
Cleveland Open 1999 Bob Albertson
Des Moines Masters 1999 Al Fredrickson
Indiana Invitational 1999 Chip
Masterson
Player Dates of Birth
Player Date of Birth
Chip Masterson 14 March 1977
Al Fredrickson 21 July 1975
Bob Albertson 28 September 1968
In order to express the same facts without violating
3NF, it is necessary to split the table into two:

Boyce-Codd Normal Form (BCNF)
 Boyce-Codd normal form (or BCNF or 3.5NF) is a normal
form used in database normalization.
 It is a slightly stronger version of the third normal form (3NF).
 A table is in Boyce-Codd normal form if and only if for every
one of its non-trivial [dependencies] X → Y, X is a super key—
that is, X is either a candidate key or a superset thereof.
 BCNF was developed in 1974 by Raymond F. Boyce and Edgar
F. Codd to address certain types of anomaly not dealt with by
3NF as originally defined.
 Only in rare cases does a 3NF table not meet the requirements
of BCNF.
 Depending on what its functional dependencies are, a 3NF
table with two or more overlapping candidate keys may or may
not be in BCNF.

Main Difference Between BCNF
And 3NF
Most relations in 3NF are also in BCNF,
the only time this may not be true is
when there is more than one candidate
key for a relation and at least one of is
composite in 3NF.

Fourth Normal Form(4NF)
 Fourth normal form (or 4NF) requires that
there be no non-trivial multivalve dependencies
of attribute sets on something other than a
superset of a candidate key. A table is said to be
in 4NF if and only if it is in the BCNF and
multivalued dependencies are functional
dependencies. The 4NF removes unwanted
data structures: multivalued dependencies.

Fifth Normal Form(5NF)
 Fifth normal form (5NF and also PJ/NF)
requires that there are no non-trivial join
dependencies that do not follow from the key
constraints. A table is said to be in the 5NF if
and only if it is in 4NF and every join
dependency in it is implied by the candidate
keys.

Functional dependencies and normalization

More Related Content

What's hot

Viewers also liked

Similar to Functional dependencies and normalization

More from daxesh chauhan

Recently uploaded

Functional dependencies and normalization