DBMS Basics: Concepts, Formulas, and Clear Examples
DBMS aptitude hits relational fundamentals: keys, normal forms, SQL clauses, joins, transactions (ACID), and basic indexing—concept level for placement, not DBA tuning.
Translate word problems to tables and functional dependencies for normalization questions. SQL MCQs ask output row count or clause order (SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY).
Keys and constraints
- Primary key: unique + not null; candidate keys minimal unique sets.
- Foreign key enforces referential integrity.
Normal forms (sketch)
- 1NF: atomic cells; no repeating groups.
- 2NF: 1NF + no partial dependency on composite key.
- 3NF: no transitive dependency of non-key on non-key.
Example — Transitive dependency
EmpID → Dept, Dept → DeptLoc implies EmpID → DeptLoc transitive—may violate 3NF; decompose tables.
SQL clauses
- WHERE filters rows before grouping; HAVING filters groups.
- INNER JOIN keeps matches; LEFT JOIN keeps all left rows.
Transactions ACID
Atomicity (all or nothing), Consistency, Isolation, Durability—match scenario to property.
Index idea
- B-tree index speeds equality/range on indexed column; trade storage and write cost.
Aggregate queries
- COUNT, SUM, AVG with GROUP BY; non-aggregated columns must appear in GROUP BY (standard SQL).
Step-by-step method for exam questions
- For schema questions, list FDs from business rules.
- Apply NF definition stepwise.
- For SQL output, draw tiny sample tables (2–3 rows).
- Execute join/filter mentally row by row.
- For ACID, name the property being violated or guaranteed.
- Eliminate options contradicting relational rules.
Formula cheat sheet
| Situation | Formula | When to use |
|---|---|---|
| Unique row id | Primary key | Schema design |
| Partial dependency | 2NF issue | Composite key |
| Transitive non-key | 3NF issue | Normalization |
| Filter groups | HAVING | Post-aggregate |
| All left rows kept | LEFT JOIN | Join type |
Frequently asked questions
Write full SQL in exam?
Often pick correct query among four; know clause order.
BCNF needed?
Occasionally; most items stop at 3NF.
NULL in comparisons?
NULL = NULL is unknown; use IS NULL.
Practice on this platform
Use 4 practice sets (10 MCQs, detailed solution after each answer) and 2 timed mocks (20 minutes) on this topic to convert formulas into speed and accuracy.