Question 1

Correlated subqueries can be used to verify functional dependencies.

Accepted Answer

A) True 
 B)False  True

Question 2

What is a correlated subquery? Include an example.

Accepted Answer

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

Question 3

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

Accepted Answer

A) True 
 B)False  True

Question 4

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

Accepted Answer

A) Preserving the existing tables 
B) Preserving the existing relationships 
C) Preserving the existing data 
D) Both A and B are correct 
A) Preserving the existing tables 
B) Preserving the existing relationships 
C) Preserving the existing data 
D) Both A and B are correct

Question 5

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

Accepted Answer

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.SalesRepNoS1.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.OrderNoC2.OrderNo); 
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.SalesRepNoS1.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.OrderNoC2.OrderNo);

Question 6

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

Accepted Answer

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

Question 7

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

Accepted Answer

A) True 
 B)False

Question 8

To change a table name,we ________.

Accepted Answer

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

Question 9

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

Accepted Answer

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

Question 10

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

Accepted Answer

A) True 
 B)False

Question 11

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

Accepted Answer

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

Question 12

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

Accepted Answer

A) True 
 B)False

Question 13

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.

Accepted Answer

A) True 
 B)False

Question 14

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

Accepted Answer

A) True 
 B)False

Question 15

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

Accepted Answer

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

Question 16

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

Accepted Answer

A) True 
 B)False

Question 17

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.

Accepted Answer

A) True 
 B)False

Question 18

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

Accepted Answer

A) True 
 B)False

Question 19

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?

Accepted Answer

A) TableOne 
B) TableTwo 
C) Both TableOne and TableTwo 
D) Either TableOne or TableTwo 
A) TableOne 
B) TableTwo 
C) Both TableOne and TableTwo 
D) Either TableOne or TableTwo

Question 20

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.

Accepted Answer

A) True 
 B)False

Exam 8: Database Redesign

Correlated subqueries can be used to verify functional dependencies.

What is a correlated subquery? Include an example.

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

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

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

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

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

To change a table name,we ________.

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

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

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

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

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.

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

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

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

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.

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

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?

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.

Introduction

Introduction to Structured Query Language

The Relational Model and Normalization

Database Design Using Normalization

Data Modeling With the Entity-Relationship Model

Transforming Data Models Into Database Designs

SQL for Database Construction and Application Processing

Managing Multiuser Databases

Managing Databases With SQL Server 2014

Managing Databases With Oracle Database

Managing Databases With Mysql 5.6

The Web Server Environment

Big Data, Data-Warehouses, and Business Intelligence Systems

Getting Started With Microsoft Access 2013

Getting Started With Systems Analysis and Design

E-R Diagrams and the IDEF1X Standard

E-R Diagrams and the UML Standard

Getting Started With Mysql Workbench Data Modeling Tools

Getting Started With Microsoft Visio 2013

Data Structures for Database Processing

the Semantic Object Model

Getting Started With Web Servers, PHP, and the Netbeans IDE

Business Intelligence Systems

Big Data

Filters