Database Redesign Questions and Answers

Answer: C

Correct answer: Three

Three copies are used: the operational schema, which is left untouched; a test copy on which the changes and their SQL are proved; and a copy carrying the proposed new design. Working with fewer means either testing against live data or having nowhere to develop the new structure, and the three-copy arrangement is what allows the change to be rehearsed completely before it is applied.

Answer: D

Correct answer: All of the above should be done.

All three precautions are necessary and none substitutes for another. The current structure and contents must be understood, or a change will have consequences nobody anticipated; the statements must be tested on a test database, since a DDL error against live data may be irreversible; and a complete backup must exist, because some of what can go wrong cannot be undone by any other means.

Answer: C

Correct answer: ALTER TABLE STUDENT ADD CONSTRAINT GradeCheck CHECK (Grade > 0);

A named constraint is added with ADD CONSTRAINT to give it a name, followed by the constraint type - CHECK, UNIQUE, PRIMARY KEY or FOREIGN KEY - and its definition. Omitting the CHECK keyword leaves the statement without a constraint type and it will not parse. ALTER CONSTRAINT would modify one that already exists. Naming the constraint matters, since an unnamed one receives a generated name that is awkward to drop later.

Answer: D

Correct answer: None of the above is correct.

SQL-92 contains no command for renaming a table. Individual products supply their own - RENAME TABLE in MySQL, sp_rename in SQL Server, ALTER TABLE ... RENAME TO in PostgreSQL and Oracle - but none of these is standard, so a rename cannot be written portably. The standard-conforming alternative is to create the new table, copy the data and drop the old one.

Answer: B

Correct answer: False

Intersection tables exist physically in the schema, so reverse engineering reads them and includes them like any other table. What it cannot do is recognise that they are intersection tables representing a many-to-many relationship rather than entities in their own right - that interpretation requires a person, and it is one of the reasons the reverse-engineered model needs correction.

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: True

EXISTS returns true as soon as the subquery yields a single row. It examines only whether rows exist, not what they contain, which is why it can stop at the first match and why the select list inside it is conventionally written as SELECT * or SELECT 1.

Answer: A

Correct answer: True

The doubly nested NOT EXISTS is the standard SQL expression of relational division: there is no member of the required set for which no matching row exists. It is how one finds, for example, the students who have taken every course in a list, and it is used because SQL has no division operator of its own.

Answer: A

Correct answer: True

A dependency graph represents each database structure as a node and each dependency as an arc, so that everything relying on a given table, column or view can be read off directly. It is the tool that shows, before a change is made, which views, constraints, procedures and queries would be affected by it.

Answer: A

Correct answer: True

A correlated subquery references a column from the outer query, so it cannot be evaluated once on its own; the database must re-execute it for each candidate row of the outer query, which is what nested processing means. That is also why it is generally the more expensive form and why an equivalent join is often preferred where one exists.