GATE CSE DBMS PYQ

GATE CSE DBMS PYQ

Q1➡ | GATE 2021 Set-1
Suppose a database system crashes again while recovering from a previous crash. Assume checkpointing is not done by the database either during the transactions or during recovery. Which of the following statements is/are correct?
i ➥ All the transactions that are already undone and redone will not be recovered again.
ii ➥ The same undo and redo list will be used while recovering again.
iii ➥ The database will become inconsistent.
iv ➥ The system cannot recover any further.

Show Answer With Best Explanation

Answer: II
Explanation: Upload Soon

More DiscussionExplanation On YouTubeTransaction management Help-Line

Q2➡ | GATE 2021 Set-1
A relation r(A, B) in a relational database has 1200 tuples. The attribute A has integer values ranging from 6 to 20, and the attribute B has integer values ranging from 1 to 20. Assume that the attributes A and B are independently distributed.

Show Answer With Best Explanation

Answer: 820
Explanation: Upload Soon

More DiscussionExplanation On YouTubeRelational Database Help-Line

Q3➡ | GATE 2021 Set-1
The following relation records the age of 500 employees of a company, where empNo (indicating the employee number) is the key:


Consider the following relational algebra expression:
The following relation records the age of 500 employees of a company, where empNo (indicating the employee number) is the key:    Consider the following relational algebra expression:
What does the above expression generate?
i ➥ Employee numbers of only those employees whose age is more than the age of exactly one other employee
ii ➥ Employee numbers of all employees whose age is not the minimum.
iii ➥ Employee numbers of all employees whose age is the minimum.
iv ➥ Employee numbers of only those employees whose age is the maximum.

Show Answer With Best Explanation

Answer: II
Explanation: Upload Soon

More DiscussionExplanation On YouTubeRelational Algebra Help-Line

Q4➡ | GATE 2021 Set-1
Let ri(z) and wi(z) denote read and write operations respectively on a data item z by a transaction Ti. Consider the following two schedules.
S1: r1(x) r1(y) r2(x) r2(y) w2(y) w1(x)
S2: r1(x) r2(x) r2(y) w2(y) r1(y) w1(x)
Which one of the following options is correct?
i ➥ Both S1 and S2 are conflict serializable.
ii ➥ Neither S1 nor S2 is conflict serializable.
iii ➥ S1 is conflict serializable, and S2 is not conflict serializable.
iv ➥ S1 is not conflict serializable, and S2 is conflict serializable.

Show Answer With Best Explanation

Answer: IV
Explanation: Upload Soon

More DiscussionExplanation On YouTube Transaction management Help-Line

Q5➡ | GATE 2021 Set-1
Consider the relation R(P, Q, S, T, X, Y, Z, W) with the following functional dependencies

Consider the decomposition of the relation R into the consistent relations according to the following two decomposition schemes.
D1: R=[(P,Q,S,T); (P,T,X); (Q,Y); (Y,Z,W)]
D2: R=[(P,Q,S);(T,X);(Q,Y);(Y,Z,W)]

Which one of the following options is correct?
i ➥ Both D1 and D2are lossy decompositions.
ii ➥ D1 is a lossless decomposition, but D2 is a lossy decomposition.
iii ➥ D1 is a lossy decomposition, but D2 is a lossless decomposition.
iv ➥ Both D1 and D2 are lossless decompositions.

Show Answer With Best Explanation

Answer: II
Explanation: Upload Soon

More DiscussionExplanation On YouTubeNormalization Help-Line

Q6➡ | GATE 2021 Set-2
Consider the following statements S1 and S2 about the relational data model:
S1: A relation scheme can have at most one foreign key.
S2: A foreign key in a relation schema R cannot be used to refer to tuples of R.
Which one of the following choices is correct?
i ➥ Both S1 and S2 are false.
ii ➥ S1 is false and S2 is true.
iii ➥ S1 is true and S2 is false.
iv ➥ Both S1 and S2 are true.

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTubeRelational Model Help-Line

Q7➡ | GATE 2021 Set-2
A data consisting of 1,50,000 student-records is stored on a hard disk with block size of 4096 bytes. The data file is sorted on the primary key RollNo. The size of a record pointer for this disk is 7 bytes. Each student-record has a candidate key attribute called ANum of size 12 bytes. Suppose an index file with records consisting of two fields, ANum value and the record pointer to the corresponding student record, is built and stored on the same disk. Assume that the records of data file and index file are not split across disk blocks. The number of blocks in the index file is _______.

Show Answer With Best Explanation

Answer: 698
Explanation: Upload Soon

More DiscussionExplanation On YouTubeFile Structure Help-Line

Q8➡ | GATE 2021 Set-2
Let S be the following schedule of operations of three transactions T1, T2 and T3 in a relational database system:
R2(Y), R1(X), R3(Z), R1(Y), W1(X), R2(Z), W2(Y), R3(X), W3(Z)

Consider the statements P and Q below:
P: S is conflict-serializable.
Q: If T3 commits before T1 finishes, then S is recoverable.
Which one of the following choices is correct?
i ➥ P is false and Q is true.
ii ➥ Both P and Q are false.
iii ➥ P is true and Q is false.
iv ➥ Both P and Q are true.

Show Answer With Best Explanation

Answer: III
Explanation: Upload Soon

