Second Normal Form (2NF) & Functional Dependencies0%

Third Normal Form (3NF) & Transitive Dependencies

Second Normal Form (2NF) & Functional Dependencies

Intermediate12 min read•Updated: 2026-09-12

Second Normal Form (2NF) & Functional Dependencies

Once a table achieves First Normal Form (1NF), it must be evaluated for Second Normal Form (2NF). Second Normal Form eliminates Partial Functional Dependencies, ensuring that every non-key column depends on the entire primary key, rather than just a portion of it.

1. What is a Functional Dependency?

In relational database theory, a Functional Dependency (X -> Y) means that the value of attribute X uniquely determines the value of attribute Y.

If you know an employee's employee_id (X), you can uniquely determine their email (Y). We write: employee_id -> email.

2. The Rules of Second Normal Form (2NF)

A table is in Second Normal Form (2NF) if and only if:

1
It is already in First Normal Form (1NF).
2
It contains NO Partial Dependencies: Every non-key column must depend on the entire Primary Key!

[!IMPORTANT] Key Architectural Insight: If a table has a single-column Primary Key (e.g., user_id INT PRIMARY KEY), it is AUTOMATICALLY in 2NF! Partial dependencies can ONLY occur in tables that have a Composite Primary Key (a key composed of two or more columns).

3. The 2NF Violation: A Partial Dependency

Consider a junction table tracking student course enrollments where the Primary Key is the composite pair: (student_id, course_id):

 TABLE: enrollments_flawed (In 1NF, but VIOLATES 2NF!)
 PRIMARY KEY: (student_id, course_id)
 
 +------------+-----------+----------------+----------------+-------+
 | student_id | course_id | course_name    | instructor_name| grade |
 +------------+-----------+----------------+----------------+-------+
 |        101 |       CS1 | Python Mastery | Dr. Ramesh     | A     |
 |        102 |       CS1 | Python Mastery | Dr. Ramesh     | B+    |
 |        101 |       WD2 | Web Designing  | Prof. Amit     | A-    |
 +------------+-----------+----------------+----------------+-------+

Analyzing the Dependencies:

1
grade: Does grade depend on student_id alone? No (a student has multiple grades). Does it depend on course_id alone? No. It depends on both (student_id, course_id). This is a FULL functional dependency!
2
course_name & instructor_name: Does course_name depend on student_id? NO! It depends ONLY on course_id!

Because course_name depends on only a part of the composite primary key (course_id), this is a Partial Dependency!

4. The 2NF Solution: Decomposing into Two Tables

To achieve 2NF, remove the partially dependent columns and place them into a separate table where course_id serves as the primary key:

1-- Table 1: Courses (Eliminates partial dependency!)
2CREATE TABLE courses (
3    course_id VARCHAR(10) PRIMARY KEY,
4    course_name VARCHAR(100) NOT NULL,
5    instructor_name VARCHAR(100) NOT NULL
6);
7 
8-- Table 2: Student Course Enrollments (Now in 2NF!)
9CREATE TABLE student_enrollments (
10    student_id INT UNSIGNED NOT NULL,
11    course_id VARCHAR(10) NOT NULL,
12    grade CHAR(2) DEFAULT NULL,
13    
14    PRIMARY KEY (student_id, course_id),
15    CONSTRAINT fk_se_course FOREIGN KEY (course_id) REFERENCES courses(course_id)
16);

5. Best Practices & Common Pitfalls

Surrogate Keys Do Not Automatically Fix Schema Flaws: Some developers mistakenly believe that adding an auto-increment id INT PRIMARY KEY to a flawed composite table "fixes" 2NF. While it technically satisfies 2NF syntactically, the underlying semantic redundancy still exists! You must still decompose the tables.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does Second Normal Form (2NF) specifically eliminate?

Question 2

Can a table with a single-column Primary Key violate Second Normal Form (assuming it is in 1NF)?

Question 3

In a table with composite primary key (order_id, product_id), which column represents a partial dependency?

