Database Storage & Design Chapter 8 2 Null Not Null Must Changed From Not Null Null Must Changed Composite

Unlock access to all the studying documents.

View Full Document

62) 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.

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

A) regular processing

B) nested processing

C) “quick and dirty” processing

D) SQL-92 processing

64) 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.SalesRepNo

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);

65) SQL queries that use EXISTS and NOT EXISTS are ________.

A) normal subqueries

B) correlated subqueries

C) uncorrelated subqueries

D) constraint dependent subqueries

66) 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

67) 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

68) A doubly 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) Both A and B are correct

69) In a doubly 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

70) 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

71) The data model produced by reverse engineering is not truly a logical model because it will

contain ________.

A) tables for strong entities

B) supertype/subtype tables

C) tables for ID-dependent entities

D) intersection tables

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

A) conceptual schema

B) internal schema

C) dependency graph

D) table-relationship diagram

73) 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

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

A) tables

B) views

C) triggers

D) DEFAULT values

75) Which of the following different copies of the database schema is typically not used in the

database redesign process?

A) small test database

B) data warehouse database

C) operational database

D) large test database

76) 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) re-interview all relevant users

77) To change a table name, we ________.

A) use the RENAME TABLE command

B) use the ALTER TABLENAME command

C) use the MODIFY TABLENAME command

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

78) When making any change to the database structure, we may need to check for effects of the

change on any of the following except ________.

A) data

B) foreign keys

C) ad-hoc SQL queries

D) triggers

79) 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

80) 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) Both A and B are correct

81) To add a NOT 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 NULL column, insert data values into every row, and change the NULL

constraint to NOT NULL

82) 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.)

A) Drop any column constraints from the table

B) Drop the column from the table

C) Drop any foreign keys constraints based on the column

D) Both A and B are correct

83) 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.)

A) Drop all constraints from the table

B) Drop the foreign keys from the table

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

D) Drop the primary key(s) of any table(s) to which this one is related

84) 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.)

A) Drop the foreign key constraint from the table

B) Drop the foreign key column from the table

C) Drop the primary key in the other table referenced by the referential integrity constraint

D) Both A and B are correct

85) Changing cardinalities ________.

A) never occurs in database redesign

B) rarely occurs in database redesign

C) commonly occurs in database redesign

D) always occurs in database redesign

86) When changing column data types, which of the following data conversions will either

usually or always succeed?

A) Numeric → date

B) Date or money → char or varchar

C) Char or varchar → numeric

D) Char or varchar → date or money

87) When changing the minimum cardinality on the parent side of the relationship from zero to

one, the foreign key ________.

A) must be changed from NULL to NOT NULL

B) must be changed from NOT NULL to NULL

C) must be changed to a composite key

D) must be changed to a surrogate key

88) Which of the following are difficulties when changing the maximum cardinality from 1:1 to

1:N?

A) Preserving the existing tables

B) Preserving the existing relationships

C) Preserving the existing data

D) Both A and B are correct

89) 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.)

A) Create an intersection table

B) Populate the intersection table

C) Drop the old foreign key

D) Drop the primary key

90) When decreasing cardinalities, there will always be ________.

A) relationship loss

B) foreign key loss

C) data loss

D) default value loss

91) 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.)

A) Drop the foreign key constraints from the tables

B) Drop the tables

C) Drop the primary key constraints from the tables

D) Both A and B are correct

92) 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).

To fix, this, you use what SQL syntax?

A) ON UPDATE CASCADE

B) ON DELETE CASCADE

C) CASCADE CONSTRAINTS

D) DEFAULT

93) 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. To fix this, you use what SQL syntax?

A) ON UPDATE CASCADE

B) ON DELETE CASCADE

C) ADD CONSTRAINT

D) FOREIGN KEY

94) Why do databases need redesigning?

95) When redesigning a database, how can we check assumptions about functional

dependencies?

96) What is a correlated subquery? Include an example.

97) Explain and contrast the results provided by the EXISTS, NOT EXISTS, and doubly-nested

NOT EXISTS keywords.

98) What is reverse engineering and how well does it work?

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

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

101) What is the process and what are the considerations when changing a table name?

102) What is the process for adding a NOT NULL column to a table?

103) Discuss workable data conversion and possible data loss when changing column data types

during a database redesign.