Key Concepts in Relational Database Design

Relational Model

The relational model, introduced in 1970 by E.F. Codd, is now the standard model for commercial DBMS products.

Relation

two-dimensional tables with named columns and rows.

Entity

an identifiable thing that users want to track. It can be a person, object, concept, or event.

Attribute

properties that describe entities

Functional Dependency

a relationship that exists when one attribute uniquely determines another attribute.

Determinant

an attribute or a set of attributes on which some other attribute is fully functionally dependent.

Candidate Key

determines all other columns in a relation. A table may have multiple, but one is selected as the primary key.

Composite Key

A key consisting of two or more columns.

Primary Key

a candidate key chosen to identify rows in a relation.

Surrogate Key

an artificial column added to serve as the primary key. It is typically short, numeric, never changes, and meaningless to users.

Foreign Key

a column or composite of columns that is the primary key of another table.

Referential Integrity Constraint

ensures that a foreign key value must match an existing primary key value in another table.

Normal Form

a property of a relation that ensures it is free from undesirable characteristics like redundancy and dependency.

Multivalued Dependency

occurs when one attribute in a relation uniquely determines another attribute, which can have multiple values.

Domain Integrity Constraint

ensures that all entries in a column are of the same kind.

Entity Set

a collection of similar types of entities.

Field

Represents an attribute in a record.

Relationship Attributes

Attributes that describe the relationship between entities in a relational model.

ER Diagram

the logical view of a system, including entities, entity types, entity sets, and relationships.

Schema

the organization of data as a blueprint of how the database is constructed.

Entity Integrity Constraint

states that the primary key must have unique, non-null data values in every row.

foreign key

A column or composite of columns that is the primary key of another table, establishing a link between tables.

functional dependency

Occurs when the value of one attribute determines the value of another.

determinant

The attribute on the left side of the dependency that determines every other column in the relation.

composite determinant

A determinant that consists of more than one attribute.

normalization

Minimizes redundant data by organizing data into tables and defining relationships between them.

normal forms

Categorized relations based on modification anomalies or other problems to create appropriate database designs.

First Normal Form (1NF)

Meets the conditions for a relation and has a defined primary key.

Second Normal Form (2NF)

Must be in all previous normal froms; all non-key attributes must be dependent on the primary key.

Third Normal Form (3NF)

Must be in all pervious normal forms; no non-key attributes determined by another non-key attribute.

Boyce-Codd Normal Form (BCNF)

Must be in all other previous normal forms; every determinant is a candidate key.

Fourth Normal Form (4NF)

Addresses multivalued dependencies by moving each multivalued dependency to its own table.

Fifth Normal Form (5NF)

A table is in this normal form only if it is in all pervious normal forms and cannot be decomposed into smaller tables without loss of data.

Domain-Key Normal Form (DK/NF)

Make every constraint a logical consequence of candidate keys and domains.

modification anomalies

Include deletion, insertion, and update anomalies.

deletion anomaly

Deleting data results in losing facts about multiple entities.

insertion anomaly

Cannot insert data about one entity without knowing data about another.

update anomaly

Data inconsistencies occur when updating data.

multivalued dependency

Occurs when a determinant is matched with a set of values.

domain integrity constraint

All values in a column must be of the same kind.

entity integrity constraint

The primary key must have unique, non-null data values in every row.

referential integrity constraint

Foreign key values must exist as primary key values in the corresponding relation.

data integrity

Ensures that the data is useful and meaningful.

7 characteristics of a relation

• rows contain data about an entity

• columns contain data about the attributes of an entity

• All entries in a column are of the same kind

• each column has a unique name

• cells of the table hold a single value

• order of the columns/rows is unimportant

• no 2 rows are identical