Database Management System & SQL: Concepts, DBMS, SQL Queries & Design

```mermaid
mindmap
  root((Database Management System))
    Concepts
      Definition
      Features
      Types
    DBMS Architecture
      Users
      Database
      Software
      Hardware
    Data Models
      Hierarchical
      Network
      Relational
      Object-Oriented
    SQL Basics
      DDL
      DML
      DCL
      TCL
    Database Design
      ER Model
      Normalization
      Constraints
    Applications
      Banking
      Airlines
      Schools

What is a Database?

A database is an organized collection of structured data stored electronically. Think of it like a digital filing cabinet where you store and retrieve information efficiently.

Database server hardware (Image: Aaron Hall, CC BY-SA 2.0, via Wikimedia Commons)

Why Use a Database?

• Organized Data: Data is stored systematically, making it easy to access.
• Reduces Redundancy: Avoids duplicate data.
• Data Integrity: Ensures accuracy and consistency.
• Security: Controls access to sensitive data.
• Efficient Retrieval: Faster search and updates compared to manual files.

Example: Instead of keeping student records in separate notebooks, a school can store them in a database where teachers, admins, and students can access grades, attendance, and personal details quickly.

What is a Database Management System (DBMS)?

A DBMS is software that helps create, manage, and manipulate databases. It acts as an interface between users and the database.

Key Functions of DBMS

1. Data Definition: Creates and modifies database structure.
2. Data Storage: Stores data efficiently.
3. Data Manipulation: Inserts, updates, and deletes data.
4. Data Retrieval: Fetches data based on queries.
5. Security & Backup: Protects data from unauthorized access and ensures recovery in case of failures.

Example: MySQL, Oracle, and Microsoft Access are popular DBMS tools.

Types of Databases

Databases can be classified based on their structure and use:

Visual Comparison:

Relational Database Model (Most Important for NEB!)

A relational database stores data in tables (relations) linked by keys.

Key Terms

1. Table: A collection of records (rows) and fields (columns).
   • Example: Students table with columns: RollNo, Name, Grade.
2. Primary Key (PK): Uniquely identifies each record (e.g., RollNo).
3. Foreign Key (FK): Links tables (e.g., TeacherID in a Classes table refers to TeacherID in a Teachers table).
4. Attribute: A column in a table (e.g., Name, Age).

Example Table:

| RollNo (PK) | Name    | Grade |
|-------------|---------|-------|
| 101         | Ram     | A     |
| 102         | Sita    | B     |

Structured Query Language (SQL)

SQL is the standard language used to interact with relational databases.

SQL Commands Categories

SQL Worked Examples

1. Creating a Table (DDL)

CREATE TABLE Students (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50),
    Grade CHAR(1)
);

Explanation:

• Students is the table name.
• RollNo is the primary key.
• VARCHAR(50) means a text field with max 50 characters.
• CHAR(1) means a single-character field (e.g., A, B).

2. Inserting Data (DML)

INSERT INTO Students (RollNo, Name, Grade)
VALUES (101, 'Ram', 'A');

Output:

| RollNo | Name | Grade |
|--------|------|-------|
| 101    | Ram  | A     |

3. Selecting Data (Query)

SELECT Name, Grade FROM Students WHERE Grade = 'A';

Output:

| Name | Grade |
|------|-------|
| Ram  | A     |

4. Updating Data

UPDATE Students SET Grade = 'B' WHERE RollNo = 101;

New Output:

| RollNo | Name | Grade |
|--------|------|-------|
| 101    | Ram  | B     |

5. Deleting Data

DELETE FROM Students WHERE RollNo = 101;

Output (after deletion): (Table is now empty for RollNo = 101.)

Database Design: Entity-Relationship (ER) Model

The ER model visually represents:

• Entities (e.g., Student, Teacher).
• Attributes (e.g., Name, Age).
• Relationships (e.g., Enrolls, Teaches).

Example ER Diagram for a School:

erDiagram
  STUDENT ||--o{ ENROLLMENT : "takes"
  ENROLLMENT ||--|| COURSE : "enrolled_in"
  TEACHER ||--o{ COURSE : "teaches"
  STUDENT {
    int RollNo PK
    string Name
    string Grade
  }
  COURSE {
    int CourseID PK
    string Title
  }
  TEACHER {
    int TeacherID PK
    string Name
  }
  ENROLLMENT {
    int RollNo FK
    int CourseID FK
    date Date
  }

Key Symbols:

• Rectangle = Entity (e.g., Student).
• Oval = Attribute (e.g., Name).
• Diamond = Relationship (e.g., Enrolls).
• Crow’s Foot (||--o{) = One-to-Many relationship.

Database Normalization

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

Normal Forms (NF)

Example: Converting Unnormalized to 3NF Bad Design (Violates 1NF & 2NF):

| OrderID | Items (Repeating Group) |
|---------|------------------------|
| 1001    | Laptop, Mouse          |
| 1002    | Keyboard               |

Normalized Design (3NF):

Orders (OrderID, OrderDate)
Items (ItemID, ItemName, Price)
OrderDetails (OrderID, ItemID, Quantity)

Constraints in SQL

Constraints enforce rules on data to maintain integrity.

Database Transactions (ACID Properties)

A transaction is a single logical operation (e.g., transferring money from one account to another). DBMS ensures ACID properties:

Example Transaction:

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 2;
COMMIT; -- Saves changes permanently
-- OR ROLLBACK; -- Undoes changes if error occurs

Applications of DBMS

Exam Tip: How to Score Full Marks in NEB

1. Understand Key Concepts:

   • Know the difference between DBMS and database.
   • Memorize SQL commands (SELECT, INSERT, JOIN, etc.).
   • Practice ER diagrams (entities, attributes, relationships).

2. Practical Questions:

   • Write SQL queries for given scenarios (e.g., "Find all students with grade A").
   • Design a database for a real-world case (e.g., library management).
   • Normalize tables from unstructured data.

3. Common Mistakes to Avoid:

   • ❌ Forgetting WHERE clause in SELECT queries.
   • ❌ Not using proper data types (e.g., INT vs. VARCHAR).
   • ❌ Skipping constraints (e.g., PRIMARY KEY, FOREIGN KEY).

4. Diagram-Based Questions:

   • Draw ER diagrams clearly with proper symbols.
   • Label all entities, attributes, and relationships.

5. Short Answer Tips:

   • For DBMS advantages, list 3-4 points (e.g., "reduces redundancy," "improves security").
   • For SQL, always show sample queries in your answer.