Database Management Systems DBMS PCCST402 Semester 4 KTU CS 2024 Scheme - Dr Binu V P

 Problems Given: Relation: R(A, B, C, D, E) Functional Dependencies: A → B A → C BC → D D → E i. Identify the candidate key(s). ii.Determine whether R is in 1NF, 2NF, and 3NF. Give reasons for each. iii. If R is not in 3NF, decompose it into 3NF relations. (i) Find Candidate Key(s) Step 1: Find closure of A Compute A + A^+ A + : A → B, C ⇒ {A, B, C} BC → D ⇒ {A, B, C, D} D → E ⇒ {A, B, C, D, E} 👉 A + = { A , B , C , D , E } A^+ = \{A, B, C, D, E\} A + = { A , B , C , D , E } ✅ Conclusion: A determines all attributes So, A is a candidate key Check others (brief): No smaller subset exists So only one candidate key ✅ Answer: Candidate Key = {A} (ii) Normal Forms ✅ 1NF (First Normal Form) Assumes atomic attributes (given in question) ✔ R is in 1NF ✅ 2NF (Second Normal Form) Rule: No partial dependency on candidate key 👉 Candidate key = A (single attribute) Partial dependency happens only with composite keys Here ke...

  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 ...

  Third Normal Form (3NF) Third Normal Form (3NF) is a database normalization rule that removes transitive dependencies from a relation. It ensures that non-key attributes depend only on the primary key and nothing else . 1. What is a Transitive Dependency? A transitive dependency occurs when: X → Z Z → Y So indirectly: X → Y through Z But Z is not a candidate key . This means Y depends on X indirectly through Z , which causes redundancy and possible update anomalies. 2. Definition of 3NF According to Edgar F. Codd’s definition : A relation schema R is in Third Normal Form (3NF) if: It is already in Second Normal Form (2NF) , and No non-prime attribute is transitively dependent on the primary key . Important Terms Prime attribute → part of a candidate key Non-prime attribute → not part of any candidate key So in simple terms: Non-key attributes should depend directly on the primary key, not through another non-key attribute. 3. ...

  Problem  Given: F = { A → BC, CD → E, B → D, E → A } Tasks: Find A⁺ Prove whether A → E Check if A is a candidate key Solution: Step 1: Start closure A⁺ = {A} Step 2: Apply A → BC A⁺ = {A, B, C} Step 3: Apply B → D A⁺ = {A, B, C, D} Step 4: Apply CD → E A⁺ = {A, B, C, D, E} Step 5: Apply E → A (already included)  Final: A⁺ = {A, B, C, D, E} Answers: A → E ✅ (since E ∈ A⁺) A is a candidate key ✅ Problem  Given: F = { AB → C, C → D, D → E, E → B } Tasks: Find (AB)⁺ Check if AB → E Find all attributes determined by AB Solution: Start: (AB)⁺ = {A, B} Apply: AB → C → {A, B, C} C → D → {A, B, C, D} D → E → {A, B, C, D, E} E → B (already present) Final: (AB)⁺ = {A, B, C, D, E} Answers: AB → E ✅ AB determines all attributes Problem 3 Given: F = { A → B, B → C, AC → D, D → E, E → F } ❓ Tasks: Find A⁺ Is AC → F valid? Is A → D valid? ✅ Solution: Step 1: A⁺ A → B → C A⁺ = {A, ...