More DiscussionExplanation On YouTubeTransaction ManagementHelp-Line

Q9➡ | GATE 2021 Set-2
The relation scheme given below is used to store information about the employees of a company, where empId is the key and deptId indicates the department to which the employee is assigned. Each employee is assigned to exactly one department.
emp(empId, name, gender, salary, deptId)

Consider the following SQL query:
select deptId, count (*)
from emp
where gender = “female” and salary > (select avg(salary) from emp)
group by deptId;
The above query gives, for each department in the company, the number of female employees whose salary is greater than the average salary of
i ➥ female employees in the department
ii ➥ employees in the company
iii ➥ employees in the department
iv ➥ female employees in the company

Show Answer With Best Explanation

Answer: II
Explanation: Upload Soon

More DiscussionExplanation On YouTubeSQL Help-Line

Q10➡ | GATE 2021 Set-2
Suppose the following functional dependencies hold on a relation U with attributes P, Q, R, S, and T:
P⟶ QR
RS⟶ T
Which of the following functional dependencies can be inferred from the above functional dependencies?
i ➥ R ⟶ T
ii ➥ PS ⟶ Q
iii ➥ P ⟶ R
iv ➥ PS ⟶ T

Show Answer With Best Explanation

Answer: II, III, IV
Explanation: Upload Soon

More DiscussionExplanation On YouTubeNormalization Help-Line

Q11➡ | GATE 2020
Which one of the following is used to represent the supporting many-one relationships of a weak entity set in an entity-relationship diagram?
i ➥ Diamonds with double/bold border
ii ➥ Ovals with double/bold border
iii ➥ Ovals that contain underlined identifiers
iv ➥ Rectangles with double/bold border

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTubeER Model Help-Line

Q12➡ | GATE 2020
Consider a relational database containing the following schemas.
The primary key of each table is indicated by underlying the constituent fields.

SELECT s.sno, s.sname
FROM Suppliers s, Catalogue c
WHERE s.sno = c.sno AND
Cost > (SELECT AVG (cost)
FROM Catalogue
WHERE pno = ‘P4’
GROUP BY pno);

The number of rows returned by the above SQL query is
i ➥ 2
ii ➥ 0
iii ➥ 4
iv ➥ 5

Show Answer With Best Explanation

Answer: III
Explanation: Upload Soon

More DiscussionExplanation On YouTubeSQL Help-Line

Q13➡ | GATE 2020
Consider a schedule of transactions T1 and T2:

Here, RX stands for “Read(X)” and WX stands for “Write(X)”. Which one of the following schedules is conflict equivalent to the above schedule?
i ➥
ii ➥
iii ➥
iv ➥

Show Answer With Best Explanation

Answer: i
Explanation: Upload Soon

More DiscussionExplanation On YouTubeTransaction ManagementHelp-Line

Q14➡ | GATE 2020
Consider a relational table R that is in 3NF, but not in BCNF. Which one of the following statements is TRUE?
i ➥ R has a nontrivial functional dependency X→A, where X is not a superkey and A is a prime attribute.
ii ➥ R has a nontrivial functional dependency X→A, where X is not a superkey and A is a non-prime attribute and X is a proper subset of some key.
iii ➥ A cell in R holds a set instead of an atomic value.
iv ➥ R has a nontrivial functional dependency X→A, where X is not a superkey and A is a non-prime attribute and X is not a proper subset of any key.

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTubeNormalization Help-Line

Q15➡ | GATE 2020
Consider a database implemented using B+ tree for file indexing and installed on a disk drive with block size of 4 KB. The size of search key is 12 bytes and the size of tree/disk pointer is 8 bytes. Assume that the database has one million records. Also assume that no node of the B+ tree and no records are present initially in main memory. Consider that each record fits into one disk block. The minimum number of disk accesses required to retrieve any record in the database is ________.

Show Answer With Best Explanation

Answer: 4
Explanation: Upload Soon

More DiscussionExplanation On YouTubeFile Structue Help-Line

Q16➡ | GATE 2019
Consider the following two statements about database transaction schedules:
I. Strict two-phase locking protocol generates conflict serializable schedules that are also recoverable.
II. Timestamp-ordering concurrency control protocol with Thomas Write Rule can generate view serializable schedules that are not conflict serializable.
Which of the above statements is/are TRUE?
i ➥ II only
ii ➥ I only
iii ➥ Neither I nor II
iv ➥ Both I and II

Show Answer With Best Explanation

Answer: IV
Explanation: Upload Soon

More DiscussionExplanation On YouTube Transaction Management Help-Line

Q17➡ | GATE 2019
Which one of the following statements is NOT correct about the B+ tree data structure used for creating an index of a relational database table?
i ➥ Non-leaf nodes have pointers to data records
ii ➥ B+ Tree is a height-balanced tree
iii ➥ Each leaf node has a pointer to the next leaf node
iv ➥ Key values in each node are kept in sorted order

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTubeFile Structures Help-Line

Q18➡ | GATE 2019
Let the set of functional dependencies F = {QR → S, R → P, S → Q} hold on a relation schema X = (PQRS). X is not in BCNF. Suppose X is decomposed into two schemas Y and Z, where Y = (PR) and Z = (QRS).

Consider the two statements given below:

I. Both Y and Z are in BCNF
II. Decomposition of X into Y and Z is dependency preserving and lossless

