Database-Management-System

Question 1

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-2]
Question 1 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 2

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       B-and-B+-Trees       GATE 2019
Question 2 Explanation: 
Memory based question:
In B+ trees non-leaf nodes do not have pointers to data records.
Question 3

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       Transactions       GATE 2019
Question 3 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 4

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       Relational-Algebra       GATE 2019
Question 4 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 5

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       SQL       GATE 2019
Question 5 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 6

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       Normalization       GATE 2019
Question 6 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 7

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 7 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 8

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 8 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 9

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 9 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 10

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 10 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 11

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-1]
Question 11 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 12

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-1]
Question 12 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 13

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-1]
Question 13 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 14

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-1]
Question 14 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 15

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-1]
Question 15 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 16

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-2]
Question 16 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 17

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-2]
Question 17 Explanation: 

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

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-2]
Question 18 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 19

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-2]
Question 19 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 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       GATE 2016 [Set-1]
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       GATE 2016 [Set-1]
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       GATE 2016 [Set-1]
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       GATE 2016 [Set-1]
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 possible 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-1]
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-1]
Question 29 Explanation: 

Question 30

Consider the following relations:

Consider the following SQL query.

SELECT S. Student_Name, sum(P.Marks)
     FROM Student S, Performance P
     WHERE S.Roll_No = P.Roll_No
     GROUP BY S.Student_Name

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

A
2
B
3
C
4
D
5
       Database-Management-System       SQL       GATE 2015 [Set-1]
Question 30 Explanation: 
Output table is
Question 31

Consider the following transaction involving two bank account x and y.

read(x); x:= x-50; write(x); read(y); y:= y+50; write(y)

The constraint that the sum of the accounts x and y should remain constant is that of

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

    (start, T4); (write, T4, y, 2, 3); (start, T1); (commit, T4); (write, T1, z, 5, 7);
    (checkpoint);
    (start, T2); (write, T2, x, 1, 9); (commit, T2); (start, T3); (write, T3, z, 7, 2);

If a crash happens now and the system tries to recover using both undo and redo operations, what are the contents of the undo list and the redo list

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-3]
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-3]
Question 36 Explanation: 
Inner query collects capacities of all the theaters and in outer query we are filtering the tuples with the condition “capacity >= All”.
So the theaters which are having maximum capacity will satisfy the condition.
Question 37

The value of is

A
0
B
1/2
C
1
D
       Database-Management-System       Calculus       GATE 2015 [Set-3]
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[Xi = 0] = Pr[Xi = 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-3]
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-1]
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-1]
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-1]
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-1]
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-1]
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-2]
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-2]
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-2]
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-2]
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-2]
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-3]
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-3]
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-3]
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 C.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-3]
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 relation schema

    Students(rollno: integer, sname: string)
    Courses(courseno: integer, cname: string)
    Registration(rollno: integer, courseno: integer, percent: real)

Which of the following queries are equivalent to this query in English?

"Find the distinct names of all students who score more than 90% in the course numbered 107" 
(I) SELECT DISTINCT S.sname
    FROM Students as S,Registration as R
    WHERE R.rollno = S.roll AND R.courseno = 107 AND R.percent > 90
(II) Πsnamecourseno=107∧percent>90(Registration ⋈ Students))
(III) {T|∃S ∈ Students, ∃R ∈ Registration (S.rollno = R.rollno ∧ R.courseno = 107 ∧ R.percent > 90 ∧ T.sname = S.sname)}
(IV) {〈SN〉|∃SR∃RP(〈SR,SN〉 ∈ Students ∧ 〈SR,107,RP〉 ∈ Registration ∧ RP>90)}
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

Relation R has eight attributes ABCDEFGH . Fields of R contain only atomic values.

F = {CH→G, A→BC, B→CFG, E→A, F→EG}, is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R.

How many candidate keys does the relation R have?

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.

F = {CH→G, A→BC, B→CFG, E→A, F→EG}, is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R.

The relation R is

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 AUB is the same as that of A.

(AUB)⋈A.Id>40∨C.Id<15 C
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 following relations A, B, C.

How many tuples does the result of the following SQL query contain?

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.

    Q1: πA1,...,ApAp=c(r)) where c is a constant
    Q2: πA1,...,Apc1≤Ap≤c2(r)) where c1 and c2 is a constants

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       a       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       GATE 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?

SELECT 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       GATE 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       GATE 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       GATE 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       GATE 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:

    T1 = R1[X] W1[X] W1[Y]
    T2 = R2[X] R2[Y] W2[Y]
    S1 = R1[X] R2[X] R2[Y] W1[X] W1[Y] W2[Y]
    S2 = R1[X] R2[X] R2[Y] W1[X] W2[Y] W1[Y]
    S3 = R1[X] W1[X] R2[X] W1[Y] R2[Y] W2[Y]
    S1 = R1[X] R2[Y]R2[X]W1[X] W1[Y] W2[Y]

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       GATE 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       GATE 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:

I. πR-S(r)-πR-SR-S(r)×s-πR-S,S(r))
II. {t|t∈ πR-S(r) ∧ ∀u ∈ s(∃v ∈ r(u = v[s] ∧ t = v[R - S]))}
III. {t|t∈ πR-S(r) ∧ ∀v ∈ r(∃u ∈ s(u = v[s] ∧ t = v[R - S]))}  
IV. Select R.a, R.b
    from R, S 
    where R.c = S.c

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       GATE 2009
Question 75 Explanation: 
R={a,b,c}
S={c}
I. πR-S(r)-πR-SR-S(r)×s-πR-S,S(r))

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.
IV. Select R.a, R.b
From R, S
Where R.c = S.c
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 IN (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       GATE 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       GATE 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 ∀t ∈ r(P(t))?

    I. ¬∃t ∈ r(P(t))
    II. ∃t ∉ r(P(t))
    III. ¬∃t ∈ r(¬P(t))
    IV. ∃t ∉ r(¬P(t))
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

    R(P,Q,R1,R2,R3)
    S(P,Q,S1,S2)

Where {P, Q} is the key for both schemas. Which of the following queries are equivalent?

    I. ΠP(R ⨝ S)
    II. ΠP(R) ⨝ ΠP(S)
    III. ΠPP,Q(R) ∩ ΠP,Q(S))
    IV. ΠPP,Q(R) - (ΠP,Q(R) - ΠP,Q(S)))
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, Price)
    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 → Price

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

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?

ΠcourseId((ΠstudIdsex="female"(studInfo))×ΠcourseId(enroll))-enroll)
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 86 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 87

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?

{e.name|employee(e)∧}
     (∀x)[¬employee(x) ∨ x.supervisorName ≠ e.name ∨ x.sex = "male"]}
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 87 Explanation: 
The given Tuple Relational calculus produce names of employees with no immediate female subordinates.
Question 88

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 88 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 89

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 89 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 90

Consider the following schedules involving two transactions. Which one of the following statements is TRUE?

    S1: r1(X); r1(Y); r2(X); r2(Y); w2(Y); w1(X)
    S2: r1(X); r2(X); r2(Y); w2(Y); r1(Y); w1(X)
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 90 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 91

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 91 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 92

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 92 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 93

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 93 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 94

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       SQL       GATE 2006
Question 94 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 95

The following functional dependencies are given:

  AB → CD, AF → D, DE → F, C → G, F → E, G → 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 95 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 96

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 96 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 97

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 97 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 two attributes (X, Y).
If (X→Y), X is a candidate key. It is in BCNF and vice-versa.
Question 98

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 98 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 99

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 99 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 100
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 100 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 101

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 101 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 102

Let R1(A,B,C) 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 102 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 103

Consider the following relation schema pertaining to a students database:

   Student (rollno, name, address)
   Enroll (rollno, courseno, coursename) 

Where the primary keys are shown underlined. The number of tuples in the Student and Enroll tables are 120 and 8 respectively. What are the maximum and minimum number of tuples that can be present in (Student * Enroll), where '*' denotes natural join ?

A
8, 8
B
120, 8
C
960, 8
D
960, 120
       Database-Management-System       Relational-Algebra       GATE 2004
Question 103 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 104

The relation scheme Student Performance (name, courseNo, rollNo, grade) has the following functional dependencies:

   name, courseNo → grade
   rollNo, courseNo → grade
               name → rollNo
             rollNo → name 

The highest normal form of this relation scheme is

A
2 NF
B
3 NF
C
BCNF
D
4NF
       Database-Management-System       Normalization       GATE 2004
Question 104 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 105

Consider the relation Student (name, sex, marks), where the primary key is shown underlined, pertaining to students in a class that has at least one boy and one girl. What does the following relational algebra expression produce? (Note: p is the rename operator).

The condition in join is "(sex = female ^ x = male ^ marks ≤ m)"

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 105 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 106

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 106 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 107

The employee information in a company is stored in the relation

Employee (name, sex, salary, deptName)

Consider the following SQL query

Select deptName From Employee Where sex = 'M' Group by deptName Having avg (salary) > (select avg (salary) from Employee)

It returns the names of the department in which

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 107 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 108

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 108 Explanation: 
Irrecoverable error occurs when a transaction reads a data item after it is written by uncommitted transaction.
Question 109

Consider the following SQL query

   select distinct al, a2,........., an
   from rl, 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 109 Explanation: 
If we want to get distinct elements then we need to perform cross product in between the relations r1, r2, .... rm.
Question 110

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 110 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 111

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 111 Explanation: 
The query results a names of students who got an A grade in at least one of the courses taught by korth.
Question 112

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 112 Explanation: 
Precedence graph:

Cycle formed so not serializable.
Question 113

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 113 Explanation: 
If a relation is in BCNF then there is no functional dependencies.
Question 114

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 114 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 115

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 115 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 116

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       Normalizati       GATE 2002
Question 116 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 117

From the following instance of a relation scheme R(A,B,C), we can conclude that:

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 117 Explanation: 
From the given instance of 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 118

A university placement center maintains a relational database of companies that interview students on campus and make job offers to those successful in the interview. The schema of the database is given below:

  COMPANY (cname, clocation)     STUDENT (scrollno, sname, sdegree)
   INTERVIEW (cname, srollno, idate)  OFFER (cname,srollno, osalary) 

The COMPANY relation gives the name and location of the company. The STUDENT relation gives the student’s roll number, name and the degree program for which the student is registered in the university. The INTERVIEW relation gives the date on which a students is interviewed by a company. The OFFER relation gives the salary offered to a student who is successful in a company’s interview. The key for each relation is indicated by the underlined attributes.

(a) Write relational algebra expressions (using only the operator ⨝,σ,π,∪,−) for the following queries:

    (i) List the rollnumbers and names of those students who attended at least one interview but did not receive any job offer.
    (ii) List the rollnumbers and names of students who went for interviews and received job offers from every company with which they interviewed.

(b) Write an SQL query to list, for each degree program in which more than five students were offered jobs, the name of the degree and the average offered salary of students in this degree program.

A
Theory Explanation is given below.
       Database-Management-System       Relational-Algebra-and-SQL       GATE 2002
Question 119

For relation R = (L, M, N , O, P), the following dependencies hold:

   M → O NO → P P → L and L → MN 

R is decomposed into R1 = (L, M, N , P) and R2 = (M, O).

    (a) Is the above decomposition a lossless-join decomposition? Explain.
    (b) Is the above decomposition dependency-preserving? If not, list all the dependencies that are not preserved.
    (c) What is the highest normal form satisfied by the above decomposition?
A
Theory Explanation is given below.
       Database-Management-System       Normalization       GATE 2002
Question 120

(a) The following table refers to search times for a key in B-trees and B+-trees.

A successful search means that the key exists in the database and unsuccessful means that it is not present in the database. Each of the entries X1, X2, X3 and X4 can have a value of either Constant or Variable. Constant means that the search time is the same, independent of the specific key value, where Variable means that it is dependent on the specific key value chosen for the search.

Give the correct values for the entries X1, X2, X3 and X4 (for example X1 = Constant, X2 = Constant, X3 = Constant, X4 = Constant).

(b) Relation R(A,B) has the following view defined on it:

 CREATE VIEW V AS
 (SELECT R1.A, R2.B
 FROM R AS R1, R AS R2
 WHERE R1.B = R2.A)

(i) The current contents of relation R are shown below. What are the contents of the view V?

(ii) The tuples (2,11) and (11,6) are now inserted into R. What are the additional tuples that are inserted in V?

A
Theory Explanation is given below.
       Database-Management-System       Descriptive       GATE 2002
Question 121

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 121 Explanation: 
If the given relations 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 122

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 122 Explanation: 
(a) Finding a adjacency 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 123

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 123 Explanation: 
(a) A=a for all r
(b) Display table
(c) A=a for all Tables r and s
Question 124

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 124 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 125

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 126

Consider a relation geq which represents “greater than or equal to”, that is, (x,y) ∈ geq only if y >= x.

create table geq
(  Ib integer not null
   ub integer not null
   primary key 1b
   foreign key (ub) references geq on delete cascade ) 

Which of the following is possible if a tuple (x,y) is deleted?

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 127

Consider the relation examinee (regno, name, score), where regno is the primary key to score is a real number.
(a) Write a relational algebra using (∏,σ,ρ,×) to find the list of names which appear more than once in examinee.
(b) Write an SQL query to list the regno of examinees who have a score greater than the average score.
(c) Suppose the relation appears (regno, centr_code) specifies the center where an examinee appears. Write an SQL query to list the centr_code having an examinee of score greater than 80.

A
Theory Explanation is given below.
       Database-Management-System       Descriptive       GATE 2001
Question 128

We wish to construct a B+ tree with fan-out (the number of pointers per node) equal to 3 for the following set of key values:

             80, 50, 10, 70, 30, 100, 90   

Assume that the tree is initially empty and the values are added in the order given.
(a) Show the tree after insertion of 10, after insertion of 30, and after insertion of 90. Intermediate trees need not be shown.
(b) The key values 30 and 10 are now deleted from the tree in that order. Show the tree after each deletion.

A
Theory Explanation is given below.
       Database-Management-System       B+-Trees       GATE 2001
Question 129

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 129 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 130

Given the relations

   employee (name, salary, deptno) and
   department (deptno, deptname, address)  

Which of the following queries cannot be expressed using the basic relational algebra operations (σ, π, ×, ⋈, ∪, ∩, -)?

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 130 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 131

Given the following relation instance.

          x  y  z
          1  4  2
          1  5  3
          1  6  3
          3  2  2  

Which of the following functional dependencies are satisfied by the instance?

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 131 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 132

Given relations r(w, x) and s(y, z), the result of

    select distinct w, x
    from r, s  

is guaranteed to be same as r, provided

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 132 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 133

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 133 Explanation: 
For all values less than five, x<5 is true and if x=5 then it is false.
Question 134

(a) Suppose you are given an empty B+-tree where each node (leaf and internal) can store up to 5 key values. Suppose values 1,2,….. 10 are inserted, in order, into the tree, Show the tree pictorially
(i) After 6 insertions, and
(ii) After all 10 insertions
Do NOT show intermediate stages.

(b) Suppose instead of splitting a node when it is full, we try to move a value to the left sibling. If there is no left sibling, or the left sibling is full, we split the node. Show the tree after values, 1, 2,….., 9 have been inserted. Assume, as in (a) that each node can hold up to 5 keys.

(c) In general, suppose a B+-tree node can hold a maximum of m keys, and you insert a long sequence of keys in increasing order. Then what approximately is the average number of keys in each leaf level node.
(i) In the normal case, and
(ii) With the insertion as in (b).

A
Theory Explanation is given below.
       Database-Management-System       B+-Trees       GATE 2000
Question 134 Explanation: 
(a)
(i)

(ii)

(b)
Question 135

Consider a bank database with only one relation
transaction (transno, acctno, date, amount)
The amount attribute value is positive for deposits and negative for withdrawals.

(a) Define an SQL view TP containing the information.
(acctno, T1.date, T2.amount)
for every pair of transactions T1, T2 such that T1 and T2 are transaction on the same account and the date of T2 is ≤ the date of T1.

(b) Using only the above view TP, write a query to find for each account the minimum balance it ever reached (not including the 0 balance when the account is created). Assume there is at most one transaction per day on each account, and each account has had atleast one transaction since it was created. To simply your query, break it up into 2 steps by defining an intermediate view V.

A
Theory Explanation is given below.
       Database-Management-System       SQL       GATE 2000
Question 136

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 136 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 137

The relational algebra expression equivalent to the following tuple calculus expression:

{t| t ∈ r ∧(t[A] = 10 ∧ t[B] = 20)} is
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 137 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 138

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 of R?

A
CD
B
EC
C
AE
D
AC
       Database-Management-System       Functional-Dependency       GATE 1999
Question 138 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 139

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 139 Explanation: 
Range queries are faster on B+ trees.
Question 140

For the schedule given below, which of the following is Correct?

1   Read A
2                               Read B
3   Write A
4                               Read A
5                               Write A
6                               Write B
7   Read B
8   Write B 
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 140 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 141

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 141 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 142

Consider the circuit shown below. In a certain steady state, the line Y is at '1'. What are the possible values of A, B and C in this state?

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 142 Explanation: 

So the above equation is satisfied if either C=0 or A=0 and B=1.
Hence, Option (B) is correct.
Question 143

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 143 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 144

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 144 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 145

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 145 Explanation: 
The join here will be selecting only those tuples where A=C and B=D, meaning it is the intersection.
Question 146

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 146 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 147

There are 5 records in a database.

There is an index file associated with this and it contain the values 1, 3, 2, 5 and 4. Which one of the fields is the index built form?

A
Age
B
Name
C
Occupation
D
Category
       Database-Management-System       Indexing       GATE 1998
Question 147 Explanation: 
Indexing will be on occupation field because occupation field lexigraphically sorted will give the sequence 1, 3, 2, 5, 4.
Question 148

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
D
πA1C1(R1)) → σC1A1(R1))
       Database-Management-System       Relational-Algebra       GATE 1998
Question 148 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 149

For a database relation R(a,b,c,d), where the domains a, b, c, d include only atomic values, only the following functional dependencies and those that can be inferred from them hold:

   a → c 
   b → d  

This relation is

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 149 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 150

Let R(a,b,c) and S(d,e,f) be two relations in which d is the foreign key of S that refers to the primary key of R. Consider the following four operations R and S

   (a) Insert into R            (b) Insert into S 
   (c) Delete from R            (d) Delete from S    

Which of the following can cause violation of the referential integrity constraint above?

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 150 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 151

State True or False with reason
There is always a decomposition into Boyce-Codd normal form (BCNF) that is lossless and dependency preserving.

A
True
B
False
       Database-Management-System       Normalizations       GATE 1994
Question 151 Explanation: 
BCNF decomposition can always be lossless, but it may not be always possible to get a dependency preserving BCNF decomposition.
Question 152

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 152 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 153

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 153 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 154

State True or False with reason
Logical data independence is easier to achieve than physical data independence

A
True
B
False
       Database-Management-System       General       GATE 1994
Question 154 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 155

Consider a relational database containing the following schemas.

The primary key of each table is indicated by underlying the constituent fields.

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

The number of rows returned by the above SQL query is

A
0
B
5
C
4
D
2
       Database-Management-System       SQL       GATE 2020
Question 155 Explanation: 
The inner query “select avg(cost) from catalogue where pno='P4' group by pno;” returns:
AVG(COST)
------------
225
The outer query “select s.sno, s.sname from suppliers s, catalogue c where s.sno=c.sno” returns:
SNO SNAME
----------------------------------------
S1 M/s Royal furniture
S1 M/s Royal furniture
S1 M/s Royal furniture
S2 M/s Balaji furniture
S2 M/s Balaji furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
So, the final result of the query is:
SN SNAME
----------------------------------------
S2 M/s Balaji furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
S3 M/s Premium furniture
Therefore, 4 rows will be returned by the query.
Question 156

Which one of the following is used to represent the supporting many-one relationships of a weak entity set in an entity-relationship diagram?

A
Ovals that contain underlined identifiers
B
Rectangles with double/bold border
C
Diamonds with double/bold border
D
Ovals with double/bold border
       Database-Management-System       ER-Model       GATE 2020
Question 156 Explanation: 
An entity set that does not have sufficient attributes to form a primary key is termed as a weak entity and an entity set that has a primary key is termed as strong entity set. For a weak entity set to be meaningful, it must be associated with another entity set, called identifying or owner entity set. The relationship associating the weak entity set with the identifying entity set is called the identifying relationship and it is represented by double diamond. The identifying relationship is many-to-one from the weak entity set to the identifying entity set and the participation of weak entity set in the relationship is total.
Question 157

Consider a schedule of transactions T1 and T2:

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

A
B
C
D
       Database-Management-System       Transactions       GATE 2020
Question 157 Explanation: 
• Two schedules are said to be conflict equivalent, if conflict operations in both the schedules are executed in the same order.
• First, let’s list the conflict operations of each of the schedule given in the options and compare with the conflict operations of schedule which is given in the question.
Given schedule:

Conflict operations:
R2(B) → W1(B)
W2(B) → W1(B)
R1(C) → W2(C)
R2(D) → W1(D)
Option(1):

Conflict operations:
R1(C) → W2(C)
W1(D) → R2(D)
W1(B) → R2(B)
W1(B) → W2(B)
Option(2):

Conflict operations:
R2(B) → W1(B)
W2(B) → W1(B)
R2(D) → W1(D)
R1(C) → W2(C)
Option(3):

Conflict operations:
R2(B) → W1(B)
W2(B) → W1(B)
R2(D) → W1(D)
W2(C) → R1(C)
Option(4):

Conflict operations:
R1(C) → W2(C)
W1(D) → R2(D)
R2(B) → W1(B)
W2(B) → W1(B)
The conflict operations in the option (2) and given schedule are appearing in the same sequence order, so option (2) is the answer.
Question 158

Consider a relational table R that is in 3NF, but not in BCNF. Which one of the following statements is TRUE?

A
A cell in R holds a set instead of an atomic value.
B
R has a nontrivial functional dependency X→A, where X is not a superkey and A is a non-prime attribute and X is not a proper subset of any key.
C
R has a nontrivial functional dependency X→A, where X is not a superkey and A is a non-prime attribute and X is a proper subset of some key.
D
R has a nontrivial functional dependency X→A, where X is not a superkey and A is a prime attribute.
       Database-Management-System       Normalization       GATE 2020
Question 158 Explanation: 
R(ABCD)
FDs:
AB → C
BC → A
(BD)+ = BD ✖
(ABD)+ = ABDC ✔
(CBD)+ = CBDA ✔
Candidate keys = {ABD, CBD}
• The relation R is in 3NF, as there are no transitive dependencies.
• The relation R is not in BCNF, because the left side of both the FD’s are not Super keys.
• In R, BC → A is a non-trivial FD and in which BC is not a Super key and A is a prime attribute.
Question 159

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

A
4
       Database-Management-System       File-System       GATE 2020
Question 159 Explanation: 
Block factor = 4096/20 = 204
(1) Database BF = 1
No. of block = 106 } ➝ 1 block access from database
(2) ⎡106/204⎤ = 491
(3) ⎡491/204⎤ = 3
(4) ⎡3/204⎤ = 1
So, 1+3 = 4 disk accesses are required to retrieve any record in the database.
Question 160

Match the pairs in the following questions:

(a) Secondary index                  (p) Function dependency
(b) Non-procedural query language    (q) B-tree
(c) Closure of a set of attributes   (r) Domain calculus
(d) Natural join                     (s) Relational algebraic operations   </pre
A
(a) - (q), (b) - (r), (c) - (p), (d) - (s)
       Database-Management-System       Match-the-Following       GATE 1990
Question 160 Explanation: 
Secondary index → B-Tree
Non-procedural query language → Domain calculus
Closure of set of attributes → Function dependency
Natural join → Relational algebraic operations
Question 161

Choose the correct alternatives (More than one may be correct).

Indicate which of the following statements are true: A relational database which is in 3NF may still have undesirable data redundancy because there may exist:

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 161 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 162

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 162 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.
Question 163

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 163 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 decomposed into (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 164

Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional dependencies 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 164 Explanation: 
Candidate key is AB.
Since there is a partial dependency B→G.
So the relational schema R is Not in 2NF.
Question 165

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 165 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 166

Consider the following relational schema:

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
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 166 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 instead of school-name.
Question 167

Consider the following relational schema:

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)

Consider the following tuple relational calculus query:

If a student needs to score more than 35 marks to pass an exam, what does the query return?
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 167 Explanation: 
Query having the division with
{ x | x ∈ Enrollment ∧ x . school-id = t } | * 100 > 35 }
This is school with enrollment % is 35 or above.
As we are actually taking percentage of
(Total count in which a student has passed from a particular school)/(Total exams taken by all student combined)
Eg: if A passed in 3 and B passed in 4 and they both took 5-5 exam each. Then it is (7/10)
Question 168

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 168 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 type 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 169

Consider the following two transactions: T1 and T2.

T1: read(A);
               read(B);
               if A=0 then B ← B+1;
               write(B);
T2: read(B);
               read(A);
               if B≠0 then A ← A-1;
               write(A); 
 

Which of the following schemes, using shared and exclusive locks, satisfy the requirements for strict two phase locking for the above transactions?

A
B
C
D
       Database-Management-System       Transactions       GATE 2007-IT
Question 169 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 170

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 170 Explanation: 
The rule is
if A→B then A→→B holds but reverse is not true.
So,
(i) False.
(ii) True.
(iii) False.
(iv) True.
Question 171

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-nameb-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 171 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 comparison 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 172

Consider the B+ tree in the adjoining figure, where each node has atmost two keys and three links.

Keys K15 and then K25 are inserted into this tree in that order. Exactly how many of the following nodes (disregarding the links) will be present in the tree after the two insertions?

A
1
B
2
C
3
D
4
       Database-Management-System       B+-Trees       GATE 2007-IT
Question 172 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 173

Consider the B+ tree in the adjoining figure, where each node has atmost two keys and three links.

Now the key K50 is deleted from the 8 tree resulting after the two insertions made earlier. Consider the following statements about the B tree resulting after this deletion.
(i) The height of the tree remains the same.
(ii) The node (disregarding the links) is present in the tree.
(iii) The root node remains unchanged (disregarding the links).
Which one of the following options is true?

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 173 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 174

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 174 Explanation: 
r1⋈ r2 is a join operation done on the common attribute R. So this can have 2000 tuples.
Question 175

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 175 Explanation: 
Given all three languages have same expressive power.
Question 176

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 176 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 177

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 177 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 178

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

What is the output of the following SQL query?

select D.dname
from Drivers D
where D.did in (
                 select R.did
                 from Cars C, Reserves R
                 where R.cid = C.cid and C.colour = 'red'
                 intersect
                 select R.did
                 from Cars C, Reserves R
                 where R.cid = C.cid and C.colour = 'green'
                )
A
Karthikeyan, Boris
B
Sachin, Salman
C
Karthikeyan, Boris, Sachin
D
Schumacher, Senna
       Database-Management-System       SQL       GATE 2006-IT
