Normalization

Database schema before class: database_day11_end_of_class.sql
Slides from class

Database notes

Normalization

A process that assigns attributes to entities so that data redundancies are are reduce or eliminated.

De-normalization

A process by which a table is changed from a higher-level normal form to a lower-level normal form, usual to increase processing speed (avoid joins). De-normalization may result in data anomalies.

Data anomaly

A data abnormality in which inconsistent changes have been made to a database. For example, an employee move, but the address is not changed in all relations in the database. Anomalies can occur on inserts, updates, or deletes.

Determinant

Any attribute in a specific row whose value directly determines the other values in that row.

Functional dependence

Within a relation R, an attribute B is functionally dependent on attribute A if and only if a given value of A determines exactly one value of attribute B. The relationship "B is dependent on A" is equivalent to "A determines B".

Partial dependency

A condition in which an attribute is dependent on only a portion (subset) of the primary key.

Transitive dependency

A condition in which an attribute is dependent on another attribute that is not part of the primary key.

First normal form (1NF)

The first stage in the normalization process. It describes a relation in tabular format, with no repeating groups and with a primary key identified.

Second normal form (2NF)

The second stage in the normalization process. A relation in 1NF with no partial dependencies.

Third normal form (3NF)

The third stage in the normalization process. A relation in 2NF with no transitive dependencies. This the normal form most commonly used in professional database designs.

Boyce-Codd normal form (BCNF)

A special type of 3NF in which every determinant is a candidate key. A table in BCNF must also be in 3NF.

Fourth normal form (4NF) and higher

Primarily of academic interest only.

Identifying (strong) relationship

A relationship that occurs when two entities are existence-dependent; from a database design perspective, this relation exists whenever the primary of the related entity contains the primary key of the parent entity.

Non-identifying (weak) relationship

A relationship in which the primary key of the related entity does not contain a primary key component of the parent entity.

Strong entity

An entity that is existence-independent; that is, it can exist apart from all of its related entities. Also called a regular entity.

Weak entity

An entity that is existence dependent and inherits the primary key of the parent entity. Example: a Dependent requires the existence of an Employee.