Q&A Hero

Q: In a typical star schema, each dimension record is related to thousands of ____ records.a. attribute b. fact c. keyd. star

Q: Fact and dimension tables are related by ____ keys.a. shared b. primary c. foreignd. linked

Q: The attribute hierarchyprovides a top-down data organization that is used for two main purposes: ____ and drill-down/roll-up data analysis.a. hierarchy b. de-normalization c. normalizationd. aggregation

Q: The ____ dimension of a data cube provides a framework from which sales patterns can be analyzed and possibly predicted.a. time b. cube c. thirdd. multi-

Q: In a star schema, attributes are often used to search, filter, or classify ____.a. tables b. stars c. factsd. dimensions

Q: In a star schema, dimensions are normally stored in ____.a. attributes b. indexes c. fact tablesd. dimension tables

Q: Computed or derived facts are sometimes called ____to differentiate them from stored facts.a. details b. attributes c. metricsd. fact tables

Q: The basic star schema has four components: facts, ____, attributes, and attribute hierarchies.a. keys b. relationships c. cubesd. dimensions

Q: There are 12 rules that define a(n) ____.a. data warehouse b. multidimensional cube c. OLAP toold. star schema

Q: How should storage volumes be allocated for indexes, system, and high-usage tables?

Q: How can queries be written to perform the fastest when equality and inequality comparisons are needed?

Q: Why do we need to optimize a DBMS with SQL performance tuning, even though they automatically optimize SQL queries?

Q: Describe query optimization and the modes that an optimizer can operate in.

Q: List and describe some typical DBMS processes.

Q: The ____________________ table space is the most frequently accessed table space and should be stored in its own volume.

Q: Use __________________________________________________ to provide balance between performance and fault tolerance.

Q: A conditional expression is normally expressed within the ____________________ or HAVING clauses of a SQL statement.

Q: ____________________ is a measure of how likely an index will be used in query processing.

Q: ____________________ are special instructions for the optimizer that are embedded inside the SQL command text.

Q: ____________________ are ordered sets of values that are crucial in speeding up data access.

Q: Once an SQL statement is transformed, the DBMS creates what is commonly known as a(n) ____________________ plan.

Q: The ____________________ analyzes the SQL query and finds the most efficient way to access the data.

Q: DBMS query processing has ____________________ phases.

Q: A(n) ____________________ request is a low-level (read or write) data access operation to/from computer devices.

Q: The ____________________ cache is a shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions.

Q: ____________________ is another name for table space.

Q: Database ____________________ activities can be divided into those taking place either on the client side or on the server side.

Q: The system will perform best when its hardware and software resources are ____________________.

Q: End users and the DBMS interact through the use of ____________________ to generate information.

Q: The ____ table space is used as a temporary storage area for merge, sort, or set aggregate operations.a. system b. user data c. temporaryd. rollback segment

Q: The ____ table space is used to store the data dictionary tables.a. system b. user data c. temporaryd. rollback segment

Q: The ____ table space is used to store end-user data.a. system b. user data c. temporaryd. rollback segment

Q: The ____ table space is used for transaction-recovery purposes.a. system b. user data c. temporaryd. rollback segment

Q: Most DBMSs operate in one of two optimization modes: cost-based or rule-based. Others automatically determine the ____ based on whether database statistics are available.a. data cache b. SQL cache c. sort cached. optimization mode

Q: The ____ cache is used as a temporary storage area for ORDER BY or GROUP BY operations, as well as for index-creation functions.a. data b. SQL c. sortd. optimizer

Q: The ____ cache stores the most recently executed SQL statements.a. data b. SQL c. sortd. optimizer

Q: The majority of primary memory resources will be allocated to the ____ cache.a. data b. SQL c. sortd. optimizer

Q: The ____ must be set large enough to permit as many data requests to be serviced from cache as possible.a. data cache b. SQL cache c. sort cached. optimizer mode

Q: When setting optimizer hints, ____ forces the optimizer to use the P_QOH_NDX index to process this query.a. ALL_ROWS b. FIRST_ROWS c. INDEX(P_QOH_NDX)d. OPTIMIZATION_ROWS

Q: When setting optimizer hints, ____ instructs the optimizer to minimize the time it takes to process the first set of rows, that is, to minimize the time it takes to return only the first set of rows in the query result set. This hint is generally used for interactive mode processes.a. ALL_ROWS b. FIRST_ROWS c. INDEX(P_QOH_NDX)d. OPTIMIZATION_ROWS

