Relational Model, Normalization & Schema Design

TAKEAWAYS:

• The relational model organizes data into tables (relations) with rows (tuples) and columns (attributes), enforcing entity integrity (unique primary keys) and referential integrity (foreign keys).
• Normalization (1NF–BCNF) systematically decomposes tables to remove insert/update/delete anomalies, using functional dependencies and candidate keys.
• Denormalization is a trade-off for performance in read-heavy systems (e.g., Daraz’s product catalog), but risks redundancy.
• SQL constraints (PRIMARY KEY, FOREIGN KEY, CHECK) enforce relational rules, while JOIN operations reconstruct relationships between normalized tables.
• Worked examples tie theory to real systems: e.g., eSewa’s transaction logs (3NF), Ncell’s customer-service database (BCNF), and NEPSE’s stock-trading schema (4NF).
• Exam focus: Design ER→relational schemas, normalize given tables, and justify normalization levels with dependency analysis.

The Relational Model: Tables, Keys, and Constraints

1. Core Concepts: Relations, Attributes, and Domains

The relational model represents data as a collection of tables (relations), where each table has:

• Rows (tuples): Individual records (e.g., a single order in Daraz’s database).
• Columns (attributes): Fields like order_id, customer_name, or product_price.
• Domains: Valid data types/values for each attribute (e.g., price must be numeric, email must match a regex).

Why tables? Relations simplify complex data into flat, two-dimensional structures that computers process efficiently. For example, a bank like Nabil Bank stores all account transactions in a transactions table with columns for account_id, amount, date, and transaction_type.

