0% found this document useful (0 votes)

54 views91 pages

Dbms WB Print

The document is a workbook for a Database Management System course aimed at Computer Science Engineering and Information Technology students preparing for GATE, ESE, and PSUs. It includes a syllabus covering topics such as ER-Model, Relational Model, SQL, and transactions, along with a table of contents listing chapters and practice questions related to normalization and functional dependencies. The workbook contains various questions from past GATE exams to help students understand and apply database concepts.

Uploaded by

Soumen Golder

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

54 views91 pages

Dbms WB Print

Uploaded by

Soumen Golder

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

You are on page 1/ 91

Database

Management System
PEN-Drive / G-Drive Course / VOD & Tablet Users

Workbook

Computer Science Engineering

Information Technology

GATE / ESE / PSUs

Ankush Saklecha Sir

GATE Syllabus
1. ER‐Model
2. Relational Model : Relational Algebra, Tuple Calculus, SQL.
3. Integrity Constraints, Normal Forms.
4. File Organization, Indexing (e.g., B and B+ trees).
5. Transactions and Concurrency Control.

Table of Contents
Sr. Chapter Name Page No

1. Relational Algebra and Relational Calculus ………..……. 1

2. Transaction and Serializability………………….…….………. 16

3. File Organization & Indexing ……………………….…………. 32

4. Hashing……………………………..……………….…….….…………. 42

5. Structured Query Language ……………………………………. 47

6. Relational Algebra and Calculus………………..………..……. 69

7. ER Diagram……………………………….……….………….…………. 83
1 Normalization &
Functional Dependency

Classroom Practice Questions :

