Functional Dependencies and Normalization for Relational Databases

Practice flashcards covering informal design guidelines, update anomalies, functional dependencies, and the first three normal forms of relational database design.

Relational Database Design

The grouping of attributes to form good relation schemas, primarily focusing on the storage base relation level.

Guideline 1 (Semantics)

Each tuple in a relation should represent one entity or relationship instance; attributes of different entities should not be mixed in the same relation.

Update Anomalies

Problems such as redundant storage that make modifying data inefficient, like having to update a project name for all 100 employees working on it.

Insertion Anomalies

Situations where certain data cannot be recorded, such as being unable to insert a project unless an employee is assigned to it.

Deletion Anomalies

The unintended loss of data, such as deleting an employee resulting in the loss of a project's existence if they were the sole staff member.

Guideline 3 (Null Values)

Relations should be designed to minimize $NULL$ values, as they can indicate inapplicable attributes, unknown values, or unavailable data.

Spurious Tuples

Erroneous results generated from joining relations on attributes that are not (primary key, foreign key) pairs.

Functional Dependency (FD)

Formal constraints derived from the meaning of attributes where a set of attributes $X$ determines a unique value for a set of attributes $Y$, written as $X \rightarrow Y$.

Full Functional Dependency

A property where attribute $Y$ is functionally dependent on composite key $X$, but not on any proper subset of $X$.

Normalization

The process of decomposing unsatisfactory relations by breaking up their attributes into smaller, more stable relations.

Normal Form

A condition using keys and functional dependencies to certify whether a relation schema meets specific design standards.

Superkey

A set of attributes $S$ such that no two distinct tuples in any legal relation state will have the same values for $S$.

Key

A minimal superkey from which the removal of any attribute would cause it to lose the superkey property.

Candidate Key

One of several potential keys for a relation schema; one is designated as the primary key and others as secondary keys.

Prime Attribute

An attribute that is a member of at least one candidate key.

Nonprime Attribute

An attribute that is not a member of any candidate key.

First Normal Form (1NF)

A condition requiring that the domain of an attribute contains only atomic (indivisible) values and that there are no nested or multivalued relations.

Partial Dependency

A functional dependency where a nonprime attribute is dependent on only a part of a composite primary key.

Second Normal Form (2NF)

A relation state where the schema is in 1NF and every non-prime attribute is fully functionally dependent on the primary key.

Transitive Functional Dependency

A functional dependency $X \rightarrow Z$ that is derived from the chain $X \rightarrow Y$ and $Y \rightarrow Z$.

Third Normal Form (3NF)

A relation state where the schema is in 2NF and no non-prime attribute is transitively dependent on the primary key.