Database Design & Schema Architecture: ER to Relational, 3-Schema, Normalization

Key Concepts and Definitions

Database Schema

A database schema defines the structure of a database, including:

• Logical schema: Describes what data is stored and how it relates (e.g., tables, fields, constraints).
• Physical schema: Specifies how data is physically stored (e.g., file organization, indexing).
• External schema: User-specific views of the database (e.g., a cashier’s view vs. a manager’s view).

Why it matters: Schemas act as a blueprint for the database, ensuring consistency and integrity.

ER Diagram to Relational Schema Mapping

Step-by-Step Conversion Rules

Use these rules to convert an Entity-Relationship (ER) diagram into a relational schema (tables):

1. Entities → Tables

• Each entity becomes a table.
• Attributes of the entity become columns in the table.
• The primary key (PK) of the entity becomes the PK of the table.

2. Relationships → Tables or Foreign Keys

3. Attributes in Relationships

• If a relationship has attributes, create a table for it (even for 1:N).
• Example: WorksAt (attributes: Workinghrs, Shift) becomes a table with Cname (FK) and Rname (FK).

4. Composite and Multivalued Attributes

• Composite attributes (e.g., Address: Street, City, Zip) → Split into columns.
• Multivalued attributes (e.g., PhoneNumbers) → Create a separate table with a FK to the entity.

Worked Example: Hospital Database

ER Diagram Assumptions:

• Entities: Patient, Doctor, Appointment, Test (weak entity of Patient).
• Relationships:
  • Patient has Appointment (1:N).
  • Doctor prescribes Test (M:N via Prescription).
  • Test is a weak entity of Patient (requires Patient_ID to identify).

Relational Schema:

CREATE TABLE Patient (
    Patient_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    Gender CHAR(1)
);

CREATE TABLE Doctor (
    Doctor_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Speciality VARCHAR(50)
);

CREATE TABLE Appointment (
    Appointment_ID INT PRIMARY KEY,
    Patient_ID INT,
    Doctor_ID INT,
    Date DATE,
    Time TIME,
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID)
);

CREATE TABLE Test (
    Test_ID INT PRIMARY KEY,
    Patient_ID INT,  -- Partial key (identifying relationship)
    Test_Name VARCHAR(100),
    Result VARCHAR(100),
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID)
);

CREATE TABLE Prescription (
    Prescription_ID INT PRIMARY KEY,
    Doctor_ID INT,
    Test_ID INT,
    Date DATE,
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID),
    FOREIGN KEY (Test_ID) REFERENCES Test(Test_ID)
);

Three-Schema Architecture

The ANSI/SPARC three-level architecture separates concerns for flexibility and security:

![ANSI SPARC three schema architecture](/media/3fe3a83a5f76dea4cdc7.png "Layers: External (user views), Conceptual (logical design), Internal (physical storage) (Image: Fred the Oyster iThe source code of this SVG is valid. This , Public domain, via Wikimedia Commons)")

1. External Schema (User Views)

• Defines how users interact with the database (e.g., a Cashier view vs. an Admin view).
• Uses views in SQL to hide complexity.
• Example:

  CREATE VIEW Cashier_View AS
  SELECT Order_ID, Customer_Name, Total_Amount
  FROM Orders;
  

2. Conceptual Schema (Logical Design)

• Describes the entire database structure (tables, relationships, constraints).
• Independent of physical storage or user views.
• Example: The Patient, Doctor, Appointment tables above.

3. Internal Schema (Physical Storage)

• Details how data is stored (e.g., file organization, indexing, hashing).
• Example: Patient table stored in a B-tree index on Patient_ID.

Advantages of Three-Schema Architecture

Schema Evolution Techniques

Databases change over time. Use these techniques to modify schemas without breaking applications:

1. Adding Columns

• Add a new column to a table.
• Example:

  ALTER TABLE Patient ADD Email VARCHAR(100);
  

2. Renaming Tables/Columns

• Use RENAME or ALTER:

  ALTER TABLE Appointment RENAME TO Medical_Appointment;
  

3. Dropping Columns/Tables

• Drop unused columns or tables:

  ALTER TABLE Patient DROP COLUMN Old_Address;
  

4. Handling Data Migration

• For critical changes (e.g., renaming Patient_ID to User_ID):
  1. Add the new column.
  2. Update data:

     UPDATE Patient SET User_ID = Patient_ID;
     

  3. Drop the old column after validation.

Normalization vs. Denormalization Trade-offs

When to Normalize (3NF/Boyce-Codd NF)

When to Denormalize

In the Real World

1. eSewa (Nepal)

• Idea Used: Three-Schema Architecture
• How: eSewa’s database has:
  • External Schema: User-specific views (e.g., Customer_View for payments, Admin_View for transactions).
  • Conceptual Schema: Core tables like User, Transaction, Service.
  • Internal Schema: Optimized storage (e.g., indexing on Transaction_ID for fast lookups).
• Real Example: When you pay a bill, eSewa’s Customer_View hides the Bank_Integration table from you.

2. Daraz (Nepal)

• Idea Used: Schema Evolution + Denormalization
• How:
  • Schema Evolution: Daraz’s Order table started with Order_ID, Customer_ID, Product_ID. Later, they added Shipping_Status and Payment_Method columns.
  • Denormalization: The Order_Details table includes a pre-computed Total_Price to avoid recalculating SUM(Quantity * Price) for every order view.
