Test 1 Flashcards
1.) The__________ determines the common attribute or attributes by looking for identically named attributes and compatible data types.
natural join
When using a subquery, the output of a(n)_________ query is used as the input for the outer query.
inner
The_________clause is used to restrict the output of a GROUP BY query by applying a conditional criteria to the grouped rows.
HAVING
The IN subquery uses a(n)___________ operator.
equality
The use of the____________ operator allows you to compare a single value with a list of values returned by the first subquery (sqA) using a comparison operator other than EQUALS.
ALL
A(n)_____________subquery is a subquery that executes once for each row in the outer query.
correlated
The___________ statement combines the output of two SELECT queries.
UNION
A(n)____________ query can be used to produce a relation that retains the duplicate rows.
UNION ALL
The____________ statement can be used to combine rows from two queries, returning only the rows that appear in both sets.
INTERSECT
If the DBMS does not support the INTERSECT statement, one can use a(n)__________ subquery to achieve the same result.
IN
A(n)___________view is a view that can be used to update attributes in the base table(s) that are used in the view.
updatable
A(n)____________ routine pools multiple transactions into a single batch to update a master table field in a single operation.
batch update
A row-level trigger requires use of the___________ keywords and is executed once for each row affected by the triggering statement.
FOR EACH ROW
When the critical application code is isolated in a single program,____________improves.
maintenance and logic control
_____ is the term used to describe an environment in which the SQL statement is not known in advance and is generated at run time.
Dynamic SQL
______ is the process that establishes the need for, and the extent of, and information system.
Systems analysis
The traditional Systems Development Life Cycle (SDLC) phases are ____, detailed systems design, implementation, and maintenance.
planning
- An initial assessment of the information flow-and-extent requirements must be made during the ______ portion of the Systems Development Life Cycle (SDLC).
planning
- After testing is concluded, the final ______ is reviewed and printed and end users are trained.
documentation
- The advent of very sophisticated application generators and ______ has substantially decreased coding and testing time.
debugging tools
- The system and operational costs are addressed by the ______ during the planning phase of the Systems Development Life Cycle (SDLC).
feasibility study
- ______ are usually provided by the database management system (DBMS) to check for access violations
Audit trails
- The ______ describes the general conditions in which a company operates, its organizational structure, and its mission.
company situation
- The system’s ______ defines the extent of the design according to operational requirements.
scope
- The proposed system is subject to limits known as ______, which are external to the system.
boundaries
- Making sure that the final product meets user and system requirements is the most critical ___ phase.
Database Life Cycle (DBLC)
Programmers use database tools to ______ the applications during coding of the programs.
prototype
______ allows the assignment of access rights to specific authorized users
Password security
The ______ stage uses data modeling to create an abstract database structure that represents real- world objects in the most realistic way possible.
conceptual design
The first step in ______ is to discover the data element characteristics.
conceptual design
A designer must have a thorough understanding of the company’s data types, extent, and uses in order to develop an accurate ______.
data model
From a database point of view, the collection of data becomes meaningful only when the _____ are defined.
business rules
A ______ rule is a brief and precise narrative of a policy, procedures, or principle within a specific organization’s environment.
business rule
Because real world database design is generally done by teams, the database design is probably divided into major components known as ______.
modules
A (n) ______ is an information system component that handles a specific business function, such as inventory, orders, or payroll.
module
______ is the first stage in the database design process.
conceptual design
The term ______ describes the extent to which modules are independent of one another
module coupling
The term ______ describes the strength of the relationships found among a module’s entities
cohesivity
The ______ goal is to design an enterprise-wide database based on a specific data model but independent of physical-level details.
logical design
______ could become a very technical job that affects not only the accessibility of the data in the storage device (s) but also the performance of the system
Physical design
___________ requires that all operations of a transaction be completed.
. Atomicity
__________ means that data used during the execution of a transaction cannot be used by a second transaction until the first one is completed.
c. Isolation
All transactions must display __________.
. atomicity, durability, consistency, and isolation
A single-user database system automatically ensures ________________ of the database, because only one transaction is executed at a time.
serializability and isolation
The ANSI has defined standards that govern SQL database transactions. Transaction support is provided by two SQL statements: ____________ and ROLLBACK.
COMMIT
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?
The end of a program is successfully reached.
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 ROLLBACK?
The program is abnormally terminated.
The implicit beginning of a transaction is ______________.
when the first SQL statement is encountered
The information stored in the ___________ is used by the DBMS for a recovery requirement triggered by a ROLLBACK statement, a program’s abnormal termination, or a system failure such as a network discrepancy or a disk crash.
transaction log
One of the three most common data integrity and consistency problems is ___________.
lost updates
_____________ occurs when a transaction accesses data before and after one or more other transactions finish working with such data.
Inconsistent retrievals
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.
unrelated
A ___________ lock prevents the use of any tables in the database from one transaction while another transaction is being processed.
database-level
______________ are required to prevent another transaction from reading inconsistent data.
Locks
The ____________ manager is responsible for assigning and policing the locks used by the transactions.
lock
Lock _______________ indicates the level of lock use.
granularity
A_____________ lock locks the entire table preventing access to any row by a transaction while another transaction is using the table.
table-level
A___________ lock locks the entire diskpage.
page-level
A diskpage, or page, is the equivalent of a ___________.
diskblock
A __________ lock allows concurrent transactions to access different rows of the same table.
row-level
A(n) __________ lock has only two stages (0 and 1).
binary
A(n) _____________ specifically reserves access to the transaction that locked the object.
exclusive lock
End users and the DBMS interact through the use of _____to generate information.
Queries
A system will perform best when its hardware and software resources are _________.
optimized
Database __________ activities can be divided into those taking place either on the client side or on the server side.
performance tuning
_________ is another name for table space.
File group
The ______ cache is a shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions.
SQL
A (n) request is a low-level read or write data access operation to or from computer devices.
input/output (mouse, Keyboard, Speakers)
DBMS query processing has phases.
3,
- Parsing -Chooses the most efficient access/execution plan
- Execution- DBMS executes the sql query using the chosen execution plan
- Fetching- DBMS fetches the data and sends the result set back to the client
The __________ analyzes the SQL query and finds the most efficient way to access the data.
query optimizer
________ are ordered sets of values that are crucial in speeding up data access.
Indexes
A ________ is good for simple and fast lookup operations based on equality conditions.
hash index
_________ are special instructions for the optimizer that are embedded inside the SQL command text.
Optimizer hints
_________ is a measure of the likelihood that an index will be used in query processing.
Index selectivity
___________ is evaluated based on client perspective.
SQL performance tuning
___________ is an index based on a specific SQL function or expression.
function-based index
A condition expression is normally expressed with the _______ or HAVING clauses of a SQL statement.
WHERE
____________ helps provide a balance between performance and fault tolerance.
RAID
Redundant
Array of
Independant
Disks
The ______ table space is the most frequently accessed table space and should be stored in its own volume.
system
