CSIS 304 Chapter 10-13

The mainframe environment enables an OLAP system to be divided into several components that define its architecture.
true
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.
shared
common
unrelated
locked
unrelated
A characteristic of operational data is ____.
summarized data
hundreds of gigabytes
high normalization
historic data
high normalization
____ transparency ensures that the system will continue to operate in the event of a node failure.
Transaction
Distribution
Failure
Performance
Failure
A(n) ____ specifically reserves access to the transaction that locked the object.
shared lock
exclusive lock
binary lock
deadlock
exclusive lock
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.
client and server
database
SQL performance
DBMS performance
SQL performance
The implicit beginning of a transaction is _____.

1) when the database is started
2) when a table is accessed for the first time
3) when the first SQL statement is encountered
4) when the COMMIT command is issued

when the first SQL statement is encountered
The transaction recovery write-ahead-log protocol ensures that transaction logs are always written before any database data are actually updated.

True
False

True
ANSI defines four events that signal the end of a transaction. Of the following events, which is defined by ANSI as being equivalent to a COMMIT?

1) Five SQL statements are executed.
2) The end of a program is successfully reached
3) The program is abnormally terminated.
4) The database is shut down for maintenance.

The end of a program is successfully reached
To work with data, the DBMS must retrieve the data from permanent storage and place it in RAM.
1) True
2) False
True
When setting optimizer hints, _____ instructs the optimizer to minimize the overall execution time, that is, to minimize the time it takes to return the total number of rows in the query result set. This hint is generally used for batch mode processes.

1) ALL_ROWS
2) FIRST_ROWS
3) INDEX(P_QOH_NDX)
4) OPTIMIZATION_ROWS

INDEX(P_QOH_NDX)
If there is no index, the DBMS will perform a _____ scan.
1) loop
2) range
3) row ID table access
4) full table
full table
The _____ cache is used as a temporary storage area for ORDER BY or GROUP BY operations, as well as for index-creation functions.
1) data
2) SQL
3) sort
4) optimizer
sort
The _____ is a shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions.

1) buffer cache
2) procedure cache
3) data cache
4) permanent storage

procedure cache
Which of the following is the first step of query processing at the DBMS server end?
1) Executing
2) Parsing
3) Fetching
4) Delivering
Parsing
Character field comparisons are faster than numeric, date, and NULL comparisons.
1) True
2) False
False
The SQL cache stores the end-user written SQL.
1) True
2) False
False
A cost-based optimizer uses a set of preset rules and points to determine the best approach to execute a query.
1) True
2) False
False
An index scan is less efficient than a full table scan.

1) True
2) False

False
The majority of primary memory resources will be allocated to the _____ cache.
1) data
2) SQL
3) sort
4) optimizer
data
On the server side, the database 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 this goal are commonly referred to as _____ tuning.
1) client and server
2) database SQL
3) SQL performance
4) DBMS performance
DBMS performance
The DBMS _____ the SQL query and chooses the most efficient access/execution plan.
1) parses
2) executes
3) fetches
4) processes
parses
DBMS implementations are typically similar in complexity to two-tier client/server configurations.
1) True
2) False
False
A system table space, a user data table space, an index table space, and a temporary table space are examples of _____.

1) procedure caches
2) file groups
3) data caches
4) operation modes

file groups
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 _____.

1) procedure cache
2) buffer cache
3) supplements
4) extends

