Physical Database Design Questions and Answers

Q: In a B+ tree, the requirement of at least half-full (50%) node occupancy is relaxed for which one of the following cases?

The requirement is relaxed for only the root node.A B+ tree of order n requires every node except the root to be at least half full. The root is exempt because a tree that has just been created, or that has shrunk after deletions, may hold very few keys: the root of a non-leaf tree needs only two children, and a root that is a leaf may hold a single key.All leaf nodes and all internal nodes other than the root must meet the half-full rule, which is what keeps the tree balanced and its height logarithmic. Nothing special applies to the leftmost leaf.

Q: B+ Trees are considered BALANCED because

B+ trees are balanced because the lengths of the paths from the root to all leaf nodes are all equal.A B+ tree grows and shrinks only at the root: when a node overflows it splits and pushes a key up, and when the root splits the tree gains one level everywhere at once. So every leaf is always at the same depth, which guarantees \(O(\log n)\) search, insertion and deletion.A difference of at most one in path length is the balance condition of an AVL tree, not a B+ tree. The numbers of children of sibling nodes, and of records in leaves, may differ by much more than one; they need only stay between the minimum (about half full) and the maximum fan-out.

Q: An index in a DBMS is said to be dense if an index entry appears for every search-key value in the indexed file. Otherwise it is called a sparse index. Consider the following two statements.S1: A hash index must be a dense indexS2: A \(B^{+}\) tree index can be a sparse indexWhich one of the following options is correct?

Both S1 and S2 are true.S1: A hash index scatters search keys across buckets by their hash values, with no ordering. A sparse index works only when the file is sorted on the key, so that an entry for one key also locates its neighbours. A hash index has no such order to exploit, so it must hold an entry for every search-key value: it must be dense.S2: A B+ tree built on the sort key of a clustered (sorted) file can store just one entry per block, the first key in each block, and still locate any record by scanning within the block. So a B+ tree index can be sparse. It is dense when it is a secondary index on an unsorted attribute.Since both statements hold, the options calling either one false are wrong.

Q: The ................... file organization provides very fast access to any arbitrary record of a file.

Very fast access to any record is given by hashed file organisation.A hash function computes the address (bucket) of a record directly from its key, so a record can be located in about one disk access on average, whatever the size of the file.A B-tree index needs a search down the tree, in a number of steps that grows logarithmically with the file. Sequential files must be scanned, and ordered files allow binary search but not direct access. All are slower for arbitrary single-record lookups than hashing.

Q: Select the correct option :

The correct statement is range queries are faster on a B\(^{+}\)-tree.In a B\(^{+}\)-tree all records (or record pointers) are in the leaves, which are linked in key order. A range query finds the first key and then follows the leaf links, without returning up the tree.The height of a B\(^{+}\)-tree does depend on the number of records, growing logarithmically. Both structures can be used for primary or secondary indexes, and both are designed for disk-based storage, so the other statements are false.

Q: Which of the following is true about "indexing" in databases ?

The true statement is Indexing improves the speed of data retrieval.An index, usually a B-tree, lets the database find rows matching a condition without scanning the whole table, much as a book's index saves reading every page.Indexes take extra storage and slow down inserts and updates, so they do not reduce storage. They do not make queries more complex, and they do nothing to stop SQL injection, which needs parameterised queries.

Q: 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 block are 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 ?

The best degree is 43.A node with n pointers holds n - 1 keys, and must fit in one 512-byte block:\[ \begin{aligned} 4n + 8(n - 1) &\le 512 \\ 12n &\le 520 \\ n &\le 43.3 \end{aligned} \]So n = 43. With 44 pointers the node would need \(4 \times 44 + 8 \times 43 = 520\) bytes, more than a block. 42 wastes space, and 16 does not follow from the sizes.In a B+ tree the leaf and internal nodes may hold slightly different numbers of entries, but the question asks for the pointer count of a node that fits one block.

Q: A file is organized so that the ordering of data records is the same as or close to the ordering of data entries in some index. Then that index is called

The index is called Clustered.A clustered index is one whose order matches, or nearly matches, the physical order of the data records in the file. Range searches on it are efficient because matching records sit together on the disk.An unclustered index has a different order from the data. Dense and sparse describe whether there is an index entry for every record or only for some, not how the order compares.

