Database Processing, 15e (Kroenke)
Chapter 4: Database Design Using Normalization
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.
2) The first step in assessing table structure is to count rows and examine columns.
3) To count the number of rows in a table, use COUNT(ROWS).
4) To determine the number and type of columns in a table, use COUNT(*).
5) To limit the number of rows retrieved from a table, use the TOP keyword.
6) When examining data values as a part of assessing table structure, you should determine two
types of dependencies: functional dependencies and multivalued dependencies.
7) When examining data values as a part of assessing table structure, determine three types of
keys: the primary key, any candidate keys, and any foreign keys.
8) The second step in assessing table structure is to examine data values and determine
dependencies and keys.
9) When examining data values as a part of assessing table structure, determine functional
dependencies.
10) When examining data values as a part of assessing table structure, determine multivalued
dependencies.
11) When examining data values as a part of assessing table structure, determine the table’s
primary key.
12) When examining data values as a part of assessing table structure, there is no need to
determine candidate keys other than the table’s primary key.
3
13) When examining data values as a part of assessing table structure, there is no need to
determine foreign keys.
14) The third step in assessing table structure is to check the validity of presumed referential
integrity constraints.
15) The elimination of modification anomalies and the reduction of duplicated data are
advantages of normalization.
16) Database design varies depending on whether you’re building an updatable database or a
read-only database.
17) Normalization eliminates modification anomalies and data duplication.
18) The presence of one or more foreign keys in a relation means that we cannot eliminate
duplicated data in that table.
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.
20) Relations are sometimes left unnormalized to improve performance.
21) Relations in BCNF have no modification anomalies regarding functional dependencies.
22) A defining requirement for BCNF is that every determinant must be a candidate key.
23) The INSERT statement can be used to populate normalized tables.
24) The DELETE TABLE statement can be used to remove unneeded tables after the normalized
tables are created and populated.
25) SQL statements that can be used to create referential integrity statements for normalized
tables are created during the normalization process.
26) United States ZIP codes are a classic example of unneeded normalization.
27) Multivalued dependencies create anomalies that must always be eliminated.
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.
29) To eliminate multivalued dependencies, normalize your tables so that they are all in BCNF.
30) Creating a read-only database is a job often given to beginning database professionals.
31) Read-only databases are often updated.
32) Design guidelines and priorities are the same whether you’re working with an updatable
database or a read-only database.
33) Normalization is an advantage for a read-only database.
34) Denormalization is the process of joining previously normalized tables back together.
35) Denormalization reduces the complexity of the SQL statements needed in an application to
read required data.
36) Denormalization involves joining the data together and storing it in a table.
37) Read-only databases seldom use more than one copy of a set of the same data.
38) Read-only databases often use several copies of a set of the same data, where each copy is
modified for a specific use.
39) Multivalued dependencies show up under a different name as the multivalued, multicolumn
problem.
40) The multivalued, multicolumn problem occurs when a set of columns are used to store data
that should actually be in one column.
41) The multivalue, multicolumn problem is just another form of a multivalued dependency.
42) 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.
43) When you are creating a database from existing data, you will have only minor problems
with inconsistent values.
44) An inconsistent values problem is created when different users have coded the same data
entries differently.
45) The problem of misspelled data entries is an entirely different problem than the inconsistent
values problem.
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.
47) A missing value is called a null value.
48) Null values are a problem because they are ambiguous.
49) A null value in a column may indicate that there is no appropriate value for that attribute.
50) A null value in a column may indicate that there is an appropriate value for that attribute, but
it is unknown.
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.
52) Most DBMS products will let you define a primary key on a column that contains null
values.
53) The IS NULL keyword can be used to count the number of nulls in a column.
54) General-purpose remarks columns rarely contain important data.
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.
56) If you see a column name Notes, it is likely that this is a general-purpose remarks column.
57) 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
58) 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
59) The second step in assessing table structure includes ________.
A) counting rows
B) examining columns
C) examining data values
D) A and B
60) During the second step of assessing table structure, you are trying to determine ________.
A) multivalued dependencies
B) data types
C) missing values
D) subqueries
61) During the second step of assessing table structure, you are trying to determine ________.
A) relationships
B) candidate keys
C) updatability
D) efficiency