Database Systems & Management: DBMS, Data Models, SQL, and Real-World Apps

TAKEAWAYS:

• A database system organizes data logically to enable fast, secure, and consistent access for multiple users (e.g., eSewa’s transaction records).
• DBMS (e.g., MySQL, Oracle) acts as a middleware between users/applications and data, handling storage, integrity, and concurrency.
• Relational databases use tables (rows/columns) with keys (PRIMARY, FOREIGN) to eliminate redundancy (e.g., Daraz’s inventory and orders).
• SQL (Structured Query Language) lets users query, insert, update, and delete data—critical for apps like Pathao’s ride scheduling.
• Normalization (1NF, 2NF, 3NF) reduces anomalies by structuring tables properly (e.g., NTC’s customer-billing data).
• Security threats (SQL injection, unauthorized access) require encryption, access controls, and auditing (e.g., Ncell’s user authentication).

1. Introduction to Database Systems

A database system is an organized collection of data stored electronically, designed to:

• Store data efficiently.
• Retrieve data quickly.
• Ensure data integrity and security.
• Support multiple users and applications.

Key Components of a Database System

• Hardware: Servers, storage devices (e.g., SSDs, HDDs), and networks.
• Software: DBMS (e.g., MySQL, PostgreSQL), application programs.
• Data: Raw facts (e.g., customer names, transaction amounts).
• Users: End-users, administrators, developers.
• Procedures: Rules for data access (e.g., backup policies).

Why Databases?

• Centralized data: Avoids duplication (e.g., NTC’s customer records in one place).
• Scalability: Handles growing data (e.g., Daraz’s inventory during festivals).
• Concurrency: Multiple users access data simultaneously (e.g., eSewa transactions).

2. Database Management System (DBMS)

A DBMS is software that manages databases, providing tools for:

• Data definition (CREATE, ALTER tables).
• Data manipulation (INSERT, UPDATE, DELETE).
• Data control (security, backup, recovery).

Popular DBMS Examples

How DBMS Works

1. Users/applications send requests (e.g., "Show all orders for User 123").
2. DBMS parses the request.
3. DBMS retrieves data from storage.
4. DBMS returns results to the user.

3. Data Models

A data model defines how data is structured and organized. The most common is the relational model, which uses tables (relations) with rows (tuples) and columns (attributes).

Relational Model Key Concepts

• Table (Relation): A 2D structure (e.g., Students table).
• Row (Tuple): A single record (e.g., one student’s data).
• Column (Attribute): A field (e.g., student_id, name).
• Primary Key (PK): Uniquely identifies a row (e.g., student_id).
• Foreign Key (FK): Links to another table’s PK (e.g., course_id in Enrollments).

Example: Student-Course Enrollment

