DBMS & SQL›Level 6
PART 2 • CORRECT DATABASE DESIGN

Reason About What Determines What

Use functional dependencies to express business rules, infer hidden implications, discover candidate keys and prepare relations for normalization.

Level 06 of 18Intermediate100–140 minutesLevel 4 recommended
BY THE END, YOU CAN
  • Interpret X → Y precisely.
  • Detect valid and violated dependencies.
  • Apply Armstrong’s axioms.
  • Calculate attribute closure step by step.
  • Discover candidate keys.
  • Produce a minimal cover.
01 • READ A FUNCTIONAL DEPENDENCY

X → Y Is a Constraint on Every Legal Relation State

If two tuples agree on X, they must also agree on Y.

DETERMINANTstudent_id
→
DEPENDENTstudent_name, branch

Read it correctly

For any two STUDENT tuples, equal student_id values require equal student_name and branch values. The ID determines those facts.

Do not reverse it automatically: two students may share the same name, so student_name → student_id need not hold.

LEGAL INSTANCE
student_idnamebranch
101AnuAIML
102AnuCSE

Repeated name is allowed; IDs differ.

VIOLATES student_id → branch
student_idnamebranch
101AnuAIML
101AnuCSE

Equal determinant values produce different branch values.

02 • CLASSIFY DEPENDENCIES

Dependency Type Reveals Design Risk

TRIVIAL

Y ⊆ X

{A,B} → A

Always holds by reflexivity because the right side is already contained on the left.

NON-TRIVIAL

Y ⊄ X

student_id → name

Adds information not already present in the determinant.

COMPLETELY NON-TRIVIAL

X ∩ Y = ∅

course_id → title

The two sides share no attribute.

FULL FUNCTIONAL

Whole determinant is required

{student_id,course_id} → grade

Neither student_id nor course_id alone determines the grade.

PARTIAL

Part of a composite determinant is enough

{student_id,course_id} → student_name

student_id alone determines student_name. This becomes important for 2NF.

TRANSITIVE

X → Y and Y → Z imply X → Z

student_id → dept_id → dept_name

A non-key fact determines another non-key fact, relevant to 3NF.

03 • INFER SOUNDLY

Armstrong’s Axioms Generate All Implied FDs

The three axioms are sound and complete for functional-dependency inference.

A1

Reflexivity

If Y ⊆ X, then X → Y

{A,B} → A

A2

Augmentation

If X → Y, then XZ → YZ

From A → B, infer AC → BC.

A3

Transitivity

If X → Y and Y → Z, then X → Z

From A → B and B → C, infer A → C.

Union / Additivity

X → Y and X → Z ⇒ X → YZ

Decomposition

X → YZ ⇒ X → Y and X → Z

Pseudotransitivity

X → Y and WY → Z ⇒ WX → Z

Composition

X → Y and Z → W ⇒ XZ → YW
GIVENA → BB → CDD → E⇒

A → B given

A → CD transitivity through B

A → D decomposition

A → E transitivity through D

04 • CALCULATE ATTRIBUTE CLOSURE

X⁺ Contains Everything Functionally Determined by X

ALGORITHM
  1. Start with X⁺ = X.
  2. Find an FD Y → Z whose left side is contained in X⁺.
  3. Add every attribute of Z to X⁺.
  4. Repeat until no new attribute can be added.
USES
  • Test whether X is a super key.
  • Test whether an FD is implied.
  • Discover candidate keys.
  • Check extraneous attributes.
  • Support normalization proofs.
WORKED EXAMPLE

R(A,B,C,D,E), F = {A → B, B → C, AC → D, D → E}

Start: A⁺ = {A}

Use A → B: {A,B}

Use B → C: {A,B,C}

Use AC → D: {A,B,C,D}

Use D → E: {A,B,C,D,E}

A⁺ contains every attribute, so A is a super key. Because A is a single attribute, it is also a candidate key.
05 • CALCULATE & TRACE

Attribute Closure and Candidate-Key Calculator

Enter a relation and one dependency per line. The calculator validates the input and shows each inference.

Single-letter schemas may be written as AC → D. For words, use commas: student_id,course_id → grade. Candidate-key search is limited to 10 attributes.

READY

Enter a set and calculate its closure

The result will explain every dependency that adds new attributes.

06 • DISCOVER KEYS SYSTEMATICALLY

Candidate Keys Are Minimal Attribute Sets Whose Closure Is the Whole Schema

STEP 1

Find attributes never appearing on any RHS

They cannot be derived, so every candidate key must include them.

STEP 2

Calculate the mandatory set’s closure

If it reaches the complete schema, remove no attribute and stop.

STEP 3

Add optional attributes systematically

Try smallest combinations first and recalculate closure.

STEP 4

Test minimality

Remove each attribute. If the reduced set still determines all attributes, the original is not candidate.

EXAMPLE

R(A,B,C,D), F = {A → B, C → D}

A and C never appear on any right side, so both are mandatory. (AC)⁺ = {A,B,C,D}. Neither A⁺ nor C⁺ reaches all attributes; therefore AC is a candidate key.

07 • REMOVE REDUNDANCY FROM THE FD SET

A Minimal Cover Preserves the Same Implications with No Unnecessary Parts

1

Split right sides

Replace X → YZ with X → Y and X → Z.

2

Remove extraneous left attributes

Test whether an attribute can be removed from a determinant without changing implication.

3

Remove redundant dependencies

Temporarily remove one FD and test whether the remaining set still implies it.

TRACE
F = {A → BC, B → C, A → B}→ Split RHS{A → B, A → C, B → C}→ Test A → CA → B → C→ Remove redundant A → CFmin = {A → B, B → C}

Why equivalent: the remaining dependencies still imply A → C by transitivity.

Extraneous is not the same as redundant

An extraneous attribute is unnecessary inside one FD side. A redundant FD is an entire dependency implied by the others.

08 • CHECK YOUR UNDERSTANDING

Seven Formative Concept Checks

1. X → Y means:

2. Which FD is trivial?

3. From A → B, augmentation permits:

4. X is a super key when:

5. What additional property makes a super key a candidate key?

6. The first minimal-cover step normally makes:

7. A dependency observed in current rows is valid only if it:

Answered correctly: 0 of 7
09 • EXPLAIN & PREPARE

University and Interview Questions

2-MARK QUESTIONS
  1. Define a functional dependency.
  2. What is a trivial FD?
  3. State Armstrong’s axioms.
  4. Define attribute closure.
  5. What is a minimal cover?
5-MARK / PROBLEMS
  1. Find closures and candidate keys for a given FD set.
  2. Derive union and decomposition rules.
  3. Find a minimal cover step by step.
  4. Distinguish full, partial and transitive dependencies.
INTERVIEW QUESTIONS
  1. Can data alone prove an FD?
  2. Why must candidate keys be minimal?
  3. How do you test an implied FD?
  4. Extraneous attribute versus redundant FD?
  5. How do dependencies lead to normalization?
Show the closure and candidate-key exam format
  1. Write the schema and FD set clearly.
  2. Start closure with the chosen attributes.
  3. State each applied FD and newly added attributes.
  4. Repeat until no change occurs.
  5. Compare the closure with the full schema.
  6. If it is a super key, remove each attribute and retest minimality.
  7. State candidate keys separately from non-minimal super keys.
LEVEL 6 SUMMARY

You Can Now Infer Keys and Design Rules

  • An FD constrains all legal relation instances.
  • Armstrong’s axioms derive exactly the implied functional dependencies.
  • Attribute closure tests implication and super-key status.
  • Candidate keys are minimal super keys.
  • Partial and transitive dependencies signal normalization concerns.
  • A minimal cover removes unnecessary RHS groups, attributes and dependencies.
COURSE CHECKPOINT

Mark this level when you can calculate closure, find candidate keys and minimize an FD set with written reasoning.

Saved in this browser only.