Database Redesign Questions and Answers

Answer: A

Correct answer: True

Getting a database right the first time requires knowing the requirements completely and correctly at the outset, which almost never happens, and anticipating changes that have not yet occurred. Requirements are discovered as the system is used, and the organisation changes around it, so some redesign is the normal course of a database’s life rather than evidence of failure.

Answer: D

Correct answer: They are very similar to a regular subquery.

This is the false statement. A correlated subquery differs fundamentally: it references a column of the outer query, so it cannot be evaluated independently and must be re-run for every candidate row. The other three are true - EXISTS and NOT EXISTS are the usual form, the processing is nested rather than bottom up, and the construct is exactly what is used to test a functional dependency in the data.

Answer: B

Correct answer: all rows in the subquery fail the condition.

NOT EXISTS is the exact negation of EXISTS: it is true when the subquery returns no rows at all, which is to say when every row fails the condition. It is the natural way to express “there is no ...”, and it handles nulls more predictably than NOT IN, which returns unknown if the subquery produces a single null.

Answer: B

Correct answer: False

That describes EXISTS. NOT EXISTS is its negation and is true only when the subquery returns no rows - that is, when every row fails the condition. Confusing the two inverts the result of the query, and since both are commonly written in the same statement the distinction has to be held clearly.

Answer: B

Correct answer: False

No database stays correct, because the organisation it serves does not stay still. New requirements, new regulations, new products and mergers all change what the data must represent, so redesign follows from changed needs rather than from original error. Building it well the first time makes redesign easier, not unnecessary.

Answer: B

Correct answer: Double use of NOT EXISTS

A single NOT EXISTS finds rows for which no matching row exists, which is not the same as rows failing to match a condition across a set. The classic relational division problem - find the rows that match every member of a set - is expressed as a doubly nested NOT EXISTS: there is no member of the set for which there is no matching row. It is awkward to read and is the standard idiom for the purpose.

Answer: A

Correct answer: True

A regular subquery is self-contained: it references nothing from the query above it, so it can be evaluated once, from the bottom up, and its result handed to the outer query. This independence is exactly what distinguishes it from a correlated subquery and what makes it the cheaper of the two.

Answer: B

Correct answer: skeptical

Automated tools can generate the DDL for a structural change, but they cannot know what the data means, which values will fail a new constraint, or what the applications expect - and a mistake against live data may be irreversible. Practitioners therefore use such tools to draft the change and then review, test and often rewrite it by hand rather than trusting the output.

Answer: A

Correct answer: dependency graph.

A dependency graph shows the database’s structures as nodes and their dependencies as arcs, so that before changing anything the designer can see what refers to it - the views, constraints, triggers, stored procedures and application queries that would break. It is the tool that turns “what does this column touch?” from a search into a reading.

Answer: B

Correct answer: False

The reverse is true: an empty database is the easy case, since the structure can be changed by issuing the DDL with nothing to convert, nothing to lose and no existing rows to violate a new constraint. Every real difficulty in redesign comes from the data already present.