Database Systems & Management: DBMS, ERD, SQL, Normalization, Security

What is a Database System?

A database system is an organized collection of data stored and accessed electronically. It allows efficient storage, retrieval, and management of data. The core components include:

• Database: Organized collection of data.
• DBMS (Database Management System): Software that interacts with the database (e.g., MySQL, Oracle, PostgreSQL).
• Users: End-users, application programs, or system administrators.
• Hardware: Storage devices, processors, and memory.

How a Database System Works

A DBMS acts as an intermediary between users and the database. It provides:

• Data definition language (DDL): Defines the database schema (e.g., CREATE TABLE).
• Data manipulation language (DML): Manipulates data (e.g., INSERT, UPDATE, DELETE).
• Data control language (DCL): Manages access (e.g., GRANT, REVOKE).
• Query processing: Executes user queries efficiently.

flowchart TD
    A["User/Application"] -->|"Query/Command"| B["DBMS"]
    B -->|"Processes Query"| C["Query Optimizer"]
    C -->|"Generates Execution Plan"| D["Storage Engine"]
    D -->|"Retrieves/Stores Data"| E["Database"]
    E -->|"Returns Result"| B
    B -->|"Result"| A

Types of Database Systems

Entity-Relationship (ER) Modeling

ER modeling is a visual way to represent database structure using entities, attributes, and relationships.

Key Components

1. Entity: A real-world object (e.g., Student, Course).
2. Attribute: Properties of an entity (e.g., StudentID, Name).
3. Relationship: Association between entities (e.g., Enrolls between Student and Course).
4. Cardinality: Describes how many instances of one entity relate to another (1:1, 1:M, M:N).

ER Diagram Example: University Database

erDiagram
    STUDENT ||--o{ ENROLLMENT : enrolls
    ENROLLMENT ||--|| COURSE : takes
    STUDENT {
        int StudentID PK
        string Name
        date DOB
    }
    COURSE {
        int CourseID PK
        string Title
        int Credits
    }
    ENROLLMENT {
        int EnrollmentID PK
        int StudentID FK
        int CourseID FK
        date EnrollmentDate
    }

Structured Query Language (SQL)

SQL is the standard language for interacting with relational databases.

Basic SQL Commands

Worked Example: Managing a Daraz Order Queue

Suppose Daraz uses a database to track orders. Here’s how SQL helps:

1. Create a table for orders:

   CREATE TABLE Orders (
       OrderID INT PRIMARY KEY,
       CustomerID INT,
       ProductID INT,
       OrderDate DATE,
       Status VARCHAR(20)
   );
   

2. Insert an order:

   INSERT INTO Orders (OrderID, CustomerID, ProductID, OrderDate, Status)
   VALUES (1001, 5001, 2005, '2023-10-15', 'Processing');
   

3. Update order status:

   UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 1001;
   

4. Query pending orders:

   SELECT * FROM Orders WHERE Status = 'Pending';
   

Database Normalization

Normalization reduces redundancy and improves data integrity by organizing data into tables.

Normal Forms

Worked Example: Normalizing a Bank Loan Database

Unnormalized Table:

Issues:

• BranchName and BranchManager are repeated.
• InterestRate depends on BranchName, not LoanID.

Normalized Tables (3NF):

1. Loans:

   LoanID	CustomerName	LoanAmount	BranchID (FK)
   1	Ram	500000	1
   2	Sita	300000	2

2. Branches:

   BranchID	BranchName	InterestRate	BranchManager
   1	Kathmandu	8%	Ramesh
   2	Pokhara	7%	Suresh

Database Security

Security ensures data integrity, confidentiality, and availability.

Threats and Countermeasures

Worked Example: Securing NEPSE’s Database

NEPSE (Nepal Stock Exchange) must protect sensitive financial data:

1. Encryption: Use AES-256 to encrypt customer transaction records.
2. Access Control: Grant SELECT permissions only to analysts and INSERT/UPDATE to authorized traders.
3. Audit Logs: Track all UPDATE operations on stock prices to detect fraud.

In the Real World

1. eSewa (Nepal):

   • Idea Used: Relational DBMS (MySQL/PostgreSQL) to store user transactions, payment histories, and service requests.
   • How: When you pay your electricity bill via eSewa, the system queries the database to verify your account, deduct the amount, and update the NTC’s records in real-time.

2. Khalti (Nepal):

   • Idea Used: Normalization and transactions (ACID properties) to ensure money transfers between users are atomic (either fully complete or fully rolled back).
   • How: If you send Rs. 5000 to a friend, Khalti’s database locks both your and your friend’s accounts during the transfer to prevent double-spending.

3. Google Maps (Global):

   • Idea Used: NoSQL databases (e.g., Bigtable) to store geospatial data and handle massive scale.
   • How: When you search for "restaurants near me," Google’s database retrieves location data, user reviews, and ratings in milliseconds using distributed indexing.

Exam Tip

1. ER Diagrams: Always label entities, attributes (with primary keys underlined), and relationships with cardinality (e.g., 1:M). Partial credit is given for correct symbols even if labels are missing.
2. SQL Queries: Practice writing JOIN queries (e.g., INNER JOIN, LEFT JOIN) as they are frequently tested. Example:

   SELECT Students.Name, Courses.Title
   FROM Students
   INNER JOIN Enrollment ON Students.StudentID = Enrollment.StudentID
   INNER JOIN Courses ON Enrollment.CourseID = Courses.CourseID;
   

3. Normalization: Questions often ask you to convert an unnormalized table to 3NF. Start by identifying partial and transitive dependencies.
4. Security: Know the difference between authentication (proving identity) and authorization (granting permissions). Example:
   • Authentication: Logging in with a username and password.
   • Authorization: A bank teller can update accounts but not delete customer records.
5. Real-World Applications: Relate theoretical concepts to Nepalese examples (e.g., how Ncell’s customer database uses normalization to avoid redundancy in phone plans and usage records).