Definitions SQL TEST 1 (1)

Question 1

anonymous PL/SQL block

Answer 1

A PL/SQL block that has not been given a specific name.

Question 2

base table

Answer 2

The table on which a view is based.

Question 3

batch update routine

Answer 3

A routine that pools transactions into a single batch to update a master table in a single operation.

Question 4

correlated subquery

Answer 4

A subquery that executes once for each row in the outer query.

Question 5

CREATE VIEW

Answer 5

A SQL command that creates a logical, virtual table based on stored end-user tables. The view can be treated as a real table.

Question 6

cross join

Answer 6

A join that performs a relational product of two tables.

Question 7

cursor

Answer 7

A special construct used in procedural SQL to hold the data rows returned by a SQL query. A cursor may be considered a reserved area of memory in which query output is stored, like an array holding columns and rows. Cursors are held in a reserved memory area in the DBMS server, not in the client computer.

Question 8

dynamic SQL

Answer 8

An environment in which the SQL statement is not known in advance, but instead is generated at run time. In a dynamic SQL environment, a program can generate the SQL statements that are required to respond to ad hoc queries.

Question 9

embedded SQL

Answer 9

SQL statements contained within application programming languages such as COBOL, C++, ASP, Java, and ColdFusion.

Question 10

explicit cursor

Answer 10

In procedural SQL, a cursor created to hold the output of a SQL statement that may return two or more rows, but could return zero or only one row.

Question 11

host language

Answer 11

Any language that contains embedded SQL statements.

Question 12

implicit cursor

Answer 12

A cursor that is automatically created in procedural SQL when the SQL statement returns only one value.

Question 13

inner join

Answer 13

A join operation in which only rows that meet a given criterion are selected. The join criterion can be an equality condition or an inequality condition. The inner join is the most commonly used type of join. Contrast with outer join.

Question 14

outer join

Answer 14

A relational algebra JOIN operation that produces a table in which all unmatched pairs are retained; unmatched values in the related table are left null. Contrast with inner join.

Question 15

persistent stored module (PSM)

Answer 15

A block of code with standard SQL statements and procedural extensions that is stored and executed at the DBMS server.

Question 16

Procedural Language SQL (PL/SQL)

Answer 16

A type of SQL that allows the use of procedural code and in which SQL statements are stored in a database as a single callable object that can be invoked by name.

Question 17

row-level trigger

Answer 17

A trigger that is executed once for each row affected by the triggering SQL statement. A row-level trigger requires the use of the FOR EACH ROW keywords in the trigger declaration.

Question 18

set-oriented

Answer 18

Dealing with or related to sets, or groups of things. In the relational model, SQL operators are set-oriented because they operate over entire sets of rows and columns at once.

Question 19

statement-level trigger

Answer 19

A SQL trigger that is assumed if the FOR EACH ROW keywords are omitted. This type of trigger is executed once, before or after the triggering statement completes, and is the default case.

Question 20

static SQL

Answer 20

A style of embedded SQL in which the SQL statements do not change while the application is running.

Question 21

stored function

Answer 21

A named group of procedural and SQL statements that returns a value, as indicated by a RETURN statement in its program code.

Question 22

stored procedure

Answer 22

1- A named collection of procedural and SQL statements. 2- Business logic stored on a server in the form of SQL code or another DBMS-specific procedural language.

Question 23

trigger

Answer 23

A procedural SQL code that is automatically invoked by the relational database management system when a data manipulation event occurs.

Question 24

union-compatible

Answer 24

Two or more tables that share the same column names and have columns with compatible data types or domains.

Question 25

updatable view

Answer 25

A view that can update attributes in base tables that are used in the view.

Question 26

view

Answer 26

A virtual table based on a SELECT query.

Question 27

bottom-up design

Answer 27

A design philosophy that begins by identifying individual design components and then aggregates them into larger units. In database design, the process begins by defining attributes and then groups them into entities. Compare to top-down design.

Question 28

boundaries

Answer 28

The external limits to which any proposed system is subjected. These limits include budgets, personnel, and existing hardware and software.

Question 29

centralized design

Answer 29

A process in which a single conceptual design is modeled to match an organization’s database requirements. It is typically used when a data component consists of a relatively small number of objects and procedures. Compare to decentralized design.

Question 30

clustered table

Answer 30

A storage technique that stores related rows from two related tables in adjacent data blocks on disk.

Question 31