Which of the above statements is/are correct?
i ➥ Both I and II
ii ➥ I only
iii ➥ II only
iv ➥ Neither I nor II

Show Answer With Best Explanation

Answer: III
Explanation: Upload Soon

More DiscussionExplanation On YouTubeNormalization Help-Line

Q19➡ | GATE 2019
Consider the following relations P(X,Y,Z), Q(X,Y,T) and R(Y,V).

How many tuples will be returned by the following relational algebra query?

Show Answer With Best Explanation

Answer: 1
Explanation: Upload Soon

More DiscussionExplanation On YouTubeRelational Algebra Help-Line

Q20➡ | GATE 2019
A relational database contains two tables Student and Performance as shown below:

The primary key of the Student table is Roll_no. For the Performance table, the columns Roll_no. and Subject_code together from the primary key. Consider the SQL query given below:

SELECT S.Student_name, sum (P.Marks)
FROM Student S, Performance P
WHERE P.Marks > 84
GROUP BY S.Student_name;

The number of rows returned by the above SQL query is ________.

Show Answer With Best Explanation

Answer: 5
Explanation: Upload Soon

More DiscussionExplanation On YouTubeSQL Help-Line

Q21➡ | GATE 2018
In an Entity-Relationship (ER) model, suppose R is a many-to-one relationship from entity set E1 to entity set E2. Assume that E1 and E2 participate totally in R and that the cardinality of E1 is greater than the cardinality of E2.
Which one of the following is true about R?
i ➥ Every entity in E1 is associated with exactly one entity in E2.
ii ➥ Some entity in E1 is associated with more than one entity in E2.
iii ➥ Every entity in E2 is associated with exactly one entity in E1.
iv ➥ Every entity in E2 is associated with at most one entity in E1.

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTubeER model Help-Line

Q22➡ | GATE 2018
Consider the following four relational schemas. For each schema, all non-trivial functional dependencies are listed. The underlined attributes are the respective primary keys.

Schema I: Registration(rollno, courses)
Field ‘courses’ is a set-valued attribute containing the set of
courses a student has registered for.
Non-trivial functional dependency
rollno → courses

Schema II: Registration (rollno, courseid, email)
Non-trivial functional dependencies:
rollno, courseid → email
email → rollno

Schema III: Registration (rollno, courseid, marks, grade)
Non-trivial functional dependencies:
rollno, courseid, → marks, grade
marks → grade

Schema IV: Registration (rollno, courseid, credit)
Non-trivial functional dependencies:
rollno, courseid → credit
courseid → credit
Which one of the relational schemas above is in 3NF but not in BCNF?
i ➥ Schema I
ii ➥ Schema II
iii ➥ Schema III
iv ➥ Schema IV

Show Answer With Best Explanation

Answer: II
Explanation: Upload Soon

More DiscussionExplanation On YouTube Normalization Help-Line

Q23➡ | GATE 2018
Consider the relations r(A, B) and s(B, C), where s.B is a primary key and r.B is a foreign key referencing s.B. Consider the query
Q: r ⋈ (σB<5 (s))
Let LOJ denote the natural left outer-join operation. Assume that r and s contain no null values.
Which one of the following is NOT equivalent to Q?
i ➥ σB<5 (r ⨝ s)
ii ➥ σB<5 (r LOJ s)
iii ➥ r LOJB<5(s))
iv ➥ σB<5(r) LOJ s

Show Answer With Best Explanation

Answer: III
Explanation: Upload Soon

More DiscussionExplanation On YouTubeRelational AlgebraHelp-Line

Q24➡ | GATE 2017 Set-1
The following functional dependencies hold true for the relational schema {V, W, X, Y, Z} :
• V → W
• VW → X
• Y → VX
• Y → Z
Which of the following is irreducible equivalent for this set of functional dependencies?
i ➥
V→W
W→X
Y→V
Y→Z
ii ➥
V→W
W→X
Y→V
Y→X
Y→Z
iii ➥
V→W
V→X
Y→V
Y→Z
iv ➥
V→W
V→X
Y→V
Y→X
Y→Z

Show Answer With Best Explanation

Answer: III
Explanation: Upload Soon

More DiscussionExplanation On YouTubeFunctional DependencyHelp-Line

Q25➡ | GATE 2017 Set-1
Consider a database that has the relation schema EMP (EmpId, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are given below:

The output of executing the SQL query is __________.

Show Answer With Best Explanation

Answer: 2.6
Explanation: Upload Soon

More DiscussionExplanation On YouTube SQL Help-Line

Q26➡ | GATE 2017 Set-1
In a database system, unique timestamps are assigned to each transaction using Lamport’s logical clock. Let TS(T1) and TS(T2) be the timestamps of transactions T1 and T2 respectively. Besides, T1 holds a lock on the resource R and T2 has requested a conflicting lock on the same resource R. The following algorithm is used to prevent deadlocks in the database system assuming that a killed transaction is restarted with the same timestamp.

if TS(T2)<TS(T1) then
T1 is killed
else T2 waits.

Assume any transaction that is not killed terminates eventually. Which of the following is TRUE about the database system that uses the above algorithm to prevent deadlocks?
i ➥ The database system is deadlock-free, but not starvation-free.
ii ➥ The database system is starvation-free, but not deadlock-free.
iii ➥ The database system is neither deadlock-free nor starvation-free.
iv ➥ The database system is both deadlock-free and starvation-free.

Show Answer With Best Explanation

Answer: IV
Explanation: Upload Soon

More DiscussionExplanation On YouTubeTransaction ManagementHelp-Line

Q27➡ | GATE 2017 Set-1
Consider a database that has the relation schemas EMP(EmpId, EmpName, DeptId), and DEPT(DeptName, DeptId). Note that the DeptId can be permitted to a NULL in the relation EMP. Consider the following queries on the database expressed in tuple relational calculus.

(I) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∀v ∈ DEPT(t[DeptId] ≠ v[DeptId]))}
(II) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃v ∈ DEPT(t[DeptId] ≠ v[DeptId]))}
(III) {t│∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃v ∈ DEPT(t[DeptId] = v[DeptId]))}

