AI-Generated Video Summary by NoteTube
Lec-29: Third Normal Form in dbms with examples in Hindi | Normalization
Gate Smashers
Overview
This video explains the Third Normal Form (3NF) in database management systems, building upon the concepts of Second Normal Form (2NF). The primary rule for 3NF is that a table must first be in 2NF and must not contain any transitive dependencies. A transitive dependency occurs when a non-prime attribute (an attribute not part of any candidate key) determines another non-prime attribute. The video uses examples to illustrate transitive dependencies, showing how they can lead to data redundancy and anomalies. It then provides a clear condition to check for 3NF: for every functional dependency (FD), either the left-hand side (LHS) must be a candidate key or superkey, or the right-hand side (RHS) must be a prime attribute (part of a candidate key). The video emphasizes the importance of correctly identifying all candidate keys when applying these rules.
This summary expires in 30 days. Save it permanently with flashcards, quizzes & AI chat.
Chapters
- •A table must be in Second Normal Form (2NF) to be considered for 3NF.
- •The core rule of 3NF is the absence of transitive dependencies.
- •Transitive dependency involves non-prime attributes determining other non-prime attributes.
- •A non-prime attribute is one not part of any candidate key.
- •Transitive dependency occurs when a non-prime attribute is determined by another non-prime attribute.
- •Example: Roll Number -> State, State -> City. Here, Roll Number (candidate key) determines State (non-prime), and State (non-prime) determines City (non-prime).
- •This creates a transitive dependency where Roll Number indirectly determines City via State.
- •Table: Roll Number, State, City
- •Functional Dependencies (FDs): Roll Number -> State, State -> City
- •Candidate Key: Roll Number
- •Prime Attributes: Roll Number
- •Non-Prime Attributes: State, City
- •The FD 'State -> City' violates 3NF because a non-prime attribute (State) determines another non-prime attribute (City).
- •Relation: ABCD
- •FDs: AB -> C, C -> D
- •Candidate Key: AB
- •Prime Attributes: A, B
- •Non-Prime Attributes: C, D
- •The FD 'C -> D' violates 3NF as non-prime C determines non-prime D.
- •This leads to a transitive dependency where AB indirectly determines D.
- •Relation: ABCD
- •FDs: AB -> CD, D -> A
- •Candidate Keys: AB, DB
- •Prime Attributes: A, B, D
- •Non-Prime Attribute: C
- •Checking FDs against 3NF conditions:
- •AB -> CD: LHS (AB) is a candidate key. Valid.
- •D -> A: RHS (A) is a prime attribute. Valid.
- •The table is in 3NF because no non-prime attribute determines another non-prime attribute.
- •A table is in 3NF if it is in 2NF and has no transitive dependency.
- •Practical condition: For every FD (X -> Y), either X is a candidate key/superkey, OR Y is a prime attribute.
- •It's crucial to find ALL candidate keys accurately.
- •If the LHS of an FD is a candidate key, it's fine.
- •If the LHS is not a candidate key, check if the RHS is a prime attribute.
Key Takeaways
- 1Third Normal Form (3NF) aims to eliminate transitive dependencies.
- 2A transitive dependency exists when a non-prime attribute determines another non-prime attribute.
- 3For a table to be in 3NF, it must first be in 2NF.
- 4The key condition for 3NF is: for every functional dependency X -> Y, either X must be a candidate key (or superkey), or Y must be a prime attribute.
- 5Identifying all candidate keys is a critical step in verifying 3NF.
- 6If a non-prime attribute determines another non-prime attribute, the table is not in 3NF.
- 7Prime attributes can determine other prime attributes without violating 3NF.
- 8The goal is to ensure that all non-key attributes are directly dependent on the primary key, not on other non-key attributes.