Database indexing, join, hashing, block size multiple choice questions

MCQ on various aspects in DBMS including database indexing, join techniques, hashing ideas, and disk properties with answers, RDBMS Multiple Choice Questions with Answers

Database Management Systems (DBMS) MCQ Quiz Questions with Answers

1. Adding indexes to a table would help in speeding-up the execution. Clustered indexes can perform better than non-clustered indexes. But, you can create more non-clustered indexes than the clustered index      . Why?

a) Because clustered indexes take more space than non-clustered indexes and the database servers has little space.

b) Because a relation can have only one clustered index, but many non-clustered indexes.

c) Because clustered indexes will slow down updates and the application has many updates.

d) None of the above

View Answer

Answer: (b) Because a relation can have only one clustered index, but many non-clustered indexes A clustered index defines the order in which data is physically stored in a table. Hence, only one clustered index is possible. Non-clustered indexes are created on other attributes and the values in that attributes need not be stored in some order in the source table.

2. To execute a query in a database, we have both logical and physical operators. Which of the following is physical operator?

a) Equi-join

b) Natural join

c) Merge join

d) Theta join

View Answer

Answer: (c) Merge join Logical operators describe the relational algebraic operation used to process a statement. In other words, logical operators describe conceptually what operation needs to be performed. Physical operators implement the operation described by logical operators.

3. How many blocks are on a disk with the following characteristics? Sector size of 512 bytes, 8 sectors per track, 8192(16384) tracks per surface, 4 double sided platter, and 4096 bytes per block.

a) 262144 blocks

b) 65536 blocks

c) 131072 blocks

d) None of the above

View Answer

Answer: (b) 65536 blocks Number of blocks for the disk with the given properties can be calculated as follows; 512 bytes * 8 sectors * 8192 tracks * 4 platters * 2 sides / 4096 bytes = 65536

4. How do we refer the query that is written under the WHERE clause or HAVING clause of another SQL query?

a) Query

b) Nested query

c) Sub-query

d) Secondary query

View Answer

Answer: (b) Nested query and (c) Sub-query A sub-query or nested query is a SELECT statement that is written within another SQL query. Example: SELECT * FROM student WHERE regno IN (SELECT regno FROM marks WHERE avg = 100); The query given inside the parenthesis is called sub-query

5. The hashing technique that allows a hash file either to expand or to shrink dynamically is __________.

a) Extendible hashing

b) Linear hashing

c) Non-linear hashing

d) External hashing

View Answer

Answer: (a) Extendible hashing Extendible hashing is a dynamically updateable disk-based index structure which implements a hashing scheme utilizing a directory. The dynamic aspects of extendible hashing are handled by two mechanisms; Insertion and bucket splitting – to handle bucket overflow, the bucket is split into two. Deletion and bucket combining – to handle empty buckets, they can be combined with other buckets.

*************************

Related posts:

• Go to Multiple Choice Questions in DBMS home

• Go to Normalization - MCQs page

• Go to General Mixed Quiz in all topics of DBMS page

• Go to Advanced DBMS Concepts page

Quiz questions with answers on DBMS hashing

Solved quiz questions on functional dependencies and normalization process of database management systems

MCQ with answers on query processing and disk block access

logical vs physical join operators in RDBMS

indexing concepts in database management systems and how does it improve database performance