cohesivity

Answer 31

The strength of the relationships between a module’s components. Module cohesivity must be high.

Question 32

computer-aided systems engineering (CASE)

Answer 32

Tools used to automate part or all of the Systems Development Life Cycle.

Question 33

conceptual design

Answer 33

A process that uses data-modeling techniques to create a model of a database structure that represents real-world objects as realistically as possible. The techniques are both software- and hardware-independent.

Question 34

database development

Answer 34

The process of database design and implementation.

Question 35

database fragment

Answer 35

A subset of a distributed database. Although the fragments may be stored at different sites within a computer network, the set of all fragments is treated as a single database.

Question 36

Database Life Cycle (DBLC)

Answer 36

A cycle that traces the history of a database within an information system. The cycle is divided into six phases: initial study, design, implementation and loading, testing and evaluation, operation and maintenance, and evolution.

Question 37

database role

Answer 37

A set of database privileges that could be assigned as a unit to a user or group.

Question 38

decentralized design

Answer 38

A process in which conceptual design is used to model subsets of an organization’s database requirements. After verification of the views, processes, and constraints, the subsets are then aggregated into a complete design. Such modular designs are typical of complex systems in which the data component has a relatively large number of objects and procedures. Compare to centralized design.

Question 39

description of operations

Answer 39

A document that provides a precise, detailed, up-to-date, and thoroughly reviewed description of the activities that define an organization’s operating environment.

Question 40

differential backup

Answer 40

A level of database backup in which only the last modifications to the database are copied.

Question 41

full backup (database dump)

Answer 41

A complete copy of an entire database saved and periodically updated in a separate memory location. A full backup ensures a full recovery of all data after a physical disaster or database integrity failure.

Question 42

information system

Answer 42

A system that provides for data collection, storage, and retrieval; facilitates the transformation of data into information; and manages both data and information. An information system is composed of hardware, the DBMS and other software, databases, people, and procedures.

Question 43

logical design

Answer 43

A stage in the design phase that matches the conceptual design to the requirements of the selected DBMS and is therefore software-dependent. Logical design is used to translate the conceptual design into the internal model for a selected database management system, such as DB2, SQL Server, Oracle, IMS, Informix, Access, or Ingress.

Question 44

minimal data rule

Answer 44

Defined as All that is needed is there, and all that is there is needed. In other words, all data elements required by database transactions must be defined in the model, and all data elements defined in the model must be used by at least one database transaction.

Question 45

module

Answer 45

1 - A design segment that can be implemented as an autonomous unit, and is sometimes linked to produce a system. 2 - An information system component that handles a specific function, such as inventory, orders, or payroll.

Question 46

module coupling

Answer 46

The extent to which modules are independent of one another.

Question 47

physical design

Answer 47

A stage of database design that maps the data storage and access characteristics of a database. Because these characteristics are a function of the types of devices supported by the hardware, the data access methods supported by the system physical design are both hardware- and software-dependent.

Question 48

scope

Answer 48

The part of a system that defines the extent of the design, according to operational requirements.

Question 49

systems analysis

Answer 49

The process that establishes the need for an information system and its extent.

Question 50

systems development

Answer 50

The process of creating an information system.

Question 51

Systems Development Life Cycle (SDLC)

Answer 51

The cycle that traces the history of an information system. The SDLC provides the big picture within which database design and application development can be mapped out and evaluated.

Question 52

top-down design

Answer 52

A design philosophy that begins by defining the main structures of a system and then moves to define the smaller units within those structures. In database design, this process first identifies entities and then defines the attributes within the entities. Compare to bottom-up design.

Question 53

transaction log backup

Answer 53

A backup of only the transaction log operations that are not reflected in a previous backup copy of the database.

Question 54

virtualization

Answer 54

A technique that creates logical representations of computing resources that are independent of the underlying physical computing resources.

Question 55

atomic transaction property

Answer 55

A property that requires all parts of a transaction to be treated as a single, logical unit of work in which all operations must be completed to produce a consistent database.

Question 56

binary lock

Answer 56

A lock that has only two states: locked and unlocked. If a data item is locked by a transaction, no other transaction can use that data item.

Question 57

checkpoint

Answer 57

In transaction management, an operation in which the database management system writes all of its updated buffers to disk.

Question 58

concurrency control

Answer 58

A DBMS feature that coordinates the simultaneous execution of transactions in a multiprocessing database system while preserving data integrity.

Question 59

consistency

Answer 59

A database condition in which all data integrity constraints are satisfied. To ensure consistency of a database, every transaction must begin with the database in a known consistent state. If not, the transaction will yield an inconsistent database that violates its integrity and business rules.

Question 60

consistent database state

Answer 60

A database state in which all data integrity constraints are satisfied.

Question 61

database recovery

Answer 61

The process of restoring a database to a previous consistent state.

Question 62

database request

Answer 62

The equivalent of a single SQL statement in an application program or a transaction.

Question 63

database-level lock

Answer 63

A type of lock that restricts database access to the owner of the lock and allows only one user at a time to access the database. This lock works for batch processes but is unsuitable for online multiuser DBMSs.

Question 64

deadlock

Answer 64

A condition in which two or more transactions wait indefinitely for the other to release the lock on a previously locked data item.

Question 65

deferred update

Answer 65

In transaction management, a condition in which transaction operations do not immediately update a physical database.

Question 66

dirty read

Answer 66

In transaction management, when a transaction reads data that is not yet committed.

Question 67

diskpage

Answer 67

In permanent storage, the equivalent of a disk block, which can be described as a directly addressable section of a disk. A diskpage has a fixed size, such as 4K, 8K, or 16K.

Question 68

durability

Answer 68

The transaction property that indicates the permanence of a database’s consistent state. Transactions that have been completed will not be lost in a system failure if the database has proper durability.

Question 69

exclusive lock

Answer 69

A lock that is reserved by a transaction. An exclusive lock is issued when a transaction requests permission to update a data item and no locks are held on that data item by any other transaction. An exclusive lock does not allow other transactions to access the database.

Question 70

field-level lock

Answer 70

A lock that allows concurrent transactions to access the same row as long as they require the use of different fields within that row. This type of lock yields the most flexible multiuser data access but requires a high level of computer overhead.

Question 71

immediate update

Answer 71

A database update that is performed immediately during a transaction’s execution, even before the transaction reaches its commit point.

Question 72

inconsistent retrievals

Answer 72

A concurrency control problem that arises when a transaction-calculating summary functions over a set of data while other transactions are updating the data, yielding erroneous results.

Question 73

isolation

Answer 73

A property of a database transaction in which a data item used by one transaction is not available to other transactions until the first one ends.

Question 74

lock

Answer 74

A device that guarantees unique use of a data item in a particular transaction operation. A transaction requires a lock prior to data access; the lock is released after the operation’s execution to enable other transactions to lock the data item for their own use.

Question 75

lock granularity

Answer 75

The level of lock use. Locking can take place at the following levels: database, table, page, row, and field.

Question 76

lock manager

Answer 76

A DBMS component that is responsible for assigning and releasing locks.

Question 77

lost updates

Answer 77

A concurrency control problem in which data updates are lost during the concurrent execution of transactions.

Question 78

monotonicity

Answer 78

A quality that ensures that timestamp values always increase.

Question 79

mutual exclusive rule

Answer 79

A condition in which only one transaction at a time can own an exclusive lock on the same object.

Question 80

nonrepeatable read

Answer 80

In transaction management, when a transaction reads a given row at time t1, then reads the same row at time t2, yielding different results because the original row may have been updated or deleted.

Question 81

optimistic approach

Answer 81

In transaction management, a concurrency control technique based on the assumption that most database operations do not conflict.

Question 82

page-level lock

Answer 82

In this type of lock, the database management system locks an entire diskpage, or section of a disk. A diskpage can contain data for one or more rows and from one or more tables.

Question 83

pessimistic locking

Answer 83

The use of locks based on the assumption that conflict between transactions is likely.

Question 84

phantom read

Answer 84

In transaction management, when a transaction executes a query at time t1, then runs the same query at time t2, yielding additional rows that satisfy the query.

Question 85

Read Committed

Answer 85

An ANSI SQL transaction isolation level that allows transactions to read only committed data. This is the default mode of operations for most databases.

Question 86

Read Uncommitted

Answer 86

An ANSI SQL transaction isolation level that allows transactions to read uncommitted data from other transactions, and which allows nonrepeatable reads and phantom reads. The least restrictive level defined by ANSI SQL.

Question 87

redundant transaction logs

Answer 87

Multiple copies of the transaction log kept by database management systems to ensure that the physical failure of a disk will not impair the DBMS’s ability to recover data.

