DBMS Cheat Sheet

dbms Last verified: 28 Jul 2026

ER model — core building blocks

Term	Definition
Entity	A real-world object with independent existence (e.g. Student)
Attribute	A property of an entity (e.g. name, roll number)
Relationship	An association between two or more entities
Cardinality	How many instances of one entity relate to another (1:1, 1:N, M:N)

Attribute types

Type	Example
Simple	Age — cannot be divided further
Composite	Name → First Name + Last Name
Derived	Age, derived from Date of Birth
Multi-valued	Phone numbers — a person can have more than one

PYQ

An attribute that can be calculated from other attributes (like Age from Date of Birth) is called:

A) Composite
B) Multi-valued
C) Derived
D) Simple

Why: A derived attribute is computed from another stored attribute rather than stored directly itself — Age from Date of Birth is the textbook example.

Keys — quick reference

Key type	Definition
Primary Key	Uniquely identifies a row, cannot be NULL
Candidate Key	Any column set that could validly be a primary key
Foreign Key	References a primary key in another table
Composite Key	A primary key made of 2 or more columns together
Super Key	Any set of columns that uniquely identifies a row (candidate keys are the minimal super keys)
Alternate Key	A candidate key that was NOT chosen as the primary key

PYQ

What is the relationship between a Super Key and a Candidate Key?

A) They are always the same
B) Every candidate key is a super key, but a super key may have extra unnecessary attributes
C) A candidate key can have more attributes than a super key
D) There is no relationship

Why: A candidate key is a MINIMAL super key — one with no redundant attributes. Every candidate key qualifies as a super key, but not every super key is minimal enough to be a candidate key.

Normalization — the levels that get asked most

Level	Requirement
1NF	Atomic values only, no repeating groups
2NF	1NF + no partial dependency of a non-key attribute on part of a composite key
3NF	2NF + no transitive dependency (non-key attribute depending on another non-key attribute)
BCNF	Every determinant (left side of a functional dependency) must be a candidate key

Partial dependency example (violates 2NF):
  Table: (StudentID, CourseID, StudentName)
  StudentName depends only on StudentID, not on the full (StudentID, CourseID) key.

Transitive dependency example (violates 3NF):
  Table: (StudentID, DeptID, DeptName)
  DeptName depends on DeptID, which depends on StudentID — an indirect chain.

PYQ

A table is in 2NF but has a non-key attribute that depends on another non-key attribute. What normal form does it violate?

A) 1NF
B) 2NF
C) 3NF
D) It's already fully normalized

Why: A non-key attribute depending on another non-key attribute is exactly the definition of a transitive dependency — which 3NF specifically eliminates.

SQL command categories

Category	Full form	Commands	Purpose
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE	Defines/modifies structure
DML	Data Manipulation Language	INSERT, UPDATE, DELETE	Modifies row data
DQL	Data Query Language	SELECT	Retrieves data
DCL	Data Control Language	GRANT, REVOKE	Manages permissions
TCL	Transaction Control Language	COMMIT, ROLLBACK, SAVEPOINT	Manages transactions

DCL vs DML is the classic mix-up: GRANT/REVOKE control who can access data, not the data itself — that’s DCL, not DML.

PYQ

GRANT and REVOKE belong to which SQL command category?

A) DDL
B) DML
C) DCL
D) TCL

Why: GRANT and REVOKE manage user permissions and access rights — that's Data Control Language (DCL), not data manipulation (DML).

ACID properties

Property	Meaning
Atomicity	A transaction is all-or-nothing — no partial execution survives
Consistency	The database moves between valid states only, never a broken one
Isolation	Concurrent transactions don’t interfere with each other’s intermediate state
Durability	Once committed, changes survive even a system crash

Transactions & concurrency control

Transaction states

Active → Partially Committed → Committed
   ↓            ↓
 Failed  →   Aborted

Concurrency control techniques

Technique	Approach
Lock-based (2PL)	Growing phase acquires locks, shrinking phase releases them — never both at once
Timestamp ordering	Transactions ordered by timestamp; conflicting operations are rejected/rolled back
Optimistic concurrency	Assume no conflict, validate at commit time, rollback only if a conflict is found

