Duration: 4 hours (Lecture: 4 hours, Lab: 4 hours, split across two sessions)

Syllabus Topics:

• Lecture:
  • Data Redundancy
  • Data Anomalies
  • Functional Dependency
  • Normalization, Need for Normalization
  • Normal Forms (1st NF, 2nd NF, 3rd NF, BCNF) with examples
  • Introduction to 4th and 5th NF
  • DML (INSERT/UPDATE/DELETE)
• Lab (2 hours per session, total 4 hours):
  • DML (INSERT/UPDATE/DELETE)
  • TRUNCATE

Lecture Notes (In-Depth)

1. Data Redundancy

• Definition: Data redundancy occurs when the same data is stored in multiple places within a database, leading to inefficiency and potential inconsistency.
• Issues:
  • Storage Waste: Duplicate data increases storage requirements.
  • Inconsistency: Changes in one copy of data may not propagate to others, causing discrepancies.
  • Example: Storing a customer’s address in both orders and customers tables. If the address changes in one table but not the other, the data becomes inconsistent.
• Solution: Normalization reduces redundancy by organizing data efficiently.

2. Data Anomalies

Data anomalies are problems that arise during data manipulation (insertion, update, deletion) in a poorly designed database, often due to redundancy.

• Insertion Anomaly:
  • Occurs when you cannot insert valid data due to missing attributes.
  • Example: A table Student_Courses with columns student_id, student_name, course_id, course_name. You cannot add a new course without assigning a student, as student_id and student_name are required.
• Update Anomaly:
  • Occurs when updating redundant data leads to inconsistency.
  • Example: If student_name is stored in multiple rows of Student_Courses and you update the name in one row but not others, the data becomes inconsistent.
• Deletion Anomaly:
  • Occurs when deleting data removes unintended information.
  • Example: Deleting the last course a student is enrolled in from Student_Courses also deletes the student’s name, losing critical data.
• Solution: Normalization eliminates anomalies by ensuring data dependencies are logical and minimal.

3. Functional Dependency

• Definition: A functional dependency (FD) exists when the value of one attribute determines the value of another attribute in a table. Denoted as X → Y, where X determines Y.
  • Example: In a Students table, student_id → name means each student_id uniquely determines a name.
• Types:
  • Full Dependency: Y depends on the entire X (e.g., {student_id, course_id} → grade).
  • Partial Dependency: Y depends on part of a composite key (e.g., {student_id, course_id} → student_name, where student_name depends only on student_id).
  • Transitive Dependency: X → Y and Y → Z, so X → Z indirectly (e.g., student_id → dept_id, dept_id → dept_name).
• Role in Normalization: Functional dependencies guide the decomposition of tables to eliminate redundancy and anomalies.