DB ch.8

Database redesign is fairly easy when ________.

• information systems and organizations influence each other

• the design was done correctly the first time

• there is no data in the database

• good backups of the database are available

Which of the following is not a possible step in the database redesign process?

• Checking whether certain conditions or assumptions about the data are valid

• Reverse engineering the data model

• Revising user account name policies

• Maintaining backups of the existing database

In the database redesign process, before proceeding with the redesign it is often useful to ________.

• check whether certain conditions or assumptions about the data are valid

• find out why the design was not done properly the first time

• stop information systems and users from influencing each other

• set standards for user behavior

In the database redesign process, an SQL tool that is useful for testing whether or not certain

conditions or assumptions are valid is ________.

• UPDATE

• DROP TABLE

• CREATE TRIGGER

• NOT EXISTS

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?

• TableOne

• TableTwo

• Both TableOne and TableTwo

• Either TableOne or TableTwo

In the SQL statements

SELECT C1.CustName, C1.SalesRepNo

FROM CUSTOMER C1;

the "C1" is called a(n) ________.

• term

• alias

• convention

• label

When running a correlated subquery, the DBMS ________.

• runs the lower SELECT statement by itself and then sends the results to the upper SELECT

  statement.

• runs the upper SELECT statement by itself and then sends the results to the lower SELECT

  statement.

• alternates running the lower SELECT statement with running the upper SELECT statement

  based on each result of the lower SELECT statement

• Either A or B may be used depending on the query.

When running a correlated subquery, the DBMS always uses ________.

• regular processing

• nested processing

• "quick and dirty" processing

• SQL-92 processing

Which of the following SQL statements is a correctly stated correlated subquery?

• SELECT C1.CustName, C1.SalesRepNo

  FROM CUSTOMER C1

  WHERE C1.SalesRepNo IN

  (SELECT S1.SalesRepNo

  FROM SALESREP S1

  WHERE S1.RepName = 'Smith');

• SELECT C1.CustName, C1.SalesRepNo

  FROM CUSTOMER C1

  WHERE C1.SalesRepNo IN

  (SELECT S1.SaleRepNo

  FROM SALESREP S1

  WHERE S1.RepName = 'Smith')

  AND C1.SalesRepNo=S1.SalesRepNo);

• SELECT C1.CustName, C1.SalesRepNo

  FROM CUSTOMER C1

  WHERE C1.SalesRepNo IN

  (SELECT S1.SaleRepNo

  FROM SALESREP S1

  WHERE S1.RepName = 'Smith')

  AND C1.SalesRepNo<>S1.SalesRepNo);

• SELECT C1.CustName, C1.SalesRepNo

  FROM CUSTOMER C1

  WHERE C1.SalesRepNo IN

  (SELECT C2.SaleRepNo

  FROM CUSTOMER C2

  WHERE C1.SalesRepNo=C2.SalesRepNo);

  AND C1.OrderNo<>C2.OrderNo);

SQL queries that use EXISTS and NOT EXISTS are ________.

• normal subqueries

• correlated subqueries

• uncorrelated subqueries

• constraint dependent subqueries