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

CAP570 · Unit 2: E-R modeling

Constraints on relations and types of keys

10 practice questions · in the MTE and ETE syllabus

Practise this topicPractise this topic

Key points

  • Candidate key = minimal superkey
  • Entity integrity forbids null primary keys
  • Foreign keys must match a parent key or be null
  • CASCADE removes or updates dependent rows

Constraints keep data valid.

  • Domain constraint: a value must be of the declared type and range.
  • NOT NULL, UNIQUE, CHECK, DEFAULT: column rules.
  • Entity integrity: no primary key value may be null.
  • Referential integrity: a foreign key value must match an existing key in the parent table or be null.

Keys: a superkey is any set of attributes that identifies rows. A candidate key is a minimal superkey. The primary key is the chosen candidate key, and the others are alternate keys. A foreign key refers to a key of another table. A composite key has more than one attribute.

Referential actions on delete or update include CASCADE (propagate to child rows), SET NULL, and RESTRICT or NO ACTION (block the change).

Example: with candidate keys AB and AC in R(A, B, C, D), ABC is a superkey but not a candidate key because it is not minimal.

Exam trap: a primary key cannot be null, but a unique column usually may be.

Try two questions

Question 1 · Tier 2, Understand

Which integrity rule says that no primary key value may be null?

  1. Entity integrity
  2. Referential integrity
  3. Domain integrity
  4. User-defined integrity
Show the answer

A. Entity integrity requires every row to be identifiable, so primary key columns cannot be null.

Why not B: That rule requires foreign keys to match an existing key or be null.

Why not C: That rule limits values to the defined type and range.

Why not D: That covers custom business rules.

Question 2 · Tier 4, Analyse

Student(RollNo, DeptID) has a foreign key DeptID referring to Dept(DeptID) with ON DELETE CASCADE. What happens when a department row is deleted?

  1. The deletion is blocked until all students are removed
  2. All students of that department are deleted as well
  3. The students keep the old DeptID value
  4. Only the first matching student is deleted
Show the answer

B. CASCADE propagates the delete to every child row that references the deleted parent.

Why not A: That is the RESTRICT or NO ACTION behaviour.

Why not C: That would break referential integrity.

Why not D: CASCADE removes every referencing row.