Database-Management-System

Question 6

Consider two tables in a relational database with columns and rows as follows:

Table: Student
ROLL_NO	  NAME	DEPT_ID
   1	  ABC	   1
   2	  DEF	   1
   3	  GHI	   2
   4	  JKL	   3
Table: Department
DEPT_ID	DEPT_NAME
   1	   A
   2	   B
   3	   C

Roll_no is the primary key of the Student table, Dept_id is the primary key of the Department table and Student.Dept_id is a foreign key from Department.Dept_id.
What will happen if we try to execute the following two SQL statements?

(i)  update Student set Dept_id = Null 
     where Roll_on = 1
(ii) update Department set Dept_id = Null 
     where Dept_id = 1

A	Both (i) and (ii) will fail
B	(i) will fail but (ii) will succeed
C	(i) will succeed but (ii) will fail
D	Both (i) and (ii) will succeed

Database-Management-SystemER-ModelGATE 2004-IT

Question 6 Explanation:

Here in (i), when we update in Student table, Dept_id = Null, then it will not cause any problem to referenced table.
But in (ii) if we set in Department table, Dept_id = Null, then it will produce inconsistency because in Student table we will still have the tuples containing the Dept_id = 1.

Correct Answer: C

Question 6 Explanation:

Here in (i), when we update in Student table, Dept_id = Null, then it will not cause any problem to referenced table.
But in (ii) if we set in Department table, Dept_id = Null, then it will produce inconsistency because in Student table we will still have the tuples containing the Dept_id = 1.