DBMS Fundamentals: Architecture, Models, SQL & Real-World Systems

TAKEAWAYS:

• A DBMS organizes data efficiently using models (relational, hierarchical, network) and enforces integrity via constraints (primary keys, foreign keys).
• SQL (Structured Query Language) is the standard language for interacting with relational databases, with commands like SELECT, INSERT, and JOIN for querying and updating data.
• Normalization reduces redundancy in databases by structuring tables into 1NF, 2NF, and 3NF, improving data consistency and query performance.
• Real-world systems like eSewa (transaction processing), Daraz (inventory management), and NEPSE (stock trading) rely on DBMS for scalability and security.
• Distributed databases (e.g., cloud-based systems) improve accessibility and fault tolerance but introduce challenges like data synchronization.
• Security in DBMS involves authentication (e.g., role-based access), encryption, and backup strategies to protect against breaches or failures.

---

1. What is a Database Management System (DBMS)?

A DBMS is software that manages databases by storing, retrieving, and manipulating data efficiently. It acts as an intermediary between users/applications and the database, ensuring data integrity, security, and concurrency control.

Key Features of a DBMS

---

2. Database System Architecture

The architecture defines how data is organized and accessed. Two primary types:

Worked Example: eSewa’s Transaction Log eSewa uses a centralized DBMS to record every transaction (e.g., bill payments, remittances). When you pay a bill:

1. Your request → eSewa’s server.
2. DBMS checks balance, deducts amount, and logs the transaction.
3. Confirmation sent to you and the service provider (e.g., NTC).

Why centralized? Low latency for high-frequency transactions (e.g., 1000+ payments/minute).

---

3. Data Models in DBMS

Data models define how data is structured and related. Three key models:

A. Relational Model (Most Common)

• Data stored in tables (rows = records, columns = attributes).
• Uses SQL for queries.
• Example: Daraz’s Orders table:

  CREATE TABLE Orders (
      OrderID INT PRIMARY KEY,
      CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID),
      ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
      OrderDate DATE
  );
  

B. Hierarchical Model

• Data organized in a tree structure (parent-child relationships).
• Example: Ncell’s call records (each call has one parent account).
• Disadvantage: Inflexible for complex queries (e.g., retrieving all calls from a contact).

C. Network Model

• Generalization of hierarchical model; allows many-to-many relationships.
• Example: Bank loan system (a loan can have multiple borrowers, and a borrower can have multiple loans).

Comparison Table:

---

4. SQL Basics: Querying and Manipulating Data

SQL (Structured Query Language) is the standard for relational DBMS. Key commands:

A. Data Query Language (DQL)

• SELECT: Retrieve data.

  SELECT CustomerName, OrderDate
  FROM Orders
  WHERE ProductID = 101;
  

  Example: Find all customers who ordered ProductID 101 in Daraz.

B. Data Manipulation Language (DML)

• INSERT, UPDATE, DELETE: Modify data.

  INSERT INTO Orders (OrderID, CustomerID, ProductID, OrderDate)
  VALUES (2024001, 5001, 101, '2024-05-20');
  

C. Data Definition Language (DDL)

• CREATE, ALTER, DROP: Define database structure.

  CREATE TABLE Customers (
      CustomerID INT PRIMARY KEY,
      Name VARCHAR(100),
      Email VARCHAR(100)
  );
  

Worked Example: NEPSE Stock Query To find all stocks with a price > Rs. 1000:

SELECT StockName, Price
FROM Stocks
WHERE Price > 1000
ORDER BY Price DESC;

---

5. Database Normalization

Normalization reduces redundancy and improves data integrity by organizing tables into normal forms:

Before Normalization (Redundant):

After 3NF:

• Customers (CustomerID, CustomerName)
• Products (ProductID, ProductName, Price)
• Orders (OrderID, CustomerID, ProductID)

Why Normalize?

• Reduces storage space (e.g., CustomerName stored once).
• Prevents update anomalies (e.g., changing Ram’s name in one row only).

---

6. Security in DBMS

DBMS security protects data from unauthorized access or corruption. Key approaches:

A. Authentication and Authorization

• Authentication: Verify user identity (e.g., username/password, biometrics).
• Authorization: Grant permissions (e.g., SELECT access to Customers table for staff only). Example: Ncell restricts call records access to authorized employees.

B. Encryption

• Data at rest: Encrypt stored data (e.g., eSewa’s customer passwords).
• Data in transit: Use SSL/TLS for secure communication (e.g., online banking).

C. Backup and Recovery

• Regular backups: Daily snapshots of databases (e.g., Daraz’s inventory).
• Transaction logs: Record changes to restore data after crashes.

---

In the Real World

1. eSewa’s Transaction Processing

   • Idea Used: Relational DBMS + SQL
   • How? Every transaction (e.g., Rs. 500 bill payment) is logged in a normalized Transactions table with TransactionID, UserID, Amount, and Status. SQL queries retrieve summaries for users or audits.

2. Daraz’s Inventory Management

   • Idea Used: Distributed DBMS + Normalization
   • How? Product data is stored across regional servers (e.g., Kathmandu, Pokhara). Normalized tables (Products, Orders, Suppliers) ensure no redundancy when updating stock levels.

3. NEPSE’s Stock Trading System

   • Idea Used: Centralized DBMS + Concurrency Control
   • How? All buy/sell orders are processed in a single database to prevent duplicate trades. Locks ensure only one trader can update a stock’s price at a time.

---

Exam Tip

1. Define Clearly:

   • Start answers with precise definitions (e.g., "A DBMS is software that...").
   • Use bullet points for features/advantages (e.g., "DBMS provides data independence via...").

2. Compare Models:

   • Exams often ask to compare relational vs. hierarchical/network models. Use a table (as above) to score marks.

3. SQL Examples:

   • Always include one SQL query in answers about relational DBMS (e.g., SELECT, JOIN).
   • Trace Example: For a question on normalization, show before/after tables.

4. Real-World Links:

   • Tie concepts to Nepalese examples (e.g., "Like eSewa’s transaction logs, a DBMS ensures...").
   • Mention security (e.g., "Ncell uses role-based access to restrict...").

5. Diagrams:

   • Draw ER diagrams (if asked about relationships) or normalization steps (1NF → 3NF).
   • Label all components (e.g., primary keys, foreign keys).

---

Visual Summary: