Functional Dependencies & Anomalies

9. Practice Problems

Most problems here are pen-and-paper. Two also include runnable SQL to demonstrate the anomaly.

1. Given R(A, B, C, D) with FDs A → B, B → C, compute A⁺.
   A⁺ বের করুন।
   ✨ Show Answer

   Answer: Start {A}. Apply A → B → {A, B}. Apply B → C → {A, B, C}. Nothing new can be derived → A⁺ = {A, B, C}. Note that D is not in the closure, so A alone is not a superkey.

2. Same schema. Is {A, D} a superkey? Is it a candidate key?
   {A, D} কি superkey? Candidate key?
   ✨ Show Answer

   Answer: {A, D}⁺ = {A, B, C, D} = all of R, so yes — superkey. Removing D leaves A⁺ = {A, B, C} ≠ R, and removing A leaves D⁺ = {D} ≠ R. Neither subset is a superkey, so {A, D} is minimal → candidate key.

3. Run the bad enrollment table from §4. Then add a new student to CSE331. Notice that you are forced to repeat all course/instructor information. Show the resulting table.
   নতুন একজন student insert করে দেখুন কতগুলো column আবার repeat হচ্ছে।
   ✨ Show Answer

   ans3.sql

   INSERT INTO enrollment VALUES
    (104, 'Nadia', 'BBA',
     'CSE331', 'Database Systems', 'Dr Karim', 'karim@univ.bd', 'A-');
   
   SELECT student_roll, course_id, course_title, instructor
   FROM enrollment;

   Run Copy

   Notice: the same course_title and instructor are stored multiple times — pure redundancy, the seed of the update anomaly.

4. Demonstrate the update anomaly: change Dr Karim's email and verify that several rows must change. What if you forget one?
   Update anomaly demo।
   ✨ Show Answer

   ans4.sql

   -- Forget the WHERE? You wipe a column. Forget a row? Inconsistent state.
   UPDATE enrollment
   SET    instructor_email = 'mkarim@univ.bd'
   WHERE  instructor = 'Dr Karim';
   
   SELECT DISTINCT instructor, instructor_email FROM enrollment;

   Run Copy

5. In the enrollment table, identify all non-trivial FDs that involve only a strict subset of the primary key on the left.
   Primary key-এর subset ব্যবহার করে যেসব non-trivial FD আছে — সেগুলো লিখুন।
   ✨ Show Answer

   Answer:

   • student_roll → student_name, student_dept (uses only part of the PK).
   • course_id → course_title, instructor, instructor_email (uses only part of the PK).
   • instructor → instructor_email (uses no part of the PK — even worse).

   Together these three are exactly the FDs that 2NF (next module) and 3NF will eliminate.

6. Apply Armstrong's axioms: from A → B and A → C, derive A → BC. Which axiom or theorem did you use?
   Armstrong-এর কোন নিয়মে?
   ✨ Show Answer

   Answer: The union rule (a derived theorem). One way to derive it from the three primitive axioms: A → B ⇒ A → AB by augmentation. A → C ⇒ AB → BC by augmentation. Then transitivity: A → AB and AB → BC ⇒ A → BC. ∎

7. In one sentence, explain why FDs are properties of the schema, not of today's data.
   FD শুধু আজকের data-এর বৈশিষ্ট্য নয় কেন?
   ✨ Show Answer

   Answer: An FD must hold for every conceivable instance the schema is allowed to take. Today's data may "look like" an FD purely by coincidence — but a single legal future row that breaks the pattern would prove it was never a real FD.