Functional Dependency and 2NF

Sub-Page 2: Understanding Dependencies and Second Normal Form

What is Functional Dependency?

A functional dependency (FD) is a relationship where one attribute (or set of attributes) uniquely determines another attribute. Denoted as X → Y, meaning X determines Y.

Types: Full, Partial, Transitive. Used to identify redundancies for normalization.

Example Table:

StudentID	Name	Advisor	AdvisorPhone
101	Alice	Dr. Smith	555-0101
102	Bob	Dr. Smith	555-0101

FD: StudentID → Name (full), Advisor → AdvisorPhone (transitive via StudentID → Advisor → AdvisorPhone).

Second Normal Form (2NF)

2NF builds on 1NF by ensuring no partial dependencies: all non-key attributes must fully depend on the entire primary key, not just part of it (applies to tables with composite keys).

Example 1: Order Details

OrderID	ProductID	ProductName	Quantity
1	101	Laptop	2
2	102	Phone	1

Partial dependency: ProductName depends only on ProductID, not the full key (OrderID, ProductID).

2NF Solution

Split into Orders and Products tables to eliminate partial dependency.

Products(ProductID, ProductName)

Orders(OrderID, ProductID, Quantity)

Example 2: Student Courses

StudentID	CourseID	CourseName	Grade
101	C1	Mathematics	A
102	C2	Physics	B

Partial dependency: CourseName depends only on CourseID, not the full key (StudentID, CourseID).

2NF Solution

Split into Courses and Enrollments tables.

Courses(CourseID, CourseName)

Enrollments(StudentID, CourseID, Grade)

Example 3: Employee Projects

EmployeeID	ProjectID	ProjectName	HoursWorked
E1	P1	Website Redesign	40
E2	P2	App Development	30

Partial dependency: ProjectName depends only on ProjectID, not the full key (EmployeeID, ProjectID).

2NF Solution

Split into Projects and Assignments tables.

Projects(ProjectID, ProjectName)

Assignments(EmployeeID, ProjectID, HoursWorked)

SQL for 2NF

-- SQL for Example 1: Order Details
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50)
);
CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- SQL for Example 2: Student Courses
CREATE TABLE Courses (
    CourseID VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(50)
);
CREATE TABLE Enrollments (
    StudentID INT,
    CourseID VARCHAR(10),
    Grade CHAR(1),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

-- SQL for Example 3: Employee Projects
CREATE TABLE Projects (
    ProjectID VARCHAR(10) PRIMARY KEY,
    ProjectName VARCHAR(50)
);
CREATE TABLE Assignments (
    EmployeeID VARCHAR(10),
    ProjectID VARCHAR(10),
    HoursWorked INT,
    PRIMARY KEY (EmployeeID, ProjectID),
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID)
);

These schemas ensure no partial dependencies by separating entity-specific attributes into dedicated tables.

Key Takeaways

Functional Dependency

Attributes must depend on the key, enabling proper normalization.

Full Dependency

2NF ensures non-key attributes depend on the entire primary key.

Foundation for 3NF

2NF sets the stage for eliminating transitive dependencies.

Back to 1NF Next: 3NF