Q.1 Consider a relational table R that is in 3 NF, but not in BCNF. Which one of the following statements is
TRUE? [GATE 2020]
(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.
Q.2 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. [GATE 2019]
I. Both Y and Z are in BCNF
II. Decomposition of X into Y and Z is dependency preserving and a 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
Q.3 Consider the following four relational schemas. For each schema, all non-trivial functional dependencies
are listed. The underlined attributes are the respective primary keys. [GATE 2018]
(A) 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 :
roll no.  courses
(B) Schema II: Registration (rollno, courseid, email)
Non-trivial functional dependencies :
rollno, courseid  email
email  rollno
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 2 GATE ACADEMY®
(C) Schema III: Registration (rollno, courseid, marks, grade)
Non-trivial functional dependencies :
rollno, courseid  marks, grade
marks  grade
(D) Schema IV : Registration (rollno, courseid, credit)
Non-trivial functional dependencies :
rollno, courseid  credit
courseid  credit
Which one of the relational schemas above is in 3 NF but not in BCNF?
(A) Schema I (B) Schema II (C) Schema III (D) Schema IV
Q.4 The following functional dependencies hold true for the relational schema R{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? [GATE 2017]
(A) V  W (B) V  W
VX WX
YV YV
YZ YZ
(C) V  W (D) V  W
VX WX
YV YV
YX YX
YZ YZ
Q.5 Which of the following is NOT a super key in a relational schema with attributes V, W, X, Y, Z and
primary key V Y? [GATE 2016]
(A) VXYZ (B) VWXZ (C) VWXY (D) VWXYZ
Q.6 A database of research articles in a journal uses the following schema. [GATE 2016]
(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) 1 NF (B) 2 NF (C) 3 NF (D) BCNF
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 3 Normalization & Functional Dependency
Q.7 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? [GATE 2015]
(A) {P, R}  {S, T} (B) {P, R}  {R, T}
(C) {P, S}  {S} (D) {P, S, U}  {Q}
Q.8 Consider the relation schema 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 of R? [GATE 2014]
(A) {E,F} (B) {E,F,H} (C) {E,F,H,K,L} (D) {E}
Q.9 Given the following two statements: [GATE 2014]
S1: Every table with two single-valued attributes is in 1 NF, 2 NF, 3 NF 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
Q.10 A prime attribute of a relation scheme R is an attribute that appears. [GATE 2014]
(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

Common Data for

Questions 11 to 12

Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values.
F = {CH  G, A  BC, B  CFH, E  A, F  EG} is a set of functional dependencies (FDs) so that F+
is exactly the set of FDs that hold for R.
Q.11 How many candidate keys does the relation R have? [GATE 2013]
(A) 3 (B) 4 (C) 5 (D) 6
Q.12 The relation R is [GATE 2013]
(A) In 1 NF, but not in 2 NF (B) In 2 NF, but not in 3 NF
(C) In 3 NF, but not in BCNF (D) In BCNF
Q.13 Which of the following is TRUE? [GATE 2012]
(A) Every relation is 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.
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 4 GATE ACADEMY®
(C) Every relation in BCNF is also in 3NF.
(D) No relation can be in both BCNF and 3NF.
Q.14 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. [GATE 2011]
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 joint accounts. This attributes stores the primary account number.
4. Name: Name of the student.
5. Hostel_Room: Room number of the hostel.
Which of the following options is INCORRECT?
(A) BankAccount_Num is 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 super key such that S∩UID is NULL then S∪UID is also a super key.
Q.15 The following functional dependencies hold for relations [GATE 2010]
R (A, B, C) and S (B, D, E):
B  A,A  C
The relation R contains 200 tuples and the relation 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
Q.16 Consider the following relational schema:
Suppliers (Sid, sname, city, street)
Parts (pid, pname:string, color:string)
Catalog (Sid, pid,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. [GATE 2009]
Which one of the following is TRUE about the above schema?
(A) The schema is in BCNF. (B) The schema is in 3 NF but not in BCNF.
(C) The schema is in 2 NF but not in 3 NF (D) The schema in not in 2 NF.
Q.17 Consider the following relational schemes for a library database: [GATE 2008]
Book (Title, Author, Catalog_no, Publisher, Year, Price)
Collection (Title, Author, Catalog_no)
With 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 3 NF only.
(C) Book is in 2 NF and Collection is in 3 NF. (D) Both Book and Collection are in 2 NF only.

Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 6 GATE ACADEMY®
Q.25 Consider a relation scheme
R = (ABCDEH) on which the following functional dependencies hold:{A  B, BC  D, E  C, D 
A}. What are the candidate keys of R? [GATE 2005]
(A) AE, BE (B) AE, BE, DE
(C) AEH, BEH, BCH (D) AEH, BEH, DEH
Q.26 Which one of the following statements about normal forms is FALSE? [GATE 2005]
(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.
Q.27 A table has fields, F1, F2, F3, F4, F5, with the following functional dependencies:
F1  F3, F2  F4, (F1.F2)  F5 in terms of Normalization, this table is in [GATE 2005]
(A) 1 NF (B) 2 NF (C) 3 NF (D) None of these
Q.28 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 [GATE 2005]
Which of the following functional dependencies is NOT implies by the above set?
(A) CD  AC (B) BD  CD (C) BC  CD (D) AC  BC
Q.29 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 [GATE 2004]
(A) 2 NF (B) 3 NF (C) BCNF (D) 4 NF
Q.30 A relation Empdt1 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 the state, there is just
one pincode. In normalization trems, Empdt1 is a relation in [GATE 2004]
(A) 1 NF only (B) 2 NF and hence also in 1 NF
(C) 3 NF and hence also in 2 NF and 1 NF (D) BCNF and hence also in3 NF, 2 NF and 1 NF
Q.31 Consider the following functional dependencies in a database. [GATE 2003]
Date_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 2nd NF but not in 3rd NF (B) In 3rd NF but not in BCNF
(C) In BCNF (D) None of the above
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 7 Normalization & Functional Dependency
Q.32 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 relation is [GATE 2002]
(A) Zero
(B) More than zero but less than that of equivalent 3NF decomposition
+
(C) Proportional to the size of F
(D) Indeterminate
Q.33 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 guaranteed identification, which one of the
following tests should be used on the decompositions? (Assume that the closures of F and G are
available)
[GATE 2002]
(A) Dependency-preservation (B) Lossless-join
(C) BCNF (D) 3NF
Q.34 From the following instance of a relation schema R (A, B, C) we can conclude that : [GATE 2002]
A B C
1 1 1
1 1 0
2 3 2
2 3 2
(A) ‘A’ functionally determines ‘B’ and ‘B’ functionally determines ‘C’.
(B) ‘A’ functionally determines ‘B’ and ‘B’ does not functionally determine ‘C’.
(C) ‘B’ does not functionally determine ‘C’.
(D) ‘A’ does not functionally determine ‘B’ and ‘B’ does not functionally determine ‘C’.
Q.35 For relation R  ( L, M , N , O, P) the following dependencies hold: [GATE 2002]
M  O, NO  P, P  L and L  MN
R is decomposed R1 ( LMNP ) and R2 ( MO)
(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?
Q.36 Given the following relation instance [GATE 2002]
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

Q.40 Let R(ABCDEF) be a relation scheme with the following dependencies:

C  F , E  A, EC  D, A  B .Which of the following is a key for R? [GATE 1999]
(A) CD (B) EC (C) AE (D) AC
Q.41 Consider the schema R = (STUV) 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: [GATE 1999]
(A) Not in 2NF (B) In 2NF but not in 3NF
(C) In 3NF but not in 2NF (D) In both 2NF and 3NF
Q.42 Which normal form is considered adequate for normal relational database design? [GATE 1998]
(A) 2 NF (B) 5 NF (C) 4 NF (D) 3 NF
Q.43 For a database relation R(a, b, c, d),where the domains of a, b, c, d include only atomic values, only the
following functional dependencies and those that can be inferred from them hold: [GATE1997]
ac
bd
The relation is
(A) In 1st NF but not in 2nd NF (B) In 2nd NF but not in 3rd NF
(C) In 3rd NF (D) None of the above
Q.44 State True or False with reason. There is always a decomposition into Boyce-codd normal form (BCNF)
that is lossless and dependency preserving. [GATE 1993]
(A) True (B) False
Q.45 An instance of a relational schema R (A, B, C) has distinct values for attribute A. Can you conclude that
A is a candidate key for R? [GATE 1994]
(A) Yes (B) No
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 9 Normalization & Functional Dependency
Q.46 Consider the following table in a relational database
Last Name Rank Room Shift
Smith Manager 234 Morning
Jones Custodian 33 Afternoon
Smith Custodian 33 Evening
Doe Clerical 222 Morning
According to the data shown in the table, which of the following could be a candidate key of the table
(A) {Last Name} (B) {Room} (C) {Shift} (D) {Room, Shift}
[ISRO-2018]
Q.47 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
[ISRO-2007]
Q.48 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 [ISRO-2018]
Q.49 Consider the following table: Faculty(facName, dept, office, rank, dateHired)
facName dept office rank dateHired
Ravi Art A101 Professor 1975
Murali Math M201 Assistant 2000
Narayanan Art A101 Associate 1992
Lakshmi Math M201 Professor 1982
Mohan CSC C101 Professor 1980
Sreeni Math M203 Associate 1990
Tanuja CSC C101 Instructor 2001
Ganesh CSC C105 Associate 1995

(Assume that no faculty member within a single department has same name. Each faculty member has
only one office identified in office). 3NF refers to third normal form and BCNF refers to Boyee-Codd
Normal Form
Then Faculty is
(A) Not in 3NF,in BCNF (B) In 3NF,not in BCNF
(C) In 3NF, in BCNF (D) Not in 3NF, not in BCNF [ISRO-2017]
Q.50 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
[ISRO-2015]
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 10 GATE ACADEMY®

Q.51 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 [ISRO-2014]
Q.52 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 [ISRO-2014]
Q.53 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} [ISRO-2014]
Q.54 Consider the following table

A B C D E

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 and BCNF
[ISRO-2014]
Q.55 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
[ISRO-2013]
Q.56 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) Fourth Normal For
[ISRO-2011]
Q.57 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 [ISRO-2011]
Q.58 Armstrong's inference rule does not determine
(A) Reflexivity (B) Augmentation
(C) Transitivity (D) Mutual dependency [ISRO-2007]
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 11 Normalization & Functional Dependency
Q.59 State whether the following statements are TRUE or FALSE: A relation R with schema (X, Y) satisfies
the function dependency X  Y, The tuples (1, 2) and (2, 2) can both be in R simultaneously.
A. True
B. False.
[GATE 1987 : IIT Bombay]
Q.60 The following relations are used to store data about students, courses, enrollment of students in courses
and teachers of courses. Attributes for primary key in each relation are marked by ‘*’.
Students (rollno*, sname, saddr)
Courses (cno*, cname)
Enroll (rollno*, cno*, grade)
Teach (tno*, tname, cno*,)
(cno is course number cname is course name, tno is teacher number, tname is teacher name, sname is
student name, etc.)
For the relational database given above, the following functional dependencies hold:
Rollno  sname, saddr cno  cname, tno  tname, rollno cno  grade.
Is the database in 3NF?
(A) Yes
(B) No.
[GATE 1993 : IIT Bombay]
Q.61 Consider the relation scheme R(A,B,C) with the following functional dependencies:
AB  C, C  A
Find the minimal keys of the relation R.
[GATE 1995 : IIT Kanpur]
Q.62 Consider the following database relations containing the attributes
Book-id
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 from satisfied by the
relation?
[GATE 1998 : IIT Delhi]
Q.63 Suppose the following functional dependencies hold on a relation U with attributes P, Q, R, S, and T
P  QR
RS  T
Which of the following functional dependencies can be inferred from the above functional
dependencies?
(A) PS  T (B) R  T (C) P  R (D) PS  Q
[GATE 2021 : IIT Bombay]

Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 12 GATE ACADEMY®
Q.64 Consider the relation R( P, Q, S , T , X , Y , Z ,W ) with the following functional dependencies.
PQ  X ; P  YX ; Q  Y ; Y  ZW
Consider the decomposition of the relation R into the constituent relations according to the following
two decomposition schemes
 P , Q, S , T  ;  P , T , X    P, Q, S  ;  T , X  
D1 : R  D2 : R 
  Q, Y  ; Y , Z , W    Q, Y  ; Y , Z , W  
Which one of the following opt ions is correct?
(A) D1 is a lossless decomposition, but D2 is a lossy decomposition.
(B) Both D1 and D2 are lossy decompositions.
(C) Both D1 and D2 are lossless decompositions.
(D) D1 is a lossy decomposition, but D2 is a lossless decomposition.
[GATE 2021 : IIT Bombay]
Self-Practice Questions :
Q.1 Match the following :
1. Trivial functional dependency a. AB  CD
2. Non trivial functional dependency b. AB  BC
3. Semi non-trivial functional dependency c. AB  A
(A) 1-c, 2-a, 3-b (B) 1-a, 2-b, 3-c (C) 1-b, 2-a, 3-c (D) 1-c, 2-b, 3-a
Q.2 Identify the Correct statements about the normalization process.
I. Reduces number of tables
II. Reduces data base size
III. Reduces data constraints
IV. Reduces chances of data anomalies
(A) Only II (B) I, II and IV (C) II and III (D) II and IV
Q.3 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 not a key for R?
(A) CD (B) CE (C) BCE (D) CEF
Q.4 Consider the relation R (A, B, C, D, E, F, G, H, I, J) and the set of functional dependencies
F AB  C,A  D, E,B  F,F  G, H ,D  I , J 
What is the key for R?
(A) AB (B) BC (C) CD (D) EF
Q.5 Consider the relation R (ABCD) with its candidate key AB. The maximum number of super keys
possible is ______
Q.6 Consider a table R (ABCDE) with the following functional dependencies : A  C, B  D, AB  E. In
terms of normalization, this table is in.
(A) 1 NF (B) 2 NF (C) 3 NF (D) None
Q.7 Consider the relation R (ABCDE) and functional dependencies set  A  B, B  C, C  D, D  E.
What is the highest normal form of R?
(A) 1 NF (B) 2 NF (C) 3 NF (D) BCNF
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 13 Normalization & Functional Dependency
Q.8 Consider the relation scheme R (A, B, C, D) along with the set of functional dependencies
F   AB  C , AB  D, C  A, D  B. Which of the following is not a candidate key of R?
(A) AB (B) AC (C) AD (D) CD
Q.9 Identify the minimal key for relational scheme R (A, B, C, D, E) with functional dependencies
F   A  B, B  C , AC  D
(A) AB (B) AE (C) BDE (D) ACE
Q.10 Consider a schema R (W, X, Y, Z) and functional dependencies W  X and Y  Z . Then the
decomposition of R into R1(WX ) and R2(YZ ) 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
Q.11 Relation R (ABCDEFGH), F  CH  G, A  BC , B  CFH , E  A, F  EG is a set of functional
dependencies so that F  is exactly the set of FD’s that hold for R. Which of the following is not a
candidate of R?
(A) AD (B) BD (C) CD (D) ED
Q.12 Relation Took (SID, name, CourseNum, Title). Students have a unique student ID and (Possibly not
unique) name; courses have a unique course number and (possibly not unique) title. Each tuple in the
relation encodes the fact that the given student Took the given course. What are all the functional
dependencies for relation Took?
(A) SID  CourseNum (B) SID  name, CourseNum  Title
(C) name  SID, Title  CourseNum (D) CourseNum  SID
Q.13 Consider a relation R (ABCD) with functional dependencies AB  C and CD  E, Suppose there are
almost 3 different values for each of A, B and D. What’s the maximum number of different values for E?
(A) 27 (B) 9 (C) 3 (D) 81
Q.14 Consider relation R (ABCD) with functional dependencies : A  B, C  D, AD  C, BC  A
Suppose we decompose R into Boyce-codd normal form. Which of the following schemas could not be
in the result of the decomposition?
(A) AB (B) ACD (C) CD (D) AC
Q.15 Consider relation R with attribute X, Y, Z and set of FD’s are XY  Z , Z  X , The highest normal form
of R is
(A) 1 NF (B) 2 NF (C) 3 NF (D) BCNF
Q.16 Let F be a set of functional dependencies given as : F   A  BC , B  C , A  B, AB  C
Find minimal cover of F
(A)  A  C, B  C (B) AB  B, B  C (C) A  B, B  C (D) A  BC, AB  C
Q.17 Which one of the following is not correct?
(A) Relation is in 1 NF but not in 2 NF (B) Relation is in 3 NF but not in BCNF
(C) Relation is in 2 NF but not in 3 NF (D) Relation is in 3 NF but not in 2 NF
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 14 GATE ACADEMY®
Q.18 Consider a relation R (ABCDE) with the following dependencies
AB
CD
AC  E
D C
What is the highest normal form that R satisfies?
(A) 1 NF (B) 2 NF (C) 3 NF (D) BCNF
Q.19 Consider the given functional dependencies :
A  B, BC  DE, AEF  G
Which of the following is TRUE?
(A) AF  DG implied by the set (B) ACF  DG cannot be implied by the set
(C) AB  G implied by the set (D) None of the options are correct
Q.20 Consider the relation R (ABCDE) and its functional dependencies :
AB  C, C  E, B  D, E  A
The relation R is decomposed into R1 ( BCD ) and R2 ( ACE ). Which of the following option is correct
about the decomposition?
(A) Lossy and dependency preserving. (B) Lossless and dependency preserving.
(C) Lossy and not dependency preserving. (D) Lossless and not dependency preserving.
Q.21 Consider the following relational instance :
A B C
1 4 5
1 5 3
1 6 3
3 2 2
Which of the following functional dependencies are satisfied by the instance?
(A) AB  C and C  B (B) BC  A and B  C
(C) BC  A and A  C (D) AC  B and B  A

Answers

Classroom Practice Questions

1 D 2 C 3 B 4 A 5 B
6 B 7 C 8 B 9 A 10 B
11 B 12 A 13 C 14 A 15 A
16 A 17 C 18 A 19 D 20 D
21 C 22 C 23 A 24 B 25 D
26 C 27 A 28 B 29 B 30 B
31 D 32 B 33 C 34 C 35 *
36 B 37 C 38 C 39 B,D 40 B
41 D 42 D 43 A 44 B 45 B
46 D 47 B 48 C 49 B 50 B
51 A 52 B 53 B 54 C 55 B
56 B 57 B 58 D 59 True 60 10
(A) 2 NF,
61 AB, BC 62 63 A, C, D 64 A
(B) 1 NF
Self-Practice Questions
1 A 2 D 3 A 4 A 5 4
6 A 7 B 8 B 9 B 10 C
11 C 12 B 13 A 14 B 15 C
16 C 17 D 18 A 19 D 20 D
21 B



Classroom Practice Questions :

Q.1 Consider a schedule of transactions T1 and T2 : [GATE: 2020]
T1 RA RC WD WB Commit
T2 RB WB RD WC Commit

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)
T1 RA RC WD WB Commit
T2 RB WB RD WC Commit

(B)
T1 RA RC WD WB Commit
T2 RB WB RD WC Commit

(D)
T1 RA RC WD WB Commit
T2 RB WB RD WC Commit

Q.2 Consider the following two statements about database transaction schedules: [GATE : 2019]
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) I only (B) II only (C) Both I and II (D) Neither I or II
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 17 Transaction and Serializability
Q.3 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. [GATE : 2018]
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
Q.4 Two transactions T1 and T2 are given as [GATE : 2017]
T1 : r1 (X)w1 (X)r1 (Y)w1 (Y)
T2 : r2 (Y)w 2 (Y)r2 (Z)w 2 (Z)
Where ri (V) denotes a read operation by transaction Ti on a variable V and wi (V) denotes a write
operation by transaction T1 on a variable V. The total number of conflict serializable schedules that can
be formed by T1 and T2 is __________.
Q.5 Which one of the following is NOT a part of the ACID properties of database transactions?
[GATE : 2016]
(A) Atomicity (B) Consistency
(C) Isolation (D) Deadlock-freedom
Q.6 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.
Step 1: T acquires exclusive locks to {O1, ……….Ok} in increasing order of their addresses.
Step 2: The required operations are performed
Step 3: All locks are released.
This protocol will [GATE : 2016]
(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
Q.7 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? [GATE : 2016]
(A) Topological order (B) Depth-first order
(C) Breadth-first order (D) Ascending order of transaction indices
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 18 GATE ACADEMY®
Q.8 Consider the following database schedule with two transactions. T1 and T2. [GATE : 2016]
S = r2 (X);r1 (X);r2 (Y);w1 (X)
r1 (Y);w 2 (X);a1 ;a 2
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
Q.9 Consider the following transaction involving two bank accounts x and y. [GATE : 2015]
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
Q.10 Consider a simple checkpointing protocol and the following set of operations in the log.
[GATE : 2015]
(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
Q.11 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? [GATE : 2014]
(A) r1 (x);r2 (x);w1 (x);r3 (x);w 2 (x) (B) r2 (x);r1 (x);w 2 (x);r3 (x);w1 (x)
(C) r3 (x);r2 (x);r1 (x);w 2 (x);w1 (x) (D) r2 (x);w 2 (x);r3 (x);r1 (x);w1 (x)

Q.12 Consider the following schedule S of transactions T1 ,T2 ,T3 ,T4 ; [GATE : 2014]
T1 T2 T3 T4
Reads(X)
Writes(X)
commit
Writes(X)
commit
Writes(Y)
Reads(Z)
commit
Reads(X)
Reads(Y)
commit

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
Q.13 Consider the following transactions with data items P and Q initialized to zero [GATE : 2012]
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
Q.14 Which of the following concurrency control protocols ensure both conflict serializability and freedom
from deadlock? [GATE : 2010]
I. 2 – phase locking II. Time – Stamp ordering
(A) I only (B) II only (C) Both I and II (D) Neither I nor II
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 20 GATE ACADEMY®
Q.15 Consider the following schedule for transactions T1, T2 and T3: [GATE : 2010]
T1 T2 T3
Read (X)
Read (Y)
Read (Y)
Write (Y)
Write (X)
Write (X)
Read(X)
Write(X)
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
Q.16 Consider two transactions T1 and T2 and four schedules S1 ,S2 ,S3 ,S4 , of T1 and T2 as given below:
[GATE : 2009]
T1 : R1[x]W1[x]W1[Y]
T2 : R 2 [x]R 2 [y]W2 [y]
S1 : R 1[x]R 2 [x]R 2 [y]
W1[x]W1[y]W2 [y]
S2 : R 1[x]R 2 [x]R 2[y]
W1[x]W2 [y]W1[y]
S3 : R 1[x]W1[x]R 2 [x]
W1[y]R 2 [y]W2 [y]
S4 : R 2 [x]R 2 [y]R1[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
Q.17 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 transaction N on object O.)
[GATE : 2008]
(S1) (S2) (S3)
2RA 3RC 2RA
2WA 2RA 3RC
3RC 2WA 3WA
2WB 2WB 2WA
3WA 3WA 2WB
3WC 1RA 3WC
1RA 1RB 1RA
1RB 1WA 1RB
1WA 1WB 1WA
1WB 3WC 1WB
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 21 Transaction and Serializability

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
Q.18 Consider the following schedules involving two transactions. Which one of the following statements is
TRUE? [GATE : 2007]
S1:r1 (X);r1 (Y);r2 (X);r2 (Y);w 2 (Y);w1 (X)
S2 :r1 (X);r2 (X);r2 (Y);w 2 (Y);r1 (Y);w 1 (X)
(A) Both S1 and S2 are conflict serializable.
(B) S1 is conflict serializable and S2 is not conflict serializable.
(C) S1 is no conflict serializable and S2 is conflict serializable.
(D) Both S1 and S2 are not conflict serializable.
Q.19 Consider the following two transactions: T1 and T2 [GATE : 2007]
T1: read (A); T2: read (B)
Read (B); read (A);
If A = 0 then If B  0 then
B  B  1; A  A 1;
Write (B); 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)
S1: lock S (A); S2: lock S (B)
read (A); read (B);
locks S(B); locks S(A);
read (B); read (A);
If A = 0 If B  0
then Then
B  B  1; A  A-A1;
write (B); write (A);
commit; commit;
unlock (A); unlock (B);
unlock (B); unlock (A);
(B)
S1: lock X (a); S2: lock X (b)
read (A); read (B);
lock X (B); lock X (A);
read (B); read (A);
If A = 0 If B  0
then Then
B  B  1; A  A – 1;
write (B); write (A);
unlock (A); unlock (A);
commit; commit;
unlock (B); unlock (A);

R(D3);
R(D2);
W(D2); R(D2);
R(D3);
Time
R(D1);
↓ W(D1); W(D2);
W(D3);

R(D1);
R(D2);
W(D2);
W(D1);

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
Q.24 For the schedule given below, which of the following is correct? [GATE : 1999]
T1 T2
Read(A)
Read(B)
Write(A)
Read(A)
Write(A)
Write(B)
Read(B)
Write(B)

Write (A)
Read(B)
B = B + 10
Write(B)
B = B + temp
Write(B)
Which of the following is TRUE about the schedule S?
(A) S is serializable only as T1, T2.
(B) S is serializable only as T2, T1.
(C) S is serializable both as T1, T2 and T2, T1.
(D) S is not serializable either as T1, T2 or as T2, T1.
[GATE 2004 : IIT Delhi]

Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 26 GATE ACADEMY®
Q.36 Amongst the ACID Properties of a transaction, the ‘Durability’ property requires that the changes made
to the database by a successful transaction persist
(A) Except in case of an operating system crash
(B) Except in case of a disk crash
(C) Except in case of a power failure
(D) Always, even if there is a failure of any kind
[GATE 2005 : IIT Bombay]
Q.37 Consider the transactions T1, T2 and T3 are the schedules S1 and S2 given below.
T1: rl (x); rl (z); wl (x); wl (z)
T2: r2 (y); r2 (z); w2 (z)
T3: r3 (y); r3 (x); w3 (y)
S1: rl (x); r3 (y); r3 (x); r2 (y); r2 (z); w3(y); w2 (z); rl (z); wl (x); wl (z)
S2: rl (x); r3 (y); r2 (y); r3 (x); rl (z); r2 (z); w3 (y); wl (x); w2 (z); wl (z)
Which one of the following statements about the schedule is TRUE?
(A) Only S1 is conflict serializable (B) Only S2 is conflict serializable
(C) Both S1 and S2 are conflict serializable (D) Neither S1 nor S2 is conflict serializable.
[GATE 2014 : IIT Kharagpur]
Q.38 Consider the following partial Schedule S involving two transactions T1 and T2. Only the read and the
write operations have been shown. The read operation on data item P is denoted by read (P) and the
write operation on data item P is denoted by write (P).
Time Transaction – id
instance T1 T2
1 Read(A)
2 Write(A)
3 Read(C)
4 Write(C)
5 Read(B)
6 Write(B)
7 Read(A)
8 commit
9 Read(B)
Schedule S
Suppose that the transaction T1 fails immediately after time instance 9. Which one of the following
statements is correct?
(A) T2 must be aborted and then both T1 and T2 must be re-started to ensure transaction atomicity.
(B) Schedule S is non-recoverable and cannot ensure transaction atomicity.
(C) Only T2 must be aborted and then re-started to ensure transaction atomicity.
(D) Schedule S is recoverable and can ensure atomicity and nothing else needs to be done.
[GATE 2015 : IIT Kanpur]
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 27 Transaction & Concurrency
Q.39 Suppose a database system crashes again while recovering from a previous crash.
Assume check-pointing is not done by the database either during the transactions or during recovery.
Which of the following statements is/are correct?
(A) The same undo and redo list will be used while recovering again.
(B) The system cannot recover any further.
(C) The database will become inconsistent.
(D) All the transactions that are already undone and redone will not be recovered again.
[GATE 2021 : IIT Bombay]
Q.40 Let ri ( z ) and wi ( z ) denote read and write operations respectively on a data item Z by a transaction Ti .
Consider the following two schedules.
S1 : r1 ( x); r1 ( y ); r2 ( x); r2 ( y ); w2 ( y ); w1 ( x)
S2 : r1 ( x); r2 ( x); r2 ( y ); w2 ( y ); r1 ( y ); w1 ( x)
(A) S1 is conflict serializable, and S 2 is not conflict serializable.
(B) S1 is not conflict serializable and S 2 is conflict serializable.
(C) Both S1 and S 2 are conflict serializable.
(D) Neither S1 nor S 2 is conflict serializable.
[GATE 2021 : IIT Bombay]
Q.41 Let S be the following schedule of operation of three transactions T1, T2 and T3 in a relational database
system :
R2(Y), R1(X), R3(Z), R1(Y), W1 (X), R2(Z), W2(Y), R3(X), W3(Z)
P : S is conflict serializable.
Q : If T3 commits before T1 finishes, then S is recoverable.
Which of the following choices is correct?
(A) Both P and Q are true (B) P is true and Q is false
(C) Both P and Q are false (D) P is false and Q is true
[GATE 2021 : IIT Bombay]
Self-Practice Questions :
Q.1 Dead locks situation occurs only when one of the transactions wants to obtain a(n) ______ lock on a
data item.
(A) Binary (B) Exclusive (C) Shared (D) Compatible
Q.2 Consider the given schedule and choose the suitable option.
S  R1 ( X ), R1 (Y ),W1 ( X ), R2 (Y ),W3 (Y ),W1 ( X ), R2 (Y )
(A) Schedule is view serializable
(B) Schedule is conflict serializable but not view serializable
(C) Schedule is view serializable but not conflict serializable
(D) Neither view serializable nor conflict serializable
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 28 GATE ACADEMY®
Q.3 Which of the following is/are the problem (s) with concurrent execution?
I. Lost update problem
II. Dirty read problem
III. Unrepeatable read problem
IV. Phantom read
V. Incorrect summary problem
(A) I, II and V (B) II, III and IV (C) II, III, IV and V (D) All
Q.4 For the given schedule, Which of the following statement (s) is /are true?
S  r1 ( X ),W1 ( X ), r2 ( X ), r1 (Y ),W1 (Y ),W2 ( X ), r2 (Y ), C1; C2 ;
I : S is recoverable
II : If the order of W1 ( X ) and W2 ( X ) are interchanged, then S will be non-recoverable schedule.
(A) Only I (B) Only II (C) Both I and II (D) None of the above
Q.5 Which of the following statement is false?
(A) Blind writes appear in every view-serializable schedule that is not conflict serializable.
(B) In serializable graph, and edge between two nodes exists if and only if the pair of transactions
corresponding to the nodes have conflicting operations.
(C) Every strict schedule is recoverable
(D) Every view serializable schedule is recoverable
Q.6 Consider the following schedule :
S : r1 (Y ), r2 ( X ), r2 (Y ), r3 (Y ),W2 ( X ),W1 (Y );W3 ( X ); r1 ( X )
Which of the following statements is true about the schedule S?
(A) S is conflict serializable but not view serializable
(B) S in neither conflict serializable nor view serializable
(C) S is both conflict serializable as well as view serializable
(D) S is view serializable but not conflict serializable
Q.7 Consider the following schedules for transactions T1 , T2 and T3
T1 T2 T3
R( X )
R(Y )
R(Y )
W (Y )
W (X )
W (X )
R( X )

W (X )

Which one of the schedules below is the correct serializable of the above?
(A) T2  T1  T3 (B) T1  T3  T2 (C) T2  T3  T1 (D) T3  T1  T2
Q.8 Which of the following is true about 2-phase locking protocol?
S1 : Lock upgradation and degradation are allowed only in the shrinking phase.
S 2 : 2-phase locking allows lock degradation in shrinking phase.
(A) Only S1 (B) Only S 2 (C) Both S1 and S 2 (D) Neither S1 nor S 2
Q.9 Find the number of non-serial schedules possible over 2 transaction with 4 and 3 operations.
Q.10 Blind write always appears in
(A) Conflict serializable schedule (B) View serializable schedule
(C) Both of the above (D) None of the above
Q.11 The problem that occurs when one transaction updates a database item and then the transaction fails for
some reason is ______
(A) Temporary select problem (B) Temporary modify problem
(C) Dirty read problem (D) None
Q.12 How can be avoid cascading rollbacks?
I. Using strict two-phase locking protocol
II. Using rigorous two-phase protocol
III. Using basic two-phase locking protocol
(A) I only (B) I and II only (C) II and III only (D) I, II and III
Q.13 Let transactions T1 , T2 and T3 be defined to perform the following operations :
T1 : Add 5 to A
T2 : Double A
T3 : Display A on the screen and then set it to 1. i.e., A  1
(Where A is same item in the database)
Suppose transaction T1 , T2 and T3 are allowed to execute concurrently. If A has initial value zero, report
the possible values of final value of A and the displayed value of A.
I – 1, 10
II – 2, 5
III – 7, 0
IV – 12, 0
(A) I and III only (B) I, II and III only (C) II and IV only (D) I, II, III and IV
Q.14 Consider the following schedules.
S1 : R1 ( B), R1 ( A), R1 ( B),W1 ( B),W1 ( A), R1 ( A),W1 ( B)
S2 : R1 ( B), R1 ( A),W1 ( A), R2 ( B),W2 ( B), R1 ( B),W1 ( B)

S3 : R1 ( B), R1 ( A),W1 ( A), R2 ( B),W2 ( B),W1 ( B)

Which of the above schedules results in both unrepeatable reads and dirty read?
(A) S1 and S 2 (B) Only S 2 (C) Only S 3 (D) S 2 and S 3
Q.15 Consider a schedule with 3 transactions T1 , T2 , T3 executed concurrently. The schedule is given below.
Find out the correct options of the schedule
S : R2 ( X ), R1 ( Z ), R2 ( Z ), R3 ( X ), R3 (Y ),W2 ( X ),W3 (Y ), R1(Y ),W1( Z ),W1(Y ); C2 , C1, C3
(A) Cascadeless schedule (B) Strict schedule
(C) Recoverable schedule (D) Non-recoverable schedule
Q.16 Consider the following schedules where S1 and S 2
Consists of transaction T1 , T2 and T3
S1: W1 ( X ),W2 ( X ), R3 ( X )
S 2 : W1 ( X ), R3 ( X ),W2 ( X )
Identify the correct statements :
(A) Both S1 and S 2 are not conflict serializable
(B) S1 is view serializable but S 2 is not conflict serializable
(C) S1 is not view serializable but S 2 is conflict serializable
(D) Both S1 and S 2 are conflict serializable
Q.17 Consider the following order S of Transactions :
T2 : R( X ); T3 : W ( X ); T3 : Commit; T1 : W ( X ); T1 : Commit, T2 : R(Y );
T2 : W ( Z ); T2 : Commit; T4 : R( X ); T4 : R( X ); T4 : R(Y );T4 : Commit
If six serializable, then how many conflict serial ordering of S is possible?
(A) 10 (B) 11 (C) 8 (D) 13
Q.18 Consider the following statements based on the wait-die scheme for deadlock pretention.
S1 : Older transaction may wait for younger one to release data item (older means smaller time stemp).
S2 : Older transaction may force rollback of younger transaction may wait for older ones.
Identify which of the above statement (s) is/are correct?
(A) Only S1 is True (B) Only S 2 true
(C) Both S1 and S 2 are true (D) Both S1 and S 2 are false
Q.19 Which of the following occurs when a transaction reads data item it has previously read and finds
modification or deletions caused by a committed transaction?
(A) Non repeatable read (B) Phantom read (C) Dirty read (D) Consistent read
Q.20 Identify the correct statement based on the schedule below :
T1 T2
r( X )
r( X )

W (X )
W (X )

(A) Schedule shows lost update Anomaly

(B) Schedule shows lost delete Anomaly.
(C) Schedule does not show lost update Anomaly.
(D) Schedule shows lost delete Anomaly along with lost update Anomaly.
Q.21 ______, ______, and ______ are the SQL statements used for transaction control.
(A) INSERT, UPDATE, DELETE (B) COMMIT, ROLLBACK, SAVEPOINT
(C) INSERT, ALTER, DROP (D) None of the above
Q.22 S : W2 ( A), r1 ( B), r2 ( A),W2 ( B), r3 ( A),W1 ( B),W3 ( A), r2 ( B)
How many minimum number of moves (where a more consists of changing the position of one of the
operations) are required to convert S into a conflict serializable schedule?
(A) 1 (B) 2 (C) 3 (D) 4
Answers

Classroom Practice Questions

1 A 2 C 3 A 4 54 5 D
6 A 7 A 8 C 9 B 10 A
11 D 12 C 13 B 14 B 15 A
16 B 17 D 18 C 19 C 20 B
21 C 22 D 23 D 24 D 25 12
26 B 27 A 28 B 29 D 30 A
31 B 32 A 33 A 34 D 35 D
36 D 37 A 38 B 39 A 40 B
41 B
Self-Practice Questions
1 B 2 D 3 D 4 A 5 D
6 C 7 B 8 B 9 33 10 D
11 C 12 B 13 D 14 B 15 D
16 D 17 C 18 A 19 A 20 A
21 B 22 A



Classroom Practice Questions :

Q.1 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 _______.
[GATE : 2020]
Q.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) B+ Tree is a height – balanced tree
(B) Non-leaf nodes have pointers to data records
(C) Key values in each node are kept in sorted order
(D) Each leaf node has a pointer to the next leaf node
[GATE : 2019]
Q.3 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 _______.
[GATE : 2017]
Q.4 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
[GATE : 2016]
Q.5 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 greater than or equal to 7 and less than 15” is ________.

5 13 17

1 3 5 7 9 11 13 15 17

[GATE : 2015]
Q.6 An index is clustered, if
(A) It is on 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.
[GATE : 2013]
+
Q.7 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
[GATE : 2010]
+
Q.8 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 empty. 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
[GATE : 2009]
Q.9 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.
[GATE : 2008]
Q.10 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
[GATE : 2008]
Q.11 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 byte. 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
[GATE : 2008]
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 34 GATE ACADEMY®
Q.12 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
[GATE : 2007]
+
Q.13 Consider the B -tree in adjoining figure, where each node has at most 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

[GATE : 2007]
+
Q.14 Now the key K50 is deleted from the B -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 K20 (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
[GATE : 2007]
Q.15 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 block pointer is 6 bytes. The largest possible order of a non-leaf node in a B+tree
implementing the file structure is
(A) 23 (B) 24 (C) 34 (D) 44
[GATE : 2006]
Q.16 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 form disks is in blocks [GATE : 2005]
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 35 File Organization & Indexing
Q.17 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
[GATE : 2005]
+
Q.18 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
[GATE : 2004]
Q.19 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
[GATE : 2004]
+
Q.20 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 [GATE : 2002]
+
Q.21 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) Disk are more reliable than memory.
[GATE : 2000]
Q.22 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.
[GATE : 1999]
Q.23 There are five records in a database, there is an index file associated with this and it contains the values
1, 3, 2, 5 and 4, which one of the fields is the index built form?
Name Age occupation Category
Rama 27 CON A
Abdul 22 ENG A
Jeniffer 28 DOC B
Maya 32 SER D
Dev 24 MUS C
(A) Age (B) Name (C) Occupation (D) Category
[GATE : 1998]

What is the result of inserting G in the above tree?

(A)

(B)

(C)
(D) None of the above
[GATE 2003 : IIT Madras]
Q.39 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.
i. Q1:𝜋𝐴1,…,(𝜎𝐴𝑝=𝑐(𝑟)) where c is a constant
ii. Q2: 𝜋𝐴1,…,(𝜎𝑐1≤𝐴𝑝≤𝑐2(𝑟)) where c1 and c2 are 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
[GATE 2011 : IIT Madras]
Q.40 Consider a B+ tree in which the search key is 12 byte long, block size is 1024 byte, recorder pointer is
10 byte long and the block pointer is 8 byte long. The maximum number of keys that can be
accommodated in each non-leaf node of the tree is ______.
[GATE 2015 : IIT Kanpur]
Q.41 A file is organized so that the ordering of the data records is the same as or close to the ordering of data
entries in some index. Than that index is called
(A) Dense (B) Sparse (C) Clustered (D) Unclustered
[GATE 2015 : IIT Kanpur]
Q.42 A data file consisting of 1,50,000 students records is stored on a hard disk with block size of 4096 bytes.
The data file is sorted on the primary key RollNo. The size of a record pointer for this disk is 7 bytes.
Each student-record has a candidate key attribute called ANum of size 12 bytes. Suppose an index file
with records consisting of two fields. ANum value and the record pointer to the corresponding student
record, is built and stored on the same disk. Assume that the records of data file and index file are not
split across disk blocks. The number of blocks in the index file is _______.
[GATE 2021 : IIT Bombay]
Q.43 Match the following :
(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
[GATE 1990 : IISc Bangalore]
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 39 Transaction & Concurrency

Self-Practice Questions :
Q.1 In the indexed scheme of blocks to a file, the maximum possible size of the file depends on :
(A) The number of blocks used for index and the size of index
(B) Size of blocks and size of address
(C) Size of index
(D) Size of block
Q.2 Which one is true about clustered index?
(A) Clustered index is not associated with table.
(B) Clustered index is built on physically ordered non-key columns
(C) Clustered index is not built on unique key columns
(D) None of the above
Q.3 The maximum number of keys in a B-trees of order 3 and of height 3 is ______
Q.4 Which of the following statement (s) about indexing is/are correct?
S1 : Indexing is used to optimize the performance of a database by minimizing the number of disk
accesses required when a query is processed.
S 2 : Indexing is used to locate and access the data in a database table quickly.
(A) Only S1 (B) Only S 2 (C) Both S1 and S 2 (D) Neither S1 nor S 2
Q.5 Calculate the order of leaf node 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) 51 (B) 47 (C) 46 (D) 52
Q.6 Which of the following statement (S) about B-pre/B+ pre is INCORRECT?
S1 : In B tree, keys and record pointers both can be stored in the internal as well as leaf nodes.
S 2 : In B+ tree, record pointers can only be stored on the leaf nodes while internal nodes can only
store the key values.
(A) Only S1 (B) Only S 2 (C) Both S1 and S 2 (D) Neither S1 nor S 2
Q.7 If a table has an index table like the following :
Roll No Subject

10 DBMS

10 OS

10 10 MATH

20 20 MATH

30 20 OS

40 30 DBMS

40 OS

40 DBMS

40 DLD

10 20 40 50 90
After deletion of 50, 40, 30 what is the resultant B-tree?
(A) (B)
60 20 60

10 20 90 10 30 90

60 50 90 20 10 90
Q.11 S1 : In the Index allocation scheme of blocks to a file, the maximum possible size of the file depends on
the size of the blocks, and the size of the address of the blocks.
S2 : In the Index allocation scheme of blocks to a file, the maximum possible size of the file depends on
the size of the blocks, the number of blocks used for the index, and the size of the address of the blocks.
Which of the following is/are correct?
(A) Both S1 and S 2 are true (B) Only S 2 is True
(C) Only S1 is True (D) Both S1 and S 2 are false
Q.12 A block can hold either 7 records or 13 (search key, pointer) pair. If data base contains 50 records, then
how many blocks are required to hold the data file and dense index?
(A) 10 (B) 9 (C) 12 (D) 14
Q.13 Consider the given B+ tree, insert ‘19’ into the tree, what would be the new element in level 2. (order 
3)
13 30

20
5 10 40 50

1 4 5 9 11 12 13 18 20 29
30 35 41 45 60 70
(A) 13 (B) 18 (C) 20 (D) 29
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 41 Transaction & Concurrency
Q.14 A B-tree of order m has maximum ______ number of children.
m
(A) m (B) m  1 (C) m  1 (D)
2
Q.15 In a B+Tree, if the search-key value is 14 bytes long, the block size is 1024 bytes and the block pointer is
8 bytes, then the maximum number of keys that can be accommodated in each non-leaf node of the tree
is ______

Answers

Classroom Practice Questions

1 4 2 B 3 A 4 A 5 5
6 C 7 B 8 C 9 A 10 C
11 c 12 A 13 A 14 A 15 C
16 D 17 A 18 C 19 C 20 C
21 B 22 B 23 C 24 B 25 C
26 A 27 D 28 A 29 B 30 A
33.033
31 C 32 A 33 34 3 35 O(h)
MB
(i) 13, (ii)
36 37 B 38 B 39 C 40 50
2(d+1)h-1
a-q, b-r,
41 C 42 698 43
c-p, d-s
Self-Practice Questions
1 A 2 B 3 80 4 C 5 C
6 D 7 C 8 65535 9 8 10 A
11 B 12 C 13 B 14 A 15 46



Classroom Practice Questions :

Q.1 A hash table with ten buckets with one slot per bucket is shown in the following figure. The symbols S1
to S7initially entered using a hashing function with linear probing. The maximum number of
comparisons needed in searching an item that is not present is
0 S7
1 S1
2
3 S4
4 S2
5
6 S5
7
8 S6
9 S3
(A) 4
(B) 5
(C) 6
(D) 3 [1989 : 2 Marks]
Q.2 An advantage of chained hash table (external hashing) over the open addressing scheme is
(A) Worst case complexity of search operations is less
(B) Space used is less
(C) Deletion is easier
(D) None of the above [1996 : 1 Mark]
Q.3 Given the following input (4322, 1334, 1471, 9679, 1989, 6171, 6173, 4199) and the hash function x
mod 10, which of the following statements are true?
i. 9679, 1989, 4199 hash to the same value
ii. 1471, 6171 hash to the same value
iii. All elements hash to the same value
iv. Each element hashes to a different value
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 43 Hashing
(A) i only
(B) ii only
(C) i and ii only
(D) iii or iv [2004 : 1 Mark]
Q.4 A hash table contains 10 buckets and uses linear probing to resolve collisions. The key values are
integers and the hash function used is key % 10. If the values 43, 165, 62, 123, 142 are inserted in the
table, in what location would the key value 142 be inserted?
(A) 2
(B) 3
(C) 4
(D) 6 [2005 : 1Mark]
Q.5 Which of the following statement(s) is TRUE?
I. A hash function takes a message of arbitrary length and generates a fixed length code.
II. A hash function takes a message of fixed length and generates a code of variable length.
III. A hash function may give the same hash value for distinct messages.
(A) I only
(B) II and III only
(C) I and III only
(D) II only [2006: 1Mark]
Q.6 Consider a hash table of size 11 that uses open addressing with linear probing. Let h(k)=k mod 11 be the
hash function used. A sequence of records with keys 43 36 92 87 11 4 71 13 14 is inserted into an
initially empty hash table, the bins of which are indexed from zero to ten. What is the index of the bin
into which the last record is inserted?
(A) 3
(B) 4
(C) 6
(D) 7 [2008: 2Marks]
Q.7 The keys 12, 18, 13, 2, 3, 23, 5 and 15 are inserted into an initially empty hash table of length 10 using
open addressing with hash function h(k) = k mod 10 and linear probing. What is the resultant hash table?
[2009 : 2 Marks]
(A) (B) (C) (D)
0 0 0 0
1 1 1 1
2 2 2 12 2 12 2 12, 2
3 23 3 13 3 13 3 13, 3, 23
4 4 4 2 4
5 15 5 5 5 3 5 5, 15
6 6 6 23 6
7 7 7 5 7
8 18 8 18 8 18 8 18
9 9 9 15 9
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 44 GATE ACADEMY®
Q.8 Consider a hash table with 9 slots. The hash function is ℎ(k) = k mod 9. The collisions are resolved by
chaining. The following 9 keys are inserted in the order: 5, 28, 19, 15, 20, 33, 12, 17, 10. The maximum,
minimum, and average chain lengths in the hash table, respectively, are
(A) 3, 0, and 1
(B) 3, 3, and 3
(C) 4, 0, and 1
(D) 3, 0, and 2 [2014 (Set-1): 2 Marks]
Q.9 Consider a hash table with 100 slots. Collisions are resolved using chaining. Assuming simple uniform
hashing, what is the probability that the first 3 slots are unfilled after the first 3 insertions?
(A) (97 × 97 × 97)/ 1003
(B) (99 × 98 × 97)/ 1003
(C) (97 × 96 × 95)/ 1003
(D) (97 × 96 × 95)/(3! × 1003 ) [2014 (Set-3): 2 Marks]
Q.10 Given a hash table T with 25 slots that stores 2000 elements, the load factor α for T is __________
[2015 (Set-3): 1 Mark]
Q.11 Consider a double hashing scheme in which the primary hash function is h1(k) = k mod 23, and the
secondary hash function is h2(k) = 1+(k mod 19). Assume that the table size is 23. Then the address
returned by probe 1 in the probe sequence (assume that the probe sequence begins at probe 0) for key
value k = 90 is ________ . [2020 : 1 Mark]
Self-Practice Questions :

Q.1 Consider a hash table of size seven, with starting index zero, and a hash function (3x + 4)mod7. Assuming
the hash table is initially empty, which of the following is the contents of the table when the sequence 1,
3, 8, 10 is inserted into the table using closed hashing? Note that ‗_‘ denotes an empty location in the
table.
(A) 8, _, _, _, _, _, 10
(B) 1, 8, 10, _, _, _, 3
(C) 1, _, _, _, _, _,3
(D) 1, 10, 8, _, _, _, 3 [2007:2Marks]
Q.2 Consider a hash function that distributes keys uniformly. The hash table size is 20. After hashing of how
many keys will the probability that any new key hashed collides with an existing one exceed 0.5.
[2007 : 2 marks]
(A) 5
(B) 6
(C) 7
(D) 10
Linked question Answer for 3 and 4
A hash table of length 10 uses open addressing with hash function h(k)=k mod 10, and linear probing.
After inserting 6 values into an empty hash table, the table is as shown below.

0
1
2 42
3 23
4 34
5 52
6 46
7 33
8
9
Q.3 Which one of the following choices gives a possible order in which the key values could have been
inserted in the table?
1. 46, 42, 34, 52, 23, 33
2. 34, 42, 23, 52, 33, 46
3. 46, 34, 42, 23, 52, 33
4. 42, 46, 33, 23, 34, 52 [2010:2Marks]
Q.4 How many different insertion sequences of the key values using the same hash function and linear
probing will result in the hash table shown above? [2010:2Marks]
(A) 10
(B) 20
(C) 30
(D) 40
Q.5 Which one of the following hash functions on integers will distribute keys most uniformly over
10buckets numbered 0 to 9 for i ranging from 0 to 2020?
(A) h(i) =𝑖 2 mod 10
(B) h(i) =𝑖 3 mod 10
(C) h(i) = (11 ∗ 𝑖 2 ) mod 10
(D) h(i) = (12 ∗ i) mod 10 [2015 (Set-2): 2 Marks]
Q.6 Consider an open address hash table with a total of 10000 slots containing 9800 entries .What is the
expected number of probes in a successful search ?
(A) 3 (B) 4 (C) 5 (D) 6
Q.7 A hash function h defined h(key)=key mod 7, with linear probing, is used to insert the keys 44, 45, 79,
55, 91, 18, 63 into a table indexed from 0 to 6. What will be the location of key 18?
(A) 3 (B) 4 (C) 5 (D) 6
Q.8 Consider a hash table of size m = 10000, and the hash function h(K) = floor (m(KA mod 1)) for A = (
√(5) – 1)/2. The key 123456 is mapped to location ______.
(A) 46 (B) 41 (C) 43 (D) 48
Q.9 Consider a 13 element hash table for which f(key)=key mod 13 is used with integer keys. Assuming
linear probing is used for collision resolution, at which location would the key 103 be inserted, if the
keys 661, 182, 24 and 103 are inserted in that order?
(A) 0 (B) 1 (C) 11 (D) 12
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 46 GATE ACADEMY®
Q.10 Consider a hash table of size m = 100 and the hash function h(k) = floor(m(kA mod 1))
( 5  1)
for A   0.618033
2
Compute the location to which the key k = 123456 is placed in hash table.
(A) 77 (B) 82 (C) 88 (D) 89

Answers

Classroom Practice Questions

1 B 2 D 3 C 4 D 5 C
6 D 7 C 8 A 9 A 10 80
11 13
Self-Practice Questions
1 B 2 D 3 C 4 C 5 B
6 B 7 C 8 B 9 B 10 C



Classroom Practice Questions :

Q.1 A relational database contains two table Student and Performance as shown below :
Student
Roll_no. Student_name
1 Amit
2 Priya
3 Vinit
4 Rohan
5 Smita

Performance
Roll_no. Subject_code Marks
1 A 86
1 B 95
1 C 90
2 A 89
2 C 92
3 C 80

The primary key of the Student table is Roll_no. For the Performance table, the columns Roll_no. and
Subject_code together form 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 [GATE 2019]

Suppliers
sno sname location
S1 M/s Royal furniture Delhi
S2 M/s Balaji furniture Bangalore
S3 M/s Premium Chennai
furniture

Parts
pno pname Part_spec
P1 Table Wood
P2 Chair Wood
P3 Table Steel
P4 Almirah Steel
P5 Almirah Wood
The primary key of each table is indicated by underlining 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  'P 4 ' GROUP BY pno);
The number of rows returned by the above SQL query is
(A) 0 (B) 5 (C) 4 (D) 2 [GATE 2020]
Q.3 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;
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 49 Structured Query Language
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
[GATE 2018]
Q.4 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.
EMP
EmpID EmpName DeptName
1 XYA AA
2 XYB AA
3 XYC AA
4 XYD AA
5 XYE AB
6 XYF AB
7 XYG AB
8 XYH AC
9 XYI AC
10 XYJ AC
11 XYK AD
12 XYL AD
13 XYM AE
SELECT AVG (EC.Num) FROM EC WHERE (DeptName, Num) IN
(SELECT DeptName, COUNT (EmpId) AS EC (DeptName, Num)
FROM EMP GROUP BY DeptName
The output of executing the SQL query is ________.
(A) 2.6 (B) 2.7 (C) 2.8 (D) 2.9 [GATE 2017]
Q.5 Consider the following database table named top_scorer.
player country goals
Klose Germany 16
Ronaldo Brazil 15
G Muller Germany 14
Fontaine France 13
Pele Brazil 12
Klinsmann Germany 11
Kocsis Hungary 11
Batistuta Argentina 10
Cubillas Peru 10
Lato Poland 10
Lineker England 10
T Muller Germany 10
Rahn Germany 10

Performance
Roll_No Course Marks
1 Math 80
1 English 70
2 Math 75
3 English 80
2 Physics 65
3 Math 80

Q.14 Consider the following relational 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.rollno AND R.courseno=107 AND R.percent>90
II. πsname (σcourseno=107percent>90 (Registration Students))
III. {T |  S  Student,  R  Registration (S. rollno= R. rollno 
R. courseno =107  R. percent >90  T. sname=S. sname)}
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 53 Structured Query Language

IV. {< SN | SR R P ( SR ,SN  Students  SR ,107, R P  Registration  R P >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 [GATE 2013]
Q.15 Consider the following relations A, B and C:
A
ID Name Age
12 Arun 60
15 Shreya 24
99 Rohit 11

B
ID Name Age
15 Shreya 24
25 Hari 40
98 Rohit 20
99 Rohit 11

C
ID Phone Area
10 2200 02
99 2100 01

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
[GATE 2012]
Q.16 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
[GATE 2011]
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 54 GATE ACADEMY®
Q.17 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
[GATE 2010]
Q.18 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 non-blue part.
(D) Find the names of all suppliers who have not supplied only blue parts. [GATE 2009]
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 55 Structured Query Language
Q.19 Consider the following relational scheme:
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, Exam Result 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. [GATE 2008]
Q.20 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 broken but ranks are skipped: if exactly two customers have the largest
balance they each get rank 1 and rank 2 is not assigned.

Consider these statements about Query 1 and Query 2.

1. Query 1 will produce the same row set as Query 2 for some but not all databases
2. Both Query 1 and Query 2 are correct implementations of the specification
3. Query 1is a correct implementation of the specification but Query 2 is not
4. Neither Query 1 nor Query 2 is a correct implementation of the specification
5. Assigning rank with a pure relational query takes less time than scanning in decreasing balance
order and assigning rank using ODBC
Which two of the above statements are CORRECT?
(A) 2 & 5 (B) 1 & 3 (C) 1 & 4 (D) 3 & 5
[GATE 2006]
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 56 GATE ACADEMY®
Q.21 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 constrains. Given the following four queries:
Query 1:
SELECT student FROM enrolled WHERE student IN (SELECT student FROM paid)
Query 2:
SELECT student FROM paid WHERE student IN (SELECT student FROM enrolled)
Query 3:
SELECT E. student FROM enrolled E, paid P WHERE E. student = P. student
QUERY 4:
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) Query 2 and Query 4 return identical row sets for all databases but there exist databases for which
Query 1 and Query 2 return different row sets.
(C) There exist databases for which Query 3 returns strictly fewer rows than Query 2.
(D) There exist databases for which Query 4 will encounter an integrity violation at runtime.
[GATE 2006]

Common Data for

Questions Q.22 & Q.23

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 in the database.
D: Drivers relation
Did Dname rating Age
22 Karthikeyan 7 25
29 Salman 1 33
31 Boris 8 55
32 Amoldt 8 25
58 Schumacher 10 35
64 Sachin 7 35
71 Senna 10 16
74 Sachin 9 35
85 Rahul 3 25
95 Ralph 3 53

R: Reserves relation
Did cid Day
22 101 10/10/06
22 102 10/10/06
22 103 8/10/06

C: Cars relation
cid Cname Color
101 Renault Blue
102 Renault Red
103 Ferrari Green
104 Jaguar Red

Q.22 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.color= ‘red’
Intersect SELECT R.Did
FROM Cars C, Reserves R
WHERE R.cid=C.cid and C.color= ‘green’)
(A) Karthikeyan, Boris (B) Sachin, Salman
(C) Karthikeyan, Boris, Sachin (D) Schumacher, Senna
Q.23 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
[GATE 2006]
Q.24 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
[GATE 2005]
Q.25 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:
Sales info = (sales person id, total sales, commission)

Table Hobby
Roll Hobby Name
1798 Chess
1798 Music
2154 Music
2369 Swimming
2581 Cricket
2643 Chess
2643 Hockey

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
[GATE 2005]
Q.27 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, stock level)
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’);
(A) Do not supply any item (B) Supply exactly one item
(C) Supply one or more items (D) Supply two or more items [GATE 2005]
Q.28 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)

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 to 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_no = 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 [GATE 2004]

