Achieving Second Normal Form

To achieve Second Normal Form (2NF), start with a relation in 1NF, identify its candidate keys, and remove dependencies in which a non-prime attribute depends on only part of a candidate key. Place those attributes with their determinant in a separate relation, retaining the attributes needed to reconnect the data.

The essential task is to separate facts with different determinants without losing information. This lesson carries the dependency analysis from Lesson 9 into a complete decomposition. It uses a smaller, single-term enrollment example so that each step is easy to follow.

1. Establish a 1NF representation. Store one value from the intended domain in each attribute position and remove repeating slots or lists used to hold multiple occurrences of a fact. Define how rows are identified. A declared SQL primary key is useful for enforcement, but naming a primary key alone does not establish a normal form.
2. Document the business rules and candidate keys. State what a row means and which time period it covers. A candidate key is a minimal set of attributes that determines every attribute in the relation. Review all candidate keys, not only the one selected as the primary key.
3. List the relevant functional dependencies. Write each rule as X → Y. For a composite candidate key, check whether a proper subset determines any non-prime attribute. A non-prime attribute belongs to no candidate key.
4. Separate the partial dependencies. For each appropriate determinant, create a relation containing that determinant and its dependent attributes. Remove the moved attributes from the original relation while retaining the shared key attributes needed for relationships.
5. Verify the result. Account for every original attribute, check each resulting relation for remaining partial dependencies, and verify that joining the projections reconstructs the original relation. Implement the required primary, unique, and foreign-key constraints.

Do not move a column merely because its values repeat. Repeating student IDs in enrollments is expected. The reason to move a student name is the dependency StudentID → StudentName, not repetition by itself. A foreign key is not exempt from dependency analysis.

Assume this relation represents enrollments for one fixed teaching period. A student can enroll in several courses, and a course can have several students. Each student has one recorded name, and each course has one recorded name and one instructor for this period. Each student-course pair occurs at most once. Names are not assumed unique.

The candidate key is (StudentID, CourseID) under these rules. Although each student appears only once in this small sample, that does not make StudentID a key: the rules permit another enrollment for the same student.

StudentID → StudentName
CourseID → CourseName, Instructor
StudentID, CourseID → StudentName, CourseName, Instructor

Both the student name and the course details depend on proper subsets of the composite key. These are partial dependencies, so the original relation is not in 2NF.

Step 1: Preserve the Student Facts

Move StudentName into Student, where StudentID is the primary key. Keep StudentID in the enrollment relation as a foreign key. Moving course details alone would not complete this decomposition; discarding the student names would lose information.

Step 2: Preserve the Course Facts

Move CourseName and Instructor into Course, where CourseID is the primary key. The course description and instructor assignment are now recorded once per course within this example's fixed period.

Step 3: Retain the Enrollment Relationship

StudentCourse now records only which student takes which course. Its primary key combines both columns; each column is also a foreign key to its corresponding parent table.

Student       (StudentID PK, StudentName)
Course        (CourseID PK, CourseName, Instructor)
StudentCourse (StudentID PK/FK, CourseID PK/FK)
-- StudentCourse primary key: (StudentID, CourseID)

Under the stated dependencies, Student and Course have no partial dependencies. StudentCourse has no non-prime attributes, so there are no non-prime attributes that could depend on only part of its key. All three relations satisfy 2NF.

If a final grade were added, it would remain with the enrollment when the full student-course pair is required to determine it. For multiple terms or parallel sections, use an offering identifier or an expanded offering key as in Lesson 9. Do not assume that a catalog course always has the same instructor.

A lossless decomposition allows the original relation to be reconstructed by joining its projections, without missing rows or introducing spurious combinations. Merely creating smaller tables is not enough.

Here, Student and StudentCourse share StudentID, which determines every attribute in Student. Course and StudentCourse share CourseID, which determines every attribute in Course. Each enrollment therefore joins to one matching student and one matching course:

SELECT sc.StudentID, sc.CourseID, s.StudentName,
       c.CourseName, c.Instructor
FROM StudentCourse AS sc
JOIN Student AS s ON s.StudentID = sc.StudentID
JOIN Course AS c ON c.CourseID = sc.CourseID;

For the displayed data, this query returns the two original rows, including Alice and Bob. The original dependencies are also preserved: student details can be checked in Student, course details in Course, and enrollment uniqueness in StudentCourse.

When migrating existing data, first investigate conflicting names or instructor assignments for the same identifier. SELECT DISTINCT removes identical copies; it does not resolve contradictory facts. Load the parent records before the enrollments and enable the foreign keys so that orphaned references are rejected.

This design lets a student or course exist without an enrollment. Deleting an enrollment no longer requires deleting the student's name or the course's description. Those are practical improvements in addition to removing repeated descriptive facts.

A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key. Equivalently, there must be no dependency X → A implied by the business rules where A is non-prime and X is a proper subset of a candidate key.

“Fully dependent” means no smaller subset of that key still determines the attribute. The test includes dependencies implied by other dependencies, not just the arrows initially written down.

• Checking only the primary key: an alternate composite candidate key can also have partial dependencies.
• Adding an artificial ID: a surrogate primary key does not remove dependencies on an existing composite candidate key. It does not automatically normalize the relation.
• Removing every repeated value: foreign keys repeat legitimately; the target is redundant facts caused by partial dependencies.
• Assuming 2NF finishes normalization: transitive dependencies may remain. For example, a course relation containing InstructorID and InstructorOffice can be in 2NF while still needing a 3NF decomposition.

An entity-relationship model helps identify students, courses, and enrollments. Dependency analysis checks whether their attributes and keys represent the intended rules. Neither method guarantees a correct design without accurate requirements.

The two methods complement each other, but they do not guarantee identical schemas. Modeling choices, time scope, and business constraints affect the result. Follow the iterative database design process: propose a model, test its dependencies and normal forms^[1], review it with users, and revise it where necessary.

Complete the Second Normal Form Exercise. For each decomposition, identify the candidate key, name the partial dependency, and show where every original attribute is retained.

The next lesson explains the limitations of Second Normal Form and why further normalization may be needed.

[1] Normal form: A set of conditions used to evaluate the structure and dependencies of a relation. Here, 2NF adds a restriction on partial dependencies to the requirements of 1NF.