SQL Relational Joins, Constraints, and Database Normalization

1. Introduction and Transaction Control Language (TCL) Review

In complex relational database management systems, maintaining data integrity during failures is paramount. When multiple queries execute as part of a single business operation, they must either succeed entirely or be completely undone.

• Transaction Integrity: A transaction groups multiple operations into an atomic unit. If any fault occurs, the system executes a rollback to maintain data consistency.
• TCL Commands: Commands such as ROLLBACK, COMMIT, and SAVEPOINT manage these transactions, making them critical in high-concurrency systems like banking software.

2. Database Constraints: Enforcing Data Integrity

Constraints are rules enforced on table columns to ensure data accuracy, reliability, and validity across the database. Without constraints, databases would rapidly accumulate invalid or orphaned data.

Core Constraint Types

3. Database Normalization: The Problem of Redundancy

Normalization is the systematic process of organizing data in a relational database to minimize data redundancy and prevent data anomalies.

Real-Life Analogy

Real-Life Analogy: Imagine keeping a student's phone number hardcoded alongside every single course they enroll in across a massive spreadsheet. If that student changes their phone number, you would have to hunt down every row where their name appears and update it manually. If you miss even one row, your database becomes inconsistent. Normalization extracts that student's details into a single master table, linking it via IDs so a single update propagates everywhere instantly.

Common Data Anomalies

1. Update Anomaly: If a student changes their phone number and it is duplicated across multiple rows, failing to update every single row leads to conflicting contact information.
2. Insertion Anomaly: If you cannot add a new product to a catalog without simultaneously creating a fake customer order, your table structure is flawed.
3. Deletion Anomaly: Deleting a temporary or specific order row accidentally deletes the master product details entirely because everything is bundled into one unstructured table.

4. First Normal Form (1NF)

The primary requirement of First Normal Form (1NF) is atomicity.

• Atomic Values: Every column in a table must contain indivisible, single values. There should be no repeating groups, arrays, or comma-separated lists stored within a single cell.
• Example Fix: Instead of storing multiple course IDs inside a single courses column for a student (e.g., "CS101, MATH201, PHY301"), break them down so that each row represents a single student-course relationship.

5. Second Normal Form (2NF)

To achieve Second Normal Form (2NF), a table must meet two criteria:

1. It must already satisfy 1NF.
2. No Partial Dependencies: Every non-key attribute must be fully functionally dependent on the entire primary key (applies primarily to composite primary keys).

Partial Dependency Breakdown

If a table has a composite primary key consisting of (student_id, course_id), any column like student_name depends only on student_id (not the whole key). Similarly, course_name depends only on course_id.

• Solution: Split the monolithic table into three distinct tables: Students, Courses, and a junction table Enrollments.

6. Third Normal Form (3NF)

Third Normal Form (3NF) builds directly upon 2NF by eliminating transitive dependencies.

• Definition: A relation is in 3NF if it is in 2NF and no non-key attribute is transitively dependent on the primary key.
• Transitive Dependency: Occurs when a non-key attribute depends on another non-key attribute, which in turn depends on the primary key (i.e., $A \rightarrow B \rightarrow C$).
• Example: If student_id determines zip_code, and zip_code determines city, then city is transitively dependent on student_id. To achieve 3NF, the city and zip code data should be moved into a separate Locations table.

7. Boyce-Codd Normal Form (BCNF)

Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF.

• Rule: For every one of its non-trivial functional dependencies $X \rightarrow Y$, $X$ must be a super key.
• Difference from 3NF: 3NF allows a non-key attribute to depend on another non-key attribute under specific edge conditions, whereas BCNF strictly requires the determinant on the left side ($X$) to be a candidate key or super key for every functional dependency.

8. Fourth Normal Form (4NF)

Fourth Normal Form (4NF) addresses multi-valued dependencies.

• Rule: A table must be in BCNF and contain no multi-valued dependencies.
• Multi-Valued Dependency: Occurs when a single attribute determines multiple independent attributes in the same table, forcing unnecessary data duplication. Breaking these into separate tables keeps the database clean and scalable.

9. Functional Dependency Core Concepts

A functional dependency (FD) constraint specifies that the value of one set of attributes uniquely determines the value of another set of attributes within a relation.

• Formal Definition: If $X$ and $Y$ are subsets of attributes in relation $R$, $Y$ is functionally dependent on $X$ (denoted as $X \rightarrow Y$) if each value of $X$ is associated with exactly one value of $Y$.
• Transitive Rule: If $X \rightarrow Y$ and $Y \rightarrow Z$, then $X \rightarrow Z$.

     X (Determinant)
        |
        | uniquely determines
        v
     Y (Dependent Attribute)

10. Summary and Key Takeaways

• Transaction Control (TCL): Ensures atomicity (COMMIT and ROLLBACK), which is vital for maintaining database integrity during failures in critical applications.
• Constraints: Guardrails like PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT enforce data validity at the column level.
• Normalization Goal: Organizes relational data to minimize redundancy and prevent update, insertion, and deletion anomalies.
• First Normal Form (1NF): Eliminates repeating groups and arrays by ensuring all table columns contain atomic, indivisible values.
• Second Normal Form (2NF): Removes partial dependencies by requiring all non-key attributes to be fully functionally dependent on the entire primary key.
• Third Normal Form (3NF): Eliminates transitive dependencies where non-key attributes depend on other non-key attributes.
• Boyce-Codd Normal Form (BCNF): A stricter refinement of 3NF requiring the left-hand side of every functional dependency to be a super key.
• Fourth Normal Form (4NF): Removes multi-valued dependencies to ensure complete independence of multi-attribute relationships.
• Functional Dependencies: Mathematical rules governing how attribute values uniquely map and determine other attributes within database relations.