Cart $0.00 0
No products in the cart.
MODERN DATABASE MANAGEMENT 12TH EDITION BY HOFFER - TEST BANK
digital downloadTest Bank

MODERN DATABASE MANAGEMENT 12TH EDITION BY HOFFER - TEST BANK

MODERN DATABASE MANAGEMENT 12TH EDITION BY HOFFER - TEST BANK   Instant Download - Complete Test Bank With Answers     Sample Questions Are Posted Below   Modern Database Management, 11e (Hoffer et al.) Chapter 5   Physical Database Design and Performance   1) A requirement to begin designing physical files and databases is: A) normalized …

$19.99

MODERN DATABASE MANAGEMENT 12TH EDITION BY HOFFER – TEST BANK

 

Instant Download – Complete Test Bank With Answers

 

 

Sample Questions Are Posted Below

 

Modern Database Management, 11e (Hoffer et al.)

Chapter 5   Physical Database Design and Performance

 

1) A requirement to begin designing physical files and databases is:

  1. A) normalized relations.
  2. B) definitions of each attribute.
  3. C) technology descriptions.
  4. D) all of the above.

Answer:  D

Diff: 1    Page Ref: 207

Topic:  Physical Database Design Process

AACSB:  Analytic Skills, Use of Information Technology

 

2) A key decision in the physical design process is:

  1. A) knowing the user base.
  2. B) selecting structures.
  3. C) deciding on the monitor.
  4. D) all of the above.

Answer:  B

Diff: 1    Page Ref: 208

Topic:  Physical Database Design Process

AACSB:  Analytic Skills, Use of Information Technology

 

3) Designing physical files requires ________ of where and when data are used in various ways.

  1. A) maps
  2. B) descriptions
  3. C) keys
  4. D) hints

Answer:  B

Diff: 2    Page Ref: 208

Topic:  Physical Database Design Process

AACSB:  Analytic Skills

 

4) The storage format for each attribute from the logical data model is chosen to maximize ________ and minimize storage space.

  1. A) query design
  2. B) programmer productivity
  3. C) data integrity
  4. D) data integration

Answer:  C

Diff: 2    Page Ref: 208

Topic:  Physical Database Design as a Basis for Regulatory Compliance

AACSB:  Analytic Skills

 

5) Database access frequencies are estimated from:

  1. A) transaction volumes.
  2. B) user logins.
  3. C) security violations.
  4. D) none of the above.

Answer:  A

Diff: 2    Page Ref: 210

Topic:  Physical Database Design Process

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Data Volume and Usage Analysis

 

6) A detailed coding scheme recognized by system software for representing organizational data is called a(n):

  1. A) DBMS code.
  2. B) data type.
  3. C) SQL.
  4. D) DB layout.

Answer:  B

Diff: 1    Page Ref: 211

Topic:  Designing Fields

AACSB:  Use of Information Technology

 

7) All of the following are objectives when selecting a data type EXCEPT:

  1. A) represent all possible values.
  2. B) improve data integrity.
  3. C) support all data manipulations.
  4. D) use a lot of storage space.

Answer:  D

Diff: 1    Page Ref: 211

Topic:  Designing Fields

AACSB:  Use of Information Technology

Subtopic:  Scope of Business Rules

 

8) All of the following are valid datatypes in Oracle 11g EXCEPT:

  1. A) varchar2.
  2. B) boolean.
  3. C) blob.
  4. D) number.

Answer:  B

Diff: 2    Page Ref: 211

Topic:  Designing Fields

AACSB:  Use of Information Technology

Subtopic:  Choosing Data Types

 

 

9) The smallest unit of application data recognized by system software is a:

  1. A) field.
  2. B) row.
  3. C) data type.
  4. D) column.

Answer:  A

Diff: 2    Page Ref: 210

Topic:  Designing Fields

AACSB:  Reflective Thinking

10) Which of the following is an objective of selecting a data type?

  1. A) Represent a small number of possible values
  2. B) Maximize storage space
  3. C) Limit security
  4. D) Improve data integrity

Answer:  D

Diff: 2    Page Ref: 211

Topic:  Designing Fields

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Choosing Data Types

 

11) In which data model would a code table appear?

  1. A) Conceptual
  2. B) Logical
  3. C) Physical
  4. D) Data layout

Answer:  C

Diff: 2    Page Ref: 212