Two-Phase Locking (2PL): once a transaction releases any lock, it cannot acquire any new locks — this guarantees serializability and is the single most commonly tested concurrency concept.

PYQ

In Two-Phase Locking (2PL), what happens once a transaction releases its first lock?

A) It can still acquire new locks freely
B) It cannot acquire any new locks — it has entered the shrinking phase
C) The transaction is automatically rolled back
D) All other transactions are blocked

Why: 2PL has two phases — growing (acquire only) and shrinking (release only). The moment a transaction releases even one lock, it has entered the shrinking phase and cannot acquire further locks.

Indexing

Index type	Structure	Best for
B-Tree	Balanced tree, sorted	Range queries, equality searches
B+ Tree	B-Tree variant, all data at leaf level, leaves linked	Most common in real DBMS — efficient range scans
Hash Index	Hash function maps key → bucket	Fast equality lookups, poor for range queries

Clustered Index  → determines the PHYSICAL order of table rows (only 1 per table)
Non-clustered Index → a separate structure pointing to row locations (many allowed per table)

PYQ

How many clustered indexes can a single table have?

A) As many as needed
B) Only 1
C) Exactly 2
D) 0 — clustered indexes don't exist in DBMS

Why: A clustered index determines the physical storage order of table rows — since a table can only be physically sorted one way, only one clustered index is possible per table. Non-clustered indexes have no such limit.

File organization

Method	How records are stored
Heap (unordered)	No particular order — fast inserts, slow search
Sequential	Sorted by a key field — fast range access, slow inserts
Hash	Records placed by a hash function on the key — fast equality lookup
Clustered	Related records from different tables stored physically together

Joins — the ones to have cold

Join type	Returns
INNER JOIN	Only matching rows from both tables
LEFT JOIN	All rows from the left table, matched rows from the right (NULL where no match)
RIGHT JOIN	All rows from the right table, matched rows from the left (NULL where no match)
FULL OUTER JOIN	All rows from both tables, matched where possible
SELF JOIN	A table joined with itself, using aliases

SELECT students.name, courses.title
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.id
WHERE courses.semester = 'Spring 2027';

PYQ

A LEFT JOIN between Table A and Table B returns:

A) Only rows that match in both tables
B) All rows from A, with matched rows from B (NULL where there's no match)
C) All rows from B, with matched rows from A
D) All rows from both tables, matched or not

Why: LEFT JOIN keeps every row from the left table regardless of a match, filling in NULLs for columns from the right table when no match exists. FULL OUTER JOIN is what returns unmatched rows from both sides.

CDAC C-CAT — top DBMS exam traps

Trap	Rule
DCL vs DML	GRANT/REVOKE = DCL (permissions). INSERT/UPDATE/DELETE = DML (data). Frequently confused.
Super Key vs Candidate Key	Every candidate key is a super key, but not every super key is minimal enough to be a candidate key.
2NF vs 3NF	2NF removes partial dependency (on part of a composite key). 3NF removes transitive dependency (non-key → non-key).
Atomicity vs Durability	Atomicity = all-or-nothing DURING execution. Durability = stays saved AFTER commit, survives crashes.
Clustered index limit	Only 1 clustered index per table (determines physical row order); unlimited non-clustered indexes.
2PL shrinking phase	Once a transaction releases any lock, it cannot acquire new ones — this is what guarantees serializability.
LEFT vs RIGHT vs FULL OUTER	LEFT keeps all of the left table, RIGHT keeps all of the right, FULL OUTER keeps both regardless of match.
B+ Tree vs B-Tree	B+ Tree stores all actual data at leaf level with linked leaves — this is what most real databases actually use for indexing.
Derived attribute	Computed from another attribute (e.g. Age from DOB) — not stored directly, a common ER-model trap.
NULL in Primary Key	A primary key can NEVER be NULL — this is one of its two defining constraints, along with uniqueness.

PYQs are indicative of exam style, not guaranteed exact repeats.