Question 88

Repeatable Read

Answer 88

An ANSI SQL transaction isolation level that uses shared locks to ensure that other transactions do not update a row after the original query updates it. However, phantom reads are allowed.

Question 89

row-level lock

Answer 89

A less restrictive database lock in which the DBMS allows concurrent transactions to access different rows of the same table, even when the rows are on the same page.

Question 90

scheduler

Answer 90

The DBMS component that establishes the order in which concurrent transaction operations are executed. The scheduler interleaves the execution of database operations in a specific sequence to ensure serializability.

Question 91

serializability

Answer 91

A property in which the selected order of transaction operations creates the same final database state that would have been produced if the transactions had been executed in a serial fashion.

Question 92

serializable

Answer 92

An ANSI SQL transaction isolation level that does not allow dirty reads, nonrepeatable reads, or phantom reads; the most restrictive level defined by the ANSI SQL standard.

Question 93

serializable schedule

Answer 93

In transaction management, a schedule of operations in which the interleaved execution of the transactions yields the same result as if they were executed in serial order.

Question 94

shared lock

Answer 94

A lock that is issued when a transaction requests permission to read data from a database and no exclusive locks are held on the data by another transaction. A shared lock allows other read-only transactions to access the database.

Question 95

table-level lock

Answer 95

A locking scheme that allows only one transaction at a time to access a table. A table-level lock locks an entire table, preventing access to any row by transaction T2 while transaction T1 is using the table.

Question 96

timestamping

Answer 96

In transaction management, a technique used in scheduling concurrent transactions that assigns a global unique timestamp to each transaction.

Question 97

transaction

Answer 97

A sequence of database requests that accesses the database. A transaction is a logical unit of work; that is, it must be entirely completed or abortedno intermediate ending states are accepted. All transactions must have the properties of atomicity, consistency, isolation, and durability.

Question 98

transaction log

Answer 98

A feature used by the DBMS to keep track of all transaction operations that update the database. The information stored in this log is used by the DBMS for recovery purposes.

Question 99

two-phase locking

Answer 99

A set of rules that governs how transactions acquire and relinquish locks. Two-phase locking guarantees serializability, but it does not prevent deadlocks. The two-phase locking protocol is divided into two phases: 1- A growing phase occurs when the transaction acquires the locks it needs without unlocking any existing data locks. Once all locks have been acquired, the transaction is in its locked point. 2 - A shrinking phase occurs when the transaction releases all locks and cannot obtain a new lock.

Question 100

uncommitted data

Answer 100

When you are trying to achieve concurrency control, uncommitted data cause problems with data integrity and consistency. These problems occur 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.

Question 101

uniqueness

Answer 101

In concurrency control, a property of timestamping that ensures no equal timestamp values can exist.

Question 102

wait/die

Answer 102

A concurrency control scheme in which an older transaction must wait for the younger transaction to complete and release the locks before requesting the locks itself. Otherwise, the newer transaction dies and is rescheduled.

Question 103

wound/wait

Answer 103

A concurrency control scheme in which an older transaction can request the lock, preempt the younger transaction, and reschedule it. Otherwise, the newer transaction waits until the older transaction finishes.

Question 104

write-ahead-log protocol

Answer 104

In concurrency control, a process that ensures transaction logs are written to permanent storage before any database data are actually updated. Also called a write-ahead protocol.

Question 105

write-through technique

Answer 105

In concurrency control, a process that ensures a database is immediately updated by operations during the transaction’s execution, even before the transaction reaches its commit point.

Question 106

access plan

Answer 106

A set of instructions generated at application compilation time that is created and managed by a DBMS. The access plan predetermines how an application’s query will access the database at run time.

Question 107

automatic query optimization

Answer 107

A method by which a DBMS finds the most efficient access path for the execution of a query.

Question 108

b-tree index

Answer 108

An ordered data structure organized as an upside-down tree.

Question 109

bitmap index

Answer 109

An index that uses a bit array to represent the existence of a value or condition.

Question 110

buffer cache

Answer 110

A shared, reserved memory area that stores the most recently accessed data blocks in RAM. A buffer cache takes advantage of a computer’s fast primary memory compared to the slower secondary memory, minimizing the number of input/output operations between primary and secondary memory. Also called data cache.

Question 111

cluster indexed table

Answer 111

In a DBMS, a type of table storage organization that stores end-user data and index data in consecutive locations in permanent storage.