Topic:  Designing Fields

AACSB:  Use of Information Technology

Subtopic:  Coding Techniques

 

12) An integrity control supported by a DBMS is:

  1. A) substitute estimates.
  2. B) security.
  3. C) range control.
  4. D) GUI guards.

Answer:  C

Diff: 1    Page Ref: 212

Topic:  Designing Fields

AACSB:  Use of Information Technology

Subtopic:  Controlling Data Integrity – A Foundation for Sarbanes-Oxley Compliance

 

 

13) The value a field will assume unless the user enters an explicit value for an instance of that field is called a(n):

  1. A) default value.
  2. B) null value.
  3. C) range control.
  4. D) gurand.

Answer:  A

Diff: 1    Page Ref: 212

Topic:  Designing Fields

AACSB:  Use of Information Technology

Subtopic:  Controlling Data Integrity – A Foundation for Sarbanes-Oxley Compliance

14) A method for handling missing data is to:

  1. A) substitute and estimate for the missing data.
  2. B) track missing data with special reports.
  3. C) perform sensitivity testing.
  4. D) all of the above.

Answer:  D

Diff: 2    Page Ref: 213

Topic:  Designing Fields

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Handling Missing Data

 

15) Sensitivity testing involves:

  1. A) checking to see if your teeth hurt when you brush.
  2. B) seeing how accurate data are.
  3. C) checking to see if missing data will greatly impact results.
  4. D) none of the above.

Answer:  C

Diff: 2    Page Ref: 213

Topic:  Designing Fields

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Handling Missing Data

 

16) All of the following are common denormalization opportunities EXCEPT:

  1. A) two entities with a one-to-one relationship.
  2. B) a one-to-many relationship.
  3. C) a many-to-many relationship with nonkey attributes.
  4. D) reference data.

Answer:  B

Diff: 2    Page Ref: 214-216

Topic:  Denormalizing and Partitioning Data

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Denormalization

 

 

17) In most cases the goal of ________ dominates the design process.

  1. A) efficient data processing
  2. B) security
  3. C) quick pointer updates
  4. D) shorter design times

Answer:  A

Diff: 2    Page Ref: 214

Topic:  Denormalizing and Partitioning Data

AACSB:  Use of Information Technology

Subtopic:  Denormalization

18) Distributing the rows of data into separate files is called:

  1. A) normalization.
  2. B) horizontal partitioning.
  3. C) vertical partitioning.
  4. D) file allocation.

Answer:  B

Diff: 1    Page Ref: 217

Topic:  Denormalizing and Partitioning Data

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Partitioning

 

19) Horizontal partitioning makes sense:

  1. A) when different categories of a table’s rows are processed separately.
  2. B) when less security is needed.
  3. C) when partitions must be organized the same.
  4. D) when all of the above are true.

Answer:  A

Diff: 2    Page Ref: 217

Topic:  Denormalizing and Partitioning Data

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Partitioning

 

20) An advantage of partitioning is:

  1. A) efficiency.
  2. B) remote optimization.
  3. C) extra space and update time.
  4. D) both A and B.

Answer:  A

Diff: 1    Page Ref: 218

Topic:  Denormalizing and Partitioning Data

AACSB:  Use of Information Technology

Subtopic:  Partitioning

 

 

21) A disadvantage of partitioning is:

  1. A) simplicity.
  2. B) remote optimization.
  3. C) extra space and update time.
  4. D) shorter technology spans.

Answer:  C

Diff: 1    Page Ref: 218

Topic:  Denormalizing and Partitioning Data

AACSB:  Use of Information Technology

Subtopic:  Partitioning

22) All of the following are horizontal partitioning methods in Oracle EXCEPT:

  1. A) key range partitioning.
  2. B) hash partitioning.
  3. C) multivalued partitioning.
  4. D) composite partitioning.

Answer:  C

Diff: 2    Page Ref: 218, 219

Topic:  Denormalizing and Partitioning Data

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Partitioning

 

23) ________ partitioning distributes the columns of a table into several separate physical records.

  1. A) Horizontal
  2. B) Crossways
  3. C) Vertical
  4. D) Final

Answer:  C

Diff: 1    Page Ref: 219

Topic:  Denormalizing and Partitioning Data

AACSB:  Use of Information Technology

Subtopic:  Partitioning

 

