Normalization

The Process of Normalization

1. Data Sources 2. Unnormalized Form (UNF) 3. First Normal Form (1NF) 4. Second Normal Form (2NF) 5. Third Normal Form (3NF)

Transitive Dependencies

A condition where A, B, and C are attributes of a relation such that if A is dependent on B and B is dependent on C, then C is transitively dependent on A via B. Can cause update anomalies

What is 1NF

A relation in which the intersection of each row and column contains one and only one value. A relation with no repeated groups.

What is 3NF

A relation that is in 1NF and 2NF and in which no non-primary-key attribute is transitively dependent on any candidate. Elements which are mutually dependent are separated off into another table with the independent part as the key.

What is 2NF

A relation that is in 1NF and every non-primary-key attribute is fully functionally dependent on any candidate key. Full functional dependency indicates that if A and B are attributes of a relation, B is fully dependent on A if B is functionally dependent on A.

What is UNF

A table that contains one or more repeating groups

Functional Dependencies

Describes relationships between attributes. If A and B are attributes of R, B is functionally dependent on A, if each value of A in R is associated with exactly one value of B.

Full Functional Dependency

Determinants should have the minimal number of attributes necessary to maintain the functional dependency with the attributes on the right hand side. There is a one-to-one relationship between the attributes on the left side to the right side.

What is Normalization

E-R is a topdown methodology Normalisation is a bottom-up methodology Highly formal Looks at relationships in data Tries to eliminate "undesirable dependencies" Leads to high degrees of "data independence" As normalization proceeds, the relations become progressively more restricted (stronger) in format and also less vulnerable to update anomalies

Benefits of Normalization

Easier for the user to access and maintain the data Take up minimal storage space on the computer

Characteristics of a suitable set of relations

Minimal number of attributes necessary Attributes with close logical relationship Minimal redundancy with each attribute

Purpose of Normalization

Normalization is a technique for producing a set of suitable relations that support the data requirements of an enterprise.

Update Anomalies

Relations that contain redundant information may potentially suffer from update anomalies. Types of anomalies include: Insertion Deletion Modification

1NF to 2NF

Remove partial dependencies by placing them in a new relation along with a copy of their determinant

UNF to 1NF

Remove repeating groups by: Entering data into the empty columns of rows containing the repeating data ('flattening' the table) or by placing the repeating data along with a copy of the original key attributes into a separate relation. A repeating group is an attribute, or group of attributes, within a table that occurs with multiple values for a single occurrence of the nominated key attributes for that table.

2NF to 3NF

Remove transitive dependencies by placing them in a new relation along with a copy of their dominant.

Data Redundancy

The duplication of data or storing the same information in multiple places. Major aim of relational database design is to group attributes into relations to minimise data redundancy

Data Sources to UNF

Transfer attributes into table format

Data Sources

User's requirement specification: 1. Users 2. Forms/reports that are used or generated by the enterprise. 3. Sources describing the enterprise such as data dictionary and corporate data model.