• Real Example: During the Dashain sale, Daraz denormalizes Inventory tables to speed up "low stock" alerts.

3. Khalti (Nepal)

• Idea Used: Weak Entities + Relationships
• How:
  • Weak Entity: Transaction is a weak entity of User (requires User_ID to identify).
  • Relationship Table: Transaction_Details links Transaction (PK: Transaction_ID) to Merchant (PK: Merchant_ID) with attributes like Amount, Fee, Status.
• Real Example: When you transfer money to a merchant, Khalti’s Transaction_Details table records the M:N relationship between User and Merchant.

Exam Tip

What Examiners Look For

1. ER to Relational Mapping:

   • Show all tables, including junction tables for M:N relationships.
   • Label PKs and FKs clearly.
   • Example: For a Student ↔ Course (M:N), create Enrollment with both Student_ID and Course_ID as PK.

2. Three-Schema Questions:

   • Draw the three layers (External → Conceptual → Internal).
   • Explain one advantage of each layer (e.g., "External schemas provide security").

3. Schema Evolution:

   • For ALTER TABLE questions, show step-by-step SQL (e.g., add column → update data → drop old column).
   • Mention data integrity risks (e.g., "Dropping a column without backing up data can lose history").

4. Normalization vs. Denormalization:

   • Compare in a table (as above) with real-world examples (e.g., "eSewa uses 3NF for transactions").
   • Justify your choice (e.g., "Denormalizing Orders improves report generation speed").

Common Pitfalls to Avoid

• Forgetting FKs: Always include FOREIGN KEY constraints in your schema.
• Ignoring Weak Entities: Weak entities need both the identifying relationship’s PK and their own discriminator.
• Over-Denormalizing: Denormalize only where performance gains outweigh integrity risks.
• Vague ER Diagrams: Label cardinalities (1:N, M:N) and participation (total/partial). Example:

  erDiagram
    Patient ||--o{ Appointment : "has"
    Appointment ||--|| Doctor : "with"
    Doctor }|--|| Prescription : "prescribes"
    Prescription ||--|| Test : "for"
    Test }|--|| Patient : "belongs to"

Practice Question Walkthrough

Question: Design an ER diagram for a Library System with:

• Entities: Book, Member, Loan (weak entity of Member), Fine.
• Relationships:
  • Member borrows Book (M:N via Loan).
  • Loan has attributes: Due_Date, Return_Date.
  • Member owes Fine (1:N).

Step 1: ER Diagram

erDiagram
    Member ||--o{ Loan : "borrows"
    Loan }|--|| Book : "borrowed"
    Member ||--o{ Fine : "owes"
    Loan ||--|| Fine : "incurs"

Step 2: Relational Schema

CREATE TABLE Member (
    Member_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

CREATE TABLE Book (
    ISBN INT PRIMARY KEY,
    Title VARCHAR(200),
    Author VARCHAR(100)
);

CREATE TABLE Loan (
    Loan_ID INT PRIMARY KEY,
    Member_ID INT,  -- Partial key (identifying relationship)
    ISBN INT,
    Due_Date DATE,
    Return_Date DATE,
    FOREIGN KEY (Member_ID) REFERENCES Member(Member_ID),
    FOREIGN KEY (ISBN) REFERENCES Book(ISBN)
);

CREATE TABLE Fine (
    Fine_ID INT PRIMARY KEY,
    Member_ID INT,
    Amount DECIMAL(10,2),
    Issue_Date DATE,
    FOREIGN KEY (Member_ID) REFERENCES Member(Member_ID)
);

Step 3: Three-Schema Explanation

• External Schema:
  • Librarian_View: Book_Title, Member_Name, Loan_Status.
  • Member_View: Only My_Loans and My_Fines.
• Conceptual Schema: The 4 tables above.
• Internal Schema: Book table indexed on ISBN for fast searches.

Real-World Trace: Kathmandu Traffic Routes Database

Scenario: Design a database for Kathmandu’s traffic management system to track routes, congestion, and accidents.

ER Diagram:

erDiagram
    Road ||--o{ Traffic_Camera : "has"
    Road ||--o{ Accident : "site of"
    Vehicle ||--o{ Violation : "commits"
    Traffic_Camera ||--|| Violation : "captures"

Relational Schema:

CREATE TABLE Road (
    Road_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Length_KM DECIMAL(5,2)
);

CREATE TABLE Traffic_Camera (
    Camera_ID INT PRIMARY KEY,
    Road_ID INT,
    Location VARCHAR(100),
    FOREIGN KEY (Road_ID) REFERENCES Road(Road_ID)
);

CREATE TABLE Vehicle (
    License_Plate VARCHAR(20) PRIMARY KEY,
    Type VARCHAR(20)
);

CREATE TABLE Violation (
    Violation_ID INT PRIMARY KEY,
    Camera_ID INT,
    License_Plate VARCHAR(20),
    Violation_Type VARCHAR(50),
    Fine_Amount DECIMAL(10,2),
    FOREIGN KEY (Camera_ID) REFERENCES Traffic_Camera(Camera_ID),
    FOREIGN KEY (License_Plate) REFERENCES Vehicle(License_Plate)
);

Denormalization Example:

• Add Congestion_Level (e.g., "Low", "High") to the Road table to avoid joining Traffic_Camera data every time.

Why?:

• Kathmandu’s traffic police use this to fine violators (via Violation table) and predict congestion (via Congestion_Level). The Traffic_Camera → Violation relationship ensures real-time updates for enforcement.