24) Another form of denormalization where the same data are stored in multiple places in the database is called:

  1. A) data duplication.
  2. B) data replication.
  3. C) advanced placement.
  4. D) horizontal partitioning.

Answer:  B

Diff: 2    Page Ref: 219

Topic:  Denormalizing and Partitioning Data

AACSB:  Use of Information Technology

Subtopic:  Partitioning

 

 

25) Within Oracle, the named set of storage elements in which physical files for database tables may be stored is called a(n):

  1. A) extent.
  2. B) table.
  3. C) tablespace.
  4. D) partition.

Answer:  C

Diff: 1    Page Ref: 220

Topic:  Designing Physical Database Files

AACSB:  Use of Information Technology

26) While Oracle has responsibility for managing data inside a tablespace, the tablespace as a whole is managed by the:

  1. A) user.
  2. B) database administrator.
  3. C) application developer.
  4. D) operating system.

Answer:  D

Diff: 3    Page Ref: 220

Topic:  Designing Physical Database Files

AACSB:  Use of Information Technology

 

27) A contiguous section of disk storage space is called a(n):

  1. A) track.
  2. B) sector.
  3. C) extent.
  4. D) tablespace.

Answer:  C

Diff: 2    Page Ref: 220

Topic:  Designing Physical Database Files

AACSB:  Use of Information Technology

 

28) A(n) ________ is a field of data used to locate a related field or record.

  1. A) key
  2. B) index
  3. C) lock
  4. D) pointer

Answer:  D

Diff: 2    Page Ref: 225

Topic:  File Organizations

AACSB:  Use of Information Technology

Subtopic:  Hashed File Organization

 

 

29) A(n) ________ is a technique for physically arranging the records of a file on secondary storage devices.

  1. A) physical pointer
  2. B) retrieval program
  3. C) file organization
  4. D) update program

Answer:  C

Diff: 1    Page Ref: 220

Topic:  File Organizations

AACSB:  Use of Information Technology

 

30) A factor to consider when choosing a file organization is:

  1. A) fast data retrieval.
  2. B) security.
  3. C) efficient storage.
  4. D) all of the above.

Answer:  D

Diff: 2    Page Ref: 220

Topic:  File Organizations

AACSB:  Analytic Skills, Use of Information Technology

31) One field or combination of fields for which more than one record may have the same combination of values is called a(n):

  1. A) secondary key.
  2. B) index.
  3. C) composite key.
  4. D) linked key.

Answer:  A

Diff: 1    Page Ref: 221

Topic:  File Organizations

AACSB:  Use of Information Technology

Subtopic:  Indexed File Organizations

 

32) An index on columns from two or more tables that come from the same domain of values is called a:

  1. A) bitmap index.
  2. B) multivalued index.
  3. C) join index.
  4. D) transaction index.

Answer:  C

Diff: 1    Page Ref: 223

Topic:  File Organizations

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Indexed File Organizations

 

 

33) A(n) ________ is a routine that converts a primary key value into a relative record number.

  1. A) record index calculator
  2. B) index pointer program
  3. C) hashing algorithm
  4. D) pointing algorithm

Answer:  C

Diff: 1    Page Ref: 224

Topic:  File Organizations

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Hashed File Organization

 

34) In which type of file is multiple key retrieval not possible?

  1. A) Sequential
  2. B) Hashed
  3. C) Indexed
  4. D) Clustered

Answer:  B

Diff: 2    Page Ref: 226

Topic:  File Organizations

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Hashed File Organization

35) A file organization that uses hashing to map a key into a location in an index where there is a pointer to the actual data record matching the hash key is called a:

  1. A) hashed file organization.
  2. B) hash key.
  3. C) multi-indexed file organization.
  4. D) hash index table.

Answer:  D

Diff: 2    Page Ref: 225

Topic:  File Organizations

AACSB:  Use of Information Technology

Subtopic:  Hashed File Organization

 

36) Which type of file is most efficient with storage space?

  1. A) Sequential
  2. B) Hashed
  3. C) Indexed
  4. D) Clustered

Answer:  A

Diff: 2    Page Ref: 226

Topic:  Designing Physical Database Files

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Summary of File Organizations

 

 

37) Which type of file is easiest to update?

  1. A) Sequential
  2. B) Hashed
  3. C) Indexed
  4. D) Clustered

Answer:  B

