Chapter 4

Question 1

When you are given a set of tables and asked to create a database to store their data, the first step is to assess the tables’ structure and content.

Answer 1

True

Question 2

The first step in assessing table structure is to count rows and examine columns.

Answer 2

True

Question 3

To count the number of rows in a table, use the SQL construct COUNT(ROWS).

Answer 3

False

Question 4

To determine the number and type of columns in a table, use the SQL construct COUNT(*).

Answer 4

False

Question 5

To limit the number of rows retrieved from a table, use the SQL TOP keyword.

Answer 5

True

Question 6

When examining data values as a part of assessing table structure, you should try to determine two types of dependencies: functional dependencies and functional dependencies.

Answer 6

True

Question 7

When examining data values as a part of assessing table structure, you should try to determine three types of keys: the primary key, any candidate keys and any foreign keys.

Answer 7

True

Question 8

The second step in assessing table structure is to examine data values and determine dependencies and keys.

Answer 8

True

Question 9

When examining data values as a part of assessing table structure, you should try to determine functional dependencies.

Answer 9

True

Question 10

When examining data values as a part of assessing table structure, you should try to determine multivalued dependencies.

Answer 10

True

Question 11

When examining data values as a part of assessing table structure, you should try to determine the table’s primary key.

Answer 11

True

Question 12

When examining data values as a part of assessing table structure, there is no need to try to determine candidate keys other than the table’s primary key.

Answer 12

False

Question 13

When examining data values as a part of assessing table structure, there is no need to try to determine foreign keys.

Answer 13

False

Question 14

The third step in assessing table structure is to check the validity of presumed referential integrity constraints.

Answer 14

True

Question 15

The elimination of modification anomalies and the reduction of duplicated data are advantages of normalization.

Answer 15

True

Question 16

Database design varies depending on whether you’re building an updatable database or a read-only database.

Answer 16

True

Question 17

Normalization eliminates modification anomalies and data duplication.

Answer 17

False

Question 18

The presence of one or more foreign keys in a relation means that we cannot eliminate duplicated data in that table.

Answer 18

True

Question 19

Normalization requires applications to use more complex SQL since they will need to write subqueries and joins to recombine data stored in separate relations.

Answer 19

True

Question 20

Relations are sometimes left unnormalized to improve performance.

Answer 20

True

Question 21

Relations in BCNF have no modification anomalies in regard to functional dependencies.

Answer 21

True

Question 22

A defining requirement for BCNF is that every determinant must be a candidate key.

Answer 22

True

Question 23

The SQL INSERT statement can be used to populate normalized tables.

Answer 23

True

Question 24

The SQL DELETE TABLE statement can be used to remove unneeded tables after the normalized tables are created and populated.

Answer 24

False

Question 25

SQL statements that can be used to create referential integrity statements for normalized tables are created during the normalization process.

Answer 25

True

Question 26

The standard sales order/line item pattern is a classic example of unneeded normalization.

Answer 26

False

Question 27

Multivalued dependencies create anomalies so serious that multivalued dependencies must always be eliminated.

Answer 27

True

Question 28

Writing SQL subqueries and joins against normalized tables is simple compared to the code that must be written to handle anomalies from multivalued dependencies.

Answer 28

True

Question 29

To eliminate multivalued dependencies, normalize your tables so that they are all in BCNF.

Answer 29

False

Question 30

Creating a read-only database is a job often given to beginning database professionals.

Answer 30

True

Question 31

Read-only databases are often updated.

Answer 31

False

Question 32

Design guidelines and priorities are the same whether you're working with an updatable database or a read-only database.

Answer 32

False

Question 33

Normalization is an advantage for a read-only database.

Answer 33

False

Question 34

Denormalization is the process of joining previously normalized tables back together.

Answer 34

True

Question 35

Denormalization reduces the complexity of the SQL statements needed in an application to read required data.

Answer 35

True

Question 36

Denormalization is simple—join the data together and store it in a table.

Answer 36

True

Question 37

Read-only databases seldom use more than one copy of a set of same data.

Answer 37

False

Question 38

Read-only databases often use several copies of a set of the same data, where each copy is modified for a specific use.

Answer 38

True

Question 39

Multivalued dependencies show up under a different name as the multivalued, multicolumn problem.

Answer 39

True

Question 40

The multivalued, multicolumn problem occurs when a set of columns are used to store data that should actually be in one column.

Answer 40

True

Question 41

One situation created by the multivalued, multicolumn problem is that the maximum number of data values for an attribute is limited.

Answer 41

True

Question 42

If you have a table with a set of columns named "Child01", "Child02" and "Child03", the table has the multivalued, multicolumn problem.

Answer 42

True

Question 43

When you are creating a database from existing data, you will have only minor problems with inconsistent values.

Answer 43

False

Question 44

An inconsistent values problem is created when different users have coded the same data entries differently.

Answer 44

True

Question 45

The problem of misspelled data entries is an entirely different problem than the inconsistent values problem.

Answer 45

False

Question 46

You are creating a BOAT table using existing data from multiple sources, and you find that you have "power boat blue", "boat, power, blue" and "blue power boat" as data values for the same column. This is an example of the inconsistent values problem.

Answer 46

True

Question 47

A missing value is called a null value.

Answer 47

True

Question 48

Null values are a problem because they are ambiguous.

Answer 48

True

Question 49

A null value in a column may indicate that there is no appropriate value for that attribute.

Answer 49

True