(B) a1 a 2 ....a n P (r1 r2 .... rm )

(D) a1 a 2 ....an P (r1  r2  ....  rm )

A disk seek takes 4ms, disk data transfer bandwidth is 300MB/s and checking a tuple to see if amount is
greater than x takes 10 μs . 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 1 executes slower than Plan 2 for all databases.
[GATE 2006 : IIT Kharagpur]
Q.42 Consider the table employee (empId, name, department, salary) and the two queries Q1 , Q 2 below.
Assuming that department 5 has more than one employee, and we want to find the employees who get
higher salary than anyone in the department 5, which one of the statement is TRUE for any arbitrary
employee table?
Q 1 : Select e.empId
From employee e
Where not exists
(Select * From employee s where s. department = ‘5’ and s. salary >= e. salary)

Q 2 : Select e.empId
From employee e
Where e. salary > Any
(Select distinct salary from employee s where s. department = ‘5’)
(A) Q1 is the correct query. (B) Q 2 is the correct query.
(C) Both Q1 and Q 2 produce the same answer. (D) Neither Q1 nor Q 2 is the correct query.
[GATE 2008 : IISc Bangalore]
Q.43 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
[GATE 2011 : IIT Madras]
Q.44 Which of the following statements are TRUE about 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 only 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
[GATE 2012 : IIT Delhi]
Q.45 The relation scheme given below is used to store information about the employees of a company, where
empId is the key and deptId indicates the department to which the employee is assigned. Each employee
is assigned to exactly one department.
emp (empId, name, gender, salary, deptId )
Consider the following SQL Query :
select deptId, count (*)
from emp
where gender = "female" and salary > (select avg (salary) from emp)
group by deptId;
The above query gives, for each department in the company, the number of female employees whose
salary is greater than the average salary of
(A) Employees in the department
(B) Female employees in the department
(C) Employees in the company
(D) Female employees in the company
[GATE 2021 : IIT Bombay]

