Database Redesign Questions and Answers

Answer: A

Correct answer: True

That is the definition. Reverse engineering reads an existing schema - tables, columns, keys and constraints - and produces a data model from it, working backwards from the implementation to the design. It is the first step whenever a database must be understood before it can be changed and its documentation is missing or out of date.

Answer: A

Correct answer: True

Some organisations require applications to address the database only through views, never by the true table names, so that tables can be restructured without touching application code - the view is rewritten to present the old shape from the new tables. It is a discipline that costs something to maintain and buys a great deal of freedom to redesign later.

Answer: A

Correct answer: any row in the subquery meets the condition only.

EXISTS is a test of emptiness: it returns true as soon as the subquery produces one row, and false only if the subquery produces none. Because it stops at the first match and never examines the values returned, it is usually more efficient than the equivalent IN or count, and it is written as SELECT * or SELECT 1 for exactly that reason.

Answer: C

Correct answer: database has no data.

With no data present, changing the structure is simply a matter of issuing the DDL: there is nothing to convert, nothing to lose and no constraint that existing rows might violate. Every real difficulty in redesign comes from the data - values that will not fit the new type, rows that break a new constraint, relationships whose cardinality the existing rows do not satisfy. Size and quality of design affect the work, but neither removes it.

Answer: C

Correct answer: ALTER TABLE TableName DROP COLUMN ColumnName

Structural changes to an existing table are made with ALTER TABLE, not MODIFY TABLE, which is not an SQL command at all. For removing a column the keyword COLUMN is required. Note the asymmetry with adding a column, where most implementations accept ADD without it - an inconsistency in the language rather than in the question.

Answer: D

Correct answer: None of the above is correct.

What reverse engineering produces is not a clean model of any of the three kinds. It reflects the schema as built, including implementation compromises, denormalisations and physical artefacts, and it lacks the business meaning a conceptual model would carry. The result is best described as a quasi-logical model requiring interpretation and correction by someone who understands the application.

Answer: A

Correct answer: True

A column that permits nulls can be added to a populated table without difficulty: the existing rows simply receive null, no constraint is violated and no data has to be supplied. It is adding a NOT NULL column that is awkward, since every existing row needs a value - which requires a default, or a three-step process of adding it nullable, populating it and then imposing the constraint.

Answer: D

Correct answer: Eliminates duplicate rows from the result

DISTINCT removes repeated rows so that each combination of the selected columns appears once. Sorting is the work of ORDER BY and limiting the row count that of LIMIT or its equivalent.

Answer: C

Correct answer: drop the foreign key constraint.

The constraint is what prevents the column being removed, so it must be dropped first - a single ALTER TABLE ... DROP CONSTRAINT, after which the column itself can be dropped. Dropping the primary key of the referenced table or the whole table containing the foreign key would both be far more destructive than the change requires.

Answer: A

Correct answer: Changing a column data type from char to date

Widening a type is safe; narrowing or reinterpreting it depends on what the existing rows contain. Numeric to char always succeeds, since any number has a character representation. Char to date succeeds only if every value in the column can be parsed as a date, and a single row containing a blank, a note or a differently formatted string will cause the whole statement to fail.