SQL: DATABASE DESIGN AND NORMALIZATION

1. What is Database Design?

Database design is the structured process of classifying real-world business requirements into an organized relational schema composed of tables, columns, constraints, and relationships.

Professional database modeling progresses through three distinct development tiers:

• Conceptual Design: High-level identification of core business entities (such as Customers, Invoices, and Items) and their business connections without technical details.
• Logical Design: Translation of entities into relational tables, defining candidate keys, establishing foreign keys, and applying normalization rules.
• Physical Design: Implementation within a specific database engine, choosing storage types, setting up B-Tree indexes, partitioning disks, and configuring buffer pool caches.

2. ER Diagram (Entity Relationship Diagram)

An Entity Relationship (ER) Diagram is a graphical model that maps out the logical structure of a database before writing DDL statements.

ER models comprise three core building blocks:

• Entities: Distinct real-world objects or concepts that hold data (e.g., Student, Course, Order). In relational databases, entities become tables.
• Attributes: Individual data fields describing an entity (e.g., student name, email, birth date). In relational databases, attributes become columns.
• Relationships: Logical connections associating two or more entities (e.g., a Student enrolls in a Course).

3. Relationships: One-to-One, One-to-Many, Many-to-Many

Relational databases model connections between entities using three cardinality types:

One-to-One (1:1)

Each record in Table A corresponds to at most one record in Table B, and vice versa. Common examples include separating sensitive authentication credentials from public user profiles.

Implementation: Place a Foreign Key in Table B that references Table A, and apply a UNIQUE constraint on the foreign key column.

One-to-Many (1:N)

Each record in Table A can associate with multiple records in Table B, but each record in Table B belongs to exactly one parent record in Table A (e.g., one Department contains many Employees).

Implementation: Place a Foreign Key in the child table (Employees) referencing the Primary Key of the parent table (Departments).

Many-to-Many (N:M)

Each record in Table A can link to multiple records in Table B, and each record in Table B can link to multiple records in Table A (e.g., Students and Courses).

Implementation: Relational engines cannot implement N:M directly with a single foreign key. You must resolve it using an intermediate Junction Table.

4. Junction (Bridge) Tables

A junction table (also called a bridge, associative, or cross-reference table) splits a Many-to-Many relationship into two clean One-to-Many relationships.

The junction table contains foreign keys referencing both parent tables. Together, these foreign keys frequently form a composite primary key, preventing duplicate associations. Junction tables can also store relationship metadata, such as enrollment dates or exam grades.

5. Normalization in DBMS

Normalization is the formal mathematical technique of decomposing tables to eliminate data redundancy and prevent data modification anomalies.

Poorly designed unnormalized tables suffer from three destructive operational anomalies:

• Insertion Anomaly: Inability to record certain facts without recording unrelated data. For example, unable to register a new department because no employee has been hired yet.
• Update Anomaly: Redundant facts stored across hundreds of rows. Updating a department location requires modifying 500 employee records; missing a single row creates conflicting data.
• Deletion Anomaly: Deleting one piece of information unintentionally deletes unrelated facts. For example, deleting the only employee in Research accidentally deletes the department itself.

6. First Normal Form (1NF)

A relation is in First Normal Form (1NF) if and only if:

• Atomic Values: Every column contains atomic (single, indivisible) values. Storing comma-separated lists like '555-0101, 555-0199' violates 1NF.
• No Repeating Groups: Defining multiple columns for identical attributes (such as phone_1, phone_2, phone_3) violates 1NF.
• Unique Identification: Each row must be uniquely identifiable using a designated Primary Key.

7. Second Normal Form (2NF)

A relation is in Second Normal Form (2NF) if:

1. It satisfies all requirements of First Normal Form (1NF).
2. No Partial Dependencies: Every non-key attribute must depend on the entire primary key, not a partial subset of a composite primary key.

Consider a table with composite primary key (student_id, course_id) containing attributes grade and course_title:

• grade depends on both student_id and course_id (fully functional dependency).
• course_title depends solely on course_id (partial dependency, violating 2NF).

Fix: Extract course_title into a distinct courses table.

8. Third Normal Form (3NF)

A relation is in Third Normal Form (3NF) if:

1. It satisfies all requirements of Second Normal Form (2NF).
2. No Transitive Dependencies: No non-key attribute depends on another non-key attribute.

In simple terms: Every attribute must depend on the key, the whole key, and nothing but the key.

Consider an employees table with columns (emp_id, emp_name, dept_id, dept_location):

• emp_id determines dept_id.
• dept_id determines dept_location.
• Therefore, emp_id transitively determines dept_location through another non-key field.

Fix: Move dept_id and dept_location into a separate departments table.

9. Boyce-Codd Normal Form (BCNF)

Boyce-Codd Normal Form (often called 3.5NF) is a stricter version of Third Normal Form designed to resolve rare anomalies when a relation has multiple overlapping candidate keys.

A table is in BCNF if and only if for every non-trivial functional dependency X -> Y, X is a superkey.

If a non-key attribute determines part of a candidate key, the relation satisfies 3NF but violates BCNF. To reach BCNF, decompose the table into two separate tables so that all determinants become superkeys.

10. Denormalization

Denormalization is the intentional, controlled re-introduction of redundancy into a normalized schema to improve read performance.

In massive web applications and reporting warehouses, joining 10 normalized tables for every page load consumes significant CPU and disk I/O. By duplicating specific attributes or storing precomputed summary metrics directly on parent records, queries retrieve all required fields from a single table scan.

11. Normalization vs Denormalization

Understanding the architectural trade-offs between normalized and denormalized designs:

12. Types of Keys: Candidate, Super, Alternate, and Surrogate Keys

Keys are fundamental constraints that enforce row identity and relational integrity:

• Candidate Key: A minimal set of attributes that uniquely identifies any record without unnecessary columns.
• Primary Key: The specific candidate key chosen by the architect as the authoritative row identifier.
• Alternate Key: Any candidate key not selected as the primary key (e.g., if id is primary key, email is an alternate key enforced via UNIQUE).
• Super Key: Any combination of columns containing a candidate key that uniquely identifies a row (e.g., (id, name)).
• Surrogate Key: An artificial, system-assigned identifier (such as an AUTOINCREMENT integer or UUID) with zero intrinsic business meaning.
• Natural Key: An existing real-world attribute that possesses inherent uniqueness (such as an ISBN number or tax ID).

13. Database Schema Design Best Practices

Adhere to industry standards when modeling production relational schemas:

• Consistent Naming Conventions: Standardize on lowercase snake_case (e.g., order_items, customer_id). Choose plural table names consistently.
• Always Index Foreign Keys: Relational engines do not index foreign key columns automatically. Add indexes to prevent full table locks during cascades and joins.
• Select Exact Data Types: Avoid generic TEXT for numbers or dates. Use integer types for quantities and TIMESTAMP for chronological events.
• Include Standard Audit Columns: Equip every operational table with created_at and updated_at timestamp columns.

14. OLTP vs OLAP

Relational databases serve two distinct operational paradigms:

15. Star Schema vs Snowflake Schema

In analytical data warehouses (OLAP), data is modeled dimensionally using Fact Tables and Dimension Tables:

• Fact Table: The central table containing numerical quantitative measurements (e.g., units sold, revenue, discount amount) and foreign keys linking to dimensions.
• Dimension Tables: The perimeter tables containing descriptive context (e.g., Customer, Date, Store, Product).