Database Constraints & Normalization: Rules, Anomalies & Design

TAKEAWAYS:

• Constraints enforce data rules (e.g., NOT NULL, UNIQUE, FOREIGN KEY) to maintain accuracy and consistency in databases.
• Normalization (1NF–BCNF) systematically decomposes tables to eliminate insert/update/delete anomalies while preserving relationships.
• Functional dependencies and partial dependencies are the mathematical foundation for designing efficient schemas.
• Real-world applications: Constraints secure transactions (e.g., bank balances), while normalization speeds up queries (e.g., Daraz’s order processing).
• SQL implementation: Constraints are defined via CREATE TABLE clauses (e.g., PRIMARY KEY, CHECK), and normalization requires iterative table restructuring.
• Trade-offs: Higher normal forms reduce redundancy but may increase join complexity; denormalization is sometimes used for performance.

1. Database Constraints: Rules to Enforce Data Integrity

Constraints are rules applied to database columns or tables to ensure data validity, consistency, and reliability. They prevent invalid operations (e.g., inserting duplicate records or violating business logic). Constraints can be column-level (applied to a single column) or table-level (applied across columns).

Types of Constraints with Examples

Use this table to compare constraints visually:

Why Constraints Matter: Real-World Scenarios

1. eSewa (Nepal’s digital payment system)

   • Constraint Used: FOREIGN KEY and CHECK
   • How? When a user transfers money, the system checks:
     • The AccountID exists in the User table (FOREIGN KEY).
     • The Balance after transfer is ≥ 0 (CHECK constraint).
   • Outcome: Prevents fraudulent transactions or overdrafts.

2. Khalti (Mobile Payment App)

   • Constraint Used: UNIQUE and NOT NULL
   • How? Every transaction has a unique TransactionID (UNIQUE), and the Amount cannot be NULL (NOT NULL).
   • Outcome: Ensures every transaction is traceable and valid.

3. NTC (Nepal Telecom) Billing System

   • Constraint Used: CHECK and DEFAULT
   • How? Customer plans have a ValidityPeriod with a CHECK to ensure it’s not expired, and Status defaults to "Active".
   • Outcome: Prevents billing errors for expired connections.

2. Anomalies in Databases: Why Normalization is Needed

Before normalization, databases often suffer from anomalies—problems that arise when data is redundant, inconsistent, or inefficient. There are three types:

Types of Anomalies

mindmap
  root((Database Anomalies))
    Insertion
      "Cannot insert data without related data"
      Example: Adding a course without any students enrolled.
    Update
      "Updating one record requires updating multiple records"
      Example: Changing a student’s address in multiple tables.
    Deletion
      "Deleting data unintentionally removes other data"
      Example: Deleting a course removes all its enrolled students.

Example: Unnormalized University Database

Consider a table StudentCourse storing student-course enrollments without proper design:

Problems:

1. Insertion Anomaly: Cannot add a new course (e.g., "Biology") without a student enrolled.
2. Update Anomaly: If Ram changes his name to "Ram Prasad," you must update all rows where Ram appears.
3. Deletion Anomaly: If Ram drops Physics, his Math record is not deleted, but the data is still duplicated.

3. Normalization: Step-by-Step Rules to Fix Anomalies

Normalization is the process of organizing data into tables to minimize redundancy and dependency. The normal forms (1NF–BCNF) are hierarchical rules.

Normal Forms Summary Table

Worked Example: Normalizing a Car Rental Database

Initial Unnormalized Table (Rental):

Issues:

• Repeating CarID and Model (violation of 1NF).
• CustomerName and RentalDates are duplicated for the same car.

Step-by-Step Normalization:

1. 1NF: Atomic Values

   • Split RentalDates into StartDate and EndDate.
   • Remove repeating groups (e.g., Model is repeated for CarID).

2. 2NF: Remove Partial Dependencies

   • CarID is part of the PK (CarID + CustomerName), but Model and LicensePlate depend only on CarID.
   • Fix: Create a Car table:

     CREATE TABLE Car (
         CarID INT PRIMARY KEY,
         Model VARCHAR(50) NOT NULL,
         LicensePlate VARCHAR(20) UNIQUE NOT NULL
     );
     

3. 3NF: Remove Transitive Dependencies

   • No transitive dependencies exist here, so we stop at 3NF.

Final Normalized Schema:

Real-World Tie-In: Pathao’s Ride Booking System

• Problem: If Pathao stored driver details, ride history, and payment in one table, updating a driver’s license would require multiple edits.
• Solution: Normalization separates:
  • Driver (ID, Name, License, Vehicle)
  • Ride (RideID, DriverID, StartTime, EndTime, Location)
  • Payment (RideID, Amount, Status)
