Database Design & Schema Architecture: 3-Schema Model, ER-to-Relational, SQL Schema, Views, Integrity

TAKEAWAYS:

• The three-schema architecture (ANSI/SPARC) separates user views (external), logical design (conceptual), and physical storage (internal) to insulate applications from database changes.
• ER-to-relational mapping converts entities, relationships, and attributes into tables, foreign keys, and constraints—critical for designing databases like Ncell’s customer-billing system.
• SQL schema commands (CREATE TABLE, ALTER TABLE, DROP TABLE) build the database structure, while views (CREATE VIEW) provide customized data access without duplicating data.
• Schema evolution (adding/removing columns, renaming tables) must handle backward compatibility—e.g., when Daraz adds a new order status field.
• Normalization vs. denormalization: Normalized schemas reduce redundancy (e.g., NEPSE’s stock tables), but denormalization (e.g., eSewa’s transaction logs) can improve read performance.
• Constraints (primary keys, foreign keys, checks) enforce data integrity—e.g., ensuring a Pathao driver’s driver_id always matches the Drivers table.

1. The Three-Schema Architecture: ANSI/SPARC Model

The three-schema architecture is the foundation of database design, introduced by ANSI/SPARC in 1975. It divides the database into three layers to manage complexity and change:

classDiagram
    class ExternalSchema {
        +User-specific views
        +Customized data access
        +Insulates from internal changes
    }
    class ConceptualSchema {
        +Logical structure (entities, relationships)
        +Independent of storage/access
        +Shared by all external schemas
    }
    class InternalSchema {
        +Physical storage details
        +File organizations, indexes
        +DBMS-specific
    }
    ExternalSchema --> ConceptualSchema : "Maps to"
    ConceptualSchema --> InternalSchema : "Maps to"
    note for ExternalSchema "Example: eSewa user sees only their transactions"
    note for ConceptualSchema "Example: ER diagram of eSewa’s payment system"
    note for InternalSchema "Example: B-tree indexes on eSewa’s transaction table"