erDiagram
    STUDENTS ||--o{ ENROLLMENTS : takes
    COURSES ||--o{ ENROLLMENTS : offers
    STUDENTS {
        int student_id PK
        string name
        string email
    }
    COURSES {
        int course_id PK
        string title
        int credits
    }
    ENROLLMENTS {
        int enrollment_id PK
        int student_id FK
        int course_id FK
        date enrollment_date
    }

Why Use Relational Models?

• Reduces redundancy: Data is stored once (e.g., a student’s name appears only in the Students table).
• Enforces integrity: Rules prevent invalid data (e.g., a course must exist before enrolling students).

4. Database Normalization

Normalization is the process of organizing data to minimize redundancy and dependency. The three key normal forms are:

1NF (First Normal Form)

• Each table cell contains atomic (indivisible) values.
• No repeating groups (e.g., a single column for courses_taken instead of a list).

Before 1NF (Bad):

After 1NF (Good):

STUDENTS (student_id, name)
COURSES (course_id, name)
ENROLLMENTS (student_id, course_id)

2NF (Second Normal Form)

• Must satisfy 1NF and all non-key attributes depend on the entire primary key.
• Applies to tables with composite keys (e.g., OrderID + ProductID).

Example: Order Details

Problem: Price depends only on ProductID, not the full key (OrderID + ProductID). Solution: Split into Orders and Products tables.

3NF (Third Normal Form)

• Must satisfy 2NF and no transitive dependencies (non-key attributes depend on other non-key attributes).
• Example: Address in a Customers table should not depend on CustomerID via City.

Why Normalize?

• Less redundancy: Saves storage (e.g., NTC’s customer addresses).
• Faster queries: Fewer joins needed.
• Easier updates: Fewer anomalies when data changes.

Worked Example: Normalizing a Library Database Unnormalized Data (Bad):

Step 1: 1NF

BOOKS (book_id, title, author, publisher)
COPIES (book_id, copies_available)

Step 2: 2NF (No composite keys here) Step 3: 3NF

• If publisher depends on author (e.g., all books by Fitzgerald are published by Penguin), split further:

AUTHORS (author_id, name)
PUBLISHERS (publisher_id, name)
BOOKS (book_id, title, author_id, publisher_id)

5. SQL (Structured Query Language)

SQL is the standard language for interacting with relational databases. Key commands:

Example: Daraz’s Order Query

-- Find all orders placed by a customer (e.g., customer_id = 1001)
SELECT o.order_id, o.order_date, p.product_name, o.quantity, o.total_price
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
WHERE o.customer_id = 1001;

6. Database Security

Security ensures data is protected from unauthorized access, corruption, or theft. Key measures:

Common Threats

Security Measures

• Authentication: Username/password, biometrics (e.g., Ncell’s fingerprint login).
• Authorization: Role-based access (e.g., admins vs. regular users in NEPSE).
• Encryption: Data scrambled (e.g., eSewa’s transaction encryption).
• Backups: Regular snapshots (e.g., NTC’s daily database backups).

SQL Injection Example (Bad vs. Good)

-- UNSAFE: Direct user input in SQL
SELECT * FROM Users WHERE username = '[user_input]' AND password = '[user_input]';

-- SAFE: Use parameterized queries
PREPARE stmt FROM 'SELECT * FROM Users WHERE username = ? AND password = ?';
EXECUTE stmt USING 'admin', 'password123';

7. Database Administration

Database administrators (DBAs) ensure databases run smoothly:

• Backup and Recovery: Restore data after failures (e.g., NTC’s disaster recovery plan).
• Performance Tuning: Optimize queries (e.g., indexing in Daraz’s search).
• User Management: Grant/revoke access (e.g., Ncell’s app permissions).

In the Real World

1. eSewa’s Transaction Records
   • Idea: Relational databases store user accounts, transactions, and balances in normalized tables (e.g., Users, Transactions, Wallets).
   • Why? Prevents duplicate user data and ensures transaction integrity (e.g., no double-charging).

2. Daraz’s Order Fulfillment

   • Idea: SQL queries dynamically update inventory and track orders in real time.
   • Example: When a customer buys a product, Daraz’s system:

     -- Deduct stock
     UPDATE Inventory SET quantity = quantity - 1 WHERE product_id = 123;
     
     -- Create order record
     INSERT INTO Orders (customer_id, product_id, quantity) VALUES (456, 123, 1);
     

3. NTC’s Customer-Billing System

   • Idea: Normalized tables separate Customers, Plans, and Payments to avoid billing errors.
   • Worked Example:
     • Before Normalization: A Bills table might list customer_name, plan_name, amount—but plan_name repeats.
     • After Normalization:

       CUSTOMERS (customer_id, name)
       PLANS (plan_id, name, price)
       BILLS (bill_id, customer_id, plan_id, amount, due_date)
       

     • Query to find all bills for a customer:

       SELECT b.bill_id, p.name AS plan, b.amount, b.due_date
       FROM BILLS b
       JOIN PLANS p ON b.plan_id = p.plan_id
       WHERE b.customer_id = 1001;
       

Exam Tip

• Define and Compare: Know the difference between a database (data storage) and a DBMS (software managing it).
• Normalization: Always show before/after examples for 1NF, 2NF, and 3NF.
• SQL Queries: Practice SELECT, JOIN, and WHERE with real-world scenarios (e.g., Daraz orders).
• Security: Link threats (SQL injection) to real apps (eSewa, banks).
• Diagrams: Draw ER diagrams for relationships (e.g., Students-Enrollments-Courses) and SQL execution flows.
• Application Focus: Tie concepts to Nepali apps (eSewa, Pathao) or global ones (WhatsApp’s user chats use databases).