Diff: 2    Page Ref: 226

Topic:  Designing Physical Database Files

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Summary of File Organizations

 

38) A method to allow adjacent secondary memory space to contain rows from several tables is called:

  1. A) cluttering.
  2. B) clustering.
  3. C) concatenating.
  4. D) compiling.

Answer:  B

Diff: 2    Page Ref: 226, 227

Topic:  Designing Physical Database Files

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Clustering Files

39) A rule of thumb for choosing indexes is to:

  1. A) be careful indexing attributes that may be null.
  2. B) index each primary key of each table.
  3. C) use an index when there is variety in attribute values.
  4. D) all of the above.

Answer:  D

Diff: 1    Page Ref: 229

Topic:  Using and Selecting Indexes

AACSB:  Analytic Skills, Use of Information Technology

 

40) A method that speeds query processing by running a query at the same time against several partitions of a table using multiprocessors is called:

  1. A) multiple partition queries.
  2. B) perpendicular query processing.
  3. C) parallel query processing.
  4. D) query optimization.

Answer:  C

Diff: 2    Page Ref: 230, 231

Topic:  Optimizing for Query Performance

AACSB:  Use of Information Technology

Subtopic:  Parallel Query Processing

 

 

41) A command used in Oracle to display how the query optimizer intends to access indexes, use parallel servers and join tables to prepare query results is the:

  1. A) explain plan.
  2. B) show optimization.
  3. C) explain query.
  4. D) analyze query.

Answer:  A

Diff: 2    Page Ref: 231

Topic:  Optimizing for Query Performance

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Overriding Automatic Query Optimization

 

42) Requirements for response time, data security, backup and recovery are all requirements for physical design.

Answer:  TRUE

Diff: 2    Page Ref: 207

Topic:  Physical Database Design as a Basis for Regulatory Compliance

AACSB:  Analytic Skills, Reflective Thinking

 

43) One decision in the physical design process is selecting structures.

Answer:  TRUE

Diff: 1    Page Ref: 208

Topic:  Physical Database Design Process

AACSB:  Analytic Skills, Use of Information Technology

 

44) The logical database design always forms the best foundation for grouping attributes in the physical design.

Answer:  FALSE

Diff: 1    Page Ref: 208

Topic:  Physical Database Design Process

AACSB:  Analytic Skills, Use of Information Technology

45) Efficient database structures will be beneficial only if queries and the underlying database management system are tuned to properly use the structures.

Answer:  TRUE

Diff: 1    Page Ref: 208

Topic:  Physical Database Design Process

AACSB:  Analytic Skills

 

46) SOX stands for the Sorbet-Oxford Act.

Answer:  FALSE

Diff: 1    Page Ref: 208

Topic:  Physical Database Design as a Basis for Regulatory Compliance

AACSB:  Reflective Thinking

 

 

47) Adding notations to the EER diagram regarding data volumes and usage is of no value to the physical design process.

Answer:  FALSE

Diff: 2    Page Ref: 209

Topic:  Physical Database Design Process

AACSB:  Analytic Skills, Use of Information Technology

 

48) The smallest unit of named application data is a record.

Answer:  FALSE

Diff: 1    Page Ref: 210

Topic:  Designing Fields

AACSB:  Analytic Skills, Use of Information Technology

 

49) One objective of selecting a data type is to minimize storage space.

Answer:  TRUE

Diff: 1    Page Ref: 211

Topic:  Designing Fields

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Choosing Data Types

 

50) A default value is the value that a field will always assume, regardless of what the user enters for an instance of that field.

Answer:  FALSE

Diff: 2    Page Ref: 212

Topic:  Designing Fields

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Controlling Data Integrity

 

51) A range control limits the set of permissible values that a field may assume.

Answer:  TRUE

Diff: 1    Page Ref: 212

Topic:  Designing Fields

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Controlling Data Integrity

 

52) One method to handle missing values is to substitute an exact value.

Answer:  FALSE

Diff: 2    Page Ref: 214

Topic:  Designing Fields

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Handling Missing Data

 

53) Sensitivity testing involves ignoring missing data unless knowing a value might significantly change results.

Answer:  TRUE

Diff: 1    Page Ref: 213

Topic:  Designing Fields

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Handling Missing Data

 

54) Denormalization is the process of transforming relations with variable-length fields into those with fixed-length fields.