Question 4

What does the functional dependency notation A -> B mean?

Question 5

How is a table violating 2NF brought into full 2NF compliance?

Next Lesson

Third Normal Form (3NF) & Transitive Dependencies

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
First Normal Form (1NF)	Third Normal Form (3NF) & Transitive Dependencies

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Third Normal Form (3NF) & Transitive Dependencies

Second Normal Form (2NF) & Functional Dependencies

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Second Normal Form (2NF) & Functional Dependencies

1. What is a Functional Dependency?

In relational database theory, a Functional Dependency (X -> Y) means that the value of attribute X uniquely determines the value of attribute Y.

If you know an employee's employee_id (X), you can uniquely determine their email (Y). We write: employee_id -> email.

2. The Rules of Second Normal Form (2NF)

A table is in Second Normal Form (2NF) if and only if:

1
It is already in First Normal Form (1NF).
2
It contains NO Partial Dependencies: Every non-key column must depend on the entire Primary Key!

3. The 2NF Violation: A Partial Dependency

Consider a junction table tracking student course enrollments where the Primary Key is the composite pair: (student_id, course_id):

 TABLE: enrollments_flawed (In 1NF, but VIOLATES 2NF!)
 PRIMARY KEY: (student_id, course_id)
 
 +------------+-----------+----------------+----------------+-------+
 | student_id | course_id | course_name    | instructor_name| grade |
 +------------+-----------+----------------+----------------+-------+
 |        101 |       CS1 | Python Mastery | Dr. Ramesh     | A     |
 |        102 |       CS1 | Python Mastery | Dr. Ramesh     | B+    |
 |        101 |       WD2 | Web Designing  | Prof. Amit     | A-    |
 +------------+-----------+----------------+----------------+-------+

Analyzing the Dependencies:

1
grade: Does grade depend on student_id alone? No (a student has multiple grades). Does it depend on course_id alone? No. It depends on both (student_id, course_id). This is a FULL functional dependency!
2
course_name & instructor_name: Does course_name depend on student_id? NO! It depends ONLY on course_id!

Because course_name depends on only a part of the composite primary key (course_id), this is a Partial Dependency!

4. The 2NF Solution: Decomposing into Two Tables

To achieve 2NF, remove the partially dependent columns and place them into a separate table where course_id serves as the primary key:

1-- Table 1: Courses (Eliminates partial dependency!)
2CREATE TABLE courses (
3    course_id VARCHAR(10) PRIMARY KEY,
4    course_name VARCHAR(100) NOT NULL,
5    instructor_name VARCHAR(100) NOT NULL
6);
7 
8-- Table 2: Student Course Enrollments (Now in 2NF!)
9CREATE TABLE student_enrollments (
10    student_id INT UNSIGNED NOT NULL,
11    course_id VARCHAR(10) NOT NULL,
12    grade CHAR(2) DEFAULT NULL,
13    
14    PRIMARY KEY (student_id, course_id),
15    CONSTRAINT fk_se_course FOREIGN KEY (course_id) REFERENCES courses(course_id)
16);

5. Best Practices & Common Pitfalls

Surrogate Keys Do Not Automatically Fix Schema Flaws: Some developers mistakenly believe that adding an auto-increment id INT PRIMARY KEY to a flawed composite table "fixes" 2NF. While it technically satisfies 2NF syntactically, the underlying semantic redundancy still exists! You must still decompose the tables.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does Second Normal Form (2NF) specifically eliminate?

Question 2

Can a table with a single-column Primary Key violate Second Normal Form (assuming it is in 1NF)?

Question 3

In a table with composite primary key (order_id, product_id), which column represents a partial dependency?

Question 4

What does the functional dependency notation A -> B mean?

Question 5

How is a table violating 2NF brought into full 2NF compliance?

Next Lesson

Third Normal Form (3NF) & Transitive Dependencies

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
First Normal Form (1NF)	Third Normal Form (3NF) & Transitive Dependencies

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next