Definitions SQL TEST 1 (1) Flashcards
anonymous PL/SQL block
A PL/SQL block that has not been given a specific name.
base table
The table on which a view is based.
batch update routine
A routine that pools transactions into a single batch to update a master table in a single operation.
correlated subquery
A subquery that executes once for each row in the outer query.
CREATE VIEW
A SQL command that creates a logical, virtual table based on stored end-user tables. The view can be treated as a real table.
cross join
A join that performs a relational product of two tables.
cursor
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.
dynamic SQL
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.
embedded SQL
SQL statements contained within application programming languages such as COBOL, C++, ASP, Java, and ColdFusion.
explicit cursor
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.
host language
Any language that contains embedded SQL statements.
implicit cursor
A cursor that is automatically created in procedural SQL when the SQL statement returns only one value.
inner join
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.
outer join
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.
persistent stored module (PSM)
A block of code with standard SQL statements and procedural extensions that is stored and executed at the DBMS server.
Procedural Language SQL (PL/SQL)
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.
row-level trigger
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.
set-oriented
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.
statement-level trigger
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.
static SQL
A style of embedded SQL in which the SQL statements do not change while the application is running.
stored function
A named group of procedural and SQL statements that returns a value, as indicated by a RETURN statement in its program code.
stored procedure
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.
trigger
A procedural SQL code that is automatically invoked by the relational database management system when a data manipulation event occurs.
union-compatible
Two or more tables that share the same column names and have columns with compatible data types or domains.
updatable view
A view that can update attributes in base tables that are used in the view.
view
A virtual table based on a SELECT query.
bottom-up design
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.
boundaries
The external limits to which any proposed system is subjected. These limits include budgets, personnel, and existing hardware and software.
centralized design
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.
clustered table
A storage technique that stores related rows from two related tables in adjacent data blocks on disk.
cohesivity
The strength of the relationships between a module’s components. Module cohesivity must be high.
computer-aided systems engineering (CASE)
Tools used to automate part or all of the Systems Development Life Cycle.
conceptual design
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.
database development
The process of database design and implementation.
database fragment
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.
Database Life Cycle (DBLC)
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.
database role
A set of database privileges that could be assigned as a unit to a user or group.
decentralized design
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.
description of operations
A document that provides a precise, detailed, up-to-date, and thoroughly reviewed description of the activities that define an organization’s operating environment.
differential backup
A level of database backup in which only the last modifications to the database are copied.
full backup (database dump)
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.
information system
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.
logical design
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.
minimal data rule
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.
module
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.
module coupling
The extent to which modules are independent of one another.
physical design
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.
scope
The part of a system that defines the extent of the design, according to operational requirements.
systems analysis
The process that establishes the need for an information system and its extent.
systems development
The process of creating an information system.
Systems Development Life Cycle (SDLC)
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.
top-down design
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.
transaction log backup
A backup of only the transaction log operations that are not reflected in a previous backup copy of the database.
virtualization
A technique that creates logical representations of computing resources that are independent of the underlying physical computing resources.
atomic transaction property
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.
binary lock
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.
checkpoint
In transaction management, an operation in which the database management system writes all of its updated buffers to disk.
concurrency control
A DBMS feature that coordinates the simultaneous execution of transactions in a multiprocessing database system while preserving data integrity.
consistency
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.
consistent database state
A database state in which all data integrity constraints are satisfied.
database recovery
The process of restoring a database to a previous consistent state.
database request
The equivalent of a single SQL statement in an application program or a transaction.
database-level lock
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.
deadlock
A condition in which two or more transactions wait indefinitely for the other to release the lock on a previously locked data item.
deferred update
In transaction management, a condition in which transaction operations do not immediately update a physical database.
dirty read
In transaction management, when a transaction reads data that is not yet committed.
diskpage
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.
durability
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.
exclusive lock
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.
field-level lock
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.
immediate update
A database update that is performed immediately during a transaction’s execution, even before the transaction reaches its commit point.
inconsistent retrievals
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.
isolation
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.
lock
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.
lock granularity
The level of lock use. Locking can take place at the following levels: database, table, page, row, and field.
lock manager
A DBMS component that is responsible for assigning and releasing locks.
lost updates
A concurrency control problem in which data updates are lost during the concurrent execution of transactions.
monotonicity
A quality that ensures that timestamp values always increase.
mutual exclusive rule
A condition in which only one transaction at a time can own an exclusive lock on the same object.
nonrepeatable read
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.
optimistic approach
In transaction management, a concurrency control technique based on the assumption that most database operations do not conflict.
page-level lock
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.
pessimistic locking
The use of locks based on the assumption that conflict between transactions is likely.
phantom read
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.
Read Committed
An ANSI SQL transaction isolation level that allows transactions to read only committed data. This is the default mode of operations for most databases.
Read Uncommitted
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.
redundant transaction logs
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.
Repeatable Read
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.
row-level lock
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.
scheduler
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.
serializability
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.
serializable
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.
serializable schedule
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.
shared lock
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.
table-level lock
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.
timestamping
In transaction management, a technique used in scheduling concurrent transactions that assigns a global unique timestamp to each transaction.
transaction
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.
transaction log
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.
two-phase locking
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.
uncommitted data
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.
uniqueness
In concurrency control, a property of timestamping that ensures no equal timestamp values can exist.
wait/die
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.
wound/wait
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.
write-ahead-log protocol
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.
write-through technique
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.
access plan
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.
automatic query optimization
A method by which a DBMS finds the most efficient access path for the execution of a query.
b-tree index
An ordered data structure organized as an upside-down tree.
bitmap index
An index that uses a bit array to represent the existence of a value or condition.
buffer cache
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.
cluster indexed table
In a DBMS, a type of table storage organization that stores end-user data and index data in consecutive locations in permanent storage.
cluster organized table
In a DBMS, a type of table storage organization that stores end-user data and index data in consecutive locations in permanent storage.
cost-based optimizer
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.
data cache
A shared, reserved memory area that stores the most recently accessed data blocks in RAM. Also called buffer cache.
data file
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.
data sparsity
A column distribution of values or the number of different values a column can have.
database performance tuning
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.
database statistics
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.
DBMS performance tuning
Activities to ensure that clients’ requests are addressed as quickly as possible while making optimum use of existing resources.
dynamic query optimization
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.
dynamic statistical generation mode
In a DBMS, the capability to automatically evaluate and update the database access statistics after each data access.
extends
In a DBMS environment, refers to the ability of data files to expand in size automatically using predefined increments.
function-based index
A type of index based on a specific SQL function or expression.
hash index
An index based on an ordered list of hash values.
in-memory database
A database optimized to store large portions of the database in primary storage rather than secondary storage.
index organized table
In a DBMS, a type of table storage organization that stores end-user data and index data in consecutive locations in permanent storage.
index selectivity
A measure of how likely an index is to be used in query processing.
input/output (I/O) request
A low-level operation that reads or writes data to and from computer devices such as memory, hard disks, video, and printers.
manual query optimization
An operation mode that requires the end user or programmer to define the access path for the execution of a query.
manual statistical generation mode
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.
optimizer hints
Special instructions for the query optimizer that are embedded inside the SQL command text.
procedure cache
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.
query optimizer
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.
query processing bottleneck
In query optimization, a delay introduced in the processing of an I/O operation that causes the overall system to slow down.
RAID
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.
rule-based optimizer
A query optimization mode based on the rule-based query optimization algorithm.
rule-based query optimization algorithm
A query optimization technique that uses preset rules and points to determine the best approach to executing a query.
SQL cache
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.
SQL performance tuning
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.
static query optimization
A query optimization mode in which the access path to a database is predetermined at compilation time. Contrast with dynamic query optimization.
statistically based query optimization algorithm
A query optimization technique that uses statistical information about a database. The DBMS then uses these statistics to determine the best access strategy.
table space
In a DBMS, a logical storage space used to group related data. Also known as a file group.
