Skip to main content

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.


Popular posts from this blog

ORACLE 9i practice solutions

Created by BCL easyConverter SDK 3 (HTML Version)

Zoho Puzzle Questions With Answers

Measuring Time Logic Puzzle You are given with two ropes with variable width. However if we start burning both the ropes, they will burn at exactly same time i.e. an hour. The ropes are non-homogeneous in nature. You are asked to measure 45 minutes by using these two ropes.

How can you do it?

Please note that you can’t break the rope in half as it is being clearly stated that the ropes are non-homogeneous in nature.
Answer & Explanation Solution: 45 minutes

Explanation :
All you have to do is burn the first rope from both the ends and the second rope from one end only simultaneously. The first rope will burn in 30 minutes (half of an hour since we burned from both sides) while the other rope would have burnt half. At this moment, light the second rope from the other end as well. Where, the second rope would have taken half an hour more to burn completely, it will take just 15 minutes as we have lit it from the other end too.

Thus you have successfully calculated 30+15 = 45 minutes …

Hackerrank > SQL > Basic Select

01-Select All
Given a City table, whose fields are described as +-------------+----------+ | Field       | Type     | +-------------+----------+ | ID          | int(11)  | | Name        | char(35) | | CountryCode | char(3)  | | District    | char(20) | | Population  | int(11)  | +-------------+----------+
write a query that will fetch all columns for every row in the table.

My Solution
02-Select by ID
Given a City table, whose fields are described as