Database management systems

1
Database management systems

• Normalization

2
Normal Forms

• The key, the whole key, and nothing but the key
• Assumptions
• FDs exist
• A designated primary key can be found
• Remember, a key is a FD, but a FD is not always a
  key
• A property of a relation schema indicating the
  type of redundancy in the relation schema
• The goal is to remove redundancy based on
  dependencies
• The desire is to minimize additional integrity
  constraints

3
Normal Forms

• Considerations
• Relational design by analysis
• Normal forms are based on functional dependencies
  (FDs)
• Intuitive, perhaps, but identifying a strictly
  controlled procedure allows a programmatic
  process
• Should consider 2 additional properties
• Lossless join (nonadditive join property)
• required
• Dependency preservation property
• use when possible

4
Lossless Joins and Dependency Preservation

• If relation R and FDs F hold over R, then
  decomposing R into R1 and R2 is lossless if the
  closure of F contains either
• FD R1 n R2 -gt R1
• or
• FD R1 n R2 -gt R2
• If the closure of the attributes in R1,
  independent of those attributes in R2, unioned
  with the closure of attributes of R2, independent
  of those attributes in R1, are equivalent to the
  closure F, then dependency is preserved

5
Normal Forms

• Definitions
• Superkey
• RA1,A2,,An
• A set of attributes S ? R
• No 2 tuples (t1,t2) in any legal relation state r
  of R has t1St2S
• Key
• A superkey that is disqualified by any attribute
  being removed,
• Must be minimal (cant remove another attribute
  without disqualifying as key of R)
• Candidate Key
• A set of attributes in a relation satisfying key
  definition
• Primary Key
• Arbitrarily designated
• Primary Attribute
• A member of a candidate key

6
Fully normalized table example
7
Levels of Normalization
1 NF
2 NF
3 NF
BCNF
8
First Normal Form (1NF)

• 1NF disallows multivalued attributes, or
  composite attributes, by requiring only single
  atomic (indivisible) values in the domain of an
  attribute

9
First Normal Form
10
Business Rules Example

• Staffing is on a per project activity (activities
  within projects) basis
• Managers and their departments are assigned to
  projects
• Departments are assigned to project managers
• Project Managers are assigned to projects

11
Second Normal Form (2NF)

• 2NF requires full functional dependency on the
  whole key
• If FD X?Y, removal of A eliminates the FD
• For any attribute A?X, (X-A)?Y
• If A can be removed and FD remains, X?Y is a
  partial functional dependency (a violation of 2NF)

\
12
2NF violations

• Partial dependencies X is a subset of some key K

K
X
A
13
2NF
Staffing is on a per project activity ( and
activities within projects) basis Managers and
their departments are assigned to projects
PN,AN?STAFF PN ?PM,DN
14
Third Normal Form (3NF)

• 3NF requires elimination of transitive dependency
  of nonprime attributes on the primary key (i.e.,
  dependency is directly on the key)
• X?Y,Y?Z?X?Z
• If a functional dependency X?A holds in R, 1 of
  these must be true
• X is a superkey of R, or
• A?X, or
• Each attribute in A-X is part of some candidate
  key for R

15
3NF violations

• Transitive dependencies X is not a subset of
  the key

K
X
A
X
16
3NF
Departments are assigned to project
managers Project Managers are assigned to projects
PN?PM ?D
17
1NF to 2NF
18
2NF to 3NF
19
Boyce-Codd Normal Form (BCNF)

• BCNF is a simpler form of 3NF that is more
  restrictive
• If a functional dependency X?A holds in R, then
  either
• A?X (trivial), or
• X is a superkey of R
• Each attribute is identified by nothing but the
  key
• Sometimes too restrictive, may not be dependency
  preserving with regard to closure, for example
• R has ABC, F includes A ?B,B ?C,C ?A, so
  (FAB?FBC)? F

20
BCNF
KEY
A1
A1
A1

21
BCNF table example
22
Decomposition into BCNF

• Assumptions
• We have a schema for Contracts
• The meaning of a tuple is that contract C is an
  agreement that supplier S will supply Q items of
  part P to project J associated with department D
  for a value V in this contract

23
Decomposition into BCNF
Relation Contracts Attributes Contract,
Supplier, Project, Department, Part, Quantity,
Value Key C FDs JP?C, SD?P J?S
CSJDPQV
SD?P
CSJDQV
SDP
J?S
JS
CJDQV
BCNF
24
Other dependences and normal forms (rarely used)

• Multivalued dependences (4NF)
• If X and Y are subsets of attributes of relation
  schema R
• MVD X?Y independent of the values in other
  attributes
• R is in 4NF if for every MVD X?Y that holds over
  R, one of the following is true
• Y?X or XY R (trivial MVD), or
• X is a superkey

25
MVD

• An employee can be assigned to any project and,
  within those projects, to any activities
• An employee can be assigned to the same
  activities regardless of project assignments
  (assignments to projects and activities are
  independent)
• A project or activity can have any number of
  employees assigned to it

EN?P EN?A
26
More - normal forms (less frequent)

• Join Dependences (5NF)
• A further generalization of MVDs
• All MVDs are JD, but not all JDs are MVDs
• For every JD ?R1,Rn, one of the following is
  true
• Ri R for some i, or
• The JD is implied by the set of those FDs over R
  in which the left side is a key for R
• If a relation schema is in 3NF and each of its
  keys consists of a single attribute, it is also
  in 5NF

27
5NF
If an employee works for a project, the employee
will be assigned to activities within that project
EN,P,A JD EN,P EN,P,A JD P,A EN,P,A JD
EN,A
28
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
29
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

30
Decompose