Relational Model, Normalization & Anomalies: 1NF to BCNF

TAKEAWAYS:

• The relational model stores data in tables with rows (tuples) and columns (attributes), linked by keys (primary, foreign).
• Normalization (1NF–BCNF) removes redundancy and anomalies by decomposing tables into logical structures.
• Functional dependency and transitive dependency are the root causes of update, insert, and delete anomalies.
• BCNF is stricter than 3NF and eliminates all anomalies by enforcing that every determinant must be a candidate key.
• SQL constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE) enforce relational integrity in practice.
• Real-world applications include bank transactions (3NF for loan records), eSewa payments (BCNF for user balances), and NTC billing (2NF for service records).

The Relational Model: Tables, Keys, and Constraints

What is a Relational Database?

A relational database organizes data into tables (relations), where:

• Each row (tuple) represents a unique record.
• Each column (attribute) holds a specific data type (e.g., VARCHAR, INT, DATE).
• Tables are linked via keys:
  • Primary Key (PK): Uniquely identifies a row (e.g., CID in Customer).
  • Foreign Key (FK): References a PK in another table (e.g., C_id in Buys links to Customer.C_id).

erDiagram
  Customer ||--o{ Buys : "places"
  Customer {
    int CID PK "Primary Key"
    varchar CName "Customer Name"
    varchar address
    int age
  }
  Buys {
    int Code PK "Order Code"
    varchar iname "Item Name"
    float price "Price"
    int C_id FK "Customer ID" ||--|{ Customer : "references"
  }

ER diagram showing Customer-Buys relationship with primary/foreign keys labeled Example: In a bank database, Account (PK: ACCOUNTNO) links to Transaction (FK: ACCOUNTNO) to track deposits/withdrawals.

Why Use the Relational Model?

Normalization: Fixing Anomalies with 1NF–BCNF

What Are Anomalies?

Anomalies occur when data redundancy leads to:

1. Update Anomaly: Changing one record requires multiple updates (e.g., a customer’s address in 3 places).
2. Insert Anomaly: Cannot add data without redundant info (e.g., adding a new product with no sales yet).
3. Delete Anomaly: Deleting a record loses unrelated data (e.g., deleting a product removes its price history).

Step-by-Step Normalization

1. First Normal Form (1NF)

Rule: Each table cell must contain a single value (atomicity), and each row must be unique. How to achieve:

• Remove repeating groups (e.g., multiple phone numbers in one cell).
• Ensure every column has a distinct name.

Before (Violates 1NF):

After (1NF):

Real Example: eSewa stores user phone numbers in a separate User_Phone table (1NF) to avoid repeating groups.

2. Second Normal Form (2NF)

Rule: Must be in 1NF and all non-key attributes must depend on the entire primary key (no partial dependencies). How to achieve:

• Identify composite keys (e.g., OrderID + ProductID).
• Move attributes dependent on part of the key to a new table.

Before (Violates 2NF):

After (2NF): Table: Order_Details (PK: OrderID + ProductID)

Table: Product (PK: ProductID)

Real Example: Daraz uses 2NF to separate Order_Items (quantity) from Products (price) to avoid redundancy.

3. Third Normal Form (3NF)

Rule: Must be in 2NF and no transitive dependencies (non-key attributes depending on other non-key attributes). How to achieve:

• Remove attributes that depend on another non-key attribute.

Before (Violates 3NF):

After (3NF): Table: Customer (PK: CID)

Table: City_District (PK: City)

Real Example: NTC uses 3NF to link Customer_Address (city) to City_Details (district) without repeating district names.

4. Boyce-Codd Normal Form (BCNF)

Rule: Stricter than 3NF—every determinant must be a candidate key. How to achieve:

• If an attribute X determines another attribute Y, but X is not a PK, split the table.

Before (Violates BCNF):

After (BCNF): Table: Manager (PK: ManagerID)

Table: Department (PK: DeptID)

Real Example: Ncell uses BCNF to separate Employee_Dept (manager-department link) from Department (name) to ensure no duplicate department names.

Worked Example: Normalizing a Bank Loan Database

Problem: Design a table for bank loans with anomalies:

Issues:

1. Update Anomaly: Changing InterestRate requires updating all rows.
2. Insert Anomaly: Cannot add a new bank without a loan.
3. Delete Anomaly: Deleting a loan removes bank info.

Solution (3NF):

erDiagram
    Loan ||--o{ Loan_Details : "has"
    Customer ||--o{ Loan : "takes"
    Bank {
        int BankID PK
        varchar BankName
        float InterestRate
    }
    Loan {
        int LoanID PK
        int CustomerID FK
        int BankID FK
        float LoanAmount
    }
    Customer {
        int CustomerID PK
        varchar CName
    }

SQL Implementation:

CREATE TABLE Bank (
    BankID INT PRIMARY KEY,
    BankName VARCHAR(50) UNIQUE,
    InterestRate DECIMAL(5,2)
);

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    CName VARCHAR(50)
);

CREATE TABLE Loan (
    LoanID INT PRIMARY KEY,
    CustomerID INT REFERENCES Customer(CustomerID),
    BankID INT REFERENCES Bank(BankID),
    LoanAmount DECIMAL(10,2)
);

In the Real World

1. Khalti Payments (BCNF):

   • Uses BCNF to separate User (PK: UserID), Transaction (PK: TransactionID), and Bank (PK: BankID).
   • Why? Ensures no duplicate bank names and transactions are uniquely linked to users.

2. NTC Billing System (3NF):

   • Customer (PK: CustomerID) links to Service (PK: ServiceID) via Bill.
   • Why? Avoids repeating service names (e.g., "Internet") in every bill.

3. Daraz Order Processing (2NF):

   • Order (PK: OrderID) has a FK to Product (PK: ProductID).
   • Why? Separates order quantities from product prices to prevent redundancy.

4. NEPSE Stock Records (1NF):

   • Each trade has a unique TradeID; no repeating share codes.
   • Why? Ensures atomicity for high-frequency trades.

Exam Tip

What Examiners Look For

1. Definitions:

   • Clearly define 1NF, 2NF, 3NF, BCNF and functional dependency.
   • Example: "3NF eliminates transitive dependencies where a non-key attribute depends on another non-key attribute."

2. Worked Examples:

   • Always normalize a given table step-by-step (show before/after diagrams).
   • Link to real systems: Mention how banks/eSewa use normalization.

3. SQL Constraints:

   • Use PRIMARY KEY, FOREIGN KEY, and UNIQUE in your DDL answers.
   • Example:

     CREATE TABLE Customer (
         CID INT PRIMARY KEY,
         CName VARCHAR(50) NOT NULL,
         address VARCHAR(100)
     );
     

4. Anomalies:

   • Name and explain update, insert, and delete anomalies with real scenarios (e.g., "Deleting a product from Daraz removes its price history").

5. Comparison Tables:

   • Create a markdown table comparing 1NF–BCNF (rules, examples, anomalies fixed).

Common Mistakes to Avoid

• Skipping steps: Jumping from 1NF to 3NF without showing 2NF.
• Incorrect keys: Using CName as a PK (not unique).
• Ignoring real-world ties: Always relate examples to Nepali apps (e.g., "Like in Khalti’s transaction system...").