1. What is true about the indices defined on a given table in a database system?

a. Indices can be used to speed up join operations.

b. Index has to be stored separately from the table and hence increase
the space overhead.
c. Index reduce the space it takes to store the entire relation.
d. Index speeds up the data access in a given table if properly defined on
the corresponding attribute.

If answer is true the options are A,B,Dif it was false the option is C

2. Which is true about the B+-tree from the following options?

a. The height of the B+-tree is directly related to the query cost.
b. The B+-tree adds a space overhead to the database.
c. The leaf nodes have records of the search key that you are looking for.
d. The leaf nodes have pointers to the records that you are looking for.
If answer is true the options are C,D if it was false the option is A
3. What is false about a bucket in hash indexing or hash file organization?
a• Bucket contains records itself in hash file organization.
b• In hash indexing, the size of the bucket is not a problem as it is normally a
disk block. c• If there are many duplicate keys, the corresponding bucket may
overflow.
d• Bucket contains pointer to the record in hash indexing.
If answer is true the options are D if it was false the option is B
4. What is true about B+-tree index vs B+-tree file organization?
a• Index contains pointer to the table records while the file organization
keeps the records itself at the leaf nodes.
b. Insertion and deletion are handled differently in index than in file
organization.
c• The number of entries in the leaf node of a file organization is the same as
internal nodes.
d• B+-tree index and B+-tree file organization refer to the same thing and it
is just a two different name given for the same thing.
If answer is true, the options are A if it was false the option is D
5. What is true about the indices defined on a given table in a database system?
a. index can be used to spend up join operations.
b Index has to be stored separately from the table and hence increases the
space overhead.
c. Index speeds up data access in a given table if properly defined on the
corresponding attribute.
d. index induce the soace it takes to store the entire selation.
If answer is true the options are C if it was false the option is B
6. What is false about transaction state transition?
a. A partially committed transaction may be aborted directly to prevent
data inconsistency
b. b• An active transaction can fail at any time
c. c• A failed transactions then be aborted.
 d. d• Partially committed transactions have executed all its operations
but waiting to be committed
if answer is true the options are D if it was false the option is B
7. What is the main goal of allowing concurrent execution of transactions?
a• To reduce the risk of the database being in an inconsistent state.
b• To reduce the risk of transaction failures.
c• To make sure that the ACID properties are preserved.
d. Maximize the utilization of computer resources such as CPU and Disk
Option D
8. What is a blind write in a transaction schedule?
a• A write operation which was occurred before the current execution point
so that the current transaction can not see it.
b• A write operation performed by another transaction but not its own write
operation.
c. A direct write on a data item which is not read before by the same
transaction
d• None of the above
Option C
9. What is true about the clustering index?
a• In a clustering index. search keys form a cluster.
b. Clustering index can be either primary index or the secondary index of a
given table. c• Clustering index is created only for primary keys.
d. The order of search key in the sequential file is same as the order of the
search key in t
if it is true Option B if it is false option C
10. What action the database query engine must take in the following scenario?
There is a clustering index available for salary field and the user wants to find
the salaries that are less than $60,000.
a• Since the relation may not be sorted on the salary, the query engine has
to scan through the file using the linear file scan algorithm.
b• The information given is not sufficient enough to decide what to do for the
given query.
c. Do not use the index and use linear file scan algorithm and scan until you
find 60,000.
d• Use the index available to locate the salary that is 60,000 and start from
that point and scan until the beginning of the file.
Option D
11. What is true about an n order B+-tree nodes from the given options?
a. if the node id the root of the tree, it must have at least two children.
b. b• l the node is an internal node, it can have maximum of n number of
node pointers and n-1 number of search key values.
c. c• The size of the node does not determine the length of the search
path.
d. d• lf the node is an internal node, it can have a maximum n number of
record pointers and n-1 number of search key values.
If answer is true option is B if answer is false option is D
12. What is a conflict operation pair from two transactions those access two data
items A and B.
• Read(A) and Write (B)
• Write(B) and Read(A)
• Write(A) and Write(B)
• Read(A) and Write(A)
Option is C
13. What option is not useful in reducing the number of over flow buckets in hash
ind
a• Select a search key which has no duplicate records in the table.
[Link] the sufficient number of buckets in advance by examining the
dataset and creat information.
c. Use a dynamic hashing technique.
d• Carefully selecting the hash function based on the data statistics of the
records in the table.
option is not useful A option is useful D.
14. What is false about the linear file scan algorithm in searching for records in a
relation.
a• The availability of an index on the search key is not relevant.
b• The order of the records in the file is not important.
c. The selection conditions are not important.
d• The file has to be sorted based on the search key in ascending or
descending order.
If it is false the option is D if it is true option is
15. What will not improve the performance of a query (will not reduce the cost)?
a• None of the above.
b. Apply the selection predicates early so that the join operation is performed
on smaller relations.
c• Reducer the number of attributes that are not related to the query by
using the projection on each relation.
d• Apply the most restrictive operations first and then apply other larger
operations on the resulting relations.
What will not improve option A What will improve B,C
16. Which statement is true in the context of transaction locking (concurrency
control)
a• One data item can have multiple exclusive locks
b. Any other transaction that is requesting a shared lock is granted even if
there are number of other transactions have shared loks on that item but not
an exclusive lock.
c• A transaction requesting a shared lock is granted on an item that has a an
exclusive lock from another transaction
d• One data item can have only one lock regardless of whether it is shared or
exclusive lock
If it is true option is A if it is false option is C
17. What is the main goal of allowing concurrent execution of transactions?
a. To reduce the risk of transaction falures
 b. b• Maxamize the unization of computer resources such as CPU and
Disk
c. c. To make sure that the ACID properties are preserved
d. d. To reduce the rick of the database being in an in constent state
Option B
18. In some situations, linear file scan is more efficient than using an index
created on the search key for the record lookup. What is the condition that
must be satisfied in order to prove that the above claim is true? Consider the
following situation to support your decision, where you want to search for
instructors in Instructor relation. List of all instructors who's salary is less than
some amount (say X).
a. When the file on the disk is sorted on the salary in ascending order.
b. b• When the instructor relation is small.
c. c• when the instructor relation is very large,
Option A
19. One possible option if the created index is not fit in the main memory is to
[Link] a sparse index on the index that is created on the search key of the
relation which is on the magnetic disk.
b• Divide the index created on the search key of the relation and load it into
the main memory block by block.
c. delete the index created as there is no enough memory in the system to
load the index to memory. This is because, if the index can not fit in the
memory it is not usable.
d• Create a secondary index for the relation.
Option B
20. This question is based on dense and sparse indices. What is true about these
two methods of indexing?
a. When the size (number of entries in the index) of the sparse index is
increasing the access time is decreasing.
b. in a dense index, you initially locate the starting record pointed by the
index entry and sequentially search through the file using pointers until you
find the required record in the [Link]
c• For every search key, there is an index entry in a sparse index.
d• In a dense index, deletion and insertion has less space and maintenance
overhead.
If it is true B,D if it is false the option is A
21.