Integrity Constraints & Normalization: INF, 2NF, 3NF, BCNF, SQL & Real-World DB Design

Key Concepts: Integrity Constraints

1. Types of Integrity Constraints

Integrity constraints ensure data accuracy and consistency. They are rules enforced by the DBMS to maintain data validity.

Example in Nepali Context:

• eSewa enforces referential integrity when linking a user’s user_id to their payment_id in transactions. If a user deletes their account, pending payments cannot reference a non-existent user.
• NEPSE uses domain constraints to ensure share_price is a positive number (no negative stock prices).
• Khalti applies entity integrity to prevent duplicate transactions by making transaction_id a primary key.

---

2. SQL Implementation of Constraints

-- Domain + Entity Integrity (Primary Key)
CREATE TABLE Customer (
    customer_id INT PRIMARY KEY,  -- Entity integrity: NOT NULL
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) CHECK (email LIKE '%@%.%')  -- Domain constraint
);

-- Referential Integrity (Foreign Key)
CREATE TABLE Order (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)  -- Referential integrity
);

Real-World Trace: Daraz Order System

1. A user places an order (order_id = 1001, customer_id = 50).
2. The DBMS checks:
   • Is customer_id = 50 valid? (Referential integrity)
   • Is order_date in the future? (Domain constraint: CHECK (order_date <= CURRENT_DATE))
3. If valid, the order is stored; else, it rejects with an error.

---

Normalization: Eliminating Redundancy

Normalization is the process of organizing data to minimize redundancy and dependency anomalies. It decomposes tables into smaller, related tables.

1. Normal Forms (1NF to BCNF)

---

2. Worked Example: Normalizing a University Database

Unnormalized Table (0NF):

Step 1: 1NF

• Remove repeating groups by creating separate tables:

  Student(Student_ID, Name)
  Enrollment(Student_ID, Course_ID, Grade)
  Course(Course_ID, Course_Name)
  

Step 2: 2NF

• Check for partial dependencies:
  • Enrollment(Student_ID, Course_ID, Grade) has a composite PK (Student_ID, Course_ID).
  • No partial dependencies exist (all non-key attributes depend on the whole PK).

Step 3: 3NF

• Check for transitive dependencies:
  • Suppose Course(Course_ID, Course_Name, Credit_Hours) and Credit_Hours determines Course_Name (unlikely, but possible).
  • If Credit_Hours is derived from Course_Name, split into:

    Course(Course_ID, Course_Name)
    Credit_Hours(Course_Name, Hours)
    

Final Schema (3NF):

Real-World Tie-In: NTC Exam Scheduling

• Problem: NTC’s old system stored exam_date, subject, and room in one table, leading to redundancy (e.g., "DBMS" always held in Room 101).
• Solution: Normalize into:
  • Subject(subject_id, name)
  • Room(room_id, capacity)
  • Exam_Schedule(exam_id, subject_id, room_id, date)
  • Benefit: Adding a new subject or room doesn’t require updating every exam record.

---

3. Denormalization: When to Break the Rules

Denormalization intentionally introduces redundancy to improve read performance. Used in:

• Data warehouses (e.g., NEPSE’s historical stock data).
• OLAP systems where queries are complex but updates are rare.

Example: Khalti Transaction Logs

• Original 3NF design:

  User(user_id, name)
  Transaction(transaction_id, user_id, amount, timestamp)
  

• Denormalized for faster reports:

  Transaction_Report(transaction_id, user_name, amount, timestamp)  -- user_name duplicated
  

• Trade-off: Faster queries but slower updates (if user_name changes).

---

In the Real World

1. eSewa (Nepal Government)
   • Constraint Used: Referential integrity between user_id (in Users table) and payment_id (in Payments table).
   • How: When a user pays a bill, eSewa checks if the user_id exists in the Users table before processing. If not, it rejects the payment with an error like "User not found."
   • Normalization: The Payments table is in 3NF to avoid redundancy when multiple payments reference the same user.

2. Khalti (Digital Wallet)

   • Constraint Used: Domain constraints on amount (must be ≥ 0) and CHECK (balance >= amount).
   • Normalization: The Transactions table is decomposed into:
     • User(user_id, name, balance)
     • Transaction(transaction_id, user_id, amount, type, timestamp)
   • Why? Prevents negative balances and ensures every transaction is traceable.

3. NEPSE (Stock Exchange)

   • Constraint Used: Entity integrity on share_id (primary key) and referential integrity between trade_id and share_id.
   • Normalization: The Trades table is in BCNF to handle complex dependencies (e.g., a trade involves buyer, seller, and share details without redundancy).
   • Real Trace:
     • A trade for share_id = 100 (Nepal Bank Ltd) is recorded.
     • NEPSE’s system checks:
       1. Does share_id = 100 exist? (Referential integrity)
       2. Is trade_price valid? (Domain constraint: price > 0)
       3. Is the buyer’s balance sufficient? (User-defined constraint: CHECK (balance >= price))

---

Exam Tip

1. For Normalization Questions:

   • Always start with 1NF (atomic values, no repeating groups).
   • For 2NF, look for partial dependencies (non-key attributes depending on part of a composite key).
   • For 3NF, hunt for transitive dependencies (A → B → C).
   • BCNF is stricter: every determinant must be a candidate key. Use decomposition if violated.
   • Past Exam Pattern: Questions often ask to normalize a given table up to 3NF. Show every step with clear justification.

2. For Integrity Constraints:

   • Define each type clearly (domain, key, referential, entity, user-defined).
   • SQL is key: Be ready to write CREATE TABLE statements with constraints.
   • Real-World Link: Relate constraints to apps like eSewa (referential integrity) or Khalti (domain constraints).

3. Common Pitfalls:

   • Forgetting to check for partial dependencies in 2NF (e.g., (A,B) as PK with C depending only on A).
   • Misapplying 3NF: Not all transitive dependencies are obvious (e.g., manager_id depending on city).
   • Denormalization: Only mention it if asked about performance trade-offs.

4. Diagrams in Exams:

   • Draw ER diagrams for normalized schemas.
   • Use tables to show before/after normalization.
   • For SQL constraints, write the exact syntax (e.g., FOREIGN KEY, CHECK).

---