Database Design Fundamentals: Key Concepts, ER Diagrams, and Normal Forms

1. Related Terms

1.1 Candidate Key, Primary Key

Candidate keys are one or more attributes used to uniquely identify a tuple (row, object). For example, a primary key or a unique index can serve as a candidate key.

The primary key is chosen from the candidate keys, usually the table's primary key.

1.2 Attribute, Attribute Name, Domain, Component

An attribute corresponds to a column, i.e., an object’s property.

The attribute name is the column name or field name.

A domain defines the range of possible attribute values.

A component is a specific value within that domain.

Example: In a user table, "gender" is an attribute; "gender" is the attribute name; "female" and "male" are domain values; each specific value (female or male) is a component.

1.3 Relation, Tuple

A relation is a collection of objects (a table).

A tuple is a single object (a row in the table).

2. E‑R Diagram

An Entity‑Relationship diagram includes four related symbols: rectangle, diamond, ellipse, and line.

2.1 Symbol Definitions

Rectangle

A rectangle represents an entity (a class in OOP or a table in a database).

Diamond

A diamond denotes a relationship between two entities. For example, the relationship between a shopping‑cart entity and a product entity can be "selection"; originally a many‑to‑many relationship, after modeling it becomes a one‑to‑many relationship (one cart can select many products, but each product is selected by only one cart).

Ellipse

An ellipse represents an attribute (a column in a database table). Primary‑key attributes are often underlined.

Line

Lines connect rectangles to ellipses or rectangles to rectangles to establish relationships.

2.2 Example: E‑Commerce E‑R Diagram

Caption: E‑commerce ER diagram.

3. Database Design Normal Forms

Database design normal forms are best‑practice guidelines that help avoid data‑operation anomalies (insertion, deletion, update anomalies) and data redundancy, thereby preserving data consistency.

Caption: Data operation anomalies.

The normal forms include First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and Boyce‑Codd Normal Form (BCNF), with additional Fourth and Fifth Normal Forms.

4. First Normal Form (1NF)

All attributes in a table must be atomic (single‑valued) and indivisible.

In plain language: a two‑dimensional table (rows and columns) must satisfy 1NF.

5. Second Normal Form (2NF)

A table violates 2NF when a non‑key attribute is partially dependent on a composite candidate key, i.e., when a subset of the composite key determines a non‑key attribute.

Explanation: the key attribute uniquely identifies a tuple (the candidate key). This situation is called a partial functional dependency.

In plain language: a table with a single‑column primary key automatically satisfies 2NF.

Example: Product‑Supplier Table

Fields: product name, price, description, weight, supplier name, supplier phone, category, expiration date.

Because products and suppliers have a many‑to‑many relationship, the combination of product name and supplier name uniquely identifies a product‑supplier record, making them a composite candidate key. The product name determines price, description, and weight; the supplier name determines the supplier phone, which violates 2NF.

Problems: insertion, deletion, update anomalies and data redundancy.

Insertion anomaly: a product cannot be recorded without an associated supplier.

Deletion anomaly: deleting a product also removes supplier information.

Update anomaly: changing a supplier’s phone number requires updating multiple rows.

Data redundancy: repeated supplier phone numbers.

Solution: split the original table into a product table, a supplier table, and a product‑supplier junction table, ensuring each table has a single‑column primary key and thus satisfies 2NF.

6. Third Normal Form (3NF)

Built on 2NF, a table satisfies 3NF when no non‑key attribute is transitively dependent on any candidate key.

Transitive dependency example: A → B → C, where C is transitively dependent on A.

Example: Product‑Category Table

Fields: product name (key), category, category description.

The product name determines the category, and the category determines its description, creating a transitive dependency that violates 3NF.

Problems:

Data redundancy: duplicated category descriptions.

Insertion anomaly: a product cannot be entered without an associated category.

Deletion anomaly: removing all products of a category also deletes the category itself.

Update anomaly: changing a category description for one product affects all products in that category.

Solution: decompose into separate product, category, and product‑category junction tables.

7. Boyce‑Codd Normal Form (BCNF)

BCNF builds on 3NF; it requires that for every non‑trivial functional dependency X → Y, X must be a superkey, eliminating all transitive dependencies among composite keys.

Click to access IT architecture resources