Which of the above queries are safe?
i ➥ (I), (II) and (III)
ii ➥ (I) and (III) only
iii ➥ (I) and (II) only
iv ➥ (II) and (III) only

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTubeRelational Algebra Help-Line

Q28➡ | GATE 2017 Set-1
Consider a database that has the relation schema CR(StudentName, CourseName). An instance of the schema CR is as given below:

The following query is made on the database.
T1 ← πCourseNameStudentName=’SA’(CR))
T2 ← CR ÷ T1
The number of rows in T2 is _________.

Show Answer With Best Explanation

Answer: 4
Explanation: Upload Soon

More DiscussionExplanation On YouTubeRelational Algebra Help-Line

Q29➡ | GATE 2017 Set-2
An ER model of a database consists of entity types A and B. These are connected by a relationship R which does not have its own attribute. Under which one of the following conditions, can the relational table for R be merged with that of A?
i ➥ Relationship R is one-to-many and the participation of A in R is partial.
ii ➥ Relationship R is many-to-one and the participation of A in R is total.
iii ➥ Relationship R is one-to-many and the participation of A in R is partial.
iv ➥ Relationship R is one-to-many and the participation of A in R is total.

Show Answer With Best Explanation

Answer: II
Explanation: Upload Soon

More DiscussionExplanation On YouTubeER Model Help-Line

Q30➡ | GATE 2017 Set-2
Consider the following tables T1 and T2.

In table T1, P is the primary key and Q is the foreign key referencing R in table T2 with on-delete cascade and on-update cascade. In table T2, R is the primary key and S is the foreign key referencing P in table T1 with on-delete set NULL and on-update cascade. In order to delete record 〈3,8〉 from table T1, the number of additional records that need to be deleted from table T1 is ______.

Show Answer With Best Explanation

Answer: 0
Explanation: Upload Soon

More DiscussionExplanation On YouTubeReferential Integrity Help-Line

Q31➡ | GATE 2017 Set-2
Two transactions T1 and T2 are given as
• T1: r1(X) w1(X) r1(Y) w1(Y)
• T2: r2(Y) w2(Y) r2(Z) w2(Z)
where ri(V) denotes a read operation by transaction Ti on a variable V and wi(V) denotes a write operation by transaction Ti on a variable V. The total number of conflict serializable schedules that can be formed by T1 and T2 is _______.

Show Answer With Best Explanation

Answer: 54
Explanation: Upload Soon

More DiscussionExplanation On YouTubeTransaction ManagementHelp-Line

Q32➡ | GATE 2017 Set-2
In a B+ tree, if the search-key value is 8 bytes long, the block size is 512 bytes and the block pointer size is 2 bytes, then the maximum order of the B+ tree is ________.

Show Answer With Best Explanation

Answer: 52
Explanation: Upload Soon

More DiscussionExplanation On YouTubeFile Structure Help-Line

Q33➡ | GATE 2016 Set-1
Which of the following is NOT a superkey in a relational schema with attributes V, W, X, Y, Z and primary key VY?
i ➥ VWXYZ
ii ➥ VWXY
iii ➥ VWXZ
iv ➥ VXYZ

Show Answer With Best Explanation

Answer: III
Explanation: Upload Soon

More DiscussionExplanation On YouTubeNormalization Help-Line

Q34➡ | GATE 2016 Set-1
Which of the following is NOT a part of the ACID properties of database transactions?
i ➥ Deadlock-freedom
ii ➥ Isolation
iii ➥ Consistency
iv ➥ Atomicity

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTubeTransaction ManagemntHelp-Line

Q35➡ | GATE 2016 Set-1
A database of research articles in a journal uses the following schema.
(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, YEAR, PRICE)

The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema.
• (VOLUME, NUMBER, STARTPAGE, ENDPAGE) → TITLE
• (VOLUME, NUMBER) → YEAR
• (VOLUME, NUMBER, STARTPAGE, ENDPAGE) → PRICE
The database is redesigned to use the following schemas.
• (VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE)
(VOLUME, NUMBER, YEAR)

Which is the weakest normal form that the new database satisfies, but the old one does not?
i ➥ BCNF
ii ➥ 3NF
iii ➥ 2NF
iv ➥ 1NF

Show Answer With Best Explanation

Answer: III
Explanation: Upload Soon

More DiscussionExplanation On YouTube Normalization Help-Line

