Database-Management-System

Question 1

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?

A
Each leaf node has a pointer to the next leaf node
B
Non-leaf nodes have pointers to data records
C
B+ Tree is a height-balanced tree
D
Key values in each node are kept in sorted order
       Database-Management-System       GATE 2019
Question 1 Explanation: 
(Memory based question)
In B+ trees non-leaf nodes do not have pointers to data records.
Question 2

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?
A
Both I and II
B
Neither I nor II
C
II only
D
I only
       Database-Management-System       GATE 2019
Question 2 Explanation: 
(Memory-based question)
In strict 2PL, a transaction T does not release any of its exclusive (write) locks until after it commits or aborts.
Hence, no other transaction can read or write an item that is written by T unless T has committed, leading to a strict schedule for recoverability.
(Ref: Fundamentals of Database Systems by Elmasri and Navathe, 7e Pg. No. 789)
By ignoring the write, Thomas write rule allows schedules that are not conflict serializable but are nevertheless correct.
Those non-conflict-serializable schedules allowed satisfy the definition of view serializable schedules.
(Ref: Database System Concepts by Silberschatch, Korth and Sudarshan, 6e Pg No. 686)
Question 3

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?

x(P.Y=R.Y ∧ R.V=V2)(P × R)) - ∏x(Q.Y=R.Y ∧ Q.T>2)(Q × R))
A
0
B
1
C
2
D
3
       Database-Management-System       GATE 2019
Question 3 Explanation: 
σ(P.Y=R.Y ∧ R.V=V2)(P × R)

x(P.Y=R.Y ∧ R.V=V2)(P × R))

σ(Q.Y=R.Y ∧ Q.T>2)(Q × R)
x(Q.Y=R.Y ∧ Q.T>2)(Q × R))

x(P.Y=R.Y ∧ R.V=V2)(P × R)) - ∏x(Q.Y=R.Y ∧ Q.T>2)(Q × R))
Question 4

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 _____.

A
0
B
9
C
7
D
5
       Database-Management-System       GATE 2019
Question 4 Explanation: 
(Executed under Oracle Express Edition)
SQL> SELECT S.Student_name,sum(P.Marks)
2 FROM Student S,Performance P
3 WHERE P.Marks>84
4 GROUP BY S.Student_name;
Question 5

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?  
A
I only
B
Neither I nor II
C
II only
D
Both I and II
       Database-Management-System       GATE 2019
Question 5 Explanation: 
Y = (PR)
R → P
R+ = RP
* In R → P, 'R' is a super key. So, Y is in BCNF.
Z = (QRS)
QR → S
S → Q
CK's = QR, RS
* In, S → Q, 'S' is not a super key. So, Z is not in BCNF.
* Y is in BCNF and Z is not in BCNF.
* 'R' is common attribute in the relations Y and Z and R is candidate key for Y. So, the decomposition is lossless.
* The FD, R → P is applicable on Y and QR → S, S → Q are applicablein 2.
So, the decomposition is dependency preserving.
* Hence, Statement II is correct.
Question 6

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?

A
Every entity in E1 is associated with exactly one entity in E2.
B
Some entity in E1 is associated with more than one entity in E2.
C
Every entity in E2 is associated with exactly one entity in E1.
D
Every entity in E2 is associated with at most one entity in E1.
       Database-Management-System       ER-Model       Gate 2018
Question 6 Explanation: 

The M : 1 relationship holds between two entities E1 and E2, in which each tuple from E2 is in relation with many tuples of E1. One tuple from E1 is in relation with only one tuple of E2. It is given that participation from both the sides is total and the cardinality of E1 is greater than E2.

Therefore, every entity E1 is associated with exactly one entity in E2.
Question 7

Consider the following two tables and four queries in SQL.

     Book (isbn, bname), Stock (isbn, copies)
Query 1:    SELECT B.isbn, S.copies
            FROM Book B INNER JOIN Stock S
            ON B.isbn = S.isbn;

Query 2:    SELECT B.isbn, S.copies
            FROM Book B LEFT OUTER JOIN Stock S
            ON B.isbn = S.isbn;

Query 3:    SELECT B.isbn, S.copies
            FROM Book B RIGHT OUTER JOIN Stock S
            ON B.isbn = S.isbn;

Query 4:    SELECT B.isbn, S.copies
            FROM Book B FULL OUTER JOIN Stock S
            ON B.isbn = S.isbn;

Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?

A
Query 1
B
Query 2
C
Query 3
D
Query 4
       Database-Management-System       SQL       Gate 2018
Question 7 Explanation: 
Given two tables are,
Book (isbn, bname)
Stock (isbn, copies)
isbn is a primary key of Book and isbn is a foreign key of stock referring to Book table.
For example:

Query 1:
INNER JOIN keyword selects records that have matching values in both tables (Book and Stock).

So, the result of Query 1 is,

Query 2:
The LEFT OUTER JOIN keyword returns all records from the left table (Book) and the matched records from the right table (Stock).
The result is NULL from the right side, if there is no match.

So, the result of Query 2 is,

Query 3:
The RIGHT OUTER JOIN keyword returns all records from the right table (Stock), and the matched records from the left table(BOOK).
The result is NULL from the left side, when there is no match.


Query 4:
The FULL OUTER JOIN keyword return all records when there is a match in either left (Book) or right (Stock) table records.

So, the result of Query 4 is,

Therefore, from the result of above four queries, a superset of the outputs of the Query 1, Query 2 and Query 3 is Query 4.
Note:
If we take isbn as a primary key in both the tables Book and Stock and foreign key, in one of the tables then also will get option (D) as the answer.
Question 8

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, coursid, 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?

A
Schema I
B
Schema II
C
Schema III
D
Schema IV
       Database-Management-System       Normalization       Gate 2018
Question 8 Explanation: 
Schema I:
Registration (rollno, courses) rollno → courses
For the given schema Registration ‘rollno’ is a primary key.
Left-side of the functional dependency is a superkey so, Registration is in BCNF.
Schema II:
Registrstion (rollno, courseid, email)
rollno, courseid → email
email → rollno
From the given schema the candidate key is (rollno + courseid).
There is no part of the key in the left hand of the FD’s so, it is in 2NF.
In the FD email→rollno, email is non-prime attribute but rollno is a prime attribute.
So, it is not a transitive dependency.
No transitive dependencies so, the schema is in 3NF.
But in the second FD email→rollno, email is not a superkey.
So, it is violating BCNF.
Hence, the schema Registration is in 3NF but not in BCNF.
Schema III:
Registration (rollno, courseid, marks, grade)
rollno, courseid → marks, grade
marks → grade
For the schema the candidate key is (rollno + courseid).
There are no part of the keys are determining non-prime attributes.
So, the schema is in 2NF.
In the FD marks → grade, both the attributes marks and grade are non-prime.
So, it is a transitive dependency.
The FD is violating 3NF.
The schema Registration is in 2NF but not in 3NF.
Schema IV:
Registration (rollno, courseid, credit)
rollno, courseid → credit
courseid → credit
The candidate key is (rollno + courseid).
In the FD, courseid → credit, courseid is part of the key (prime attribute) and credit is non-prime.
So, it is a partial dependency.
The schema is violating 2NF.
Question 9

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?

A
σB<5 (r ⨝ s)
B
σB<5 (r LOJ s)
C
r LOJ (σB<5(s))
D
σB<5(r) LOJ s
       Database-Management-System       Relational-Algebra       Gate 2018
Question 9 Explanation: 
Consider the following 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 following tables without NULL values.

Q: r⨝(σB<5(S))
The result of σB<5(S) is

The result of σB<5(S) is

Option (A):
The result of r⨝S is

The result of σB<5(r⨝S) is

Option (B):
The result of r LOJ S is

The result of σB<5(r LOJ S) is

Option (C):
The result of σB<5(S) is

Now, the result of r LOJ(σB<5(S))

Option (D):
The result of σB<5(r) is

Now, the result of σB<5(r) LOJ S is

Therefore, from the output of above four options, the results of options, the results of options (A), (B) and (D) are equivalent to Q.
Question 10

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?

 
A
V→W
V→X
Y→V
Y→Z
B
V→W
W→X
Y→V
Y→Z
C
V→W
V→X
Y→V
Y→X
Y→Z
D
V→W
W→X
Y→V
Y→X
Y→Z
       Database-Management-System       Functional-Dependency       Gate 2017 set-01
Question 10 Explanation: 
Step 1:
V → W, VW → X, Y → V, Y → X, Y→ Z
Step 2:
V → W, VW → X, Y → V, Y → X, Y→ Z
(V)+ = V ×
(VW)+ = VW ×
(Y)+ = YXZ
(Y)+ = YVW ×
(Y)+ = YVWX
Without Y → X, the closure of Y is deriving ‘X’ from the remaining attributes.
So, we can remove Y → X as its redundant.
Step 3:
V → W, VW → X, Y → V, Y → Z
(V)+ = VW, the closure of V is deriving W from the remaining FD’s.
So, W is redundant. We can remove it.
So, the final canonical form is
V→W, V→X, Y→V, Y→Z
⇾ So, option (A) is correct.
Question 11

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 ___________.

A
2.6
B
2.7
C
2.8
D
2.9
       Database-Management-System       SQL       Gate 2017 set-01
Question 11 Explanation: 
The given query is

⇾ We start evaluating from the inner query.
The inner query forms DeptName wise groups and counts the DeptName wise EmpIds.
⇾ In inner query DeptName, Count(EmpId) is the alias name DeptName, Num.
So, the output of the inner query is,

The outer query will find the
Avg(Num) = (4+3+3+2+1)/5 = 2.6
Question 12
 

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?

A
(I) and (II) only
B
(I) and (III) only
C
(II) and (III) only
D
(I), (II) and (III)
       Database-Management-System       Relational-Algebra       Gate 2017 set-01
Question 12 Explanation: 
An expression is said to be safe, if it yield a finite number of tuples, otherwise unsafe.
(I) Gives EmpNames who do not belong to any Department. So, it is going to be a finite number of tuples as a result.
(II) Gives EmpNames who do not belong to some Department. This is also going to have finite number of tuples.
(III) Gives EmpNames who do not belong to same Department. This one will also give finite number of tuples.
All the expressions I, II and III are giving finite number of tuples. So, all are safe.
Question 13

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?

 
A
The database system is both deadlock-free and starvation-free.
B
The database system is deadlock-free, but not starvation-free.
C
The database system is starvation-free, but not deadlock-free.
D
The database system is neither deadlock-free nor starvation-free.
       Database-Management-System       Time-stamp-Ordering       Gate 2017 set-01
Question 13 Explanation: 
In the question, it is given that ‘unique time stamps’ are assigned to the transactions.
So, there will be no equal timestamps.
Lamport’s logical clock:
In this timestamps are assigned in increasing order only.
According to the given algorithm,
TS(T2) < TS(T1)
then T1 is killed
else
T2 will wait
So, in both the cases, it will be deadlock free and there will be no starvation.
Question 14

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 ____________.

 
A
4
B
5
C
6
D
7
       Database-Management-System       Relational-Algebra       Gate 2017 set-01
Question 14 Explanation: 
(1) T1 ← πCourseNameStudentName = 'SA'(CR))
The σStudentName = 'SA'(CR) will produce the following

⇾ The result of T1 ← πCourseNameStudentName='SA'(CR)) is

(2) T2 ← CR÷T1
⇾ We see that SA is enrolled for CA, CB and CC.
⇾ T2 will give the StudentNames those who have enrolled for all the CA, C, CC courses. So, the following Students are enrolled for the given 3 courses.

⇾ So, the output of T2 will have 4 rows.
Question 15

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?

 
A
Relationship R is one-to-many and the participation of A in R is total.
B
Relationship R is one-to-many and the participation of A in R is partial.
C
Relationship R is many-to-one and the participation of A in R is total.
D
Relationship R is one-to-many and the participation of A in R is partial.
       Database-Management-System       ER-Model       GATE 2017(set-02)
Question 15 Explanation: 

The relational table for R be merged that of A, if the relationship R is Many-to-one and the participation of A in R is total.
Question 16
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 _________.

 
A
0
B
1
C
2
D
3
       Database-Management-System       Referential-Integrity       GATE 2017(set-02)
Question 16 Explanation: 

Therefore, no. of additional records deleted from the table T1 is 0 (zero).
Question 17

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 ____________.

A
54
B
55
C
56
D
57
       Database-Management-System       Transactions       GATE 2017(set-02)
Question 17 Explanation: 
From the given transactions T1 and T2, total number of schedules possible = (4+4)!/4!4!
= 8!/(4×3×2×4×3×2)
= (8×7×6×5×4×3×2×1)/(4×3×2×4×3×2)
= 70
Following two conflict actions are possible:


∴# Permutations = 4 × 3 = 12

#permutations = 4 × 1 = 4
∴ Total no. of conflict serial schedules possible = 70 - 12 - 4 = 54
Question 18

Consider the following database table named top_scorer.

SELECT ta.player FROM top_scorer AS ta
WHERE ta.goals > ALL (SELECT tb.goals
                  FROM top_scorer AS tb
                  WHERE tb.country = 'Spain')
AND ta.goals > ANY (SELECT tc.goals
                  FROM top_scorer AS tc
                  WHERE tc.country = 'Germany')

The number of tuples returned by the above SQL query is _____.

 
A
7
B
8
C
9
D
10
       Database-Management-System       SQL       GATE 2017(set-02)
Question 18 Explanation: 

In the given database table top_scorer no players are there from ‘Spain’.
So, the query (1) results 0 and ALL (empty) is always TRUE.
The query (2) selects the goals of the players those who are belongs to ‘Germany’.
So, it results in ANY (16, 14, 11, 10).
So, the outer most query results the player names from top_scorer, who have more goals.
Since, the minimum goal by the ‘Germany’ player is 10, it returns the following 7 rows.
Question 19

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 _________.

A
52
B
53
C
54
D
55
       Database-Management-System       B+-Trees       GATE 2017(set-02)
Question 19 Explanation: 
Given,
Block size = 512 bytes
Block pointer = 2 bytes
Search key = 8 bytes
Let Maximum order of B+ tree = n
n(Pb) + (n - 1)(k) ≤ 512
n(2) + (n - 1)(8) ≤ 512
2n + 8n - 8 ≤ 512
10n ≤ 520
n ≤ 520/10
(n = 52)
Question 20

