2NF (partial dependency)

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. What does 1NF require? from Lesson 4.4.1
2. What is a prime attribute? from Lesson 4.2.3
3. Find the keys of R(A, B, C, D, E) with F = {AB → C, C → D, D → B, D → E}. from Lesson 4.2.3

1 · The Hook

In ENROLL(roll, cid, grade, name), the key is (roll, cid). But name depends on roll alone, half of the key. So Asha's name is repeated for every course she takes. That is a partial dependency, and second normal form forbids it.

2 · Predict First

R(roll, cid, grade, title) with FDs roll cid → grade and cid → title. Key (roll, cid). Which attribute causes redundancy, and why?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

title: it depends only on cid, part of the key. Every enrolment in C1 repeats "DBMS". This partial dependency violates 2NF.

Full and partial dependency

For an FD X → A where X is a candidate key (or contains one):

• A is fully dependent on X if no proper subset of X determines A.
• A is partially dependent on X if some proper subset of a candidate key determines A.

Second normal form (2NF)

R is in 2NF if it is in 1NF and no non-prime attribute is partially dependent on any candidate key.

In other words: there is no FD Y → A where Y is a proper subset of a candidate key and A is non-prime.

Consequences

• If every candidate key is a single attribute, R is automatically in 2NF (a single attribute has no non-empty proper subset).
• If every attribute is prime, R is automatically in 2NF (and, as you will see, in 3NF).
• Partial dependencies onto prime attributes are allowed by 2NF.

Checking 2NF

1. Find all candidate keys and the non-prime attributes.
2. For each FD Y → A (from the minimal cover, or F with single right sides): if A is non-prime and Y is a proper subset of some candidate key → violation.

Example

R(A, B, C, D, E), F = {AB → C, C → D, D → B, D → E} (keys AB, AC, AD; prime A, B, C, D; non-prime E).

• D → E: D is a proper subset of key AD, and E is non-prime → not in 2NF.

Decomposing into 2NF

Move each partially dependent attribute out with the part of the key it depends on: R(roll, cid, grade, title) → ENROLL(roll, cid, grade), COURSE(cid, title). Lossless: the common attribute cid is a key of COURSE.

4 · Visual

key = (roll, cid)

roll cid ---------> grade        full dependency (needs both)     OK
     cid ---------> title        partial (half the key -> non-prime)   2NF VIOLATION
roll     ---------> name         partial                               2NF VIOLATION

5 · Worked Examples

Example 1 Easy

R(A, B, C) with key A and F = {A → B, B → C}. In 2NF?

Solution

Yes: the only key is a single attribute, so there are no partial dependencies. (B → C is a different problem: transitive, handled by 3NF.)

Example 2 Medium

R(A, B, C, D) with F = {AB → C, B → D}. Is it in 2NF? If not, decompose.

Solution

Key AB (A, B never on the right). B → D: B ⊂ AB and D non-prime → not 2NF. Decompose: R1(A, B, C), R2(B, D).

Example 3 GATE

R(A, B, C, D) with F = {AB → C, C → D, D → A}. Keys AB, BC, BD. Is R in 2NF?

Solution

Every attribute is prime (A, B, C, D all appear in keys). 2NF only restricts non-prime attributes: yes, in 2NF.

8 · Connect the Dots

Lesson 4.4.1 required atomic values; this lesson banned non-key facts depending on part of a key. Next: 3NF bans non-key facts depending on other non-key facts (transitive dependencies), with a precise rule GATE uses directly.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — 2NF

R(A, B, C) with key AB and F = {AB → C, A → C}. Is R in 2NF?

1. (A) Yes
2. (B) No, because A → C is a partial dependency of a non-prime attribute
3. (C) No, because AB → C is partial
4. (D) Yes, because C is prime

Q2 · MCQ · 2 mark(s) — 2NF

R(A, B, C, D) with F = {A → BCD}. Highest of {1NF, 2NF}?

1. (A) 2NF
2. (B) 1NF only
3. (C) Not even 1NF
4. (D) Cannot tell

Q3 · MSQ · 2 mark(s) — Partial dependencies

R(A, B, C, D, E) with F = {AB → C, B → D, A → E}. Which FDs are partial dependencies violating 2NF?

1. (A) AB → C
2. (B) B → D
3. (C) A → E
4. (D) none

Q4 · NAT · 2 mark(s) — Decomposition

R(roll, cid, grade, name, title) with roll cid → grade, roll → name, cid → title. Decomposing into 2NF by moving out each partial dependency gives how many relations?

Numerical answer: ________

Q5 · MCQ · 1 mark(s) — All prime

If every attribute of R is prime, R is:

1. (A) not in 1NF
2. (B) in 4NF
3. (C) not in 2NF
4. (D) in 2NF

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

full functional dependencypartial dependencysecond normal form (2NF)2NF decomposition