Consider a relation geq which represents “greater than or equal to” — that is,…

2001

Consider a relation geq which represents “greater than or equal to” — that is, a pair (x, y) belongs to geq only if y ≥ x. The relation is stored in a table where lb (lower bound) is the primary key and ub (upper bound) is a foreign key referencing lb in the same table, with ON DELETE CASCADE:

create table geq
(
  lb integer not null,
  ub integer not null,
  primary key (lb),
  foreign key (ub) references geq (lb) on delete cascade
)

Which of the following is possible if a tuple (x, y) is deleted?

Answer: C. A tuple (z, w) with z < x is deleted — A self-referencing foreign key ties two rows of the same table together: the value in the FK column of one row must match the value in the PRIMARY KEY column…

  1. A.

    A tuple (z, w) with z > y is deleted

  2. B.

    A tuple (z, w) with z > x is deleted

  3. C.

    A tuple (z, w) with z < x is deleted

  4. D.

    The deletion of (x, y) is prohibited

Attempted by 61 students.

Show answer & explanation

Correct answer: C

A self-referencing foreign key ties two rows of the same table together: the value in the FK column of one row must match the value in the PRIMARY KEY column of some row (possibly the same row). Because the primary-key column is unique, at most one row can hold any given primary-key value. ON DELETE CASCADE then means that when a row's primary-key value is removed, every row whose FK column currently equals that value is removed too.

  1. In table geq, lb is the primary key and ub is a foreign key referencing lb in the same table, so a tuple (a, b) stores a in lb and b in ub; by the stated definition of geq, every such tuple satisfies b ≥ a — a property of the relation's data, not something enforced by the DDL.

  2. Deleting the tuple (x, y) removes the row whose lb = x.

  3. ON DELETE CASCADE then fires for every remaining row whose ub column equals x — call such a row (z, w), so w = x for that row.

  4. Because lb is the primary key, no surviving row can also have lb = x — the deleted row was the only one.

  5. By the same geq invariant, w ≥ z for this row, and here w = x, so z ≤ x.

  6. Combined with the previous step (z cannot equal x), this forces z < x.

Cross-check with a concrete instance: rows (6, 6), (4, 6), (2, 4), (1, 4) — each satisfies the FK match (every ub value equals some row's lb, including (6, 6) referencing itself) and the invariant b ≥ a. Deleting (4, 6) removes lb = 4; the surviving rows whose ub = 4 are (1, 4) and (2, 4), giving z = 1 or z = 2 — both strictly less than x = 4, confirming the derivation above.

So deleting (x, y) can indeed force the deletion of a tuple (z, w) with z < x — this is the option the schema actually supports.

Explore the full course: Iocl Engineers Officers Grade A Paper 2

Loading lesson…