erDiagram
    CUSTOMER ||--o{ ORDER : "places (1:N)"
    ORDER ||--|{ ORDER_ITEM : "contains (1:N)"
    PRODUCT }|--|| ORDER_ITEM : "has (M:N)"
    CUSTOMER {
        int customer_id PK
        string name
        string email
        string phone
    }
    ORDER {
        int order_id PK
        int customer_id FK
        date order_date
        string status
    }
    ORDER_ITEM {
        int order_item_id PK
        int order_id FK
        int product_id FK
        int quantity
        decimal unit_price
    }
    PRODUCT {
        int product_id PK
        string name
        decimal price
        string category
    }

2. Integrity Constraints: Rules That Keep Data Clean

Relational databases enforce two critical constraints to maintain accuracy:

Real-world impact:

• Violation in Ncell: If a customer_id in billing doesn’t exist in customers, Ncell’s system would reject the invoice, causing billing errors.
• SQL syntax:

  CREATE TABLE orders (
      order_id INT PRIMARY KEY,
      customer_id INT NOT NULL,
      order_date DATE,
      FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  );
  

3. Relationships: How Tables Talk to Each Other

Relations connect via foreign keys, representing real-world associations:

• One-to-Many (1:N): One customer (eSewa) has many transactions.
• Many-to-Many (M:N): Many students enroll in many courses (resolved with a junction table like enrollment).

Normalization: Fixing "Bad" Database Designs

1. Why Normalize? Problems in Unnormalized Data

Poorly designed tables suffer from anomalies (errors when data changes):

2. Normal Forms: Step-by-Step Rules

Normalization follows a hierarchy of normal forms (NF), each eliminating specific anomalies.

Worked Example: Normalizing a Bank Loan Database Problem: A bank’s loans table has redundant data:

loan_id | customer_name | loan_amount | interest_rate | repayment_schedule
---------------------------------------------------------------
1       | Ram Nepal     | 500000      | 8.5%          | [Jan, Feb, Mar...]
2       | Sita Thapa    | 300000      | 7.2%          | [Apr, May, Jun...]

Issues:

• Repeating interest_rate for the same customer across loans.
• repayment_schedule is unstructured (violates 1NF).

Solution (3NF):

1. 1NF: Split repayment_schedule into a separate repayments table.
2. 2NF: loan_id is the primary key; no partial dependencies.
3. 3NF: Move interest_rate to a customers table (since it depends on customer_id, not loan_id).

erDiagram
    CUSTOMER ||--o{ LOAN : "takes (1:N)"
    LOAN ||--|{ REPAYMENT : "has (1:N)"
    CUSTOMER {
        int customer_id PK
        string name
        decimal interest_rate
        string loan_type
    }
    LOAN {
        int loan_id PK
        int customer_id FK
        decimal amount
        date start_date
    }
    REPAYMENT {
        int repayment_id PK
        int loan_id FK
        date due_date
        decimal amount
        string status
    }

3. When to Denormalize?

Normalization isn’t always best:

• Pros: Reduces redundancy, improves data integrity.
• Cons: More JOIN operations slow down queries (e.g., Daraz’s product search).

Trade-off in NEPSE’s Trading System:

• Normalized: Separate tables for stocks, trades, and investors (clean data).
• Denormalized: Combine stock_price and trade_details for faster real-time updates during trading hours.

SQL and Normalization: Putting It All Together

1. Creating Normalized Tables in SQL

-- 3NF-compliant schema for a university (TU) database
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    grade CHAR(2),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

2. Querying Normalized Data

To find all courses taken by a student (e.g., TU student ID 1001):

SELECT c.course_name, e.grade
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
WHERE e.student_id = 1001;

Performance Note:

• Normalized: Faster updates, but complex queries need JOINs.
• Denormalized: Faster reads (e.g., Pathao’s rider-location queries), but harder to maintain.

In the Real World

1. eSewa’s Transaction Logs (3NF)

   • Idea Used: 3NF to separate users, transactions, and wallets.
   • How: Each transaction references a user_id (foreign key) and logs amount and timestamp. Updating a user’s balance only requires changing the wallets table, not every transaction.
   • Impact: Prevents anomalies when users top-up or make payments.

2. Ncell’s Customer Service Database (BCNF)

   • Idea Used: BCNF to ensure no duplicate customer records.
   • How: The customers table has phone_number as the primary key (no two customers share a number). Complaints are linked via customer_id (foreign key).
   • Impact: Avoids confusion when resolving billing disputes.

3. Daraz’s Product Catalog (Denormalization for Speed)

   • Idea Used: Partial denormalization for product listings.
   • How: The products table includes category, price, and stock_quantity (redundant with inventory), but speeds up search results.
   • Trade-off: Inventory updates require careful scripting to avoid inconsistencies.

4. NEPSE’s Stock Trading (4NF for Multi-Valued Data)

   • Idea Used: 4NF to handle multiple stock holdings per investor.
   • How: Separate investors, stocks, and holdings tables. An investor can own shares in multiple stocks without repeating investor details.
   • Impact: Simplifies portfolio calculations and dividend distributions.

Exam Tip

What Examiners Want to See:

1. Design: Given an ER diagram, draw the normalized relational schema (tables + keys) and justify each normal form step.
   • Example: Start with a denormalized orders table, then show 1NF→2NF→3NF transformations.
2. Dependency Analysis: Identify functional dependencies (e.g., customer_id → name) and explain how they violate BCNF.
3. SQL Implementation: Write CREATE TABLE statements with constraints (PRIMARY KEY, FOREIGN KEY).
4. Trade-offs: Compare normalized vs. denormalized designs for a given scenario (e.g., "Design a database for a hospital’s patient records").

Common Pitfalls:

• Forgetting to check for transitive dependencies in 3NF.
• Misidentifying candidate keys (e.g., using a composite key when a single attribute suffices).
• Over-normalizing without considering performance (e.g., using 5NF for a simple app like a local shop’s inventory).

Sample Exam Question: "Normalize the following table to BCNF. Justify each step and write the SQL to create the tables. Assume the table represents a university’s course enrollment system with repeated student details."

enrollment_id | student_name | student_email | course_name | grade | semester
-------------------------------------------------------------------
1             | Ram Nepal    | ram@tu.edu.np | DBMS        | A     | Spring 2023
2             | Sita Thapa   | sita@pu.edu.np | OS          | B     | Spring 2023
3             | Ram Nepal    | ram@tu.edu.np | AI          | A-    | Fall 2023

Your Answer Should Include:

1. 1NF: Atomic values (already satisfied).
2. 2NF: Remove partial dependencies (e.g., student_name depends on student_email).
3. 3NF: Eliminate transitive dependencies (e.g., course_name → semester).
4. BCNF: Ensure all determinants are candidate keys.
5. SQL:

   CREATE TABLE students (student_email VARCHAR(100) PRIMARY KEY, student_name VARCHAR(100));
   CREATE TABLE courses (course_name VARCHAR(100) PRIMARY KEY, semester VARCHAR(20));
   CREATE TABLE enrollments (enrollment_id INT PRIMARY KEY, student_email VARCHAR(100), course_name VARCHAR(100), grade CHAR(2), FOREIGN KEY (student_email) REFERENCES students(student_email), FOREIGN KEY (course_name) REFERENCES courses(course_name));