IT EXAM 4- CHP 8-10

Stored Function

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

base table

The table on which a view is based.

Cohesivity

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

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.

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.

lock manager

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

concurrency control

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

Anonymous PL/SQL Block

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

UPDATE

A SQL command that allows attribute values to be changed in one or more rows of a table.

DELETE

A SQL command that allows data rows to be deleted from a table.

INSERT

A SQL command that allows the insertion of one or more data rows into a table using subquery

CREATE VIEW

A SQL command that creates a logical, "virtual" table. The view can be treated as a real table.

CREATE TABLE command

A SQL command that creates a tables structures using the characteristics and attributes given

CREATE INDEX

A SQL command that creates indexes on the basis of a selected attribute or attributes.

ROLLBACK

A SQL command that restores the database table contents to the condition that existed after the last COMMIT statement.

DROP INDEX

A SQL command used to delete database objects such as tables, views, indexes, and users.

DROP TABLE

A SQL command used to delete database objects such as tables, views, indexes, and users.

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.

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.

transaction log backup

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

persistent storage module (PSM)

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

full backup

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.

lost update

A concurrency control problem in which a data update is lost during the concurrent execution of transactions.

uncommitted data

A concurrency control problem in which a transaction accesses uncommitted data from another transaction.

inconsistent retrievals

A concurrency control problem that arises when a transactioncalculating summary (aggregate) functions over a set of data while other transactions are updating the data, yielding erroneous results.

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.

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.

mutual exclusive rule

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

deadlock

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

implicit cursor

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

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.

Consistency

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

consistent database state

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

isolation

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

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.

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.

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.

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.

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.

differential backup

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

field-level lock

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

binary lock

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

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 readonly 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.

trigger

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

centralized design

A process by which all database design decisions are carried out centrally by a small group of people. Suitable in a top-down design approach when the problem domain is relatively small, as in a single unit or department in an organization.

Decentralization

A process in which conceptual design models subsets of an organization's database requirements, which are then aggregated into a complete design. Such modular designs are typical of complex systems with a relatively large number of objects and procedures.

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.

serializability

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

monotonicity

A quality that ensures that time stamp values always increase. (The time stamping approach to scheduling concurrent transactions assigns a global, unique time stamp to each transaction. The time stamp value produces an explicit order in which transactions are submitted to the DBMS.)

batch update routine

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

Tranaction

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 aborted— no intermediate ending states are accepted. All transactions must have the properties of atomicity, consistency, isolation, and durability.

database role

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

two-phase locking (2PL) or deadly embrace

A set of rules that governs how transactions acquire and relinquish locks. guarantees serializability, but it does not prevent deadlocks. s 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.

cursor

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

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.

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.

clustered table

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

Static SQL

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

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.

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.

Virtualization

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

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.

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

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 that is saved as an object in the database.

Procedural Language SQL (PL/SQL)

An Oracle-specific programming language based on SQL with procedural extensions designed to run inside the Oracle database.

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.

Host Language

Any language that contains embedded SQL statements.

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.

uniqueness

In concurrency control, a property of time stamping that ensures no equal time stamp values can exist.

diskpage (page)

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.

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.

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.

optimistic approach

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

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.

dirty read

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

Embedded SQL

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

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

COMMIT

The SQL command that permanently writes data changes to a database.

ALTER TABLE

The SQL command used to make changes to table structure. When the command is followed by a keyword (ADD or MODIFY), it adds a column or changes column characteristics.

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.

database request

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

module coupling

The extent to which modules are independent of one another.

Boundaries

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

lock granularity

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

Database Development

The process of database design and implementation.

system analysis

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

Authentication

The process through which a DBMS verifies that only registered users can access the database.

durability

The transaction property that ensures that once transaction changes are done and committed, they cannot be undone or lost, even in the event of a system failure.

Atomicity

The transaction property that requires all parts of a transaction to be treated as a single, indivisible, logical unit of work. All parts of a transaction must be completed or the entire transaction is aborted.

pessimistic locking

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

computer - aided systems engineering (CASE)

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

reserved words

Words used by a system that cannot be used for any other purpose. For example, in Oracle SQL, the word INITIAL cannot be used to name tables or columns.

Schema

a logical grouping of database objects, such as tables, indexes, views, and queries, that are related to each other. Usually, a schema belongs to a single user or application

sequence

an object for generating unique sequential values for field

time stamping

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

scope

the part of a system that defines the extent of the define, according to operational requirements

exclusive lock

ssued 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.

systems development

the process create an information system