Notesale: Turn your study into money

Document Preview

Extracts from the notes are below, to see the PDF you'll receive please use the links above

---

DATABASE AND MANAGEMENT SYSTEMS
1
...
The following
SQLquery select
distinct al ,
a2,
...
, rm
where P
For an arbitrary predicate P, this query is equivalent to which of the following
relational algebra expressions
GATE
2003
1
...

3
...

SOLUTION:1
3
...
Suppose there is no violation of
the above referential integrity constraint in the

IT DEPARTMENT

GATE MATERIAL

i

corresponding relation instances r1 and r2
...
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
...
What are the maximum
and minimum number of tuples that can be present in (Student * Enroll),
where '*' denotes natural join ?
GATE 2004
1) 8, 8
2) 120, 8
3) 960, 8
4) 960, 120
SOLUTION:3
5
...
The order of an internal node in a B+ tree index is the maximum number of
IT DEPARTMENT

GATE MATERIAL

children it can have
...
What is the order
of the internal node ?
GATE
2004
a
...
25 c
...
27

SOLUTION C
7
...

(d) the average salary of male employees is more than the average
salary in the company
SOLUTION D
8
...
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
SOLUTION C
10
...
We define r1 = ΠA, B, C (r)
and r2= Π A
...
Let s = r1 * r2 where * denotes natural join
...
Consider the relation account (customer, balance) where customer is a primary
key and there are no null values
...
The customer with the largest balance gets rank
1
...

GATE 2006
Query 1 : select A
...
customer) from account A, account
B where
A
...
balance group by A
...

Query 2 : select A
...
customer) from account A, account B where
A
...
balance 7 group by A
...

1
...

3
...

5
...
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
...
Given the
following four queries : Query 1 : select student form enrolled where student in
(select student form paid)
GATE 2006
Query 2 : select student from paid where student in (select student from
enrolled) Query 3 : select E
...
student = P
...
student =
paid
...
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
that amounts 6000, 7000,
8000, 9000 and 10000 were each paid by 20% of the students
...
"
GATE
2006

A disk seek takes 4 ms, disk data transfer bandwidth is 300 MB/s and checking a
tuple to see if amount is greater than x takes 10 µs
...
Information about a collection of students is given by the relation studInfo (studId, name,
sex)
...
Assume that every course is taken by at least one male and at least one
female student
...

b) Courses in which a proper subset of female students are enrolled
...

d) None of the above
...
Consider the relation employee (name, sex, supervisorName) with name as the
key
...

What does the following Tuple Relational Calculus query produce ?
{e
...
supervisorName ≠ e
...
sex=
"male"]}
GATE
2007
a) Names of employees with a male supervisor
...

c) Names of employees with no immediate female subordinates
...

SOLUTION B
16
...
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
statements is TRUE for any arbitrary employee table ?
GATE 2007
IT DEPARTMENT

GATE MATERIAL

Q1 : Select e
...
department = "5" and s
...
salary)
Q2 : Select e
...
salary > Any
(Select distinct salary From employee s Where s
...

b) Q2 is the correct query
...

d) Neither Q1 nor Q2 is the correct query
...
Which one of the following statements is FALSE?
GATE 2007
a) Any relation with two attributes is in BCNF
...

c) A prime attribute can be transitively dependent on a key in a 3NF relation
...

SOLUTION D
18
...
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?
GATE 2007
a) 63
SOLUTION B

b) 64

c) 67

d) 68

19
...
Which one of the following
statements is TRUE ?
GATE 2007
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)
IT DEPARTMENT

GATE MATERIAL

ga e edoon com

a) Both S1 and S2 are conflict serializable
...

c) S1 is not conflict serializable and S2 is conflict serializable
...
Consider the following relational schemes for a library
database: Book (Title, Author, Catalog_no, Publisher,
Year, Price) Collection (Title, Author, Catalog_no)
GATE 2008
With the following functional
dependencies: I
...
Catalog_no → Title Author
Publisher Year
III
...
Which of the following
statements is true?
a) Both Book and Collection are in BCNF
b) Both Book and Collection are in 3NF only
c) Book is in 2NF and Collection is in 3NF
d) Both Book and Collection are in 2NF only
SOLUTION C
21
...
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 B+ - tree is initially empty
...
Which of the following statements are TRUE about an SQL query?
GATE 2012
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 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
IT DEPARTMENT