Question 178 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 179

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 179 Explanation: 
(4) for taking red cars with (20) comparisons 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 180

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 theseThe information should appear as an attribute of lodging, because this is the only common attribute that relating to the hotel room and person. The information should appear as an attribute of lodging, because this is the only common attribute that relating to the hotel room and person.
       Database-Management-System       ER-Model       GATE 2005-IT
Question 180 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 181

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 181 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 182

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 182 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 183

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 183 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 184

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.rollmarks > 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 184 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 185

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 185 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 186

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 186 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 187

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 187 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 188

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 188 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 189

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 189 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 190

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 190 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 191

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 191 Explanation: 
Simply, cartesian product of two tables will result
rows = 3 * 2 = 6
Columns = 3 + 2 = 5
Question 192

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 192 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 193

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 193 Explanation: 
Update on null values gives null. Now, avg function ignores null values. So, here avg will be
(15+25+35)/3 = 25
Question 194

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?
(i)  update Student set Dept_id = Null 
     where Roll_on = 1
(ii) 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 194 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 195

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 195 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 196
In a file which contains 1 million records and the order of the tree is 100, then what is the maximum number of nodes to be accessed if B+ tree index is used?
A
5
B
4
C
3
D
10
       Database-Management-System       B-and-B+-Trees       ISRO-2018
Question 196 Explanation: 
Explanation:
→ If there are K search-key values in the file, the path is no longer than ⌈log⌈n/2⌉(K)⌉.
→ A node is generally the same size as a disk block, typically 4 kilobytes, and n is typically around 100 (40 bytes per index entry).
→ With 1 million search key values and n = 100, at most log50(1000000)=4 nodes are accessed in a lookup.
Note: Contrast this with a balanced binary free with 1 million search key values around 20 nodes are accessed in a lookup. Above difference is significant since every node access may need a disk I/O, costing around 20 milliseconds!
Question 197
Which of the following is a dense index?
A
Primary index
B
Clusters index
C
Secondary index
D
secondary non-key index
       Database-Management-System       B-and-B+-Trees       ISRO-2018
Question 197 Explanation: 
→ The primary index is created for the primary key of a table. So, records are usually clustered according to the primary key. It can be sparse.
→ In the sparse index, index records are not created for every search key. An index record here contains a search key and an actual pointer to the data on the disk. To search a record, we first proceed by index record and reach the actual location of the data. If the data we are looking for is not where we directly reach by following the index, then the system starts a sequential search until the desired data is found.
→ The secondary index usually dense.
→ In the dense index, there is an index record for every search key value in the database. This makes searching faster but requires more space to store index records itself. Index records contain search key value and a pointer to the actual record on the disk.
Question 198
In E-R model, Y is the dominant entity and X is a subordinate entity
A
If X is deleted, then Y is also deleted
B
If Y is deleted, then X is also deleted
C
If Y is deleted, then X is not deleted
D
None of the above
       Database-Management-System       ER-Model       ISRO-2018
Question 198 Explanation: 
→ It is the best example of referential integrity. In referential integrity, we are using a foreign key.
→ If we want to delete any entity in the dominant entity(Like primary key of a table) then subordinate entity(derived attribute) also deleted.
Note: If we want to delete any subordinate entity in entity then the dominant entity is not going to delete.
Question 199
A particular BNF definition for a “word” is given by the following rules. <word> ::= <letter> | <letter><pairlet> | <letter><pairdig> <pairlet> ::= <letter><letter> | <pairlet><letter><letter> <pairdig> ::= <digit><digit> | <pairdig><digit><digit> <letter> ::= a | b | c | ... | y | | z <digit> ::= 0 | 1 | 2 | ... | 9 Which of the following lexical entries can be derived from < word > ? I. pick II. picks III. c44
A
I, II and III
B
I and II only
C
l and III only
D
lI and III only
       Database-Management-System       ISRO-2018
Question 199 Explanation: 
Step-1: Both < letter > and < digit > produce only a single character
Step-2: Both < pairlet > and < pairdig > produce even number of characters
Step-3: < word > produces odd number of characters.
Step-4: As per the statements I, II and III. I statement having even number of characters
So, a statement I is wrong. Statement II and III are having odd number of characters.
Question 200
Consider the following table in a relational database
A
{Last Name}
B
{Room}
C
{Shift}
D
{Room, Shift}
       Database-Management-System       Relational-databases       ISRO-2018
Question 200 Explanation: 
→ Candidate means uniquely identified key.
→ In the above table, Room having duplicate values. So, we can’t say room is candidate key
→ The last name also having duplicates. So, we can’t say the last name is candidate key.
→ Shift also having duplicate keys, so we can’t say shift also a candidate key.
→ Combining Room and Shift we can say that candidate key.
Question 201
Which commands are used to control access over objects in relational database?
A
CASCADE & MVD
B
GRANT & REVOKE
C
QUE & QUIST
D
None of these
       Database-Management-System       Relational-databases       ISRO-2007
Question 201 Explanation: 
DCL(Data Control Language) includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system.
Examples of DCL commands:
GRANT-gives user’s access privileges to database.
REVOKE-withdraw user’s access privileges given by using the GRANT command.
Question 202
Which of the following is aggregate function in SQL?
A
Avg
B
Select
C
Ordered by
D
distinct
       Database-Management-System       SQL       ISRO-2007
Question 202 Explanation: 
Avg is one of the aggregate functions. It returns average value after calculating from values in a numeric column.
Syntax:
SELECT AVG(column_name) FROM table_name;
Question 203
A view of database that appears to an application program is known as
A
Schema
B
Subschema
C
Virtual table
D
None of these
       Database-Management-System       Schema       ISRO-2007
Question 203 Explanation: 
A view of database that appears to an application program is known as Virtual table
Question 204
Which operation is used to extract specific columns from a table?
A
Project
B
Join
C
Extract
D
Substitute
       Database-Management-System       Relational-Algebra       ISRO-2007
Question 204 Explanation: 
Projection (π)
Projection is used to project required column data from a relation. By Default projection removes duplicate data.
Example :
R(A B C)
----------
1 2 4
2 2 3
3 2 3
4 3 4
π (BC)
B C
-----
2 4
2 3
3 4
Question 205
BCNF is not used for cases where a relation has
A
Two (or more) candidate keys
B
Two candidate keys and composite
C
The candidate key overlap
D
Two mutually exclusive foreign keys
       Database-Management-System       Normalization       ISRO-2007
Question 205 Explanation: 
A relation is in Boyce-Codd normal form if all attributes which are determinants are also candidate keys.
Transformation into Boyce-Codd normal form deals with the problem of overlapping keys.
Question 206
Which of the following is correct with respect to Two phase commit protocol?
A
Ensures serializability
B
Prevents Deadlock
C
Detects Deadlock
D
Recover from Deadlock
       Database-Management-System       Transactions       ISRO-2007
Question 206 Explanation: 
The two phase commit protocol is a distributed algorithm which lets all sites in a distributed system agree to commit or rollback a transaction based upon consensus of all participating sites.If any database server is unable to commit its portion of the transaction, all database servers participating in the transaction must be prevented from committing their work.
It ensures serializability but does not ensures freedom from deadlock.
Question 207
For a database relation R(a,b,c,d) where the domains of a, b, c and d include only atomic values, only the following functional dependencies and those that can be inferred from them hold a → c b → d The relation is in
A
First normal form but not in second normal form
B
Second normal form but not in third normal form
C
Third normal form
D
None of the above
       Database-Management-System       Functional-Dependency       ISRO-2018
Question 207 Explanation: 
→ Candidate Key of given relation R is {ab}.
→ So option A is right it is not in second normal form because its part of key(candidate key) determines non-key.
→ The domains of a, b, c and d include only atomic values.
So, which satisfies the definition of first normal form.
Question 208
Consider the set of relations given 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 =Sriram
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 Sriram
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 Sriram
D
None of the above
       Database-Management-System       SQL       ISRO-2018
Question 208 Explanation: 
→ The query results a names of students who got an A grade in at least one of the courses taught by Sriram.
→ The above query they are using AND command, it means it satisfy all conditions.
Question 209
Given relations R(w,x) and S(y,z), the result of SELECT DISTINCT w, x FROM R, S Is guaranteed to be the same as R, if
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       ISRO-2018
Question 209 Explanation: 
→ R has no duplicate if R can have duplicates it can be removed in the final state.
→ S in non-empty if S is empty then R*S becomes empty.
Question 210
Database-Management-System
A
Physical Data Independence
B
Logical Data Independence
C
Both (a) and (b)
D
None of the above
       Database-Management-System       SQL       ISRO-2018
Question 210 Explanation: 
Logical data independence:
The ability to change the Conceptual (Logical) schema without changing the External schema (User View) is called logical data independence. For example, the addition or removal of new entities, attributes, or relationships to the conceptual schema or having to rewrite existing application programs.

Physical data independence:
The ability to change the physical schema without changing the logical schema is called physical data independence. For example, a change to the internal schema, such as using different file organization or storage structures, storage devices, or indexing strategy, should be possible without having to change the conceptual or external schemas.
Note: Immunity is when data at one layer is changed, it does not affect the data at another level.
Question 211
The set of attributes X will be fully functionally dependent on the set of attributes Y if the following conditions are satisfied.
A
X is functionally dependent on Y
B
X is not functionally dependent on any subset of Y
C
Both (a) and (b)
D
None of these
       Database-Management-System       Functional-Dependency       ISRO-2018