Answer:  FALSE

Diff: 1    Page Ref: 214

Topic:  Denormalizing and Partitioning Data

AACSB:  Use of Information Technology

Subtopic:  Denormalization

 

55) Keeping the zip code with the city and state in a table is a typical form of denormalization.

Answer:  TRUE

Diff: 1    Page Ref: 214

Topic:  Denormalizing and Partitioning Data

AACSB:  Analytic Skills

Subtopic:  Denormalization

 

56) Denormalization almost always leads to more storage space for raw data.

Answer:  TRUE

Diff: 1    Page Ref: 216

Topic:  Denormalizing and Partitioning Data

AACSB:  Use of Information Technology

Subtopic:  Denormalization

 

57) Horizontal partitioning refers to the process of combining several smaller relations into a larger table.

Answer:  FALSE

Diff: 2    Page Ref: 217

Topic:  Denormalizing and Partitioning Data

AACSB:  Use of Information Technology

Subtopic:  Denormalization

 

58) Horizontal partitioning is very different from creating a supertype/subtype relationship.

Answer:  FALSE

Diff: 2    Page Ref: 217

Topic:  Denormalizing and Partitioning Data

AACSB:  Use of Information Technology

Subtopic:  Denormalization

 

 

59) Security is one advantage of partitioning.

Answer:  TRUE

Diff: 2    Page Ref: 218

Topic:  Denormalizing and Partitioning Data

AACSB:  Use of Information Technology

Subtopic:  Partitioning

60) Reduced uptime is a disadvantage of partitioning.

Answer:  FALSE

Diff: 2    Page Ref: 218

Topic:  Denormalizing and Partitioning Data

AACSB:  Use of Information Technology

Subtopic:  Partitioning

 

61) Hash partitioning spreads data evenly across partitions independent of any partition key value.

Answer:  TRUE

Diff: 1    Page Ref: 218

Topic:  Denormalizing and Partitioning Data

AACSB:  Use of Information Technology

Subtopic:  Partitioning

 

62) Free range partitioning is a type of horizontal partitioning in which each partition is defined by a range of values for one or more columns in the normalized table.

Answer:  FALSE

Diff: 2    Page Ref: 217

Topic:  Denormalizing and Partitioning Data

AACSB:  Use of Information Technology

Subtopic:  Partitioning

 

63) Vertical partitioning means distributing the columns of a table into several separate physical records.

Answer:  TRUE

Diff: 1    Page Ref: 219

Topic:  Denormalizing and Partitioning Data

AACSB:  Use of Information Technology

Subtopic:  Partitioning

 

64) An extent is a named portion of secondary memory allocated for the purpose of storing physical records.

Answer:  FALSE

Diff: 1    Page Ref: 220

Topic:  Designing Physical Database Files

AACSB:  Use of Information Technology

 

 

65) A tablespace is a named set of disk storage elements in which physical files for the database tables may be stored.

Answer:  TRUE

Diff: 1    Page Ref: 220

Topic:  Designing Physical Database Files

AACSB:  Use of Information Technology

 

66) A pointer is a field of data that can be used to locate a related field or record of data.

Answer:  TRUE

Diff: 1    Page Ref: 225

Topic:  Evolution of Databases

AACSB:  Use of Information Technology

Subtopic:  Hashed File Organization

67) A file organization is a named portion of primary memory.

Answer:  FALSE

Diff: 2    Page Ref: 220

Topic:  Designing Physical Database Files

AACSB:  Use of Information Technology

Subtopic:  File Organizations

 

68) Fast data retrieval is one factor to consider when choosing a file organization for a particular database file.

Answer:  TRUE

Diff: 2    Page Ref: 220

Topic:  Denormalizing and Partitioning Data

AACSB:  Reflective Thinking

Subtopic:  Partitioning

 

69) In a sequential file, the records are stored in sequence according to primary key.

Answer:  TRUE

Diff: 1    Page Ref: 221

Topic:  Designing Physical Database Files

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Sequential File Organizations

 

70) A key is a data structure used to determine the location of rows in a file that satisfy some condition.

Answer:  FALSE

Diff: 1    Page Ref: 221

Topic:  Designing Physical Database Files

AACSB:  Use of Information Technology

Subtopic:  Indexed File Organizations

 

 

71) A join index is a combination of two or more indexes.

