DBMS Complete Notes — B.Tech CS Sem 4

DBMS kya hai?

Database Management System ek software hai jo data ko systematically store, retrieve, aur manage karta hai. File systems se better kyon?

| Problem in File System | DBMS Solution | |---|---| | Data redundancy | Centralized storage, normalization | | Data inconsistency | Single source of truth | | No concurrent access | Transaction management | | No security | User roles, permissions | | No backup/recovery | Built-in backup mechanisms |

Types of DBMS:

• Relational (RDBMS): MySQL, PostgreSQL, Oracle, SQL Server — tabular
• NoSQL: MongoDB (document), Redis (key-value), Cassandra (column), Neo4j (graph)
• NewSQL: CockroachDB, Spanner — SQL + NoSQL scalability

---

1. ER Model (Entity-Relationship)

Components:

| Component | Notation | Meaning | |---|---|---| | Entity | Rectangle | Real-world object (Student, Course) | | Attribute | Ellipse | Property (name, age, rollno) | | Relationship | Diamond | Association (Enrolls, Teaches) | | Weak Entity | Double Rectangle | Depends on strong entity (OrderItem depends on Order) |

Attribute Types:

• Simple: Atomic, indivisible (age, phone)
• Composite: Can be divided (name → first + last)
• Derived: Calculated from others (age from DOB)
• Multivalued: Multiple values (phone numbers)

Cardinality:

• 1:1 — One employee, one parking spot
• 1:M — One department, many employees
• M:N — Many students, many courses (needs junction table)

---

2. Normalization

Goal: Eliminate data redundancy and anomalies.

Anomalies without normalization:

• Insert Anomaly: Can't insert without complete data
• Update Anomaly: Change one row, must change many
• Delete Anomaly: Deleting row loses unrelated info

1NF (First Normal Form)

• All attributes atomic (no arrays, no sets)
• Each row uniquely identifiable (primary key)

❌ Bad: Student(ID, Name, Phones)    → Phones has multiple values
✅ Good: Student(ID, Name) + StudentPhone(StudentID, Phone)

2NF (Second Normal Form)

• Must be in 1NF
• No partial dependency — every non-key attribute depends on the whole PK (not just part)

❌ Bad: OrderItem(OrderID, ProductID, ProductName, Quantity)
       ProductName depends only on ProductID (partial dependency!)
✅ Good: OrderItem(OrderID, ProductID, Quantity)
         Product(ProductID, ProductName)

3NF (Third Normal Form)

• Must be in 2NF
• No transitive dependency — non-key attributes shouldn't depend on other non-key attributes

❌ Bad: Employee(EmpID, DeptID, DeptName)
       DeptName depends on DeptID (transitive, not directly on EmpID!)
✅ Good: Employee(EmpID, DeptID)
         Department(DeptID, DeptName)

BCNF (Boyce-Codd Normal Form)

• Stronger 3NF
• For every FD X → Y, X must be a super key

---

3. SQL — Complete Reference

DDL (Data Definition Language)

