Deck 4: Database Design Using Normalization

Question 1

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

Answer 1

False

Question 2

Normalization eliminates modification anomalies and data duplication.

Answer 2

False

Question 3

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

Answer 3

True

Question 4

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.

Question 5

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

Question 6

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

Question 7

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

Question 8

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.

Question 9

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

Question 10

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

Question 11

Relations are sometimes left unnormalized to improve performance.

Question 12

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

Question 13

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

Question 14

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

Question 15

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

Question 16

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

Question 17

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

Question 18

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.

Question 19

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.

Question 20

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

Question 21

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

Question 22

Normalization is an advantage for a read-only database.

Question 23

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

Question 24

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

Question 25

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

Question 26

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

Question 27

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

Question 28

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

Question 29

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

Question 30

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

Question 31

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

Question 32

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

Question 33

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

Question 34

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

Question 35

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

Question 36

Read-only databases are often updated.

Question 37

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

Question 38

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

Question 39

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

Question 40

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

Question 41

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

Question 42

General-purpose remarks columns rarely contain important data.

Question 43

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.

Question 44

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

Question 45

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

Question 46

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

Question 47

Null values are a problem because they are ambiguous.

Question 48

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

Question 49

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

Question 50

The second step in assessing table structure includes ________.

A)counting rows
B)examining columns
C)examining data values
D)A and B

Question 51

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.

Question 52

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

Question 53

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

Question 54

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.

Question 55

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

A)multivalued dependencies
B)data types
C)missing values
D)subqueries

Question 56

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

Question 57

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

Question 58

A missing value is called a null value.

Question 59

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.

Question 60

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

Question 61

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

Question 62

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

Question 63

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.

Question 64

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

Question 65

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

Question 66

________ 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

Question 67

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

Question 68

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

Question 69

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

Question 70

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

A)1NF
B)2NF
C)3NF
D)BCNF

Question 71

An advantage of denormalization is ________.

A)faster updating
B)faster querying
C)faster copying of data
D)faster design

Question 72

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

A)relationships
B)candidate keys
C)updatability
D)efficiency

Question 73

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

Question 74

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

Question 75

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

Question 76

Read-only databases are used for ________.

A)updating
B)querying
C)data entry
D)user account management

Question 77

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

Question 78

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(*)

Question 79

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

Question 80

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(*)