Self-Practice Questions :
Q.1 What does the Alter TABLE Clause with MODIFY do?
(A) The SQL ALTER TABLE Clause with MODIFY, Changes the data type of a column and size in a
table.
(B) The SQL ALTER TABLE clause with MODIFY is used to insert data into data base table.
(C) The SQL ALTER TABLE clause With MODIFY delete data from database table.
(D) The SQL ALTER TABLE clause with MODIFY is used to delete a database table.
Q.2 How can you change ‘A’ into “G” in the “Last Name” column in the Student table?
(A) UPDATE Student SET LASTNAME = ‘A’ INTO LASTNAME = “G”;
(B) MODIFY Student SET LASTNAME = “G” WHERE LASTNAME = “A”
(C) MODIFY Student SET LASTNAME = “A” WHERE LASTNAME = “G”
(D) UPDATE Student SET LASTNAME = “G” WHERE LASTNAME= “A”
Q.3 S1: The UPDATE and SELECT clause are used in one SQL statement if you use nested SQL
statements.
S2: The SELECT clause list can have a computed value.
Which of the following option is CORRECT about S1 and S2?
(A) Only S1 is true (B) Only S2 is
(C) Both S1 and S2 are true (D) Both S1 and S2 are false.
Q.4 Which aggregate function is also work on non numeric column? (MSQ)
(A) COUNT () (B) Avg() (C) Max() (D) Min()
Q.5 Statement. “Salary of an Employee should be positive integer”.
Above statement corresponds to which of the following DBMS constraints?
(A) Entity Integrity constraint (B) Domain constraint
(C) Key constraint (D) Referential Integrity constraint
Q.6 Consider the following table [GATE 2022]
Test_ID Student_Name Gate_Score
1 Luv 100
2 Kush 300
3 Atharav 300
4 Kriyansh 200
5 Dhruvi 150
6 Raj 250
How many tuples result after executing the query.
SELECT Student Name, Sum (Gate Score) from Gate 2022 by Student Name Having sum (Gate Score)
< 500;
(A) 2 (B) 3 (C) 5 (D) 6
Q.7 Consider the following schema;
Students (Sid, Sname, age)
Courseinfo (Cid, Cname, InstructorSSN)
Enroll (Sid, Cid, grade)
Write an SQL query for “Find name of students who enrolled for all courses”
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 66 GATE ACADEMY®
(A) SELECT S.Sname FROM Student S
WHERE NOT EXISTS (Select C.Cid from courseinfo
WHERE NOT EXISTS (Select E.Cid FROM Enroll E WHERE
E.Cid = C.Cid And E.Sid = S.Sid))
(B) Select S.Sname FROM Student S WHERE EXISTS
(Select C.Cid from Courseinfo C WHERE EXISTS
(Select E.Cid from Enroll E WHERE
E.Cid = C.Cid And E.Sid = S.Sid))
(C) Select S.Sname FROM Student S
WHERE EXISTS (Select C.Cid from Courseinfo C
WHERE NOT EXISTS (Select E.Cid FROM Enroll E WHERE
E.Cid = C.Cid And E.Sid = S.Sid))
(D) None of the above
Q.8 Which of the following is work on only on numeric column?
(A) Max (B) Min (C) Sum (D) Count
Q.9 Referential Integrity constraint is related to which of the following:
(A) Primary key (B) NOT NULL (C) Foreign key (D) Unique.
Q.10 Consider the SQL statements (s) below:
S1: INSERT INTO employees (first_Name, last_Name, course)
Values (‘kush’, ‘Saklecha’,’PHD’);
S2: SELECT instructor, ID, department. dept_Name
FROM instructor, department
WHERE instructor, dept_Name = department, dept_name
AND department. Budget > 8500;
Indentify the correct statement related to S1 and S2
(A) Both S1 and S2 are DDL Queries (B) S1 is DCL and S2 is a DML Query
(C) Both S1 and S2 are DML queries (D) S1 is a DDL Query and S2 is a DCL
Q.11 Consider the following relation 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 cutomer. What dose the following query return?
SELECT empName
From employee E WHERE NOT EXISTS (SELECT Cost Id)
FROM Customer C WHERE C, salesRepId = E.empId
AND C.rating < > ‘GOOD’);
(A) Names of all employees with atleast one of their customers having a ‘GOOD’ rating
(B) Names of all employees with atmost one of their customers having a ‘GOOD’ rating
(C) Names of all employees with none of their customers having a ‘GOOD’ rating.
(D) Names of all the employees with all their customers having a ‘GOOD’ rating

