DBMS Basics: Systems, Models, Architectures & File Processing

TAKEAWAYS:

• A DBMS replaces manual file systems by storing data centrally, enforcing integrity, and enabling efficient queries via structured models.
• Database models (hierarchical, network, relational) differ in how they organize data—relational (tables) dominates modern systems.
• Architecture types (centralized vs. distributed) impact scalability, cost, and fault tolerance—distributed systems handle global operations (e.g., banks, e-commerce).
• Redundancy in file systems leads to anomalies; DBMS eliminates it via normalization and constraints.
• Applications span from local systems (e.g., eSewa transactions) to global platforms (e.g., Google’s distributed databases).
• Exam focus: Compare architectures, explain redundancy solutions, and link concepts to real-world examples (e.g., Ncell’s customer data).

1. File Processing vs. Database Systems

Why File Systems Fail

Traditional file systems (e.g., Excel sheets, text files) store data in isolated files. Problems include:

• Redundancy: Same data (e.g., customer name) repeated across files → wastes storage.
• Inconsistency: Updating one file but not others (e.g., changing a customer’s address in "Orders" but not "Billing").
• Security Risks: No centralized access control → unauthorized users may modify data.
• Poor Data Sharing: Multiple users/departments can’t collaborate efficiently.
• Limited Querying: Finding data requires manual searches (e.g., "All orders from Kathmandu in 2023").

How DBMS Solves These Problems

A Database Management System (DBMS) centralizes data and provides tools to:

1. Eliminate redundancy via normalization (covered in Unit 3).
2. Enforce integrity (e.g., constraints like "Salary cannot be negative").
3. Support concurrent access (multiple users querying/updating safely).
4. Enable complex queries (e.g., "Show all employees earning > Rs. 50,000 in Pokhara").

2. Database Models: How Data is Structured

Data is organized differently in various models. The three primary models are:

mindmap
  root((Database Models))
    Hierarchical
      Parent-Child Relationships
      Example: Old IBM IMS
    Network
      Many-to-Many via Pointers
      Example: CODASYL
    Relational
      Tables with Rows/Columns
      Example: MySQL, Oracle

Comparison Table

3. Database Architecture: Centralized vs. Distributed

Centralized Database

• Definition: Single server stores all data; all users access it remotely.
• Example: A small bank’s database where all branches connect to one central server in Kathmandu.

Pros:

• Simpler to manage.
• Lower cost (single server).
• Strong security (centralized access control).

Cons:

• Bottleneck: Server overload if too many users query simultaneously (e.g., during Diwali sales on Daraz).
• Single point of failure: If the server crashes, the entire system goes down.
• Scalability issues: Adding more users requires upgrading the central server.

Distributed Database

• Definition: Data is split across multiple physical locations (servers), connected via networks.
• Example: Ncell’s customer data stored in servers across Nepal, with each region handling its own queries.

Pros:

• Scalability: Add servers as needed (e.g., Google adds data centers globally).
• Fault tolerance: If one server fails, others take over (e.g., WhatsApp’s backup servers).
• Local processing: Reduces latency (e.g., Pathao’s ride requests processed regionally).

Cons:

• Complexity: Requires synchronization (e.g., ensuring all servers have the latest customer data).
• Higher cost: Multiple servers and network infrastructure.
• Security risks: More entry points for attacks (e.g., distributed denial-of-service attacks).

4. Redundancy in File Processing: A Real-World Example

Problem Scenario: Kathmandu Traffic Police Database

Imagine the traffic police manually track fines in two files:

1. DriverDetails.txt (Name, License No., Address)
2. Fines.txt (License No., Fine Amount, Date)

Redundancy Issue:

• If a driver moves, their address must be updated in both files. If forgotten, the system shows inconsistent data (e.g., "Driver X fined at old address").

DBMS Solution: Normalization

A DBMS would store this in two tables with a primary key (License No.) to link them:

Driver (LicenseNo, Name, Address)
Fines (LicenseNo, FineAmount, Date)

• No redundancy: Address is stored once.
• Integrity: Updating Driver.Address automatically reflects in queries.

5. Database Application Architectures

Databases are used in three-layer architectures (common in web apps like eSewa):

In the Real World

1. eSewa (Nepal)

   • Concept: Distributed database architecture with regional servers to handle high transaction volumes during festivals (e.g., Dashain).
   • How it works: User requests in Pokhara are processed by a Pokhara server, reducing latency. Data is synchronized across servers to ensure consistency.

2. Google Search

   • Concept: Distributed relational databases (spanner) to handle billions of queries daily.
   • How it works: Data is split across servers worldwide. When you search "best restaurants in Kathmandu," Google’s system queries the nearest server and combines results from others.

3. NTC (Nepal Telecom)

   • Concept: Centralized database for customer records (for smaller-scale operations).
   • How it works: All customer data (billing, subscriptions) is stored in a single server in Kathmandu. Employees in regional offices access it via a secure network.

4. Pathao (Ride-Hailing App)

   • Concept: Three-tier architecture with real-time database updates.
   • How it works:
     • UI Layer: Driver/customer apps show live locations.
     • Application Layer: Matches drivers to riders using algorithms.
     • Database Layer: Updates ride status (e.g., "On the way") in milliseconds.

6. Worked Example: Bank Loan System

Scenario: A bank uses a centralized DBMS to manage loans. Design a simple architecture to avoid redundancy.

Problem with File System

• Files:
  • Customer.txt (Name, Account No., Address)
  • Loans.txt (Account No., LoanAmount, InterestRate)
• Issue: If a customer moves, their address must be updated in both files. If not, the system shows outdated info.

DBMS Solution: Relational Model

erDiagram
  Customer ||--o{ Loan : "takes"
  Customer {
    int AccountNo PK
    string Name
    string Address
  }
  Loan {
    int LoanID PK
    int AccountNo FK
    decimal Amount
    decimal InterestRate
  }

• Primary Key (PK): AccountNo in Customer ensures unique records.
• Foreign Key (FK): AccountNo in Loan links to Customer.
• Benefit: Update Customer.Address once—all queries reflect the change.

SQL Queries

1. Find all loans for customers in Pokhara:

   SELECT L.LoanID, L.Amount
   FROM Loan L
   JOIN Customer C ON L.AccountNo = C.AccountNo
   WHERE C.Address LIKE '%Pokhara%';
   

2. Calculate total loans for a customer:

   SELECT SUM(Amount) AS TotalLoan
   FROM Loan
   WHERE AccountNo = 12345;
   

Exam Tip

1. Architecture Questions:

   • Centralized: Use for small-scale, low-cost systems (e.g., a local school’s student database).
   • Distributed: Use for global scalability (e.g., "Design a database for a multinational company like Daraz").
   • Key points to mention:
     • Scalability, fault tolerance, cost, and latency.

2. Redundancy Solutions:

   • Always link to normalization (Unit 3) and primary/foreign keys.
   • Example answer for "How does DBMS solve redundancy?":

     "DBMS eliminates redundancy by storing data in tables with relationships (e.g., Customer and Loan tables linked by AccountNo). This ensures data is stored once and accessed via joins, reducing anomalies."

3. Real-World Links:

   • eSewa: Distributed database for regional processing.
   • Ncell: Centralized database for customer records.
   • Google: Distributed databases for global queries.

4. Diagrams:

   • ER diagrams (for design questions).
   • Architecture diagrams (centralized vs. distributed).
   • Always label tables, keys, and relationships clearly.

5. Common Pitfalls:

   • Don’t confuse database models (hierarchical/network/relational) with architectures (centralized/distributed).
   • For redundancy, avoid vague answers like "DBMS is better." Instead, explain how (e.g., "via normalization and constraints").