• Benefit: Faster queries, less redundancy, and easier updates.

4. Functional Dependencies and Dependency Analysis

Functional dependencies (FDs) are the mathematical foundation of normalization. They define how one attribute determines another.

Notation and Examples

• Notation: X → Y means "X functionally determines Y" (each X value maps to exactly one Y value).
• Example:
  • In a Student table: SID → SName (one student ID maps to one name).
  • SID → Department (one student ID maps to one department).

Types of Dependencies

1. Full Functional Dependency (FFD): A non-key attribute depends on the entire primary key.
   • Example: (SID, CID) → Grade (grade depends on both student and course).
2. Partial Dependency: A non-key attribute depends on part of the primary key (violates 2NF).
   • Example: (SID, CID) → SName (SName depends only on SID).
3. Transitive Dependency: A non-key attribute depends on another non-key attribute (violates 3NF).
   • Example: SID → Department and Department → Dean implies SID → Dean (transitive).

Dependency Diagram for a University Database

graph LR
    SID["SID"] --> SName["SName"]
    SID --> Department["Department"]
    Department --> Dean["Dean"]
    SID & Department --> Grade["Grade"]

Color-coded functional dependencies: Candidate key (yellow), partial dependency (blue), transitive dependency (red) Interpretation:

• SID → SName (direct dependency).
• SID → Department → Dean (transitive dependency).
• (SID, CID) → Grade (full dependency).

5. Denormalization: When to Break the Rules

While normalization reduces redundancy, denormalization (intentionally introducing redundancy) is sometimes used for performance optimization.

When to Denormalize?

Example: Daraz’s Order Processing

• Normalized Design:
  • Order (OrderID, CustomerID, OrderDate)
  • Customer (CustomerID, Name, Address)
  • Product (ProductID, Name, Price)
  • OrderItem (OrderID, ProductID, Quantity)
• Denormalized View for Faster Checkout: Combine Customer and Order into a single table to avoid joins during checkout:

  SELECT o.OrderID, c.Name, c.Address, o.OrderDate
  FROM Order o JOIN Customer c ON o.CustomerID = c.CustomerID;
  

  → Trade-off: Faster reads but slower writes (updates to Customer must update multiple rows).

6. SQL Implementation of Constraints and Normalization

Creating Tables with Constraints

-- Bank Account Table with Constraints
CREATE TABLE Account (
    AccountNumber INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    Balance DECIMAL(10,2) CHECK (Balance >= 0),
    BranchID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
    FOREIGN KEY (BranchID) REFERENCES Branch(BranchID)
);

-- Student-Course Enrollment (3NF)
CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
);

CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    CreditHours INT
);

CREATE TABLE Enrollment (
    StudentID INT,
    CourseID INT,
    Grade CHAR(2),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Modifying Tables to Add Constraints

-- Adding a CHECK constraint to ensure valid grades
ALTER TABLE Enrollment
ADD CONSTRAINT CHK_Grade CHECK (Grade IN ('A', 'A-', 'B+', 'B', 'C', 'D', 'F'));

-- Adding a DEFAULT value for order status
ALTER TABLE Order
ADD COLUMN Status VARCHAR(20) DEFAULT 'Pending';

7. Exam Tip: How to Score Full Marks

1. For Constraint Questions:

   • Always define the constraint first (e.g., "A FOREIGN KEY ensures referential integrity by linking to a primary key in another table").
   • Provide a SQL syntax example (e.g., FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)).
   • Give a real-world analogy (e.g., "Like how a library book must belong to a valid member").

2. For Normalization Questions:

   • Step-by-step trace: Show the initial table → anomalies → normalized tables.
   • Use 1NF–3NF labels: Clearly state which normal form each step achieves.
   • Draw an ER diagram (if allowed) for the final schema.
   • Relate to business logic: Explain how normalization prevents anomalies (e.g., "Without 2NF, updating a student’s address would require editing multiple rows").

3. Common Pitfalls to Avoid:

   • ❌ Forgetting to mention partial dependencies in 2NF or transitive dependencies in 3NF.
   • ❌ Skipping the initial unnormalized table in worked examples.
   • ❌ Using vague language like "remove redundancy" without specifying how (e.g., "separate into tables").

4. High-Score Formula for SQL Questions:

   • Step 1: Write the CREATE TABLE statement with all constraints.
   • Step 2: Insert sample data using INSERT INTO.
   • Step 3: Write a query to demonstrate the constraint (e.g., SELECT * FROM Account WHERE Balance < 0; should return no rows).