normalization a technique for identifying table structures that have potential maintenance problems

Normalization

A technique for identifying table structures that have potential maintenance problems

Normalization

Normalization is a set of formal conditions that assure that a database is maintainable.

The results of a well executed normalization process are the same as those of a well planned E-R model

PROCESS OF DATA NORMALIZATION ELIMINATE REPEATING GROUPS

Make a separate table for each set of related attributes and give each table a primary key.

ELIMINATE REDUNDANT DATAIf an attribute depends on only part of a multi-valued key, remove it to a separate table.

ELIMINATE COLUMNS NOT DEPENDENT ON KEYIf attributes do not contribute to a description of the key, remove them to a separate table.

Database Programming and Design

PROCESS OF DATA NORMALIZATION ISOLATE INDEPENDENT MULTIPLE RELATIONSHIPS

No table may contain two or more 1:n or n:m relationships that are not directly related.

ISOLATE SEMANTICALLY RELATED MULTIPLE RELATIONSHIPS

There may be practical constraints on information that justify separating logically related many-to-many

relationships.Database Programming and Design

Anomalies

A table anomaly is a structure for which a normal database operation cannot be executed without information loss or full search of the data table

Insertion Anomaly Deletion Anomaly Update or Modification Anomaly

Normal Forms

Relational theory defines a number of structure conditions called Normal Forms that assure that certain data anomalies do not occur in a database.

Normal Forms

1NF Keys; No repeating groups2NF No partial dependencies3NF No transitive dependenciesBCNF Determinants are candidate keys4NF No multi-valued dependencies

0 Normal Form

HISTORY(CustName, CustAddr, CustCity {OrderNum, OrderDate {ProdDescr, ProdCode, QtyOrdered, OrderPrice}}

Remove titles and derived quantities Schema notation

Premier Products Order Form

Order # 12003 Date Oct 1, 1997

Oklahoma Retail Company1111 AspNorman

Description Code Qty Price Amount

1. Footballs 21 6 25.00 1502. Sweat Shirts 44 20 15.00 3003. Shorts 37 10 12.00 120

Total 570

0nf

Order # 12003 Date Oct 1, 1997

Oklahoma Retail Company1111 AspNorman

Description Code Qty Price Amount

1. Footballs 21 6 25.00 1502. Sweat Shirts 44 20 15.00 3003. Shorts 37 10 12.00 120

Total 570

1st Normal Form

HISTORY(CustID, CustName, CustAddr, CustCity {OrderNum, OrderDate {ProdDescr, ProdCode, QtyOrdered, OrderPrice}}

Add Keys for embedded entities Remove Repeating Groups

1st Normal Form

CUSTOMER(CustID, CustName, CustAddr, CustCity)

ORDER(OrderNum, CustID, OrderDate {ProdDescr, ProdCode, QtyOrdered, OrderPrice})

Add Keys for embedded entities Remove Repeating Groups

Create a table for each embedded entity, from the outside for nested groups

Insert foreign keys and junction tables

1st Normal Form

CUSTOMER(CustID, CustName, CustAddr, CustCity)

ORDER(OrderNum, CustID, OrderDate)PRODUCT(ProdDescr, ProdCode,)ORDER-PRODUCT(OrderNum, ProdCode,

QtyOrdered, OrderPrice)

2nd Normal Form

No partial dependencies(an attribute has a partial

dependency if it depends on part of a concatenated key)

2nd Normal Form

ROSTER(StuID, ZAPNum, StudentName, CourseTitle, CourseGrade)

Remove partial dependencies

STUDENT(StuID, StudentName)SECTION(ZAPNum, CourseTitle)STUDENT-SECTION(StuID, ZAPNum,

CourseGrade)

2nd Normal Form

ROSTER

STUDENT SECTION

STUDENT-SECTION

3rd Normal Form

No transitive dependencies(a transitive dependency is an

attribute that depends on other non-key attributes)

3rd Normal Form

Note: a transitive dependency arises when attributes from a second entity appear in a given table.

SECTION(ZAPNum, RoomNum, Day, Time, CourseTitle, HoursCredit)

3rd Normal Form

SECTION(ZAPNum, RoomNum, Day, Time, CourseID ,CourseTitle, HoursCredit)

SECTION(ZAPNum, RoomNum, Day, Time, CourseID)

COURSE(CourseID, CourseTitle, HoursCredit)

3NFNo transitive dependencies

Table contains data from an embedded entity with non-key attributes.

TABLE SUB-TABLE

TABLE

SUB-TABLE

??

BCNF is the same, but the embedded table may involve key attributes.

Boyce Codd Normal Form

Every determinant is a candidate key

4th Normal Form

No multi-valued dependencies