Chapter 8 Questions

The NOT EXITS keyword will be true if any row in the subquery fails to meet the condition.

.False

What is a correlated subquery? Include an example.

A correlated subquery uses the standard SQL subquery structure of a SELECT statement (called the lower SELECT) within the WHERE clause of a controlling (or upper) SELECT. However, where a non-correlated subquery uses different tables in the upper and lower SELECTS, the correlated subquery uses the same table in both SELECTS. SQL aliases are used to provide different table names within the query. When a non-correlated subquery is processed, the lower SELECT is processed first and the entire result set of the lower SELECT is passed to the upper SELECT. When a correlated subquery is processed, a nested processing is used where each individual result of the lower SELECT is returned one at a time to the upper SELECT for processing. Here is an example that finds the last names of all students advised by a faculty whose last name is 'Smith': SELECT S.LastName FROM Student S WHERE EXISTS (SELECT * FROM FACULTY F WHERE F.FacultyID = S.AdvisorID AND F.LastName = 'Smith'));

A dependency graph should include all of the following except ________.

DEFAULT values

Which of the following different copies of the database schema is typically not used in the database redesign process?

Data warehouse database

Explain and contrast the results provided by the EXISTS, NOT EXISTS, and doubly-nested NOT EXISTS keywords.

The EXISTS and NOT EXISTS keywords are used in the WHERE clause of correlated subqueries, and thus are the basis of just another form of correlated subquery. The EXISTS keyword will be true if any row in the subquery meets the subquery condition(s). The NOT EXISTS keyword will be true when all rows in the subquery fail to meet the subquery condition(s). The double use of NOT EXISTS, which requires three nested SELECT statements, can be used to test for situations where every row of a table meets a specified condition tested. The doubly nested NOT EXISTS pattern is a famous pattern in SQL and very useful when needed.

When redesigning a database, how can we check assumptions about functional dependencies?

There are two related SQL techniques that we can use to check assumptions about functional dependencies in an existing database. The first is to use a correlated subquery with the SQL IN keyword, and the second is to use a correlated subquery structure using the SQL EXISTS and/or NOT EXISTS keywords.

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.

True

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.

True

A means must be created to recover all test databases to their original stat during the redesign testing process.

True

Because EXISTS and NOT EXISTS are forms of correlated subqueries, the processing of the associated SELECT statements must be nested,

True

Because of the need to know the functional dependencies in a database, it is a good idea to create a dependency graph.

True

Changing table names is complicated by the fact that constraints are often associated with the table and will also need to be changed.

True

Converting date, money, or other more specific data types to char or varchar will usually succeed.

True

Correlated subqueries can be used to verify functional dependencies.

True

Deleting tables and relationships is basically a matter of foreign key constraints and then dropping the tables.

True

Depending on the DBMS, when changing the minimum cardinality on the parent side from zero to one, the foreign key constraint that defines the relationships may have to be dropped before the change is made and re-added afterwards.

True

EXISTS and NOT EXISTS are actually just another form of correlated subqueries.

True

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 existing rows at the time the new column is added.

True

In a correlated subquery, the DBMS must run the lower SELECT statement as a process that is nested within the upper SELECT statement.

True

In a correlated subquery, the same table is used in he upper and lower SELECT statements

True

In a real sense, information systems and organizations do not just influence each other, but rather they create each other.

True

In order to minimize the need to change table names, some organizations have a policy that no user or application should ever employ the true name of a table, but use views as table aliases instead.

True

In the SQL statement: SELECT S1.CustName, S1.SalesRepNo FROM SALES S1; the "S1" is called an alias.

True

In the database redesign process, it is often useful to test whether certain conditions or assumptions are valid before proceeding with the redesign.

True

SQL Server 2014 contains a system-stored procedure named sp_rename that can be used to change table names.

True

The authors refer to the data model produced by reverse engineering as the RE data model.

True

The continuous circular process of changes is known as the Systems Development Life Cycle (SDLC)

True

The data model produced by reverse engineering may include some entities that should not appear in the data model.

True

