Normalization of Database Tables

1
Normalization of Database Tables

• CHAPTER 4

2
Chapter Objectives

• Understand concepts of normalization
• Learn how to normalize tables
• Understand normalization and database design
  issues

3
Database Tables and Normalization

• Normalization is a process for assigning
  attributes to entities.
• It reduces data redundancies.
• An un-normalized relation (table) stores
  redundant data, which can cause insertion,
  deletion, and modification anomalies.
• In simple words Normalization means keeping a
  single copy of data in your database.
• Normalization theory provides a step by step
  method to remove redundant data and undesirable
  table structures.

4
Normal Forms

• Tables are normalized by applying rules to create
  a series of normal forms
• First normal form (1NF)
• Second normal form (2NF)
• Third normal form (3NF)
• Boyce/Codd normal form (BCNF)
• Fourth normal form (4NF)
• Projection Join normal form (PJNF, aka 5NF)
• A table or relation in a higher level normal form
  always confirms to lower level normal forms.

5
Normal Forms

• While higher level normal forms are available,
  normalization up to BCNF is often found to be
  adequate for business data.

6
First Normal Form

• A relation is in 1NF if all underlying domains
  contain atomic values only, i.e., the
  intersection of each row and column contains one
  and only one value.
• The relation must not contain repeating groups.

PNo PName ENo EName Jcode ChgHr Hrs
1 Alpha 101 John Doe NE 65 20 105 Jane Vo
SA 80 15 110 Bob Lund CP 60 40
2 Beta 101 John Doe NE 65 20 108 Jeb Lee
NE 65 15 106 Sara Lee SA 80 20
3 Omega 102 Beth Reed PM 125 20 105 Jane Vo
SA 80 10 Is the above relation in 1NF?
7
First Normal Form

• The previous relation can be converted into first
  normal form by adding Pno and Pname to each row.

PNo PName ENo EName Jcode ChgHr Hrs 1 Alpha 101
John Doe NE 65 20 1 Alpha 105 Jane Vo
SA 80 15 1 Alpha 110 Bob Lund
CP 60 40 2 Beta 101 John Doe NE 65 20 2 Beta
108 Jeb Lee NE 65 15 2 Beta 106 Sara Lee
SA 80 20 3 Omega 102 Beth Reed
PM 125 20 3 Omega 105 Jane Vo SA 80 10
What is the primary key in this relation?
Do you see redundant data in this table? What
anomalies could be caused?
8
Functional Dependency Revisited

• If A and B are attributes (or group of
  attributes) of a relation R, B is functionally
  dependent on A (denoted A B), if each value of
  A in R is associated with exactly one value of B
  in R.
• A is called a determinant.
• Consider the relation
• Student (ID, Name, Soc Sec Nbr, Major, Deptmt)
• Assume a department offers several majors, e.g.
  INSY department offers, INSY, MASI, and POMA
  majors.
• How many determinants can you identify in Student?

9
Functional Dependency Revisited

• A Dependency diagram

Name
Soc_Sec_Nbr
Major
Dept
10
Functional Dependency Revisited

• Full functional dependency
• Attribute B is fully functionally dependent on
  attribute A if it is functionally dependent on A
  and not functionally dependent on any proper
  subset of A.
• This becomes an issue only with composite keys.
• Transitive dependency
• A, B and C are attributes of a relation such that
  A B and B C, then C is transitively
  dependent on A via B (provided that A is not
  functionally dependent on B or C)

11
Second Normal Form

• Dependency diagram for Project

PNo
PName
ENo
ChgHr
Hrs
EName
JCode
12
Second Normal Form

• A relation is in 2NF if
• It is in 1NF and
• every nonkey attribute is fully dependent on the
  primary key, i.e., no partial dependency.
• A nonkey attribute is one that is not a primary
  key or part of a primary key.
• We create new relations that are in 2NF through
  projection of the original relation.
• Project(PNo, PName)
• Employee(ENo, EName, Jcode, ChgHr)
• Charge(PNo, ENo, Hrs)

13
2NF

• 2NF

PNo
PName
ENo
ChgHr
EName
JCode
PNo
ENo
Hrs
14
Second Normal Form

• Tables in 2NF

15
Second Normal Form

• Note that the original relation can be recreated
  through natural join of the new relation.
• Thus, no information is lost in the process of
  creating 2NF relations from a 1NF relation. This
  is called nonloss decomposition.
