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

Need a 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

Need a 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

Need a 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

Need a hint?

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