BEFORE YOU BEGIN

Start with the right foundations

Sets, relations, basic SQL and logical predicates.

3 key concepts1 worked example3 revision checks

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.

Concept 1

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.

Concept 2

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.

Concept 3

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. 1

    Start with A+: add B using A → B, then C using B → C. Thus A+ = {A,B,C}.

  2. 2

    B+ = {B,C} and C+ = {C}, so neither is a key. Every key must include A because no dependency produces A.

  3. 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.