The design produced by reverse engineering may be described as a table-relationship diagram.

True

The process of reading an actual database schema and producing a data model from that schema is called reverse engineering.

True

The use of double nested set of NOT EXISTS SELECT statements is a famous pattern in SQL use.

True

There is a common trap in writing a correlated subquery, which will cause no rows to ever be displayed in the results.

True

There is no good SQL command that can be used to change table names.

True

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.

True

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.

True

To drop a constraint, no preliminary steps are needed and we can simply use the ALTER TABLE DROP CONSTRAINT statement.

True

To drop a nonkey column from a table, no preliminary steps are needed and we can simply use the ALTER TABLE DROP COLUMN statement.

True

When decreasing maximum cardinalities, there will always be data loss.

True

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.

True

When using a double nested set of NOT EXIST SELECT statements, a row that does not match any row matches every row.

True

When using queries with EXISTS and NOT EXISTS, the processing of the associated SELECT statements must be nested.

True

the EXISTS keyword will be true if any row in the subquery meets the condition.

True

Discuss the role of backup and test databases in database redesign.

Typically, there are at least three different copies of the database schema used in database redesign: (1) a small test database for initial testing, (2) a large or full copy test database for further testing, and (3) the operational database itself. There must be a process to restore the test databases to their original state during the testing process so that tests can be rerun as needed. If it is not possible to use a full copy of a very large operational database for testing, an appropriate large scale test version must be created and used.

A doubly nested set of NOT EXISTS SELECT statements is ________.

a famous pattern in SQL regularly used Both A and B are correct

When making any change to the database structure, we may need to check for effects of the change on any of the following except ________.

ad-hoc SQL queries

In the SQL statements SELECT C1.CustName, C1.SalesRepNo FROM CUSTOMER C1; the "C1" is called a(n) ________.

alias

If a DEFAULT constraint is included when a new column is added to a table, the default value is applied to ________.

all new rows

When running a correlated subquery, the DBMS ________.

alternates running the lower SELECT statement with running the upper SELECT statement based on each result of the lower SELECT statement

When running an SQL query that uses EXISTS, the EXISTS keyword will be true if ________.

any row in the subquery meets the condition

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

Changing cardinalities ________.

commonly occurs in database redesign

SQL queries that use EXISTS and NOT EXISTS are ________.

correlated subqueries

To add a NOT NULL column to a table, we ________.

create a new NULL column, insert data values into every row, and change the NULL constraint to NOT NULL

To change a table name, we ________.

create a new table, move the data, and drop the old table

When decreasing cardinalities, there will always be ________.

data loss

Because of the need to know the functional dependencies in a database, it is a good idea to create a(n) ________.

dependency graph

In a doubly nested set of NOT EXISTS SELECT statements, ________.

if a row does not not match any row, then it matches every row

The data model produced by reverse engineering is not truly a logical model because it will contain ________.

intersection tables

When changing the minimum cardinality on the parent side of the relationship from zero to one, the foreign key ________.

must be changed from NULL to NOT NULL

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

nested processing

When running an SQL query that uses NOT EXISTS, the NOT EXISTS keyword will be true if ________.

no row in the subquery meets the condition

In order to make sure the database redesign is working properly during the redesign testing process, a means must be created to ________.

recover all test databases to their original state

The process of reading an actual database schema and producing a data model from that schema is called ________.

reverse engineering

The data model produced by reverse engineering is a(n) ________.

table-relationship diagram

Database redesign is fairly easy when ________.

there is no data in the database

To add a NULL column to a table, we ________.

use the ALTER TABLE command

Why do databases need redesigning?

Databases need redesigning for two reasons. First, it is not always easy to build a database correctly the first time, and changes in an organization's business process may create the need for a change. With regards to the first reason, even if the data modeling process captured all the users' requirements, translating the requirements into a correct data model and database design can be difficult. Almost inevitably, there will be errors that need to be corrected. With regards to the second reason, information systems and the organizations that use them influence each other to the point where they can be said to "create" each other. As the organization changes so will the user requirements, which will necessitate database redesign.

