Second Normal Form (2NF) - Detailed Explanation

 

Second Normal Form (2NF) – Detailed Explanation

Second Normal Form (2NF) focuses on removing partial dependency so that every non-key attribute depends on the entire primary key, not just part of it.

Concept of Full Functional Dependency

2NF is based on the idea of full functional dependency.

Full Functional Dependency

A functional dependency X → Y is full if:

  • Removing any attribute from X makes the dependency invalid.

Example

{Ssn, Pnumber} → Hours

  • Ssn alone → Hours ❌

  • Pnumber alone → Hours ❌

Therefore this dependency is full functional dependency.

Partial Dependency

A dependency is partial if part of the key can determine an attribute.

Example:

{Ssn, Pnumber} → Ename

But actually:

Ssn → Ename

So the attribute Ename depends only on part of the key (Ssn).

This is called partial dependency, and it violates 2NF.

Definition of Second Normal Form

A relation schema R is in Second Normal Form (2NF) if:

Every nonprime attribute A in R is fully functionally dependent on the primary key of R.


Important Terms

TermMeaning
Prime attribute        Attribute that is part of a primary  key
Nonprime attribute        Attribute that is NOT part of any primary key
Full dependency        Attribute depends on whole key
Partial dependency        Attribute depends on part of key

Key Rule for 2NF

2NF is concerned only when the primary key is composite (multiple attributes).

If the primary key has only one attribute, the relation is automatically in 2NF.

Example: EMP_PROJ Relation

Consider the relation:

EMP_PROJ(Ssn, Pnumber, Ename, Pname, Plocation, Hours)

Primary Key:

(Ssn, Pnumber)

Functional Dependencies:

Ssn → Ename
Pnumber → Pname, Plocation
{Ssn, Pnumber} → Hours

Problems in This Relation

Partial Dependency 1

Ssn → Ename

Ename depends on only part of the primary key.

Partial Dependency 2

Pnumber → Pname, Plocation

These attributes depend only on Pnumber, not the full key.

Therefore

The relation is:

1NF ✔
2NF ❌

Converting to Second Normal Form

To achieve 2NF, we separate attributes that depend on part of the key.

Step 1: Employee Relation

EMPLOYEE(Ssn, Ename)

Step 2: Project Relation

PROJECT(Pnumber, Pname, Plocation)

Step 3: Works Relation

WORKS_ON(Ssn, Pnumber, Hours)

Primary key:

(Ssn, Pnumber)

Final Result

Relation    Key
EMPLOYEE(EP2)            Ssn
PROJECT(EP3)            Pnumber
WORKS_ON(EP1)            (Ssn, Pnumber)

Now every non-key attribute depends on the whole key.


Why 2NF is Important

2NF helps eliminate:

Data Redundancy

Repeated storage of the same information.

Update Anomaly

Changing a value in many rows.

Insertion Anomaly

Cannot insert data without other data.

Deletion Anomaly

Deleting one tuple removes important information.

Practice Questions: Normalize into 2NF

Question 1

Consider the relation:

ENROLLMENT(Student_ID, Course_ID, Student_Name, Course_Name, Instructor, Grade)

Functional Dependencies:

  • (Student_ID, Course_ID) → Grade

  • Student_ID → Student_Name

  • Course_ID → Course_Name, Instructor

Task:

  1. Identify the candidate key.

  2. Determine the partial dependencies.

  3. Convert the relation into 2NF.

Question 2

Consider the relation:

ORDER_DETAILS(Order_ID, Product_ID, Product_Name, Quantity, Unit_Price, Customer_ID, Customer_Name)

Functional Dependencies:

  • (Order_ID, Product_ID) → Quantity

  • Product_ID → Product_Name, Unit_Price

  • Order_ID → Customer_ID

  • Customer_ID → Customer_Name

Task:

  1. Identify the primary key.

  2. Find partial dependencies.

  3. Decompose the relation into 2NF relations.

Question 3

Consider the relation:

SUPPLIER_PART(Supplier_ID, Part_ID, Supplier_Name, Part_Name, Price, Quantity)

Functional Dependencies:

  • (Supplier_ID, Part_ID) → Quantity

  • Supplier_ID → Supplier_Name

  • Part_ID → Part_Name, Price

Task:

  1. Identify the candidate key.

  2. Identify partial dependencies.

  3. Convert the relation into 2NF.

Question 4

Consider the relation:

STUDENT_COURSE(Student_ID, Course_ID, Student_Name, Course_Name, Credits, Marks)

Functional Dependencies:

  • (Student_ID, Course_ID) → Marks

  • Student_ID → Student_Name

  • Course_ID → Course_Name, Credits

Task:

  1. Identify the primary key.

  2. Find partial dependencies.

  3. Normalize the relation into 2NF.

Question 5

Consider the relation:

PROJECT_ASSIGNMENT(Employee_ID, Project_ID, Employee_Name, Project_Name, Hours_Worked, Project_Location)

