Database Design Using Normalization Questions and Answers

Answer: B

Correct answer: False

Creating the database comes last. The first step is to examine what you have been given - count the rows, look at the columns and the data in them - then work out the dependencies and keys, then test the referential integrity constraints. Building the new database before understanding the old data means building the old problems into it.

Answer: B

Correct answer: False

A surrogate key is an artificial identifier - typically a system-generated integer - with no relationship to the data it identifies, which is exactly the point: it never changes, it is short, and it is guaranteed unique. The cost is that it means nothing to a user, so a meaningful candidate key usually has to be kept alongside it and given a unique constraint.

Answer: B

Correct answer: second step.

Determining functional dependencies, primary keys and foreign keys all belong to the second step, in which the structure implied by the data is worked out. The first step is simply to see what is there - the row count and the columns - and the third is to test whether the referential integrity constraints implied by the foreign keys actually hold.

Answer: A

Correct answer: True

COUNT(*) counts the rows in a table or in each group of a grouped query, including rows containing nulls. It is the first thing to run on an acquired table, since the row count establishes the scale of what you are dealing with before any structural inference is attempted.

Answer: A

Correct answer: True

Because a read-only database is never updated, modification anomalies cannot occur, and the chief reason for normalizing disappears. What remains is the cost of joins, so the priorities invert: denormalized structures that answer queries in fewer joins are preferred, and the design is judged on retrieval performance rather than on update integrity.

Answer: C

Correct answer: third step.

The assessment proceeds in order: first count the rows and examine the columns to see what is there, then determine the functional dependencies, primary keys and foreign keys, then assess whether the referential integrity constraints those foreign keys imply are actually satisfied by the data. The check comes third because it cannot be made until the foreign keys have been identified.

Answer: D

Correct answer: BCNF.

Boyce-Codd Normal Form requires every determinant to be a candidate key, and a table satisfying it is free of the anomalies that arise from functional dependencies. Third normal form leaves some cases unhandled, which is why BCNF rather than 3NF is the practical target. Beyond it, 4NF deals with multivalued dependencies, which are a separate matter.

Answer: A

Correct answer: True

The tables you are given were designed by someone else, often for a different purpose, and may carry every problem normalization exists to prevent. Understanding their dependencies and deciding what to normalize is part of building a sound database from them - and even where you choose to leave a table denormalized, that should be a decision rather than an oversight.

Answer: D

Correct answer: disadvantage

Normalization decomposes one relation into several linked by foreign keys, and every foreign key brings a referential integrity constraint that must be assessed and then enforced. That work would not exist without the decomposition, so it counts as a cost of normalizing - the second of the two standard disadvantages, alongside the more complicated SQL that the extra tables require.

Answer: B

Correct answer: False

Normalization removes modification anomalies at the cost of more tables, more joins and more complex SQL, and where anomalies cannot arise the cost buys nothing. A read-only database is the standard case, and reporting and warehouse designs are deliberately denormalized. BCNF is the usual target for an updateable database, not a universal rule.