Mastering Logical Database Design: ER Diagrams & Normalization Basics

Logical Design

1. Related Terms

1.1 Candidate Keys and Primary Keys

Candidate keys usually consist of one or more attributes that 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 selected from the candidate keys, typically serving as the table’s primary key.

1.2 Attributes, Attribute Names, Domains, Values

An attribute corresponds to a column or an object’s property.

The attribute name is the column name or field name.

A domain defines the range of possible attribute values.

A value (or member) is a specific value within that domain.

Example: In a user table, “gender” is an attribute; “gender” is the attribute name; “female” is a value; “male” and “female” constitute the domain; “female” is a member of the domain.

1.3 Relations and Tuples

A relation is a collection of objects (i.e., a table).

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

2. ER Diagram

An ER 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 changes from many‑to‑many to one‑to‑many after introducing a “selection” association.

Ellipse

An ellipse represents an attribute (a field in a database table). Attributes that serve as primary keys are often underlined.

Line

Lines connect entities to attributes or entities to other entities, establishing relationships.

2.2 Example: E‑commerce ER Diagram

3. Database Design Normal Forms

Database design normal forms represent best practices to avoid data anomalies and redundancy, which can lead to insertion, deletion, and update anomalies and compromise data consistency.

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

4. First Normal Form (1NF)

All attributes in a table must be atomic (indivisible). In plain terms, a two‑dimensional table (rows and columns) must satisfy 1NF.

5. Second Normal Form (2NF)

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

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

In plain terms, a table with a single‑column 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 pair, making them a composite key. Here, product name determines price, description, weight, while supplier name determines supplier phone, violating 2NF.

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

Insertion anomaly: a product cannot be recorded without its supplier.

Deletion anomaly: deleting a product also removes supplier information.

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

Data redundancy: repeated supplier phone numbers.

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

6. Third Normal Form (3NF)

Built on 2NF. A table meets 3NF when no non‑key attribute has a transitive dependency on a candidate key.

Transitive dependency: A → B → C, where C depends on A through B.

Example: Product‑Category Table

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

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

Problems:

Data redundancy: repeated category descriptions.

Insertion anomaly: a product cannot be recorded without a category.

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

Update anomaly: changing a category description requires updating multiple rows.

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

7. Boyce‑Codd Normal Form (BCNF)

BCNF builds on 3NF and requires that for every non‑trivial functional dependency X → Y, X must be a superkey; i.e., there are no transitive dependencies among composite keys.