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: A secondary key is which of the following?

Correct answer: Nonunique keyA secondary key identifies a set of rows rather than a single one - all customers in a city, all orders on a date - and is used to speed retrieval on a common search condition. It is a candidate for an index precisely because it is not unique, and it plays no part in identifying rows.

Q: Sequential retrieval on a primary key for sequential file storage has which of the following features?

Correct answer: Very fastSequential file storage keeps the rows in primary key order, so reading them in that order is simply reading the file from start to finish - no index lookups and no seeking. That is what the organisation is for, and it is the reason it survives for batch processing despite being poor at everything else.

Q: The fastest read/write time and most efficient data storage of any disk array type is:

Correct answer: RAID-0.RAID-0 stripes data across the disks with no redundancy at all, so every byte of capacity holds data and reads and writes are spread over all the spindles. That gives the best speed and the most efficient storage of any array type - and no protection whatever, since the failure of one disk loses the whole array.

Q: Some advantages of partitioning include: efficiency, security, and load balancing.

Correct answer: TrueAll three are among the recognised advantages, along with local optimisation and recovery. Efficiency comes from a query reading only the partition it needs; security from applying different controls to different partitions; and load balancing from placing partitions on separate devices so their accesses do not contend.

Q: Data-volume and frequency-of-use statistics are not critical inputs to the physical database design process.

Correct answer: FalseData volumes and access frequencies are among the most critical inputs to physical design: they decide which tables are large enough to warrant indexing, which queries run often enough to be worth optimising for, and where the storage and contention will fall. Without them the design decisions are guesses.

Q: Two basic constructs to link one piece of data with another piece of data: sequential storage and pointers.

Correct answer: TrueEvery physical structure reduces to one of the two. Sequential storage links records by placing them next to each other, so the next record is found by continuing to read. Pointers link them by holding an address, so the next record can be anywhere. Indexes, hashing and chains are all built from these two ideas.

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

A secondary key is which of the following?

A.Nonunique key
B.Primary key
C.Useful for denormalization decisions
D.Determines the tablespace required

Answer: A

Correct answer: Nonunique key

A secondary key identifies a set of rows rather than a single one - all customers in a city, all orders on a date - and is used to speed retrieval on a common search condition. It is a candidate for an index precisely because it is not unique, and it plays no part in identifying rows.

Discuss in Forum

Sequential retrieval on a primary key for sequential file storage has which of the following features?

A.Very fast
B.Moderately fast
C.Slow
D.Impractical

Answer: A

Correct answer: Very fast

Sequential file storage keeps the rows in primary key order, so reading them in that order is simply reading the file from start to finish - no index lookups and no seeking. That is what the organisation is for, and it is the reason it survives for batch processing despite being poor at everything else.

Discuss in Forum

The fastest read/write time and most efficient data storage of any disk array type is:

A.RAID-0.
B.RAID-1.
C.RAID-2.
D.RAID-3.

Answer: A

Correct answer: RAID-0.

RAID-0 stripes data across the disks with no redundancy at all, so every byte of capacity holds data and reads and writes are spread over all the spindles. That gives the best speed and the most efficient storage of any array type - and no protection whatever, since the failure of one disk loses the whole array.

Discuss in Forum

A rule of thumb for choosing indexes for a relational database includes which of the following?

A.Indexes are more useful on smaller tables.
B.Indexes are more useful for columns that do not appear frequently in the WHERE clause in queries.
C.Do not specify a unique index for the primary key of each table.
D.Be careful indexing attributes that have null values.

Answer: D

Correct answer: Be careful indexing attributes that have null values.

Nulls complicate an index, since how they are stored and whether they can be searched for varies between products, and a column with many of them may not benefit from indexing at all. The other three rules are each stated backwards: indexes help most on large tables, on columns that do appear in WHERE clauses, and a unique index on the primary key is essential rather than avoidable.

Discuss in Forum

Some advantages of partitioning include: efficiency, security, and load balancing.

A.True
B.False

Answer: B

Correct answer: True

All three are among the recognised advantages, along with local optimisation and recovery. Efficiency comes from a query reading only the partition it needs; security from applying different controls to different partitions; and load balancing from placing partitions on separate devices so their accesses do not contend.

Discuss in Forum

Data-volume and frequency-of-use statistics are not critical inputs to the physical database design process.

A.True
B.False

Answer: B

Correct answer: False

Data volumes and access frequencies are among the most critical inputs to physical design: they decide which tables are large enough to warrant indexing, which queries run often enough to be worth optimising for, and where the storage and contention will fall. Without them the design decisions are guesses.

Discuss in Forum

10.

Two basic constructs to link one piece of data with another piece of data: sequential storage and pointers.

A.True
B.False

Answer: A

Correct answer: True

Every physical structure reduces to one of the two. Sequential storage links records by placing them next to each other, so the next record is found by continuing to read. Pointers link them by holding an address, so the next record can be anywhere. Indexes, hashing and chains are all built from these two ideas.

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