Database Design Theory: Introduction to Normalization

Flashcards on Database Design Theory: Introduction to Normalization

Informal Design Guidelines

A measure of quality for relation schemas, ensuring attribute semantics are clear and reducing redundant information.

Guideline 1

Design relation schema so that it is easy to explain its meaning and not combine attributes from multiple entity and relationship types into a single relation.

Guideline 2

Design base relation schemas so that no update anomalies are present; if present, note them clearly and ensure programs update the database correctly.

Problems with NULL values

Problems include wasted storage space and difficulties in understanding the meaning of the attributes.

Guideline 3

Avoid placing attributes in a base relation whose values may frequently be NULL; if unavoidable, ensure they apply only in exceptional cases.

Guideline 4

Design relation schemas to be joined with equality conditions on appropriately related attributes, avoiding relations with matching attributes that are not (foreign key, primary key) combinations.

Functional Dependency

A formal tool for analysis of relational schemas, enabling detection and description of problems in precise terms.

Definition of Functional Dependency

A constraint between two sets of attributes from the database, based on the semantics or meaning of the attributes.

Normalization

Process for evaluating and correcting table structures to minimize data redundancies and reduce data anomalies.

Normalization of Relations

Takes a relation schema through a series of tests to certify whether it satisfies a certain normal form in a top-down fashion.

Practical Use of Normal Forms

Pay particular attention to normalization only up to 3NF, BCNF, or at most 4NF.

Prime attribute

Part of any candidate key.

General Definition of Second Normal Form

A relation schema R is in second normal form (2NF) if every nonprime attribute A in R is not partially dependent on any key of R.

General Definition of Third Normal Form

A relation schema R is in third normal form (3NF) if, whenever a nontrivial functional dependency X→ A holds in R, either (a) X is a superkey of R, or (b) A is a prime attribute of R.

Boyce-Codd Normal Form

Every determinant in table is a candidate key, having same characteristics as primary key, but, for some reason, not chosen to be primary key

Fourth Normal Form (4NF)

A table is in fourth normal form (4NF) when it is in 3NF and has no multiple sets of multivalued dependencies.

Multivalued dependency (MVD)

Consequence of first normal form (1NF).

Join Dependencies and Fifth Normal Form

Very peculiar semantic constraint; normalization into 5NF is very rarely done in practice.

Minimal Cover

A minimal set of functional dependencies that is equivalent to the original set, containing no redundant dependencies or attributes.

Extraneous Attribute

An attribute that can be removed from a functional dependency without changing the closure of the attributes.

Canonical Cover

A unique minimal cover for a set of functional dependencies, ensuring no redundancy and simplifying the set.

Decomposition

The process of breaking down a relation into smaller relations to eliminate redundancy and anomalies.

Lossy Decomposition

A decomposition that results in loss of information; the original relation cannot be exactly reconstructed from the decomposed relations.

Testing for Lossless Decomposition

Involves checking whether the common attributes form a superkey for at least one of the decomposed relations.

BCNF Decomposition

If a relation is not in BCNF, decompose it into smaller relations that satisfy BCNF, ensuring that every determinant is a candidate key.

3NF Decomposition

If a relation is not in 3NF, decompose it into smaller relations that satisfy 3NF, preserving dependencies and minimizing redundancy.

Multivalued Attribute

An attribute that can have multiple values for a single entity instance.

Join Dependency

A constraint that requires the natural join of certain projections of a relation to be equal to the original relation.

Domain Key Normal Form (DKNF)

A normal form that aims to eliminate all possible modification anomalies by ensuring that every constraint on the relation is a logical consequence of the definition of keys and domains.

Denormalization

The process of intentionally adding redundancy to a database to improve performance, such as combining tables to reduce the number of JOIN operations required.

Data Redundancy

The duplication of data within a database, which can lead to inconsistencies and update anomalies.

Update Anomaly

A data inconsistency that results from data redundancy and partial updates; can be insertion, deletion, or modification anomalies.

Insertion Anomaly

The inability to add data to the database due to absence of other data.

Deletion Anomaly

The unintended loss of data due to deletion of other data.

Modification Anomaly

A situation in which data inconsistencies arise from failing to change all redundant copies of an attribute.

Surrogate Key

A system-generated primary key, usually a sequence number, that is used when there is no natural key or the natural key is too complex.

Candidate Key

A minimal superkey; that is, an attribute or set of attributes that uniquely identifies a tuple in a relation.

Superkey

A set of attributes within a table that can uniquely identify each record within a table.