18:00
Lec-28: Second Normal Form | 2NF | Database Management System
Gate Smashers
Overview
This video explains the Second Normal Form (2NF) in database management systems, building upon the First Normal Form (1NF). The core principle of 2NF is the elimination of partial dependencies, meaning that all non-prime attributes must be fully functionally dependent on the entire candidate key, not just a part of it. The video illustrates this with examples and demonstrates how to decompose tables that violate 2NF into tables that adhere to it, using a practical example of a customer and store relationship and then solving a GATE exam question.
How was this?
Save this permanently with flashcards, quizzes, and AI chat
Chapters
- A table must be in First Normal Form (1NF) to be considered for Second Normal Form (2NF).
- The primary rule for 2NF is that there should be no partial dependencies.
- All non-prime attributes must be fully functionally dependent on the entire candidate key.
Ensuring tables are in 2NF helps to reduce data redundancy and improve data integrity by preventing anomalies that can arise from partial dependencies.
A customer table with a composite candidate key (CustomerID, StoreID) and a non-prime attribute 'Location' which is only dependent on 'StoreID' (a part of the candidate key).
- Non-prime attributes are those not part of any candidate key.
- A partial dependency occurs when a non-prime attribute is functionally dependent on only a part (a proper subset) of a composite candidate key.
- This is problematic because it means the non-prime attribute's information is repeated unnecessarily across different parts of the candidate key.
Identifying and eliminating partial dependencies is crucial for database design, as they lead to update, insertion, and deletion anomalies.
In a table with candidate key (A, B) and non-prime attribute C, if A determines C, then there is a partial dependency because C is dependent on only 'A', a part of the candidate key (A, B).
- To resolve partial dependencies, a table must be decomposed into smaller tables.
- One new table will contain the parts of the candidate key that caused the partial dependency, along with the dependent non-prime attribute.
- Another table will retain the remaining parts of the original candidate key and other attributes, ensuring that all non-prime attributes in the new tables are fully dependent on their respective candidate keys.
Decomposition breaks down complex relationships into simpler, more manageable ones, ensuring each table adheres to 2NF and reducing redundancy.
The customer table with (CustomerID, StoreID) as candidate key and Location dependent on StoreID is split into two tables: one with (CustomerID, StoreID) and another with (StoreID, Location).
- The process involves finding the candidate key(s) of the relation.
- Identify prime attributes (part of any candidate key) and non-prime attributes (not part of any candidate key).
- Check for partial dependencies: a proper subset of a candidate key determining a non-prime attribute.
- If any partial dependency exists, the table is not in 2NF and needs decomposition.
This systematic approach allows learners to confidently apply 2NF principles to solve real-world database design problems and exam questions.
For a relation R(A, B, C, D, E, F) with FDs C->F, E->A, EC->D, A->B, the candidate key is EC. Since C determines F (a non-prime attribute) and E determines A (a non-prime attribute), partial dependencies exist, violating 2NF.
Key takeaways
- Second Normal Form (2NF) requires a table to be in 1NF and have no partial dependencies.
- Partial dependencies occur when a non-prime attribute depends on only a part of a composite candidate key.
- Fully functional dependency means all non-prime attributes must depend on the *entire* candidate key.
- Decomposition is the method to fix tables violating 2NF by splitting them into smaller, 2NF-compliant tables.
- Understanding candidate keys and prime/non-prime attributes is fundamental to applying 2NF.
- The presence of even a single partial dependency means a table is not in 2NF.
Key terms
Second Normal Form (2NF)First Normal Form (1NF)Partial DependencyFull Functional DependencyCandidate KeyComposite Candidate KeyPrime AttributeNon-Prime AttributeDecomposition
Test your understanding
- What are the two main conditions a table must satisfy to be in Second Normal Form (2NF)?
- How does a partial dependency differ from a full functional dependency in the context of 2NF?
- What is the process for decomposing a table that violates 2NF?
- Why is it important to identify and eliminate partial dependencies in database design?
- How do you determine if an attribute is prime or non-prime relative to a candidate key?