GATE DA Database Management and Warehousing PYQs & Answers — Page 2

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

Consider an ER model with the entities E1 (A₁₁, A₁₂, A₁₃) and E2 (A₂₁, A₂₂, A₂₃), where, A₁₁, A₁₂, A₁₃ are the attributes of E1, and A₂₁, A₂₂, A₂₃ are the attributes of E2. Let A₂₂ be a multi-valued attribute. A₁₁ and A₂₁ are the primary keys of E1 and E2, respectively.

Let R12 be a many-to-many relationship between E1 and E2. Participation of both E1 and E2 in R12 is total.

The minimum number of relations required to convert the ER model into relational model (assuming there is no other functional dependency) where each relation is in third normal form (3NF) 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

If a relational decomposition is not dependency-preserving, which one of the following relational operators will be executed more frequently in order to maintain the dependencies?

Your answerChoose one option.

Selection

Projection

Join

Set union

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

Consider the following three relations:

Car (model, year, serial, color)

Make (maker, model)

Own (owner, serial)

A tuple in Car represents a specific car of a given model, made in a given year, with a serial number and a color. A tuple in Make specifies that a maker company makes cars of a certain model. A tuple in Own specifies that an owner owns the car with a given serial number. Keys are underlined; (owner, serial) together form key for Own. (

denotes natural join)

Which one of the following options describes what the above expression computes?

Your answerChoose one option.

All owners of a red car, a car made by ABC, or a red car made by ABC

All owners of more than one car, where at least one car is red and made by ABC

All owners of a red car made by ABC

All red cars made by ABC

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

On a relation named Loan of a bank:

loan_number	branch_name	amount
L11	Banjara Hills	9000
L14	Kondapur	5000
L15	SR Nagar	4000
L22	SR Nagar	2500
L23	Balanagar	8000
L25	Kondapur	7000
L19	SR Nagar	6500

the following SQL query is executed.

SELECT L1.loan_number

FROM Loan L1

WHERE L1.amount > (SELECT MAX (L2.amount)

FROM Loan L2

WHERE L2.branch_name = 'SR Nagar');

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

Consider a fact table in an OLAP application: Facts (D1, D2, val), where D1 and D2 are its dimension attributes and val is a dependent attribute. Suppose attribute D1 takes 3 values and D2 takes 2 values, and all combinations of these values are present in the table Facts. How many tuples are there in the result of the following query?

SELECT D1, D2, sum(val)

FROM Facts

GROUP BY CUBE (D1, D2);

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 two relations, named Customer and Person, in a database:

Person ( aadhaar CHAR(12) PRIMARY KEY, name VARCHAR(32));

Customer ( name VARCHAR(32), email VARCHAR(32) PRIMARY KEY, phone CHAR(10), aadhaar CHAR(12), FOREIGN KEY (aadhaar) REFERENCES Person(aadhaar));

Which of the following statements is/are correct?

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

aadhaar is a candidate key in the Customer relation

phone can be NULL in the Customer relation

aadhaar is a candidate key in the Person relation

aadhaar can be NULL in the Person relation

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

Consider a database relation R with attributes ABCDEFG, and having the following functional dependencies:

A → BCE E → DG BC → A

Which of the following statements is/are correct?

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

A is the only candidate key of R

A, BC are the candidate keys of R

A, BC, E are the candidate keys of R

Relation R is not in Boyce-Codd Normal Form (BCNF)

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

Consider the following tables, Loan and Borrower, of a bank.

Loan:

loan_num	branch_name	amount
L11	Banjara Hills	9000
L14	Kondapur	5000
L15	SR Nagar	4000
L22	SR Nagar	2500
L23	Balanagar	8000
L25	Kondapur	7000
L19	SR Nagar	6500

Borrower:

customer_name	loan_num
And	L11
Karteek	L11
Karteek	L14
Ankita	L15
Gopal	L19
Karteek	L22
Karteek	L23
Sunil	L23
Sunil	L25

Query:

where

denotes natural join.

The number of tuples returned by the above relational algebra 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

Consider a database that includes the following relations:

Defender(name, rating, side, goals)

Forward(name, rating, assists, goals)

Team(name, club, price)

Which ONE of the following relational algebra expressions checks that every name occurring in Team appears in either Defender or Forward, where

denotes the empty set?

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 two tables named Raider and Team in a relational database maintained by a Kabadi league. The attribute ID in table Team references the primary key of the Raider table, ID.

Raider:

ID	Name	Raids	RaidPoints
1	Arjun	200	250
2	Ankush	190	219
3	Sunil	150	200
4	Reza	150	190
5	Pratam	175	220
6	Gopal	193	215

Team:

City	ID	BidPoints
Jaipur	2	200
Patna	3	195
Hyderabad	5	175
Jaipur	1	250
Patna	4	200
Jaipur	6	200

The SQL query described below is executed on this database:

SELECT * FROM Raider, Team WHERE Raider.ID=Team.ID AND City="Jaipur" AND RaidPoints > 200;

The number of rows returned by this query is _____.

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

Question 61

Question 16

Question 17

Question 33

Question 46

Question 56

Question 57

Question 62

Question 26

Question 31