Second Normal Form (2NF) in DBMS Theory - Mini Project: Build & Apply

Understanding Second Normal Form (2NF)

📖 Scenario: You are working with a simple database table that stores information about students and the courses they take. The table currently has some repeated data and partial dependencies, which can cause problems when updating or deleting records.Your task is to organize this table into Second Normal Form (2NF) to make the data more reliable and easier to maintain.

🎯 Goal: Build a clear example of a database table in First Normal Form (1NF) and then transform it step-by-step into Second Normal Form (2NF) by removing partial dependencies.

📋 What You'll Learn

Create a table called student_courses with columns student_id, course_id, student_name, and course_name with exact sample data.

Add a variable called composite_key representing the combined primary key of student_id and course_id.

Create two separate tables called students and courses to remove partial dependencies.

Define the final structure showing tables in 2NF with proper keys and no partial dependencies.

💡 Why This Matters

🌍 Real World

Database designers use normalization to organize data efficiently and avoid errors when updating or deleting records.

💼 Career

Understanding 2NF is essential for roles like database administrator, data analyst, and software developer working with relational databases.

1

Create the initial table in First Normal Form (1NF)

Create a dictionary called student_courses representing a table with these exact entries: {(1, 101): {'student_name': 'Alice', 'course_name': 'Math'}, (1, 102): {'student_name': 'Alice', 'course_name': 'English'}, (2, 101): {'student_name': 'Bob', 'course_name': 'Math'}}. Use tuples of student_id and course_id as keys.

DBMS Theory

# Create the student_courses dictionary with given data
# Your code here

Hint

Use a dictionary with tuple keys for student_id and course_id. Each value is another dictionary with student_name and course_name.

2

Define the composite primary key

Create a variable called composite_key and set it to a tuple containing the strings 'student_id' and 'course_id' to represent the combined primary key.

DBMS Theory

student_courses = {
    (1, 101): {'student_name': 'Alice', 'course_name': 'Math'},
    (1, 102): {'student_name': 'Alice', 'course_name': 'English'},
    (2, 101): {'student_name': 'Bob', 'course_name': 'Math'}
}
# Define the composite primary key
# Your code here

Hint

The composite key is a tuple of the two column names that together identify each record uniquely.

3

Separate tables to remove partial dependencies

Create two dictionaries called students and courses. students maps student_id to student_name with entries {1: 'Alice', 2: 'Bob'}. courses maps course_id to course_name with entries {101: 'Math', 102: 'English'}.

DBMS Theory

student_courses = {
    (1, 101): {'student_name': 'Alice', 'course_name': 'Math'},
    (1, 102): {'student_name': 'Alice', 'course_name': 'English'},
    (2, 101): {'student_name': 'Bob', 'course_name': 'Math'}
}

composite_key = ('student_id', 'course_id')
# Create students and courses dictionaries
# Your code here

Hint

Separate the student names and course names into their own dictionaries keyed by their IDs.

4

Show the final 2NF structure

Create a dictionary called enrollments with keys as tuples of student_id and course_id and empty dictionaries as values to represent the relationship table in 2NF.

DBMS Theory

student_courses = {
    (1, 101): {'student_name': 'Alice', 'course_name': 'Math'},
    (1, 102): {'student_name': 'Alice', 'course_name': 'English'},
    (2, 101): {'student_name': 'Bob', 'course_name': 'Math'}
}

composite_key = ('student_id', 'course_id')

students = {1: 'Alice', 2: 'Bob'}
courses = {101: 'Math', 102: 'English'}
# Create enrollments dictionary to complete 2NF
# Your code here

Hint

The enrollments dictionary links student IDs and course IDs without repeating names, completing the 2NF structure.

Practice

1. Which of the following best describes the Second Normal Form (2NF) in database normalization?

easy

A. It requires the table to have no duplicate rows.

B. It removes transitive dependencies.

C. It removes partial dependencies on a composite primary key.

D. It ensures all columns are atomic.

Solution

1. Step 1: Understand 2NF definition

   2NF requires that every non-key attribute is fully functionally dependent on the entire primary key, not just part of it.

2. Step 2: Compare options

   It removes partial dependencies on a composite primary key correctly states removal of partial dependencies on composite keys. Other options describe 1NF (atomic columns), uniqueness, or 3NF (transitive dependencies).

3. Final Answer:

   It removes partial dependencies on a composite primary key. -> Option C

4. Quick Check:

   2NF = Remove partial dependencies [OK]

Hint: 2NF removes partial dependencies on composite keys [OK]

