First Normal Form (1NF)

Each attribute must be atomic (single value)

•  No repeating columns within a row (composite attributes)
•  No multi-valued columns.

1NF simplifies attributes

•  Queries become easier.

1NF

Database Normalization

Functional dependency (FD) X----------> Y         means that if
there is only one  possible  value of  Y for every value of X, then
Y is Functionally dependent on X.

• Functional Dependency is “good”.  With functional dependency the primary key (Attribute A) determines the value of all the other non-key attributes (Attributes B,C,D,etc.)

• Transitive dependency is “bad”.  Transitive dependency exists if the primary/candidate key (Attribute A) determines non-key Attribute B, and Attribute B determines non-key Attribute C.

• If a relation schema has more than one key, each is called a candidate key

• An attribute in a relation schema R is called prim if it is a member of some candidate key of R

Normalization

Database Normalization

• Proposed by Codd (1972)
• Introduced 3 normal forms, the first, second and third normal form
• A stronger definition of 3NF  - called Boyce-Codd normal form (CDNF) was               proposed later
• Later, 4NF and 5NF were proposed

The minimum, and most common, goal is to achieve 3NF.



Normalization Is the process of analyzing the given relational schema based on its functional dependencies and keys to achieve the desirable properties of:

• Minimizing redundancy
• Minimizing the insertion, deletion, and updating  anomalies
• Minimize data storage
• Unsatisfactory relation schema that do not meet  a given normal form test are decomposed into smaller relational schemas that meet the test and hence possess the desired properties.
• Key Concepts in normalization are Functional Dependency and keys

Example

• Sales

(Order#, Date, CustID, Name, Address, City, State, Zip, {Product#, ProductDesc, Price, QuantityOrdered}, Subtotal, Tax, S&H, Total)

What are the problems with using a single table for all order information?

• Insert Anomaly
• Update Anomaly
• Delete Anomaly