Q: When setting optimizer hints, ____ instructs the optimizer to minimize the overall execution time, that is, to minimize the time it takes to return all rows in the query result set. This hint is generally used for batch mode processes.a. ALL_ROWS b. FIRST_ROWS c. INDEX(P_QOH_NDX)d. OPTIMIZATION_ROWS

Q: ____ is/are the central activity during the parsing phase in query processing.a. Database statistics b. Data sparsity c. SQL queryd. Query optimization

Q: Knowing the sparsity of a column helps you decide whether the use of ____ is appropriate.a. query processing b. query optimization c. an indexd. a full table scan

Q: ____ refers to the number of different values a column could possibly have.a. Database statisticsb. Data sparsity c. Primary keysd. Query optimization

Q: If there is no index, the DBMS will perform a ____ scan.a. loop b. range c. row ID table accessd. full table

Q: The DBMS ____ the data and sends the result set back to the client.a. parsesb. executes c. fetchesd. processes

Q: The DBMS ____ the SQL query using the chosen execution plan.a. parses b. executes c. fetchesd. processes

Q: The DBMS ____ the SQL query and chooses the most efficient access/execution plan.a. parses b. executes c. fetchesd. processes

Q: To generate database object statistics manually, you could use the following syntax: ____.a. ANALYZE <TABLE/INDEX> object_name;b. CREATE <TABLE/INDEX> object_name;c. ANALYZE <TABLE/INDEX> object_name COMPUTE STATISTICS;d. CREATE <TABLE/INDEX> object_name COMPUTE STATISTICS;

Q: The ____ is a shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions.a. buffer cache b. procedure cache c. data cached. permanent storage

Q: The data cache or ____ is a shared, reserved memory area that stores the most recently accessed data blocks in RAM.a. buffer cache b. procedure cache c. SQL cached. permanent storage

Q: To work with the data, the DBMS must retrieve the data from the ____ and place it in the ____.a. data files, procedure cache b. data files, data cache c. temporary files, RAMd. temporary files, procedure cache

Q: The data cache is where the data read from the database data files are stored ____ the data have been read or ____ the data are written to the database data files.a. after, before b. after, after c. before, befored. before, after

Q: A system table space, a user data table space, an index table space, and a temporary table space are examples of ____.a. procedure caches b. file groups c. data filesd. extends

Q: A(n) ____ is a logical grouping of several data files that store data with similar characteristics.a. procedure cache b. file group c. data filesd. extends

Q: A DBA determines the initial size of the data files that make up the database; however, as required, the data files can automatically expand in predefined increments known as ____.a. procedure cache b. file group c. data filesd. extends

Q: All data in a database are stored in ____ files.a. table b. data c. RAMd. system

Q: On the server side, the DBMS environment must be properly configured to respond to clients requests in the fastest way possible, while making optimum use of existing resources. The activities required to achieve that goal are commonly referred to as ____ tuning.a. client and server b. database c. SQL performanced. DBMS performance

Q: On the client side, the objective is to generate a SQL query that returns the correct answer in the least amount of time, using the minimum amount of resources at the server end. The activities required to achieve that goal are commonly referred to as ____ tuning.a. client and server b. database c. SQL performanced. DBMS performance

Q: RAID systems use a single disk to create storage volumes.

Q: Character field comparisons are faster than numeric, date, and NULL comparisons.

Q: Indexes are very useful in small tables or tables with low sparsity.

Q: Most current-generation relational DBMSs perform automatic query optimization at the client end.

Q: A cost-based optimizer uses a set of preset rules and points to determine the best approach to execute a query.

Q: Indexes do not facilitate join operations.

Q: All transaction management commands are processed during the parsing and execution phases of query processing.

Q: The SQL execution activities are performed by the query optimizer.

Q: Fully equivalent means that the optimized query results are always the same as the original query.

Q: Working with data in the data cache is many times faster than working with data in the data files.

Q: The purpose of an I/O operation is to move data to and from different computer components or devices.

Q: To work with data, the DBMS must retrieve the data from permanent storage and place it in RAM.

Q: The SQL cache stores the end-user written SQL.

Q: The data cache caches system catalog data and the contents of the indexes.

Q: A data file can only contain rows from one single table.

Q: DBMS implementations are typically similar in complexity to two-tier client/server configurations.

Q: Good database performance starts with good database design.

Q: All factors must be checked to ensure that each system component operates at its optimum level and has sufficient resources to minimize the occurrence of bottlenecks.

Q: Good database performance is easy to evaluate.

Q: One of the main functions of a database system is to provide timely answers to end users.

Q: What are database checkpoints?