Physical Database Design Questions and Answers

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.

Answer: A

Correct answer: True

A composite attribute is stored as one field per component - street, town and postcode rather than a single address field - whenever the parts will be used separately. Storing it whole is the alternative, and the choice turns on whether anything will ever need to search or sort on a part.

Answer: D

Correct answer: All of the above.

All three are integrity controls the DBMS itself can enforce: a default supplied when no value is given, a domain constraint limiting the permissible values, and a not-null constraint requiring one. Placing them in the database rather than in each application means every route to the data is checked identically.