Gyanrush
Start freeOpen Gyanrush
Start freeOpen Gyanrush
  1. LPU MCA Semester 1
  2. CAP570
  3. Unit 3

CAP570 · Unit 3: Database normalisation

Functional dependencies and anomalies

10 practice questions · in the MTE and ETE syllabus

Practise this topicPractise this topic

Key points

  • Closure is built by applying FDs until nothing new is added
  • Transitivity chains two dependencies
  • Insertion, deletion and update are the three anomalies
  • A superkey has a closure containing all attributes

A functional dependency X → Y means that rows agreeing on X must agree on Y. It is trivial when Y is a subset of X.

The closure X+ is the set of attributes determined by X. Compute it by repeatedly adding the right side of any FD whose left side is already inside the set. X is a superkey when X+ covers every attribute. Armstrong's rules give new FDs: reflexivity, augmentation and transitivity (X → Y and Y → Z give X → Z), with union and decomposition as consequences.

Example: with AB → C and C → D, closure of AB is ABCD, so AB → D holds.

Poor design causes anomalies from redundancy.

  • Insertion anomaly: a fact cannot be added without another fact.
  • Deletion anomaly: removing a row loses an unrelated fact.
  • Update anomaly: repeated data changed in only some places becomes inconsistent.

Exam trap: X → Y does not give Y → X, and a left side that reaches only some attributes is not a key.

Try two questions

Question 1 · Tier 2, Understand

A student's address is stored in every enrolment row, and a change is applied to only some of those rows. Which anomaly results?

  1. Insertion anomaly
  2. Deletion anomaly
  3. Joining anomaly
  4. Update anomaly
Show the answer

D. Repeated data updated in only some places leaves inconsistent copies.

Why not A: Nothing needed to be inserted in this situation.

Why not B: No row was deleted in this situation.

Why not C: This is not a standard anomaly name.

Question 2 · Tier 4, Analyse

Which of the following is a trivial functional dependency?

  1. A → AB
  2. A → B
  3. AB → A
  4. B → AC
Show the answer

C. A dependency is trivial when the right side is a subset of the left side.

Why not A: This adds B on the right and is not trivial.

Why not B: B is not contained in A, so it is not trivial.

Why not D: The right side is not contained in the left side.