Database-Management-System

Question 1

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

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 2 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 3

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

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 4 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 5

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

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

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

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       Video-Explanation
Question 8 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 9

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       Video-Explanation
Question 9 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 10

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       Video-Explanation
Question 10 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 11

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       Video-Explanation
Question 11 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 12

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       Video-Explanation
Question 12 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 13
In a relational data model, which one of the following statements is TRUE?
A
A relation with only two attributes is always in BCNF.
B
If all attributes of a relation are prime attributes, then the relation is in BCNF.
C
Every relation has at least one non-prime attribute.
D
BCNF decompositions preserve functional dependencies.
       Database-Management-System       Normalization       GATE 2022       Video-Explanation
Question 13 Explanation: 
A relation with only two attributes will always be in BCNF.
Example:
R(A, B).
Two functional dependencies possible for the relation: (1) A->B and (2) B->A
If there is no functional dependency, we can assume trivial functional dependencies like AB->A and AB->B.
In all cases, functional dependencies like A->B, A must be a key.
So they all will be in BCNF irrespective of the functional depencies set.
Question 14
Consider the following three relations in a relational database.
Employee ( eId , Name ), Brand ( bId , bName ), Own ( eId , bId )
Which of the following relational algebra expressions return the set of elds who own all the brands?
A
B
C
D
       Database-Management-System       Relational-databases       GATE 2022       Video-Explanation
Question 14 Explanation: 
Option (A) returns eid’s which owns every brand of the relation Brand.
In relational algebra, divide (/) is not a basic operator and it can be derived from the basic operators. In option (B), the divide operator is derived using basic operators and which is equivalent to option (A)
Question 15
Consider a relation R ( A , B , C , D , E ) with the following three functional dependencies.
AB → C; BC → D; C → E;
The number of superkeys in the relation R is _____________.
A
8
       Database-Management-System       Functional-Dependency       GATE 2022       Video-Explanation
Question 15 Explanation: 
R(A, B, C, D, E)
AB → C
BC → D
C → E
¯ The attributes A, B are not there in the right hand side of any of the given FDs.
¯ So, AB can be a candidate key.
(AB)^+ = ABCDE
¯ (AB)^+ is deriving all the attributes of R Hence, AB is a candidate key.
¯ The number of super keys possible for R with “AB” candidate kay is:
2^5 – 2 = 2^3
= 8
Question 16
Let Ri(z) and Wi(z) denote read and write operations on a data element z by a transaction Ti, respectively. Consider the schedule S with four transactions.
S: R4(x) R2(x) R3(x) R1(y) W1(y) W2(x) W3(y) R4(y)
Which one of the following serial schedules is conflict equivalent to S
A
B
C
D
       Database-Management-System       Transactions       GATE 2022       Video-Explanation
Question 16 Explanation: 
The given schedule ‘s’ is:

Precedence Graph

T1 → T3 → T4 → T2
Question 17
Consider the relational database with the following four schemas and their respective instances.
Student(sNo, sName, dNo) Dept(dNo, dName)
Course(cNo, cName, dNo)
Register(sNo, cNo)


The number of rows returned by the above SQL query is___________.
A
2
       Database-Management-System       SQL       GATE 2022       Video-Explanation
Question 17 Explanation: 
Final result of the given SQL query is:
—–+———+——+
| sNo | sName | dNo |
+—–+———+——+
| S01 | James | D01 |
| S04 | Jane | D01 |
+—–+———+——+
Question 18

A library relational database system uses the following schema

