Chapter 4 Questions

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

True

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.

True

A classic example of unneeded normalization is when we are dealing with ________. A) ZIP codes B) sales orders and line items C) association patterns D) multivalued dependencies

A

A form of multivalued dependency is found in ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem

A

A table with columns PhoneNumber01, PhoneNumber02, and PhoneNumber03 is likely to have ________. A) the multivalue, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem

A

During the first step of assessing table structure, you are trying to determine ________. A) multivalued dependencies B) data types C) missing values D) subqueries

A

The advantages of normalization include ________. A) the elimination of modification anomalies B) the elimination of duplicated data C) more complex SQL for multitable subqueries and joins D) simpler SQL queries

A

When you are given a set of tables and asked to create a database to store their data, the first step is to ________. A) assess the existing tables' structure and content B) design the database structure C) create one or more new tables D) move the data into the new database

A

________ is the process of joining two or more tables and storing the result as a single table. A) Querying B) Normalization C) Denormalization D) Updating

C

What are null values, and why are they a problem?

A null value is a missing value, a value that has never been input into a cell in a table. Null values are a problem because they are ambiguous. A null value can represent three different conditions: (1) the value is inappropriate, (2) the value is appropriate but unknown, or (3) the value is appropriate and known but not entered into the database.

A missing value is called a(n) ________. A) empty value B) null value C) missing value D) deleted value

B

A null value can indicate which of the following conditions? A) The value is incorrect. B) The value is appropriate but unknown. C) The value has been updated. D) The value can never be changed.

B

An advantage of denormalization is ________. A) faster updating B) faster querying C) faster copying of data D) faster design

B

During the second step of assessing table structure, you are trying to determine ________. A) relationships B) candidate keys C) updatability D) efficiency

B

For a number of reasons, ________ is not often an advantage for a read-only database. A) updating B) normalization C) denormalization D) Both A and B are correct

B

Read-only databases are used for ________. A) updating B) querying C) data entry D) user account management

B

The SQL function COUNT ________. A) counts the number of columns in a table B) counts the number of rows in a table C) counts the number of tables in a database D) counts the number of updates to a database

B

The SQL keyword TOP ________. A) limits the number of columns retrieved from a table B) limits the number of rows retrieved from a table C) limits the number of tables retrieved from a database D) A and C

B

The presence of one or more foreign keys in a relation prevents ________. A) the elimination of modification anomalies B) the elimination of duplicated data C) more complex SQL for multitable subqueries and joins D) Both A and B are correct

B

The problem of misspelled data entries is really the same as ________. A) the multivalue, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem

B

To limit the number of rows retrieved from a table, use the SQL construct ________. A) SELECT * B) SELECT TOP n * C) SELECT COUNT(TOP n) D) SELECT COUNT(*)

B

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 ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem

B

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 ________. A) the multivalue, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem

B

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 CUSTOMER); What is shown in the results of this query? A) All values of CustomerNumber that match the constraint. B) All values of CustomerNumber that violate the constraint. C) All values of CustomerNumber where SALE.CustomerNumber = CUSTOMER.CustomerNumber. D) All values of Customer number that are in CUSTOMER but not in SALE.

B

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 ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem

C

The SQL keyword IS NULL can be used to help ________. A) count the number of columns in a table B) count the number of rows in a table C) count the number of null values in a column D) count the number of values in a column

C

The disadvantages of normalization include ________. A) the elimination of modification anomalies B) the elimination of duplicated data C) more complex SQL for multitable subqueries and joins D) Both A and B are correct

C

The second step in assessing table structure includes ________. A) counting rows B) examining columns C) examining data values D) A and B

C

To check for null values in a column in a table, use the SQL phrase ________. A) IS B) IS NOT C) IS NULL D) COUNT(NULL)

C

When a table is created using existing data from multiple sources, you are likely to find that some data values have never been provided because the values are not known. This is an example of ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem

C

When a table is created using existing data, you are likely to find that some data values have never been provided because any value there would be inappropriate. This is an example of ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem

C

Anomalies caused by functional dependencies can be eliminated by putting tables into ________. A) 1NF B) 2NF C) 3NF D) BCNF

D

Reviewing the work done on a table that was created using existing data from multiple sources, you find that a column named Remarks has been included, and it is populated with inconsistent and verbose verbal data. This is an example of ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem

D

The defining characteristic of BCNF is that a table is in BCNF if ________. A) all rows are unique B) all columns are consistent C) the primary key is a candidate key D) all determinants are candidate keys