Question 211 Explanation: 
→ A functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.
→ An attribute is fully functional dependent on another attribute, if it is Functionally Dependent on that attribute and not on any of its proper subset.
For example, an attribute X is fully functional dependent on another attribute Y, if it is Functionally Dependent on X and not on any of the proper subset of Y.

Must satisfied conditions:
i) X is functionally dependent on Y and
ii) X is not functionally dependent on any subset of Y.
Question 212
Let us assume that transaction T1 has arrived before transaction T2. Consider the schedule
S=r1(A); r2(B) ; w2(A); w1(B)
Which of the following is true?
A
Allowed under basic timestamp protocol.
B
Not allowed under basic timestamp protocols because T1 is rolled back
C
Not allowed under basic timestamp protocols because T2 is rolled back
D
None of these
       Database-Management-System       Transactions       ISRO-2018
Question 212 Explanation: 

→ There are 2 conflicting actions a and b is shown in above diagram.
→ In timestamp ordering protocol, conflicting actions in ascending order time-stamps are allowed i.e 'a' is allowed but not 'b'.
→ So we need to roll back T1 after that only it will be allowed. Because of all conflicting actions in ascending order timestamps in below diagram.

Question 213
What does the data dictionary identify?
A
Field names
B
Field Formats
C
Field Types
D
All of these
       Database-Management-System       Relational-databases       ISRO-2017 May
Question 213 Explanation: 
→ A data dictionary(or) metadata repository, as defined in the IBM Dictionary of Computing, is a "centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format".
→ Oracle defines it as a collection of tables with metadata. The term can have one of several closely related meanings pertaining to databases and database management systems (DBMS)
1. A document describing a database or collection of databases
2. An integral component of a DBMS that is required to determine its structure
3. A piece of middleware that extends or supplants the native data dictionary of a DBMS
Question 214
Which of the following concurrency control protocol ensures both conflict serializability and free from deadlock?
A
Timestamp ordering
B
2 Phase Locking
C
Both (a) and (b)
D
None of the above
       Database-Management-System       Transactions       ISRO-2017 May
Question 214 Explanation: 
→ Two-phase locking protocol (2PL) ensures the conflict serializable schedule but it may not free from deadlock.
→ Timestamp ordering protocol ensures conflict serializability and free from deadlock.
Question 215
ACID properties of a transactions are
A
Atomicity, consistency, isolation, database
B
Atomicity, consistency, isolation, durability
C
Atomicity, consistency, integrity, durability
D
Atomicity, consistency, integrity, database
       Database-Management-System       Transactions       ISRO-2017 May
Question 215 Explanation: 
Atomicity: Execute the all the operations or none of them
→ Responsible for Transaction Manager
Consistency: Database should be consistent before and after the execution of the transaction
→ Responsible for user/application manager
Isolation: Each transaction Tie must be executed without knowing what is happening with other transactions. Responsible for Concurrency control manager
Durability: All updates done by a transaction must become permanent.
→ Responsible for recovery manager
Question 216
Database table by name overtime_allowance is given below

What is the output of the following SQL query?
select count(*) from ((select Employee, Department from Overtime_allowance) as S
natural join (select Department, OT_allowance from Overtime_allowance) as T);
A
16
B
4
C
8
D
None of the above
       Database-Management-System       SQL       ISRO-2017 May
Question 216 Explanation: 

Common attributes in both the table column are the department. So, we apply natural join, it will give the output as common tuples in both the table S and R.
Question 217
Which symbol denote derived attributes in the ER Model?
A
Double ellipse
B
Dashed ellipse
C
Squared ellipse
D
An ellipse with attribute name underlined
       Database-Management-System       ER-Model       ISRO-2017 May
Question 217 Explanation: 
Derived attributes are depicted by dashed ellipse.
Example:

Question 218
The join operation can be defined as
A
a cartesian product of two relations followed by a selection
B
a cartesian product of two relations
C
a union of two relations followed by cartesian product of the two relations
D
a union of two relations
       Database-Management-System       Relational-Algebra       ISRO CS 2008
Question 218 Explanation: 

→ The join operation can be defined as a cartesian product of two relations followed by a selection.

→ A SQL JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Different Types of SQL JOINs

1. INNER JOIN: Returns records that have matching values in both tables



2. LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table



3. RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table



4. FULL (OUTER) JOIN: Return all records when there is a match in either left or right table


Question 219
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
1
D
2
       Database-Management-System       B-and-B+-Trees       ISRO-2017 May
Question 219 Explanation: 
No. of nodes in 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 220
Consider the schema
Sailors(sid, sname, rating, age) with the following data

For the query
SELECT S.rating, AVG(S.age) AS average FROM Sailors S
Where S.age >= 18
GROUP BY S.rating
HAVING 1 < (SELECT COUNT(*) FROM Sailors S2 where S.rating = S2.rating)
The number of rows returned is
A
6
B
5
C
4
D
3
       Database-Management-System       SQL       ISRO-2017 December
Question 220 Explanation: 


Question 221
Consider a table that describes the customers : Customers(custid, name, gender, rating) The rating value is an integer in the range 1 to 5 and only two values (male and female) are recorded for gender. Consider the query “how many male customers have a rating of 5”? The best indexing mechanism appropriate for the query is
A
Linear hashing
B
Extendible hashing
C
B+ Tree
D
Bit-mapped hashing
       Database-Management-System       B-and-B+-Trees       ISRO-2017 December
Question 221 Explanation: 
→ A bitmap index is a special kind of database index that uses bitmaps.
→ Bitmap indexes have traditionally been considered to work well for low-cardinality columns, which have a modest number of distinct values, either absolutely, or relative to the number of records that contain the data.
→ The extreme case of low cardinality is Boolean data (e.g., does a resident in a city have internet access?), which has two values, True and False.
→ Bitmap indexes use bit arrays (commonly called bitmaps) and Solution queries by performing bitwise logical operations on these bitmaps. Bitmap indexes have a significant space and performance advantage over other structures for query of such data.
→ Their drawback is they are less efficient than the traditional B-tree indexes for columns whose data is frequently updated: consequently, they are more often employed in read-only systems that are specialized for fast query - e.g., data warehouses, and generally unsuitable for online transaction processing applications.
Question 222
Consider the join of a relation R , with a relation S . If R has m number of tuples and S has n number of tuples then the maximum and minimum sizes of the join respectively are:
A
m + n & 0
B
mn & 0
C
m + n & | m – n |
D
mn & m + n
       Database-Management-System       Relational-Algebra       ISRO-2016
Question 222 Explanation: 
For maximum:
If there is common attribute in R and S, and every row of R match with every row of S then total no. of tuples will be mn.
For minimum:
If there is no common attribute between R and S or if there is common attribute but none of the row of R matches with rows of S then output tuples will be 0.
Question 223
Let R(a, b, c) and S(d, e, f) be two relations in which d is the foreign key of S that refers to the primary key of R. Consider the following four operations R and S. I. Insert into R II. Insert into S III. Delete from R IV. Delete from S Which of the following can cause a violation of the referential integrity constraint above?
A
Both I and IV
B
Both II and III
C
All of these
D
None of these
       Database-Management-System       Constraints       ISRO-2016
Question 223 Explanation: 
Question 224
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       ISRO-2016
Question 224 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 225
Goals for the design of the logical scheme include
A
avoiding data inconsistency
B
being able to construct query easily
C
being able to access data efficiently
D
All of the above
       Database-Management-System       ISRO-2016
Question 225 Explanation: 
Logical Schema includes
1. Avoiding data inconsistency
2. Able to construct query easily
3. Able to access data efficiently
Question 226
Given the relations employee (name, salary, dept-no), and department (dept-no, dept-name,address) Which of the following queries cannot be expressed using the basic relational algebra operations (σ, π, x, -, ∪, p)
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       ISRO-2016
Question 226 Explanation: 
The sum of all employee 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 227
Trigger is
A
Statement that enables to start any DBMS
B
Statement that is executed by the user when debugging an application program
C
The condition that the system tests for the validity of the database user
D
Statement that is executed automatically by the system as a side effect of a modification of the database
       Database-Management-System       Trigger       ISRO-2016
Question 227 Explanation: 
→ A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database.
→ The trigger is mostly used for maintaining the integrity of the information on the database.
→ For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries.
→Triggers can also be used to log historical data, for example to keep track of employees' previous salaries.
Question 228
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-and-B+-Trees       ISRO-2016
Question 228 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 229
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       B-and-B+-Trees       ISRO-2016
Question 229 Explanation: 
Single level index
1. Primary index(Sparse): Ordered with key field
2. Clustered index(Sparse): Ordered with non key field
3. Secondary index(dense): Ordered with either key or non key field.
Question 230
The ‘command’ used to change contents of one database using the contents of another database by linking them on a common key field?
A
Replace
B
Join
C
Change
D
Update
       Database-Management-System       DML-commands       ISRO CS 2009