USERS (User#, UserName, HomeTown)
BOOKS (Book#, BookTitle, AuthorName)
ISSUED (Book#, User#, Date) 

Explain in one English sentence, what each of the following relational algebra queries is designed to determine

(a) σ User #=6 (11 User #, Book Title ((USERS ISSUED) BOOKS))
(b) σ Author Name (BOOKS (σ Home Town) = Delhi (USERS ISSUED))) 
A
Theory Explanation.
       Database-Management-System       SQL       GATE 1996
Question 19

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 19 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 20

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 20 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 21

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

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 22 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 23

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 23 Explanation: 
Indexing will be on occupation field because occupation field lexigraphically sorted will give the sequence 1, 3, 2, 5, 4.
Question 24

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 24 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 25

(a) Suppose we have a database consisting of the following three relations.
FREQUENTS(student, parlor) giving the parlors each student visits.
SERVES(parlor, ice-cream) indicating what kind of ice-creams each parlor serves.
LIKES(student, ice-cream) indicating what ice-creams each parlor serves.
(Assuming that each student likes at least one ice-cream and frequents at least one parlor)
Express the following in SQL:
Print the students that frequent at least one parlor that serves some ice-cream that they like.

(b) In a computer system where the ‘best-fit’ algorithm is used for allocating ‘jobs’ to ‘memory partitions’, the following situation was encountered:

When will the 20K job complete? Note – This question was subjective type.

A
Theory Explanation.
       Database-Management-System       SQL       GATE 1998
Question 26

(a) Four jobs are waiting to be run. Their expected run times are 6, 3, 5 and x. In what order should they be run to minimize the average response time?

(b) Write a concurrent program using par begin – par end to represent the precedence graph shown below.

A
Theory Explanation.
       Database-Management-System       Precedence-graph       GATE 1998
Question 27

Consider the following database relations containing the attributes

Book_id 
Subject_Category_of_book
Name_of_Author
Nationality_of_Author 
with Book_id as the Primary Key. 

(a) What is the highest normal form satisfied by this relation?

(b) Suppose the attributes Book_title and Author_address are added to the relation, and the primary key is changed to (Name_of_Author, Book_Title), what will be the highest normal form satisfied by the relation?

A
Theory Explanation.
       Database-Management-System       SQL       GATE 1998
Question 28

Consider the following relational database schemes:

  COURSES(Cno, name)
  PRE-REQ(Cno, pre_Cno)
  COMPLETED(student_no, Cno) 

COURSES give the number and the name of all the available courses.
PRE-REQ gives the information about which course are pre-requisites for a given course.
COMPLETED indicates what courses have been completed by students.

Express the following using relational algebra:
List all the courses for which a student with student_no 2310 has completed all the pre-requisites.

A
Theory Explanation.
       Database-Management-System       SQL       GATE 1998
Question 29

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 29 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 30

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 30 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 31

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 31 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 32

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

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 34 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 35

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

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

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 36 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 37

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 37 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 38

Consider a B-tree with degree m, that is, the number of children, c, of any internal node (except the root) is such that m ≤ c ≤ 2m-1. Derive the maximum and minimum number of records in the leaf nodes for such a B-tree with height h, h≥1. (Assume that the root of a tree is at height 0.)

A
Theory Explanation.
       Database-Management-System       B-and-B+-Trees       GATE 1999
Question 39

Consider the set of relations

EMP(Employee-no, Dept-no, Employee-name, Salary)
DEPT(Dept-no, Dept-name, Location) 

Write an SQL query to:
(a) Find all employee names who work in departments located at “Calcutta” and whose salary is greater than Rs. 50,000.
(b) Calculate, for each department number, the number of employees with a salary greater than Rs. 100,000.

A
Theory Explanation.
       Database-Management-System       SQL       GATE 1999
Question 40

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 40 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 41

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 41 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 42

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 42 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 43

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 43 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 44

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

(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 45 Explanation: 
(a)
(i)

(ii)

(b)
Question 46

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 47

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

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 48 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 49

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 49 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 50

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 50 Explanation: 
If it is BCNF then it is 3NF. But the relation is in 3NF then it is need not to be in BCNF.
There are 50 questions to complete.

Access subject wise (1000+) question and answers by becoming as a solutions adda PRO SUBSCRIBER with Ad-Free content

Register Now