Database Normalization

Question 1

What is normalization?

Answer 1

Series of tests to determine whether a relational schema belongs to a certain
normal form
Five normal forms
◦ first (1NF) : atomic attributes
◦ second (2NF): functional dependency
◦ third (3NF): transitive dependency
◦ fourth (4NF): multi-valued dependency
◦ fifth (5NF): join dependency

Decompose unsatisfactory relational schema into smaller tables (projections)
that possess certain desirable properties
◦ implications for storage requirements and access times

Question 2

What are the objectives of Normalization?

Answer 2

Eliminate certain types of redundancy

Avoid transaction anomalies
◦ Prevent data loss during insertion, modification or deletion operations

Produce good representation of real world
◦ Intuitively easy to understand
◦ Good basis for future growth

Ensure consistency of the database

Question 3

What is first normal form (1NF)?

Answer 3

No composite or multi-valued attributes
◦ domains of attributes must contain only atomic values, i.e. simple, indivisible values
◦ e.g. consider the following tables

ORDER is not 1NF because it contains all Customer (name, address) information in one item

◦ normalize by including CUSTOMER
attributes in ORDER table

Question 4

What is second normal form (2NF)

Answer 4

Based on full functional dependency

A table is in second normal form (2NF) if
◦ it is 1NF and
◦ all of its non-prime attributes are fully functional dependent on the primary key

Whenever a non-prime attribute is partially dependent on the primary key’s attributes the table
is not 2NF

ORDER is not 2NF
◦ Customer_name and Customer_address are
partially dependent on the primary key
◦ depend on Customer# but not on Order#
◦ if an order is cancelled, may lose customer data from database

Decompose ORDER into two projections
◦ non-prime attributes depend only on the primary key

Question 5

What are pros of database normalization?

Answer 5

ensures consistency in the database

results in partitioning of relations into a larger number of smaller relations
◦ may result in slower access times
◦ greater overhead to keep track of larger number of relations
◦ may not be suited to visual display
◦ may be difficult to determine rules governing functional dependencies

does help to avoid anomalies

encourages thinking about well formed databases

Question 6

What is functional dependency?

Answer 6

Constraint between two sets of attributes within a table
◦ X –> Y
◦ usually X is key, Y is subset of non-key attributes
◦ Y is functionally dependent on X if for any two rows t1 and t2 such that t1(X) = t2(X) then t1(Y) = t2(Y)
◦ values of the Y component depend on values of the X component or
alternatively, values of the X component uniquely determine values of the Y component

examples:
◦ SIN –> person_name
◦ (LAT,LON) –> City_name

full functional dependency
◦ removal of any attribute of X means that the dependency no longer holds
◦ e.g. LAT –> City_name
◦ may be many cities with same LAT

partial functional dependency
◦ dependency still holds even if remove an attribute from X

transitive functional dependency
◦ if there is a set of attributes Z which is not a subset of any key of R and both X –> Z and Y –> Z hold