Physical Database Design Questions and Answers

Answer: A

Correct answer: CHAR

CHAR is fixed width and pads every value to the declared length, which is exactly right for data that genuinely has one length - a two-character state code, a fixed-format reference. For anything varying, the padding is waste, and VARCHAR2 is the correct choice instead.

Answer: A

Correct answer: True

A hashing algorithm computes an address directly from the key value, so a record is found in one access with no index to traverse. The price is that the records are then in no useful order, so sequential processing by key becomes expensive, and that collisions must be handled when two keys hash to the same address.

Answer: B

Correct answer: All fields are stored in two relations.

A many-to-many relationship denormalises into the associative relation and one of the participants, giving two relations rather than three. It cannot be collapsed into one, because doing so would repeat every attribute of one participant for each of its associations - which is the redundancy normalisation exists to prevent.

Answer: B

Correct answer: The number of columns

Block size decisions turn on how much data a read brings in and how many rows share a block, so row length, block contention and random access speed all bear on it. The number of columns matters only through its effect on the row length, and so is not a separate factor.

Answer: A

Correct answer: VARCHAR2

VARCHAR2 stores only the characters actually supplied, so a column declared long enough for the worst case costs nothing for the shorter values. CHAR pads every value to the full declared width, which wastes space and can complicate comparison when the data varies in length.

Answer: B

Correct answer: the number of physical records per page.

The blocking factor is how many records fit in one block or page, and it follows from the record length and the block size. It governs how many records one read brings in, which is why sequential processing is faster with a high blocking factor and why block size is a physical design decision.

Answer: A

Correct answer: True

Clustering places records that are read together on the same page, so one physical read brings in all of them, and denormalisation removes the join that would otherwise have been needed. Both work best on small records, since more of them fit in a block and the saving per read is correspondingly greater.

Answer: A

Correct answer: True

Where a record cannot span a page, any space left at the end of a page after the last whole record is wasted. Splitting the relation into several shorter physical records lets more of them fit, so less is left over. It is a genuine trade - the retrieval now needs more than one record - and it is worth making only when space is the binding constraint.

Answer: B

Correct answer: False

Distributing the columns of a table is vertical partitioning. Horizontal partitioning distributes the rows, typically by a value such as region or date, so each partition holds complete records for part of the population. The statement has given the definition of one under the name of the other.

Answer: A

Correct answer: All fields are stored in one relation.

In a one-to-one relationship each row on either side matches at most one on the other, so the two relations can be merged with no duplication of any value. That is why this is the safest case for denormalisation - the join is removed and nothing is repeated in exchange.