Answer:  FALSE

Diff: 1    Page Ref: 223

Topic:  Designing Physical Database Files

AACSB:  Use of Information Technology

Subtopic:  Indexed File Organizations

 

72) A hashing algorithm is a routine that converts a primary key value into a relative record number.

Answer:  TRUE

Diff: 1    Page Ref: 224

Topic:  Designing Physical Database Files

AACSB:  Use of Information Technology

Subtopic:  Hashed File Organization

 

73) Clustering allows for adjacent secondary memory locations to contain rows from several tables.

Answer:  TRUE

Diff: 2    Page Ref: 226, 227

Topic:  Designing Physical Database Files

AACSB:  Use of Information Technology

Subtopic:  Clustering Files

74) Indexes are most useful on small, clustered files.

Answer:  FALSE

Diff: 2    Page Ref: 229

Topic:  Using and Selecting Indexes

AACSB:  Use of Information Technology

 

75) Indexes are most useful for columns that frequently appear in WHERE clauses of SQL commands, either to qualify the rows to select or for linking.

Answer:  TRUE

Diff: 2    Page Ref: 229

Topic:  Using and Selecting Indexes

AACSB:  Use of Information Technology

Subtopic:  When to Use Indexes

 

76) Using an index for attributes referenced in ORDER BY and GROUP BY clauses has no significant impact upon database performance.

Answer:  FALSE

Diff: 2    Page Ref: 229

Topic:  Using and Selecting Indexes

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  When to Use Indexes

 

 

77) Parallel query processing speed is not significantly different from running queries in a non-parallel mode.

Answer:  FALSE

Diff: 2    Page Ref: 230, 231

Topic:  Optimizing for Query Performance

AACSB:  Use of Information Technology

Subtopic:  Parallel Query Processing

 

78) Along with table scans, other elements of a query can be processed in parallel.

Answer:  TRUE

Diff: 2    Page Ref: 231

Topic:  Optimizing for Query Performance

AACSB:  Use of Information Technology

Subtopic:  Parallel Query Processing

 

79) The query processor always knows the best way to process a query.

Answer:  FALSE

Diff: 3    Page Ref: 231

Topic:  Optimizing for Query Performance

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Overriding Automatic Query Optimization

80) Discuss the critical decisions that must be made during physical database design.

Answer:  There are several critical decisions which must be made during the physical database design process. First, one must choose the storage format (or data types) of each attribute. These should be chosen to maximize data integrity and to minimize storage space. Next, one must decide how to group the attributes from the logical model into tables. One must also decide upon the file organization to be used to store records and also think about protecting data and how to recover if errors are found. Finally, one has to think about indexing and query optimization. What types of indexes will be required for the most efficient retrieval? How can queries be optimized?

Diff: 2    Page Ref: 207, 208

Topic:  Physical Database Design Process

AACSB:  Analytic Skills, Use of Information Technology

 

81) Discuss why physical database design is a foundation for compliance with regulations on financial reporting.

Answer:  Careful physical design enables an organization to demonstrate that data are accurate and well protected. Laws such as Sarbanes-Oxley and Basel II have been enacted to protect investors by requiring an internal control report within each financial report. Without a good physical design, an organization would be hard pressed to prove that its data are accurate.

Diff: 2    Page Ref: 208

Topic:  Physical Database Design as a Basis for Regulatory Compliance

AACSB:  Reflective Thinking

 

 

82) Discuss the rationale behind data volume and usage analysis.

Answer:  Data volume and frequency of use statistics are important inputs to the database design process. One has to maintain a good understanding of the size and usage patterns throughout the lifecycle of the database in order to plan the design.

Diff: 2    Page Ref: 209

Topic:  Physical Database Design Process

AACSB:  Analytic Skills, Reflective Thinking

Subtopic:  Data Volume and Usage Analysis

 

83) Explain how one goes about choosing data types.

Answer:  Selecting a datatype involves four objectives that have different levels of importance for different applications. One must represent all possible values, improve data integrity, support all data manipulations and minimize storage space.

Diff: 2    Page Ref: 211

Topic:  Designing Fields

AACSB:  Analytic Skills

Subtopic:  Choosing Data Types

 

84) Discuss coding techniques and how they could be used.