Answers

Classroom Practice Questions

1 D 2 C 3 D 4 A 5 A
6 A 7 D 8 A 9 A 10 D
11 B 12 C 13 D 14 A 15 B
16 C 17 C 18 A 19 D 20 C
21 A 22 A 23 B 24 D 25 D
26 B 27 D 28 D 29 C 30 C
31 C 32 A 33 D 34 A 35 *
36 * 37 C 38 D 39 * 40 D
41 C 42 A 43 A 44 B 45 C
Self-Practice Questions
1 A 2 D 3 C 4 A,C,D 5 B
6 D 7 A 8 C 9 C 10 C
11 D 12 C 13 D 14 D 15 A
16 B 17 C 18 A
Classroom Practice Answer key

35 *
Select distinct frequents. student from
FREQUENTS, SERVES, LIKES
Where, FREQUENTS.parlor=SERVES.parlor
AND
SERVES.ice-cream=LIKES.ice-cream
AND
FREQUENTS.student=LIKES.student
36 *
(A) Select Employee-name
From EMP, DEPT
where, Salary>50000 and EMP. Dept-no=DEPT. Dept-no and Loaction=“Calcutta”
(B) Select Dept-no, count(*)
From EMP
where, salary>100000 group by Dept-no
39 *
(A)  exm1.name (  (exam1.regno  examinee.regno))^(emp1.name=emp2.name)) (  exam1
(examinee)  examinee)
(B) Select Distinct centr_code
From appears
where, regno IN (Select regno from examinee where score>80)



Classroom Practice Questions :

Q.1 Consider the following P( X , Y , Z ), Q( X , Y , T ) and R(Y , V ) .
P
X Y Z
X1 Y1 Z1
X1 Y1 Z2
X2 Y2 Z2
X2 Y4 Z4

Q
X Y T
X2 Y1 2
X1 Y2 5
X1 Y1 6
X3 Y3 1

R
Y V
Y1 V1
Y3 V2
Y2 V3
Y2 V2

How many tuples will be returned by the following relational algebra query?
  X ( PV  RY  RV V 2)  P  R     X  (QY  RY QT 2)  Q  R  
(A) 0 (B) 1 (C) 2 (D) 3
[GATE 2019]

(C)  B (r1 )   C (r2 ) (D)  B (r1 )   C (r2 )   [GATE 2012]

B
ID Name Age
15 Shreya 24
25 Hari 40
98 Rohit 20
99 Rohit 11

C
ID Phone Area
10 2200 02
99 2100 01
How many tuples does the result of the following relational algebra expression contain? Assume that the
schema of A  B is the same as that of A.
(A  B) A.Id 40 C.Id 15 C
(A) 7 (B) 4 (C) 5 (D) 9 [GATE 2012]
Q.11 Consider a relational table r with sufficient number of records, having attributes
A1, A2, A3, ……………..An and 1  p  n.
Two queries Q1 and Q2 are given below.
Q1: A1.............Ap (Apc (r))
Where is c a constant
Q2 : A1.............A p (c1 A p c2 (r))
Where c1 and c2 are 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 [GATE 2011]
Q.12 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-S (R-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)  u  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
[GATE 2009]
Q.13 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
[GATE 2008]
Q.14 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.  (R S)
P

II.  (R)  (S)

P P

III.  ( (R)  
P P,Q P,Q (S))
IV.  ( (R)  (
P P,Q 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
[GATE 2008]
Q.15 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) what 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?

 courseld   studId sex "female" studInfo     courseld  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. [GATE 2007]
Q.16 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. [GATE 2007]
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 74 GATE ACADEMY®

Q.17 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
[GATE 2007]
Q.18 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 respective instances of the above schemas. Assume that account
and depositor relations are much bigger than the branch relation.
Consider the following query:
 c  name (bcity"Agra" bal0 (branch (account depositor))
Which one of the following queries is the most efficient version of the above query?
(A)  cname  bal0 (bcity"Agra"branch account) depositor 
(B)  cname  bcity"Agra"branch (bal0account depositor) 

(C)  cname  (b city "Agra"branch bcity"Agra"bal0 account depositor 

(D)  cname  b city "Agra"branch (bcity"Agra"bal0 account depositor)  [GATE 2007]

Q.19 Which of the following relational query languages have the same expressive power?
(I) Relational algebra
(II) Tuple relational calculus restricted to safe expressions
(III)Domain relational calculus restricted to safe expressions
(A) II and III only (B) I and II only (C) I and III only (D) I, II and III
[GATE 2006]
Q.20 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
[GATE 2005]
Q.21 A data base 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 algorithms is employed to perform the join, with the most appropriate choice of table
to be used in outer loop, the number of block access required for reading the data are
(A) 80000 (B) 40080 (C) 32020 (D) 100
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 75 Relational Algebra and Calculus
Q.22 In the above question 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
[GATE 2005]
Q.23 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 (r1 C D r2 ) (D)  C (r1 CD r2 ) [GATE 2004]

Q.24 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
[GATE 2004]
Q.25 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:  is the rename operator).
name (sex female (Student)) name (student (sex female x  male  marks m) n,x,m (Student))

(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 then all the boy students [GATE 2004]
Q.26 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 [GATE 2002]
Q.27 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
(, , , , , , )?

Q.30 Given two union compatible relations R1 (A, B)and R 2 (C, D). What is the result of the operation?
R1 A  C  B D R2?

(A) R1  R 2 (B) R 1  R 2 (C) R1  R 2 (D) R1  R 2

[GATE 1998]
Q.31 Which of the following query transformations (i.e. replacing the l.h.s. expression by the r.h.s.
expression) is incorrect? R 1 and R 2 are relations, C1 ,C 2 are selection conditions and A1 , A 2 are attributes
of R 1 .
(A) C1 (C2 (R1 ))  C2 (C2 (R1 )) (B) C1 (A1 (R1 ))  A1 (C2 (R1 ))

(C) C1 (R1  R 2 )  C2 (R1 ) C1 (R 2 ) (D) πA1 (C1 (R1 ))  C1 (π A1 (R1 )) [GATE 1998]
Q.32 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 relation 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
[GATE 2001]
Q.33 Which of the following relational calculus expressions is not safe?
(A) {t u  R1 (t[A]  u[A])  s  R 2 (t[A]  s[A])}

(B) {t u  R1 (u[A]  'X'  s  R 2 (t[A]  s[A]  s[A]  u[A]))}

(D) {t u  R1 (t[A]  u[A])  s  R 2 (t[A]  s[A])} [GATE 2001]

Q.34 Consider the relation r1 (PQR) and r2 (RST) with primary keys P and R respectively. The relation r1
contains 2000 types and r2 contains 250 tuples. The maximum size of join r1 * r2 is [GATE 2006]
(A) 2000 (B) 2500 (C) 4500 (D) 5000

Q.35 Consider two relations R 1 (A, B) with tuples (1, 5) (3, 7) and R 2 (A, C)  (1, 7) (4,9) . Assume that R(A,
B, C) is the natural join of R 1 and R 2 . Consider the following tuples of the form (A, B, C); a = 1, 5,
null), b = (1, null, ), 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 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.
Q.36 A library relational database system uses the following schema
USERS (User #, User Name, Home Town)
BOOK (Book #, Book Title, Author Name)
ISSUED (Book #, User #, Date)
Explain in one English sentence, what each of the following relational algebra queries is designed to
determine.
(A) User #  6 (User #, BookTitle )
((USERS ISSUED) BOOK))
(B)  AuthorName ( BOOK (HomeTown  Delhi (USERS ISSUED)))
[GATE 1996 : IISc Bangalore]
Q.37 Consider the following relational scheme :
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 :
{t ∣ ∃ E ∈ Enrolment t=E.school-id ∧ | {x ∣ x ∈ Enrolment ∧ x.school-id=t ∧ (∃B ∈ ExamResult
B.erollno = x.erollno ∧ B.examname = x.examname ∧ B.marks > 35)} |÷| {x ∣ x ∈ Enrolment ∧
x.school-id = t} |∗100>35}
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 exam taken together.
(D) Schools with a pass percentage above 35% over each exam.
[GATE 2008 : IISc Bangalore]
Q.38 A relation r (A, B) in relational database has 1200 tuples. The attribute A has integer value ranging 6 to
20 and the attribute B has integer values ranging from 1 to 20. Assume that the attributes A and B
independently distributed. The estimated number of tuple in the output of ( A10)( B18) (r) is ______.
[GATE 2021 : IIT Bombay]
Q.39 The following relation records the age of 500 employees of a company, where empNo (indicating the
employee number) is the key :
empAge(empNo, age)
Consider the following relational algebra expression :
emp No (empAge (ageage1) emp No1, age1 (empAge))
What does the above expression generate?

a4 b1

a1 b2

a3 b2

a2 b3

a3 b3

a4 b3

a1 b4

a2 b4

a3 b4

If T  R  S then number of rows does T contains is ______

3 3 5 1 6

6 4 3 3 5

2 3 4 3 1

3 5

3 6

(A) 2, 3, 3, 5 (B) 3, 3, 1, 6 (C) 6, 4, 4, 1 (D) 3, 5, 3, 1

Q.10 Which of the following TRC expression will find the first name and last name of employees whose
salary is more than 20000?
(A) {t.FName, t.LName|Employee (t) AND t.SALARY > 20000}
(B) {t|Employee (t) AND t. SALARY > 20000}
(C) {t.FName, t.LName|EMPLOYEE (t) OR t. SALARY > 20000}
(D) {t.FName, t.LNAME|EMPLOYEE (t) AND t. salary  20000}
Q.11 Which of the following statement (s) is/are Incorrect?
I. (x)(P( x))  (x)(P( x))
II. Not (x)( P( x))  Not (x)( P( x))
(A) Both I and II (B) Only I (C) Only II (D) Neither I Nor II
Q.12 Which of the following statements is/are correct?
S1 : In relational algebra selection () operator is commutative
S 2 : In relational algebra projection () operator is commutative
(A) Both S1 and S 2 are correct (B) Both S1 and S 2 are incorrect
(C) S1 is correct but S 2 is incorrect (D) S1 is incorrect but S 2 is correct
Q.13 Which of the following operations can be expressed as a sequence of ,  and  (minus)?
(A) UNION (B) INTERSECTION (C) SELECTION (D) DIVISION
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 80 GATE ACADEMY®
Q.14 Let Employee (Eid, Ename, Salary, DNO) and Department (DNO, Dname) are two relational schemas
with primary keys as underlined. The relation employee contains 800 tuples and the relational
department contains 500 tuples. What is the maximum number of tuples in (Employee Department)?
(A) 80000 (B) 500 (C) 800 (D) 0
Q.15 Consider the schema Person (id, name, age, hobby) and identify the correct relational algebra expression
to select the persons whose age is above 20 and below 30?
(A) age20 and age30 (Person) (B) age20 or age30 (Person)
(C) age20 and age30 (Person) (D) 20age30 (Person)
Q.16 Which of the following statements is/are true about selection and projection operation?
I. Selection operator is used to filter rows
II. Projection operator to filter columns
III. Projection operator does not eliminate duplicate automatically from output.
(A) Only I and II (B) Only II and III (C) Only I (D) None
Q.17 Consider the join of a relation R1 with R2 . If R1 has 16 tuples and R2 has 4 tuples then the minimum size
of the join is
(A) 20 (B) 0 (C) 64 (D) 16
Q.18 Which of the following statements are true?
(i) ‘NOT IN’ is equivalent to != ALL
(ii) ‘IN’ is equivalent to != ALL
(iii) ‘NOT IN’ is equivalent to = ANY (iv) ‘IN’ is equivalent to =ANY
(A) ii and iii only (B) ii and iv only
(C) i and iii only (D) i and iv only
Q.19 You have two relations A and B. A has three tuples, and B has two. What is the maximum number of
tuples that could appear in the natural join of A and B?
(A) 2 (B) 3 (C) 4 (D) 6
Q.20 Select the persons whose hobby is either painting or singing.
(A) Hobby' Pa int ing 'ORHobby'Sin ging ' (Persons) (B) Hobby' Pa int ing ','Sin ging ' (Persons)
(C) Hobby' Pa int ing 'OR 'Sin ging ' (Persons) (D) All are correct
Q.21 Consider the sequence of operations given below on the relation Employee (Eid, Ename, address, Bdate,
Super_ssn, Dno)
1. MALE_EMPS   Sex' M ' (Employee)
2. RESULT1   Eid (MALE_EMPS)
3. RESULT2   Supper _ ssn (MALE_EMPS)
4. RESULT  RESULT1 U RESULT2
What will be the above sequence of operations performed on the given relation produce?
(A) Eid of an employee who is either male or a supervisor.
(B) Eid of all male supervisors.
(C) Eid of all male employee and Eid of their supervisors
(D) Eid of all male employees.
Q.22 Let the following two relational schemas be given
Employee (Eid, Ename, Sex, Dno)

Department ( Dno, Dname)

With primary keys are underlined. The relation employee contains 1500 tuples and the relation
department contains 1000 tuples. What is the maximum number of tuples in (Employee
Department)?
(A) 15,00,000 (B) 1500 (C) 1000 (D) 0
Q.23 Consider the given table called persons.
P_ID Last_Name First_Name Address City
10 Saklecha Kush Royal Krishna Indore
20 Jain Dhruvi Adinath Bhopal
30 Dave Sachin AB Road Indore
and the ‘orders’ table
O_ID Order_No. P.Id
11 77 30
12 44 30
13 22 10
14 24 10
15 34 50

Perform NATURAL JOIN Operation on both ‘Persons’ and ‘Orders’ tables and what is the Number of
tuples in the result?
(A) 4 (B) 5 (C) 6 (D) 7
Q.24 The operation of relation X, Produces Y, such that Y contains only selected attributes of X. Such an
operation is :
(A) Projection (B) Intersection (C) Union (D) Difference
Q.25 Which of the following statements is/are true?
S1 : Relational algebra is equivalent to relational calculus
S 2 : Relational calculus is procedural approach
(A) Only S1 (B) Only S 2 (C) Both S1 and S 2 (D) Neither S1 nor S 2
Q.26 Suppose relation R (A, B) has the tuples :
A B
1 2
3 4
5 6
and the relation S (C, D, E) has tuples :
C D E
2 4 6
4 6 8
4 7 9
Which of the following options is correct about R S?
( R . A S .C and R . B  S . D )

(A) (4,7,9) of S (B) (5,6) of R

Answers

Classroom Practice Questions

1 B 2 C 3 D 4 4 5 D
6 A 7 A 8 D 9 A 10 A
11 C 12 A 13 C 14 D 15 B
16 C 17 D 18 A 19 D 20 C
21 C 22 B 23 B 24 A 25 D
26 C 27 C 28 B 29 C 30 D
31 D 32 C 33 C 34 A 35 C
36 * 37 C 38 820 39 C
Self-Practice Questions
1 C 2 A 3 D 4 A 5 C
6 2 7 A 8 D 9 A 10 A
11 D 12 C 13 D 14 C 15 A
16 A 17 B 18 D 19 D 20 A
21 C 22 B 23 A 24 A 25 D
26 A
36 *
Classroom Practice Answer key
(A) Show all the book tittle which been issued #6.
(B) The user who hometown is Delhi and issued a book show all the name author of the book he/she
have issued.


Classroom Practice Questions :

Q.1 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) Diamonds with double/bold border (B) Rectangles with double/bold border
(C) Ovals with double/bold border (D) Ovals that contain underlined identifiers.
[GATE 2020]
Q.2 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 that
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. [GATE 2018]
Q.3 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 many-to-one and the participation of A in R is partial. [GATE-2017]
Q.4 Consider the following tables T1 and T2.
T1 T2
P Q R S
2 2 2 2
3 8 8 3
7 3 3 2
5 8 9 7
6 9 5 7
8 5 7 2
9 8

For (StudentName, StudentAge) to be the key for this instance, the value X should not be equal to
(A) 18 (B) 19 (C) 20 (D) 21 [GATE 2014]
Q.8 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.
[GATE-2012]
Q.9 Consider the following ER diagram.
M2 P1 P2 N1 N2
M1 M3

M R1 P R2 N

The minimum number of tables needed to represent M, N, P, R1, R2 is

(A) 2 (B) 3 (C) 4 (D) 5
Q.10 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}
[GATE-2008]
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
GATE ACADEMY® 85 ER Diagram
Q.11 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. The set of all tuples that must be additionally deleted to preserve
referential integrity.
A 2 3 4 5 7 9 6
C 4 4 3 2 2 5 4
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) 1 [GATE-2005]
Q.12 Let E1 and E2 be two entities in an E/R diagram with simple single valued attributes, R1 and R2 are two
relationships between E1 and E2 , where R1 is one-to-many and R 2 is many-to-many. R1 and R2 do not
have any attributes of their own. What is the minimum number of tables required to represent this situation
in the relational model?
(A) 2 (B) 3 (C) 4 (D) 5 [GATE-2005]
Q.13 Consider the entities ‘hotel room’, and ‘person’ with a many to many relationship ‘lodging’ as shown
below
Hotel
Lodging Person
Room

If we wish to store information about the rent payment to be made by person (s) occupying different
hotel rooms, then this information should appear as an attribute of
(A) Person (B) Hotel Room (C) Lodging (D) None of these
[GATE-2005]
Q.14 Consider the following entity relationship diagram (ERD), where two entities E1 and E2 have a relation
R of cardinality 1: m
A11 A12 A21
A22

A13 E1 1 M E2 A23
R

A relational database containing minimum number of tables with each table satisfying the requirements
of the 3rd normal form(3NF) is designed from the above ERD. The number of minimum table ?
(A) 2 (B) 3 (C) 5 (D) 4 [GATE-2004]
Q.15 In E-R model, Y is the dominant entity and X is 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 [ISRO-2018]
Q.16 Immunity of the external schemas (or application programs) to changes in the conceptual schema is
referred to as:
(A) Physical Data Independence (B) Logical Data Independence
(C) Both (a) and (b) (D) None of the above [ISRO-2018]

Q.17 Which symbol denote derived attributes in ER Model?

(A) Double ellipse (B) Dashed ellipse
(C) Squared ellipse (D) Ellipse with attribute name underlined
[ISRO-2017]
Gate Academy Shop Address : Street 04, Narsingh Vihar, Katulbod, Bhilai 490022 (C.G.), Contact : 97131-13156 Online Test Series
https://www.gateacademy.shop/ Live class room: https://play.google.com/store/apps/details?id=com.gateacademy1 http://onlinetestseries.gateacademy.co.in
Database Management System [Workbook] 86 GATE ACADEMY®
Q.18 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 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
[ISRO-2016]
Q.19 Consider the following Relationship Entity Diagram (ERP)

QualifiedDate

Person Qualification Exam

NI ExamID ExamName
Name
D
Which of the following possible relations will not hold if the above ERD is mapped into a relation
model?
(A) Person (NID, Name) (B) Qualification (NID, ExamID, QualifiedDate)
(C) Exam (ExamID, NID, ExamName) (D) Exam (ExamID, ExamName) [ISRO-2015]
Q.20 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 [ISRO-2011]
Q.21 Purpose of 'Foreign Key' in a table is to ensure
(A) Null Integrity (B) Referential Integrity
(C) Domain Integrity (D) Null and Domain Integrity [ISRO-2009]
Q.22 State True or False :
Logical data independence is easier to achieve than physical data independence.
[GATE 1994 : IIT Kharagpur]
Q.23 Consider the following statements S1 and S2 about the relational data model:
S1: A relational scheme can have at most one foreign key.
S2: A foreign key in a relation scheme R cannot be used to refer to tuples to R.
Which one of the following choices is correct?
(A) Both S1 and S2 are true. (B) S1 is true and S2 is false.
(C) S1 is false and S2 is true. (D) Both S1 and S2 are false.
[GATE 2021 : IIT Bombay]

Self-Practice Questions :
Q.1 Which of the following statements is Incorrect?
S1 : Data elements in the data base can be modified by changing the data dictionary.
S 2 : The data dictionary contains the name and description of each data element.
(A) Only S1 (B) Only S 2 (C) Both S1 and S 2 (D) Neither S1 nor S 2
Q.2 R is a relationship between the entities E1 and E2 . The existence of E2 is completely dependent on the
entity set E1. Which of the following construct is used to represent the entity set E2 ?
(A) Dotted rectangle (B) Diamond
(C) Double outlined rectangle (D) Rectangle
Q.3 Given {Student_id} is a candidate key, {Student name, Student_street} is another candidate key then,
(A) {Student_id, Student_name} is also a candidate key.
(B) {Student_id, Student_street} is also a candidate key.
(C) {Student_id, Student_name, Student_Street}is also a candidate key.
(D) None
Q.4 An edge between an entity set and a binary relationship set can have an associated minimum and
maximum cardinality, shown in the form (1, n) where ‘1’ is the minimum and ‘n’ is the maximum
cardinality. The minimum value ‘1’ indicates :
(A) Total participation (B) Partial participation
(C) Double participation (D) No participation
Q.5 In terms of relational model, which of the following is correct?
(A) Cardinality is termed as a number of tuples.
(B) Cardinality is termed as a number of tables.
(C) Cardinality is termed as a number of attributes
(D) Cardinality is termed as a number of constraints
Q.6 If PQRST are the attributes of relation and PQRS is super key and PQR is also super key then
(A) PQR must be candidate key. (B) PQR cannot be super key.
(C) PQR cannot be candidate key. (D) PQR may be candidate key.
Q.7 Consider the following ER diagram :
r1

a11 a12 a21

1 M
E1 R E2 a22

How many minimum number of tables are required to represent the above ER diagram?
(A) 2 (B) 3 (C) 4 (D) 1

Years_Employeed EMPLOYEE Skill

Date_Completed

(A) 1 (B) 2 (C) 3 (D) 4

Q.9 Let a Relation R (a1 , a2 , a3 an ) and a1 is the candidate key. Then how many super keys are possible?
(A) 2n1 (B) 3n1 (C) 2n  1 (D) 2 n2
Q.10 The attribute name has two parts First_name and Last_name. The attribute name is.
(A) Single valued (B) Multi valued (C) Composite (D) Derived
Q.11 The attribute AGE is calculated from Date_of_Birth. The attribute AGE is.
(A) Single valued (B) Multi valued (C) Composite (D) Derived
Q.12
a1 a2 b1 b2

1 N
A R B

Which of the following attribute set is correct representation when the above ER diagram is converted
into a minimum number of relations in a relational model?
(A) b , b 
1 2 (B) a , a , b 
1 2 1 (C) b , b , a 
1 2 1 (D) a , a , b , b 
1 2 1 2

Q.13 Identify the correct statement(s)?

(A) Student (roll_no., S_name, marks) is a relation instance.
(B) (15, Yadu, 98) is an instance of a relation schema.
(C) (15, Yadu, 98) specifies a relation schema.
(D) (15, Yadu, 98) is neither an instance of a relation schema nor specifies a relation schema.
Q.14 Consider a relation R ( A1 , A2 An ). How many number of super keys are possible for R, if every attribute
is the candidate key?
(A) 2 n (B) 2n1 (C) 22 n1 (D) 2n  1
Q.15 Let there be a relation R (ABCDE) with FD set :  A  BC , C  D, D  A. How many super keys are
possible?
(A) 10 (B) 11 (C) 13 (D) 14
Q.16 Consider an empty relation R (AB) where A is primary key and B is foregin key referring same relation
R. Which one of the following row sequence can be inserted into R?
(A) (1, 2) (2, 3) (3, 4) (4, 5) (B) (1, NULL) (2, 1) (3, 2) (4, 3)
(C) (1, NULL) (2, NULL) (4, 3) (5, 4) (D) None

Answers

Classroom Practice Questions

1 A 2 A 3 C 4 0 5 4
6 8 7 B 8 C 9 B 10 A
11 C 12 B 13 C 14 3 15 B
16 B 17 B 18 B 19 C 20 C
21 B 22 False 23 D
Self-Practice Questions
1 A 2 C 3 D 4 A 5 A
6 D 7 A 8 B 9 A 10 C
11 D 12 C 13 B 14. D 15. D
16. B



1 FDNormalForm30YearsPYQsPart12007-1993byVijayAgarwalSir
No ratings yet
1 FDNormalForm30YearsPYQsPart12007-1993byVijayAgarwalSir
49 pages
Practice Questions
No ratings yet
Practice Questions
4 pages
Mid Sem Exam1
No ratings yet
Mid Sem Exam1
3 pages
GATE Questions-DBMS-Functional Dependencyas 1
50% (4)
GATE Questions-DBMS-Functional Dependencyas 1
15 pages
FD
No ratings yet
FD
5 pages
Dbms Functional
No ratings yet
Dbms Functional
4 pages
GATE Questions-DBMS-Functional Dependency
No ratings yet
GATE Questions-DBMS-Functional Dependency
6 pages
GATE Dbms Functional Dependency
No ratings yet
GATE Dbms Functional Dependency
6 pages
Quiz About Database Design (Normal Forms)
No ratings yet
Quiz About Database Design (Normal Forms)
45 pages
Functional Dependencies and Normalization
No ratings yet
Functional Dependencies and Normalization
28 pages
Dbms DPP Part 2 Solution
No ratings yet
Dbms DPP Part 2 Solution
22 pages
Previous GATE Questions With Solutions On DBMS (Normalization) - CS/IT
100% (3)
Previous GATE Questions With Solutions On DBMS (Normalization) - CS/IT
7 pages
GATE Dbms Normalization
No ratings yet
GATE Dbms Normalization
7 pages
Database Normalization Quiz
No ratings yet
Database Normalization Quiz
6 pages
Functional Dependencies and Candidate Keys
No ratings yet
Functional Dependencies and Candidate Keys
2 pages
GATE 2021 Questions
No ratings yet
GATE 2021 Questions
12 pages
Gate Question On Dbms
No ratings yet
Gate Question On Dbms
5 pages
Database Management System: Assignment 4: September 2, 2018
No ratings yet
Database Management System: Assignment 4: September 2, 2018
10 pages
Database Management System (Solution)
No ratings yet
Database Management System (Solution)
7 pages
Database Normalization Quiz
No ratings yet
Database Normalization Quiz
4 pages
Assignment 4 - V2
No ratings yet
Assignment 4 - V2
10 pages
Functional Dependency Practice Set
No ratings yet
Functional Dependency Practice Set
3 pages
Assignment 4 NPTEL DBMS January 2025
No ratings yet
Assignment 4 NPTEL DBMS January 2025
10 pages
Relational Algebra and SQL Queries MCQs
No ratings yet
Relational Algebra and SQL Queries MCQs
22 pages
DBMS DPP 2
No ratings yet
DBMS DPP 2
3 pages
DB Part1
No ratings yet
DB Part1
9 pages
Assignment 4
50% (2)
Assignment 4
10 pages
FD - Normalization
No ratings yet
FD - Normalization
12 pages
CTV - DBMS Questions (Anskey)
No ratings yet
CTV - DBMS Questions (Anskey)
6 pages
GATE DBMS Normalization Questions
100% (1)
GATE DBMS Normalization Questions
100 pages
4 Database Management Systems
No ratings yet
4 Database Management Systems
4 pages
DBMS W06 Pas
No ratings yet
DBMS W06 Pas
14 pages
CC-215 DBS Assignment #2 - SPR 25
No ratings yet
CC-215 DBS Assignment #2 - SPR 25
3 pages
Mindterm Sol
No ratings yet
Mindterm Sol
12 pages
Normalization Questions
No ratings yet
Normalization Questions
8 pages
Unit III Normalization
No ratings yet
Unit III Normalization
4 pages
DB 2022
No ratings yet
DB 2022
7 pages
Database Management System 1. For A Database Relation R (A, B, C, D) Where The Domains of A, B, C and D Only Include Atomic
No ratings yet
Database Management System 1. For A Database Relation R (A, B, C, D) Where The Domains of A, B, C and D Only Include Atomic
4 pages
Module 3 ND 4 QP Discussion
No ratings yet
Module 3 ND 4 QP Discussion
13 pages
Checking Functional Dependencies in R
No ratings yet
Checking Functional Dependencies in R
17 pages
1.practice Questions and Solutions Set-1
No ratings yet
1.practice Questions and Solutions Set-1
10 pages
2016 hw4 Solutions
No ratings yet
2016 hw4 Solutions
3 pages
Quiz - Quiz 8
No ratings yet
Quiz - Quiz 8
10 pages
NPTEL DBMS 2 Week 4
No ratings yet
NPTEL DBMS 2 Week 4
42 pages
Quiz-Ans Iitg Ma321
No ratings yet
Quiz-Ans Iitg Ma321
5 pages
Database Design Quiz
No ratings yet
Database Design Quiz
24 pages
Normalization Solutions
No ratings yet
Normalization Solutions
14 pages
3.1 Normalization
No ratings yet
3.1 Normalization
13 pages
DBMS MCQ: Normalization Concepts
No ratings yet
DBMS MCQ: Normalization Concepts
6 pages
Database Management System Weekly Test 01 Test Paper
No ratings yet
Database Management System Weekly Test 01 Test Paper
5 pages
Databases
No ratings yet
Databases
20 pages
MCQ SK CK PK NF
No ratings yet
MCQ SK CK PK NF
14 pages
Hw7 Sol Motro
100% (1)
Hw7 Sol Motro
6 pages
Relational Database Management System: Student
No ratings yet
Relational Database Management System: Student
28 pages
RPSC Programmer Exam DBMS & Java Tests
No ratings yet
RPSC Programmer Exam DBMS & Java Tests
27 pages
Normalization in Database Manangement System
No ratings yet
Normalization in Database Manangement System
12 pages
Normalization
No ratings yet
Normalization
161 pages
Ch1-Introduction, Fundamentals, Classic Mistakes
No ratings yet
Ch1-Introduction, Fundamentals, Classic Mistakes
33 pages
Understanding Expert Systems Components
No ratings yet
Understanding Expert Systems Components
9 pages
Wireshark Lab 1.2 Import and Examine PCAP File (V1.1)
No ratings yet
Wireshark Lab 1.2 Import and Examine PCAP File (V1.1)
9 pages
Autocad Mep 2009
100% (1)
Autocad Mep 2009
98 pages
Cbc-Esl-General Eng Course 2
No ratings yet
Cbc-Esl-General Eng Course 2
50 pages
Culture Health Intro CH 1 R
No ratings yet
Culture Health Intro CH 1 R
69 pages
Priyadarshini Tamilselvan
No ratings yet
Priyadarshini Tamilselvan
1 page
Ceia THS-21
No ratings yet
Ceia THS-21
6 pages
Updated CV
No ratings yet
Updated CV
3 pages
Manually SQA Testing Interview Question
No ratings yet
Manually SQA Testing Interview Question
8 pages
Censo
No ratings yet
Censo
562 pages
Resume January 2011
No ratings yet
Resume January 2011
3 pages
Root Finding Methods in Matlab
100% (3)
Root Finding Methods in Matlab
22 pages
Communication Process Explained
No ratings yet
Communication Process Explained
2 pages
ANDROID
No ratings yet
ANDROID
54 pages
Credit Suisse - Measuring The Moat
100% (1)
Credit Suisse - Measuring The Moat
70 pages
Prompt Engineering White Paper
100% (1)
Prompt Engineering White Paper
13 pages
AutoCAD by Dhyan Pal Sir
No ratings yet
AutoCAD by Dhyan Pal Sir
81 pages
Raymarine 54E DSC VHF Manual PDF
No ratings yet
Raymarine 54E DSC VHF Manual PDF
116 pages
Official Laptop Handover Acknowledgment
No ratings yet
Official Laptop Handover Acknowledgment
3 pages
Singtel Broadband, Mobile Plans (English Version) - 22022025 - Watermark
No ratings yet
Singtel Broadband, Mobile Plans (English Version) - 22022025 - Watermark
9 pages
Guidelines in HCI
No ratings yet
Guidelines in HCI
60 pages
Modbus RTU Protocol Guide
No ratings yet
Modbus RTU Protocol Guide
18 pages
Frequency Domain Filters
No ratings yet
Frequency Domain Filters
43 pages
HP Parts Reference Guide™: Laserjet 1300 Series Printer
No ratings yet
HP Parts Reference Guide™: Laserjet 1300 Series Printer
18 pages
Pengenalan Sistem Audio & Video
No ratings yet
Pengenalan Sistem Audio & Video
41 pages
College Brochure
No ratings yet
College Brochure
60 pages
Client Mate Manual
No ratings yet
Client Mate Manual
4 pages
Raghu Engineering College
No ratings yet
Raghu Engineering College
24 pages
LAB6 Final
No ratings yet
LAB6 Final
4 pages

Dbms WB Print

Uploaded by

Dbms WB Print

Uploaded by

Database

Computer Science Engineering

GATE / ESE / PSUs

Ankush Saklecha Sir

1. Relational Algebra and Relational Calculus ………..……. 1

2. Transaction and Serializability………………….…….………. 16

3. File Organization & Indexing ……………………….…………. 32

5. Structured Query Language ……………………………………. 47

6. Relational Algebra and Calculus………………..………..……. 69

Classroom Practice Questions :

Common Data for

Q.40 Let R(ABCDEF) be a relation scheme with the following dependencies:

The table is in which normal form?

Classroom Practice Questions

Classroom Practice Questions :

Which one of the following statements is CORRECT?

Which of the following statements is TRUE?

Which of the following statements is correct?

S3 : R1 ( B), R1 ( A),W1 ( A), R2 ( B),W2 ( B),W1 ( B)

(A) Schedule shows lost update Anomaly

Classroom Practice Questions

Classroom Practice Questions :

(A) 1 (B) 2 (C) 3 (D) 4

What is the result of inserting G in the above tree?

Classroom Practice Questions

Classroom Practice Questions :

Classroom Practice Questions

Classroom Practice Questions :

Q.14 Consider the following relational schema.

IV. {< SN | SR R P ( SR ,SN  Students  SR ,107, R P  Registration  R P >90)}

Consider these statements about Query 1 and Query 2.

Common Data for

Q.22 What is the output of the following SQL query?

(B) a1 a 2 ....a n P (r1 r2 .... rm )

(D) a1 a 2 ....an P (r1  r2  ....  rm )

Classroom Practice Questions

Classroom Practice Questions :

(C)  B (r1 )   C (r2 ) (D)  B (r1 )   C (r2 )   [GATE 2012]

II. {t t  R-S (r)  u  s(v  r((u  v[s]  t  v[R  S]))}

III. {t t  R-S (r)  u  r(u s(u  v[s] t  v[R  S]))}

II.  (R)  (S)

 courseld   studId sex "female" studInfo     courseld  enroll    enroll 

(C)  cname  (b city "Agra"branch bcity"Agra"bal0 account depositor 

(D)  cname  b city "Agra"branch (bcity"Agra"bal0 account depositor)  [GATE 2007]

(C)  D (r1 C D r2 ) (D)  C (r1 CD r2 ) [GATE 2004]

(A) Names of girl students with the highest marks

(A) R1  R 2 (B) R 1  R 2 (C) R1  R 2 (D) R1  R 2

(B) {t u  R1 (u[A]  'X'  s  R 2 (t[A]  s[A]  s[A]  u[A]))}

(D) {t u  R1 (t[A]  u[A])  s  R 2 (t[A]  s[A])} [GATE 2001]

If T  R  S then number of rows does T contains is ______

(A) 2, 3, 3, 5 (B) 3, 3, 1, 6 (C) 6, 4, 4, 1 (D) 3, 5, 3, 1

Department ( Dno, Dname)

(A) (4,7,9) of S (B) (5,6) of R

Classroom Practice Questions

Classroom Practice Questions :

The minimum number of tables needed to represent M, N, P, R1, R2 is

Q.17 Which symbol denote derived attributes in ER Model?

Person Qualification Exam

a11 a12 a21

Years_Employeed EMPLOYEE Skill

(A) 1 (B) 2 (C) 3 (D) 4

Q.13 Identify the correct statement(s)?

Classroom Practice Questions

You might also like