GATE DA Database Management and Warehousing PYQs & Answers

Questions 1–10 of 22. Imported answers require independent review unless marked reviewed.

Let

be a relational schema with functional dependency set

Which of the following statements is correct?

Your answerChoose one option.

AD, ED and CD are the only candidate keys of R.

AD and ED are the only candidate keys of R.

A, E and CD are the only candidate keys of R.

A and CD are the only candidate keys of R.

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

Consider that the visualization of a 3-dimensional data cube is showing Sales Quantity for each combination of the attributes Product Type, Month and Country.

From this, if we want to further visualize the Sales Quantity for each combination of Product Type, Month and State, which of the following OLAP operations should be performed?

Your answerChoose one option.

Slicing

Dicing

Roll-up

Drill-down

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

Consider two relations r and s defined on the relational schemas R(A, B) and S(E, C), respectively. A is the primary key of R and E is a foreign key of S referencing A in R.

Which of the following operations will NEVER violate the foreign key constraint?

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

Inserting records into relation r

Deleting records from relation s

Deleting records from relation r

Inserting records into relation s

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

In a relational database, a B+ Tree Index is to be constructed for a relation on a key field. In a B+ Tree, a Node Pointer points to a sub-tree and a Data Record Pointer points to a block of database records. Let, Node size = 4096 bytes, Node Pointer size = 10 bytes, Search Key Field size = 11 bytes and Data Record Pointer size = 12 bytes. The maximum number of Node Pointers that can be present in a non-leaf node of the B+ Tree 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

Consider a B+ Tree where the maximum number of key values in each leaf node is 2 and the maximum number of pointers in each non-leaf node is 3. Let the content of the B+ Tree be as shown in the figure.

Which of the following options denotes the key value(s) stored in the root node after inserting a key value 3 in the given B+ Tree?

Diagram for Data Science and Artificial Intelligence question 41

Your answerChoose one option.

3 and 5

3, 5 and 8

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

Consider the given relations

and

. The relation

has three columns

and

. The relation

has three columns

and

. The relation

has two columns

and

Relation


P1	Q1	R1
P2	Q2	R2
P3	Q3	R2

Relation


P1	Q1	2
P1	Q2	5
P2	Q1	6
P3	Q3	1

Relation


P1	T1
P3	T2
P4	T3
P4	NULL

Consider the relational algebra expression

where

denotes natural join operation.

Which of the following options is the correct output for the given expression?

Your answerChoose one option.

Two rows (P1, R1, 2) and (P1, R1, 5)

Three rows (P1, R1, 2), (P1, R1, 5) and (P2, R2, 6)

One row (P1, R1, 2)

Zero rows

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

Consider the concept hierarchies as shown in the figure.

Which of the following options denotes the total number of possible data cubes from these concept hierarchies?

Diagram for Data Science and Artificial Intelligence question 43

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 a table Employee(EmpID, TeamID), where the column EmpID (ID of an employee) is the primary key. The column TeamID denotes the team ID of the team of which the employee is a member. TeamID is a NOT NULL column.

We want to display the size of the team (denoted as TeamSize) in which each employee is a member by using SQL. As an example, the desired output for the given Employee table is also shown in tabular form.

Employee

EmpID	TeamID
1	8
2	8
3	8
4	7
5	7
6	9

Output

EmpID	TeamSize
1	3
2	3
3	3
4	2
5	2
6	1

Which of the following is/are correct?

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

SELECT E.EmpID, B.TeamSize

FROM Employee AS E, (SELECT TeamID, COUNT(TeamID) AS TeamSize FROM Employee GROUP BY TeamID) AS B

WHERE E.TeamID = B.TeamID

SELECT A.EmpID, COUNT(B.TeamID) AS TeamSize

FROM Employee AS A, Employee AS B

WHERE A.TeamID = B.TeamID AND A.EmpID = B.EmpID

GROUP BY A.EmpID

SELECT B.EmpID, B.TeamSize

FROM (SELECT EmpID, COUNT(TeamID) AS TeamSize

FROM Employee GROUP BY EmpID) AS B

SELECT A.EmpID, B.TeamSize

FROM Employee AS A, (SELECT COUNT(TeamID) AS TeamSize FROM Employee GROUP BY TeamID) AS B

WHERE A.TeamID = B.TeamID

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

Let there be two relations

and

as shown.

has three columns

and

has two columns

and


P1	Q1	R1
P2	Q2	R2
P3	Q3	R2


P1	10
P1	15
P2	20
P3	1

Consider that the following tuple relational calculus expression is evaluated.

The number of tuples that will be returned 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

Let Account be a relation as shown.

AccNo	Balance
A1	5000
A2	5000
A3	1000
A4	1500
A5	1800

Consider the given SQL query.

SELECT AccNo FROM Account AS A

WHERE (SELECT COUNT(*) FROM Account AS B

WHERE A.Balance < B.Balance) >= (SELECT COUNT(*)

FROM Account AS C WHERE A.Balance > C.Balance)

The number of rows returned by the 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

GATE DA Database Management and Warehousing PYQs

How to practice Database Management and Warehousing

Question 17

Question 18

Question 26

Question 32

Question 41

Question 42

Question 43

Question 51

Question 59

Question 60