FOR FREE MATERIALS

GATE and NET questions on ER Diagram

Q. UGC NET 2017

Integrity constraints ensure that changes made to the database by authorized users do not result in loss of data consistency. Which of the following statement(s) is (are) true with respect to the examples of integrity constraints?

(i) An instructor Id. No. cannot be null, provided Instructor Id. No. being the primary key.

(ii) No two citizens have the same Aadhar-Id.

(iii) The budget of a company must be zero.

(a) (i),(ii) and (iii) are true.

(b) (i) false, (ii) and (iii) are true.

(c) (i), (ii) are true and (iii) false.

(d) (i), (ii) and (iii) are false.

Solution:

Statement (i) is true because the primary key must be not null- Entity Integrity Constraint.

Statement (ii) is also true because primary has no duplicate value - Entity Integrity Constraint.

For any details follow: Relational constraints or Data Integrity

Statement (iii) is false because it is an irreverent statement.

Q. UGC NET 2017 Question 9

Let pk(R) denotes the primary key of the relation R. A many to one relationship that exists between two relations R1 and R2 can be expressed as follows:

(a) pk(R2) → pk(R1)

(b) pk(R1) → pk(R2)

(c) pk(R2) → R1 ∩ R2

(d) pk(R1) → R1 ∩ R2

Answer: 

The question tells us to make a relationship with a foreign key. Now the question is from which table we create the foreign key and in which table we put the foreign key.

We draw the ER diagram as per the question:

Now, we always create the foreign key from the table with cardinality many (R2) and put it to the table where cardinality 1 (R1).

Arrow is referencing that from which table we create the foreign key. 

So, the correct relation is pk(R1) → pk(R2).

Option (b) is the correct answer.

Q: Gate – 1997

Let R (a, b, c) and S (d, e, f) be two relations ‘d’ is a foreign key of S that refers to the primary key of ‘R’. Consider the following four operations on ‘R’ and ‘S’.

I. Insert into ‘R’

II. Insert into ‘S’

III. Delete from ‘R’

IV. Delete from ‘S’

Which of these can cause violation of referential integrity constraint?

A. Both I and IV

B. Both II and III

C. All of these

D. None of these

Answer:

We draw the table as per the question:

a. Insert into ‘R’ not make any problem because it is father table.

b. Insert into ‘S’ – can violet integrity constraint because at foreign key, we can’t insert any value which is not present at R (primary key).

c. Delete from ‘R’ – we can’t delete the primary key of R, which is referred to as foreign key of S. So, it is also violet integrity constraint.

d. Delete from ‘S’ – it is the independent table, not make any problem if do delete operation on S.

So, option B (Both II and III) is the correct answer.