Databases: SQL, ORM, Transactions & Database Design

TAKEAWAYS:

• Understand SQL vs. NoSQL trade-offs and when to use each (e.g., relational for structured data like bank transactions, NoSQL for unstructured data like user reviews).
• Master CRUD operations (Create, Read, Update, Delete) using Python’s sqlite3 and pymysql libraries, with real-world examples like eSewa’s transaction logs or Ncell’s customer records.
• Design normalized database schemas (1NF, 2NF, 3NF) to eliminate redundancy, using ER diagrams for clarity (e.g., a Daraz order system with tables for Customers, Orders, and Products).
• Implement transactions to ensure atomicity (e.g., a Khalti payment either fully succeeds or fails without partial updates).
• Use ORM (Object-Relational Mapping) like SQLAlchemy to map Python objects to database tables, reducing boilerplate SQL code.
• Optimize queries with indexes and joins, and handle errors gracefully using try-except blocks (e.g., NEPSE’s stock data retrieval).

1. Introduction to Databases

A database is an organized collection of structured data stored electronically. Databases allow efficient storage, retrieval, and manipulation of data. They are classified into two main types:

• Relational Databases (SQL): Use tables with rows and columns (e.g., MySQL, PostgreSQL, SQLite).
• Non-Relational Databases (NoSQL): Use flexible schemas (e.g., MongoDB, Firebase).

Why Use Databases?

• Data Integrity: Ensures accuracy and consistency (e.g., no duplicate customer records in Ncell’s system).
• Concurrency Control: Handles multiple users accessing data simultaneously (e.g., eSewa’s simultaneous transactions).
• Security: Restricts access via permissions (e.g., bank databases only allow authorized queries).

Real-World Example: NTC’s Customer Database

The Nepal Telecommunications Company (NTC) stores customer details (name, phone number, billing history) in a relational database. When a customer checks their bill online, the system:

1. Takes the phone number as input.
2. Queries the Customers table for matching records.
3. Retrieves the latest billing data from the Bills table.
4. Displays the result in a structured format.

2. Database Design: ER Diagrams and Normalization

Before implementing a database, you must design its structure. This involves:

1. Entity-Relationship (ER) Diagrams: Visualize entities (e.g., Customers, Orders) and their relationships.
2. Normalization: Organize data to minimize redundancy (1NF, 2NF, 3NF).

ER Diagram Example: Daraz Order System