-- Create table
CREATE TABLE Student (
    StudentID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    DeptID INT,
    Age INT CHECK (Age >= 17 AND Age <= 30),
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

-- Modify table
ALTER TABLE Student ADD COLUMN CGPA DECIMAL(3,2);
ALTER TABLE Student DROP COLUMN CGPA;
ALTER TABLE Student MODIFY Name VARCHAR(100);

-- Delete table
DROP TABLE Student;          -- Remove table + data
TRUNCATE TABLE Student;      -- Remove all data, keep structure

-- Create index
CREATE INDEX idx_dept ON Student(DeptID);
CREATE UNIQUE INDEX idx_email ON Student(Email);

DML (Data Manipulation Language)

-- Insert
INSERT INTO Student (Name, Email, DeptID) 
VALUES ('Rahul Sharma', 'rahul@wohotech.in', 1);

-- Insert multiple
INSERT INTO Student VALUES 
(NULL, 'Priya', 'priya@example.com', 2),
(NULL, 'Amit', 'amit@example.com', 1);

-- Update
UPDATE Student SET DeptID = 3 WHERE StudentID = 5;

-- Delete
DELETE FROM Student WHERE StudentID = 5;
DELETE FROM Student WHERE DeptID IS NULL;

SELECT — All Clauses

SELECT [DISTINCT] column1, column2
FROM table1
[JOIN table2 ON condition]
[WHERE condition]
[GROUP BY column]
[HAVING group_condition]
[ORDER BY column ASC|DESC]
[LIMIT n OFFSET m];

Aggregate Functions:

SELECT 
    DeptID,
    COUNT(*) AS TotalStudents,
    AVG(CGPA) AS AvgCGPA,
    MAX(CGPA) AS TopCGPA,
    MIN(CGPA) AS MinCGPA,
    SUM(CGPA) AS TotalCGPA
FROM Student
GROUP BY DeptID
HAVING AVG(CGPA) > 7.5
ORDER BY AvgCGPA DESC;

JOINs — Complete Guide

-- INNER JOIN: Only matching rows
SELECT s.Name, d.DeptName
FROM Student s
INNER JOIN Department d ON s.DeptID = d.DeptID;

-- LEFT JOIN: All left rows + matching right (NULL if no match)
SELECT s.Name, d.DeptName
FROM Student s
LEFT JOIN Department d ON s.DeptID = d.DeptID;

-- RIGHT JOIN: All right rows + matching left
SELECT s.Name, d.DeptName
FROM Student s
RIGHT JOIN Department d ON s.DeptID = d.DeptID;

-- FULL OUTER JOIN: All rows from both (NULL where no match)
SELECT s.Name, d.DeptName
FROM Student s
FULL OUTER JOIN Department d ON s.DeptID = d.DeptID;

-- SELF JOIN: Join table with itself
SELECT e.Name AS Employee, m.Name AS Manager
FROM Employee e
INNER JOIN Employee m ON e.ManagerID = m.EmpID;

-- CROSS JOIN: Cartesian product (every combination)
SELECT s.Name, c.CourseName
FROM Student s
CROSS JOIN Course c;

Subqueries

-- Subquery in WHERE
SELECT Name FROM Student
WHERE DeptID = (SELECT DeptID FROM Department WHERE DeptName = 'CS');

-- Correlated subquery (runs once per row)
SELECT Name FROM Student s
WHERE CGPA > (SELECT AVG(CGPA) FROM Student WHERE DeptID = s.DeptID);

-- Subquery in FROM (derived table)
SELECT dept_avg.DeptID, dept_avg.avg_cgpa
FROM (SELECT DeptID, AVG(CGPA) as avg_cgpa FROM Student GROUP BY DeptID) dept_avg
WHERE dept_avg.avg_cgpa > 7.5;

-- EXISTS / NOT EXISTS
SELECT Name FROM Student s
WHERE EXISTS (SELECT 1 FROM Enrollment e WHERE e.StudentID = s.StudentID);

String, Date, Math Functions

-- String
SELECT UPPER(Name), LOWER(Email), LENGTH(Name) FROM Student;
SELECT SUBSTRING(Name, 1, 3), CONCAT(FirstName, ' ', LastName) FROM Student;
SELECT TRIM('  hello  '), REPLACE(Name, 'a', 'A') FROM Student;

-- Date
SELECT NOW(), CURDATE(), CURTIME();
SELECT YEAR(DOB), MONTH(DOB), DAY(DOB) FROM Student;
SELECT DATEDIFF('2026-01-01', '2025-01-01);
SELECT DATE_ADD(NOW(), INTERVAL 30 DAY);

-- Math
SELECT ROUND(3.567, 2), CEIL(3.2), FLOOR(3.9), ABS(-5), MOD(10, 3);

---

4. Transactions & ACID

Transaction: A group of operations that must all succeed or all fail.

START TRANSACTION;
    UPDATE Account SET Balance = Balance - 1000 WHERE ID = 1;
    UPDATE Account SET Balance = Balance + 1000 WHERE ID = 2;
    -- If any error:
ROLLBACK;
    -- If all OK:
COMMIT;

ACID Properties:

• Atomicity: All operations succeed or all rolled back
• Consistency: DB moves from one valid state to another
• Isolation: Concurrent transactions don't interfere
• Durability: Committed data survives system failures

Transaction Isolation Levels

| Level | Dirty Read | Non-Repeatable Read | Phantom Read | |---|---|---|---| | READ UNCOMMITTED | ✅ Yes | ✅ Yes | ✅ Yes | | READ COMMITTED | ❌ No | ✅ Yes | ✅ Yes | | REPEATABLE READ | ❌ No | ❌ No | ✅ Yes | | SERIALIZABLE | ❌ No | ❌ No | ❌ No |

---

5. Concurrency Control

Problems in Concurrent Transactions

• Dirty Read: Read data written by uncommitted transaction
• Non-Repeatable Read: Same row gives different values in same transaction
• Phantom Read: New rows appear in repeated query
• Lost Update: Two transactions update same row, one update lost

Locking

Shared Lock (S): Multiple readers, no writer
Exclusive Lock (X): Only one writer

Lock Compatibility:
       S    X
S:  ✅ OK  ❌ Wait
X:  ❌ Wait ❌ Wait

Deadlock in Transactions: T1 holds lock on A, wants B T2 holds lock on B, wants A → Deadlock!

Detection + Recovery: Wait-for graph cycle detection; abort one transaction

2PL (Two-Phase Locking)

• Growing phase: Only acquire locks
• Shrinking phase: Only release locks
• Ensures serializability but may cause deadlocks

---

6. Indexing

Index: Data structure that speeds up data retrieval. Trade-off: faster reads, slower writes.

B+ Tree Index (Most Common)

- All data at leaf nodes
- Leaf nodes linked (good for range queries)
- Height = O(log n)
- Used in MySQL InnoDB, PostgreSQL

Types of Indexes

| Type | Description | Use Case | |---|---|---| | Primary | Based on PK, clustered | Always exists | | Unique | No duplicate values | Email, username | | Composite | Multiple columns | WHERE a=1 AND b=2 | | Full-Text | Text search | Blog search | | Hash | Exact match O(1) | =, IN queries |

When NOT to Index

• Small tables (full scan is faster)
• Columns with low cardinality (gender: M/F — only 2 values)
• Frequently updated columns (index update overhead)
• Columns rarely used in WHERE/JOIN

---

Interview Questions (DBMS)

Basic:

1. DBMS vs RDBMS vs NoSQL kab use karein?
2. Primary, Candidate, Super, Foreign key explain karo
3. Normalization kyon zaroori hai?
4. 2NF aur 3NF mein kya difference hai?

SQL: 5. INNER JOIN vs OUTER JOIN vs CROSS JOIN 6. GROUP BY vs HAVING vs WHERE 7. Subquery vs JOIN — kab kya use karein? 8. RANK() vs DENSE_RANK() vs ROW_NUMBER() 9. Window functions kya hain? 10. UNION vs UNION ALL mein kya fark?

Advanced: 11. ACID kya hai, koi ek property fail ho to kya hoga? 12. Dirty read kab hota hai, example do 13. Deadlock in DB kaise handle hota hai? 14. Index kab banana chahiye, kab nahi? 15. Clustered vs Non-clustered index? 16. Query optimization kaise karte hain? 17. Stored procedure vs function? 18. Trigger kya hai, kab use karte hain?