Q&A Hero

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?

Q: What are the three basic techniques to control deadlocks?

Q: How does a shared/exclusive lock schema increase the lock manager's overhead?

Q: Explain the transaction log. What is its function?

Q: What is transaction isolation and why it is important?

Q: Database transaction ____________________ restores a database from an inconsistent state to a previously consistent state.

Q: The DBMS keeps several copies of ____________________ to ensure that a disk physical failure will not impair the DBMS's ability to recover data.

Q: Uniqueness ensures that no equal time stamp values can exist, and ____________________ ensures that time stamp values always increase.

Q: If T1 has not unlocked data item Y, T2 cannot begin; if T2 has not unlocked data item X, T1 cannot continue. Consequently, T1 and T2 each wait for the other to unlock the required data item. Such a deadlock is also known as a(n) ____________________.

Q: The ____________________ rule states that only one transaction at a time can own an exclusive lock on the same object.

Q: As a rule, a(n) ____________________ must unlock the object after its termination.

Q: ____________________-level locks are less restrictive than database-level locks, but they create traffic jams when many transactions are waiting to access the same table.

Q: ____________________ can take place at any of the following levels: database, table, page, row, or field.

Q: Most multiuser ______________________________ automatically initiate and enforce locking procedures, where all locking information is managed by the lock manager.

Q: To determine the appropriate order of the operations, the scheduler bases its actions on concurrency control algorithms, such as ____________________ or time stamping methods.

Q: The ____________________ interleaves the execution of database operations to ensure serializability.

Q: ____________________ control is important because the simultaneous execution of transactions over a shared database can create several data integrity and consistency problems.

Q: The objective of ____________________ control is to ensure the serializability of transactions in a multiuser database environment.

Q: If a(n) ____________________ is issued before the termination of a transaction, the DBMS will restore the database only for that particular transaction, rather than for all transactions, in order to maintain the durability of the previous transactions.

Q: Although the DBMS is designed to recover a database to a previous consistent state (when an interruption prevents the completion of a required set of transactions), the transactions themselves are defined by the end user or programmer and must be ____________________ correct.

Q: In the optimistic approach, during the ____ phase, changes are permanently applied to the database.a. read b. validation c. writed. shared

Q: In the optimistic approach, during the ____ phase, a transaction scans the database, executes the needed computations, and makes the updates to a private copy of the database values.a. read b. validation c. writed. shared

Q: The ____ approach to scheduling concurrent transactions assigns a global unique stamp to each transaction.a. scheduled b. table-locking c. uniqued. timestamping

Q: A(n) ____ condition occurs when two or more transactions wait for each other to unlock data.a. deadlock b. exclusive lock c. binary lockd. two-phase lock

Q: A(n) ____ phase in a two-phase lock is when a transaction releases all locks and cannot obtain any new lock.a. growing b. shrinking c. lockingd. unlocking

Q: What is a rule that applies to the two-phase locking protocol? a. Two transactions cannot have conflicting locks. b. No unlock operation can precede a lock operation in a different transaction. c. No data is affected until all locks are released. d. No data is affected until the transaction is in its locked position.

Q: A(n) ____ lock exists when concurrent transactions are granted read access on the basis of a common lock.a. shared b. exclusive c. binaryd. two-phase

Q: A(n) ____ specifically reserves access to the transaction that locked the object.a. shared lock b. exclusive lock c. binary lockd. deadlock

Q: A(n) ____ lock has only two stages (0 and 1).a. shared b. exclusive c. binaryd. two-phase

Q: A ____ lock allows concurrent transactions to access different rows of the same table.a. database-level b. table-level c. page-leveld. row-level

Q: A diskpage, or page, is the equivalent of a ____.a. database table b. disk sector c. database schemad. diskblock

Q: A ____ lock locks the entire diskpage.a. transaction-level b. table-level c. page-leveld. row-level

Q: A ____ lock locks the entire table preventing access to any row by a transaction while another transaction is using the table.a. database-level b. table-level c. page-leveld. row-level

Q: Lock ____ indicates the level of lock use.a. granularity b. shrinking c. growingd. serializability

Q: The ____ manager is responsible for assigning and policing the locks used by the transactions.a. transaction b. database c. lockd. schedule

Q: ____ are required to prevent another transaction from reading inconsistent data.a. Locks b. Schedules c. Stampsd. Logs

Q: A ____ lock prevents the use of any tables in the database from one transaction while another transaction is being processed.a. database-level b. table-level c. page-leveld. row-level

Q: As long as two transactions, T1 and T2, access ____data, there is no conflict, and the order of execution is irrelevant to the final outcome.a. shared b. common c. unrelatedd. locked

Q: One of the three most common data integrity and consistency problems is ____.a. lost updates b. disk failures c. user errorsd. deadlocks