Q: Which technique improves read performance in databases at the cost of redundancy ?

The technique is Denormalization.Denormalization deliberately adds redundant data, for example copying a customer's name into an orders table or storing precomputed totals, so that common queries need fewer joins. Reads become faster at the cost of duplicated data and more work to keep the copies consistent on updates.Normalization does the opposite, removing redundancy. Indexing speeds reads with extra index structures rather than duplicated table data, and partitioning splits a table into pieces; neither is defined by trading redundancy for read speed.

Q: In database indexing, which scenario benefits MOST from using a B+ Tree ?

B+ trees benefit Range queries most.In a B+ tree all keys are stored in the leaf nodes in sorted order, and the leaves are linked to one another. A range query finds the first key by descending the tree once, then reads the rest by following the leaf links, without returning to the root.A hash index serves random point lookups as well or better. Small datasets gain little from any index, and write-heavy workloads suffer from the cost of keeping the tree balanced on every insert and delete.

In a B+ tree, the requirement of at least half-full (50%) node occupancy is relaxed for which one of the following cases?

A.Only the root node
B.All leaf nodes
C.All internal nodes
D.Only the leftmost leaf node

Exam Year(s)

GATE CS - 2024,

Answer: A

The requirement is relaxed for only the root node.

A B+ tree of order n requires every node except the root to be at least half full. The root is exempt because a tree that has just been created, or that has shrunk after deletions, may hold very few keys: the root of a non-leaf tree needs only two children, and a root that is a leaf may hold a single key.

All leaf nodes and all internal nodes other than the root must meet the half-full rule, which is what keeps the tree balanced and its height logarithmic. Nothing special applies to the leftmost leaf.

Discuss in Forum

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.

Exam Year(s)

GATE CS - 2016,

Answer: A

B+ trees are balanced because the lengths of the paths from the root to all leaf nodes are all equal.

A B+ tree grows and shrinks only at the root: when a node overflows it splits and pushes a key up, and when the root splits the tree gains one level everywhere at once. So every leaf is always at the same depth, which guarantees \(O(\log n)\) search, insertion and deletion.

A difference of at most one in path length is the balance condition of an AVL tree, not a B+ tree. The numbers of children of sibling nodes, and of records in leaves, may differ by much more than one; they need only stay between the minimum (about half full) and the maximum fan-out.

Discuss in Forum

An index in a DBMS is said to be dense if an index entry appears for every search-key value in the indexed file. Otherwise it is called a sparse index. Consider the following two statements.

S1: A hash index must be a dense index
S2: A \(B^{+}\) tree index can be a sparse index

Which one of the following options is correct?

A.Both S1 and S2 are true
B.Both S1 and S2 are false
C.S1 is true and S2 is false
D.S1 is false and S2 is true

Exam Year(s)

GATE CS - 2026,

Answer: A

Both S1 and S2 are true.

S1: A hash index scatters search keys across buckets by their hash values, with no ordering. A sparse index works only when the file is sorted on the key, so that an entry for one key also locates its neighbours. A hash index has no such order to exploit, so it must hold an entry for every search-key value: it must be dense.

S2: A B⁺ tree built on the sort key of a clustered (sorted) file can store just one entry per block, the first key in each block, and still locate any record by scanning within the block. So a B⁺ tree index can be sparse. It is dense when it is a secondary index on an unsorted attribute.

Since both statements hold, the options calling either one false are wrong.

Discuss in Forum

The ................... file organization provides very fast access to any arbitrary record of a file.

A.B-tree
B.Sequential
C.Ordered
D.Hashed

Exam Year(s)

MH SET - 2025, SET Exam - 2025,

Answer: D

Very fast access to any record is given by hashed file organisation.

A hash function computes the address (bucket) of a record directly from its key, so a record can be located in about one disk access on average, whatever the size of the file.

A B-tree index needs a search down the tree, in a number of steps that grows logarithmically with the file. Sequential files must be scanned, and ordered files allow binary search but not direct access. All are slower for arbitrary single-record lookups than hashing.

Discuss in Forum

Select the correct option :

A.The height of a B\(^{+}\)-tree is independent of the number of records
B.B-trees are used in primary indexes and B\(^{+}\)-trees are used in secondary indexes
C.Range queries are faster on B\(^{+}\)-tree
D.B\(^{+}\)-trees are used for storing data in main memory and B-trees are used for storing data on disk

