Database Management System Notes — BCA Sem 3

DBMS — Introduction

Database: Related data ka organized collection. DBMS: Software system jo database manage karta hai (MySQL, PostgreSQL, Oracle, SQL Server).

Advantages over File System:

• Data redundancy control
• Data integrity & consistency
• Concurrent access management
• Security & access control
• Backup & recovery

Three-Schema Architecture:

External Schema (User Views)
        ↕ [Conceptual-External Mapping]
Conceptual Schema (Logical structure)
        ↕ [Physical-Conceptual Mapping]
Internal/Physical Schema (Storage)

---

1. ER Model (Entity-Relationship)

Basic Concepts:

| Concept | Meaning | Symbol | |---------|---------|--------| | Entity | Real-world object (Student, Course) | Rectangle | | Attribute | Property of entity (Name, Age) | Ellipse | | Relationship | Association between entities | Diamond | | Weak Entity | Depends on another entity | Double rectangle |

Attribute Types:

Simple: Name, Age (cannot be divided)
Composite: Address → Street + City + State
Multi-valued: Phone (can have multiple values) — double ellipse
Derived: Age (derived from DOB) — dashed ellipse

Cardinality Ratios:

One-to-One (1:1): Employee ← manages → Department
One-to-Many (1:N): Student ← enrolls → many Courses
Many-to-Many (M:N): Student ← takes → Course (through Enrollment)

Sample ER Diagram — College Database:

Student(StudentID, Name, DOB, Email)
    ↕ enrolls (EnrollDate, Grade)
Course(CourseID, Title, Credits)
    ↕ taught-by
Faculty(FacultyID, Name, Department)
Department(DeptID, DeptName)
    contains → Course

---

2. Relational Model

Relations (Tables):

STUDENT:
StudentID | Name    | Age | DeptID
----------|---------|-----|-------
101       | Alice   | 20  | CS
102       | Bob     | 21  | IT
103       | Charlie | 20  | CS

DEPARTMENT:
DeptID | DeptName        | HOD
-------|-----------------|-----
CS     | Computer Sci    | Dr. Kumar
IT     | Info Technology | Dr. Sharma

Key Types:

• Super Key: uniquely identifies rows (can have extra attributes)
• Candidate Key: minimal super key
• Primary Key: chosen candidate key — NOT NULL, UNIQUE
• Foreign Key: references primary key of another table
• Composite Key: primary key made of multiple attributes

---

3. SQL — Structured Query Language

DDL — Data Definition Language

-- Create database
CREATE DATABASE college;
USE college;

-- Create table with constraints
CREATE TABLE department (
    dept_id   VARCHAR(5)   PRIMARY KEY,
    dept_name VARCHAR(50)  NOT NULL UNIQUE,
    hod_name  VARCHAR(100)
);

