Database Management Systems

2 · SQL Written + Viva

SQL = Structured Query Language. Admission e tomake query likhte bola hote pare, ar output bolte bola hote pare. Niche sob clause example soho.

Sample tables (ei gula diye sob example)

Core clauses

SELECT + WHERE

-- CSE dept er sob employee
SELECT name, salary
FROM Employee
WHERE dept = 'CSE';

Output: Rahim/50000, Sadia/60000, Nabila/55000.

ORDER BY

SELECT name, salary FROM Employee
ORDER BY salary DESC;   -- DESC = boro theke choto

Aggregate functions

COUNT(), SUM(), AVG(), MAX(), MIN()

SELECT AVG(salary) FROM Employee;        -- sob er average
SELECT MAX(salary) FROM Employee WHERE dept='EEE';

GROUP BY + HAVING

-- Prottek dept er average salary, jegula 50000+
SELECT dept, AVG(salary) AS avg_sal
FROM Employee
GROUP BY dept
HAVING AVG(salary) > 50000;

CSE avg = (50000+60000+55000)/3 = 55000 ✓ | EEE avg = (45000+48000)/2 = 46500 ✗. Output: shudhu CSE.

JOINs

SELECT e.name, d.dept_name
FROM Employee e
INNER JOIN Department d ON e.dept = d.dept_code;

Subquery (nested query)

-- Average er beshi salary jara pay
SELECT name, salary FROM Employee
WHERE salary > (SELECT AVG(salary) FROM Employee);

AVG = (50000+45000+60000+55000+48000)/5 = 51600. Output: Sadia(60000), Nabila(55000).

SELECT dept, MAX(salary) AS max_sal
FROM Employee
GROUP BY dept
HAVING MAX(salary) > 50000;

3 · Relational Algebra

Relational algebra holo SQL er theoretical bhitti — symbol diye query lekha. MCQ te symbol ar tar mane jiggesh kore.

Example

σ (salary > 50000) (Employee)
   → salary 50000 er beshi jara, sob column

π (name, dept) (Employee)
   → shudhu name ar dept column

π (name) ( σ (dept='CSE') (Employee) )
   → CSE dept er employee der name

4 · ER Model (Entity-Relationship)

ER model diye database design kora hoy — bastob world ke entity ar relationship diye represent kora.

Component gula

Cardinality (mapping) — important

• One-to-One (1:1): ek entity ek-i onno entity'r sathe. Eg: ek person ↔ ek passport.
• One-to-Many (1:N): ek entity onek entity'r sathe. Eg: ek department ↔ onek employee.
• Many-to-One (N:1): ulta 1:N.
• Many-to-Many (M:N): dui pasher-i onek. Eg: student ↔ course (ek student onek course, ek course onek student).

5 · Normalization High weight ★

Normalization = table ke এমনভাবে design kora jate redundancy kome ar anomaly (insert/update/delete problem) na hoy. Ei topic admission e pakka ase.

Functional Dependency (FD) — bhitti

X → Y mane: X jana thakle Y nirdharon hoy. Eg: roll → name (roll diye name pawa jay).

Normal Forms — step by step

6 · Transactions & Concurrency

Transaction = ek ba ekadhik operation er logical unit ja "sob-i hobe noile kichu-i hobe na" niyom mene chole.

ACID Properties — pakka MCQ/viva

Transaction states

Active → Partially committed → Committed | (problem hole) → Failed → Aborted

Schedule & Serializability

• Serial schedule: ek transaction puro shesh, tarpor arekta. Always correct, kintu slow.
• Concurrent schedule: mishe mishe chole — fast, kintu thik na hole problem.
• Conflict serializable: conflict operation gula reorder kore serial banano gele — eta safe. Precedence graph e cycle na thakle conflict serializable.

Concurrency problems (anomalies)

• Dirty read: ekta T arekta T er uncommitted data pore.
• Lost update: dui T ekই data update kore, ekta update hariye jay.
• Unrepeatable read: same query duibar parle different result.

Locking — 2PL

Two-Phase Locking (2PL): dui phase — Growing (shudhu lock newa) ar Shrinking (shudhu lock chhara)। Ekbar lock chharle r notun lock newa jabe na. 2PL conflict-serializability nishchit kore.

7 · MCQ Practice GATE / CUET style

Eigula bhalo kore bujhe nao। Concept clear thakle MCQ e bhul hobe na।

Score nije track koro — kon topic e bhul hoy sheta abar oi tab e fire giye poro।