extends
The data cache caches system catalog data and the contents of the indexes.
1) True
2) False
True
All transaction management commands are processed during the parsing and execution phases of query processing.
1) True
2) False
False
On the client side, the objective is to generate an SQL query that returns a correct answer in the least amount of time, using a minimum amount of resources at the server end. The activities required to achieve this goal are commonly referred to as _____ tuning.
1) client SQL
2) database SQL
3) SQL performance
4) DBMS performance
SQL performance
The primary factor in determining the most efficient access plan is the I/O cost.
1) True
2) False
True
The purpose of an I/O operation is to move data to and from different computer components or devices.
1) True
2) False
True
Good database performance is easy to evaluate.
1) True
2) False
False
In the context of RAID levels, striped arrays provide:
1) increased read performance and fault tolerance.
2) increased fault tolerance but decreased performance.
3) increased read performance but no fault tolerance.
4) neither fault tolerance nor good performance.
increased read performance but no fault tolerance.
Working with data in the data cache is many times faster than working with data in the data files.
1) True
2) False
True
The _____ table space is used for transaction-recovery purposes.
1) system
2) user data
3) temporary
4) rollback segment
rollback segment
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.
1) True
2) False
True
RAID systems use a single disk to create storage volumes.
1) True
2) False
False
Indexes do not facilitate join operations.
1) True
2) False
False
Using index characteristics, a database designer can determine the best type of index to use.
1) True
2) False
True
Fully equivalent means that the optimized query results are always the same as the original query.
1) True
2) False
True
The _____ must be set large enough to permit as many data requests to be serviced from cache as possible.
1) data cache
2) SQL cache
3) sort cache
4) optimizer mode
data cache
DBMS performance tuning includes global tasks such as managing the DBMS processes in primary memory and managing the structures in physical storage.
1) True
2) False
True
Most current-generation relational DBMSs perform automatic query optimization at the client end.
1) True
2) False
False
A(n) _____ is a logical grouping of several data files that store data with similar characteristics.

1) procedure cache
2) table space
3) data cache

table space
Automatic query optimization means that the:
1) optimization takes place at compilation time by the programmer.
2) DBMS finds the most cost-effective access path without user intervention.
3) optimization process is scheduled and selected by the end user or programmer.
4) database access strategy is defined when the program is executed.
DBMS finds the most cost-effective access path without user intervention.
To work with data, a DBMS must retrieve the data from _____ and place them in _____.
1) data files; procedure cache
2) RAM; data cache
3) permanent storage; RAM
4) temporary files; procedure cache
data files; procedure cache
In standard SQL, the optimizer hint ALL_ROWS is generally used for _____ mode processes.
1) interactive
2) real-time
3) batch
4) transaction
batch
From the performance point of view, _____ databases eliminate disk access bottlenecks.
1) RAID
2) distributed
3) index-organized
4) in-memory
in-memory
The _____ cache stores the most recently executed SQL statements.
1) data
2) procedure
3) sort
4) optimizer
procedure/SQL
When moving data from permanent storage to RAM, an I/O disk operation retrieves:
1) an entire table.
2) an entire physical disk block.
3) only the row containing the attribute requested.
4) only the attribute which was requested.
an entire physical disk block.
In the context of RAID levels, _____ refers to writing the same data blocks to separate drives.
1) striping
2) mirroring
3) partitioning
4) aggregating
mirroring
A fully replicated database stores multiples copies of each database fragment at multiple sites.
true
A partially replicated database stores multiples copies of some database fragment at multiple sites.
true
One of the advantages of a DDBMS is growth facilitation.
True
False
True
A remote transaction, composed of several requests, may access data at multiple sites.
1) True
2) False
False
The _____ guarantees that if a portion of a transaction operation cannot be committed, all changes made at the other sites participating in the transaction will be undone to maintain a consistent database state.
1) DO-UNDO-REDO protocol
2) two-phase commit protocol (2PC)
3) coordinator protocol
4) write-ahead protocol
two-phase commit protocol (2PC)
One of the advantages of a distributed database management system (DDBMS) is that the data is located near the site with the least demand.
1) True
2) False
False
A(n) _____ database stores each database fragment at a single site.
1) partially replicated
2) unreplicated
3) fully replicated
4) partitioned
unreplicated
The objective of _____ optimization is to minimize the total cost associated with the execution of a request.
1) data
2) remote
3) transaction
4) query
query
The World Wide Web is the repository for distributed data.
1) True
2) False
True
_____ distributed database systems integrate different types of centralized database management systems (DBMS) over a network.
1) Homogeneous
2) Heterogeneous
3) Fully homogeneous
4) Combination
Heterogeneous
In order to manage distributed data, copies or parts of the database processing functions must be distributed to all data storage sites.
1) True
2) False
True
A transaction processor (TP) is the software component residing on each computer that stores and retrieves data located at the site.
1) True
2) False
False
A _____ contains the description of the entire database as seen by the database administrator.
1) distributed global dictionary
2) distributed data dictionary
3) distributed global schema
4) distributed data schema
distributed data dictionary
Performance transparency ensures that the system finds the most cost-effective path to access remote data.
1) True
2) False
True
Distributed processing shares a database’s logical processing among two or more physically independent sites that are connected through a network.
1) True
2) False
True
Under the _____ scenario, all record- and file-locking activities are performed at the end-user location.
1) single-site processing, single-site data
2) multiple-site processing, single-site data
3) single-site processing, multiple-site data
4) multiple-site processing, multiple-site data
multiple-site processing, single-site data
The transaction processor (TP) is the software component found in each computer that requests data.
1) True
2) False
True
A disadvantage of a distributed database management system (DDBMS) is that:
Question 16 options:

1) it is slower in terms of data access.
2) adding new sites affects other sites’ operations.
3) it is processor dependent.
4) there are lack of standards.

there are lack of standards.
_____ fragmentation allows a user to break a single object into two or more segments, or fragments.
1) Horizontal
2) Vertical
3) Data
4) Request
Data
A distributed database management system (DDBMS) must be communications-media-dependent.
1) True
2) False
False
Both distributed processing and distributed databases require a network of interconnected components.
1) True
2) False
True
The _____ rule requires that all copies of data fragments be identical.
1) shared fragment
2) mutual consistency
3) horizontal fragmentation
4) replication
mutual consistency
Current distributed database management system (DDBMS) are subject to some problems, such as the complexity of management and control.
1) True
2) False
True
Distributed database systems do not require complex mechanisms to manage transactions and ensure the database’s consistency and integrity.
1) True
2) False
False
Which of the following is an advantage associated with a distributed database management system (DDBMS)?
1) Reduced operating costs
2) Simplicity of management and control
3) Decreased storage requirements
4) Decreased training costs
Reduced operating costs
_____ distributed database management system (DDBMS) integrate multiple instances of the same DBMS over a network.
1) Homogeneous
2) Heterogeneous
3) Fully heterogeneous
4) Combination
Homogeneous
A _____ distributed database system will support different database management systems (DBMS) that may even support different models running under different computer systems.
1) fully heterogeneous
2) fully homogeneous
3) homogeneous
4) heterogeneous
fully heterogeneous
In the single-site processing, single-site data (SPSD) scenario, all processing must be done on the end user’s side of the system.
1) True
2) False
False
A DDBMS is subject to which of the following restrictions?
1) Multiple instances of the same database should be integrated over a network.
2) All database processing must be done at a single site.
3) Rapid ad hoc data access is not possible.
4) Remote data access is provided on a read-only basis.
Remote data access is provided on a read-only basis.
A distributed database is composed of several parts known as database _____.
1) sections
2) fragments
3) partitions
4) parts
fragments
Distribution transparency is supported by a distributed data dictionary.
1) True
2) False
True
A _____ request lets a single SQL statement reference data located at several different local or remote DP sites.
1) distributed
2) transaction
3) fragmented
4) remote
distributed
_____ transparency ensures that the system will continue to operate in the event of a node or network malfunction.
1) Transaction
2) Distribution
3) Failure
4) Performance
Failure
_____ transparency allows the system to operate as if it were a centralized database management system.
1) Heterogeneity
2) Distribution
3) Performance
4) Failure
Performance
The _____ processor is the software component residing on each computer that stores and retrieves data located at the site.
1) transaction
2) network
3) data
4) management
data
_____ transparency is the highest level of transparency. The end user or programmer does not need to know that a database is partitioned.
1) Performance
2) Fragmentation
3) Location
4) Local mapping
Fragmentation
_____ transparency allows the integration of several different local database management systems (DBMS) under a common, or global, schema.
1) Transaction
2) Performance
3) Distribution
4) Heterogeneity
Heterogeneity
Distributed processing does not require:
1) database processing functions to be distributed to all data storage sites.
2) an existing distributed database.
3) a network of interconnected components.
4) multiple sites to share processing chores.
database processing functions to be distributed to all data storage sites.