Q36➡ | GATE 2016 Set-1
Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects{O1,…,Ok}. This is done in the following manner:
Step1: T acquires exclusive locks to O1,…,Ok in increasing order of their addresses.
Step2: The required operations are performed.
Step3: All locks are released.
This protocol will
i ➥ guarantee serializability and deadlock-freedom
ii ➥ guarantee neither serializability nor deadlock-freedom
iii ➥ guarantee serializability but not deadlock-freedom
iv ➥ guarantee deadlock-freedom but not serializability

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTubeTransaction ManagementHelp-Line

Q37➡ | GATE 2016 Set-1
B+ Trees are considered BALANCED because
i ➥ The number of records in any two leaf nodes differ by at most 1.
ii ➥ The number of children of any two non-leaf sibling nodes differ by at most 1.
iii ➥ The lengths of the paths from the root to all leaf nodes differ from each other by at most 1.
iv ➥ The lengths of the paths from the root to all leaf nodes are all equal.

Show Answer With Best Explanation

Answer: IV
Explanation: Upload Soon

More DiscussionExplanation On YouTubeFile Structure Help-Line

Q38➡ | GATE 2016 Set-1
Suppose a database schedule S involves transactions T1, …, Tn. Construct the precedence graph of S with vertices representing the transactions and edges representing the conflicts. If S is serializable, which one of the following orderings of the vertices of the precedence graph is guaranteed to yield a serial schedule?
i ➥ Ascending order of transaction indices
ii ➥ Breadth-first order
iii ➥ Depth-first order
iv ➥ Topological order

Show Answer With Best Explanation

Answer: IV
Explanation: Upload Soon

More DiscussionExplanation On YouTubeTransaction ManagemntHelp-Line

Q39➡ | GATE 2016 Set-1
Consider the following database schedule with two transactions, T1 and T2.
• S = r2(X); r1(X); r2(Y); w1(X); r1(Y); w2(X); a1; a2
where ri(Z) denotes a read operation by transaction Ti on a variable Z, wi(Z) denotes a write operation by Ti on a variable Z and ai denotes an abort by transaction Ti.
Which one of the following statements about the above schedule is TRUE?
i ➥ S is non-recoverable
ii ➥ S is recoverable, but has a cascading abort
iii ➥ S does not have a cascading abort
iv ➥ S is strict

Show Answer With Best Explanation

Answer: III
Explanation: Upload Soon

More DiscussionExplanation On YouTubeTransaction ManagementHelp-Line

Q40➡ | GATE 2016 Set-1
Consider the following database table named water_schemes :

The number of tuples returned by the following SQL query is ___________.

Show Answer With Best Explanation

Answer: 2
Explanation: Upload Soon

More DiscussionExplanation On YouTubeSQL Help-Line

Q41➡ | GATE 2015 Set-1
SELECT operation in SQL is equivalent to
i ➥ The selection operation in relational algebra
ii ➥ The selection operation in relational algebra, except that SELECT in SQL retains duplicates
iii ➥ The projection operation in relational algebra
iv ➥ The projection operation in relational algebra, except that SELECT in SQL retains duplicates

Show Answer With Best Explanation

Answer: IV
Explanation: Upload Soon

More DiscussionExplanation On YouTubeSQL Help-Line

Q42➡ | GATE 2015 Set-1
A file is organized so that the ordering of the data records is the same as or close to the ordering of data entries in some index. Than that index is called
i ➥ Dense
ii ➥ Sparse
iii ➥ Clustered
iv ➥ Unclustered

Show Answer With Best Explanation

Answer: III
Explanation: Upload Soon

More DiscussionExplanation On YouTubeFile Structure Help-Line

Q43➡ | GATE 2015 Set-1
Consider an Entity-Relationship (ER) model in which entity sets E1 and E2 are connected by an m:n relationship R12, E1 and E3 are connected by a 1:n (1 on the side of E1 and n on the side of E3) relationship R13.
E1 has two single-valued attributes a11 and a12 of which a11 is the key attribute. E2 has two single-valued attributes a21 and a22 is the key attribute. E3 has two single-valued attributes a31 and a32 of which a31 is the key attribute. The relationships do not have any attributes.

If a relational model is derived from the above ER model, then the minimum number of relations that would be generated if all the relations are in 3NF is __________.

Show Answer With Best Explanation

Answer: 4
Explanation: Upload Soon

More DiscussionExplanation On YouTubeE-R ModelHelp-Line

Q44➡ | GATE 2015 Set-1
Consider the following relations:

Consider the following SQL query.
SELECT S. Student_Name, sum(P.Marks)
FROM Student S, Performance P
WHERE S.Roll_No = P.Roll_No
GROUP BY S.Student_Name

The number of rows that will be returned by the SQL query is ______.

Show Answer With Best Explanation

Answer: 2
Explanation: Upload Soon

More DiscussionExplanation On YouTubeSQL Help-Line

Q45➡ | GATE 2015 Set-2
Consider the following transaction involving two bank account x and y.
read(x);
x:= x-50;
write(x);
read(y);
y:= y+50;
write(y)
The constraint that the sum of the accounts x and y should remain constant is that of
i ➥ Atomicity
ii ➥ Consistency
iii ➥ Isolation
iv ➥ Durability

Show Answer With Best Explanation

Answer: II
Explanation: Upload Soon

More DiscussionExplanation On YouTubeTransaction Management Help-Line