Question 230 Explanation: 
1. The REPLACE() function replaces all occurrences of a substring within a string, with a new substring.

2. The UPDATE statement is used to modify the existing records in a table.

3.An SQL join clause - corresponding to a join operation in relational algebra - combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join.

4.There is no change command in SQL
Question 231
A locked database file can be
A
Accessed by only one user
B
Modified by users with the correct password
C
Used to hide sensitive information
D
Updated by more than one user
       Database-Management-System       Transactions       ISRO CS 2009
Question 231 Explanation: 
File locking is a data management feature that restricts other users from changing a specific file.
This allows only one user or process access to this file at any given time.
This is to prevent the problem of interceding updates on the same files.
Question 232
Which of the following contains complete record of all activity that affected the contents of a database during a certain period of time?
A
Transaction log
B
Query language
C
Report writer
D
Data manipulation language
       Database-Management-System       Transactions       ISRO CS 2009
Question 232 Explanation: 
The transaction log is an integral part of database.
Every database has a transaction log that is stored within the log file that is separate from the data file.
A transaction log basically records all database modifications. When a user issues an INSERT, for example, it is logged in the transaction log.
This enables the database to roll back or restore the transaction if a failure were to occur and prevents data corruption.
Question 233
Purpose of ‘Foreign Key’ in a table is to ensure
A
Null Integrity
B
Referential Integrity
C
Domain Integrity
D
Null and Domain Integrity
       Database-Management-System       ER-Model       ISRO CS 2009
Question 233 Explanation: 
A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table.
In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table.
For example, a table called Employees has a primary key called employee_id.
Another table called Employee Details has a foreign key which references employee_id in order to uniquely identify the relationship between the two tables.
Question 234
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       ACID-properties       ISRO CS 2009
Question 234 Explanation: 
Irrecoverable error occurs when a transaction reads a data item after it is written by uncommitted transaction.
An irrecoverable error is an error that remains an error, no matter how many times you try to perform the same action. In the integration space, that could mean trying to access a database table that does not exist, which would cause the JDBC driver to throw back a SQLException
Question 235
Which one of the following in place sorting algorithms needs the minimum number of swaps?
A
Quick sort
B
Insertion sort
C
Selection sort
D
Heap sort
       Database-Management-System       Sorting       ISRO CS 2011
Question 235 Explanation: 
Selection sort requires maximum number of swaps i.e O(n).
The algorithm finds the minimum value, swaps it with the value in the first position, and repeats these steps for the remainder of the list. It does no more than n swaps, and thus is useful where swapping is very expensive.
Question 236
What is the equivalent serial schedule for the following transactions?
A
T1 − T2 − T3
B
T3 − T1 − T2
C
T2 − T1 − T3
D
T1 − T3 − T2
       Database-Management-System       Transactions       ISRO CS 2011
Question 236 Explanation: 
From the following precedence graph, T3 → T1→ T2

Question 237
Which type of DBMS provides support for maintaining several versions of the same entity?
A
Relational Database Management System
B
Hierarchical
C
Object Oriented Database Management System
D
Network
       Database-Management-System       ER-Model       ISRO CS 2011
Question 237 Explanation: 
Many object databases, for example Gemstone or VOSS, offer support for versioning.
An object can be viewed as the set of all its versions.
Object versions can be treated as objects in their own right. Some object databases also provide systematic support for triggers and constraints which are the basis of active databases.
Question 238
Which normal form is based on the concept of ‘full functional dependency’ is
A
First Normal Form
B
Second Normal Form
C
Third Normal Form
D
Third Normal Form
       Database-Management-System       Normalization       ISRO CS 2011
Question 238 Explanation: 
A full functional dependency is a state of database normalization that equates to the normalization standard of Second Normal Form (2NF).
Second normal form (2NF) is a normal form used in database normalization.
To qualify for second normal form a relation must:
→be in first normal form (1NF)
→not have any non-prime attribute that is dependent on any proper subset of any candidate key of the relation.
A non-prime attribute of a relation is an attribute that is not a part of any candidate key of the relation.
Question 239
In functional dependency Armstrong inference rules refers to
A
Reflexive, Augmentation and Decomposition
B
Transitive, Augmentation and Reflexive
C
Augmentation, Transitive, Reflexive and Decomposition
D
Reflexive, Transitive and Decomposition
       Database-Management-System       Functional-dependency       ISRO CS 2011
Question 239 Explanation: 
Armstrong's axioms are a set of axioms (or, more precisely, inference rules) used to infer all the functional dependencies on a relational database.
The axioms are sound in generating only functional dependencies in the closure of a set of functional dependencies when applied to that set
Axiom of Reflexivity:
→If X is a set of attributes and Y is a subset of X, then X holds Y. Hereby, X holds Y ( X -> Y) means that X functionally determines Y.
Axiom of Augmentation:
→If X holds Y and Z is a set of attributes, then XZ holds YZ. It means that attribute in dependencies does not change the basic dependencies.
Axiom of Transitivity:
The axiom of transitivity says if X holds Y, and Y holds Z, then X must also hold Z.
Question 240
Which of the following is the highest isolation level in transaction management?
A
Serializable
B
Repeated Read
C
Committed Read
D
Uncommitted Read
       Database-Management-System       Transactions       ISRO CS 2013
Question 240 Explanation: 
→Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions.
→Isolation levels are described in terms of which concurrency side effects, such as dirty reads or phantom reads, are allowed.
→The highest isolation level, serializable, guarantees that a transaction will retrieve exactly the same data every time it repeats a read operation, but it does this by performing a level of locking that is likely to impact other users in multi-user systems.
Question 241
Consider the following relational schema:
Suppliers (sid:integer, sname:string, sadress:string)
Parts (pid:integer, pname:string, pcolor:string)
Catalog (sid:integer, pid:integer, pcost:real)
What is the result of the following query?
(SELECT Catalog.pid from Suppliers, Catalog
WHERE Suppliers.sid = Catalog.pid)
MINUS
(SELECT Catalog.pid from Suppliers, Catalog
WHERE Suppliers.sname <> 'sachin' and Suppliers.sid = Catalog.sid)
A
pid of Parts supplied by all except sachin
B
pid of Parts supplied only by sachin
C
pid of Parts available in catalog supplied by sachin
D
pid of Parts available in catalogs supplied by all except sachin
       Database-Management-System       SQL       ISRO CS 2013
Question 241 Explanation: 
→SELECT Catalog.pid from Suppliers, Catalog WHERE Suppliers.sid = Catalog.pid The above query will gives all pids of both Catalog and Supplier .
→SELECT Catalog.pid from Suppliers, Catalog WHERE Suppliers.sname <> 'sachin' and Suppliers.sid = Catalog.sid
*The above query will gives the pids of all parts which are supplied by any other supplier other than Sachin.
→The SQL MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement
→The entire query will get the pids which are supplied by only Sachin.
Question 242
Consider the following dependencies and the BOOK table in a relational database design. Determine the normal form of the given relation.
ISBN → Title
ISBN → Publisher
Publisher → Address
A
First Normal Form
B
Second Normal Form
C
Third Normal Form
D
BCNF
       Database-Management-System       Normalization       ISRO CS 2013
Question 242 Explanation: 
→All attributes in the given functional dependencies are atomic values So it is FIrst normal form.
→In order to check whether it is in Second normal form or not,fist we need to find the candidate key.
→After finding closure of attribute of ISBN from the above dependencies,the candidate key is ISBN.
→A relation is in 2NF if it is in 1NF and every non-prime attribute of the relation is dependent on the whole of every candidate key.
→The given dependencies satisfies second normal form rules as as non prime attribute of the relation is dependent on the whole of every candidate key.
→So the above dependencies are in Second normal form.
Question 243
Calculate the order of leaf(pleaf) and non leaf(p) nodes of a B+ tree based on the information given below Search key field = 12 bytes Record pointer = 10 bytes Block pointer = 8 bytes Block size = 1 KB
A
pleaf = 51 & p = 46
B
pleaf= 47 & p = 52
C
pleaf= 46 & p = 50
D
pleaf = 52 & p = 47
       Database-Management-System       B+trees       ISRO CS 2013
Question 243 Explanation: 
In B+ trees the satellite information(record information) is stored in only leaf nodes and not in the non leaf nodes, so no need to include record pointer in the non-leaf nodes.
i) leaf node:
let the order of leaf be 'n'
size of search key field * n + record pointer * n + block pointer <= 1024
12 * n + 10 * n + 8 <= 1024
22n <= 1016
n = 46
ii) for non-leaf node:
size of search key field * n + block pointer * (n+1) <= 1024
12 * n + 8 * n + 8 = 1024
n = 50.8
order of non-leaf node (p) = 50
Question 244
The physical location of a record determined by a formula that transforms a file key into a record location is
A
Hashed file
B
B-Tree file
C
Indexed file
D
Sequential file
       Database-Management-System       B+trees       ISRO CS 2013
Question 244 Explanation: 
Hash File organization method is the one where data is stored at the data blocks whose address is generated by using hash function.
Question 245
Embedded pointer provides
A
a secondary access path
B
a physical record key
C
an inverted index
D
a prime key
       Database-Management-System       Storage       ISRO CS 2013