Question 50

A null value in a column may indicate that there is an appropriate value for that attribute, but it is unknown.

Answer 50

True

Question 51

A null value in a column may indicate that there is an appropriate value for that attribute, and although the value is known no one has entered the value into the database.

Answer 51

True

Question 52

Most DBMS products will let you define a primary key on a column that contains null values.

Answer 52

False

Question 53

The SQL IS NULL keyword can be used to count the number of nulls in a column.

Answer 53

True

Question 54

General-purpose remarks columns rarely contain important data

Answer 54

False

Question 55

The problem with a general-purpose remarks column is that the data it contains are likely to be verbal, inconsistent and stored in a verbose manner.

Answer 55

True

Question 56

If you see a column name Notes, it is likely that this is a general-purpose remarks column.

Answer 56

True

Question 57

When you are given a set of tables and asked to create a database to store their data, the first step is to ________.

Answer 57

assess the existing tables' structure and content

Question 58

The first step in assessing table structure includes ________.

Answer 58

counting rows and examining columns

Question 59

The second step in assessing table structure includes ________.

Answer 59

examining data values

Question 60

During the second step of assessing table structure, you are trying to determine ________.

Answer 60

multivalued dependencies, functional dependencies, and foreign keys

Question 61

During the second step of assessing table structure, you are trying to determine ________.

Answer 61

primary keys, candidate keys and foreign keys

Question 62

To count the number of rows in a table, use the SQL construct ________.

Answer 62

SELECT COUNT(*)

Question 63

The SQL function COUNT ________.

Answer 63

counts the number of rows in a table

Question 64

To limit the number of rows retrieved from a table, use the SQL construct ________.

Answer 64

SELECT TOP n *

Question 65

The SQL keyword TOP ________.

Answer 65

limits the number of rows retrieved from a table

Question 66

You have been given two tables, CUSTOMER and SALE. You want to check the referential integrity constraint: SALE.CustomerNumber must exist in CUSTOMER.CustomerNumber You run the following SQL query: ``` SELECT CustomerNumber FROM SALE WHERE CustomerNumber NOT IN (SELECT CustomerNumber FROM SALE, CUSTOMER WHERE SALE.CustomerNumber = CUSTOMER.CustomerNumber); ``` What is shown in the results of this query?

Answer 66

All values of CustomerNumber that violate the constraint.

Question 67

The advantages of normalization include ________.

Answer 67

the elimination of modification anomalies

Question 68

The disadvantages of normalization include ________.

Answer 68

more complex SQL for multitable subqueries and joins

Question 69

The presence of one or more foreign keys in a relation prevents ________.

Answer 69

the elimination of duplicated data

Question 70

Anomalies caused by functional dependencies can be eliminated by putting tables into ________.

Answer 70

BCNF

Question 71

The defining characteristic of BCNF is that a table is in BCNF if ________.

Answer 71

all determinants are candidate keys

Question 72

A classic example of unneeded normalization is when we are dealing with ________.

Answer 72

ZIP codes

Question 73

Unlike the anomalies from functional dependencies, the anomalies from ________ are so serious that they should always be eliminated.

Answer 73

multivalued dependencies

Question 74

Read-only databases are used for ________.

Answer 74

querying and reporting

Question 75

For a number of reasons, ________ is not often an advantage for a read-only database.

Answer 75

normalization

Question 76

________ is the process of joining two or more tables and storing the result as a single table.

Answer 76

Denormalization

Question 77

An advantage of denormalization is ________.

Answer 77

faster querying and less complex SQL in application code

Question 78

A table designed to store PhoneNumber01, PhoneNumber02 and PhoneNumber03 contains ________.

Answer 78

the multivalued, multicolumn problem

Question 79

A form of multivalued dependency is found in ________.

Answer 79

the multivalued, multicolumn problem

Question 80

When a table is created using existing data from multiple sources, you are likely to find that the different sources code data in slightly different ways. This is an example of ________.

Answer 80

the inconsistent values problem

Question 81

You are creating a PRODUCT table using existing data from multiple sources. Examining the data, you find that you have "large red hat", "large hat, red", "red hat large" and "hat, large, red." This is an example of ________.

Answer 81

the inconsistent values problem

Question 82

The problem of misspelled data entries is really the same as ________.

Answer 82

the inconsistent values problem

Question 83

When a table is created using existing data from multiple sources, you are likely to find that some data values have never been provided. This is an example of ________.

Answer 83

the missing values problem

Question 84

When a table is created using existing data from multiple sources, you are likely to find that some data values have never been provided. This is an example of ________.

Answer 84

the missing values problem

Question 85

Reviewing the work done on a table that was created using existing data from multiple sources, you are likely to find that some data values that were provided were never entered into the table. This is an example of ________.

Answer 85

the missing values problem

Question 86

A missing value is called a(n) ________.

Answer 86

null value

Question 87

A null value can indicate which of the following conditions?

Answer 87

The value is inappropriate, The value is appropriate but unknown, and The value is appropriate and known, but not entered into the database

Question 88

To check for null values in a column in a table, use the SQL phrase ________.

Answer 88

IS NULL

Question 89

The SQL keyword IS NULL can be used to ________.

Answer 89

count the number of null values in a column

Question 90

Reviewing the work done on a table that was created using existing data from multiple sources, you find that a column name Remarks has been included, and it is populated with inconsistent and verbose verbal data. This is an example of ________.

Answer 90

the general-purpose remarks column problem