Database Systems: Models, DBMS, Architecture & Management

TAKEAWAYS:

• A database is an organized collection of data stored electronically, accessed via a DBMS (e.g., MySQL, PostgreSQL).
• Relational databases (tables with rows/columns) dominate; other models include hierarchical (tree-like) and network (graph-like).
• DBMS manages data storage, retrieval, and security (e.g., user authentication in Khalti).
• Database architecture follows a 3-tier model: client → application server → database server.
• Normalization (1NF to 3NF) eliminates redundancy (e.g., in NTC’s customer records).
• Distributed databases (e.g., Ncell’s CDRs) improve scalability vs. centralized systems.

1. What is a Database?

A database is an organized, structured collection of data stored electronically. It allows efficient storage, retrieval, and management of data. Unlike spreadsheets or flat files, databases use a structured schema (rules for data organization) to ensure consistency and reduce redundancy.

Why Use Databases?

2. Database Models

Databases are classified based on their data organization structure. The three primary models are:

A. Relational Model (Most Common)

• Data stored in tables (relations) with rows (tuples) and columns (attributes).
• Uses SQL (Structured Query Language) for queries.
• Example: MySQL (used by Daraz for order management).

erDiagram
  Customers ||--o{ Orders : places
  Orders ||--o{ Products : contains
  Customers {
    int customer_id PK, "PK = Primary Key"
    string name
    string email
  }
  Orders {
    int order_id PK
    int customer_id FK, "FK = Foreign Key"
    date order_date
  }
  Products {
    int product_id PK
    string name
    float price
  }

ER diagram showing relational model with primary/foreign keys labeled Worked Example: Daraz Order Processing

1. A customer places an order → data inserted into the Orders table.
2. The order links to Customers (via customer_id) and Products (via product_id).
3. SQL query to retrieve order details:

   SELECT Customers.name, Orders.order_date, Products.name, Products.price
   FROM Orders
   JOIN Customers ON Orders.customer_id = Customers.customer_id
   JOIN Products ON Orders.product_id = Products.product_id
   WHERE Orders.order_id = 12345;
   

B. Hierarchical Model

• Data organized in a tree structure (parent-child relationships).
• Example: Old banking systems where accounts branch from customers.

graph TD
    A["Customers"] --> B["Savings Account"]
    A --> C["Loan Account"]
    B --> D["Transaction 1"]
    B --> E["Transaction 2"]
    C --> F["EMI 1"]

Use Case: Legacy systems like NTC’s billing hierarchy (main branch → sub-offices → customers).

C. Network Model

• More flexible than hierarchical; allows many-to-many relationships.
• Example: Airline reservation systems (passengers → flights → seats).

graph LR
  A["Passengers"] -- "books" --> B["Flights"]
  B -- "contains" --> C["Seats"]
  A -- "has" --> D["Loyalty Points"]

3. Database Management System (DBMS)

A DBMS is software that interacts with the database to perform tasks like:

• Storing, updating, and retrieving data.
• Enforcing security (e.g., role-based access in banks).
• Managing concurrency (e.g., multiple users booking flights on Ncell’s system).

Popular DBMS Examples

4. Database Architecture

Most databases follow a 3-tier architecture:

1. Client Tier: User interface (e.g., eSewa mobile app).
2. Application Tier: Business logic (e.g., Khalti’s payment processing).
3. Database Tier: Stores data (e.g., Ncell’s customer records in PostgreSQL).

Real-World Tie-In: NEPSE Stock Tracking

• Client Tier: Trader’s dashboard (shows live stock prices).
• Application Tier: Server validates trades and checks limits.
• Database Tier: Stores historical prices, user portfolios, and transactions.

5. Data Storage: Relational Model Deep Dive

In relational databases, data is stored in tables with constraints:

• Primary Key (PK): Unique identifier (e.g., customer_id in eSewa).
• Foreign Key (FK): Links tables (e.g., order_id in Daraz’s Orders table references Customers).
• Normalization: Organizing data to minimize redundancy (1NF to 3NF).

Normalization Example: Kathmandu Traffic Routes

Problem: Storing traffic data in one table causes redundancy. Solution: Normalize into 3 tables:

1. Roads (PK: road_id, attributes: name, length).
2. Junctions (PK: junction_id, FK: road_id).
3. TrafficViolations (PK: violation_id, FK: junction_id).

erDiagram
  Roads ||--o{ Junctions : connects
  Junctions ||--o{ TrafficViolations : records
  Roads {
    int road_id PK
    string name
    float length
  }
  Junctions {
    int junction_id PK
    int road_id FK
  }
  TrafficViolations {
    int violation_id PK
    int junction_id FK
    date violation_time
  }

6. Centralized vs. Distributed Databases

Worked Example: Ncell’s Call Detail Records (CDRs)

• Centralized: All CDRs stored in one server → slow for nationwide queries.
• Distributed: CDRs split across regional servers → faster access for any user.

7. Database Security

Critical for protecting sensitive data (e.g., user passwords in Khalti). Key measures:

• Authentication: Username/password (or biometrics in eSewa).
• Authorization: Role-based access (e.g., admins vs. regular users in NEPSE).
• Encryption: SSL/TLS for data in transit (e.g., online banking).
• Backup: Regular snapshots to recover from failures (e.g., Daraz’s order history).

In the Real World

1. eSewa

   • Idea Used: Relational DBMS (MySQL) to store transactions, user profiles, and payment records.
   • How: When you pay a bill, eSewa’s backend queries the Users and Transactions tables to deduct funds and update records atomically (all-or-nothing).

2. Pathao

   • Idea Used: Distributed Database for ride requests and driver locations.
   • How: Ride data is sharded by geographic region (e.g., Kathmandu vs. Pokhara servers) to reduce latency. Uses NoSQL (MongoDB) for flexible schema (e.g., dynamic ride attributes).

3. NEPSE (Nepal Stock Exchange)

   • Idea Used: 3-Tier Architecture + Normalization for stock trades.
   • How:
     • Client Tier: Trader’s web/mobile app.
     • Application Tier: Validates trades against user limits (e.g., no short-selling).
     • Database Tier: Stores trades in normalized tables (Users, Stocks, Trades) to prevent anomalies (e.g., duplicate trades).

Exam Tip

1. Definitions Matter: Memorize key terms like DBMS, relational model, and normalization. Exams often ask for definitions first.
2. Compare Models: Be ready to contrast relational (tables), hierarchical (trees), and network (graphs) models with examples.
3. SQL Basics: Know SELECT, JOIN, and WHERE clauses. A simple query like:

   SELECT name FROM Customers WHERE city = 'Kathmandu';
   

   might appear in short-answer questions.
4. Real-World Links: Connect theory to local apps (e.g., "How does Daraz use foreign keys?").
5. Diagrams: Draw ER diagrams or 3-tier architecture in exams—visuals score extra marks!
6. Short vs. Long Answers:
   • Short: Define DBMS (2 marks) or list 3 benefits of databases (3 marks).
   • Long: Explain normalization with a table example (10 marks) or compare centralized/distributed DBs (10 marks).

Final Checklist Before Exam:

• Can you sketch a 3-tier database architecture?
• Do you know 2 real-world uses of relational databases in Nepal?
• Can you write a SQL query to join two tables?
• Are you comfortable explaining 1NF vs. 2NF with an example?