Functional Dependencies:

  • (Employee_ID, Project_ID) → Hours_Worked

  • Employee_ID → Employee_Name

  • Project_ID → Project_Name, Project_Location

Task:

  1. Identify the candidate key.

  2. Identify partial dependencies.

  3. Decompose the relation into 2NF.

Answers

Answer 1

Candidate Key:
(Student_ID, Course_ID)

Partial Dependencies:

  • Student_ID → Student_Name

  • Course_ID → Course_Name, Instructor

2NF Relations

  1. STUDENT(Student_ID, Student_Name)

  2. COURSE(Course_ID, Course_Name, Instructor)

  3. ENROLLMENT(Student_ID, Course_ID, Grade)

Answer 2

Primary Key:
(Order_ID, Product_ID)

Partial Dependencies:

  • Product_ID → Product_Name, Unit_Price

  • Order_ID → Customer_ID

2NF Relations

  1. PRODUCT(Product_ID, Product_Name, Unit_Price)

  2. ORDER(Order_ID, Customer_ID)

  3. ORDER_DETAILS(Order_ID, Product_ID, Quantity)

  4. CUSTOMER(Customer_ID, Customer_Name)

Answer 3

Candidate Key:
(Supplier_ID, Part_ID)

Partial Dependencies:

  • Supplier_ID → Supplier_Name

  • Part_ID → Part_Name, Price

2NF Relations

  1. SUPPLIER(Supplier_ID, Supplier_Name)

  2. PART(Part_ID, Part_Name, Price)

  3. SUPPLIER_PART(Supplier_ID, Part_ID, Quantity)

Answer 4

Primary Key:
(Student_ID, Course_ID)

Partial Dependencies:

  • Student_ID → Student_Name

  • Course_ID → Course_Name, Credits

2NF Relations

  1. STUDENT(Student_ID, Student_Name)

  2. COURSE(Course_ID, Course_Name, Credits)

  3. STUDENT_COURSE(Student_ID, Course_ID, Marks)

Answer 5

Candidate Key:
(Employee_ID, Project_ID)

Partial Dependencies:

  • Employee_ID → Employee_Name

  • Project_ID → Project_Name, Project_Location

2NF Relations

  1. EMPLOYEE(Employee_ID, Employee_Name)

  2. PROJECT(Project_ID, Project_Name, Project_Location)

  3. PROJECT_ASSIGNMENT(Employee_ID, Project_ID, Hours_Worked)

Comments

Post a Comment

Popular posts from this blog

Database Management Systems DBMS PCCST402 Semester 4 KTU CS 2024 Scheme

 About Me Database Management Systems DBMS PCCST402 Scheme and Syllabus Module-1 Introduction to DBMS Introduction to DBMS Example  Database Database System Vs Traditional File Processing Advantages of using DBMS Database System Concepts and Architecture Data Models, Schemas and Instances   Three schema architecture and Data Independence Database Languages and Interfaces Centralized and Client Server Architectures in DBMS Classification of Database Management Systems Conceptual Data Modeling and Database Design Conceptual Modeling and the Role of the ER Model in Database Design Entities and Attributes  Company Database Application - Example Entity Types, Entity Sets, Keys, and Value Sets in the ER Model Initial Conceptual Design of the COMPANY Database Relationship Types in the ER Model Relationship Degree, Role Names, and Recursive Relationships Constraints on Binary Relationship Types Attributes of Relationship Types Weak Entity Types Summary of the Notations Used ...
Read more

Data Models, Schemas and Instances

  Data Models, Schemas and Instances  1. Data Abstraction In databases, data abstraction means hiding the complex details of how data is stored and focusing only on what is important. Different users can view data at different levels of detail depending on their needs. 2. Data Models A data model is a collection of concepts used to describe the structure of a database. It defines: Data types Relationships among data Constraints on data Operations for accessing and updating data Data models help us understand and design databases without worrying about low-level storage details. Categories of Data Models a) High-level / Conceptual Data Models Close to how users think about data. Use concepts like entities , attributes , and relationships . Example: ER (Entity–Relationship) Model . b) Representational (Implementation) Data Models In between conceptual and physical models. Easy for users to understand but still close to how data is st...
Read more

Introduction to Database Management System -DBMS

Image
In today’s digital world, almost every application—from mobile banking and e-commerce to university portals and hospital systems—depends on the efficient storage, organization, and retrieval of data. As the amount of information generated by individuals and organizations continues to grow, managing this data in a structured and meaningful way becomes essential. This is where database systems play a central role. A database can be thought of as a well-organized collection of related data, designed so that it can be easily accessed, managed, and updated. But the database itself is only one component. To manage it effectively, we rely on a specialized software package known as a Database Management System (DBMS) . A DBMS provides a systematic and automated way to store large volumes of data, enforce rules for consistency and correctness, protect data from unauthorized access, and allow multiple users to work with the data simultaneously without conflicts. A database typically has three...
Read more