not sure

A database management system needs _____ to prepare the data for presentation to the end user or to an application program.
1) security
2) concurrency control
3) formatting
4) I/O interface
formatting
A centralized database management is subject to a problem such as _____.
1) a growing number of remote locations
2) maintaining and operating small database systems
3) dependence on multiple sites
4) organizational flexibility of the database
a growing number of remote locations
Rapid ad hoc data became unnecessary in the quick-response decision-making environment.
1) True
2) False
False
A fully distributed database management system (DBMS) must perform all the functions of a centralized DBMS, and it must handle all necessary functions imposed by the distribution of data and processing.
1) True
2) False
True
Distributed processing does not require a distributed database, and a distributed database does not require distributed processing.
1) True
2) False
False
A distributed database management system (DDBMS) governs the storage and processing of logically related data over interconnected computer systems.
1) True
2) False
True
The ANSI has defined standards that govern SQL database transactions. Transaction support is provided by two SQL statements: ____ and ROLLBACK.
RETRIEVE
ASSIGN
UPDATE
COMMIT
COMMIT
A(n) ____ is a logical grouping of several data files that store data with similar characteristics.
procedure cache
file group
data files
extends
file group
Knowing the sparsity of a column helps you decide whether the use of ____ is appropriate.
query processing
query optimization
an index
a full table scan
an index
____ is a disadvantage of a DDBMS.
Processor independence
The graphical user interface
Security
Data access that is too rapid
Security
A ____ index is based on 0 and 1 bits to represent a given condition.
logical
multidimensional
star
bitmapped
bitmapped
What is one of the main characteristics of OLAP systems?
They use single-dimensional data analysis techniques.
They provide complex command-line end-user interfaces.
They provide minimal database support.
They support client/server architecture.
They support client/server architecture.
Data ____ implies that all business entities, data elements, data characteristics, and business metrics are described in the same way throughout the enterprise.
volatility
time-variation
subject-orientation
integration
integration
____ transparency allows a transaction to update data at several network sites.
Transaction
Distribution
Failure
Performance
Transaction
The data warehouse development life cycle differs from classical systems development.
True
False
True
Character field comparisons are faster than numeric, date, and NULL comparisons.
True
False
False
In a star schema, dimensions are normally stored in ____.
attributes
indexes
fact tables
dimension tables
dimension tables
The reliance on ____ as the design methodology for relational databases is seen as a stumbling block to its use in OLAP systems.
normalization
denormalization
star schema
multidimensional schema
normalization
The ____ table space is used to store the data dictionary tables.
system
user data
temporary
rollback segment
system
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.
read
validation
write
shared
read
In the optimistic approach, during the ____ phase, changes are permanently applied to the database.
read
validation
write
shared
write
The data cache or ____ is a shared, reserved memory area that stores the most recently accessed data blocks in RAM.
buffer cache
procedure cache
SQL cache
permanent storage
buffer cache
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.
data cache
SQL cache
sort cache
optimization mode
optimization mode
To generate database object statistics manually, you could use the following syntax: ____.
1-ANALYZE object_name;
2-CREATE
object_name;
3- ANALYZE
object_name COMPUTE STATISTICS;
4-CREATE
object_name COMPUTE STATISTICS;
ANALYZE
object_name COMPUTE STATISTICS;
The phenomenon of uncommitted data occurs when two transactions are executed concurrently and the first transaction is rolled back after the second transaction has already accessed the uncommitted data—thus violating the isolation property of transactions.
True
False
True
Creating a data warehouse is a simple exercise that takes little time, money, or effort.
True
False
False
A DDBMS governs the storage and processing of logically related data over interconnected computer systems.
True
False
True