CREATE TABLE student (
    student_id   INT          PRIMARY KEY AUTO_INCREMENT,
    name         VARCHAR(100) NOT NULL,
    email        VARCHAR(100) UNIQUE,
    dob          DATE,
    age          INT          CHECK (age >= 17 AND age <= 30),
    dept_id      VARCHAR(5),
    FOREIGN KEY (dept_id) REFERENCES department(dept_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- Alter table
ALTER TABLE student ADD COLUMN phone VARCHAR(15);
ALTER TABLE student MODIFY COLUMN name VARCHAR(150);
ALTER TABLE student DROP COLUMN phone;

-- Drop table
DROP TABLE IF EXISTS student;

-- Truncate (delete all rows, keep structure)
TRUNCATE TABLE student;

DML — Data Manipulation Language

-- INSERT
INSERT INTO department VALUES ('CS', 'Computer Science', 'Dr. Kumar');
INSERT INTO department (dept_id, dept_name) VALUES ('IT', 'Information Technology');

-- Bulk insert
INSERT INTO student (name, email, dob, dept_id) VALUES
    ('Alice Sharma', 'alice@college.edu', '2004-05-15', 'CS'),
    ('Bob Patel',    'bob@college.edu',   '2003-08-22', 'IT'),
    ('Charlie Roy',  'charlie@college.edu','2004-01-10', 'CS');

-- UPDATE
UPDATE student SET dept_id = 'IT' WHERE student_id = 101;
UPDATE student SET age = age + 1 WHERE dept_id = 'CS';

-- DELETE
DELETE FROM student WHERE student_id = 103;
DELETE FROM student WHERE dept_id = 'IT' AND age > 25;

DQL — Data Query Language (SELECT)

-- Basic SELECT
SELECT * FROM student;
SELECT name, email FROM student;

-- WHERE clause
SELECT * FROM student WHERE dept_id = 'CS';
SELECT * FROM student WHERE age BETWEEN 18 AND 22;
SELECT * FROM student WHERE name LIKE 'A%';  -- starts with A
SELECT * FROM student WHERE dept_id IN ('CS', 'IT');
SELECT * FROM student WHERE email IS NOT NULL;

-- ORDER BY, LIMIT
SELECT name, age FROM student ORDER BY age DESC, name ASC;
SELECT * FROM student LIMIT 5 OFFSET 10;  -- page 3 (page size 5)

-- Aggregate Functions
SELECT COUNT(*)               FROM student;           -- total count
SELECT COUNT(DISTINCT dept_id) FROM student;          -- unique depts
SELECT AVG(age), MAX(age), MIN(age) FROM student;
SELECT dept_id, COUNT(*) as student_count
FROM student GROUP BY dept_id;

-- HAVING (filter after GROUP BY)
SELECT dept_id, COUNT(*) as cnt
FROM student
GROUP BY dept_id
HAVING cnt > 10;  -- depts with more than 10 students

-- Subquery
SELECT name FROM student
WHERE dept_id = (SELECT dept_id FROM department WHERE dept_name = 'CS');

-- EXISTS
SELECT name FROM student s
WHERE EXISTS (
    SELECT 1 FROM enrollment e WHERE e.student_id = s.student_id
);

Joins

-- INNER JOIN — only matching rows
SELECT s.name, d.dept_name
FROM student s
INNER JOIN department d ON s.dept_id = d.dept_id;

-- LEFT JOIN — all students, even without dept
SELECT s.name, d.dept_name
FROM student s
LEFT JOIN department d ON s.dept_id = d.dept_id;

-- RIGHT JOIN — all departments, even without students
SELECT s.name, d.dept_name
FROM student s
RIGHT JOIN department d ON s.dept_id = d.dept_id;

-- FULL OUTER JOIN (MySQL uses UNION workaround)
SELECT s.name, d.dept_name FROM student s LEFT JOIN department d ON s.dept_id = d.dept_id
UNION
SELECT s.name, d.dept_name FROM student s RIGHT JOIN department d ON s.dept_id = d.dept_id;

-- SELF JOIN — find students in same dept
SELECT a.name, b.name, a.dept_id
FROM student a
INNER JOIN student b ON a.dept_id = b.dept_id AND a.student_id < b.student_id;

-- Multiple joins
SELECT s.name, c.course_name, e.grade
FROM student s
INNER JOIN enrollment e ON s.student_id = e.student_id
INNER JOIN course c ON e.course_id = c.course_id
WHERE e.grade = 'A';

---

4. Normalization

Why normalize?

• Eliminate data redundancy
• Prevent anomalies: insert, update, delete

Unnormalized Table — Student_Course:
StudentID | StudentName | CourseID | CourseName | InstructorID | InstructorName
101       | Alice       | CS101    | DBMS       | I01          | Prof. Kumar
101       | Alice       | CS102    | OS         | I02          | Prof. Sharma
102       | Bob         | CS101    | DBMS       | I01          | Prof. Kumar

Problems:
- Update anomaly: Prof. Kumar's name change → update all rows
- Insert anomaly: Can't add instructor without student
- Delete anomaly: Delete last student → lose course info

1NF (First Normal Form)

• All attributes must be atomic (no multi-valued, no repeating groups)

Violation: Phone = "9876543210, 9123456789"
Fix: Separate rows or separate PhoneNumbers table

2NF (Second Normal Form)

• Must be in 1NF
• No partial dependencies (non-key attribute must depend on WHOLE primary key)

Table: (StudentID, CourseID) → Grade, CourseName
Problem: CourseName depends only on CourseID (partial dependency)

Fix:
Enrollment(StudentID, CourseID) → Grade
Course(CourseID) → CourseName

3NF (Third Normal Form)

• Must be in 2NF
• No transitive dependencies (non-key → non-key through another non-key)

Table: Student(StudentID, DeptID, DeptName)
Problem: StudentID → DeptID → DeptName (transitive)

Fix:
Student(StudentID, DeptID)
Department(DeptID, DeptName)

BCNF (Boyce-Codd Normal Form)

• Stricter than 3NF
• For every functional dependency X → Y, X must be a superkey

---

5. Transactions & ACID

Transaction: A unit of work — all or nothing.

START TRANSACTION;
    UPDATE accounts SET balance = balance - 5000 WHERE acc_id = 'A001';
    UPDATE accounts SET balance = balance + 5000 WHERE acc_id = 'A002';
COMMIT;   -- Save permanently

-- If error occurs:
ROLLBACK;  -- Undo all changes

ACID Properties: | Property | Meaning | Example | |----------|---------|---------| | Atomicity | All or nothing | Money transfer: both debit+credit happen or neither | | Consistency | DB rules preserved | Balance can't go negative | | Isolation | Concurrent transactions don't interfere | Two transfers don't see each other's partial state | | Durability | Committed data persists | Power failure se committed transaction nahi jayega |

Concurrency Problems:

• Dirty Read — uncommitted data padhna
• Non-repeatable Read — same query twice, different result
• Phantom Read — new rows appear mid-transaction

Isolation Levels:

READ UNCOMMITTED → Dirty reads allowed
READ COMMITTED   → No dirty reads (default many DBs)
REPEATABLE READ  → No dirty/non-repeatable reads (MySQL default)
SERIALIZABLE     → Maximum isolation (slowest)

---

Exam Important Questions

1. ER Model diagram banao — College ya Library system
2. Primary Key, Foreign Key, Composite Key explain karo
3. SQL queries likhein — student table pe JOIN, GROUP BY, HAVING
4. 1NF, 2NF, 3NF explain karo examples ke saath
5. ACID properties explain karo with transaction example
6. INNER JOIN aur LEFT JOIN mein kya fark hai?
7. Normalization kyon zaroori hai? Anomalies explain karo
8. Subquery aur JOIN mein kab kya use karein?

---

Viva Questions

Q: Candidate Key aur Primary Key mein kya fark hai? A: Table mein multiple candidate keys ho sakti hain (e.g., StudentID aur Email — dono unique). Database designer unme se ek ko Primary Key choose karta hai.

Q: DELETE aur TRUNCATE mein kya fark hai? A: DELETE: WHERE clause se specific rows delete, transactions rollback possible, triggers fire. TRUNCATE: sari rows delete, faster, auto-increment reset, rollback generally not possible.

Q: Index kya hai aur kab use karein? A: Index database search ko fast banata hai — B+ tree structure. SELECT mein use hone wale columns pe (WHERE, JOIN). Downside: INSERT/UPDATE/DELETE slow ho jaata hai.