Question 245 Explanation: 
1. To understand how pointers and their associated data elements are allocated in Microsoft RPC, you have to differentiate between top-level pointers and embedded pointers
2. Top-level pointers are those that are specified as the names of parameters in function prototypes. Top-level pointers and their referents are always allocated on the server.
3. Embedded pointers are pointers that are embedded in data structures such as arrays, structures, and unions. When embedded pointers only write output to a buffer and are null on input, the server application can change their values to non-null. In this case, the client stubs allocate new memory for this data.
4.If the embedded pointer is not null on the client before the call, the stubs do not allocate memory on the client on return. Instead, the stubs attempt to write the memory associated with the embedded pointer into the existing memory on the client associated with that pointer, overwriting the data already there.
Question 246
An aggregation, the association is drawn using which symbol?
A
A line which loops back on to the same table
B
A small open diamond at the end of a line connecting two tables
C
A small closed diamond at the end of a line connecting two tables
D
A small closed triangle at the end of a line connecting two tables
       Database-Management-System       ISRO CS 2014
Question 246 Explanation: 
Association: Relationship between 2 objects. It defines the multiplicity between objects like 1-1, 1-many, many-1, many to many.
Aggregation: A directional between objects. When an object “has-a” another object, then you have got an aggregation between them, you have got an aggregation between them. Direction between them specified which object contains the other object.
Question 247
Let x, y, z, a, b, c be the attributes of an entity set E. If {x}, {x,y}, {a,b}, {a,b,c}, {x,y,z} are superkeys then which of the following are the candidate keys?
A
{x,y} and {a,b}
B
{x} and {a,b}
C
{x,y,z} and {a,b,c}
D
{z} and {c}
       Database-Management-System       Keys       ISRO CS 2014
Question 247 Explanation: 
● A candidate key is simply the "shortest" super key. Candidate Key are individual columns in a table that qualifies for uniqueness of each row/tuple.Every table must have at least one candidate key but at the same time can have several.
Question 248
Consider the following table

The table is in which normal form?
A
First Normal Form
B
Second Normal Form
C
Third Normal Form but not BCNF
D
Third Normal Form but BCNF
       Database-Management-System       Normalization       ISRO CS 2014
Question 248 Explanation: 
From the given figure, we can write functional dependencies as
AB→ CDE
C→ B
The candidate keys are "AB" and "AC".
The relation is not in BCNF because the functional dependencies C→ B do not contains the key AB or AC
So, the given relation is in 3NF but not in BCNF
Question 249
Consider the schema R(A,B,C,D) and the functional dependencies A→ B and C→ D. If the decomposition is made as R1(A,B) and R2(C,D), then which of the following is TRUE?
A
Preserves dependency but cannot perform lossless join
B
Preserves dependency and performs lossless join
C
Does not perform dependency and cannot perform lossless join
D
Does not preserve dependency but perform lossless join
       Database-Management-System       Functional-Dependency       ISRO CS 2014
Question 249 Explanation: 
Given Data,
Schema R(A, B, C, D)
Functional dependencies are A→ B and C→ D
Decomposed Schema is R1(A,B) and R2(C,D)
Dependency preservation decomposition:
It is another property of decomposed relational database schema D in which each functional dependency X → Y specified in F either appeared directly in one of the relation schemas Ri in the decomposed D or could be inferred from the dependencies that appear in some Ri.
Decomposition D={ R1 , R2, R3,,.., ,Rm} of R is said to be dependency-preserving with respect to F if the union of the projections of F on each Ri , in D is equivalent to F.
In other words, R ⊂ join of R1, R1 over X. The dependencies are preserved because each dependency in F represents a constraint on the database. If decomposition is not dependency-preserving, some dependency is lost in the decomposition.
→ R1(A,B) is covered A→ B
→ R2(C,D) is covered C→ D
It is Functional Dependency preserving because both the functional dependencies are covered.
Lossless join:
The decomposition is a lossless-join decomposition of R if at least one of the following functional dependencies are in F+ (where F+ stands for the closure for every attribute or attribute sets in F):
R1 ∩ R2 → R1
R1 ∩ R2 → R2
According to functional dependency R1(A,B) ∩ R2(C,D) = null. There is no common key in both the tables. So, it is not lossless join.
Question 250
Every time the attribute A appears, it is matched with the same value of attribute B but not the same value of attribute C. Which of the following is true?
A
A→ (B,C)
B
A → B, A→→ C
C
A→ B, C→→A
D
A→→B, B→ C
       Database-Management-System       Functional-Dependency       ISRO CS 2014
Question 250 Explanation: 
→ represents functional dependency and
→→ is multivalued dependency.
Functional Dependency:
A → B means that the values of B are determined by the values of A. Two tuples sharing the same values of A will necessarily have the same values of B.
Multivalued dependency: It is a special case of a join dependency, with only two sets of values involved, i.e. it is a binary join dependency.
A multivalued dependency exists when there are at least three attributes (like A,B and C) in a relation and for a value of A there is a well defined set of values of B and a well defined set of values of C. However, the set of values of B is independent of set C and vice versa.
Question 251

In RDBMS, which type of Join returns all rows that satisfy the join condition ?

A
Inner Join
B
Outer Join
C
Semi Join
D
Anti Join
       Database-Management-System       Relational-databases       UGC-NET CS 2018 JUNE Paper-2
Question 251 Explanation: 
Inner Join :
Inner join combines two tables having a common attributes.
While combining it only join the rows of two tables having same value in common attribute.
So, in that way inner join return the records having matching values in both the tables.
Question 252

Consider a relation book(title, price) which 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) < 7
A
Titles of the six most expensive books.
B
Title of the sixth most expensive books.
C
Titles of the seven most expensive books.
D
Title of the seventh most expensive books.
       Database-Management-System       SQL       UGC-NET CS 2018 JUNE Paper-2
Question 252 Explanation: 
→ SQL query, which results titles of the 7 most expensive books.
→ The where clause of outer query will be true for 7 most expensive books.
Note: All aggregate functions except count(*) ignore NULL values in their input collection.
Question 253

In a Hierarchical database, a hashing function is used to locate the

A
Collision
B
Root
C
Foreign Key
D
Records
       Database-Management-System       B-and-B+-Trees       UGC-NET CS 2018 JUNE Paper-2
Question 253 Explanation: 
→ A hierarchical database model is a data model in which the data are organized into a tree-like structure. The data are stored as records which are connected to one another through links. A record is a collection of fields, with each field containing only one value. The type of a record defines which fields the record contains.
→ The hierarchical database model mandates that each child record has only one parent, whereas each parent record can have one or more child records.
→ In order to retrieve data from a hierarchical database the whole tree needs to be traversed starting from the root node.
→ In a Hierarchical database, a hashing function is used to locate the root node.
Question 254

Relations produced from E-R Model will always be in

A
1 NF
B
2 NF
C
3 NF
D
4 NF
       Database-Management-System       ER-Model       UGC-NET CS 2018 JUNE Paper-2
Question 254 Explanation: 
Relations produced from E-R Model will always be in 3NF.
Question 255

Consider the following schedules involving two transactions.

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

Which one of the following statements is correct with respect to above ?

A
Both S1 and S2 are conflict serializable.
B
Both S1 and S2 are not conflict serializable.
C
S1 is conflict serializable and S2 is not conflict serializable.
D
S1 is not conflict serializable and S2 is conflict serializable.
       Database-Management-System       Transactions       UGC-NET CS 2018 JUNE Paper-2
Question 255 Explanation: 
Question 256

For a database relation R(a, b, c, d) where the domains of a, b, c and d include only atomic values, and only the following functional dependencies and those that can be inferred from them hold :

    a → c
    b → d

The relation is in

A
First normal form but not in second normal form
B
Second normal form but not in third normal form
C
Third normal form
D
BCNF
       Database-Management-System       Normalization       UGC-NET CS 2018 JUNE Paper-2
Question 256 Explanation: 
Primary key of given relation is “ab”.
And there is a partial dependency exist in given FD’s so the given relation is in 1NF but not in second normal form.
Question 257

A many-to-one relationship exists between entity sets r1 and r2. How will it be represented using functional dependencies if Pk(r) denotes the primary key attribute of relation r?

A
Pk(r1) → Pk(r2)
B
Pk(r2) → Pk(r1)
C
Pk(r2) → Pk(r1) and Pk(r1) → Pk(r2)
D
Pk(r2) → Pk(r1) or Pk(r1) → Pk(r2)
       Database-Management-System       Normalization       UGC-NET CS 2018 JUNE Paper-2
Question 257 Explanation: 

Here, we have a many to one relationship between between Set(r1) and Set(r2).
→ Elements of Set(r2) can’t identify elements of Set(r1) because one value element in Set(r2) is pointing to more than one element of Set(r1).
→ So, we can’t say Pk(r2) → Pk(r1) but elements of Set(r1) are pointing to exactly one element of Set(r2) so we can say that Pk(r2) → Pk(r1) because r1 is uniquely identifying r2.
Question 258

Database systems that store each relation in a separate operating system file may use the operating system’s authorization scheme, instead of defining a special scheme themselves. In this case, which of the following is false ?