Exam Year(s)

MH SET - 2025, SET Exam - 2025,

Answer: C

The correct statement is range queries are faster on a B\(^{+}\)-tree.

In a B\(^{+}\)-tree all records (or record pointers) are in the leaves, which are linked in key order. A range query finds the first key and then follows the leaf links, without returning up the tree.

The height of a B\(^{+}\)-tree does depend on the number of records, growing logarithmically. Both structures can be used for primary or secondary indexes, and both are designed for disk-based storage, so the other statements are false.

Discuss in Forum

Which of the following is true about "indexing" in databases ?

A.Indexing improves the speed of data retrieval.
B.Indexing reduces the storage requirements of a database.
C.Indexing increases the complexity of queries.
D.Indexing prevents SQL injection attacks.

Exam Year(s)

Tripura Public Service Commission - 2025,

Answer: A

The true statement is Indexing improves the speed of data retrieval.

An index, usually a B-tree, lets the database find rows matching a condition without scanning the whole table, much as a book's index saves reading every page.

Indexes take extra storage and slow down inserts and updates, so they do not reduce storage. They do not make queries more complex, and they do nothing to stop SQL injection, which needs parameterised queries.

Discuss in Forum

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 block are 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

Exam Year(s)

Tripura Public Service Commission - 2025,

Answer: C

The best degree is 43.

A node with n pointers holds n - 1 keys, and must fit in one 512-byte block:

\[ \begin{aligned} 4n + 8(n - 1) &\le 512 \\ 12n &\le 520 \\ n &\le 43.3 \end{aligned} \]

So n = 43. With 44 pointers the node would need \(4 \times 44 + 8 \times 43 = 520\) bytes, more than a block. 42 wastes space, and 16 does not follow from the sizes.

In a B+ tree the leaf and internal nodes may hold slightly different numbers of entries, but the question asks for the pointer count of a node that fits one block.

Discuss in Forum

A file is organized so that the ordering of data records is the same as or close to the ordering of data entries in some index. Then that index is called

A.Dense
B.Sparse
C.Clustered
D.Unclustered

Exam Year(s)

Tripura Public Service Commission - 2025,

Answer: C

The index is called Clustered.

A clustered index is one whose order matches, or nearly matches, the physical order of the data records in the file. Range searches on it are efficient because matching records sit together on the disk.

An unclustered index has a different order from the data. Dense and sparse describe whether there is an index entry for every record or only for some, not how the order compares.

Discuss in Forum

Which technique improves read performance in databases at the cost of redundancy ?

A.Indexing
B.Normalization
C.Denormalization
D.Partitioning

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: C

The technique is Denormalization.

Denormalization deliberately adds redundant data, for example copying a customer's name into an orders table or storing precomputed totals, so that common queries need fewer joins. Reads become faster at the cost of duplicated data and more work to keep the copies consistent on updates.

Normalization does the opposite, removing redundancy. Indexing speeds reads with extra index structures rather than duplicated table data, and partitioning splits a table into pieces; neither is defined by trading redundancy for read speed.

Discuss in Forum

10.

In database indexing, which scenario benefits MOST from using a B+ Tree ?

A.Random access only
B.Range queries
C.Small datasets
D.Write-heavy workloads

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: B

B+ trees benefit Range queries most.

In a B+ tree all keys are stored in the leaf nodes in sorted order, and the leaves are linked to one another. A range query finds the first key by descending the tree once, then reads the rest by following the leaf links, without returning to the root.

A hash index serves random point lookups as well or better. Small datasets gain little from any index, and write-heavy workloads suffer from the cost of keeping the tree balanced on every insert and delete.

Discuss in Forum

Loading…

Topics

Other Subjects

Tags: Physical Database Design Questions and Answers || Physical Database Design MCQ Questions and Answers || Physical Database Design GK Questions and Answers || Physical Database Design GK MCQ Questions || Physical Database Design Multiple Choice Questions and Answers || Physical Database Design GK || GK on Physical Database Design || Database Questions and Answers || Database MCQ Questions and Answers || Database GK Questions and Answers || GK on Database

Physical Database Design Questions and Answers

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications