Database Basics: Tables, Queries, Keys, and Design

TAKEAWAYS:

• A database stores organized data in tables, linked by relationships (e.g., orders → customers).
• Normalization reduces redundancy by splitting tables into logical structures (1NF → 3NF).
• SQL (Structured Query Language) retrieves, inserts, and updates data using SELECT, JOIN, and WHERE.
• Primary keys uniquely identify records (e.g., student_id), while foreign keys link tables (e.g., order_id in an order_details table).
• Indexes speed up searches (e.g., CREATE INDEX idx_name ON customers(last_name)).
• ACID properties ensure reliable transactions (Atomicity, Consistency, Isolation, Durability).

1. What is a Database?

A database is an organized collection of structured data stored electronically. It allows efficient CRUD operations:

• Create (insert new records)
• Read (query data)
• Update (modify records)
• Delete (remove records)

Types of Databases

2. Relational Database Model

The most common model uses tables (relations) with:

• Rows (tuples): Individual records (e.g., one customer).
• Columns (attributes): Fields (e.g., customer_id, name, email).
• Relationships: Links between tables (e.g., one customer can place many orders).

Example: E-Commerce Database

erDiagram
    CUSTOMERS ||--o{ ORDERS : places
    ORDERS ||--|{ ORDER_ITEMS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : includes
    CUSTOMERS {
        int customer_id PK
        string name
        string email
        string address
    }
    ORDERS {
        int order_id PK
        int customer_id FK
        date order_date
        decimal total_amount
    }
    ORDER_ITEMS {
        int order_item_id PK
        int order_id FK
        int product_id FK
        int quantity
        decimal unit_price
    }
    PRODUCTS {
        int product_id PK
        string product_name
        decimal price
        int stock_quantity
    }

Key Terms:

• Primary Key (PK): Uniquely identifies a row (e.g., customer_id).
• Foreign Key (FK): Links to a PK in another table (e.g., customer_id in orders).
• Composite Key: Combination of columns as PK (e.g., order_id + product_id in order_items).

3. Normalization: Organizing Data Efficiently

Normalization reduces data redundancy and anomalies (update/delete issues) by dividing tables into normal forms.

Normal Forms

Worked Example: Normalizing a Poorly Designed Table

Before (Unnormalized):

After (3NF):

Why?

• Eliminates repeating groups (Product1, Product2).
• Supports queries like "All orders by Ram" or "Total laptops sold."

4. SQL: The Language of Databases

SQL (Structured Query Language) is used to interact with databases. Key commands:

Data Query Language (DQL)

-- Select all customers from Kathmandu
SELECT name, email
FROM customers
WHERE city = 'Kathmandu';

```figure
{"type":"fields","width":32,"rows":[[{"label":"SELECT","bits":6},{"label":"columns","bits":10},{"label":"FROM","bits":6},{"label":"table","bits":10}]],"caption":"Example DQL structure (SELECT * FROM orders)"}

-- Join orders with customer details SELECT c.name, o.order_date, o.total_amount FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

#### **Data Manipulation Language (DML)**
```sql
-- Insert a new order
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES (201, 101, '2023-10-15', 12000);

-- Update stock after an order
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 5 AND stock_quantity > 0;

Data Definition Language (DDL)

-- Create a table
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    stock_quantity INT
);

-- Add an index for faster searches
CREATE INDEX idx_product_name ON products(name);

5. Database Applications in Nepal

In the Real World

1. eSewa

   • Idea Used: Relational Database + Transactions
   • How? Stores user accounts (PK: user_id), transactions (PK: transaction_id, FK: user_id), and merchant details in normalized tables. Ensures ACID compliance for payments (e.g., deducting Rs. 500 from your wallet atomically when paying a bill).

2. Nepal Stock Exchange (NEPSE)

   • Idea Used: Normalized Tables + Indexes
   • How? Tracks company shares (companies table), trades (trades table with PK: trade_id, FK: company_id), and investor portfolios. Indexes on company_symbol speed up stock price lookups.

3. Pathao (Ride-Hailing)

   • Idea Used: Real-Time Queries + Foreign Keys
   • How? Matches drivers (drivers table) to riders (riders table) via ride_id (FK in both). Uses JOIN to show "All rides in Kathmandu today" with driver/rider details.

Worked Example: Daraz Order Processing

Scenario: A customer buys 2 products from Daraz. How does the database handle this?

1. Insert into orders:

   INSERT INTO orders (order_id, customer_id, order_date, status)
   VALUES (999, 123, '2023-10-16', 'Processing');
   

2. Insert into order_items (one row per product):

   INSERT INTO order_items (order_item_id, order_id, product_id, quantity, price)
   VALUES (1001, 999, 5, 1, 1500), (1002, 999, 7, 2, 800);
   

3. Update products stock:

   UPDATE products
   SET stock_quantity = stock_quantity - 1
   WHERE product_id = 5;
   UPDATE products
   SET stock_quantity = stock_quantity - 2
   WHERE product_id = 7;
   

Visualization:

sequenceDiagram
    participant Customer
    participant DarazDB
    participant Products
    Customer->>DarazDB: Places order (order_id=999)
    DarazDB->>DarazDB: Insert into ORDERS
    DarazDB->>DarazDB: Insert into ORDER_ITEMS (2 rows)
    DarazDB->>Products: Update stock (product_id=7, quantity=-2)
    Products-->>DarazDB: Stock updated
    DarazDB-->>Customer: Confirmation email
    note right of DarazDB: SQL:
    SET stock_quantity = stock_quantity - 2
    WHERE product_id = 7;

6. Database Security and Ethics

• Backup: Regularly back up data (e.g., daily snapshots).
• Access Control: Use roles (e.g., admin, customer) with GRANT/REVOKE in SQL.

  GRANT SELECT ON products TO customer_role;
  

• Ethics: Protect user data (e.g., GDPR-like rules in Nepal’s Digital Transaction Act).

Exam Tip

1. Diagrams: Always draw an ER diagram for relational questions (use ||--o{ for one-to-many).
2. SQL Practice: Memorize SELECT, JOIN, WHERE, and GROUP BY syntax. Examiners test:
   • Writing a query to "Find all orders over Rs. 10,000" → SELECT * FROM orders WHERE total_amount > 10000.
   • Normalizing a table → Show steps from 1NF to 3NF.
3. Real-World Links: Connect theory to eSewa payments, NEPSE trades, or Daraz inventory. Example:

   "How would you design a database for a bank’s loan system? Use customers, loans, and payments tables with appropriate keys."

4. Shortcuts:
   • PK/FK: Underline primary keys, use arrows for foreign keys in diagrams.
   • Normalization: Start with "This table has repeating groups → violate 1NF" and fix it.