```mermaid
classDiagram
    class ExternalSchema {
        +User-specific views
        +Customized data access
        +Insulates from internal changes
    }
    class ConceptualSchema {
        +Logical structure (entities, relationships)
        +Independent of storage/access
        +Shared by all external schemas
    }
    class InternalSchema {
        +Physical storage details
        +File organizations, indexes
        +DBMS-specific
    }
    ExternalSchema --> ConceptualSchema : "Maps to"
    ConceptualSchema --> InternalSchema : "Maps to"
    note for ExternalSchema "Example: eSewa user sees only their transactions"
    note for ConceptualSchema "Example: ER diagram of eSewa’s payment system"
    note for InternalSchema "Example: B-tree indexes on eSewa’s transaction table"

Key Components:

Why It Matters:

• Insulation: Changing the internal schema (e.g., switching from a flat file to a relational DB) doesn’t break external applications.
• Security: External schemas can hide sensitive data (e.g., password_hash in eSewa’s Users table).
• Flexibility: Add new user views without modifying the core database.

2. Logical Database Design: ER to Relational Mapping

Convert an ER diagram into a relational schema using these rules:

erDiagram
    STUDENT ||--o{ ENROLLMENT : enrolls
    ENROLLMENT ||--o{ COURSE : takes
    STUDENT {
        string student_id PK
        string name
        string email
    }
    COURSE {
        string course_id PK
        string title
        int credit_hours
    }
    ENROLLMENT {
        string student_id PK,FK
        string course_id PK,FK
        string grade
    }

Step-by-Step Mapping Rules:

1. Entities → Tables

   • Each entity becomes a table with attributes as columns.
   • Primary Key (PK): Underlined attribute (e.g., student_id in Students).

2. Relationships → Tables or Foreign Keys

   • 1:1 or 1:M: Add the PK of the "1" side as a foreign key (FK) to the "many" side.
   • M:N: Create a junction table with PKs of both entities + optional attributes.
   • Weak Entities: Include the identifying relationship’s PK as part of their PK.

3. Attributes

   • Simple attributes → columns.
   • Composite attributes → split into columns (e.g., address → street, city, zip).
   • Multivalued attributes → separate table (e.g., Student_Phones).

Example: University Database (ER to Relational)

ER Diagram (Conceptual):

```mermaid
erDiagram
    STUDENT ||--o{ ENROLLMENT : enrolls
    ENROLLMENT ||--o{ COURSE : takes
    STUDENT {
        string student_id PK
        string name
        string email
    }
    COURSE {
        string course_id PK
        string title
        int credit_hours
    }
    ENROLLMENT {
        string student_id PK,FK
        string course_id PK,FK
        string grade
    }

**Relational Schema (SQL):**
```sql
CREATE TABLE Student (
    student_id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) UNIQUE
);

CREATE TABLE Course (
    course_id VARCHAR(10) PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    credit_hours INT CHECK (credit_hours > 0)
);

CREATE TABLE Enrollment (
    student_id VARCHAR(10),
    course_id VARCHAR(10),
    grade CHAR(2),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Student(student_id),
    FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

Real-World Tie-In: NEPSE’s Stock Trading System

NEPSE’s database must track:

• Traders (with trader_id, name, balance).
• Stocks (with stock_id, symbol, price).
• Transactions (with trader_id, stock_id, quantity, timestamp).

Problem: A trader can buy/sell multiple stocks → M:N relationship. Solution: Junction table Transactions with composite PK (trader_id, stock_id, transaction_id).

3. SQL Schema Creation: Tables, Constraints, and Keys

SQL uses CREATE TABLE to define the database schema. Key components:

A. Data Types

B. Constraints

C. Worked Example: Daraz Order System

Scenario: Daraz needs to track orders, customers, and products. Schema:

CREATE TABLE Customer (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15),
    address VARCHAR(200)
);

CREATE TABLE Product (
    product_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock_quantity INT DEFAULT 0
);

CREATE TABLE Order (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')),
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);

CREATE TABLE OrderItem (
    order_id INT,
    product_id INT,
    quantity INT CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES Order(order_id),
    FOREIGN KEY (product_id) REFERENCES Product(product_id)
);

Why This Design?

• Normalization: Order and OrderItem avoid redundancy (one order can have many products).
• Constraints: CHECK (price > 0) prevents negative prices; FOREIGN KEY ensures valid customer IDs.
• Default Values: order_date auto-fills to current time; status defaults to 'pending'.

4. Database Views: Virtual Tables

Views are virtual tables defined by a SQL query. They:

• Provide customized access to data (e.g., hide sensitive columns).
• Simplify complex queries (e.g., ActiveCustomers view).
• Do not store data—they dynamically fetch data from base tables.

Creating and Using Views

-- Create a view of active students (enrolled in at least one course)
CREATE VIEW ActiveStudents AS
SELECT student_id, name, email
FROM Student
WHERE student_id IN (SELECT student_id FROM Enrollment);

-- Query the view
SELECT * FROM ActiveStudents WHERE email LIKE '%tu.edu.np';

Real-World Example: eSewa Transaction History

eSewa might create views for:

1. User-Specific: UserTransactions (shows only a user’s transactions).
2. Admin-Only: FraudulentTransactions (flags suspicious activity).
3. Analytics: MonthlyRevenue (aggregates transactions by month).

Advantages:

• Security: Hide password_hash from user views.
• Performance: Pre-compute complex joins (e.g., CustomerOrders).
• Abstraction: Change base tables without breaking views.

Disadvantages:

• No DML: Cannot INSERT, UPDATE, or DELETE directly on most views.
• Maintenance: Views depend on base tables—dropping a table breaks dependent views.

5. Schema Evolution: Modifying the Database

Databases change over time. Use these commands to evolve schemas:

Worked Example: Adding a Column to Ncell’s Customer Table

Scenario: Ncell wants to track customer loyalty points. SQL:

ALTER TABLE Customer
ADD COLUMN loyalty_points INT DEFAULT 0;

-- Update existing customers (e.g., give 100 points to all)
UPDATE Customer SET loyalty_points = 100 WHERE loyalty_points = 0;

Challenges:

• Backward Compatibility: Old applications may not recognize loyalty_points.
• Data Migration: Existing data may need updates (e.g., setting defaults).
• Downtime: Large tables may require maintenance windows.

6. Normalization vs. Denormalization: Trade-offs

When to Denormalize?

• Read-Heavy Systems: e.g., Daraz’s product catalog (denormalize Product + Category into one table).
• Reporting: e.g., NTC’s monthly traffic reports (pre-aggregate data).
• Legacy Systems: Simplify migration from flat files to relational DBs.

Example: Daraz Product Catalog Normalized:

Product (product_id, name, price)
Category (category_id, name)
ProductCategory (product_id, category_id)

Denormalized (for faster searches):

Product (
    product_id,
    name,
    price,
    category_name,  -- Duplicate data
    category_id
)

7. Database Schema Design Best Practices

1. Start with ER Diagrams: Model real-world entities and relationships before writing SQL.
2. Normalize First: Aim for 3NF (Third Normal Form) to minimize redundancy.
3. Document Constraints: Clearly define NOT NULL, UNIQUE, and CHECK rules.
4. Plan for Growth: Use VARCHAR(255) instead of CHAR(255) for variable-length fields.
5. Use Views for Security: Restrict access to sensitive data (e.g., AdminView vs. UserView).
6. Test Schema Changes: Use ALTER TABLE in a staging environment first.
7. Backup Before Modifications: Schema changes can break applications.

In the Real World

1. eSewa’s Payment System

   • Schema Architecture: Uses a three-schema model where:
     • External Schema: Users see only their transaction history (UserTransactions view).
     • Conceptual Schema: ER model with User, Transaction, Merchant, and Wallet entities.
     • Internal Schema: Sharded tables by region (Kathmandu, Pokhara, etc.) for performance.
   • Key Idea: Views hide sensitive data (e.g., Wallet balances) from merchants.

2. NEPSE’s Stock Trading Database

   • ER-to-Relational Mapping: Converts the M:N relationship between Trader and Stock into a junction table Transaction with:
     • PK: (trader_id, stock_id, transaction_id)
     • FKs: trader_id → Trader, stock_id → Stock
   • Real-World Impact: Ensures no duplicate trades and maintains audit trails.

3. Daraz’s Order Fulfillment

   • Schema Evolution: When Daraz added "same-day delivery," they:
     1. Added a delivery_option column to Order:

        ALTER TABLE Order ADD COLUMN delivery_option VARCHAR(20) DEFAULT 'standard';
        

     2. Created a view for same-day orders:

        CREATE VIEW SameDayOrders AS
        SELECT * FROM Order WHERE delivery_option = 'same_day';
        

   • Challenge: Old orders had NULL in delivery_option—they used COALESCE to handle this:

     SELECT * FROM Order WHERE COALESCE(delivery_option, 'standard') = 'same_day';
     

4. Ncell’s Customer Database

   • Denormalization: For billing reports, Ncell denormalizes Customer and Plan into a BillingSummary table:

     CREATE TABLE BillingSummary (
         customer_id INT,
         plan_name VARCHAR(50),
         monthly_charge DECIMAL(10,2),
         data_limit MB,
         FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
     );
     

   • Why? Simplifies monthly billing queries (no joins needed).

Exam Tip

What Examiners Look For

1. Three-Schema Architecture:

   • Must include: External, conceptual, and internal schemas with one example each (e.g., TU’s student database).
   • Common Mistake: Forgetting to explain how changes in one layer affect others (e.g., adding a column to the internal schema may require updating external views).

2. ER-to-Relational Mapping:

   • Show your work: Start with an ER diagram, then write the SQL schema step-by-step.
   • Key Points to Cover:
     • How 1:1, 1:M, and M:N relationships map to tables/FKs.
     • Handling weak entities and multivalued attributes.
   • Example Trace: For the University database, explain why Enrollment needs a composite PK.

3. SQL Schema Questions:

   • Syntax: Use proper CREATE TABLE with constraints (e.g., FOREIGN KEY, CHECK).
   • Realistic Data: Include NOT NULL, UNIQUE, and DEFAULT where appropriate (e.g., email UNIQUE NOT NULL).
   • Common Pitfall: Forgetting to reference the PK in FOREIGN KEY clauses.

4. Views:

   • Define: "A virtual table based on a SQL query."
   • Use Case: Give an example like ActiveStudents or FraudulentTransactions.
   • Limitations: Mention that views cannot modify data (except in rare cases with INSTEAD OF triggers).

5. Schema Evolution:

   • Commands: Know ALTER TABLE ADD/DROP COLUMN, RENAME TABLE, and DROP TABLE.
   • Backward Compatibility: Discuss how to handle existing data (e.g., DEFAULT values, UPDATE statements).

6. Normalization vs. Denormalization:

   • Compare: Use a table (as above) and give one pro/con for each.
   • Apply to Nepal: e.g., "NEPSE uses normalization for trades but denormalizes for daily reports."

How to Score Full Marks

• Diagrams: Draw an ER diagram for any database design question (even if not asked).
• SQL Code: Write complete CREATE TABLE statements with all constraints.
• Real-World Links: Tie examples to Nepali companies (e.g., "Like Daraz’s order system...").
• Step-by-Step: For mapping ER to relational, show each transformation (e.g., "M:N → junction table").
• Exam Language:
  • Use terms like "insulation", "abstraction", "data independence", and "referential integrity".
  • For views: "dynamic result set", "logical table", "does not store data".

In the real world

• eSewa’s Payment System: Uses the three-schema architecture to separate user views (external schema) from the core transaction database (conceptual schema), allowing new features (e.g., QR payments) without breaking existing apps.
• NEPSE’s Stock Trading Platform: Employs ER-to-relational mapping to convert trader-stock relationships into normalized tables (e.g., Traders, Stocks, Transactions), ensuring data integrity with foreign keys.
• Daraz’s Order Management: Uses denormalization in transaction logs (e.g., storing order_id, customer_name, product_details in one table) to speed up read-heavy operations during Black Friday sales.