More General Definitions of 2NF and 3 NF

 

Second Normal Form (2NF) – General Definition

Definition

A relation schema R is in Second Normal Form (2NF) if:

Every non-prime attribute is fully functionally dependent on every candidate key of R.

Key Concepts

1. Prime Attribute

An attribute that is part of a candidate key.

2. Non-Prime Attribute

An attribute that is not part of any candidate key.

3. Partial Dependency

A partial dependency occurs when a non-key attribute depends only on part of a composite key.

Example:

(A, B) → C
but
A → C

Here C depends only on A, not the whole key (A,B) → this is partial dependency.

2NF eliminates this problem.

---

When Do We Check for 2NF?

The 2NF test is needed only if the primary key has multiple attributes.

• If the primary key has one attribute, the relation automatically satisfies 2NF.

---

Example from the LOTS Relation

which describes parcels of land for sale in various counties of a state. Suppose that there are two  candidate keys: Property_id# and {County_name,Lot#}; that is, lot numbers are unique only within each county, but Property_id# numbers are unique across counties for the entire state.

LOTS Relation

LOTS(Property_id#, County_name, Lot#, Area, Price, Tax_rate)

Candidate Keys

1. Property_id#

2. {County_name, Lot#}

Functional Dependencies

FD1: Property_id# → County_name, Lot#, Area
FD2: {County_name, Lot#} → Property_id#, Area
FD3: County_name → Tax_rate
FD4: Area → Price

---

Why LOTS Violates 2NF

Consider candidate key:

{County_name, Lot#}

But we have:

County_name → Tax_rate

Here:

• Tax_rate depends only on County_name

• But the key is {County_name, Lot#}

So Tax_rate depends on part of the key, not the whole key.

This is a partial dependency, which violates 2NF.

---

Converting LOTS into 2NF

To remove the partial dependency, we decompose the relation.

Table 1: LOTS1

LOTS1(Property_id#, County_name, Lot#, Area, Price)

Dependencies carried here:

Property_id# → County_name, Lot#, Area
Area → Price

---

Table 2: LOTS2

LOTS2(County_name, Tax_rate)

Dependency:

County_name → Tax_rate

Now:

• Tax_rate depends fully on County_name

• No partial dependency exists.

Therefore both relations are in 2NF.

---

Important Note from the Example

The dependency:

Area → Price

does not violate 2NF because it is not a partial dependency on a candidate key.

However, it will later cause a 3NF violation (transitive dependency).

---

Summary

A relation is in Second Normal Form (2NF) if it is in 1NF and every non-prime attribute is fully functionally dependent on the whole candidate key, meaning no partial dependency exists.

 

General Definition of Third Normal Form (3NF)

Formal Definition

A relation schema R is in Third Normal Form (3NF) if for every non-trivial functional dependency:

$$X\to A$$

one of the following conditions must be true:

1. X is a superkey, or

2. A is a prime attribute (part of some candidate key).

---

Important Terms

1. Non-trivial Functional Dependency

A dependency X → A is non-trivial if A is not part of X.

Example:

A → B

Here B is not in A, so it is non-trivial.

---

2. Superkey

A superkey is a set of attributes that can uniquely identify a tuple in a relation.

Example:

StudentID → StudentName

If StudentID uniquely identifies rows, it is a superkey.

---

3. Prime Attribute

A prime attribute is any attribute that is part of a candidate key.

Example:

Candidate key:

(StudentID, CourseID)

Prime attributes:

StudentID, CourseID

---

Simple Meaning of 3NF

A table is in 3NF if:

• Non-key attributes depend only on candidate keys

• There is no transitive dependency

In simple words:

Non-key attributes must depend only on the key and not on other non-key attributes.

---

Example from the LOTS Relation

Relation after 2NF

LOTS1(Property_id#, County_name, Lot#, Area, Price)

Functional Dependency

FD4: Area → Price

---

Why LOTS1 Violates 3NF

Check the rule:

X → A

Here:

Area → Price

Check the conditions:

Condition	Result
Is Area a superkey?	❌ No
Is Price a prime attribute?	❌ No

Since both conditions fail, the relation violates 3NF.

This also creates a transitive dependency:

Property_id# → Area
Area → Price

So:

Property_id# → Price (transitive)

---

Converting LOTS1 into 3NF

To remove the violation, split the relation.

LOTS1A

LOTS1A(Property_id#, County_name, Lot#, Area)

Dependency:

Property_id# → County_name, Lot#, Area

---

LOTS1B

LOTS1B(Area, Price)

Dependency:

Area → Price

Now both tables satisfy 3NF.

---

Important Observations

1. 3NF Automatically Ensures 2NF

If a relation satisfies 3NF, it will automatically satisfy 2NF.

So testing for 3NF alone is sufficient.

---

2. 3NF Removes Two Types of Problems

Condition X must be a superkey prevents:

1. Partial dependency

2. Transitive dependency

---

Alternative Definition of 3NF

A relation is in 3NF if every non-prime attribute:

1. Is fully functionally dependent on every candidate key.

2. Is non-transitively dependent on every candidate key.

---

Summary of 3NF Rules

A functional dependency X → A is acceptable in 3NF if:

Condition	Allowed?
X is a superkey	✅ Yes
A is a prime attribute	✅ Yes
X not superkey AND A not prime	❌ Violates 3NF

---

Key Idea to Remember

A relation is in 3NF if:

• It has no partial dependency

• It has no transitive dependency

or simply:

Every non-key attribute depends on the key, the whole key, and nothing but the key.

✅ Short Memory Rule

A table is in 3NF if every non-key attribute depends only on the key and not on another non-key attribute.