Common Mistakes:

• Confusing 2NF with 1NF or 3NF
• Thinking 2NF removes transitive dependencies
• Believing 2NF applies to single-column keys only

2. Which of the following is the correct condition for a table to be in Second Normal Form (2NF)?

easy

A. Every non-key attribute must depend on the whole primary key.

B. All attributes must be unique.

C. There must be no transitive dependencies.

D. The table must have no repeating groups.

Solution

1. Step 1: Recall 2NF condition

   2NF requires that non-key attributes depend on the entire primary key, not just part of it.

2. Step 2: Analyze options

   Every non-key attribute must depend on the whole primary key matches this condition. The table must have no repeating groups describes 1NF, There must be no transitive dependencies describes 3NF, and All attributes must be unique is unrelated to normalization.

3. Final Answer:

   Every non-key attribute must depend on the whole primary key. -> Option A

4. Quick Check:

   2NF = Full dependency on primary key [OK]

Hint: Non-key attributes depend on entire key in 2NF [OK]

Common Mistakes:

• Mixing 2NF with 1NF or 3NF conditions
• Assuming uniqueness means 2NF
• Ignoring composite keys in dependency

3. Consider a table with columns StudentID, CourseID, and InstructorName. The primary key is a composite of StudentID and CourseID. If InstructorName depends only on CourseID, what is the normalization status of this table?

medium

A. The table is not in 2NF due to partial dependency.

B. The table is in 2NF.

C. The table is in 3NF.

D. The table violates 1NF.

Solution

1. Step 1: Identify dependencies

   InstructorName depends only on CourseID, which is part of the composite key (StudentID, CourseID).

2. Step 2: Check for partial dependency

   Since InstructorName depends on only part of the key, this is a partial dependency, violating 2NF.

3. Final Answer:

   The table is not in 2NF due to partial dependency. -> Option A

4. Quick Check:

   Partial dependency means not 2NF [OK]

Hint: Partial dependency on part of key breaks 2NF [OK]

Common Mistakes:

• Assuming table is 2NF because it has a composite key
• Confusing 2NF with 3NF
• Ignoring partial dependencies

4. A table has columns OrderID, ProductID, ProductName, and Quantity. The primary key is composite: (OrderID, ProductID). The ProductName depends only on ProductID. What is the error in this design regarding 2NF?

medium

A. There is a transitive dependency violating 2NF.

B. The primary key is not composite.

C. The table is already in 2NF.

D. There is a partial dependency violating 2NF.

Solution

1. Step 1: Identify dependency of ProductName

   ProductName depends only on ProductID, part of the composite key (OrderID, ProductID).

2. Step 2: Recognize partial dependency

   This partial dependency violates 2NF, which requires full dependency on the entire key.

3. Final Answer:

   There is a partial dependency violating 2NF. -> Option D

4. Quick Check:

   Partial dependency breaks 2NF [OK]

Hint: Partial dependency on part of composite key breaks 2NF [OK]

Common Mistakes:

• Confusing partial with transitive dependency
• Thinking primary key must be single column for 2NF
• Assuming table is normalized without checking dependencies

5. You have a table with columns EmployeeID, ProjectID, EmployeeName, and ProjectName. The primary key is composite: (EmployeeID, ProjectID). Both EmployeeName and ProjectName depend only on their respective single keys (EmployeeID and ProjectID). How should you redesign the tables to achieve 2NF?

hard

A. Keep one table but remove EmployeeName and ProjectName columns.

B. Split into three tables: Employee(EmployeeID, EmployeeName), Project(ProjectID, ProjectName), and Assignment(EmployeeID, ProjectID).

C. Add a new composite key including EmployeeName and ProjectName.

D. No changes needed; the table is already in 2NF.

Solution

1. Step 1: Identify partial dependencies

   EmployeeName depends only on EmployeeID, and ProjectName depends only on ProjectID, parts of the composite key.

2. Step 2: Normalize by splitting tables

   To remove partial dependencies, create separate tables for Employee and Project details, and a linking table for assignments.

3. Final Answer:

   Split into three tables: Employee(EmployeeID, EmployeeName), Project(ProjectID, ProjectName), and Assignment(EmployeeID, ProjectID). -> Option B

4. Quick Check:

   Separate tables remove partial dependencies [OK]

Hint: Split tables to remove partial dependencies for 2NF [OK]

Common Mistakes:

• Trying to keep all data in one table
• Adding composite keys incorrectly
• Ignoring partial dependencies on composite keys