D

The first step in assessing table structure includes ________. A) counting rows B) examining columns C) examining data values D) Both A and B are correct

D

To count the number of rows in a table, use the SQL construct ________. A) SELECT * B) SELECT TOP n * C) SELECT COUNT(TOP n) D) SELECT COUNT(*)

D

Unlike the anomalies from functional dependencies, the anomalies from ________ are so serious that they should always be eliminated. A) general purpose remarks columns B) sales orders and line items C) association patterns D) multivalued dependencies

D

Explain denormalization, and why it may be appropriate to denormalize a set of tables.

Denormalization is the process of taking a normalized set of relations and converting them into a smaller set of relations that is in a lower normal form and thus vulnerable to a greater number of anomalies. While the fully normalized set of relations is more desirable from a theoretical perspective, it does have some disadvantages. The greater the number of relations in the database, the more work that is required by the database whenever data must be retrieved from multiple relations to reconstruct the users' view of the data. In some circumstances, the anomalies introduced by denormalizing a data model may be considered an acceptable cost for the performance improvement from reducing the overhead work performed by the database.

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

False

General-purpose remarks columns rarely contains important data.

False

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

False

Normalization eleiminates modifications anomalies and data duplication.

False

Normalization is an advantage for a read-only database.

False

Read-only databases are often updated.

False

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

False

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

False

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

False

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

False

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

False

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

False

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.

False

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

False

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

False

Describe the steps in assessing table structure when you are given a set of tables and asked to construct a new database to store the existing data.

First, you should count the number of rows in the tables and determine the type of data stored in each column. Second, you should analyze the existing data values and work with users to determine (1) multivalued dependencies, (2) functional dependencies, (3) candidate keys, (4) primary keys and (5) foreign keys. Finally, you should check the validity of the possible referential integrity constraints.

What are general-purpose remarks columns, and why are they a problem?

General-purpose remarks columns are columns in a table with labels such as "Remarks", "Comments", or "Notes". They are a problem because these columns often contain important data that is stored in an inconsistent, verbal, and verbose way. Further, they may contain multiple data items.

What are inconsistent values, and why are they a problem?

Inconsistent values are different forms of the same data value. For example, in a database of dog owners and their dogs, we might find a border collie listed as "b. collie", "collie, border", and "border collie". This will create query problems. For example, a query for "border collie" will not return the dogs listed in the other formats. Or, alternatively, we would have to write a query that includes all possible (or at least known) variations of "border collie," and that would be a real pain.

What are the advantages and disadvantages of normalization?

The advantages of normalization are that it (1) eliminates modification anomalies and (2) reduces duplicated data. Reducing duplicated data will eliminate data integrity problems and save file space. The disadvantages of normalization are that it (1) will require application developers to write more complicated SQL statements for multitable subqueries and joins, and (2) may slow down the applications running against the database.

What is the multivalue, multicolumn problem, and why is it a problem? Include an example.

The multivalue, multicolumn problem occurs when a table uses multiple columns to store multiple values of an attribute. For example, a database of dog owners might have a table with the structure: DOG_OWNER (OwnerID, LastName, FirstName, DogName01, DogName02, DogName03) This is a problem because (1) the number of DogNames is fixed, and (2) this structure creates query problems. In fact, this structure is another form of the multivalued dependency, and requires a similar solution: a DOG table should be created to store the DogNames and with a foreign key to link the dogs to their owner.

When accessing table structure, how do you determine how many rows are in a table?

To determine how many rows are in a table, use a SELECT query that contains the SQL COUNT(*) function. This function will return the number of rows in the table.

When assessing table structure, how do you determine the number and types of columns in a table?

To determine the number and types of how many rows are in a table, use a SELECT query that contains the SQL * expression. This expression will return all the columns in the table, and the data types can be determined by examining the values in each column.

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.

True

Relations are sometimes left unnormalized to improve performance.

Tre

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

True

A missing value is called a null value

True

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.

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

Null values are a problem because they are ambiguous.

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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.

True

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

True

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

True

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

True

Unites States ZIP codes are a classic example of unneeded normalization.

True

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

True

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

True

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

True

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.

True

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

True

When accessing table structure, and determining the number and types of columns in a table, how can you control how many rows are returned?

When determining the number and types of how many rows are in a table, use a SELECT query that contains the SQL SELECT TOP ## expression, where ## is the number of rows that you want returned. This function will return only the number of rows specified by the TOP expression, which can make a table with a large number of rows manageable.