Relational Data Model: Normalization

1
Relational Data ModelNormalization

• Part 2

2
Normalization

• Any flat file is a relationbut NOT all relations
  are "well formed"
• Normalization
• A set of criteria to evaluate the "well
  formedness" of a relation
• What could make a relation not "well formed" ?

3
Problems with INVENTORY
4
Update Anomalies

• Modification
• Address of Warehouse is stored in many tuples
  (rows/records). If the warehouse
  address is changed, each tuple must be
  changed.
• Insertion
• To insert a tuple, the warehouse address must be
  known
• Deletion
• If the last tuple for a warehouse is deleted, the
  warehouse address is lost.
• These are physical considerations that motivated
  "normalization"

5
Functional Dependencies

• Functional dependencies are used to specify
  formal measures of the goodness of
  relational design
• Definition A set of attribute X functionally
  determines a set of attributes Y if the value
  of X determines a unique value for Y
• Notation X ? Y
• If X ? Y, then whenever two tuples have the same
  value for X, they must have the same value for
  Y
• FDs are derived from the real world constraints
  on the attributes

6
Functional Dependencies

• Full functional dependency
• FD X ? Y where removal of any attribute from X
  means the FD does not hold any more
• Partial functional dependency
• FD X ? Y when some attributes can be removed from
  X and the FD still holds
• Transitive functional dependency
• FD X ? Z that can be derived from two FDs X ? Y
  and Y ? Z

7
FD Example

• Grade (SID, SName, CourseID, CourseTitle,
  Instructor, ILocation, Grade)
• Full FDs
• Partial FDs
• Transitive FDs

8
Normalization Normal Form

• Normalization Process of decomposing ill-formed
  relations by breaking up their attributes into
  smaller relations
• Normal Form
• 1NF
• 2NF
• 3NF
• BCNF
• 4NF
• 5NF

9
Normal Form

• First Normal Form
• Disallows composite attributes, multivalued
  attributes, and nested relations.
• Considered to be part of the definition of
  relation
• Second Normal Form
• A relation schema is in second normal form (2NF)
  if every non key attribute is fully
  functionally dependent on the primary key
• Third Normal Form
• A relation schema is in third normal form (3NF)
  if is is in 2NF and no non-key attribute is
  transitively dependent on the
  primary key

10
Third normal form

• Relational Concept
• Each attribute in a relation must be "fully
  functionally dependent" on the key, "The whole
  key and nothing but the key." (Kent)
• The real issue
• Attributes must be associated with the correct
  entities !!

11
The problem with INVENTORY

• Information about more than one entity is
  maintained in the same relation.
• Conceptual Data Model

12
Second Normal Form is Violated

• INVENTORY(p, wh, quantity, wh-address)
• p wh ? wh-address
• But, wh ? wh-address
• wh-address is fully functionally dependent on
  part of the key(wh)
• wh-address does not describe the entity
  identified by the key (pwh), but the entity
  identified by part of the key(wh only).
• Solution Break up the relation
• Inventory(p, wh,quantity)
• warehouse(wh, wh-address)

13
Third Normal Form Violation

• Emp(emp, dept, location)
• emp ? dept
• dept ? location
• Therefore, emp ? location
• location is transitively dependent upon emp.
• location does not describe the entity identified
  by the key (emp), but the entity identified by
  dept.
• Solution Break up the relation, use only direct
  functional dependencies.
• Emp(emp, dept).
• Dept(dept, location).

14
Normalization Example

• Book (ISBN, Copy, Title, Author, StudentID,
  StudentName, CheckoutDate, DateReturned)

15
Boyce-Codd Normal Form (BCNF)

• General Definition of 3NF
• A relation schema is in 3NF if, whenever an FD X
  ? Y holds in R, either (a) X is a superkey of R,
  or (b) Y is prime attribute of R.
• General Definition of BCNF
• A relation schema is in 3NF if, whenever an FD X
  ? Y holds in R, X is a superkey of R.

16
BCNF Violation

• Real World
• For a given team, each employee is directed by
  only on leader.
• A team may be directed by more than one leader.
• Each leader directs only one team
• Relation Schema
• Team(emp_name, team_name, leader_name)

17
Multivalued Dependencies (MVD)

• A multivalued dependency (MVD) X -gtgt Y exists
  whenever, if two tuples t1 and t2 exist in r(R)
  such that t1X t2X, then two tuples t3 and
  t4 also exist in r(R) with the following
  properties
• t3X t4X t1X t2X
• t3Y t1Y and t4Y t2Y
• t3R-(XY) t2R-(XY) and t4R-(XY)
  t1R-(XY)
• t1, t2, t3, and t4 are not necessarily distinct

18
MVD Example
19
Fourth Normal Form (4NF)

• A relation schema R is in fourth normal form if
  and only if it is in BCNF and, whenever there
  exists an MVD X -gtgt Y, at least one of the
  following holds
• MVD X -gtgt Y is trivial.
• X is a superkey of R.

20
Join Dependencies (JD)

• A join dependency, denoted by JD(R1, R2, , Rn),
  specified on relation schema R states that every
  legal instance or r of R should have a
  lossless join decomposition into R1, R2, , Rn.
• A lossless join decomposition
• (?R1(r), ?R2(r), ..., ?Rn(r)) r

21
Fifth Normal Form (5NF)

• A relation is in 5NF if, for every nontirvial JD
  (R1, R2, , Rn), every Ri is a superkey of R