Data Models into Database Designs Questions and Answers

Answer: D

Correct answer: surrogate key.

A surrogate key is an artificial identifier supplied by the DBMS - an AUTO_INCREMENT column or a sequence value - with no meaning outside the database. It is used as a primary key precisely because it has the properties an ideal key needs: short, numeric, unique and never changing.

Answer: A

Correct answer: True

SQL Server provides AFTER triggers, which fire once the statement has completed, and INSTEAD OF triggers, which replace it - the latter being how a view that is not otherwise updatable is made updatable. There is no BEFORE trigger, which is a difference from Oracle worth remembering when porting code between the two.

Answer: B

Correct answer: False

A composite of four text columns is long, not numeric, and liable to change whenever someone moves or a postcode is revised - and every foreign key referring to it would have to change with it. An ideal primary key is short, numeric and seldom changing, which is exactly why a surrogate key is used instead.

Answer: A

Correct answer: True

Foreign keys are the only mechanism the relational model provides for relationships. There are no pointers and no links; a row in one table is associated with a row in another solely because a value in the first matches a key value in the second, and referential integrity is what keeps that matching sound.

Answer: A

Correct answer: True

If the child is required, a parent cannot exist without one, so creating a parent alone would immediately violate the constraint. The two rows must be created together, which usually means within a single transaction. This is the mirror image of the required-parent case, where it is the child that cannot be created alone.

Answer: C

Correct answer: They have meaning to the user.

Having no meaning is the point of a surrogate key. Because it says nothing about the entity, nothing can happen to the entity that would require the key to change, which is what makes it stable. The other three properties - short, fixed and numeric - are the practical advantages that follow.

Answer: A

Correct answer: MUST COVER

The MUST COVER constraint requires every combination appearing in the binary relationship to appear in the ternary one, so the binary relationship acts as a list of permitted combinations that the ternary must include. MUST NOT is its complement, excluding combinations that appear in the binary relationship.

Answer: B

Correct answer: False

It is not arbitrary at all. The entity on the one side is the parent and the entity on the many side is the child, because the foreign key must go where a single value suffices - in the child. It is in a 1:1 relationship that the choice becomes arbitrary, there being no many side to force it.

Answer: A

Correct answer: True

A many-to-many relationship cannot be represented by a foreign key in either table, since a single column cannot hold an unknown number of references. The intersection table resolves it into two one-to-many relationships, holding one row for each pair, with the two foreign keys together forming its primary key.

Answer: C

Correct answer: Four

Each side of a relationship is either optional or mandatory, so with two sides there are \( 2 \times 2 = 4 \) combinations of minimum cardinality. The figure six belongs to a different question - the number of referential integrity actions to be considered, three on each side.