GATE MATERIAL

SOLUTION A
24
...
Consider a database table T containing two columns X and Y each of type integer
...

GATE 2011

Let MX and MY denote the respective maximum values of X and Y among all records in the
table at any point in time
...
It may be noted that each
time after the insertion, values of MX and MY change
...
Which of the following relational calculus expressions is not safe?
GATE 2001
(a)

] J
{t ∃ u ∈ R1 (t[A] = u [ A] )∧ ¬∃ s ∈ R2 (t ] A] = s ] A] )}
J
[ J
[ J

(b) t ∀ u ∈ R ( u ] A] = " x " c ∃ s ∈ R ( t ] A] = s ] A] ∧ s ] A] = u ] A] ))
1 [ J
2 [ J
{
[ J
[ J
[ J }
(c) {t ¬ ( t ∈ R1 )}
t ∃ u ∈ R1 ( t ] A] = u ] A] ) ∧ ∃ s ∈ R2 ( t ] A] = s ] A] )}
[ J
[ J
[ J
[ J
(d) {
SOLUTION

44
...
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 a B+ tree in which the maximum number of keys in a
its node is 5
vio What is the minimum number of key in any root node?
lat
GATE 2010
ion a
...
2 c
...
4
SOLUTIO
SOLUTION
N

IT DEPARTMENT

GATE MATERIAL

48
...

GATE 2011
Borrower

Bank_Manager

Loan_Amount

Ramesh

Sunderajan

10000
...
00

Mahesh

Sunderajan

7000
...
questionpapers
...
in/

FROM (
(SELECT Borrower, Bank_Manager FROM
Loan_Records) AS S NATURAL JOIN

(SELECT Bank_Manager, Loan_Amount FROM Loan_Records) AS T
);
(A) 3
(C) 5

SOLUTION

(B) 9
(D) 6

49
...
Registration_number: Unique registration number for each registered student
2
...
Bank Account_number: Unique account number at the bank
...
This attributes stores the primary account number
...
Name: Name of the student
5
...
Bank Account_number is candidate key

2
...
UID is a candidate key, if all students are from the same country
4
...
Which of the following is a key factor for preferring B+ tree to binary search trees for
indexing database relations ? [gate 2005]
1
...
database relations are sorted on the primary key
3
...
data transfer from disk is in blocks
Answer: 4
51
...
BCNF is stricter than 3NF
2
...

3
...
Any relation with two attributes is in BCNF
Answer:3
52
...
The number of tuples in the student and Enroll tables
are 120 and 8 respectively
...
8,8
2
...
960,8
4
...
Which of the following scenarios may lead to an irrecoverable error in a database system ?
[GATE 2003]
1
...
A transaction reads a data item after it is read by an uncommitted transaction
IT DEPARTMENT

GATE MATERIAL

3
...
A transaction reads a data item after it is written by an uncommitted transaction
Answer:4
54
...

The redundancy ( arising our of functional dependencies) in the resulting set of relation is
[GATE 2002]
1
...
more than zero but less than that of an equivalent 3 NF decomposition
3
...
indeterminate
Answer:1
55
...

S2: Every binary tables is in BCNF
Which of the following is true
1
...
S1 is true
3
...
none of these
Answer:2

56
...
This is

1
...
physical
IT DEPARTMENT

GATE MATERIAL

level data
hiding
3
...
none of
these
Answer:1
57
...
Then the
decomposition of R into R1(AB) and R2(CD) is
1
...
lossless join but not dependency preserving
3
...
not dependency preserving and not lossless join
Answer:3
58
...
List of all vertices adjacent to a given vertex
2
...
List of all vertices which belong to cycles of less than three vertices
4
...
Consider the following schedules involving two transactions
...
both s1 and s2 are not conflict serializable
2
...
s1 is not conflict serializable and s2 is conflict serializable
4
...
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
...
(5,2) and (7,2)
2
...
(3,4) (4,3) (5,2) and (6,4)
4
...
Consider a relational schema R= (A, B,C,D,E,F,G,H) on which of the following functional
dependencies hold: { A->B, BC->D, E->C, D->A}
...
AEH,BEH,BCH
2
...
AEH
...
AE,BE,DE
Answer:3
62
...
2NF
2
...
3NF
4
...
A B+ tree index is to be built on the name attribute of the relation Student
...
Give this scenario, what would be the best choice of the degree [GATE 2002]
1

16

2

43

3
...
44
Answer:1
64
...
Assuming
that no two books have the same price, what does the following sql query list ?[GATE 2005]
Select title from book as B where (select count(*) from book as T where T
...
price)<5

IT DEPARTMENT

GATE MATERIAL

1 titles of the 5 most expensive books
2 title of the 5th most inexpensive books
3 title of the 5th most expensive books
4

titles of the 4 most expensive books

Answer:1
65
...
Each record is 32 bytes long and its key field is of size
bytes
...
The file is
stored in a file system with block size 1024 bytes, and the size of a block pointer is 10 bytes
...
the following functional dependencies are given below [gate 2005]
AB-> CD, AF->D, DE->F, C->G, F->E, and G->A
Which of the following option is false?

1
...


{AF}+ = {ABCDEFG}

3
...
{AB}+ = {ABCDFG}

Answer:2
IT DEPARTMENT

GATE MATERIAL

67
...
YZ->X AND X->Z
2
...
XY->Z AND Z->Y
4
...
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 below
...
The B+ trees is initially empty
...


2

2
...


5

4
...
from the following instance of a relational schema R(A,B,C) We can conclude that

IT DEPARTMENT

GATE MATERIAL

A B C
1 1 1
1 1 0
2 3 2
2 3 2

1
...
A functionally determine B and B functionally determines C
3
...
B does not functionally determines C
Answer:1
70
...
Suppose all comparisons with a null value are treated as false
...
x=!5 not (x=5)
2
...
x=5 not (not(x=5)
4
...
Consider the table employee(empid, name, department, salary) and the two queries Q1
and Q2 below
...
empid from employee e Where non exists ( select * From employee s where
s
...
salary>= e
...
empid from employee e where e
...
department='5')

1
...
Both Q1 and Q2 gives same results
3
...
Q2 is correct query
Answer:4

72
...

Borrower Bank_Manager Loan_Amount
Ramesh

Sunderrajan

100000

Suresh

Ramgopal

5000

Mahesh

Sunderrajan

7000

What is the output of the following SQL query ?
SELECT count(*) From(SELECT Borrower
...
9
2
...
6
4
...
The order of a leaf node in a B+ tree is the maximum number of (value, data, record
pointer) pairs it can hold
...
63
2
...
68
4
...
The following functional dependencies hold for relations R(A, B, C) and S(B, D, E ) [GATE
2010]
B->A
A->C
The relation R contains 200 tuples and the relation S contains 100 tuples
...
100
2
...
200
4
...
Which of the following statements is false ? [GATE 2007]

1
...
A prime attribute can be transitively dependent on a key in a 3 NF relation
3
...
any relation with true attributes is in BCNF
Answer:3
76
...
s has no duplicates and r is non empty
IT DEPARTMENT

GATE MATERIAL

2
...
r and s have the same number of tuples
4
...
The order of an internal node in a B+ tree index is the maximum number of children it can
have
...
What is the order of the internal node ? [ GATE 2004]
1
...


25

3
...


24

Answer:26

78
...
Which of the following does not have a lossless join, dependency
preserving BCNF decomposition ? [GATE 2001]

1
...
A->B, B->CD
3
...
A->B, B->C , C->D
Answer:1
79
...
R1 and
R2 are two relationships between E1 and E2, where R1 is one to many and R2 is many to
many
...
What is the minimum number of
tables required to represent this situation in the relational model ? [GATE2005]

IT DEPARTMENT

GATE MATERIAL

1
...


2

3
...


3

Answer:4
80
...


the average salary of male employee is more than the average salary of all male employee
in the company

2
...
the average salary is more than the average salary in the company
4
...
Relation R is decomposed using a set of functional dependencies F, and relation S is
decomposed using another set of functional dependencies G
...
3 NF definition
2
...
Lossless join
4
...
In the index allocation scheme of blocks to a file, the maximum possible size of the file
depends on ? [GATE 2002]

1
...
the number of blocks used for the index and the size of the blocks
3
...


the size of the blocks, the number of blocks used for the index and the size of the address
of the blocks

Answer:2
83
...
First Normal form
2
...
third form
4
...
Which of the following contains complete record of all activity that affected the
contents of a database during a certain period of time
1
...
d-BASE
3
...
SQL
Answer:2

85
...

For any pincode, there is only one city and state
...

In normalization terms, empdt1 is a relation in
IT DEPARTMENT

GATE MATERIAL

1
...
2 NF and hence also in 1 NF
3
...
BCNF and hence also in 3NF, 2NF and 1NF

Answer:2

5
...

(a) What is the highest normal form satisfied by this relation?
(b) Suppose the attributes Book – title and Author – address are
added to the relation, and the primary key is changed to
{Name – of – Author, Book – title}, what will be the
highest normal form satisfied by the relation?
SOLUTION

6
...
There are five records in a database
...
Consider the schema R = (S T U V) and the dependencies S € T, T € U
...
Let R = (R1 and R2) be a decomposition such that R1 ∩
R2 = ∅
...

Which of the following
is/are correct?
GAT
E 1999
(a) An SQL query automatically eliminates duplicates
(b) An SQL query will not work if there are no indexes on
the relations (c) SQL permits attribute names to be
IT DEPARTMENT

GATE MATERIAL

repeated in the same relation
(d) None of the above
SOLUTION
10
...
In SQL, relations can contain null values, and comparisons with null
values are treated as unknown
...
Which of the following pairs is not
equivalent?
GATE 2000
(a) x = 5

not (not (x = 5)

(b) x = 5
(c) x ≠ 5

x > 4 and x < 6, where x is an integer

no
t (x = 5) (d) None of
the above
SOLUTION

12
...

Then the decomposition of R into R1 (AB) and R2(CD) is
GATE 2000
(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
SOLUTON
13
...
Which of the following queries cannot be
expressed by a relational algebra expression of constant length?
GATE 2000
(a) List of all vertices adjacent to a given vertex
(b) List all vertices which have self loops
(c) List all vertices which belong to cycles of less than three
vertices
(d) List all vertices reachable from a given vertex
14
...
Relation R with an associated set of functional dependencies, F, is decomposed into
BCNF
...
With regard to the expressive power of the formal relational query languages,
which of the following statements is true?
GATE 2002
(a) Relational algebra is more powerful than relational calculus
(b) Relational algebra has the same power as relational calculus
...

(d) None of the above
SOLUTION
28
...

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
...
e
...
Relation R is decomposed using a set of functional dependencies, F, and relation
S is decomposed using another set of functional dependencies, G
...
3NF, but it is not known which is which
...
‘-‘7
GATE 2002
(a) Dependency-preservation
(b) Lossless-join
(c) BCNF definition
(d) 3NF definition
SOLUTION

41
...
Which of the following does not have a
lossless join, dependency preserving BCNF decomposition?
GATE 2001
(a) A € B, B € CD

(b) A € B, B

€ C, C € D (c) AB € C, C € AD

(d) A € BCD

IT DEPARTMENT

GATE MATERIAL

SOLUTION
42
...

GATE 2001
create table geq
(

Ib

integer

not

null

ub

integer

not

null

key

primary

1b
foreign key (ub) references geq on delete
cascade ) Which of the following is possible if
a tuple (x,y) is deleted?
(a) A tuple (z,w) with z > y is deleted
(b) A tuple (z,w) with z > x is deleted
(c) A tuple (z,w) with w < x is deleted
(d) The deletion of (x,y) is prohibited
SOLUTION

IT DEPARTMENT

GATE MATERIAL

i

IT DEPARTMENT

GATE MATERIAL



---