Answer:  Coding techniques are used in cases when attributes have a sparse set of values or have large values which would negatively impact storage space. In this case, one can translate the values into codes. For example, one may have a list of colors for a car, which would have a limited number of values. In this case, one could create a lookup table for color and then replace the value for color with a code.

Diff: 2    Page Ref: 212

Topic:  Designing Fields

AACSB:  Analytic Skills

Subtopic:  Coding Techniques

85) Discuss how data integrity can be controlled.

Answer:  In many database management systems, data integrity controls can be built into the field. The data type is one integrity control. Another integrity control is default values, where one specifies the value that the field will take if no data has been entered. Range control is another data integrity constraint which indicates the upper and lower bounds for the field. Null value control allows one to specify whether null values are allowed or not in the field. Finally, referential integrity ensures that any value entered into a foreign key must have a corresponding value in the primary key of the related table. This ensures that there are no misplaced values in the foreign key.

Diff: 2    Page Ref: 212, 213

Topic:  Designing Fields

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Controlling Data Integrity

 

 

86) How are missing values generally handled in database management systems?

Answer:  When dealing with missing data, there are many options. Two integrity controls (default value and null control) will handle this problem. One also could substitute an estimate for the missing value which would then be stored in place of the missing value (and marked as an estimate). Another possible method is to use triggers to track missing values and create a log which can be viewed later. One other method is to perform sensitivity testing, which will tell us whether having missing data greatly impacts results. If results are not impacted, then the missing values are ignored.

Diff: 2    Page Ref: 213

Topic:  Designing Fields

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Handling Missing Data

 

87) Discuss three possible opportunities for denormalization.

Answer:  There are several possible situations when denormalization makes sense. When we have two entities with a one-to-one relationship and high access frequency between them, then it makes sense to combine them into one entity. If one entity is optional, then there will be fields with null values in the combined entity.

Another scenario is when you have an associative entity with nonkey attributes. If there is a high frequency of join operations between the entities, it might be more efficient to combine the associative entity with one of the other entities.

The third case is reference data. If we have a 1:M relationship and the entity on the one side does not participate in any other relationships, then it might be wise to merge the entities.

Diff: 2    Page Ref: 214-216

Topic:  Denormalizing and Partitioning Data

AACSB:  Analytic Skills, Reflective Thinking

Subtopic:  Denormalization

88) Discuss the advantages and disadvantages of partitioning.

Answer:  Partitioning data has several advantages. First, queries are more efficient since data queried together are stored close to one another. Also, each partition of data can be stored in such a way as to optimize its own use. Partitioned data is more secure, since it is much easier to isolate data and make it available only to a group of users. Since smaller files take less time to backup and recover, it is easier to keep a partitioned database up and running. Also, the effects of damage are isolated, since other files (partitions) may not be affected by one damaged file. It is also easier to load balance, since the smaller files can be spread across multiple disks.

Some disadvantages include inconsistent access speed. Different partitions may have different access times, depending upon physical storage of the file. Also, partitioning adds complexity for the programmer. Although to the user, partitioning appears transparent, this is often not the case for a programmer. Finally, data may be replicated across multiple partitions, which will require more storage space and possibly more time for updates.

Diff: 2    Page Ref: 218

Topic:  Denormalizing and Partitioning Data

AACSB:  Analytic Skills

Subtopic:  Partitioning

 

 

89) When would you use horizontal partitioning, and when would you use vertical partitioning?

Answer:  Horizontal partitioning is best used when different categories of rows are processed differently. For example, if you would like to have a report for each region, then it might make sense to partition the data by region. This type of partitioning will increase query performance. Horizontal partitioning is most often done by using a single column value or a date.

Vertical partitioning splits the columns of a table up, repeating the primary key. This would best be used for only allowing access by a group of users to a portion of the data. Of course, in both cases, tables could be joined to retrieve all data.

Diff: 2    Page Ref: 217-219

Topic:  Denormalizing and Partitioning Data

AACSB:  Analytic Skills, Reflective Thinking

Subtopic:  Partitioning

 

90) List the seven factors to consider when choosing a file organization for a particular file in a database.

Answer:

The seven factors are:

  1. Fast data retrieval
  2. High throughput for processing data input and maintenance transactions
  3. Efficient use of storage space
  4. Protection from failures or data loss
  5. Minimizing need for reorganization
  6. Accommodating growth
  7. Security from unauthorized use

Diff: 2    Page Ref: 220

