GATE CSE topic:databases PYQs & Answers — Page 2

Questions 11–20 of 73. Imported answers require independent review unless marked reviewed.

An audit of a banking transactions system has found that on an earlier occasion, two joint holders of account

attempted simultaneous transfers of Rs. 1000 each from account

to account

. Both transactions read the same value, Rs. 1000, as the initial balance in

and were allowed to go through.

was credited Rs. 1000 twice.

was debited only once and ended up with a balance of Rs. 1000.

Which of the following properties is/are certain to have been violated by the system?

Your answerSelect all correct options. You can choose more than one.

Atomicity

Consistency

Isolation

Durability

You can attempt this question now. Sign in to view the answer key, explanations and discussion.Log in to check answer

Consider two relations describing teams and players in a sports league:

•

are team-id and team-name, respectively •

, and

denote player-id, player-name and the team-id of the player, respectively

Which ONE of the following tuple relational calculus queries returns the name of the players who play for the team having

as 'MI'?

Your answerChoose one option.

{

}

{

}

{

}

{

}

You can attempt this question now. Sign in to view the answer key, explanations and discussion.Log in to check answer

Consider the following relational schema along with all the functional dependencies that hold on them.

R1(A, B, C, D, E): {

}

R2(A, B, C, D): {

}

Which of the following statement(s) is/are TRUE?

Your answerSelect all correct options. You can choose more than one.

R1 is in 3NF

R2 is in 3NF

R1 is NOT in 3NF

R2 is NOT in 3NF

You can attempt this question now. Sign in to view the answer key, explanations and discussion.Log in to check answer

Consider a relational schema

, with functional dependencies

. The relation

is decomposed into two relations,

and

. Which of the following statement(s) is/are TRUE?

Your answerSelect all correct options. You can choose more than one.

The relation

is NOT in BCNF.

The relations

and

are in BCNF.

The decomposition constitutes a lossless join.

The relation

is NOT in 3NF.

You can attempt this question now. Sign in to view the answer key, explanations and discussion.Log in to check answer

Consider the database transactions T1 and T2, and data items X and Y. Which of the schedule(s) is/are conflict serializable?

Diagram for Computer Science and Information Technology question 53

Your answerSelect all correct options. You can choose more than one.

R₁(X), W₂(X), W₁(Y), W₂(Y), R₁(X), W₁(X), COMMIT(T₂), COMMIT(T₁)

W₂(X), R₁(X), W₂(Y), W₁(Y), R₁(X), COMMIT(T₂), W₁(X), COMMIT(T₁)

R₁(X), W₁(Y), W₂(X), W₂(Y), R₁(X), W₁(X), COMMIT(T₁), COMMIT(T₂)

W₂(X), R₁(X), W₁(Y), W₂(Y), R₁(X), COMMIT(T₂), W₁(X), COMMIT(T₁)

You can attempt this question now. Sign in to view the answer key, explanations and discussion.Log in to check answer

Consider the following relational schema:

Students (rollno: integer, name: string, age: integer, cgpa: real)

Courses (courseno: integer, name: string, credits: integer)

Enrolled (rollno: integer, courseno: integer, grade: string)

Which of the following options is/are correct SQL query/queries to retrieve the names of the students enrolled in course number (i.e., courseno) 1470?

Your answerSelect all correct options. You can choose more than one.

SELECT S.name FROM Students S WHERE EXISTS (SELECT * FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno);

SELECT S.name FROM Students S WHERE SIZEOF (SELECT * FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno) > 0;

SELECT S.name FROM Students S WHERE 0 < (SELECT COUNT(*) FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno);

SELECT S.name FROM Students S NATURAL JOIN Enrolled E WHERE E.courseno = 1470;

You can attempt this question now. Sign in to view the answer key, explanations and discussion.Log in to check answer

Consider the following database tables of a sports league.

player(pid,pname,age) coach(cid,cname) team(tid,tname,city,cid) members(pid,tid)

An instance of the table and an SQL query are given.

player: (pid, pname, age) 1, Jasprit, 31 2, Atharva, 24 3, Ishan, 26 4, Axar, 30

coach: (cid, cname) 101, Ricky 102, Mark 103, Trevor

team: (tid, tname, city, cid) 10, MI, Mumbai, 102 20, DC, Delhi, 101 30, PK, Mohali, 103

members: (pid, tid) 1, 10 2, 30 3, 10 4, 20

SELECT MIN(P.age) FROM player P WHERE P.pid IN ( SELECT M.pid FROM team T, coach C, members M WHERE C.cname = 'Mark' AND T.tid = C.cid AND M.tid = T.tid )

The value returned by the given SQL query is ______. (Answer in integer)

You can attempt this question now. Sign in to view the answer key, explanations and discussion.Log in to check answer

In a B⁺- tree where each node can hold at most four key values, a root to leaf path consists of the following nodes:

A = (49, 77, 83, -), B = (7, 19, 33, 44), C = (20*, 22*, 25*, 26*)

The *-marked keys signify that these are data entries in a leaf.

Assume that a pointer between keys

and

points to a subtree containing keys in

, and that when a leaf is created, the smallest key in it is copied up into its parent.

A record with key value 23 is inserted into the B⁺- tree.

The smallest key value in the parent of the leaf that contains 25* is __________. (Answer in integer)

You can attempt this question now. Sign in to view the answer key, explanations and discussion.Log in to check answer

Once the DBMS informs the user that a transaction has been successfully completed, its effect should persist even if the system crashes before all its changes are reflected on disk. This property is called

Your answerChoose one option.

durability

atomicity

consistency

isolation

You can attempt this question now. Sign in to view the answer key, explanations and discussion.Log in to check answer

Let S be the specification: "Instructors teach courses. Students register for courses. Courses are allocated classrooms. Instructors guide students." Which one of the following ER diagrams CORRECTLY represents S?

Diagram for Computer Science and Information Technology question 20

Your answerChoose one option.

(i)

(ii)

(iii)

(iv)

You can attempt this question now. Sign in to view the answer key, explanations and discussion.Log in to check answer

GATE CSE topic:databases PYQs

Question 27

Question 39

Question 46

Question 47

Question 53

Question 54

Question 55

Question 57

Question 19

Question 20