Q46➡ | GATE 2015 Set-2
With reference to the B+ tree index of order 1 shown below, the minimum number of nodes (including the Root node) that must be fetched in order to satisfy the following query:”Get all records with a search key grater than or equal to 7 and less than 15″ is _______.

Show Answer With Best Explanation

Answer: 5
Explanation: Upload Soon

More DiscussionExplanation On YouTubeFile Structure Help-Line

Q47➡ | GATE 2015 Set-2
Consider a simple check-pointing protocol and the following set of operations in the log.
(start, T4); (write, T4, y, 2, 3); (start, T1); (commit, T4); (write, T1, z, 5, 7);
(checkpoint);
(start, T2); (write, T2, x, 1, 9); (commit, T2); (start, T3); (write, T3, z, 7, 2);
If a crash happens now and the system tries to recover using both undo and redo operations, what are the contents of the undo list and the redo list
i ➥ Undo T3, T1; Redo T2
ii ➥ Undo T3, T1; Redo T2, T4
iii ➥ Undo: none; redo: T2, T4, T3, T1
iv ➥ Undo T3, T1; T4; Redo: T2

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTubeTransaction Management Help-Line

Q48➡ | GATE 2015 Set-2
Consider two relations R1(A,B) with the tuples (1,5), (3,7) and R2(A,C) = (1,7), (4,9). Assume that R(A,B,C) is the full natural outer join of R1 and R2. Consider the following tuples of the form (A,B,C): a = (1.5,null), b = (1,null,7), c = (3,null,9), d = (4,7,null), e = (1,5,7), f = (3,7,null), g = (4,null,9). Which one of the following statements is correct?
i ➥ R contains a,b,e,f,g but not c, d.
ii ➥ R contains all of a,b,c,d,e,f,g
iii ➥ R contains e,f,g but not a,b
iv ➥ R contains e but not f,g

Show Answer With Best Explanation

Answer: III
Explanation: Upload Soon

More DiscussionExplanation On YouTube Relational Algebra Help-Line

Q49➡ | GATE 2015 Set-3
Consider the relation X(P, Q, R, S, T, U) with the following set of functional dependencies
F = {
{P, R} → {S,T},
{P, S, U} → {Q, R}
}
Which of the following is the trivial functional dependency in F+, Where F+ is closure of F?
i ➥ {P,R}→{S,T}
ii ➥ {P,R}→{R,T}
iii ➥ {P,S}→{S}
iv ➥ {P,S,U}→{Q}

Show Answer With Best Explanation

Answer: III
Explanation: Upload Soon

More DiscussionExplanation On YouTube Functional Dependency Help-Line

Q50➡ | GATE 2015 Set-3
Consider the following relation
Cinema (theater, address, capacity)

Which of the following options will be needed at the end of the SQL query
SELECT P1. address
FROM Cinema P1
Such that it always finds the addresses of theaters with maximum capacity?
i ➥ WHERE P1.capacity >= All (select P2.capacity from Cinema P2)
ii ➥ WHERE P1.capacity >= Any (select P2.capacity from Cinema P2)
iii ➥ WHERE P1.capacity > All (select max(P2.capacity) from Cinema P2)
iv ➥ WHERE P1.capacity > Any (select max(P2.capacity) from Cinema P2)

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTubeSQL Help-Line

Q51➡ | GATE 2015 Set-3
Consider a B+ tree in which the search key is 12 bytes long, block size is 1024 bytes, record pointer is 10 bytes long and block pointer is 8 bytes long. The maximum number of keys that can be accommodated in each non-leaf node of the tree is ______.

Show Answer With Best Explanation

Answer: 50
Explanation: Upload Soon

More DiscussionExplanation On YouTubeFile Structure Help-Line

Q52➡ | GATE 2015 Set-3
Given the function F=P′+QR, where F is a function in three Boolean variables P,Q and R and P′=!P, consider the following statements.
(S1)F=∑(4,5,6)
(S2)F=∑(0,1,2,3,7)
(S3)F=Π(4,5,6)
(S4)F=Π(0,1,2,3,7)
Which of the following is true?
i ➥ (S1)-False, (S2)-True, (S3)-True, (S4)-False
ii ➥ (S1)-True, (S2)-False, (S3)-False, (S4)-True
iii ➥ (S1)-False, (S2)-False, (S3)-True, (S4)-True
iv ➥ (S1)-True, (S2)-True, (S3)-False, (S4)-False

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTube Relational Algebra Help-Line

Q53➡ | GATE 2015 Set-3
Consider the following partial Schedule S involving two transactions T1 and T2. Only the read and the write operations have been shown. The read operation on data item P is denoted by read(P) and the write operation on data item P is denoted by write(P).


Suppose that the transaction T1 fails immediately after time instance 9. Which of the following statements is correct?
i ➥ T2 must be aborted and then both T1 and T2 must be re-started to ensure transaction atomicity
ii ➥ Schedule S is non-recoverable and cannot ensure transaction atomicity
iii ➥ Only T2 must be aborted and then re-started to ensure transaction atomicity
iv ➥ Schedule S is recoverable and can ensure atomicity and nothing else needs to be done

Show Answer With Best Explanation

Answer: II
Explanation: Upload Soon

More DiscussionExplanation On YouTubeSecant mathod Help-Line

Q54➡ | GATE 2014 Set-1
Consider the relation scheme R = (E, F, G, H, I, J, K, L, M, N) and the set of functional dependencies {{E,F} → {G}, {F} → {I,J}, {E,H} → {K,L}, {K} → {M}, {L} → {N}} on R. What is the key for R?
i ➥ {E,F}
ii ➥ {E,F,H}
iii ➥ {E,F,H,K,L}
iv ➥ {E}

Show Answer With Best Explanation

Answer: II
Explanation: Upload Soon

More DiscussionExplanation On YouTubeFunctional DependencyHelp-Line

Q55➡ | GATE 2014 Set-1
Given the following statements:
S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL.
S2: Given the table R(a,b,c) where a and b together form the primary key.
the following is a valid table definition.
CREATE TABLE S (
a INTEGER,
d INTEGER,
e INTEGER,
PRIMARY KEY (d),
FOREIGN KEY (a) references R)

Which one of the following statements is CORRECT?
i ➥ S1 is TRUE and S2 is FALSE.
ii ➥ Both S1 and S2 are TRUE.
iii ➥ S1 is FALSE and S2 is TRUE.
iv ➥ Both S1 and S2 are FALSE.

Show Answer With Best Explanation

Answer: IV
Explanation: Upload Soon

More DiscussionExplanation On YouTubeKeys-SQL Help-Line

Q56➡ | GATE 2014 Set-1
Consider the following four schedules due to three transactions (indicated by the subscript) using read and write on a data item x, denoted by r(x) and w(x) respectively. Which one of them is conflict serializable?
i ➥ r1 (x); r2 (x); w1 (x); r3 (x); w2 (x)
ii ➥ r2 (x);r1 (x);w2 (x);r3 (x);w1 (x)
iii ➥ r3 (x);r2 (x);r1 (x);w2 (x);w1 (x)
iv ➥ r2 (x);w2 (x);r3 (x);r1 (x);w1 (x)

Show Answer With Best Explanation

Answer: IV
Explanation: Upload Soon

More DiscussionExplanation On YouTubeTransaction Managemnt Help-Line

Q57➡ | GATE 2014 Set-1
Given the following two statements:
S1: Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF.
S2: AB->C, D->E, E->C is a minimal cover for the set of functional dependencies
AB->C, D->E, AB->E, E->C.
Which one of the following is CORRECT?
i ➥ S1 is TRUE and S2 is FALSE.
ii ➥ Both S1 and S2 are TRUE.
iii ➥ S1 is FALSE and S2 is TRUE.
iv ➥ Both S1 and S2 are FALSE.

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTube functional dependencies Help-Line

Q58➡ | GATE 2014 Set-1
Given the following schema:

employees(emp-id, first-name, last-name, hire-date, dept-id, salary)
departments(dept-id, dept-name, manager-id, location-id)


You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:

SQL>SELECT last-name, hire-date
FROM employees
WHERE (dept-id, hire-date) IN
(SELECT dept-id, MAX(hire-date)
FROM employees JOIN departments USING(dept-id)
WHERE location-id = 1700
GROUP BY dept-id);


What is the outcome?
i ➥ It executes but does not give the correct result.
ii ➥ It executes and gives the correct result.
iii ➥ It generates an error because of pairwise comparison.
iv ➥ It generates an error because the GROUP BY clause cannot be used with table joins in a sub- query.

Show Answer With Best Explanation

Answer: II
Explanation: Upload Soon

More DiscussionExplanation On YouTube SQL Help-Line

Q59➡ | GATE 2014 Set-2
The maximum number of superkeys for the relation schema R(E,F,G,H) with E as the key is_________.

Show Answer With Best Explanation

Answer: 8
Explanation: Upload Soon

More DiscussionExplanation On YouTubeKeysHelp-Line

Q60➡ | GATE 2014 Set-2
Given an instance of the STUDENTS relation as shown below:


For (StudentName, StudentAge) to be a key for this instance, the value X should NOT be equal to__________.

Show Answer With Best Explanation

Answer: 19
Explanation: Upload Soon

More DiscussionExplanation On YouTubeKeysHelp-Line

Q61➡ | GATE 2014 Set-2
Consider the following schedule S of transactions T1, T2, T3, T4:


Which one of the following statements is CORRECT?
i ➥ S is conflict-serializable but not recoverable
ii ➥ S is not conflict-serializable but is recoverable
iii ➥ S is both conflict-serializable and recoverable
iv ➥ S is neither conflict-serializable nor is it recoverable

Show Answer With Best Explanation

Answer: III
Explanation: Upload Soon

More DiscussionExplanation On YouTube SerializableHelp-Line

Q62➡ | GATE 2014 Set-2
Consider a join (relation algebra) between relations r(R)and s(S) using the nested loop method. There are 3 buffers each of size equal to disk block size, out of which one buffer is reserved for intermediate results. Assuming size(r(R))<size(s(S)), the join will have fewer number of disk block accesses if.
i ➥ relation r(R) is in the outer loop.
ii ➥ relation s(S) is in the outer loop.
iii ➥ join selection factor between r(R) and s(S) is more than 0.5.
iv ➥ join selection factor between r(R) and s(S) is less than 0.5.

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTube Relational Algebra Help-Line

Q63➡ | GATE 2014 Set-3
What is the optimized version of the relation algebra expression πA1A2F1F2(r)))), where A1, A2 are sets of attributes in with A1 ⊂ A2 and F1, F2 are Boolean expressions based on the attributes in r?
i ➥ πA1(F1∧F2) (r))
ii ➥ πA1(F1∨F2) (r))
iii ➥ πA2(F1∧F2) (r))
iv ➥ πA2(F1∨F2) (r))

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTubeRelational Algebra Help-Line

Q64➡ | GATE 2014 Set-3
A prime attribute of a relation scheme is an attribute that appears
i ➥ in all candidate keys of R.
ii ➥ in some candidate key of R.
iii ➥ in a foreign key of R.
iv ➥ only in the primary key of R.

Show Answer With Best Explanation

Answer: II
Explanation: Upload Soon

More DiscussionExplanation On YouTubeKeys Help-Line

Q65➡ | GATE 2014 Set-3
Consider the transactions T1, T2, and T3 and the schedules S1 and S2 given below.
T1: r1(X); r1(Z); w1(X); w1(Z)
T2: r2(Y); r2(Z); w2(Z)
T3: r3(Y); r3(X); w3(Y)


S1: r1(X); r3(Y); r3(X); r2(Y); r2(Z); w3(Y); w2(Z); r1(Z); w1(X); w1(Z)
S2: r1(X); r3(Y); r2(Y); r3(X); r1(Z); r2(Z); w3(Y); w1(X); w2(Z); w1(Z)


Which one of the following statements about the schedules is TRUE?
i ➥ Only S1 is conflict-serializable.
ii ➥ Only S2 is conflict-serializable.
iii ➥ Both S1 and S2 are conflict-serializable.
iv ➥ Neither S1 nor S2 is conflict-serializable.

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTubeTransaction Managemnt Help-Line

Q66➡ | GATE 2014 Set-3
Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation.

employee (empId, empName, empAge)
dependent(depId, eId, depName, depAge)


Consider the following relational algebra query:

empId(employee)-∏empId(employee ⋈(empId = eID)∧(empAge ≤ depAge)dependent)

The above query evaluates to the set of empIds of employees whose age is greater than that of
i ➥ some dependent.
ii ➥ all dependents.
iii ➥ some of his/her dependents.
iv ➥ all of his/her dependents.

Show Answer With Best Explanation

Answer: IV
Explanation: Upload Soon

More DiscussionExplanation On YouTube Relational Algebra Help-Line

Q67➡ | GATE 2013
An index is clustered, if
i ➥ it is on a set of fields that form a candidate key.
ii ➥ it is on a set of fields that include the primary key.
iii ➥ the data records of the file are organized in the same order as the data entries of the index.
iv ➥ the data records of the file are organized not in the same order as the data entries of the index.

Show Answer With Best Explanation

Answer: III
Explanation: Upload Soon

More DiscussionExplanation On YouTubeFile Structure Help-Line

Q68➡ | GATE 2013
Consider the following relation schema


Which of the following queries are equivalent to this query in English?
“Find the distinct names of all students who score more than 90% in the course numbered 107”

(I) SELECT DISTINCT S.sname FROM Students as S,Registration as R WHERE R.rollno = S.roll AND R.courseno = 107 AND R.percent > 90

(II) Πsnamecourseno=107∧percent>90(Registration ⋈ Students))

(III) {T|∃S ∈ Students, ∃R ∈ Registration (S.rollno = R.rollno ∧ R.courseno = 107 ∧ R.percent > 90 ∧ T.sname = S.sname)}

(IV) {〈SN〉| ∃SR ∃RP(〈SR,SN〉 ∈ Students ∧ 〈SR,107,RP〉 ∈ Registration ∧ RP>90)}
i ➥ I, II, III and IV
ii ➥ I, II and III only
iii ➥ I, II and IV only
iv ➥ II, III and IV only

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTube Relational Algebra and SQLHelp-Line

Q69➡ | GATE 2013
Statement for Linked Answer Questions 54 and 55:

Relation R has eight attributes ABCDEFGH . Fields of R contain only atomic values.
F = {CH→G, A→BC, B→CFG, E→A, F→EG}, is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R.

How many candidate keys does the relation R have?
i ➥ 3
ii ➥ 4
iii ➥ 5
iv ➥ 6

Show Answer With Best Explanation

Answer: II
Explanation: Upload Soon

More DiscussionExplanation On YouTube Normalization Help-Line

Q70➡ | GATE 2013
Statement for Linked Answer Questions 54 and 55:

Relation R has eight attributes ABCDEFGH . Fields of R contain only atomic values.
F = {CH→G, A→BC, B→CFG, E→A, F→EG}, is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R.

The relation R is
i ➥ in 1NF, but not in 2NF.
ii ➥ in 2NF, but not in 3NF.
iii ➥ in 3NF, but not in BCNF.
iv ➥ in BCNF.

Show Answer With Best Explanation

Answer: I
Explanation: Upload Soon

More DiscussionExplanation On YouTube Normalization Help-Line

Q71➡ | GATE 2012
Which of the following is TRUE?
i ➥ Every relation in 3NF is also in BCNF
ii ➥ A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R
iii ➥ Every relation in BCNF is also in 3NF
iv ➥ No relation can be in both BCNF and 3NF

Show Answer With Best Explanation

Answer: III
Explanation: Upload Soon

More DiscussionExplanation On YouTube Normalization Help-Line

Q72➡ | GATE 2012