UNIT – III: Normalization

 1. Database Anomalies

• Insertion Anomaly: Unable to add data due to missing information.

• Deletion Anomaly: Unnecessary loss of data when deleting a record.

• Update Anomaly: Same data stored multiple times; updating one copy may cause inconsistency.

Example:
A student-course table where deleting the last course of a student also deletes student info.

---

2. Functional Dependency

• A functional dependency (FD) exists when one attribute uniquely determines another.

• Denoted as X → Y, meaning attribute X determines Y.

Example:
RollNo → StudentName (RollNo uniquely identifies a student).

---

3. Rules for Functional Dependency

• Reflexivity: If X is a set of attributes, then X → Y where Y ⊆ X.

• Augmentation: If X → Y, then XZ → YZ.

• Transitivity: If X → Y and Y → Z, then X → Z.

---

4. Types of Functional Dependency

1. Trivial FD: Y ⊆ X (e.g., RollNo,Name → Name).

2. Non-trivial FD: Y is not a subset of X.

3. Fully Functional: Removing any attribute from X breaks FD.

4. Partial: Part of composite key determines value.

5. Transitive: X → Y and Y → Z then X → Z.

---

5. Types of Normalization

• 1NF: No repeating groups; atomic values only.

• 2NF: In 1NF + No partial dependency.

• 3NF: In 2NF + No transitive dependency.

• BCNF: Stronger version of 3NF; every FD has superkey on LHS.

• 4NF: Removes multi-valued dependency.

---

6. Advantages of Normalization

• Removes redundancy

• Saves storage space

• Improves consistency

• Makes database flexible

Disadvantages:

• More joins required

• Complex queries

• Slower performance in some cases

Q1. Explain database anomalies with examples.

Answer:
Database anomalies are problems caused by redundancy in unnormalized relations:

• Insertion Anomaly: Can’t insert new data without other data.
  Example: Can’t add a new course unless a student takes it.

• Update Anomaly: Updating redundant data in one place but not everywhere causes inconsistency.
  Example: If department name is stored multiple times, updating one row only makes data inconsistent.

• Deletion Anomaly: Deleting a record may remove useful data unintentionally.
  Example: Deleting the last enrolled student also removes course info.

---

Q2. Define functional dependency and give example.

Answer:
Functional Dependency (FD) is a relationship between attributes in a relation.

• X → Y means value of X uniquely determines value of Y.
  Example:
  RollNo → StudentName (One roll number identifies one student).

---

Q3. Explain the rules of functional dependency.

Answer:

• Reflexivity: If Y ⊆ X, then X → Y.

• Augmentation: If X → Y, then XZ → YZ.

• Transitivity: If X → Y and Y → Z, then X → Z.

• Union: If X → Y and X → Z, then X → YZ.

• Decomposition: If X → YZ, then X → Y and X → Z.

---

Q4. Differentiate trivial and non-trivial dependency.

Answer:

• Trivial: Y ⊆ X. Example: (RollNo, Name) → Name.

• Non-Trivial: Y not a subset of X. Example: RollNo → StudentName.

---

Q5. Explain partial and fully functional dependency with example.

Answer:

• Partial Dependency: Non-prime attribute depends on part of composite key.
  Example: (RollNo, Course) → StudentName (Name depends only on RollNo).

• Fully Functional Dependency: Non-prime attribute depends on the whole key.

---

Q6. Explain transitive dependency with example.

Answer:
If X → Y and Y → Z, then X → Z is transitive dependency.
Example: RollNo → DeptNo and DeptNo → DeptName → RollNo → DeptName.

---

Q7. Explain First Normal Form (1NF) with example.

Answer:

• Rule: Values must be atomic (no repeating groups).
  Example:
  Before 1NF:
  | RollNo | Name | Subjects |
  |--------|------|----------|
  | 1 | Amit | DBMS, OS |

After 1NF:

RollNo	Name	Subject
1	Amit	DBMS
1	Amit	OS

---

Q8. Explain Second Normal Form (2NF) with example.

Answer:

• Rule: Must be in 1NF and no partial dependency.
  Example:
  Before 2NF: (RollNo, Course) → StudentName
  Fix: Separate Student(RollNo, Name), Enrollment(RollNo, Course).

---

Q9. Explain Third Normal Form (3NF) with example.

Answer:

• Rule: Must be in 2NF and no transitive dependency.
  Example: RollNo → DeptNo, DeptNo → DeptName.
  Fix: Create Student(RollNo, DeptNo), Department(DeptNo, DeptName).

---

Q10. Explain Boyce-Codd Normal Form (BCNF) with example.

Answer:

• Rule: For every FD X → Y, X must be a superkey.
  Example: In Staff table (StaffID, Subject, Room), anomalies may exist. Decompose into BCNF.

---

Q11. Explain Fourth Normal Form (4NF).

Answer:

• Rule: Must be in BCNF and remove multi-valued dependency.
  Example: If Student can have multiple Skills and Hobbies → separate them into two tables.

---

Q12. Compare 2NF and 3NF.

Answer:

• 2NF: Removes partial dependency.

• 3NF: Removes transitive dependency.
  3NF is stricter than 2NF.

---

Q13. Compare 3NF and BCNF.

Answer:

• 3NF: Allows non-prime attributes to depend on candidate keys.

• BCNF: Stronger, does not allow any dependency unless determinant is superkey.

---

Q14. List advantages of normalization.

Answer:

• Removes redundancy.

• Saves storage.

• Ensures data consistency.

• Easier updates.

---

Q15. List disadvantages of normalization.

Answer:

• More tables → complex queries.

• More joins → slower performance.

• Sometimes denormalization is preferred for efficiency.