Question 112

cluster organized table

Answer 112

In a DBMS, a type of table storage organization that stores end-user data and index data in consecutive locations in permanent storage.

Question 113

cost-based optimizer

Answer 113

A query optimizer technique that uses an algorithm based on statistics about the objects being accessed, including number of rows, indexes available, index sparsity, and so on.

Question 114

data cache

Answer 114

A shared, reserved memory area that stores the most recently accessed data blocks in RAM. Also called buffer cache.

Question 115

data file

Answer 115

A named physical storage space that stores a database’s data. It can reside in a different directory on a hard disk or on one or more hard disks. All data in a database are stored in data files. A typical enterprise database is normally composed of several data files. A data file can contain rows from one or more tables.

Question 116

data sparsity

Answer 116

A column distribution of values or the number of different values a column can have.

Question 117

database performance tuning

Answer 117

A set of activities and procedures designed to reduce the response time of a database systemthat is, to ensure that an end-user query is processed by the DBMS in the minimum amount of time.

Question 118

database statistics

Answer 118

In query optimization, measurements about database objects, such as the number of rows in a table, number of disk blocks used, maximum and average row length, number of columns in each row, and number of distinct values in each column. Such statistics provide a snapshot of database characteristics.

Question 119

DBMS performance tuning

Answer 119

Activities to ensure that clients’ requests are addressed as quickly as possible while making optimum use of existing resources.

Question 120

dynamic query optimization

Answer 120

The process of determining the SQL access strategy at run time, using the most up-to-date information about the database. Contrast with static query optimization.

Question 121

dynamic statistical generation mode

Answer 121

In a DBMS, the capability to automatically evaluate and update the database access statistics after each data access.

Question 122

extends

Answer 122

In a DBMS environment, refers to the ability of data files to expand in size automatically using predefined increments.

Question 123

function-based index

Answer 123

A type of index based on a specific SQL function or expression.

Question 124

hash index

Answer 124

An index based on an ordered list of hash values.

Question 125

in-memory database

Answer 125

A database optimized to store large portions of the database in primary storage rather than secondary storage.

Question 126

index organized table

Answer 126

In a DBMS, a type of table storage organization that stores end-user data and index data in consecutive locations in permanent storage.

Question 127

index selectivity

Answer 127

A measure of how likely an index is to be used in query processing.

Question 128

input/output (I/O) request

Answer 128

A low-level operation that reads or writes data to and from computer devices such as memory, hard disks, video, and printers.

Question 129

manual query optimization

Answer 129

An operation mode that requires the end user or programmer to define the access path for the execution of a query.

Question 130

manual statistical generation mode

Answer 130

A mode of generating statistical data access information for query optimization. In this mode, the DBA must periodically run a routine to generate the data access statisticsfor example, running the RUNSTAT command in an IBM DB2 database.

Question 131

optimizer hints

Answer 131

Special instructions for the query optimizer that are embedded inside the SQL command text.

Question 132

procedure cache

Answer 132

A shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions. Also called SQL cache.

Question 133

query optimizer

Answer 133

A DBMS process that analyzes SQL queries and finds the most efficient way to access the data. The query optimizer generates the access or execution plan for the query.

Question 134

query processing bottleneck

Answer 134

In query optimization, a delay introduced in the processing of an I/O operation that causes the overall system to slow down.

Question 135

RAID

Answer 135

An acronym for Redundant Array of Independent Disks. RAID systems use multiple disks to create virtual disks from several individual disks. RAID systems provide performance improvement, fault tolerance, and a balance between the two.

Question 136

rule-based optimizer

Answer 136

A query optimization mode based on the rule-based query optimization algorithm.

Question 137

rule-based query optimization algorithm

Answer 137

A query optimization technique that uses preset rules and points to determine the best approach to executing a query.

Question 138

SQL cache

Answer 138

A shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions. Also called procedure cache.

Question 139

SQL performance tuning

Answer 139

Activities to help 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.

Question 140

static query optimization

Answer 140

A query optimization mode in which the access path to a database is predetermined at compilation time. Contrast with dynamic query optimization.

Question 141

statistically based query optimization algorithm

Answer 141

A query optimization technique that uses statistical information about a database. The DBMS then uses these statistics to determine the best access strategy.

Question 142

table space

Answer 142

In a DBMS, a logical storage space used to group related data. Also known as a file group.