Deck 8: Database Redesign

Question 1

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

Answer 1

True

Question 2

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

Answer 2

False

Question 3

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

Answer 3

True

Question 4

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

Question 5

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

Question 6

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

Question 7

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

Question 8

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.

Question 9

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

Question 10

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

Question 11

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.

Question 12

A correlated subquery,looks very different from a noncorrelated subquery.

Question 13

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

Question 14

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

Question 15

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

Question 16

Correlated subqueries can be used to verify functional dependencies.

Question 17

The use of a double nested set of NOT EXISTS SELECT statements can be used to find rows that meet some specified condition for every row in a table.

Question 18

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

Question 19

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

Question 20

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

Question 21

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

Question 22

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

Question 23

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

Question 24

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

Question 25

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

Question 26

The use of a double 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.

Question 27

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

Question 28

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.

Question 29

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

Question 30

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.

Question 31

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

Question 32

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 structure changes.

Question 33

Dependency graphs are graphical displays like bar charts.

Question 34

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

Question 35

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

Question 36

The use of a double nested set of NOT EXISTS SELECT statements can be used to find rows that meet some specified condition for every row in a table.

Question 37

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

Question 38

The author refers to the data model produced by reverse engineering as the RE data model.

Question 39

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

Question 40

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

Question 41

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.

Question 42

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)Testing proposed changes
D)Maintaining backups of the existing database
E)All of the above are possible steps in the database redesign process.

Question 43

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

Question 44

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.

Question 45

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
E)All of the above.

Question 46

Adding new tables and relationships to a database is difficult.

Question 47

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.

Question 48

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

Question 49

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.

Question 50

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.

Question 51

In a correlated subquery of a database that has tables TableOne and TableTwo,and if table TableOne is used in the upper SELECT statements,then which table is used in the lower SELECT statement?

A)TableOne
B)TableTwo
C)both TableOne and TableTwo
D)either TableOne or TableTwo
E)neither TableOne nor TableTwo

Question 52

When decreasing cardinalities,there will always be data loss.

Question 53

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
E)All of the above.

Question 54

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.

Question 55

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

Question 56

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

Question 57

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

A)correlated subqueries
B)EXISTS
C)NOT EXISTS
D)B and C
E)A,B and C

Question 58

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.

Question 59

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

Question 60

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

Question 61

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

A)reverse engineer all test databases
B)graph dependencies in all test databases
C)recover all test databases to their original state
D)A and B
E)A,B and C

Question 62

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

A)data modeling
B)data engineering
C)reverse engineering
D)schema modeling
E)schema engineering

Question 63

To change a table name,we ________.

A)use the SQL RENAME TABLE command
B)use the SQL ALTER TABLENAME command
C)use the SQL MODIFY TABLENAME command
D)create a new table,move the data and drop the old table
E)None of the above is the correct way to change a table name.

Question 64

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

A)a famous pattern in SQL
B)regularly used
C)rarely used to nonexistent in the real world
D)A and B
E)A,B,and C

Question 65

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

A)all existing rows at the time the column is added
B)all new rows
C)all new rows but only after the UPDATE command is issued
D)A and B
E)A and C

Question 66

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

A)data
B)foreign keys
C)constraints
D)triggers
E)All of the above may need to be checked.

Question 67

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

A)any row in the subquery meets the condition
B)all rows in the subquery meet the condition
C)no row in the subquery meets the condition
D)any row in the subquery fails to meet the condition
E)all rows in the subquery fail to meet the condition

Question 68

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

A)Small test database
B)Large test database
C)Operational database
D)A and B
E)A,B and C

Question 69

When running a correlated subquery,the DBMS ________.

A)runs the lower SELECT statement by itself and then sends the results to the upper SELECT statement.
B)runs the upper SELECT statement by itself and then sends the results to the lower SELECT statement.
C)alternates running the lower SELECT statement with running the upper SELECT statement based on each result of the lower SELECT statement
D)either A or B may be used depending on the query.
E)None of the above describes how a correlated subquery is run by the DBMS.

Question 70

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

A)use the REVISE TABLE command
B)use the ALTER TABLE command
C)use the MODIFY TABLE command
D)create a new table with the NULL column,move the other data,and drop the old table
E)None of the above is the correct way to add a NULL column.

Question 71

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

A)strong entities
B)weak non-ID-dependent entities
C)ID-dependent entities
D)intersection tables
E)supertype/subtype tables

Question 72

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

A)term
B)alias
C)convention
D)phrase
E)label

Question 73

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

A)regular processing
B)nested processing
C)"quick and dirty" processing
D)SQL-92 processing
E)a form of processing that is specific to the DBMS product

Question 74

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

A)conceptual schema
B)internal schema
C)dependency graph
D)table-relationship diagram
E)entity-relationship diagram

Question 75

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

A)SELECT C1.CustName,C1.SalesRepNo
FROM CUSTOMER C1
WHERE C1.SalesRepNo IN
(SELECT S1.SaleRepNo
FROM SALESREP S1
WHERE S1.RepName = 'Smith');
B)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);
C)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);
D)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);
E)None of the above is a correctly stated correlated subquery.

Question 76

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

A)conceptual schema
B)internal schema
C)dependency graph
D)table-relationship diagram
E)entity-relationship diagram

Question 77

A dependency graph should include:

A)tables
B)views
C)triggers
D)A and B
E)A,B and C

Question 78

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

A)if a row does not match any row,then it matches every row
B)if a row matches any row,then it matches every row
C)if a row does not match any row,then it does not match every row
D)if a row matches any row,then it does not match every row
E)if a row does not match any row,then referential integrity has been broken

Question 79

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

A)any row in the subquery meets the condition
B)all rows in the subquery meet the condition
C)no row in the subquery meets the condition
D)any row in the subquery fails to meet the condition
E)all rows in the subquery fail to meet the condition

Question 80

SQL queries that use EXISTS and NOT EXISTS are ________.

A)normal subqueries
B)correlated subqueries
C)uncorrelated subqueries
D)constraint dependent subqueries
E)constraint independent subqueries