Database Processing, 15e (Kroenke)
Chapter 8: Database Redesign
1) Database redesign is rarely needed because databases are usually built correctly the first time.
2) In a real sense, information systems and organizations do not just influence each other, but
rather they create each other.
3) A continuous circular process of changes in user behaviors and change in the information
systems they use is a natural outcome of information system use.
4) The continuous circular process of changes is known as the Systems Development Life Cycle
(SDLC).
5) Database redesign is equally difficult regardless of whether or not the database has data in it.
6) In the database redesign process, it is often useful to test whether certain conditions or
assumptions are valid before proceeding with the redesign.
7) In the database redesign process, two SQL tools are useful for testing whether or not certain
conditions or assumptions are valid: uncorrelated subqueries and EXISTS/NOT EXISTS.
8) A correlated subquery looks very different from a noncorrelated subquery.
9) Correlated subqueries can be used to verify functional dependencies.
10) In a correlated subquery, the same table is used in the upper and lower SELECT statements.
11) In the SQL statement:
SELECT S1.CustName, S1.SalesRepNo FROM SALES AS S1;
the “S1” is called an alias.
12) In a correlated subquery, the DBMS can run the lower SELECT statement by itself and then
send the results to the upper SELECT statement.
13) In a correlated subquery, the DBMS must run the lower SELECT statement as a process that
is nested within the upper SELECT statement.
14) There is a common trap in writing a correlated subquery, which will cause no rows to ever be
displayed in the results.
15) Although correlated subqueries are useful in database redesign, they cannot be used to verify
functional dependencies.
16) When using queries with EXISTS and NOT EXISTS, the processing of the associated
SELECT statements must be nested.
17) A doubly nested set of NOT EXISTS SELECT statements can be used to find rows that meet
some specified condition for every row in a table.
18) The use of a doubly nested set of NOT EXISTS SELECT statements is a famous pattern in
SQL use.
19) EXISTS and NOT EXISTS are actually just another form of correlated subqueries.
20) Because EXISTS and NOT EXISTS are forms of correlated subqueries, the processing of the
associated SELECT statements must be nested.
21) The EXISTS keyword will be true if any row in the subquery meets the condition.
22) The NOT EXISTS keyword will be true if any row in the subquery fails to meet the
condition.
23) A doubly nested set of EXISTS SELECT statements can be used to find rows that meet some
specified condition for every row in a table.
24) When using a doubly nested set of NOT EXISTS SELECT statements, a row that does not
match any row matches every row.
25) The use of a doubly nested set of NOT EXISTS SELECT statements is so rare that even if
you are a professional database developer you will probably never see it used.
26) There are no good SQL commands that can be used to change table names.
27) The process of reading an actual database schema and producing a data model from that
schema is called reverse engineering.
28) The data model produced by reverse engineering is a true conceptual schema.
29) The data model produced by reverse engineering may include some entities that should not
appear in the data model.
30) The design produced by reverse engineering may be described as a table-relationship
diagram.
31) The authors refer to the data model produced by reverse engineering as the RE data model.
7
32) Because of the need to know the functional dependencies in a database, it is a good idea to
create a dependency graph.
33) Dependency graphs are graphical displays like bar charts.
34) Typically, there are at least four different copies of the database schema used in the redesign
process.
35) A means must be created to recover all test databases to their original state during the
redesign testing process.
36) Even if an organization has a very large database, it will be possible to make a complete
backup copy of the operational database prior to making structural changes.
37) The RENAME TABLENAME command can be used to change table names.
38) SQL Server 2017 contains a system-stored procedure named sp_rename that can be used to
change table names.
39) Changing table names is complicated by the fact that constraints and triggers are often
associated with the table and will also need to be changed.
40) In order to minimize the need to change table names, some organizations have a policy that it
is better to create a new table, map the data over to the new table and then delete the old table.
41) To add a NULL column to a table, we simply use the MODIFY TABLE statement.
42) If a DEFAULT constraint is included when a new column is added to a table, the default
value is only applied to new rows and not to the existing rows at the time the new column is
added.
43) To add a NOT NULL column to a table, we first add a NULL column, then we insert values
into every row, and finally we change the NULL constraint to NOT NULL.
44) To drop a nonkey column from a table, no preliminary steps are needed, and we can simply
use the ALTER TABLE DROP COLUMN statement.
45) To drop a foreign key column from a table, no preliminary steps are needed, and we can
simply use the ALTER TABLE DROP COLUMN statement.
46) To drop a primary key column from a table the primary key constraint must first be dropped,
but this does not require that related foreign keys based on the column be dropped.
47) To drop a constraint, no preliminary steps are needed and we can simply use the ALTER
TABLE DROP CONSTRAINT statement.
48) Converting date, money, or other more specific data types to char or varchar will usually
succeed.
49) To change the minimum cardinality on the parent side from zero to one, the foreign key,
which would have been NULL, must be changed to NOT NULL.
50) Depending on the DBMS, when changing the minimum cardinality on the parent side from
zero to one, the foreign key constraint that defines the relationship may have to be dropped
before the change is made and re-added afterwards.
51) There are several difficulties with increasing cardinalities from 1:1 to 1:N, including
preserving the existing relationships.
52) When increasing cardinalities from 1:N to N:M, we basically create a new intersection table,
fill it with data and drop the old foreign key.
53) When decreasing maximum cardinalities, there will always be data loss.
54) Adding new tables and relationships to a database is difficult.
55) Deleting tables and relationships is basically a matter of dropping foreign key constraints and
then dropping the tables.
56) Database redesign is fairly easy when ________.
A) information systems and organizations influence each other
B) the design was done correctly the first time
C) there is no data in the database
D) good backups of the database are available
57) Which of the following is not a possible step in the database redesign process?
A) Checking whether certain conditions or assumptions about the data are valid
B) Reverse engineering the data model
C) Revising user account name policies
D) Maintaining backups of the existing database
58) In the database redesign process, before proceeding with the redesign it is often useful to
________.
A) check whether certain conditions or assumptions about the data are valid
B) find out why the design was not done properly the first time
C) stop information systems and users from influencing each other
D) set standards for user behavior
59) In the database redesign process, an SQL tool that is useful for testing whether or not certain
conditions or assumptions are valid is ________.
A) UPDATE
B) DROP TABLE
C) CREATE TRIGGER
D) NOT EXISTS
60) In a correlated subquery of a database that has tables TableOne and TableTwo, if table
TableOne is used in the upper SELECT statement, then which table is used in the lower
SELECT statement?
A) TableOne
B) TableTwo
C) Both TableOne and TableTwo
D) Either TableOne or TableTwo
61) In the SQL statements
SELECT C1.CustName, C1.SalesRepNo
FROM CUSTOMER as C1;
the “C1” is called a(n) ________.
A) term
B) alias
C) convention
D) label