Modern Database Management, 12e (Hoffer) Chapter 5 Physical Database Design and Performance 1) A requirement to begin designing physical files and databases is: A) normalized relations. B) physical tables created. C) implementation complete. D) all datatypes determined. Answer: A LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 2) A key decision in the physical design process is: A) ignoring the size of the database. B) selecting structures. C) deciding on the monitor. D) deciding the e-r diagrams. Answer: B LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 3) Designing physical files requires ________ of where and when data are used in various ways. A) maps B) descriptions C) keys D) hints Answer: B LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Concept AACSB: Information Technology 4) Database access frequencies are estimated from: A) transaction volumes. B) user logins. C) security violations. D) random number generation. Answer: A LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Concept AACSB: Information Technology 5) A detailed coding scheme recognized by system software for representing organizational data is called a(n): A) DBMS code. B) data type. C) SQL. D) DB layout. Answer: B LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 6) All of the following are objectives when selecting a data type EXCEPT: A) represent all possible values. B) improve data integrity. C) support all data manipulations. D) use a lot of storage space. Answer: D LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 7) All of the following are valid datatypes in Oracle 11g EXCEPT: A) varchar2. B) boolean. C) blob. D) number. Answer: B LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Concept AACSB: Information Technology 8) The smallest unit of application data recognized by system software is a: A) field. B) row. C) data type. D) column. Answer: A LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Concept AACSB: Information Technology 9) An appropriate datatype for one wanting a fixed-length type for last name would include: A) VarChar. B) Char. C) Blob. D) Date. Answer: B LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Application AACSB: Information Technology 10) An appropriate datatype for adding a sound clip would be: A) VarChar. B) Char. C) Blob. D) Date. Answer: C LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Application AACSB: Information Technology 11) Which of the following is an objective of selecting a data type? A) Represent a small number of possible values B) Maximize storage space C) Limit security D) Improve data integrity Answer: D LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Concept AACSB: Information Technology 12) In which data model would a code table appear? A) Conceptual B) Logical C) Physical D) Data layout Answer: C LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 13) An integrity control supported by a DBMS is: A) substitute estimates. B) security. C) range control. D) GUI guards. Answer: C LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 14) The value a field will assume unless the user enters an explicit value for an instance of that field is called a: A) default value. B) null value. C) range control. D) gurand. Answer: A LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 15) A method for handling missing data is to: A) substitute with random numbers for the missing data. B) track missing data with special reports. C) perform insensitivity testing. D) delete the associated row. Answer: B LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 16) Sensitivity testing involves: A) checking to see if your teeth hurt when you brush. B) seeing how accurate data are. C) checking to see if missing data will greatly impact results. D) deleting the associated row. Answer: C LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 17) The storage format for each attribute from the logical data model is chosen to maximize ________ and minimize storage space. A) query design B) programmer productivity C) data integrity D) data integration Answer: C LO: 5.3: Choose storage formats for attributes from a logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 18) Within Oracle, the named set of storage elements in which physical files for database tables may be stored is called a(n): A) extent. B) table. C) tablespace. D) partition. Answer: C LO: 5.3: Choose storage formats for attributes from a logical data model. Difficulty: Easy Classification: Concept AACSB: Information Technology 19) While Oracle has responsibility for managing data inside a tablespace, the tablespace as a whole is managed by the: A) user. B) CEO. C) XML. D) operating system. Answer: D LO: 5.3: Choose storage formats for attributes from a logical data model. Difficulty: Difficult Classification: Concept AACSB: Information Technology 20) A contiguous section of disk storage space is called a(n): A) track. B) sector. C) extent. D) tablespace. Answer: C LO: 5.3: Choose storage formats for attributes from a logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 21) A method to allow adjacent secondary memory space to contain rows from several tables is called: A) cluttering. B) clustering. C) concatenating. D) compiling. Answer: B LO: 5.3: Choose storage formats for attributes from a logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 22) A(n) ________ is a field of data used to locate a related field or record. A) key B) index C) lock D) pointer Answer: D LO: 5.4: Select an appropriate file organization by balancing various important design factors. Difficulty: Difficult Classification: Concept AACSB: Information Technology 23) A(n) ________ is a technique for physically arranging the records of a file on secondary storage devices. A) physical pointer B) retrieval program C) file organization D) update program Answer: C LO: 5.4: Select an appropriate file organization by balancing various important design factors. Difficulty: Easy Classification: Concept AACSB: Information Technology 24) Which type of file is most efficient with storage space? A) Sequential B) Hashed C) Indexed D) Clustered Answer: A LO: 5.4: Select an appropriate file organization by balancing various important design factors. Difficulty: Moderate Classification: Concept AACSB: Information Technology 25) Which type of file is easiest to update? A) Sequential B) Hashed C) Indexed D) Clustered Answer: B LO: 5.4: Select an appropriate file organization by balancing various important design factors. Difficulty: Moderate Classification: Concept AACSB: Information Technology 26) A factor to consider when choosing a file organization is: A) inefficient data retrieval. B) DDL. C) efficient storage. D) DML. Answer: C LO: 5.4: Select an appropriate file organization by balancing various important design factors. Difficulty: Moderate Classification: Concept AACSB: Information Technology 27) One field or combination of fields for which more than one record may have the same combination of values is called a(n): A) secondary key. B) index. C) composite key. D) linked key. Answer: A LO: 5.4: Select an appropriate file organization by balancing various important design factors. Difficulty: Moderate Classification: Concept AACSB: Information Technology 28) In which type of file is multiple key retrieval not possible? A) Sequential B) Hashed C) Indexed D) Clustered Answer: B LO: 5.5: Describe three important types of file organization. Difficulty: Moderate Classification: Concept AACSB: Information Technology 29) 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: A) hashed file organization. B) hash key. C) multi-indexed file organization. D) hash index table. Answer: D LO: 5.5: Describe three important types of file organization. Difficulty: Moderate Classification: Concept AACSB: Information Technology 30) A file organization where files are not stored in any particular order is considered a: A) hashed file organization. B) hash key. C) multi-indexed file organization. D) heap file organization. Answer: D LO: 5.5: Describe three important types of file organization. Difficulty: Moderate Classification: Concept AACSB: Information Technology 31) An index on columns from two or more tables that come from the same domain of values is called a: A) bitmap index. B) multivalued index. C) join index. D) transaction index. Answer: C LO: 5.6: Describe the purpose of indexes and the important considerations in selecting attributes to be indexed. Difficulty: Moderate Classification: Concept AACSB: Information Technology 32) A(n) ________ is a routine that converts a primary key value into a relative record number. A) record index calculator B) index pointer program C) hashing algorithm D) pointing algorithm Answer: C LO: 5.6: Describe the purpose of indexes and the important considerations in selecting attributes to be indexed. Difficulty: Easy Classification: Concept AACSB: Information Technology 33) A method that speeds query processing by running a query at the same time against several partitions of a table using multiprocessors is called: A) multiple partition queries. B) perpendicular query processing. C) parallel query processing. D) query optimization. Answer: C LO: 5.6: Describe the purpose of indexes and the important considerations in selecting attributes to be indexed. Difficulty: Easy Classification: Concept AACSB: Information Technology 34) 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: A) explain plan. B) show optimization. C) explain query. D) analyze query. Answer: A LO: 5.6: Describe the purpose of indexes and the important considerations in selecting attributes to be indexed. Difficulty: Difficult Classification: Concept AACSB: Information Technology 35) All of the following are common denormalization opportunities EXCEPT: A) two entities with a one-to-one relationship. B) a one-to-many relationship. C) a many-to-many relationship with nonkey attributes. D) reference data. Answer: B LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Difficult Classification: Concept AACSB: Information Technology 36) In most cases, the goal of ________ dominates the design process. A) efficient data processing B) security C) quick pointer updates D) shorter design times Answer: A LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 37) Distributing the rows of data into separate files is called: A) normalization. B) horizontal partitioning. C) vertical partitioning. D) file allocation. Answer: B LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 38) Horizontal partitioning makes sense: A) when different categories of a table's rows are processed separately. B) when less security is needed. C) when partitions must be organized the same. D) when only one category is allowed. Answer: A LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 39) An advantage of partitioning is: A) efficiency. B) remote optimization. C) extra space and update time. D) increase redundancy. Answer: A LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Easy Classification: Concept AACSB: Information Technology 40) A disadvantage of partitioning is: A) simplicity. B) remote optimization. C) extra space and update time. D) shorter technology spans. Answer: C LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Easy Classification: Concept AACSB: Information Technology 41) All of the following are horizontal partitioning methods in Oracle EXCEPT: A) key range partitioning. B) hash partitioning. C) multivalued partitioning. D) composite partitioning. Answer: C LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 42) ________ partitioning distributes the columns of a table into several separate physical records. A) Horizontal B) Crossways C) Vertical D) Final Answer: C LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 43) A form of denormalization where the same data are purposely stored in multiple places in the database is called: A) data duplication. B) data replication. C) advanced placement. D) horizontal partitioning. Answer: B LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 44) Requirements for response time, data security, backup and recovery are all requirements for physical design. Answer: TRUE LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Concept AACSB: Information Technology 45) One decision in the physical design process is selecting structures. Answer: TRUE LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 46) The logical database design always forms the best foundation for grouping attributes in the physical design. Answer: FALSE LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 47) Efficient database structures will be beneficial only if queries and the underlying database management system are tuned to properly use the structures. Answer: TRUE LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 48) SOX stands for the Sorbet-Oxford Act. Answer: FALSE LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 49) Adding notations to the EER diagram regarding data volumes and usage is of no value to the physical design process. Answer: FALSE LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Concept AACSB: Information Technology 50) The smallest unit of named application data is a record. Answer: FALSE LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Concept AACSB: Information Technology 51) One objective of selecting a data type is to minimize storage space. Answer: TRUE LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 52) The Number datatype would be appropriate for a zip code. Answer: FALSE LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Application AACSB: Information Technology 53) VarChar2 would be appropriate for a user that wanted a text datatype for LastName that would only consume the required space. Answer: TRUE LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Application AACSB: Information Technology 54) 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 LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Concept AACSB: Information Technology 55) A range control limits the set of permissible values that a field may assume. Answer: TRUE LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Concept AACSB: Information Technology 56) Sensitivity testing involves ignoring missing data unless knowing a value might significantly change results. Answer: TRUE LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Concept AACSB: Information Technology 57) One method to handle missing values is to substitute an exact value. Answer: FALSE LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Concept AACSB: Information Technology 58) An extent is a named portion of secondary memory allocated for the purpose of storing physical records. Answer: FALSE LO: 5.3: Choose storage formats for attributes from a logical data model. Difficulty: Easy Classification: Concept AACSB: Information Technology 59) A tablespace is a named set of disk storage elements in which physical files for the database tables may be stored. Answer: TRUE LO: 5.3: Choose storage formats for attributes from a logical data model. Difficulty: Easy Classification: Concept AACSB: Information Technology 60) A pointer is a field of data that can be used to locate a related field or record of data. Answer: TRUE LO: 5.4: Select an appropriate file organization by balancing various important design factors. Difficulty: Moderate Classification: Concept AACSB: Information Technology 61) A file organization is a named portion of primary memory. Answer: FALSE LO: 5.4: Select an appropriate file organization by balancing various important design factors. Difficulty: Difficult Classification: Concept AACSB: Information Technology 62) A key is a data structure used to determine the location of rows in a file that satisfy some condition. Answer: FALSE LO: 5.4: Select an appropriate file organization by balancing various important design factors. Difficulty: Moderate Classification: Concept AACSB: Information Technology 63) Fast data retrieval is one factor to consider when choosing a file organization for a particular database file. Answer: TRUE LO: 5.4: Select an appropriate file organization by balancing various important design factors. Difficulty: Easy Classification: Concept AACSB: Information Technology 64) A hashing algorithm is a routine that converts a primary key value into a relative record number. Answer: TRUE LO: 5.4: Select an appropriate file organization by balancing various important design factors. Difficulty: Moderate Classification: Concept AACSB: Information Technology 65) Clustering allows for adjacent secondary memory locations to contain rows from several tables. Answer: TRUE LO: 5.4: Select an appropriate file organization by balancing various important design factors. Difficulty: Moderate Classification: Concept AACSB: Information Technology 66) In a sequential file, the records are stored in sequence according to a primary key value. Answer: TRUE LO: 5.5: Describe three important types of file organization. Difficulty: Moderate Classification: Concept AACSB: Information Technology 67) In a Heap file organization, files are not stored in any particular order: Answer: TRUE LO: 5.5: Describe three important types of file organization. Difficulty: Moderate Classification: Concept AACSB: Information Technology 68) A join index is a combination of two or more indexes. Answer: FALSE LO: 5.6: Describe the purpose of indexes and the important considerations in selecting attributes to be indexed. Difficulty: Moderate Classification: Concept AACSB: Information Technology 69) Indexes are most useful on small, clustered files. Answer: FALSE LO: 5.6: Describe the purpose of indexes and the important considerations in selecting attributes to be indexed. Difficulty: Moderate Classification: Concept AACSB: Information Technology 70) 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 LO: 5.6: Describe the purpose of indexes and the important considerations in selecting attributes to be indexed. Difficulty: Moderate Classification: Concept AACSB: Information Technology 71) Using an index for attributes referenced in ORDER BY and GROUP BY clauses has no significant impact upon database performance. Answer: FALSE LO: 5.6: Describe the purpose of indexes and the important considerations in selecting attributes to be indexed. Difficulty: Moderate Classification: Concept AACSB: Information Technology 72) Denormalization is the process of transforming relations with variable-length fields into those with fixed-length fields. Answer: FALSE LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Easy Classification: Concept AACSB: Information Technology 73) Keeping the zip code with the city and state in a table is a typical form of denormalization. Answer: TRUE LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Easy Classification: Concept AACSB: Information Technology 74) Denormalization almost always leads to more storage space for raw data. Answer: TRUE LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Difficult Classification: Concept AACSB: Information Technology 75) Horizontal partitioning refers to the process of combining several smaller relations into a larger table. Answer: FALSE LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 76) Horizontal partitioning is very different from creating a supertype/subtype relationship. Answer: FALSE LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 77) Security is one advantage of partitioning. Answer: TRUE LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Easy Classification: Concept AACSB: Information Technology 78) Reduced uptime is a disadvantage of partitioning. Answer: FALSE LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Easy Classification: Concept AACSB: Information Technology 79) Hash partitioning spreads data evenly across partitions independent of any partition key value. Answer: TRUE LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 80) 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 LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 81) Vertical partitioning means distributing the columns of a table into several separate physical records. Answer: TRUE LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 82) Parallel query processing speed is not significantly different from running queries in a nonparallel mode. Answer: FALSE LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 83) Along with table scans, other elements of a query can be processed in parallel. Answer: TRUE LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 84) The query processor always knows the best way to process a query. Answer: FALSE LO: 5.7: Translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical data model. Difficulty: Moderate Classification: Concept AACSB: Information Technology 85) Discuss the critical decisions that must be made during physical database design. Answer: First, one must choose the storage format (or data types) of each attribute. These should be chosen to maximize data integrity and 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? LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Synthesis AACSB: Information Technology 86) 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. LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Synthesis AACSB: Information Technology 87) 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. LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Moderate Classification: Synthesis AACSB: Information Technology 88) 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. LO: 5.2: Describe the physical database design process, its objectives, and its deliverables. Difficulty: Easy Classification: Synthesis AACSB: Information Technology