Friday, January 18, 2013

GATE Questions-DBMS-ER Diagram

Previous GATE questions with solutions on DBMS (ER Diagram) - CS/IT

Linked Answer Questions 1 & 2
1. Consider the following ER diagram.
The minimum number of tables needed to represent M, N, P, R1, R2 is
(a) 2 (b) 3 (c) 4 (d) 5

Ans: confused between option (a) & option (b)   { majority of the people refer option (a) as correct }
for option(a)
check explanation of question (2) of this link

for option (b)
All strong entities and weak entities will be converted into a table. Therefore we will have 3 tables: 
M (M1,M2,M3,P1)
P (P1,P2)
N (N1,N2,P1) =>N is a weak entity and it is modified to include the primary key of P (i.e. P1).

2. Which of the following is a correct attribute set for one of the tables for the correct answer to the above question?
(a) {M1, M2, M3, P1} (b) {M1, P1, N1, N2} (c) {M1, P1, N1}        d) {M1, P1}

Ans: option (a)

3. The following table has two attributes A and C where A is the primary key and C is the foreign key referencing A with on-delete cascade.
A   C
2   4
3   4
4   3
5   2
7   2
9   5
6   4
The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (2,4) is deleted is:
(a) (3,4) and (6,4)           (b) (5,2) and (7,2)
(c) (5,2), (7,2) and (9,5)           (d) (3,4), (4,3) and (6,4)

Ans: option (c)
Note that C is a foreign key, referring A with delete on cascade. Therefore when (2,4) is deleted, all the rows with value 2 in field C also should be deleted. Hence (5,2) and (7,2) is also deleted. Now rows with value 5 and 7 in field C also should be deleted. Therefore (9,5) is also deleted.

4. Let E1 and E2 be two entities in an ER diagram with simple single-valued attributes. R1 and R2 are two relationships between E1 and E2, where R1 is one-to-many and R2 is many-to-many. R1 and R2 do not have any attributes of their own. What is the minimum number of tables required to represent this situation in the relational model?
(a) 2 (b) 3 (c) 4 (d) 5

Ans: option (b)
Strong entities E1 and E2 should be converted into tables. For R1, which is one to many relation, there is no need of a separate table. Modify the "many" side to include the primary key of "one" side as foreign key. For R2, which is many to many relation, we require a separate table by including the primary key of E1 and E2 as foreign keys. Hence we require a minimum of 3 tables.

5.  Given the basic ER and relational models, which of the following is INCORRECT?
(a) An attribute of an entity can have more than one value
(b) An attribute of an entity can be composite
(c) In a row of a relational table, an attribute can have more than one value
(d) In a row of a relational table, an attribute can have exactly one value or a NULL value

Ans: option (c)
According to First Normal Form, an attribute cannot have multiple values. 

6. Consider a relational table with a single record for each registered student with the following attributes.
1. Registration_Number: Unique registration number of each registered student
2. UID: Unique Identity number, unique at the national level for each citizen
3. BankAccount_Number: Unique account number at the bank. A student can have multiple accounts or joint accounts. This attributes stores the primary account number
4. Name: Name of the Student
5. Hostel_Room: Room number of the hostel
Which of the following options is INCORRECT? 
(a) BankAccount_Number is a candidate key
(b) Registration_Number can be a primary key
(c) UID is a candidate key if all students are from the same country

(d) If S is a superkey such that S UID is NULL then S U UID is also a superkey

Ans: option (a)
Candidate Key: All unique value columns in a table are called candidate keys.
Its already specified in the question that "A student can have multiple accounts or joint accounts". Hence if two students have a joint account, BankAccount_Number will be the same both the students. Hence BankAccount_Number cannot be a candidate key.

1 comment:

  1. ans of q1 is given as a i.e 2 tables by official ans key by ugc net dec 13