When changing column data types, which of the following data conversions will either usually or always succeed?

Date or money → char or varchar

What is a dependency graph, and what is it used for in database redesign?

Dependency graphs are not a graphical display like a bar chart, but rather a set of connected nodes. The nodes represent objects such as tables, view, triggers, stored procedures, etc., in the database. Line segments show the interconnections, or dependencies, between these objects. Thus, a dependency graph is useful for determining how a change to one object in a database might affect other objects in the database.

When dropping a nonkey column from a table, which of the following steps is (are) included in the process? (The order of the steps listed below is not relevant, only the steps themselves.)

Drop the column from the table

When dropping a foreign key column from a table, which of the following steps is (are) included in the process? (The order of the steps listed below is not relevant, only the steps themselves.)

Drop the foreign key constraint from the table Drop the foreign key column from the table Both A and B are correct

When dropping tables and relationships, which of the following steps is/are included in the process? (The order of the steps listed below is not relevant, only the steps themselves.)

Drop the foreign key constraints from the tables Drop the tables Both A and B are correct

When dropping a primary key column from a table, which of the following steps is included in the process? (The order of the steps listed below is not relevant, only the steps themselves.)

Drop the foreign keys in other tables based on the primary key column

When increasing cardinalities from 1:N to N:M, which of the following steps is not included in the process? (The order of the steps listed below is not relevant, only the steps themselves.)

Drop the primary key

A correlated subquery looks very different from a non-correlated subquery.

False

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.

False

Adding new tables and relationships to a database is difficult.

False

Although correlated subqueries are useful in database redesign, they cannot be used to verify functional dependencies.

False

Database redesign is equally difficult whether or not the database has data in it.

False

Database redesign is rarely needed because databases are usually built correctly the first time.

False

Dependency graphs are graphical displays like bar charts.

False

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.

False

In a correlated subquery, the DBMS can fun the lower SELECT statement by itself and then send the results to the upper SELECT statement.

False

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 EXITS.

False

SQL contains an SQL command RENAME TABLENAME that can be used to change table names.

False

The data model produced by revers engineering is a true conceptual schema.

False

The use of a doubly nested set of NOT EXISTS SELECT statements is so rare that even if you a professional database developer you will probably never see it used.

False

There are several difficulties with increasing cardinalities from 1:1 to 1:N, one of which is preserving the existing relationships.

False

To add a NULL column to a table, we simply used the MODIFY TABLE statement.

False

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.

False

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.

False

Typically, there are at least four different copies of the database schema used in the redesign process.

False

In the database redesign process, an SQL tool that is useful for testing whether or not certain conditions or assumptions are valid is ________.

NOT EXISTS

During the database redesign process, you discover that when an ORDER is deleted, its corresponding entries in the LINE_ITEM table are not deleted (but you would like them to be). In order to fix, this, you use what SQL syntax?

ON DELETE CASCADE

During the database redesign process, you discover that when you try to change a DEPARTMENT name (the department's primary key), the change is disallowed by the DBMS due to your design of the EMPLOYEE table (which has a foreign key referencing DEPARTMENT) You want the change to be allowed. In order to fix this, you use what SQL syntax?

ON UPDATE CASCADE

Which of the following are difficulties when changing the maximum cardinality from 1:1 to 1:N?

Preserving the existing relationships

What is reverse engineering and how well does it work?

Reverse engineering (RE) is the process of reading the database schema of an existing database and using it to reconstruct a data model of the database. It works fairly well, but does have significant limitations. First, RE produces conceptual schema errors because it creates an entity for every table in the database including intersection tables in N:M relationships which would not be an entity in the original data model. Second, it does not generate internal schema information such as referential integrity constraints. Therefore, the result, which the authors call the RE data model, is a good starting point for analyzing an existing database but is not a complete solution.

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

Revising user account name policies

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 C2.SaleRepNo FROM CUSTOMER C2 WHERE C1.SalesRepNo=C2.SalesRepNo); AND C1.OrderNo<>C2.OrderNo);

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