Physical Database Design Questions and Answers

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.