Which of the following is NOT a superkey in a relational schema with attributes V, W, X, Y, Z and primary key VY?

A
VXYZ
B
VWXZ
C
VWXY
D
VWXYZ
       Database-Management-System       Normalization       2016 set-01
Question 20 Explanation: 
It is given that “VY” is a primary key of the relational schema.
Any superset of “VY” is a super key. So, option (B) does not contain “Y”.
Question 21

NOT a part of the ACID properties of database transactions?

A
Atomicity
B
Consistency
C
Isolation
D
Deadlock-freedom
       Database-Management-System       Transactions       2016 set-01
Question 21 Explanation: 
A transaction in a database system must maintain Atomicity, Consistency, Isolation and Durability – commonly known as ACID properties.
So, Deadlock – freedom is not there in the ACID properties.
Question 22
 

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?

A
1NF
B
2NF
C
3NF
D
BCNF
       Database-Management-System       Normalization       2016 set-01
Question 22 Explanation: 
Journal (V, N, S, E, T, Y, P)
V – VOLUME
N – NUMBER
S – STARTPAGE
E – ENDPAGE
T – TITLE
Y – YEAR
P – PRICE
Primary key: (V, N, S, E)
FD set:
(V, N, S, E) → T
(V, N) → Y
(V, N, S, E) → P
In (V, N) → Y; V, N is a part of the key and Y is non-prime attribute.
So, it is a partial dependency.
Now, the schema “Journal” is in 1NF but not in 2NF.
The database is redesigned as follows:

Both R1 and R2 are in BCNF.
Therefore, 2NF is the weakest normal form that the new database satisfies, but the old one does not.
Question 23

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

 
A
guarantee serializability and deadlock-freedom
B
guarantee neither serializability nor deadlock-freedom
C
guarantee serializability but not deadlock-freedom
D
guarantee deadlock-freedom but not serializability
       Database-Management-System       Two Phase Locking protocol       2016 set-01
Question 23 Explanation: 
Conservative 2PLP:
In conservative 2PL protocol, a transaction has to lock all the items before the transaction begins execution.
Advantages of conservative 2PLP:
• No possibility of deadlock.
• Ensure serializability.
The given scenario (Step1, Step2 and Step3) is conservative 2PLP.
Question 24

B+ Trees are considered BALANCED because

A
the lengths of the paths from the root to all leaf nodes are all equal.
B
the lengths of the paths from the root to all leaf nodes differ from each other by at most 1.
C
the number of children of any two non-leaf sibling nodes differ by at most 1.
D
the number of records in any two leaf nodes differ by at most 1.
       Database-Management-System       B+-Trees       GATE 2016 set-2
Question 24 Explanation: 
In both B & B+ trees all the leaf nodes will be at same level.
Question 25

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?

A
Topological order
B
Depth-first order
C
Breadth-first order
D
Ascending order of transaction indices
       Database-Management-System       Transactions       GATE 2016 set-2
Question 25 Explanation: 
If a schedule is conflict serializable then no cycle in precedence graph should be present.
But BFS and DFS are also potssible for cyclic graphs.
And topological sort is not possible for cyclic graph.
Moreover option (D) is also wrong because in a transaction with more indices might come before lower one.
Question 26
 

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?

A
S is non-recoverable
B
S is recoverable, but has a cascading abort
C
S does not have a cascading abort
D
S is strict
       Database-Management-System       Transactions       GATE 2016 set-2
Question 26 Explanation: 
Given schedule is,

Now let's check statements one by one,
A) False, because there is no dirty read. So, it is recoverable.
B) False, because there is to dirty read. So, no cascading aborts.
C) True.
D) False, because there is Transaction T2 which written the value of x which is written by T1 before T1 has aborted. So, not strict.
Question 27

Consider the following database table named water_schemes :

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

with total(name, capacity) as
   select district_name, sum(capacity)
   from water_schemes
   group by district_name
with total_avg(capacity) as
   select avg(capacity)
   from total
select name
   from total, total_avg
   where total.capacity ≥ total_avg.capacity
A
2
B
3
C
4
D
5
       Database-Management-System       SQL       GATE 2016 set-2
Question 27 Explanation: 
• The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query.
The name assigned to the sub-query is treated as though it was an inline view or table.
• First group by district name is performed and total capacities are obtained as following:

• Then average capacity is computed, Average Capacity = (20 + 40 + 30 + 10)/4 = 100/4 = 25
• Finally, 3rd query will be executed and it's tuples will be considered as output, where name of district and its total capacity should be more than or equal to 25.
• Then average capacity is computed,
Average Capacity = (20 + 40 + 30 + 10)/4 = 100/4 = 25
• Finally, 3rd query will be executed and it's tuples will be considered as output, where name of district and its total capacity should be more than or equal to 25.
Question 28
SELECT operation in SQL is equivalent to
A
the selection operation in relational algebra
B
the selection operation in relational algebra, except that SELECT in SQL retains duplicates
C
the projection operation in relational algebra
D
the projection operation in relational algebra, except that SELECT in SQL retains duplicates
       Database-Management-System       SQL       GATE 2015 (Set-01)
Question 28 Explanation: 
SELECT operation in SQL perform vertical partitioning which is performed by projection operation in relational calculus but SQL is multi sets; hence (D).
Question 29
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 ___________.  
A
4
B
6
C
7
D
8
       Database-Management-System       ER-Model       GATE 2015 (Set-01)
Question 29 Explanation: 


Question 30
Consider the following relations:
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 _________  
A
2
B
3
C
4
D
5
       Database-Management-System       SQL       GATE 2015 (Set-01)
Question 30 Explanation: 
Output table is
Question 31
Consider the following transaction involving two bank account x and y.
A
Atomicity
B
Consistency
C
Isolation
D
Durability
       Database-Management-System       Transactions       GATE 2015 -(Set-2)
Question 31 Explanation: 
The consistency property ensures that the database remains in a consistent state before the (start of the transaction and after the transaction is over. Here sum of the accounts x & y should remain same before & after execution of the given transactions which refers to the consistency of the sum.
Question 32
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 searcj key grater than or equal to 7 and less than 15" is ___________
A
6
B
7
C
5
D
9
       Database-Management-System       B+-Trees       GATE 2015 -(Set-2)
Question 32 Explanation: 
Question 33
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?  
A
R contains a,b,e,f,g but not c, d.
B
R contains all of a,b,c,d,e,f,g
C
R contains e,f,g but not a,b
D
R contains e but not f,g
       Database-Management-System       Relational-Algebra       GATE 2015 -(Set-2)
Question 33 Explanation: 

⋆ So, from the above resultant table, R contains e, f, g only but not a, b.
Question 34
COnsider a simple checkpointing protocol and the following set of operations in the log.
A
Undo T3, T1; Redo T2
B
Undo T3, T1; Redo T2, T4
C
Undo: none; redo: T2, T4, T3, T1
D
Undo T3, T1; T4; Redo: T2
       Database-Management-System       Transactions       GATE 2015 -(Set-2)
Question 34 Explanation: 
As T1 & T3 are not yet committed they must be undone. The transactions which are after the latest checkpoint must be redone. So T2 must be redone. No need to redo the records which are before the last checkpoint, so T4 need not be redone.
Question 35
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+ is closure of F?
A
{P,R}→{S,T}
B
{P,R}→{R,T}
C
{P,S}→{S}
D
{P,S,U}→{Q}
       Database-Management-System       Functional-Dependency       GATE 2015(Set-03)
Question 35 Explanation: 
X→Y is trivial if Y⊆ X
Question 36
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?
A
WHERE P1.capacity >= All (select P2.capacity from Cinema P2)
B
WHERE P1.capacity >= Any (select P2.capacity from Cinema P2)
C
WHERE P1.capacity > All (select max(P2.capacity) from Cinema P2)
D
WHERE P1.capacity > Any (select max(P2.capacity) from Cinema P2)
       Database-Management-System       SQL       GATE 2015(Set-03)
Question 36 Explanation: 
Inner query collects capacities of all the theatres and in outer query we are filtering the tuples with the condition “capacity >= All”.
So the theatres which are having maximum capacity will satisfy the condition.
Question 37
   
A
0
B
1/2
C
1
D
       Database-Management-System       Calculus       GATE 2015(Set-03)
Question 37 Explanation: 
Question 38
Suppose Xi for  i = 1, 2, 3 are independent and identically distributed random variables whose probability mass functions are Pr[X= 0] = Pr[X= 1]=1/2 for i = 1, 2, 3. Define another random variable Y = X1X2 ⊕ X3, where denotes XOR. Then Pr[Y = 0|X3 = 0] = _______________.    
A
0.75
B
0.76
C
0.77
D
0.78
       Database-Management-System       Probability       GATE 2015(Set-03)
Question 38 Explanation: 

Pr(Y=0/ X3=0) = Pr(Y=0 ∩ X3=0)/ Pr(X3=0)
= 3/8 / 4/8 = 3/4 = 0.75
Question 39
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?
A
{E,F}
B
{E,F,H}
C
{E,F,H,K,L}
D
{E}
       Database-Management-System       Functional-Dependency       GATE 2014(Set-01)
Question 39 Explanation: 
A) (EF)+ = EFGIJ. So, EF is not a key.
B) (EFH)+ = EFHGIJKLMN, EFH is deriving all the attributes of R. So, EFH is a key.
C) If EFH is a key, then EFHKL will become the super key.
D) (E)+ = E
Question 40
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?
A
S1 is TRUE and S2 is FALSE.
B
Both S1 and S2 are TRUE.
C
S1 is FALSE and S2 is TRUE.
D
Both S1 and S2 are FALSE.
       Database-Management-System       SQL       GATE 2014(Set-01)
Question 40 Explanation: 
S1: False
Using a check constraint, we can have the same effect as foreign key while adding elements to the child table. But while deleting elements from the parent table the referential integrity constraint is no longer valid. So, a check constraint cannot replace a foreign key.
S2: False:
Foreign key in one table should be defined as a primary key in other table. In above table definition, table S has a foreign key that refers to field ‘a’ of R. The field ‘a’ in table S is part of the primary key and part of the key cannot be declared as a foreign key.
Question 41
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?
A
r1 (x); r2 (x); w1 (x); r3 (x); w2 (x)
B
r2 (x);r1 (x);w2 (x);r3 (x);w1 (x)
C
r3 (x);r2 (x);r1 (x);w2 (x);w1 (x)
D
r2 (x);w2 (x);r3 (x);r1 (x);w1 (x)
       Database-Management-System       Transactions       GATE 2014(Set-01)
Question 41 Explanation: 
Option: A

- Polygraph contains cycle. So, not a conflict serializable.
Option: B

-Cyclic
Option: C

- Cyclic
Option: D

- Acyclic, so conflict serializable.
Question 42
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?
A
S1 is TRUE and S2 is FALSE.
B
Both S1 and S2 are TRUE.
C
S1 is FALSE and S2 is TRUE.
D
Both S1 and S2 are FALSE.
       Database-Management-System       Normalization       GATE 2014(Set-01)
Question 42 Explanation: 
S1: True
If we can prove the relation is in BCNF then by default it would be in 1NF, 2NF, 3NF also.
Let R(AB) be a two attribute relation, then
If {A→B} exists then BCNF since {A}+ = AB = R
If {B→A} exists then BCNF since {B}+ = AB = R
If {A→B, B→A} exists then BCNF since A and B both are Super Key now.
If {No non-trivial Functional Dependency} then default BCNF.
Hence it’s proved that a Relation with two single-valued attributes is in BCNF hence it’s also in 1NF, 2NF, 3NF.
S2: False
The canonical cover for the given FD set is {AB→C, D→E, AB→E, E→C}. As we can see AB→E is not covered in minimal cover since {AB}+ = ABC in the given cover {AB→C, D→E, E→C}
Question 43
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?
A
It executes but does not give the correct result.
B
It executes and gives the correct result.
C
It generates an error because of pairwise comparison.
D
It generates an error because the GROUP BY clause cannot be used with table joins in a subquery.
       Database-Management-System       SQL       GATE 2014(Set-01)
Question 43 Explanation: 
The given SQL query will display the last names and hire-dates of all latest hires in their respective departments in the location ID 1700. So, correct option is (B).
Question 44
The maximum number of superkeys for the relation schema R(E, F, G, H) with E as the key is _____.
A
8
B
9
C
10
D
11
       Database-Management-System       Candidate-Keys       Gate 2014 Set -02
Question 44 Explanation: 
Maximum no. of possible super keys for a relation with n attributes with one candidate key (only one attribute) = 2n-1
Here, n = 4.
So, the possible super keys = 24-1 = 8
The super keys are: E, EH, EG, EF, EGH, EFH, EFG, EFGH.
Question 45
Given the STUDENTS relation as shown below. For (StudentName, StudentAge) to be the key for this instance, the value X should not be equal to
A
19
B
20
C
21
D
22
       Database-Management-System       Candidate-Keys       Gate 2014 Set -02
Question 45 Explanation: 
There is already one entry with StudentName as “Shankar” and Age as 19. So, X value should not be 19.
Question 46
Consider the following schedule S of transactions T1, T2, T3, T4: Which one of the following statements is CORRECT?
A
S is conflict-serializable but not recoverable
B
S is not conflict-serializable but is recoverable
C
S is both conflict-serializable and recoverable
D
S is neither conflict-serializable nor is it recoverable
       Database-Management-System       Transactions       Gate 2014 Set -02
Question 46 Explanation: 

In the precedence graph, there are no cycles. So, it is conflict serializable and recoverable also.
Question 47
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
A
relation r(R) is in the outer loop.
B
relation s(S) is in the outer loop.
C
join selection factor between r(R) and s(S) is more than 0.5.
D
join selection factor between r(R) and s(S) is less than 0.5.
       Database-Management-System       Joins       Gate 2014 Set -02
Question 47 Explanation: 
The join between r(R) and s(S) which is using nested loop will be as follows,
For each tuple r in R do
For each tuple s in S do
If r and s satisfy the join condition then
output the tuple
The above algorithm involves (nr*bs+br )
block transfer and (nr+br ) seeks.
where,
br → no. of blocks in R
bs → no. of blocks in S
hr → no. of tuples in R
To have less block accesses in nr should be less and in question it is given that size(r(R)) < size(s(S)).
To have fewer no. of disk block accesses the relation r(R) should be in outer loop.
Question 48
SQL allows tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below:
    select * from R where a in (select S.a from S)
A
select R.* from R,S where R.a=S.a
B
select distinct R.* from R,S where R.a=S.a
C
select R.* from R,(select distinct a from S) as S1 where R.a=S1.a
D
select R.* from R,S where R.a=S.a and is unique R
       Database-Management-System       SQL       Gate 2014 Set -02
Question 48 Explanation: 
Multiplicity of duplicate tuples will be distributed when there is a match between R.a and S.a; and for that match, S.a's value is repeated in each cases except the third case. So, the output of query given in the question matches with the output of (C).
Question 49
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?  
A
πA1(F1∧F2) (r))
B
πA1(F1∨F2) (r))
C
πA2(F1∧F2) (r))
D
πA2(F1∨F2) (r))
       Database-Management-System       ER-Model       Gate 2014 Set -03
Question 49 Explanation: 
 Since A1 ⊂ A2 will get only attribute A1 as it is in the outside. So we can remove project A2.
 Two Selects with Boolean expression can be combined into one select with AND of two Boolean expressions.
Question 50
A prime attribute of a relation scheme  is an attribute that appears
A
in all candidate keys of R.
B
in some candidate key of R.
C
in a foreign key of R.
D
only in the primary key of R .
       Database-Management-System       ER-Model       Gate 2014 Set -03
Question 50 Explanation: 
A prime attribute of a relation scheme R is an attribute that appears in some candidate keys of R. Need not to appear in all the candidate keys.
Ex: AB, BC, CD are candidate keys of R(ABCD). In the FDs set one attribute may not be part of all the FDs.
Question 51
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  
A
some dependent.
B
all dependents.
C
some of his/her dependents.
D
all of his/her dependents.
       Database-Management-System       ER-Model       Gate 2014 Set -03