A
The administrator enjoys more control on the grant option.
B
It is difficult to differentiate among the update, delete and insert authorizations.
C
Cannot store more than one relation in a file.
D
Operations on the database are speeded up as the authorization procedure is carried out at the operating system level.
       Database-Management-System       Relational-databases       UGC-NET CS 2018 JUNE Paper-2
Question 258 Explanation: 
When the Database system will store each relation in seperate operating system file then it will become difficult for the administrator to differentiate among the update, delete and insert authorizations and also to keep track of grant options becomes difficult which is a overhead.
So, option 1 is clearly false and option 2 is true.
Option 3: In question it is mentioned that each relation is stored in a separate operating system file.
So, option 3 is true.
Option 4 : Since for each relation there is a seperate operating system file which may use the operating system’s authorization scheme.
So, Operations on the database are speeded up as the authorization procedure is carried out at the operating system level.
Question 259

Let R1(a,b,c) and R2(x,y,z) be two relations in which a is the foreign key of R1 that refers to the primary key of R2 . Consider following four options.

    (a) Insert into R1
    (b) Insert into R2
    (c) Delete from R1
    (d) Delete from R2

Which of the following is correct about the referential integrity constraint with respect to above ?

A
Operations (a) and (b) will cause violation.
B
Operations (b) and (c) will cause violation.
C
Operations (c) and (d) will cause violation.
D
Operations (d) and (a) will cause violation.
       Database-Management-System       Relational-databases       UGC-NET CS 2018 JUNE Paper-2
Question 259 Explanation: 
In case of referential integrity Insertion into table containing the foreign key and Deletion from table whose Primary key is referred can cause the violation.
Question 260
___symbol is used to denote derived attribute in ER model
A
Dashed ellipse
B
Square ellipse
C
Ellipse with attribute name undIrected
D
Rectangular box
       Database-Management-System       Nielit Scentist-B [02-12-2018]
Question 260 Explanation: 
Derived attributes are depicted by dashed ellipse.
Example:
Question 261
Maximum number of superkeys for the relation schema R(X,Y,Z,W) with X as the key is:
A
6
B
8
C
9
D
12
       Database-Management-System       Nielit Scentist-B [02-12-2018]
Question 261 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: X, XY, XZ, YZ, XYZ, XYW, YZW, XYZW.
Question 262
Identify the true statement from the given statements.
(1) Lossless, dependency preserving decomposition into 3NF is always possible
(2) Any relation with two attributes is in BCNF
A
(1)
B
(2)
C
(1) and (2)
D
None of these
       Database-Management-System       Nielit Scentist-B [02-12-2018]
Question 262 Explanation: 
Both the statements are true.
(1) TRUE: Lossless, dependency preserving decomposition into 3NF is always possible but not in BCNF. In BCNF lossless is mandatory but dependency preserving is not mandatory.
(ii) TRUE: Any relation with two attributes is in BCNF
Question 263
Identify the true statement from the given statements
(1) Number of child pointers in a B/ B+ tree node is always equal to number of keys in it plus one.
(2) B/B+ tree is defined by a term minimum depends on hard disk block size, key and address sizes.
A
(1)
B
(1) and (2)
C
(2)
D
None of these
       Database-Management-System       Nielit Scentist-B [02-12-2018]
Question 263 Explanation: 
The above two statements are true.
Question 264
The following table has two attributes X and Y where X is the primary key and Y is the foreign key referencing X with on delete cascade

The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (3,4) is deleted is:
A
(4,3) and (6,4)
B
(2,4) and (7,2)
C
(3,2) and (9,5)
D
(3,4),(4,3) and (6,4)
       Database-Management-System       Nielit Scentist-B [02-12-2018]
Question 264 Explanation: 
→ On removal of row (3,4), row (4,3) must also be deleted as they depend on value 3.
→ On removal of row (4,3), row (2,4) and (6,4) must also be deleted as it depends on value 4.
→ As there is no option with row(2,4) and also the question says additional tuples should be deleted. So, option D is eliminated.
Question 265
An object can have which of the following multiplicities?
A
Zero
B
More than one
C
One
D
All of the above
       Database-Management-System       ER-Model       Nielit Scientist-B IT 4-12-2016
Question 265 Explanation: 
Multiplicity is a definition of cardinality - i.e. number of elements of some collection of elements by providing an inclusive interval of non-negative integers to specify the allowable number of instances of described element. Multiplicity interval has some lower bound and (possibly infinite) upper bound
Question 266
_____ is NOT a part of the ACID properties.
A
Inconsistency
B
Consistency
C
Atomicity
D
Isolation
       Database-Management-System       Nielit Scentist-B [02-12-2018]
Question 266 Explanation: 
ACID means Atomicity, Consistency ,Isolation and Durability
Atomicity: Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single "unit", which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors and crashes.
Consistency : Consistency ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This prevents database corruption by an illegal transaction, but does not guarantee that a transaction is correct.
Isolation : Transactions are often executed concurrently (e.g., reading and writing to multiple tables at the same time). Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially. Isolation is the main goal of concurrency control; depending on the method used, the effects of an incomplete transaction might not even be visible to other transactions.
Durability :Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash). This usually means that completed transactions (or their effects) are recorded in non-volatile memory.
Question 267
Consider the following functional dependencies in a database:
A → B
B → C
D → E
E → D
F → G
F → H
(E,F) → I
The relation (E,D,A,B) is :
A
2 NF
B
3 NF
C
BCNF
D
None of the above
       Database-Management-System       Nielit Scentist-B [02-12-2018]
Question 267 Explanation: 
Functional dependencies of the given subrelation R(E,D,A,B) are:
A → B
D → E
E → D
Since A is not determined by any of the given functional dependencies for R(E,D,A,B) so it must be included in the key now check whether “A” is the Primary key or not.
(A)+ = {A}
(AB)+ = {AB}
(AD)+ = {ABDE}
(AE)+ = {ABDE}
So (AD)+ and (AE)+ are the primary keys of R(E,D,A,B).
A → B, in this given FD there exist a partial dependency because here a prime key attribute(attribute which is a part of primary key but not a primary key itself of a function) which is determining a non-key attribute.
So the relation R(E,D,A,B) is not in 2NF .
Question 268
Normalization from which is based on transitive dependency is classified as:
A
First normal form
B
Second normal form
C
Fourth normal form
D
Third normal form
       Database-Management-System       Normalization       Nielit Scientist-B IT 4-12-2016
Question 268 Explanation: 
The table is in 3NF if and only if both of the following conditions hold:
1. The relation R (table) is in second normal form (2NF)
2. Every non-prime attribute of R is non-transitively dependent on every key of R.
Question 269
Which of the following is a fundamental operation in relational algebra?
A
Set intersection
B
Assignment
C
Natural Join
D
None of the above
       Database-Management-System       Relational-Algebra       Nielit Scientist-B IT 4-12-2016
Question 269 Explanation: 
Fundamental operation in relational algebra
1.Select
2.Project
3.Cartesian Product
4.Rename
5.Union
6.Set Difference
Question 270
The primary key is selected from the:
A
Composite keys
B
Determinants
C
Candidate keys
D
Foreign keys
       Database-Management-System       Normalization       Nielit Scientist-B IT 4-12-2016
Question 270 Explanation: 
● A candidate key is a column or a row of columns in a table which identifies any database record without utilizing any other data.
● Each table can have one or more candidate keys, but one candidate key is unique, and it is called the primary key.
Question 271
In functional dependency between two sets of attribute A and B then set of attributes A of database is classified as:
A
Top right side
B
Down left side
C
Left hand side
D
Right hand side
       Database-Management-System       Functional-Dependency       Nielit Scientist-B IT 4-12-2016
Question 271 Explanation: 
A functional dependency (FD) is a relationship between two attributes, typically between the PK and other non-key attributes within a table. For any relation R, attribute Y is functionally dependent on attribute X (usually the PK), if for every valid instance of X, that value of X uniquely determines the value of Y.
This relationship is indicated by the representation below :
X ———–> Y
The left side of the above FD diagram is called the determinant, and the right side is the dependent.
Question 272
Which type of statement can execute parameterized queries?
A
PreparedStatement
B
Parameterized Statement
C
ParameterizedStatement and CallableStatement
D
All kinds of Statements
       Database-Management-System       SQL       Nielit Scientist-B IT 4-12-2016
Question 272 Explanation: 
The main feature of a PreparedStatement object is that, unlike a Statement object, it is given a SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled.
As a result, the PreparedStatement object contains not just a SQL statement, but a SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.
Although PreparedStatement objects can be used for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it.
Question 273
Which of the following is a fundamental operation in relational algebra?
A
Set intersection
B
Natural Join
C
Assignment
D
None of the above
       Database-Management-System       Relational-Algebra       Nielit Scientist-B IT 4-12-2016
Question 273 Explanation: 
Fundamental operation in relational algebra 1.Select
2.Project
3.Cartesian Product
4.Rename
5.Union
6.Set Difference
Question 274
Which one of the following statements about normal forms is FALSE?
A
BCNF is stricter than 3NF
B
Lossless,dependency preserving decomposition