• If a relation that is in 1NF has a non composite
  primary key (i.e., the primary key consists of a
  single attribute) what can you say about its
  status with regard to 2NF?
• Do you see any redundant data in the tables that
  are in 2NF?
• What anomalies could be caused by such redundancy?

16
Third Normal Form

• A relation is in 3NF if
• It is in 2NF and
• every nonkey attribute is nontransitively
  dependent on the primary key (i.e., no transitive
  dependency).
• Relation Employee has a transitive dependency
• ENo JCode ChgHr
• Employee can be replaced by two relations, that
  are in 3NF
• Employee(ENo, EName, Jcode)
• Job(JCode, ChgHr)

17
3NF

• 3NF

PNo
PName
ENo
ChgHr
EName
JCode
JCode
PNo
ENo
Hrs
18
Third Normal Form

• Tables in 3NF

19
Boyce-Codd Normal Form

• A relation is in BCNF if
• every determinant is a candidate key.
• A determinant is an attribute (combination of
  attributes) on which some other attribute is
  fully functionally dependent.
• BCNF is a special case of 3NF.
• The potential to violate BCNF may occur in a
  relation that
• contains two (or more) composite candidate keys,
• these keys overlap and share at least one
  attribute.
• Thus, if a table contains only one candidate key
  or only non-composite keys, then 3NF and BCNF are
  equivalent.

20
3NF Table Not in BCNF

• Figure 4.7

21
Decomposition of Table Structure to Meet BCNF
Figure 4.8
22
Boyce-Codd Normal Form

• Consider the following example
• The members of a recruiting team interview
  candidates on a one-to-one basis. Each member is
  assigned a particular room on a given date. Each
  candidate is interviewed only once on a specific
  date. He/she may return for follow up interviews
  on later dates.
• Interview (CID, IDate, ITime, StaffID, RmNo)

23
Boyce-Codd Normal Form

• This relation has following functional
  dependencies
• CID, IDate ITime, StaffID, RmNo
• StaffID, IDate, ITime CID, RmNo
• RmNo, Idate, Itime StaffID, CID
• StaffID, IDate RmNo
• This relation does not have any partial or
  transitive dependencies on the primary key (CID,
  IDate)
• It is not in BCNF because (StaffID, Idate) is a
  determinant but not a candidate key.
• The new relations in BCNF are
• Interview (CID, IDate, ITime, StaffID)
• Room(StaffID, IDate, RmNo)

24
Dependency Diagram

• Dependency diagram

CID IDate ITime StaffID RmNo
Fig 1
CID IDate ITime StaffID RmNo
Fig 2
CID IDate ITime StaffID RmNo
Fig 3
25
Fourth Normal Form

• A table is in 4NF if
• it is in 3NF and
• has no multiple sets of multivalued dependencies.
• Consider the following example
• Each course is taught by many teachers and
  requires many texts.

26
Fourth Normal Form

• CTXN is in BCNF, because it is all key and there
  are no other functional dependencies.
• It, however, has redundant data that could cause
  update anomalies.
• This table shows two multivalued dependencies
• Each course has a defined set of teachers and
• Course Teacher
• Each course has a defined set of textbooks.
• Course Text
• MVDs can exist only when the relation has at
  least three attributes.
• An FD is a special case of MVD when the set of
  dependent values has a single value.

27
Fourth Normal Form

• Tables in 4NF

CX Course Text Physics Basic Mechanics Physics Int
ro to Optics Math Modern Algebra Math Intro to
Calculus
28
Conversion to 4NF
Figure 4.15 Set of Tables in 4NF
Figure 4.14 Multivalued Dependencies
29
Normalization and Database Design

• Normalization should be part of the design
  process
• E-R Diagram provides macro view
• Normalization provides micro view of entities
• Focuses on characteristics of specific entities
• May yield additional entities
• Difficult to separate normalization from E-R
  diagramming
• Business rules must be determined
• Normalization purity is difficult to sustain due
  to conflict in
• Design efficiency
• Information requirements
• Processing

30
Denormalization

• Normalized (decomposed) tables require additional
  processing, thus reducing system speed.
• Sometimes normalization is not done keeping in
  mind processing speed requirements and practical
  aspects of the situation.
• A good example is storing Zip code and City as
  attributes in a Customer relation violates 3NF
  because City is transitively dependent on Cust ID
  via Zip Code.
• Why should we not create a separate relation
  ZIP (ZipCode, City)?