Question 51 Explanation: 
The inner query selects the employees whose age is less than or equal to at least one of his dependents. So, subtracting from the set of employees, gives employees whose age is greater than all of his dependents.
Question 52
Consider the following relational schema: employee(empId,empName,empDept) customer(custId,custName,salesRepId,rating) salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return? SELECT empName FROM employee E WHERE NOT EXISTS (SELECT custId FROM customer C WHERE C.salesRepId = E.empId AND(GATEC.rating<>’GOOD’);
A
Names of all the employees with at least one of their customers having a ‘GOOD’ rating.
B
Names of all the employees with at most one of their customers having a ‘GOOD’ rating.
C
Names of all the employees with none of their customers having a ‘GOOD’ rating.
D
Names of all the employees with all their customers having a ‘GOOD’ rating.
       Database-Management-System       SQL       Gate 2014 Set -03
Question 52 Explanation: 

The inner query i.e., ② represents all customers having other than ‘GOOD’ while the entire query represents name of all employees with all their customers having a ‘good rating’.
Question 53
An index is clustered, if
A
it is on a set of fields that form a candidate key.
B
it is on a set of fields that include the primary key.
C
the data records of the file are organized in the same order as the data entries of the index.
D
the data records of the file are organized not in the same order as the data entries of the index.
       Database-Management-System       B+-Trees       Gate 2013
Question 53 Explanation: 
Option (A) and Option (B) are not correct because, index can be created using any column or combination of columns, which need not be unique.
Basically, Indexed column is used to sort the rows of table. Whole data record of file is sorted using index so the correct option is (C).
Question 54
Consider the following releation schema        
A
I, II, III and IV
B
I, II and III only
C
I, II and IV only
D
II, III and IV only
       Database-Management-System       ER-Model       Gate 2013
Question 54 Explanation: 
Four queries given in SQL, RA, TRC and DRC and all four statements will retrieve the required information.
Question 55
A
3
B
4
C
5
D
6
       Database-Management-System       Normalization       Gate 2013
Question 55 Explanation: 
 The attribute D is not part of any FD's. So D can be a candidate key or it may be part of the candidate key.
 Now D+ = {D}.
 Hence we have to add A,B,C,E,F,G,H to D and check which of them are Candidate keys of size 2.
AD+ = {ABCDEFGH}
BD+ = {ABCDEFGH}
ED+ = {ABCDEFGH}
FD+ = {ABCDEFGH}
 But CD+, GD+ and HD+ does not give all the attributes hence CD, GD and HD are not candidate keys.
 Hence no. of candidate keys are 4: AD, BD, ED, FD.
Question 56
Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values.
A
in 1NF, but not in 2NF.
B
in 2NF, but not in 3NF.
C
in 3NF, but not in BCNF.
D
in BCNF.
       Database-Management-System       Normalization       Gate 2013
Question 56 Explanation: 
 The attribute D is not part of any FD's. So D can be a candidate key or it may be part of the candidate key.
 Now D+ = {D}.
 Hence we have to add A,B,C,E,F,G,H to D and check which of them are Candidate keys of size 2.
AD+ = {ABCDEFGH}
BD+ = {ABCDEFGH}
ED+ = {ABCDEFGH}
FD+= {ABCDEFGH}
 But CD+, GD+ and HD+ does not give all the attributes hence CD, GD and HD are not candidate keys.
 Here Candidate keys are AD, BD, ED and FD.
 A → BC, B → CFH and F → EG etc are partial dependencies.
 So given relation is in 1NF, but not in 2NF.
Question 57
Which of the following is TRUE?  
A
Every relation in 3NF is also in BCNF
B
A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R
C
Every relation in BCNF is also in 3NF
D
No relation can be in both BCNF and 3NF
       Database-Management-System       Normalization       Gate 2012
Question 57 Explanation: 
BCNF is a stronger version 3NF. So straight from definition of BCNF every relation in BCNF will also be in 3NF.
Question 58
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
       Database-Management-System       ER-Model       Gate 2012
Question 58 Explanation: 
Option (A): In ER-model, multivalued attribute of an entity can have more than one value.
Option (B): In ER model, the attribute which can be further broken down into some other attributes is called composite attribute.
Option (C): In Relational model, the intersection of one row and column should contain only one value. So, option (C) is INCORRECT.
Option (D): In Relational model, the intersection of one row and column should contain either exactly one value or NULL.
Question 59
which of the  following statements are TRUE about an SQL query? P:An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause Q:An SQL query can contain a HAVING clause even if it has a GROUP BY clause R: All attributes used in the GROUP BY clause must appear in the SELECT clause S: Not all attributes used in the GROUP BY clause need to appear in the SELECT clause
A
P and R
B
P and S
C
Q and R
D
Q and S
       Database-Management-System       SQL       Gate 2012
Question 59 Explanation: 
The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. The attributes used in GROUP BY clause must present in SELECT statement.
The HAVING Clause enables you to specify conditions that filter which group results appear in the results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. So, we cannot use HAVING clause without GROUP BY clause.
Question 60
Consider the following transactions with data items P and Q initialized to zero:
T1: read (P) ;
    read (Q) ;
    if P = 0 then Q : = Q + 1 ;
    write (Q) ;
T2: read (Q) ;
    read (P) ;
    if Q = 0 then P : = P + 1 ;
    write (P) ;
Any non-serial interleaving of T1 and T2 for concurrent execution leads to
A
a serializable schedule
B
a schedule that is not conflict serializable
C
a conflict serializable schedule
D
a schedule for which a precedence graph cannot be drawn
       Database-Management-System       Transactions       Gate 2012
Question 60 Explanation: 

The above schedule is not conflict serializable.
Question 61
Suppose R1(A, B) and R2(C, D) are two relation schemas. Let r1 and r2 be the corresponding relation instances. B is a foreign key that refers to C in R2. If data in r1 and r2 satisfy referential integrity constraints, which of the following is ALWAYS TRUE?
A
B (r1) - ∏C (r2) = ∅
B
C (r2) - ∏B (r1) = ∅
C
B (r1) = ∏C (r2)
D
B (r1) - ∏C (r2) ≠ ∅
       Database-Management-System       Relational-Algebra       Gate 2012
Question 61 Explanation: 
Referential integrity means, all the values in foreign key should be present in primary key.
So we can say that r2(C) is the superset of r1(B).
So (subset - superset) is always empty.
Question 62
Consider the following relations A, B, C. How many tuples does the result of the following relational algebra expression contain? Assume that the schema of A U B is the same as that of    
A
7
B
4
C
5
D
9
       Database-Management-System       Relational-Algebra       Gate 2012
Question 62 Explanation: 


Performs the cross product and selects the tuples whose A∙Id is either greater than 40 or C∙Id is less than 15. It yields:
Question 63
Consider the above tables A, B and C. How many tuples does the result of the following SQL query contains?
SELECT A.id 
FROM   A 
WHERE  A.age > ALL (SELECT B.age 
                    FROM   B 
                    WHERE  B. name = "arun")
   
A
4
B
3
C
0
D
1
       Database-Management-System       SQL       Gate 2012
Question 63 Explanation: 

First query (2) will be executed and 0 (no) rows will be selected because in relation B there is no Name ‘Arun’.
The outer query (1) results the follow and that will be the result of entire query now. (Because inner query returns 0 rows).
Question 64
Consider a relational table with a single record for each registered student with the following attributes.
1. Registration_Num: Unique registration number
   of each registered student
2. UID: Unique identity number, unique at the 
   national level for each citizen
3. BankAccount_Num: Unique account number at
   the bank. A student can have multiple accounts
   or join accounts. This attribute stores the 
   primary account number.
4. Name: Name of the student
5. Hostel_Room: Room number of the hostel 
Which one of the following option is INCORRECT?
   
A
BankAccount_Num is a candidate key
B
Registration_Num 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∪UID is also a superkey
       Database-Management-System       Candidate-Keys       Gate 2011
Question 64 Explanation: 
In case two students hold joint account then Bank Account_Num will not uniquely determine other attributes.
Question 65
Consider a relational table r with sufficient number of records, having attributes A1, A2,…, An and let 1 <= p <= n. Two queries Q1 and Q2 are given below. The database can be configured to do ordered indexing on Ap or hashing on Ap. Which of the following statements is TRUE?    
A
Ordered indexing will always outperform hashing for both queries
B
Hashing will always outperform ordered indexing for both queries
C
Hashing will outperform ordered indexing on Q1, but not on Q2
D
Hashing will outperform ordered indexing on Q2, but not on Q1.
       Database-Management-System       Relational-Algebra       Gate 2011
Question 65 Explanation: 
Hashing works well on the "equal" queries, while ordered indexing works well better on range queries.
For example, consider B+ tree, once you have searched a key in B+; you can find range of values via the block pointers pointing to another block of values on the leaf node level.
Question 66
Database table by name Loan_Records is given below.
Borrower    Bank_Manager   Loan_Amount
 Ramesh      Sunderajan     10000.00
 Suresh      Ramgopal       5000.00
 Mahesh      Sunderajan     7000.00
What is the output of the following SQL query?
SELECT Count(*) 
FROM  ( ( SELECT Borrower, Bank_Manager 
          FROM Loan_Records) AS S 
          NATURAL JOIN ( SELECT Bank_Manager, Loan_Amount 
                         FROM Loan_Records) AS T );
 
A
3
B
9
C
5
D
6
       Database-Management-System       SQL       Gate 2011
Question 66 Explanation: 
Question 67
Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1, Y=1) is inserted in the table. Let MX and My denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out?
SELECT Y FROM T WHERE X=7;
`  
A
127
B
255
C
129
D
257
       Database-Management-System       SQL       Gate 2011
Question 67 Explanation: 
Question 68
Consider a B+-tree in which the maximum number of keys in a node is 5. What is the minimum number of keys in any non-root node?
A
1
B
2
C
3
D
4
       Database-Management-System       B+-Trees       2010
Question 68 Explanation: 
― In B+ tree, the root contains minimum two block pointers and maximum ‘p’ block pointers.
― Here,
p = order
key = order – 1 = p – 1
― In the non-root node, the minimum no. of keys =⌈p/2⌉-1
― So, key = 5
order = 6
― So, minimum no. of keys in root node =⌈6/2⌉-1=2
Question 69
A relational schema for a train reservation database is given below. Passenger (pid, pname, age) Reservation (pid, class, tid)
Table: Passenger
pid   pname   age
-----------------
 0    Sachin   65
 1    Rahul    66
 2    Sourav   67
 3    Anil     69

Table : Reservation
pid  class  tid
---------------
 0    AC   8200
 1    AC   8201
 2    SC   8201
 5    AC   8203
 1    SC   8204
 3    AC   8202
What pids are returned by the following SQL query for the above instance of the tables?
SLECT pid
FROM Reservation ,
WHERE class ‘AC’ AND
    EXISTS (SELECT *
       FROM Passenger
       WHERE age > 65 AND
       Passenger. pid = Reservation.pid)
   
A
1, 0
B
1, 2
C
1, 3
D
1, 5
       Database-Management-System       SQL       2010
Question 69 Explanation: 
Passenger:

― 1, 3 Pids are returned
Question 70
Which of the following concurrency control protocols ensure both conflict serializability and freedom from deadlock? I. 2-phase locking II. TIme-stamp ordering
A
I only
B
II only
C
Both I and II
D
Neither I nor II
       Database-Management-System       Transactions       2010
Question 70 Explanation: 
― Two-phase locking protocol (2PLP) ensures the conflict serializable schedule but it may not free from deadlock.
― Timestamp ordering protocol ensures conflict serializability and free from deadlock.
Question 71
Consider the following schedule for transactions T1, T2 and T3: Which one of the schedules below is the correct serialization of the above?  
A
T1 → T3 → T2
B
T2 → T1 → T3
C
T2 → T3 → T1
D
T3 → T1 → T2
       Database-Management-System       Transactions       2010
Question 71 Explanation: 
The given schedule is

― Precedence graph for the above schedule is,

― From the precedence graph the correct serialization is,
Question 72
Which of the following functional dependencies hold for relations R(A, B, C) and S(B, D, E):
B -> A
A -> C
The relation R contains 200 tuples and the rel ation S contains 100 tuples. What is the maximum number of tuples possible in the natural join of R and S (R natural join S)  
A
100
B
200
C
300
D
2000
       Database-Management-System       Relational-Algebra       2010
Question 72 Explanation: 
Given
R(A, B, C) – 200 tuples
S(B, D, E) – 100 tuples
FD’s:
B → A
A → C
― ‘B’ is primary key for R and foreign key of S from the given FDs.
― Maximum tuples in natural join of R and S is min(200, 100) = 100.
Question 73
Consider two transactions T1 and T2, and four schedules S1, S2, S3, S4 of T1 and T2 as given below: Which of the above schedules are conflict-serializable?  
A
S1 and S2
B
S2 and S3
C
S3 only
D
S4 only
       Database-Management-System       Transactions       2009
Question 73 Explanation: 
S1 has a cycle from T1→T2 and T2→T1 Schedule S2,

Dependency graph is,

So, there is no cycle.
Schedule S3,

Dependency graph is,

S4 also has a cycle T1→T2 and T2→T1.
So, S2 and S3 are conflict serializable.
Question 74
The following key values are inserted into a B+ - tree in which order of the internal nodes is 3, and that of the leaf nodes is 2, in the sequence given below. The order of internal nodes is the maximum number of tree pointers in each node, and the order of leaf nodes is the maximum number of data items that can be stored in it. The B+ - tree is initially 10, 3, 6, 8, 4, 2, 1 The maximum number of times leaf nodes would get split up as a result of these insertions is  
A
2
B
3
C
4
D
5
       Database-Management-System       B+-Trees       2009
Question 74 Explanation: 
Insert 10:

Insert 3:

Insert 6:

Insert 4:

Insert 2:

Insert 1:

So, total splits are 3.
Question 75
Let R and S be relational schemes such that R={a,b,c} and S={c}. Now consider the following queries on the database: Which of the above queries are equivalent?
A
I and II
B
I and III
C
II and IV
D
III and IV
       Database-Management-System       Relational-Calculus       2009
Question 75 Explanation: 
R={a,b,c}
S={c}

It looks like Division operator. Since a division operator in E(A,B)/ P(B) will be equal to

Now replacing A=R-S P=r
B=S
E=r
we will get,

equivalent to I
∴ It is equivalent to division operator.
⇒ r(R-S,S)/r(S)

This logical statement means that
① Select t(R-S) from r such that
② for all tuples U in S,
③ there exists a tuple V in r, such that
④ U=V[S] & t=V[R-S]
A(x,y) & B(y)
A/B = {(x) | ∃(x,y)∈A(y)∈B}
which means that A/B contains all x tuples, such that for every tuple in B, there is an xy tuple in A.
So, this is just equivalent to I.

This logical statement means that
① Select t(R-S) from r such that
② for all tuples V in r,
③ there exists a tuple U in r, such that
④ U=V[S] & t=V[R-S]
⇒ Select (R-S) values from r, where the S value is in (r/r), which will be true only if S in r is a foreign key referring to S is r.

This selects (a,b) from all tuples from r which has an equivalent value in S.
Question 76
Consider the following relational schema: Suppliers(sid:integer, sname:string, city:string, street:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real) Consider the following relational query on the above database SELECT         S.sname FROM            Suppliers S WHERE S.sid NOT IN (SELECT C.sid FROM Catalog C WHERE C.pid NOT (SELECT P.pid FROM    Parts P WHERE P.color<> 'blue')) Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?
A
Find the names of all suppliers who have supplied a non-blue part.
B
Find the names of all suppliers who have not supplied a non-blue part.
C
Find the names of all suppliers who have supplied only blue parts.
D
Find the names of all suppliers who have not supplied only blue parts.
       Database-Management-System       SQL       2009
Question 76 Explanation: 



If we execute the given query the output will be S3 and S4 i.e., names of all suppliers who didn’t supply blue parts which is option (A).
Option (D) says names of suppliers who didn’t supply only blue parts that means, supplier should supply all other parts for sure and shouldn’t supply blue part.
Question 77
Consider the following relational schema: Suppliers(sid:integer, sname:string, city:string, street:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real) Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema?  
A
The schema is in BCNF
B
The schema is in 3NF but not in BCNF
C
The schema is in 2NF but not in 3NF
D
The schema is not in 2NF
       Database-Management-System       Normalization       2009
Question 77 Explanation: 
From the given data the FDs will be
(Sid, Street) → Sname
As Sid is a primary key, then
(Sid, Street) will be super key.
Hence, it is in BCNF.
Question 78
Which of the following tuple relational calculus expression(s) is/are equivalent to  
A
I only
B
II only
C
III only
D
III and IV only
       Database-Management-System       Relational-Calculus       Gate-2008
Question 78 Explanation: 
Demorgan law:
∀xP(x)≡∼∃x(∼P(x))
∼∀x(∼P(x))≡∃x(P(x))
Given: ∀t ∈ r(P(t))------ (1)
As per Demorgan law
(1) ⇒ ∼∃t ∈ r(∼P(t))
which is option (III).
Question 79
A clustering index is defined on the fields which are of type
A
non-key and ordering
B
non-key and non-ordering
C
key and ordering
D
key and non-ordering
       Database-Management-System       Indexing       Gate-2008
Question 79 Explanation: 
Create index files, fields could be non-key attributes and which are in ordered form so as to form clusters easily.
Question 80
A B-tree of order 4 is built from scratch by 10 successive insertions. What is the maximum number of node splitting operations that may take place?
A
3
B
4
C
5
D
6
       Database-Management-System       B-Trees       Gate-2008
Question 80 Explanation: 
Let's take 10 successive key values,
{1,2,3,...,10} which can cause maximum possi ble splits.
1, 2, 3 →

4 →

5 →

6 →

7 →

8 →

9 →

10 →
Question 81
Let R and S be two relations with the following schema
A
Only I and II
B
Only I and III
C
Only I, II and III
D
Only I, III and IV
       Database-Management-System       Relational-Algebra       Gate-2008
Question 81 Explanation: 
Natural join is based on the common columns of the two tables.
We have two common columns in 'R' and 'S' which are 'P' and 'Q'.
(I) Both P and Q are used while doing the join, i.e., both P and Q are used to filter.
(II) Q is not used here for filtering. Natural join is done on all P's from R and all P's from S. So different from option (I).
(III) Through venn diagram it can be proved that A∩B = A - (A-B).
So through above formula we can say that (III) and (IV) are equivalent.
So finally (I), (III) and (IV) are equivalent.
Question 82
Consider the following relational schemes for a library database: Book(Title, Author, Catalog_ no, Publisher, Year, Pr ice) Collection (Title, Author, Catalog_ no) with in the following functional dependencies: I.Title Author ® Catalog_no II.Catalog_no ® Title Author Publisher Year III.Publisher Title Year ® Pr ice Assume {Author, Title} is the key for both schemes. Which of the following statements is true?  
A
Both Book and Collection are in BCNF
B
Both Book and Collection are in 3NF only
C
Book is in 2NF and Collection is in 3NF
D
Both Book and Collection are in 2NF only
       Database-Management-System       Normalization       Gate-2008
Question 82 Explanation: 
Given that
Book(Title, Author, Catalog_no, Publisher, Year, Price)
Collection(Title, Author, Catalog_no)
I) Title Author ⟶ Catalog_no ⟶ BCNR
II) Catalog_no ⟶ Title, Author, Publisher, Year ⟶ 3NF
III) Publisher Title Year ⟶Price ⟶ 2NF Book’s in 2NF
Collection is in 3NF.
Question 83
Consider a file of 16384 records. Each record is 32 bytes long and its key field is of size 6 bytes. The file is ordered on a non-key field, and the file organization is unspanned. The file is stored in a file system with block size 1024 bytes, and the size of a block pointer is 10 bytes. If the secondary index is built on the key field of the file, and a multi-level index scheme is used to store the secondary index, the number of first-level and second-level blocks in the multi-level index are respectively
A
8 and 0
B
128 and 6
C
256 and 4
D
512 and 5
       Database-Management-System       Indexing       Gate-2008
Question 83 Explanation: 
Total no. of records in a file = 16384
Record size = 32 bytes
Key size = 6 bytes
Block pointer size = 10 bytes
Block size of file system = 1024 bytes
Record (or) index entry size = 10+6 = 16 bytes
In first level no. of blocks =No. of records in a file/Block size=16384 * 16/1024=256
In second level, it have = 256*16 entries
In second level, no. of blocks =No. of entries/Block size =256*16/1024=4
Question 84
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
       Database-Management-System       ER-Model       Gate-2008
Question 84 Explanation: 
➝ M, P are entities so they require individual tables.
➝ Here N is a Weak entity, but it need to modify the primary key of P such as P1
M = {M1, M2, M3, P1}
P = {P1, P2}
N = {N1, N2, P1}
➝ Relationship set has its own attribute, then no need to create a separate table.
➝ Finally we require 3 minimum tables to represent M,P,N,R1,R2.
Question 85
Consider the following ER diagram 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}
       Database-Management-System       ER-Model       Gate-2008
Question 85 Explanation: 
Possible set of attributes is
For M = {M1, M2, M3, P1}
P = {P1, P2}
N = {N1, N2, P1}
Question 86
Let R (A, B, C, D) be a relational schema with the following functional dependencies:
A → B, B → C,
C → D and D → B. 

The decomposition of R into 
(A, B), (B, C), (B, D)
 
A
gives a lossless join, and is dependency preserving
B
gives a lossless join, but is not dependency preserving
C
does not give a lossless join, but is dependency preserving
D
does not give a lossless join and is not dependency preserving
       Database-Management-System       Functional-Dependency       Gate 2008-IT
Question 86 Explanation: 
(A, B) (B, C) - common attribute is (B) and due to B→C, B is a key for (B, C) and hence ABC can be losslessly decomposedinto (A, B)and (B, C).
(A, B, C) (B, D) - common attributes is B and B→D is a FD (via B→C, C→D), and hence, B is a key for (B, D). So, decomposition of (A, B, C, D) into (A, B, C) (B, D) is lossless.
Thus the given decomposition is lossless.
The given decomposition is also dependency preserving as the dependencies A→B is present in (A, B), B→C is present in (B, C), D→B is present in (B, D) and C→D is indirectly present via C→B in (B, C) and B→D in (B, D).
Question 87
Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional depen­dencies are known to hold: AB → CD, DE → P, C → E, P → C and B → G. The relational schema R is
A
in BCNF
B
in 3NF, but not in BCNF
C
in 2NF, but not in 3NF
D
not in 2NF
       Database-Management-System       Normalization       Gate 2008-IT
Question 87 Explanation: 
Candidate key is AB.
Since there is a partial dependency B→G.
So the relational schema R is Not in 2NF.
Question 88
Consider the following three schedules of transactions T1, T2 and T3. [Notation: In the following NYO represents the action Y (R for read, W for write) performed by transac­tion N on object O.]
(S1)	2RA	2WA	3RC	2WB	3WA	3WC	1RA	1RB	1WA	1WB
(S2)	3RC	2RA	2WA	2WB	3WA	1RA	1RB	1WA	1WB	3WC
(S3)	2RA	3RC	3WA	2WA	2WB	3WC	1RA	1RB	1WA	1WB
Which of the following statements is TRUE?  
A
S1, S2 and S3 are all conflict equivalent to each other
B
No two of S1, S2 and S3 are conflict equivalent to each other
C
S2 is conflict equivalent to S3, but not to S1
D
S1 is conflict equivalent to S2, but not to S3
       Database-Management-System       Transactions       Gate 2008-IT
Question 88 Explanation: 
Two schedules are conflict equivalent when the precedence graph are isomorphic.
For S1:

For S2:

For S3:

Hence, S1 is conflict equivalent to S2, but not to S3.
Question 89
Student (school-id, sch-roll-no, sname, saddress) School (school-id, sch-name, sch-address, sch-phone) Enrolment(school-id sch-roll-no, erollno, examname) ExamResult(erollno, examname, marks) What does the following SQL query output?
SELECT  sch-name, COUNT (*)
FROM    School C, Enrolment E, ExamResult R
WHERE   E.school-id = C.school-id
AND
E.examname = R.examname AND E.erollno = R.erollno
AND
R.marks = 100 AND S.school-id IN (SELECT school-id
                                FROM student
                                GROUP BY school-id
                                 HAVING COUNT (*) > 200)
GROUP By school-id
 /* Add code here. Remove these lines if not writing code */
A
for each school with more than 200 students appearing in exams, the name of the school and the number of 100s scored by its students
B
for each school with more than 200 students in it, the name of the school and the number of 100s scored by its students
C
for each school with more than 200 students in it, the name of the school and the number of its students scoring 100 in at least one exam
D
nothing; the query has a syntax error
       Database-Management-System       SQL       Gate 2008-IT
Question 89 Explanation: 
If select clause consist of aggregate and non-aggregate columns, all non-aggregate columns in the select clause must appear in Group By clause. But in this Group By clause consists school-id instaed of school-name.
Question 90
Student (school-id, sch-roll-no, sname, saddress) School (school-id, sch-name, sch-address, sch-phone) Enrolment(school-id sch-roll-no, erollno, examname) ExamResult(erollno, examname, marks) What does the following SQL query output?
SELECT  sch-name, COUNT (*)
FROM    School C, Enrolment E, ExamResult R
WHERE   E.school-id = C.school-id
AND
E.examname = R.examname AND E.erollno = R.erollno
AND
R.marks = 100 AND S.school-id IN (SELECT school-id
                                FROM student
                                GROUP BY school-id
                                 HAVING COUNT (*) > 200)
GROUP By school-id
 /* Add code here. Remove these lines if not writing code */
A
The empty set
B
schools with more than 35% of its students enrolled in some exam or the other
C
schools with a pass percentage above 35% over all exams taken together
D
schools with a pass percentage above 35% over each exam
       Database-Management-System       Relational-Calculus       Gate 2008-IT
Question 90 Explanation: 
Query having the division with
{ x | x ∈ Enrolment ∧ x . school-id = t } | * 100 > 35 }
This is school with enrollment % is 35 or above.
Question 91
Information about a collection of students is given by the relation studinfo(studId, name, sex). The relation enroll(studId, courseId) gives which student has enrolled for (or taken) that course(s). Assume that every course is taken by at least one male and at least one female student. What does the following relational algebra expression represent?  
A
Courses in which all the female students are enrolled.
B
Courses in which a proper subset of female students are enrolled.
C
Courses in which only male students are enrolled.
D
None of the above
       Database-Management-System       Relational-Algebra       Gate-2007
Question 91 Explanation: 
Option A: It is not result the all the female students who are enrolled. So option A is false.
Option B: Yes, True. It selects the proper subset of female students which are enrolled because in the expression we are performing the cartesian product.
Option C: False. It doesn’t shows (or) display the males students who are enrolled.
Question 92
Consider the relation employee(name, sex, supervisorName) with name as the key. supervisorName gives the name of the supervisor of the employee under consideration. What does the following Tuple Relational Calculus query produce?  
A
Names of employees with a male supervisor.
B
Names of employees with no immediate male subordinates.
C
Names of employees with no immediate female subordinates.
D
Names of employees with a female supervisor.
       Database-Management-System       Relational-Calculus       Gate-2007
Question 92 Explanation: 
The given Tuple Relational calculus produce names of employees with no immediate female subordinates.
Question 93
Which one of the following statements if FALSE?
A
Any relation with two attributes is in BCNF
B
A relation in which every key has only one attribute is in 2NF
C
A prime attribute can be transitively dependent on a key in a 3NF relation
D
A prime attribute can be transitively dependent on a key in a BCNF relation
       Database-Management-System       Normalization       Gate-2007
Question 93 Explanation: 
Rules for BCNF is:
i) It is in 3NF.
ii) For any dependency X→ Y
where X is a super key.
iii) Functional dependency has been removed.
Option D is false.
→ Because a prime attribute can’t be transitive dependent on a key in a BCNF relation.
Question 94
The order of a leaf node in a B+- tree is the maximum number of (value, data record pointer) pairs it can hold. Given that the block size is 1K bytes, data record pointer is 7 bytes long, the value field is 9 bytes long and a block pointer is 6 bytes long, what is the order of the leaf node?
A
63
B
64
C
67
D
68
       Database-Management-System       B+-Trees       Gate-2007
Question 94 Explanation: 
Disk Block size = 1 KBytes = 210 Bytes = 1024 Bytes
Data recorder pointer size, r = 7 bytes
Value size, v = 9 bytes
Disk Block pointer, P = 6 bytes
⇒ Order of leaf be m, then
r*m+v*m+p <= 1024
7m+9m+6 <= 1024
16m <= 1024 - 6
m <= 63
Question 95
Consider the following schedules involving two transactions. Which one of the following statements is TRUE?  
A
Both S1 and S2 are conflict serializable.
B
S1 is conflict serializable and S2 is not conflict serializable.
C
S1 is not conflict serializable and S2 is conflict serializable.
D
Both S1 and S2 are not conflict serializable.
       Database-Management-System       Transactions       Gate-2007
Question 95 Explanation: 

In precedence graph of S1 since cycle is formed so not conflict serializable.
But in precedence graph of S2 No cycle is formed so it is conflict serializable.
Question 96

Consider a selection of the form σA≤100(r), where r is a relation with 1000 tuples. Assume that the attribute values for A among the tuples are uniformly distributed in the interval [0, 500]. Which one of the following options is the best estimate of the number of tuples returned by the given selection query ?

A
50
B
100
C
150
D
200
       Database-Management-System       Relational-Algebra       Gate 2007-IT
Question 96 Explanation: 
σA≤100(r), r has 1000 tuples.
Values for A among the tuples are uniformly distributed in the interval [0, 500]. This can be split to 5 mutually exclusive and exhaustive intervals of same width of 100 ([0-100], [101-200], [201-300], [301-400], [401-500], 0 makes the first interval larger - this must be typ0 in this question) and we can assume all of them have same number of values due to uniform distribution. So no. of tuples with A value in first interval should be,
Total no. of tuples/5 = 1000/5 = 200
Question 97
 
A
B
C
D
       Database-Management-System       Transactions       Gate 2007-IT
Question 97 Explanation: 
For strict 2PL the requirements are,
i) Exclusive locks should be released after the commit.
ii) No locking can be done after the first unlock.
(A) Wrong because to write x you need Exclusive Lock.
(B) Wrong because violating the 1st requirement.
(C) Correct.
(D) Wrong because violating the 1st requirement.
Question 98
Consider the following implications relating to functional and multivalued dependencies given below, which may or may not be correct. i. If A ↠ B and A ↠ C then A → BC ii. If A → B and A → C then A ↠ BC iii. If A ↠ BC and A → B then A → C iv. If A → BC and A → B then A ↠ C Exactly how many of the above implications are valid?
A
0
B
1
C
2
D
3
       Database-Management-System       Multi-valued-Dependencies       Gate 2007-IT
Question 98 Explanation: 
The rule is
if A→B then A→→B holds but reverseis not true.
So,
(i) False.
(ii) True.
(iii) False.
(iv) True.
Question 99
Consider the following relation schemas :
    • b-Schema = (b-name, b-city, assets)
    • a-Schema = (a-num, b-name, bal)
    • d-Schema = (c-name, a-number)
Let branch, account and depositor be respectively instances of the above schemas. Assume that account and depositor relations are much bigger than the branch relation. Consider the following query: Пc-name (σb-city = "Agra" ⋀ bal < 0 (branch ⋈ (account ⋈ depositor) Which one of the following queries is the most efficient version of the above query ?  
A
Пc-namebal < 0b-city = “Agra” branch ⋈ account) ⋈ depositor)
B
Пc-nameb-city = “Agra”branch ⋈ (σbal < 0 account ⋈ depositor))
C
Пc-nameb-city = “Agra” branch ⋈ σb-city = “Agra” ⋀ bal < 0 account) ⋈ depositor)
D
Пc-nameb-city = “Agra” ⋀ bal < 0 account ⋈ depositor))
       Database-Management-System       Relational-Algebra       Gate 2007-IT
Question 99 Explanation: 
Answer should be (A) and not (B), because we are doing a join between two massive tables whereas in (A) we are doing join between relatively smaller table and larger one and the output that this inner table gives (which is smaller in comparision to join that we are doing in (B)) is used for join with depositor table with the selection condition.
Options (C) and (D) are invalid as there is no b-city column in a-schema.
Question 100
 
A
1
B
2
C
3
D
4
       Database-Management-System       B+-Trees       Gate 2007-IT
Question 100 Explanation: 
It is B+ Tree.
After inserting K15 we get,

Now, we insert K25, which gives

So, we see in the final tree only (K20, K25) is present. Hence, 1 (Answer).
Question 101
 
A
Statements (i) and (ii) are true
B
Statements (ii) and (iii) are true
C
Statements (iii) and (i) are true
D
All the statements are false
       Database-Management-System       B+-Trees       Gate 2007-IT
Question 101 Explanation: 
The B+ tree in previous question we get was,

Now after deleting 50 we get B+ tree as

Hence, correct statement is only (i) & (ii).
Question 102
Consider the following log sequence of two transactions on a bank account, with initial balance 12000, that transfer 2000 to a mortgage payment and then apply a 5% interest.
  1. T1 start
  2. T1 B old=12000 new=10000
  3. T1 M old=0 new=2000
  4. T1 commit
  5. T2 start
  6. T2 B old=10000 new=10500
  7. T2 commit
Suppose the database system crashes just before log record 7 is written. When the system is restarted, which one statement is true of the recovery procedure?
A
We must redo log record 6 to set B to 10500.
B
We must undo log record 6 to set B to 10000 and then redo log records 2 and 3.
C
We need not redo log records 2 and 3 because transaction T1 has committed.
D
We can apply redo and undo operations in arbitrary order because they are idempotent.
       Database-Management-System       Transactions       Gate-2006
Question 102 Explanation: 
When the database system crashes after the transactions have committed then we need to redo the log records. And if the database system crashes before the transactions have committed then we need to undo the log records.
So from above theory we can say that option (B) is the correct answer.
Question 103

Consider the relation enrolled(student, course) in which (student, course) is the primary key, and the relation paid(student, amount), where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Assume that amounts 6000, 7000, 8000, 9000 and 10000 were each paid by 20% of the students. Consider these query plans (Plan 1 on left, Plan 2 on right) to "list all courses taken by students who have paid more than x".

A disk seek takes 4ms, disk data transfer bandwidth is 300 MB/s and checking a tuple to see if amount is greater than x takes 10 micro-seconds. Which of the following statements is correct?

A
Plan 1 and Plan 2 will not output identical row sets for all databases.
B
A course may be listed more than once in the output of Plan 1 for some databases.
C
For x = 5000, Plan 1 executes faster than Plan 2 for all databases.
D
For x = 9000, Plan I executes slower than Plan 2 for all databases.
       Database-Management-System       SOL       Gate-2006
Question 103 Explanation: 
Both plans are require the tables such as courses and enrolled to access the disks takes same time for both plans.
While analyze the plan1 and plan2 does lesser number of comparisons compared to plan1.
i) The join table consists of two tables will have more rows. So comparisons are needed to find amount greater than x.
ii) Join operation consists of more number of comparisons as the second table will have more rows in plan2 compared to plan1.
Question 104
The following functional dependencies are given:
AB \rightarrow  CD, AF \rightarrow  D, DE \rightarrow  F, C \rightarrow G , F \rightarrow  E, G \rightarrow  A
Which one of the following options is false?
A
{CF}+ = {ACDEFG}
B
{BG}+ = {ABCDG}
C
{AF}+ = {ACDEFG}
D
{AB}+ = {ABCDFG}
       Database-Management-System       Functional-Dependency       Gate-2006
Question 104 Explanation: 
AB → CD
AF → D
DE → F
C → G
F → E
G → A
CF+ = {G,E,A,D,C,F} = {A,C,D,E,F,G} (✔️)
BG+ = {B,G,A,C,D} = {A,B,C,D,G} (✔️)
AF+ = {D,E,A,F} = {A,D,E,F} (❌)
AB+ = {A,B,C,D,G} (❌)
Question 105
Consider the relation account (customer, balance) where customer is a primary key and there are no null values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank 1. ties are not broke but ranks are skipped: if exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned
Query1:
  select A.customer, count(B.customer)
  from account A, account B
  where A.balance <=B.balance
  group by A.customer

Query2:
  select A.customer, 1+count(B.customer)
  from account A, account B
  where A.balance < B.balance
  group by A.customer
Consider these statements about Query1 and Query2.
1. Query1 will produce the same row set as Query2 for 
   some but not all databases.
2. Both Query1 and Query2 are correct implementation 
   of the specification
3. Query1 is a correct implementation of the specification
   but Query2 is not
4. Neither Query1 nor Query2 is a correct implementation
   of the specification
5. Assigning rank with a pure relational query takes 
   less time than scanning in decreasing balance order 
   assigning ranks using ODBC.
Which two of the above statements are correct?
A
2 and 5
B
1 and 3
C
1 and 4
D
3 and 5
       Database-Management-System       SQL       Gate-2006
Question 105 Explanation: 
Query 1 & 2 gives the same output for all not all data based its true because the salaries may be distinct variables. Statement 1 is true.
The customer with largest balance gets rank 1. Ties are broken with ranks are skipped.
So, both queries may doesn’t give same output. Statement 4 is correct.
Question 106
Consider the relation "enrolled(student, course)" in which (student, course) is the primary key, and the relation "paid(student, amount)" where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Given the following four queries:
Query1: select student from enrolled where 
        student in (select student from paid)
Query2: select student from paid where 
        student in (select student from enrolled)
Query3: select E.student from enrolled E, paid P 
         where E.student = P.student
Query4:  select student from paid where exists
        (select * from enrolled where enrolled.student
         = paid.student)
Which one of the following statements is correct?
A
All queries return identical row sets for any database.
B
Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets.
C
There exist databases for which Query3 returns strictly fewer rows than Query2.
D
There exist databases for which Query4 will encounter an integrity violation at runtime.
       Database-Management-System       SQL       Gate-2006
Question 106 Explanation: 
Consider Table examples as:

Query1 : Output
abcd
abcd
PQRS
Query 2 : Output
abcd
PQRS
Query 3 : Output
abcd
PQRS
Query 4 : Output
abcd
PQRS
Query 2 & Query 4 gives same results but Query 1 & Query 3 gives different results.
Question 107

Consider the relations r1(P, Q, R) and r2(R, S, T) with primary keys P and R respectively. The relation r1 contains 2000 tuples and r2 contains 2500 tuples. The maximum size of the join r1⋈ r2 is :

A
2000
B
2500
C
4500
D
5000
       Database-Management-System       Relational-Algebra       Gate 2006-IT
Question 107 Explanation: 
r1⋈ r2 is a join opertaion done on the common attribute R. So this can have 2000 tuples.
Question 108
Which of the following relational query languages have the same expressive power?
  1. Relational algebra
  2. Tuple relational calculus restricted to safe expressions
  3. Domain relational calculus restricted to safe expressions
 
A
2 and 3 only
B
1 and 2 only
C
1 and 3 only
D
1, 2 and 3
       Database-Management-System       Relational-Query-Languages       Gate 2006-IT
Question 108 Explanation: 
Given all three languages have same expressive power.
Question 109

In a database file structure, the search key field is 9 bytes long, the block size is 512 bytes, a record pointer is 7 bytes and a block pointer is 6 bytes. The largest possible order of a non-leaf node in a B+ tree implementing this file structure is

A
23
B
24
C
34
D
44
       Database-Management-System       B+-Trees       Gate 2006-IT
Question 109 Explanation: 
From the structure of B+ tree we can get this question:
n × p + (n - 1) × k ≤ B (for non-leaf node)
Here, n = order, p = tree/block/index pointer, B = size of block
So,
n × p + (n - 1) × k ≤ B
n × 6 + (n - 1) × 9 ≤ 512
n ≤ 34.77
∴ n = 34
Question 110
Consider a relation R with five attributes V, W, X, Y, and Z. The following functional dependencies hold: VY→ W, WX → Z, and ZY → V. Which of the following is a candidate key for R?
A
VXZ
B
VXY
C
VWXY
D
VWXYZ
       Database-Management-System       Functional-Dependency       Gate 2006-IT
Question 110 Explanation: 
As we can see that attribute XY do not appear in RHS of an FD, they need to be a part of key.
Candidate keys are
VXY, WXY, ZXY
Question 111
Consider a database with three relation instances shown below. The primary keys for the Drivers and Cars relation are did and cid respectively and the records are stored in ascending order of these primary keys as given in the tables. No indexing is available  
A
Karthikeyan, Boris
B
Sachin, Salman
C
Karthikeyan, Boris, Sachin
D
Schumacher, Senna
       Database-Management-System       SQL       Gate 2006-IT
Question 111 Explanation: 
For colour = "Red"
did = {22, 22, 31, 31, 64}
For colour = "Green"
did = {22, 31, 74}
Intersection of Red and Green will be = {22, 31}, which is Karthikeyan and Boris.
Question 112
Consider a database with three relation instances shown below. The primary keys for the Drivers and Cars relation are did and cid respectively and the records are stored in ascending order of these primary keys as given in the tables. No indexing is available Let n be the number of comparisons performed when the above SQL query is optimally executed. If linear search is used to locate a tuple in a relation using primary key, then n lies in the range
A
36 - 40
B
44 - 48
C
60 - 64
D
100 - 104
       Database-Management-System       SQL       Gate 2006-IT
Question 112 Explanation: 
(4) for taking red cars with (20) comparisions for did and (4) for finding green cars with (10) for did.
red did : 22, 31, 64
green did : 22, 31, 74
(6) for intersection
(1) for searching 22 in driver relation, and (3) for searching 31.
Total: 38 + 6 + 4 = 48
Question 113
Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional dependencies hold: {A → B, BC → D, E → C, D → A}. What are the candidate keys of R?
A
AE, BE
B
AE, BE, DE
C
AEH, BEH, BCH
D
AEH, BEH, DEH
       Database-Management-System       Functional-Dependency       Gate-2005
Question 113 Explanation: 
Using the given functional dependencies and looking at the dependent attributes, E and H are not dependent on any. So, they must be a part of any candidate key.
So only option (D) contains E and H as part of candidate key.
Question 114
The relation book (title, price) contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list?
  select title
  from book as B
  where (select count(*)
     from book as T
     where T.price > B.price) < 5
A
Titles of the four most expensive books
B
Title of the fifth most inexpensive book
C
Title of the fifth most expensive book
D
Titles of the five most expensive books
       Database-Management-System       SQL       Gate-2005
Question 114 Explanation: 
Which results titles of the five most expensive books.
The where clause of outer query will be true for 5 most expensive books.
Question 115
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)
       Database-Management-System       Referential-Integrity       Gate-2005
Question 115 Explanation: 
If (2,4) is deleted and that results foreign key of value with 2 also deleted i.e., (5,2), (7,2) are also delete. And again deleting Primary keys (5), (7) means delete the foreign key value (5,7) that results (9,5) also deleted.
Totally (5,2), (7,2), (9,5) are also deleted.
Question 116
Let r be a relation instance with schema R = (A, B, C, D). We define r1 = ΠA,B,C (R) and r2 = ΠA,D (R). Let s = r1 * r2 where * denotes natural join. Given that the decomposition of r into r1 and r2 is lossy, which one of the following is TRUE?
A
s ⊂ r
B
r ∪ s = r
C
r ⊂ s
D
r * s = s
       Database-Management-System       Relational-Algebra       Gate-2005
Question 116 Explanation: 
Let us consider an example:
Table r: R(A, B, C, D)

Table r1: ΠA,B,C(R)

Table r2: ΠA,D(R)

S = r1 * r2 (* denotes natural join)
Table S:

Table r ⊂ Table S
⇒ r ⊂ S
Question 117
Which one of the following is a key factor for preferring B+ -trees to binary search trees for indexing database relations?
A
Database relations have a large number of records
B
Database relations are sorted on the primary key
C
B+- trees require less memory than binary search trees
D
Data transfer from disks is in blocks
       Database-Management-System       B+-Trees       Gate-2005
Question 117 Explanation: 
B+ trees is a balanced tree and it can store multiple keys in each node of B+ tree.
Thus, the data can be transferred through blocks in B+ trees. This can be used for indexing the database relations.
Question 118
Which one of the following statements about normal forms is FALSE?  
A
BCNF is stricter than 3NF
B
Lossless, dependency-preserving decomposition into 3NF is always possible
C
Lossless, dependency-preserving decomposition into BCNF is always possible
D
Any relation with two attributes is in BCNF
       Database-Management-System       Normalization       Gate-2005
Question 118 Explanation: 
Option A: BCNF is stricter than 3NF. In this all redundancy based on functional dependency has been removed.
Option B: Lossless, dependency preserving decomposition into 3NF is always possible.
Option C: It is false.
It is not possible to have dependency preserving in BCNF decomposition.
→ Let take an example, 3NF can't be decomposed into BCNF.
Option D: It is true.
Let consider t wo attributes (X, Y).
If (X→Y), X is a candidate key. It is in BCNF and vice-versa.
Question 119
Consider the entities 'hotel room', and 'person' with a many to many relationship 'lodging' as shown below: If we wish to store information about the rent payment to be made by person (s) occupying different hotel rooms, then this information should appear as an attribute of  
A
Person
B
Hotel Room
C
Lodging
D
None of these
       Database-Management-System       ER-Model       Gate 2005-IT
Question 119 Explanation: 
The information should appear as an attribute of lodging, because this is the only common attribute that relating to the hotel room and person.
Question 120
A table has fields Fl, F2, F3, F4, F5 with the following functional dependencies   F1 → F3   F2→ F4   (F1 . F2) → F5 In terms of Normalization, this table is in
A
1 NF
B
2 NF
C
3 NF
D
None
       Database-Management-System       Normalization       Gate 2005-IT
Question 120 Explanation: 
F1 → F3 ......(i)
F2 → F4 ......(ii)
(F1⋅F2) → F5 .....(iii)
F1F2 is the candidate key.
F1 and F2 are the prime key.
In (i) and (ii) we can observe that the relation from P → NP which is partial dependency. So this is in 1NF.
Question 121
A B-Tree used as an index for a large database table has four levels including the root node. If a new key is inserted in this index, then the maximum number of nodes that could be newly created in the process are:
A
5
B
4
C
3
D
2
       Database-Management-System       B+-Trees       Gate 2005-IT
Question 121 Explanation: 
No. of nodes in a children of a node = no. of keys in parent node + 1
Here, the tree has 4 levels, then 4+1=5 nodes to be present in the newly created process.
Question 122
A company maintains records of sales made by its salespersons and pays them commission based on each individual's total sales made in a year. This data is maintained in a table with following schema:
salesinfo = (salespersonid, totalsales, commission)
In a certain year, due to better business results, the company decides to further reward its salespersons by enhancing the commission paid to them as per the following formula:
If commission < = 50000, enhance it by 2% If 50000 < commission < = 100000, enhance it by 4% If commission > 100000, enhance it by 6%
The IT staff has written three different SQL scripts to calculate enhancement for each slab, each of these scripts is to run as a separate transaction as follows:
 T1
Update salesinfo
Set commission = commission * 1.02
Where commission < = 50000;
 T2
Update salesinfo
Set commission = commission * 1.04
Where commission > 50000 and commission is < = 100000;
 T3
Update salesinfo
Set commission = commission * 1.06
Where commission > 100000;
Which of the following options of running these transactions will update the commission of all salespersons correctly
A
Execute T1 followed by T2 followed by T3
B
Execute T2, followed by T3; T1 running concurrently throughout
C
Execute T3 followed by T2; T1 running concurrently throughout
D
Execute T3 followed by T2 followed by T1
       Database-Management-System       SQL       Gate 2005-IT
Question 122 Explanation: 
T3 followed by T2 followed by T1 will be the correct execution sequence.
In other cases some people will get two times increment, for example,
if we have T1 followed by T2 and if initial commission is 49500, then he is belonging to <50000.
Hence, 49500 * 1.02 = 50490.
Now again he is eligible for second category. So, he will get again increment as,
50490 * 1.04 = 52509.6
So he will get increment two times, but he is eligible for only one slab of commission.
Question 123
A table 'student' with schema (roll, name, hostel, marks), and another table 'hobby' with schema (roll, hobbyname) contains records as shown below:
Table: Student
ROLL NAME HOSTEL MARKS
1798 Manoj Rathod 7 95
2154 Soumic Banerjee 5 68
2369 Gumma Reddy 7 86
2581 Pradeep Pendse 6 92
2643 Suhas Kulkarni 5 78
2711 Nitin Kadam 8 72
2872 Kiran Vora 5 92
2926 Manoj Kunkalikar 5 94
2959 Hemant Karkhanis 7 88
3125 Rajesh Doshi 5 82
 
Table: hobby
ROLL HOBBYNAME
1798 chess
1798 music
2154 music
2369 swimming
2581 cricket
2643 chess
2643 hockey
2711 volleyball
2872 football
2926 cricket
2959 photography
3125 music
3125 chess
The following SQL query is executed on the above tables:
select hostel
from student natural join hobby
where marks > = 75 and roll between 2000 and 3000;
Relations S and H with the same schema as those of these two tables respectively contain the same information as tuples. A new relation S’ is obtained by the following relational algebra operation: S’ = ∏hostel ((σs.roll = H.roll (σmarks > 75 and roll > 2000 and roll < 3000 (S)) X (H)) The difference between the number of rows output by the SQL statement and the number of tuples in S’ is      
A
6
B
4
C
2
D
0
       Database-Management-System       SQL       Gate 2005-IT
Question 123 Explanation: 
SQL query will return:

Total 7 rows are selected.
Where in relational algebra only distinct values of hostels are selected,i.e., 5, 6, 7 (3 rows).
∴ Answer is 7 - 3 =4
Question 124
In an inventory management system implemented at a trading corporation, there are several tables designed to hold all the information. Amongst these, the following two tables hold information on which items are supplied by which suppliers, and which warehouse keeps which items along with the stock-level of these items. Supply = (supplierid, itemcode) Inventory = (itemcode, warehouse, stocklevel) For a specific information required by the management, following SQL query has been written
Select distinct STMP.supplierid
From Supply as STMP
Where not unique (Select ITMP.supplierid
                  From Inventory, Supply as ITMP
                  Where STMP.supplierid = ITMP.supplierid
                  And ITMP.itemcode = Inventory.itemcode
                  And Inventory.warehouse = 'Nagpur');
For the warehouse at Nagpur, this query will find all suppliers who
A
do not supply any item
B
supply exactly one item
C
supply one or more items
D
supply two or more items
       Database-Management-System       SQl       Gate 2005-IT
Question 124 Explanation: 
Here (not unique) in nested query ensures that only for those suppliers it return True which supplies more than 1 item in which case supplier id in inner query will be repeated for that supplier. Hence, the answer is (D) which supply two or more items.
Question 125
In a schema with attributes A, B, C, D and E following set of functional dependencies are given
A → B A → C CD → E B → D E → A
Which of the following functional dependencies is NOT implied by the above set?
A
CD → AC
B
BD → CD
C
BC → CD
D
AC → BC
       Database-Management-System       ER-Model       Gate 2005-IT
Question 125 Explanation: 
Apply membership test for all the functional dependencies.
Option (B):
BD → CD
BD+ = BD
i.e., BD cannot derive CD and hence is not implied.
Question 126
A database table T1 has 2000 records and occupies 80 disk blocks. Another table T2 has 400 records and occupies 20 disk blocks. These two tables have to be joined as per a specified join condition that needs to be evaluated for every pair of records from these two tables. The memory buffer space available can hold exactly one block of records for T1 and one block of records for T2 simultaneously at any point in time. No index is available on either table. If Nested-loop join algorithm is employed to perform the join, with the most appropriate choice of table to be used in outer loop, the number of block accesses required for reading the data are  
A
800000
B
40080
C
32020
D
100
       Database-Management-System       File-Structures       Gate 2005-IT
Question 126 Explanation: 
To minimize block accesses, we have to put that table outside having less records because for each outer record one block accesses inside will be required.
Therefore,
putting 2nd table outside,
for every 400 records {
80 block accesses in first table
}
= 32000 blocks
and also 20 blocks accesses of outer table.
So, answer comes out to be,
32000 + 20 = 32020
Question 127
A database table T1 has 2000 records and occupies 80 disk blocks. Another table T2 has 400 records and occupies 20 disk blocks. These two tables have to be joined as per a specified join condition that needs to be evaluated for every pair of records from these two tables. The memory buffer space available can hold exactly one block of records for T1 and one block of records for T2 simultaneously at any point in time. No index is available on either table. If, instead of Nested-loop join, Block nested-loop join is used, again with the most appropriate choice of table in the outer loop, the reduction in number of block accesses required for reading the data will be  
A
0
B
30400
C
38400
D
798400
       Database-Management-System       File-Structures       Gate 2005-IT
Question 127 Explanation: 
In Nested loop join the no. of block access will be,
400×80+20
= 32020 (calculated in previous question)
In block Nested loop join, the no. of block access will be
20×80+20 = 1620
∴ The difference is = 32020 - 1620 = 30400
Question 128

Let R1 (A, B, (D)) and R2 (D, E) be two relation schema, where the primary keys are shown underlined, and let C be a foreign key in R1 referring to R2. Suppose there is no violation of the above referential integrity constraint in the corresponding relation instances r1 and r2. Which one of the following relational algebra expressions would necessarily produce an empty relation? 

A
ΠD(r2) - ΠC(r1)
B
ΠC(r1) - ΠD(r2)
C
ΠD(r1C≠Dr2)
D
ΠC(r1C=Dr2)
       Database-Management-System       Relational-Algebra       Gate-2004
Question 128 Explanation: 
C be a foreign key in R1 referring to R2.
→ Based on referral integrity C is subset of values in R2 then,
ΠC(r1) - ΠD(r2) results empty relation.
Question 129
 
A
8, 8
B
120, 8
C
960, 8
D
960, 120
       Database-Management-System       Relational-Algebra       Gate-2004
Question 129 Explanation: 
Natural join is a JOIN operation that creates an implicit join cause for you based on common columns in the two tables being joined.
→ In the question only enroll Id's are same with the student table.
→ The no. of minimum and maximum tuples is same i.e., 8, 8.
Question 130
 
A
2 NF
B
3 NF
C
BCNF
D
4NF
       Database-Management-System       Normalization       Gate-2004
Question 130 Explanation: 
Student Performance (name, courseNo, rollNo, grade)
name, courseNo  → grade →(I)
rollNo, courseNo → grade →(II)
name → rollNo →(III)
rollNo → name →(IV)
Candidate keys: name, courseNo (or) rollNo
Its is not BCNF, because the relation III, there is no relationship from super key.
name → rollNo
It is not BCNF, name is not super key.
It belongs to 3NF, because if X→Y, Y is prime then it is in 3NF.
Question 131
     
A
names of girl students with the highest marks
B
names of girl students with more marks than some boy student
C
names of girl students with marks not less than some boy students
D
names of girl students with more marks than all the boy students
       Database-Management-System       Relational-Algebra       Gate-2004
Question 131 Explanation: 
In the operator '-' between the two subexpression gives the names of all female students whose marks are greater than the all the male students of the class.
Question 132
The order of an internal node in a B+ tree index is the maximum number of children it can have. Suppose that a child pointer takes 6 bytes, the search field value takes 14 bytes, and the block size is 512 bytes. What is the order of the internal node?
A
24
B
25
C
26
D
27
       Database-Management-System       B+-Trees       Gate-2004
Question 132 Explanation: 
Block size = (n-1) * key size + n * child pointer
Child pointer = 6 bytes
key size = 14 bytes
Block size = 512 bytes
⇒ 512 = (n-1)14 + n(6)
512 = 20n - 14
n = 512+14/20 = 526/20 = 26.3
∴ n = 26
Question 133
 
A
the average salary is more than the average salary in the company
B
the average salary of male employees is more than the average salary of all male employees in the company
C
the average salary of male employees is more than the average salary of employees in the same department
D
the average salary of male employees is more than the average salary in the company
       Database-Management-System       SQL       Gate-2004
Question 133 Explanation: 
Group by (avg(salary) > (select avg (salary) from employee))
This results the employees who having the salary more than the average salary.
Sex = M
Selects the Male employees whose salary is more than the average salary in the company.
Question 134
Which level of locking provides the highest degree of concurrency in a relational database?
A
Page
B
Table
C
Row
D
Page, table and row level locking allow the same degree of concurrency
       Database-Management-System       Transactions and concurrency control       Gate 2004-IT
Question 134 Explanation: 
In page level locking, it will lock whole page i.e., all rows are highly restrictive.
Table locking can be used for concurrency control with DDL operations.
In row share table is less restrictive but it consists of highest degree of concurrency compared to page and table.
Question 135

Consider the following entity relationship diagram (ERD), where two entities E1 and E2 have a relation R of cardinality 1 : m.

The attributes of E1 are A11, A12 and A13 where A11 is the key attribute. The attributes of E2 are A21, A22 and A23 where A21 is the key attribute and A23 is a multi-valued attribute. Relation R does not have any attribute. A relational database containing minimum number of tables with each table satisfying the requirements of the third normal form (3NF) is designed from the above ERD. The number of tables in the database is

A
2
B
3
C
5
D
4
       Database-Management-System       ER-Model       Gate 2004-IT
Question 135 Explanation: 
One table for E1, two tables for E2 (A21, A22 and A21, A23) because we need to make a separate table for multivalued attribute to satisfy minimum 1NF condition that requires atomic attributes.
Then, we get
T1: {A11, A12, A13} - key is A11
T2: {A21, A22, A11} - key is A21
T3: {A21, A23} - key is {A21, A23}
Question 136
A relational database contains two tables student and department in which student table has columns roll_no, name and dept_id and department table has columns dept_id and dept_name. The following insert statements were executed successfully to populate the empty tables:
Insert into department values (1, 'Mathematics')
Insert into department values (2, 'Physics')
Insert into student values (l, 'Navin', 1)
Insert into student values (2, 'Mukesh', 2)
Insert into student values (3, 'Gita', 1)
How many rows and columns will be retrieved by the following SQL statement?
Select * from student, department
A
0 row and 4 columns
B
3 rows and 4 columns
C
3 rows and 5 columns
D
6 rows and 5 columns
       Database-Management-System       SQL       Gate 2004-IT
Question 136 Explanation: 
Simply, cartesian product of two tables will result
rows = 3 * 2 = 6
Columns = 3 + 2 = 5
Question 137

A relation Empdtl is defined with attributes empcode (unique), name, street, city, state and pincode. For any pincode, there is only one city and state. Also, for any given street, city and state, there is just one pincode. In normalization terms, Empdtl is a relation in

A
1NF only
B
2NF and hence also in 1NF
C
3NF and hence also in 2NF and 1NF
D
BCNF and hence also in 3NF, 2NF an 1NF
       Database-Management-System       Normal-Forms       Gate 2004-IT
Question 137 Explanation: 
It is in 2NF. For 2NF all non-prime attribute should be fully functionally dependent on key.
Here key is empcode and contains only one attribute, hence no partial dependency. But there is transitive dependency in this.
Pincode → city, state, so it is not in 3NF.
Question 138
A table T1 in a relational database has the following rows and columns:
 roll no.  marks
 1  10
 2  20
 3  30
 4  Null
The following sequence of SQL statements was successfully executed on table T1.
Update T1 set marks = marks + 5
Select avg(marks) from T1
What is the output of the select statement?
A
18.75
B
20
C
25
D
NULL
       Database-Management-System       SQL       Gate 2004-IT
Question 138 Explanation: 
Update on null values gives null. Now, avg function ignores null values. So, here avg will be
(15+25+35)/3 = 25
Question 139
Consider two tables in a relational database with columns and rows as follows:
Table: Student
ROLL_NO NAME DEPT_ID
1 ABC 1
2 DEF 1
3 GHI 2
4 JKL 3
Table: Department
DEPT_ID DEPT_NAME
1 A
2 B
3 C
Roll_no is the primary key of the Student table, Dept_id is the primary key of the Department table and Student.Dept_id is a foreign key from Department.Dept_id What will happen if we try to execute the following two SQL statements?
  1. update Student set Dept_id = Null where Roll_on = 1
    1. update Department set Dept_id = Null where Dept_id = 1
A
Both (i) and (ii) will fail
B
(i) will fail but (ii) will succeed
C
(i) will succeed but (ii) will fail
D
Both (i) and (ii) will succeed
       Database-Management-System       ER-Model       Gate 2004-IT
Question 139 Explanation: 
Here in (i), when we update in Student table, Dept_id = Null, then it will not cause any problem to referenced table.
But in (ii) if we set in Department table, Dept_id = Null, then it will produce inconsistency because in Student table we will still have the tuples containing the Dept_id = 1.
Question 140

Consider a table T in a relational database with a key field K. A B-tree of order p is used as an access structure on K, where p denotes the maximum number of tree pointers in a B-tree index node. Assume that K is 10 bytes long; disk block size is 512 bytes; each data pointer PD is 8 bytes long and each block pointer PB is 5 bytes long. In order for each B-tree node to fit in a single disk block, the maximum value of p is

A
20
B
22
C
23
D
32
       Database-Management-System       B+-Trees       Gate 2004-IT
Question 140 Explanation: 
Let k = key_ptr_size
r = record_ptr_size
b = block_ptr_size
(p - 1) (k + r) + p × b ≤ 512
(p - 1) (10 + 8) + p × 5 ≤ 512
23p ≤ 530
p ≤ 23.04
So, maximum value of p possible will be 23.
Question 141
Which of the following scenarios may lead to an irrecoverable error in a database system?
A
A transaction writes a data item after it is read by an uncommitted transaction
B
A transaction reads a data item after it is read by an uncommitted transaction
C
A transaction reads a data item after it is written by a committed transaction
D
A transaction reads a data item after it is written by an uncommitted transaction
       Database-Management-System       Transactions       Gate-2003
Question 141 Explanation: 
Irrecoverable error occurs when a transaction reads a data item after it is written by uncommitted transaction.
Question 142
Consider the following SQL query
select distinct al, a2,........., an
from r1, r2,........, rm
where P
For an arbitrary predicate P, this query is equivalent to which of the following relational algebra expressions ?  
A
B
C
D
       Database-Management-System       SQL       Gate-2003
Question 142 Explanation: 
If we want to get distinct elements then we need to perform cross product in between the relations r1, r2, .... rm.
Question 143
Consider the following functional dependencies in a database:
  Data_of_Birth → Age
  Age → Eligibility
  Name → Roll_number
  Roll_number → Name
  Course_number → Course_name
  Course_number → Instructor
  (Roll_number, Course_number) → Grade
The relation (Roll_number, Name, Date_of_birth, Age) is:
A
in second normal form but not in third normal form
B
in third normal form but not in BCNF
C
in BCNF
D
in none of the above
       Database-Management-System       Normalization       Gate-2003
Question 143 Explanation: 
Three FD's are valid from the above set of FD's for the given relation.
Date_of_Birth → Age
Name → Roll_number
Roll_number → Name
Candidate keys for the above are:
(Date_of_Birth, Name) and (Date_of_Birth, Roll_number)
Clearly, there is a partial dependency,
Date_of_Birth → Age
So, it is only in 1NF.
Question 144
Consider the set of relations shown below and the SQL query that follows.
Students: (Roll_number, Name, Date_of_birth)
  Courses: (Course number, Course_name, Instructor)
  Grades: (Roll_number, Course_number, Grade)
 select distinct Name
         from Students, Courses, Grades
         where Students. Roll_number = Grades.Roll_number
              and Courses.Instructor = Korth
              and Courses.Course_number = Grades.Course_number
              and Grades.grade = A
Which of the following sets is computed by the above query?
A
Names of students who have got an A grade in all courses taught by Korth
B
Names of students who have got an A grade in all courses
C
Names of students who have got an A grade in at least one of the courses taught by Korth
D
in none of the above
       Database-Management-System       SQL       Gate-2003
Question 144 Explanation: 
The query results a names of students who got an A grade in at least one of the courses taught by korth.
Question 145
Consider three data items D1, D2 and D3 and the following execution schedule of transactions T1, T2 and T3. In the diagram, R(D) and W(D) denote the actions reading and writing the data item D respectively. Which of the following statements is correct?    
A
The schedule is serializable as T2; T3; T1
B
The schedule is serializable as T2; T1; T3
C
The schedule is serializable as T3; T2; T1
D
The schedule is not serializable
       Database-Management-System       Transactions       Gate-2003
Question 145 Explanation: 
Precedence graph:

Cycle formed so not serializable.
Question 146
Relation R with an associated set of functional dependencies, F, is decomposed into BCNF. The redundancy (arising out of functional dependencies) in the resulting set of relations is
A
Zero
B
More than zero but less than that of an equivalent 3NF decomposition
C
Proportional to the size of F+
D
Indetermine
       Database-Management-System       Normalization       Gate-2002
Question 146 Explanation: 
If a relation is in BCNF then there is no functional dependencies.
Question 147
With regard to the expressive power of the formal relational query languages, which of the following statements is true?
A
Relational algebra is more powerful than relational calculus.
B
Relational algebra has the same power as relational calculus.
C
Relational algebra has the same power as safe relational calculus.
D
None of the above.
       Database-Management-System       Relational-Algebra       Gate-2002
Question 147 Explanation: 
Relational algebra has the same power as safe relational calculus.
A query can be formulated in safe Relational Calculus if and only if it can be formulated in Relational Algebra.
Question 148

A B+- tree index is to be built on the Name attribute of the relation STUDENT. Assume that all student names are of length 8 bytes, disk blocks are of size 512 bytes, and index pointers are of size 4 bytes. Given this scenario, what would be the best choice of the degree (i.e. the number of pointers per node) of the B+-tree?

A
16
B
42
C
43
D
44
       Database-Management-System       B+-Trees       Gate-2002
Question 148 Explanation: 
Let P be the degree of the nodes.
Then,
8(P-1) + 4P ≤ 512
12P - 8 ≤ 512
12P ≤ 520
P ≤ 43.33
P = 43
Question 149

Relation R is decomposed using a set of functional dependencies, F, and relation S is decomposed using another set of functional dependencies, G. One decomposition is definitely BCNF, the other is definitely 3NF, but it is not known which is which. To make a quaranteed identification, which one of the following tests should be used on the decompositions? (Assume that the closures of F and G are available).

A
Dependency-preservation
B
Lossless-join
C
BCNF definition
D
3NF definition
       Database-Management-System       Normalization       Gate-2002
Question 149 Explanation: 
If it is BCNF then it is 3NF. But the relation is in 3NF then it is need not to be in BCNF.
Question 150
 
A
A functionally determines B and B functionally determines C
B
A functionally determines B and B does not functionally determines C
C
B does not functionally determines C
D
A does not functionally determines B and B does not functionally determines C
       Database-Management-System       Functional-Dependency       Gate-2002
Question 150 Explanation: 
From the given instanceof relation it can be seen that A functionally determines B, but we cannot conclude this for the entire relation.
But for the given instance it can be seen that B does not functionally determines C, and it can be concluded for entire relation
Question 151
 
A
Theory Explanation is given below.
       Database-Management-System       Relational-Algebra-and-SQL       Gate-2002
Question 152
 
A
Theory Explanation is given below.
       Database-Management-System       Normalization       Gate-2002
Question 153
 
A
Theory Explantion is given below.
       Database-Management-System       Descriptive       Gate-2002
Question 154
B+-trees are preferred to binary trees in databases because
A
Disk capacities are greater than memory capacities
B
Disk access is much slower than memory access
C
Disk data transfer rates are much less than memory data transfer rates
D
Disks are more reliable than memory
       Database-Management-System       B+-Trees       Gate-2000
Question 154 Explanation: 
In B+ trees it is easy to reduce the number of last level access from the disk when the disk size is too large.
Question 155
 
A
Department address of every employee
B
Employees whose name is the same as their department name
C
The sum of all employees’ salaries
D
All employees of a given department
       Database-Management-System       Relational-Algebra       Gate-2000
Question 155 Explanation: 
The sum of all employee’s salaries can’t be represented by using the given six basic algebra operation. If we want to represent sum of salaries then we need to use aggregation operator.
Question 156
 
A
XY → Z and Z → Y
B
YZ → X and Y → Z
C
YZ → X and X → Z
D
XZ → Y and Y → X
       Database-Management-System       Functional-Dependency       Gate-2000
Question 156 Explanation: 
A functional dependency A→B is said to hold if for two tuples t1 and t2.
If for t1[A] = t2[A] then t1[Y] = t2[Y].
Question 157
 
A
r has no duplicates and s is non-empty
B
r and s have no duplicates
C
s has no duplicates and r is non-empty
D
r and s have the same number of tuples
       Database-Management-System       SQL       Gate-2000
Question 157 Explanation: 
r has no duplicate, if r can have duplicates it can be remove in the final state. s in non-empty if s is empty then r*s becomes empty.
Question 158

In SQL, relations can contain null values, and comparisons with null values are treated as unknown. Suppose all comparisons with a null value are treated as false. Which of the following pairs is not equivalent?

A
x = 5 not AND (not (x = 5)
B
x = 5 AND x > 4 and x < 6, where x is an integer
C
x ≠ 5 AND not (x = 5)
D
None of the above
       Database-Management-System       SQL       Gate-2000
Question 158 Explanation: 
For all values less than five, x<5 is true and if x=5 then it is false.
Question 159
 
A
Theory Explanation is given below.
       Database-Management-System       B+-Trees       Gate-2000
Question 159 Explanation: 
(a)
(i)

(ii)

(b)
Question 160

A
Theory Explanation is given below.
       Database-Management-System       SQL       Gate-2000
Question 161
Consider the join of a relation R with a relation S. If R has m tuples and S has n tuples then the maximum and minimum sizes of the join respectively are
A
m + n and 0
B
mn and 0
C
m + n and |m – n|
D
mn and m + n
       Database-Management-System       Relational-Algebra       Gate-1999
Question 161 Explanation: 
For maximum:
Suppose there is no common attribute in R and S due to which natural join will act as cross product. So then in cross product total no. of tuples will be mn.
For minimum:
Suppose there is common attribute in R and S, but none of the row of R matches with rows of S then minimum no. of tuples will be 0.
Question 162
     
A
σ(A=10∨B=20) (r)
B
σ(A=10) (r) ∪ σ(B=20) (r)
C
σ(A=10) (r) ∩ σ(B=20) (r)
D
σ(A=10) (r) - σ(B=20) (r)
       Database-Management-System       Relational-Algebra       Gate-1999
Question 162 Explanation: 
The given relational algebra expression represents tuples having A=10 and B=20 which is equivalent to
σ(A=10) (r) ∩ σ(B=20) (r)
Question 163
Let R = (a, b, c, d, e, f) be a relation scheme with the following dependencies c → f, e → a, ec → d, a → b. Which of the following is a key for R?
A
CD
B
EC
C
AE
D
AC
       Database-Management-System       Functional-Dependency       Gate-1999
Question 163 Explanation: 
Let's check closure for each option,
A) (CD)+ = cdf
Not a key.
B) (EC)+ = ecdabf
Yes, it is a key.
C) (AE)+ = aeb
Not a key. D) (AC)+ = abcf
Not a key.
Question 164
Which of the following is correct?
A
B-trees are for storing data on disk and B+ trees are for main memory.
B
Range queries are faster on B* trees.
C
B-trees are for primary indexes and B* trees are for secondary indexes.
D
The height of a B* tree is independent of the number of records.
       Database-Management-System       B+-Trees       Gate-1999
Question 164 Explanation: 
Range queries are faster on B+ trees.
Question 165
 
A
This schedule is serialized and can occur in a scheme using 2PL protocol
B
This schedule is serializable but cannot occur in a scheme using 2PL protocol
C
This schedule is not serialiable but can occur in a scheme using 2PL protocol
D
This schedule is not seralisable and cannot occur in a scheme using 2PL protocol.
       Database-Management-System       Transactions       Gate-1999
Question 165 Explanation: 
Let's draw precedence graph,

Since cycle exist so not conflict serializable.
And we know that if the schedule is not serializable then it is not 2PL.
Hence correct option is (D).
Question 166
Consider the schema R = (S T U V) and the dependencies S → T, T → U, U → V and V → S. Let R = (R1 and R2) be a decomposition such that R1 ∩ R2 = ∅ . The decomposition is
A
not in 2NF
B
in 2NF but not 3NF
C
in 3NF but not in 2NF
D
in both 2NF and 3NF
       Database-Management-System       Normalization       Gate-1999
Question 166 Explanation: 
Since R1 ∩ R2 = ∅, so the decomposition is lossless join. Now since all the attributes are keys, so R1 ∩ R2 will be a key of the decomposed relation.
And since every attribute is key so the decomposed relation will be in BCNF and hence in 3NF.
Question 167
 
A
A = 0, B = 0, C = 1
B
A = 0, B = 1, C = 1
C
A = 1, B = 0, C = 1
D
A = 1, B = 1, C = 1
       Database-Management-System       Logic-Gates       Gate-1999
Question 167 Explanation: 

So the above equation is satisfied if either C=0 or A=0 and B=1.
Hence, Option (B) is correct.
Question 168
Which of the following sets of component(s) is/are sufficient to implement any arbitrary Boolean function?
A
XOR gates, NOT gates
B
2 to 1 multiplexors
C
AND gates, XOR gates
D
Three-input gates that output (A⋅B) + C for the inputs A⋅B and C
E
Both B and C
       Database-Management-System       Boolean-Functions       Gate-1999
Question 168 Explanation: 
(A) Not complete because, XOR can be used to make only NOT gate and NOT gate is already available. Hence not complete.
(B) 2 to 1 multiplexors is functionally complete.
(C) XOR gate can be used to make a NOT gate. So, (AND, NOT) is functionally complete.
(D) With given gates and inputs NOT gate cannot be derived.
Hence, not complete.
Question 169
Which of the following is/are correct?
A
An SQL query automatically eliminates duplicates
B
An SQL query will not work if there are no indexes on the relations
C
SQL permits attribute names to be repeated in the same relation
D
None of the above
       Database-Management-System       SQL       Gate-1999
Question 169 Explanation: 
→ SQL won't remove duplicates like relational algebra projection, we have to remove it explicitly by distinct.
→ If there are no indexes on the relation SQL, then also it works.
→ SQL does not permit 2 attributes to have same name in a relation.
Question 170
Given two union compatible relations R1(A,B) and R2 (C,D), what is the result of the operation R1A = CAB = DR2?
A
R1 ∪ R2
B
R1 × R2
C
R1 - R2
D
R1 ∩ R2
       Database-Management-System       Relational-Algebra       Gate-1998
Question 170 Explanation: 
The join here will be selecting only those tuples where A=C and B=D, meaning it is the intersection.
Question 171
Which normal form is considered adequate for normal relational database design?
A
2 NF
B
5 NF
C
4 NF
D
3 NF
       Database-Management-System       Normalization       Gate-1998
Question 171 Explanation: 
3NF, is considered as adequate for normal relational database design, because we can have a 3NF decomposition which is dependency preserving and lossless (not possible for any higher forms).
Question 172
 
A
Age
B
Name
C
Occupation
D
Category
       Database-Management-System       Indexing       Gate-1998
Question 172 Explanation: 
Indexing will be on occupation field because occupation field lexigraphically sorted will give the sequence 1, 3, 2, 5, 4.
Question 173

Which of the following query transformations (i.e. replacing the l.h.s. expression by the r.h.s. expression) is incorrect? R1 and R2 are relations, C1, C2 are selection conditions and A1, A2 are attributes of R1?

 
A
σC1C1(R1)) → σC2C2(R1))
B
σC1A1(R1)) → σA1C1(R1))
C
σC1(R1 ∪ R2) → σC1(R1) ∪ σC1(R2)
D
πA1C1(R1)) → σC1A1(R1))
       Database-Management-System       Relational-Algebra       Gate-1998
Question 173 Explanation: 
If the selection condition is on attribute A2, then we cannot replace it by RHS as there will not be any attribute A2 due to projection of A1 only.
Question 174
     
A
in first normal form but not in second normal form
B
in second normal form but not in third normal form
C
in third normal form
D
None of the above
       Database-Management-System       Normalization       Gate-1997
Question 174 Explanation: 
Candidate key is ab.
Since all a, b, c, d are atomic. So the relation is in 1NF.
Checking the FD's
a → c
b → d
We can see that there is partial dependencies. So it is not 2NF.
So answer is option (A).
Question 175
 
A
None of (a), (b), (c) or (d) can cause its violation
B
All of (a), (b), (c) and (d) can cause its violation
C
Both (a) and (d) can cause its violation
D
Both (b) and (c) can cause its violation
       Database-Management-System       Referential-Integrity       Gate-1997
Question 175 Explanation: 
Let take example:

Here 'd' is the foreign key of S and let 'a' is the primary key of R.
(A) Insertion into R: will cause no violation.
(B) Insertion into S: may cause violation because there may not be entry of the tuple in relation R. Example entry of 〈S4, __, __〉 is not allowed.
(C) Delete from R: may cause violation. For example, deletion of tuple 〈S2, __, __〉 will cause violation as there is entry of S2 in the foreign key table.
(D) Delete from S: will cause no violation as it does not result inconsistency.
Question 176
 
A
True
B
False
       Database-Management-System       Normalizations       Gate-1994
Question 176 Explanation: 
BCNF decomposition can always be lossless, but itmay not be always possible to get a dependency preserving BCNF decomposition.
Question 177
An instance of a relational scheme R(A, B, C) has distinct values for attribute A. Can you conclude that A is a candidate key for R?  
A
Yes
B
No
       Database-Management-System       Normal-Forms       Gate-1994
Question 177 Explanation: 
Because FD's are defined on the schema itself, not the instance. So, based on the state of the instance we cannot say what holds for schema (there can be many instances for R).
Question 178
Give a relational algebra expression using only the minimum number of operators from (∪, −) which is equivalent to R ∩ S.  
A
Out of syllabus (For explanation see below)
       Database-Management-System       Relational-Algebra       Gate-1994
Question 178 Explanation: 
R - (R - S)
→ No need of using Union operation here. → In question they gave (∪, −) but we don't use both.
→ And also they are saying that only the minimum number of operators from (∪, −) which is equivalent to R ∩ S.
So, the expression is minimal.
Question 179
 
A
True
B
False
       Database-Management-System       General       Gate-1994
Question 179 Explanation: 
Logical data independence is more difficult to achieve than physical data independence, since application programs are heavily dependent on the logical structure of the data that they access.
Question 180
Consider a schema R(A,B,C,D) and functional dependencies A → B and C → D. Then the decomposition of R into R1(AB) and R2(CD) is
A
dependency preserving and lossless join
B
lossless join but not dependency preserving
C
dependency preserving but not lossless join
D
not dependency preserving and not lossless join
       Database-Management-System       Functional-Dependency       Gate-2001
Question 180 Explanation: 
If the given realations are to be lossless then
R1∩R2 ≠ 0
Given R1(A,B), R2
R1∩R2= 0
Not lossless.
The given relation decomposed into R1(A,B) and R2(C,D) and there are only two functional dependencies A→B and C→D. So the given decomposition is dependency preserving.
Question 181
Suppose the adjacency relation of vertices in a graph is represented in a table Adj(X,Y). Which of the following queries cannot be expressed by a relational algebra expression of constant length?
A
List of all vertices adjacent to a given vertex
B
List all vertices which have self loops
C
List all vertices which belong to cycles of less than three vertices
D
List all vertices reachable from a given vertex
       Database-Management-System       Relational-Algebra       Gate-2001
Question 181 Explanation: 
(a) Finding a adjaceny vertex for the given vertex is too simple i.e., Adj(X,Y).
(b) Finding a self loop is also simple (Oop(X,X))
(c) If a → b, b → c then c!=a, finding this is also simple.
(d) List all the elements reachable from a given vertex is too difficult in Relational Algebra.
Question 182
Let r and s be two relations over the relation schemes R and S respectively, and let A be an attribute in R. Then the relational algebra expression σA=a(r⋈s) is always equal to
A
σA=a (r)
B
r
C
σA=a (r)⨝s
D
None of the above
       Database-Management-System       Relational-Algebra       Gate-2001
Question 182 Explanation: 
(a) A=a for all r
(b) Display table
(c) A=a for all Tables r and s
Question 183
R(A,B,C,D) is a relation. Which of the following does not have a lossless join, dependency preserving BCNF decomposition?
A
A → B, B → CD
B
A → B, B → C, C → D
C
AB → C, C → AD
D
A → BCD
       Database-Management-System       Normalization       Gate-2001
Question 183 Explanation: 
We have, R (A, B, C, D) and the Functional Dependency set = {AB→C, C→AD}. We decompose it as R1(A, B, C) and R2(C, D). This preserves all dependencies and the join is lossless too, but the relation R1 is not in BCNF. In R1 we keep ABC together otherwise preserving {AB→C} will fail, but doing so also causes {C→A} to appear in R1. {C→A} violates the condition for R1 to be in BCNF as C is not a super key. Condition that all relations formed after decomposition should be in BCNF is not satisfied here.
Question 184
Which of the following relational calculus expressions is not safe?
A
{t|∃u ∈ R1 (t[A] = u[A])∧ ¬∃s ∈ R2 (t[A] = s[A])}
B
{t|∀u ∈ R1 (u[A]= "x" ⇒ ∃s ∈ R2 (t[A] = s[A] ∧ s[A] = u[A]))}
C
{t|¬(t ∈ R1)}
D
{t|∃u ∈ R1 (t[A] = u[A])∧ ∃s ∈ R2 (t[A] = s[A])}
       Database-Management-System       Relational-Calculus       Gate-2001
Question 185
 
A
A tuple (z,w) with z > y is deleted
B
A tuple (z,w) with z > x is deleted
C
A tuple (z,w) with w < x is deleted
D
The deletion of (x,y) is prohibited
       Database-Management-System       SQL       Gate-2001
Question 186
A
Theory Explanation is given below.
       Database-Management-System       Descriptive       Gate-2001
Question 187
 
A
Theory Explanation is given below.
       Database-Management-System       B+-Trees       Gate-2001
Question 188
 
A
(a) - (q), (b) - (r), (c) - (p), (d) - (s)
       Database-Management-System       Match-the-Following       Gate-1990
Question 188 Explanation: 
Secondary index → B-Tree
Non-procedural query language → Domain calculus
Closure of set of attributes → Function dependency
Natural join → Relational algebraic operations
Question 189
 
A
Transitive functional dependencies.
B
Non-trivial functional dependencies involving prime attributes on the right-side.
C
Non-trivial functional dependencies involving prime attributes only on the left-side.
D
Non-trivial functional dependencies involving only prime attributes.
E
Both (B) and (D).
       Database-Management-System       Normalization       Gate-1990
Question 189 Explanation: 
A) Transitive functional dependency, so not in 3NF.
B) 3NF because right side is prime attribute.
C) Not in 3NF, because lets suppose ABC is a candidate key. Now consider
AB → Non-prime attribute
which show it is not in 3NF
D) Involves only prime attribute, so right side should definitely contain only prime attribute. So in 3NF.
Question 190
For secondary key processing which of the following file organizations is preferred? Give a one line justification:
A
Indexed sequential file organization.
B
Two-way linked list.
C
Inverted file organization.
D
Sequential file organization.
       Database-Management-System       Indexing       Gate-1989
Question 190 Explanation: 
Inverted file organization, because of reasons are as follows:
→ An index for each secondary key.
→ An index entry for each distinct value of the secondary key.
→ Exhibits better enquiry performance.
There are 190 questions to complete.
PHP Code Snippets Powered By : XYZScripts.com