erDiagram
    Customers ||--o{ Orders : places
    Orders ||--|{ Order_Items : contains
    Products }|..|{ Order_Items : references
    Customers {
        int customer_id PK
        string name
        string email
    }
    Orders {
        int order_id PK
        int customer_id FK
        date order_date
    }
    Order_Items {
        int order_item_id PK
        int order_id FK
        int product_id FK
        int quantity
    }
    Products {
        int product_id PK
        string name
        float price
    }

Key Relationships:

• A customer can place many orders (1:N).
• An order contains many order items (1:N).
• An order item references a product (N:1).

Normalization: Eliminating Redundancy

Worked Example: Kathmandu Traffic Routes Database Suppose we design a database for Kathmandu’s traffic management system:

1. Entities:
   • Roads (road_id, name, length)
   • Traffic_Lights (light_id, road_id, status)
   • Violations (violation_id, vehicle_id, road_id, time)
2. ER Diagram:

   erDiagram
       Roads ||--o{ Traffic_Lights : has
       Roads ||--o{ Violations : on

3. Normalized Tables:
   • Roads(road_id, name, length)
   • Traffic_Lights(light_id, road_id, status)
   • Violations(violation_id, vehicle_id, road_id, time)

3. SQL Basics: Queries and Operations

SQL (Structured Query Language) is the standard language for interacting with relational databases. Key operations:

CRUD Operations

Worked Example: eSewa Transaction Log

Suppose eSewa stores transactions in a Transactions table:

CREATE TABLE Transactions (
    transaction_id INT PRIMARY KEY,
    user_id INT,
    amount FLOAT,
    status VARCHAR(20),
    timestamp DATETIME
);

Query to find all failed transactions in the last 7 days:

SELECT * FROM Transactions
WHERE status = 'Failed'
AND timestamp >= DATE_SUB(NOW(), INTERVAL 7 DAY);

Python Implementation:

import sqlite3

conn = sqlite3.connect("eSewa.db")
cursor = conn.cursor()

cursor.execute("""
    SELECT * FROM Transactions
    WHERE status = 'Failed'
    AND timestamp >= DATE('now', '-7 days')
""")

failed_transactions = cursor.fetchall()
for row in failed_transactions:
    print(f"Transaction ID: {row[0]}, Amount: {row[2]}, Time: {row[4]}")

conn.close()

Joins: Combining Tables

Example: Retrieve customer names along with their order details from the Daraz ER diagram.

SELECT Customers.name, Orders.order_id, Order_Items.quantity, Products.name AS product_name
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
JOIN Order_Items ON Orders.order_id = Order_Items.order_id
JOIN Products ON Order_Items.product_id = Products.product_id;

Visualization of a JOIN:

graph LR
    Customers["Customers\n(customer_id, name)"] -->|"1:N"| Orders["Orders\n(order_id, customer_id)"]
    Orders -->|"1:N"| Order_Items["Order_Items\n(order_item_id, order_id, product_id)"]
    Order_Items -->|"N:1"| Products["Products\n(product_id, name)"]

4. Transactions and Data Integrity

A transaction is a sequence of operations performed as a single logical unit. Key properties (ACID):

• Atomicity: All operations succeed or fail together.
• Consistency: Database moves from one valid state to another.
• Isolation: Transactions do not interfere with each other.
• Durability: Once committed, changes persist.

Example: Khalti Payment Transaction

When you transfer money via Khalti:

1. Debit from sender’s account.
2. Credit to receiver’s account.
3. If either step fails, the entire transaction is rolled back.

SQL Transaction Example:

import sqlite3

conn = sqlite3.connect("bank.db")
cursor = conn.cursor()

try:
    # Start transaction
    cursor.execute("BEGIN TRANSACTION")

    # Debit sender
    cursor.execute("UPDATE Accounts SET balance = balance - 100 WHERE account_id = 1")

    # Credit receiver (simulate failure)
    cursor.execute("UPDATE Accounts SET balance = balance + 100 WHERE account_id = 2")
    raise Exception("Transfer failed!")  # Simulate error

    # Commit if no error
    conn.commit()
except Exception as e:
    print(e)
    conn.rollback()  # Revert changes
finally:
    conn.close()

5. Python Database Integration

Python provides libraries to interact with databases:

• sqlite3: Lightweight, file-based database (good for local apps).
• pymysql/psycopg2: Connect to MySQL/PostgreSQL.
• ORM (SQLAlchemy, Django ORM): Map Python objects to database tables.

Example: SQLite with Python

import sqlite3

# Create a database and table
conn = sqlite3.connect("school.db")
cursor = conn.cursor()
cursor.execute("""
    CREATE TABLE IF NOT EXISTS Students (
        id INTEGER PRIMARY KEY,
        name TEXT,
        grade FLOAT
    )
""")

# Insert data
cursor.execute("INSERT INTO Students VALUES (1, 'Rohan', 8.5)")
conn.commit()

# Query data
cursor.execute("SELECT * FROM Students WHERE grade > 8")
print(cursor.fetchall())

conn.close()

ORM Example: SQLAlchemy

from sqlalchemy import create_engine, Column, Integer, String, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Student(Base):
    __tablename__ = 'students'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    grade = Column(Float)

# Setup
engine = create_engine('sqlite:///school.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

# Add a student
new_student = Student(name="Sita", grade=9.0)
session.add(new_student)
session.commit()

# Query
high_achievers = session.query(Student).filter(Student.grade > 8).all()
for student in high_achievers:
    print(student.name, student.grade)

6. Indexes and Query Optimization

Indexes improve query performance by creating a lookup structure (like a book’s index).

Example: NEPSE Stock Data

Suppose NEPSE stores stock prices in a Stocks table:

CREATE TABLE Stocks (
    stock_id INT PRIMARY KEY,
    symbol VARCHAR(10),
    price FLOAT,
    date DATE
);

Problem: Querying by date is slow for large datasets. Solution: Add an index:

CREATE INDEX idx_stock_date ON Stocks(date);

Now, queries like:

SELECT * FROM Stocks WHERE date = '2023-10-01';

run faster.

7. Error Handling in Database Operations

Always use try-except to handle errors (e.g., duplicate entries, connection failures).

Example:

import sqlite3

try:
    conn = sqlite3.connect("nonexistent.db")
except sqlite3.Error as e:
    print(f"Database error: {e}")
else:
    cursor = conn.cursor()
    try:
        cursor.execute("INSERT INTO Users VALUES (1, 'Alice')")
        conn.commit()
    except sqlite3.IntegrityError:
        print("Duplicate entry!")
    finally:
        conn.close()

In the Real World

1. eSewa’s Transaction System

   • Idea Used: ACID Transactions
   • How: When you pay a bill via eSewa, the system:
     1. Deducts money from your wallet (atomic operation).
     2. Credits the service provider (e.g., NTC, Ncell).
     3. If either step fails (e.g., insufficient balance), the entire transaction is rolled back, and you get a refund.

2. Khalti’s Payment Gateway

   • Idea Used: SQL Joins + Normalized Database
   • How: Khalti’s backend uses a normalized database with tables for:
     • Users (user_id, name, email)
     • Transactions (transaction_id, user_id, amount, status)
     • Merchants (merchant_id, name, bank_details)
   • When you transfer money, the system:

     SELECT Users.name, Transactions.amount, Merchants.name AS merchant
     FROM Transactions
     JOIN Users ON Transactions.user_id = Users.user_id
     JOIN Merchants ON Transactions.merchant_id = Merchants.merchant_id
     WHERE Transactions.transaction_id = 12345;
     

3. Daraz’s Order Fulfillment

   • Idea Used: ER Diagrams + Indexes
   • How: Daraz’s database has:
     • Customers, Orders, Order_Items, Products (as in the ER diagram above).
     • Indexes on order_id and product_id for fast lookups.
   • When you place an order:
     1. The system checks stock availability (query on Products table with index).
     2. Creates an Order record and links it to Order_Items.
     3. Updates inventory in real-time.

Exam Tip

1. SQL Queries: Always practice writing SELECT, JOIN, GROUP BY, and HAVING clauses. Exams often test:
   • Retrieving data with conditions (WHERE).
   • Combining tables (JOIN).
   • Aggregating data (COUNT, SUM, AVG).
2. Database Design: Be ready to:
   • Draw an ER diagram for a given scenario (e.g., a library system).
   • Explain normalization (1NF, 2NF, 3NF) with examples.
3. Python Integration: Know how to:
   • Connect to a database (sqlite3.connect, pymysql).
   • Execute queries (cursor.execute).
   • Handle transactions (BEGIN, COMMIT, ROLLBACK).
4. ORM: Understand the basics of SQLAlchemy or Django ORM, especially:
   • Defining a model (class).
   • Creating a session and querying objects.
5. Common Pitfalls:
   • Forgetting to commit() changes.
   • Not closing the connection (conn.close()).
   • Writing inefficient queries (e.g., nested loops instead of JOIN).

Mock Exam Question:

"Design a database for a hospital management system with entities: Patients, Doctors, Appointments, and Medicines. Draw an ER diagram and write SQL to:

1. Book an appointment for a patient.
2. Find all medicines prescribed to a patient.
3. List all doctors with more than 10 appointments."

Solution Outline:

1. ER Diagram:

   erDiagram
       Patients ||--o{ Appointments : has
       Doctors ||--o{ Appointments : conducts
       Appointments ||--|{ Prescriptions : includes
       Prescriptions ||--|| Medicines : prescribes

2. SQL Queries:

   -- 1. Book an appointment
   INSERT INTO Appointments (patient_id, doctor_id, date, time)
   VALUES (1, 5, '2023-11-15', '10:00');
   
   -- 2. Medicines prescribed to a patient
   SELECT Medicines.name
   FROM Prescriptions
   JOIN Appointments ON Prescriptions.appointment_id = Appointments.appointment_id
   JOIN Patients ON Appointments.patient_id = Patients.patient_id
   WHERE Patients.patient_id = 1;
   
   -- 3. Doctors with >10 appointments
   SELECT Doctors.name, COUNT(Appointments.appointment_id) AS appointment_count
   FROM Doctors
   JOIN Appointments ON Doctors.doctor_id = Appointments.doctor_id
   GROUP BY Doctors.doctor_id
   HAVING COUNT(*) > 10;