GATE CSE Databases (DBMS) notes
Use small tables and explicit dependency sets to reason about SQL, normalization and transactions.
BEFORE YOU BEGIN
Start with the right foundations
Sets, relations, basic SQL and logical predicates.
Original introductory notes with source references. The example is a teaching exercise, rather than an official worked solution to a GATE question. Use the full syllabus to track coverage.
01 · UNDERSTAND
Key concepts
Use these ideas to reason through a question before choosing a formula or an answer.
Keys and functional dependencies
Compute the closure X+ by repeatedly applying dependencies whose left sides are already in the closure. X is a superkey if its closure contains every relation attribute; it is a candidate key if no proper subset is a superkey.
Normalization
BCNF requires the determinant of every nontrivial functional dependency to be a superkey. Third normal form also allows a prime attribute on the right side. A decomposition can be lossless without preserving every dependency.
SQL and transactions
SQL uses bag semantics unless DISTINCT is specified. NULL comparisons use three-valued logic; use IS NULL rather than = NULL. Conflict serializability can be checked by an acyclic precedence graph, with edges for conflicting operations on the same item.
02 · WORK THROUGH IT
A worked teaching example
Attempt the problem first, then trace each step and compare your reasoning.
The problem
For R(A,B,C) with dependencies A → B and B → C, find candidate keys and determine whether R is in BCNF.
Step-by-step reasoning
- 1
Start with A+: add B using A → B, then C using B → C. Thus A+ = {A,B,C}.
- 2
B+ = {B,C} and C+ = {C}, so neither is a key. Every key must include A because no dependency produces A.
- 3
B → C is nontrivial and B is not a superkey.
Check your result
Compare your calculation and assumptions.
A is the only candidate key. R violates BCNF; decomposition into R1(A,B) and R2(B,C) is lossless and preserves these dependencies.
03 · SPOT THE TRAPS
Common mistakes
- Confusing a minimal key with the key having the fewest letters in an example.
- Assuming SELECT removes duplicate rows.
- Creating precedence edges for two reads, which do not conflict.
04 · CHECK YOUR UNDERSTANDING
Revision checklist
- Write the full dependency set.
- Compute closures to a fixed point.
- Distinguish losslessness from dependency preservation.
Explain each point without the notes, then check it with an unfamiliar question. Print or save these notes as a PDF for your revision session.
05 · APPLY IT
Put these ideas into practice
Attempt related previous year questions and record the reasoning behind any mistakes.
Databases
73 archived questions
Plan your next revision session
Explore topic history or move to another note.
READ FURTHER
Study references
Use the original references for a fuller explanation and more worked problems.