Topic:  File Organizations

AACSB:  Analytic Skills

91) Explain what a tablespace is and how it is used.

Answer:  A tablespace is a named logical storage unit in which data from one or more tables, views, and indexes can be stored. A tablespace is mapped to one or more physical files in the OS and can be set to increase in size automatically. A tablespace can, in many ways, be thought of as its own file system available to the RDBMS.

Diff: 2    Page Ref: 220

Topic:  Designing Physical Database Files

AACSB:  Analytic Skills, Use of Information Technology

 

92) Explain the differences between sequential and indexed file organizations.

Answer:  In sequential file organization, the records are stored sequentially according to a primary key value. In index file organizations, the records are stored either sequentially or non-sequentially. An index is created that allows the application software to locate individual records. The index can be thought of as a card catalog.

Diff: 1    Page Ref: 221

Topic:  File Organizations

AACSB:  Analytic Skills

 

 

93) What is a hashing algorithm and how is it used in hashed file organizations?

Answer:  A hashing algorithm is a routine which converts a primary key value into a record address. Hashing algorithms are typically mathematical routines which perform computations such as dividing a primary key by a prime number and then using the remainder for a storage location. The hashing algorithm is used in a file organization to determine where to store the record. One limitation of this is that only one key value can be used for an index.

Diff: 3    Page Ref: 224, 225

Topic:  File Organizations

AACSB:  Reflective Thinking, Use of Information Technology

Subtopic:  Hashed File Organization

 

94) What is clustering?

Answer:  Clustering is a technique used in some database management systems (such as Oracle) to allow adjacent secondary memory space to contain rows from several tables. A cluster is defined by the tables used as well as the column or columns used for clustering. To create a cluster in Oracle, for example, we would create a cluster first with a create cluster command and then reference the cluster when we create each individual table.

Diff: 2    Page Ref: 226, 227

Topic:  File Organizations

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Clustering Files

 

95) Explain what primary, secondary and unique key indexes are.

Answer:  A primary key index is automatically generated when a table is created (provided that a primary key is specified). The primary key index identifies uniquely each row of the table. Thus, there is one entry in the primary key index for each row. A unique key index is similar, except that we have to define this with a separate create index command. A secondary key is nonunique. So, for example, we might create a secondary index on state. Since many records would have the same state, this is non-unique.

Diff: 2    Page Ref: 228

Topic:  File Organizations

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Using and Selecting Indexes

96) Discuss when it is best to use indexes.

Answer:  You should always specify a primary key index for each table. Also, indexes are most useful on larger tables. Columns that frequently appear in WHERE clauses of SQL statements are good candidates for indexes as well as those fields referenced by WHERE clauses and GROUP BY clauses. Any field where there is a wide variety of values is also a good candidate. One should use caution when creating indexes, however, since indexes do require overhead and must be updated for each insert, delete and update. This can cause significant overhead if there are a large number of transactions taking place against the database.

Diff: 2    Page Ref: 229

Topic:  File Organizations

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  When to Use Indexes

 

97) What is parallel query processing and how is it useful?

Answer:  Since many of today’s computers have either multiple processors or multi-core processors, many database management systems have introduced parallel query processing. With parallel query processing, a query can be broken apart and partitions can be run separately on each processor. The partitions have to be defined in advance by the database designer. The advantage to parallel query processing is speed, since the query can be done in much less time in parallel. The query has to be tuned to achieve the best degree of parallelism.

Diff: 2    Page Ref: 230, 231

Topic:  Designing a Database for Optimal Query Performance

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Parallel Query Processing

 

98) Why would you want to override automatic query optimization?

Answer:  Sometimes, the query designer may know information that can be overlooked by the query optimizer. Often, in the course of testing queries, one may find that it is actually faster not to use a certain index or to use a different index. When this is the case, database management systems such as Oracle include a facility to override the query optimizer, called query hints.

Diff: 2    Page Ref: 231

Topic:  Designing a Database for Optimal Query Performance

AACSB:  Analytic Skills, Use of Information Technology

Subtopic:  Overriding Automatic Query Optimization

 

 

Additional information

Add Review

Your email address will not be published. Required fields are marked *

Related Products

Quick Links
Contact Information

511 SW 10th Ave 1206, Portland, OR, United States

Pinterest

Nurse Guide Hub

©2026 – All Rights Reserved