Q&A Hero

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

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

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

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

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

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

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

Q: 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.

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

Q: 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.

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

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

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

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

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

Q: 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.

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

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

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

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

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

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

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

Q: Correlated subqueries can be used to verify functional dependencies.

Q: A correlated subquery looks very different from a noncorrelated subquery.

Q: 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.

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

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

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

Q: 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.

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

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

Q: Discuss workable data conversion and possible data loss when changing column data types during a database redesign.

Q: What is the process for adding a NOT NULL column to a table?

Q: What is the process and what are the considerations when changing a table name?

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

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

Q: What is reverse engineering and how well does it work?

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

Q: What is a correlated subquery? Include an example.

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

Q: Why do databases need redesigning?

Q: 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? A) ON UPDATE CASCADE B) ON DELETE CASCADE C) ADD CONSTRAINT D) FOREIGN KEY

Q: 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? A) ON UPDATE CASCADE B) ON DELETE CASCADE C) CASCADE CONSTRAINTS D) DEFAULT

Q: 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

Q: When decreasing cardinalities, there will always be ________. A) relationship loss B) foreign key loss C) data loss D) default value loss

Q: 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

Q: 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

Q: 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

Q: 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

Q: 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

Q: 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

Q: 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

Q: 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

Q: 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

Q: 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

Q: 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

Q: 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

Q: 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

Q: 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

Q: 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

Q: A dependency graph should include all of the following except ________. A) tables B) views C) triggers D) DEFAULT values

Q: 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

Q: The data model produced by reverse engineering is a(n) ________. A) conceptual schema B) internal schema C) dependency graph D) table-relationship diagram

Q: 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

Q: 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

Q: In a doubly nested set of NOT EXISTS SELECT statements, ________. A) if a row does not 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

Q: 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

Q: 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

Q: 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

Q: SQL queries that use EXISTS and NOT EXISTS are ________. A) normal subqueries B) correlated subqueries C) uncorrelated subqueries D) constraint dependent subqueries

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

Q: When running a correlated subquery, the DBMS always uses ________. A) regular processing B) nested processing C) "quick and dirty" processing D) SQL-92 processing

Q: 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.

Q: 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) label

Q: 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

Q: 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

Q: 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

Q: 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

Q: 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