finalexam304

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.

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 (committed) to produce a consistent database.

Boolean algebra

A branch of mathematics that uses the logical operators OR, AND, and NOT.

sparse data

A case in which the number of table attributes is very large but the number of actual data instances is low.

attribute

A characteristic of an entity or object. An attribute has a name and a data type.

HAVING

A clause applied to the output of a GROUP BY operation to restrict selected rows.

relational database management system (RDBMS)

A collection of programs that manages a relational database. The RDBMS software translates a user's logical requests (queries) into commands that physically locate and retrieve the requested data. A good RDBMS also creates and maintains a data dictionary to help provide data security, data integrity, concurrent and easy access, and system administration to the data through a query language (SQL) and application programs.

record

A collection of related (logically connected) fields.

class

A collection of similar objects with shared structure (attributes) and behavior (methods). A class encapsulates an object's data representation and a method's implementation. Classes are organized in a class hierarchy.

data sparsity

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

data quality

A comprehensive approach to ensuring the accuracy, validity, and timeliness of data.

business intelligence

A comprehensive, cohesive, and integrated set of tools and processes used to capture, collect, integrate, store, and analyze data with the purpose of generating and presenting information to support business decision making.

business intelligence (BI)

A comprehensive, cohesive, and integrated set of tools and processes used to capture, collect, integrate, store, and analyze data with the purpose of generating and presenting information to support business decision making.

lost updates

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

inconsistent retrievals

A concurrency control problem that arises when a transaction-calculating 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.

transactional database

A database designed to keep track of the day-to-day transactions of an organization.

analytical database

A database focused primarily on storing historical data and business metrics used exclusively for tactical or strategic decision making.

centralized database

A database located at a single site.

multidimensional database management system (MDBMS)

A database management system that uses proprietary techniques to store data in matrixlike arrays of n dimensions known as cubes.

consistent database state

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

XML database

A database system that stores and manages semistructured XML data.

discipline-specific database

A database that contains data focused on specific subject areas.

single-user database

A database that supports only one user at a time.

immediate update

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

business rule

A description of a policy, procedure, or principle within an organization. For example, a pilot cannot be on duty for more than 10 hours during a 24-hour period, or a professor may teach up to four classes during a semester.

fan trap

A design trap that occurs when one entity is in two 1:M relationships with other entities, thus producing an association among the other entities that is not expressed in the model.

system catalog

A detailed system data dictionary that describes all objects in a database.

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.

entity relationship diagram (ERD)

A diagram that depicts an entity relationship model's entities, attributes, and relations.

class diagram

A diagram used to represent data and their relationships in UML object notation.

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.

table

A matrix composed of intersecting rows (entities) and columns (attributes) that represents an entity set in the relational model. Also called a relation.

index selectivity

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

existence-independent

A property of an entity that can exist apart from one or more related entities. Such a table must be created first when referencing an existence-dependent table.

existence-dependent

A property of an entity whose existence depends on one or more other entities. In such an environment, the existence-independent table must be created and loaded first because the existence-dependent key cannot reference a table that does not yet exist.

software independence

A property of any model or application that does not depend on the software used to implement it.

closure

A property of relational operators that permits the use of relational algebra operators on existing tables (relations) to produce new relations.

cardinality

A property that assigns a specific value to connectivity and expresses the range of allowed entity occurrences associated with am single occurrence of the related entity.

third normal form (3NF)

A table is in 3NF when it is in 2NF and no nonkey attribute is functionally dependent on another nonkey attribute; that is, it cannot include transitive dependencies.

fourth normal form (4NF)

A table that is in 3NF and contains no multiple independent sets of multivalued dependencies.

attribute hierarchy

A top-down data organization that is used for two main purposes: aggregation and drill-down/roll-up data analysis.

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.

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.

function-based index

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

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.

snowflake schema

A type of star schema in which dimension tables can have their own dimension tables. The snowflake schema is usually the result of normalizing dimension tables.

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.

derived attribute

An attribute that does not physically exist within the entity and is derived via an algorithm. For example, the Age attribute might be derived by subtracting the birth date from the current date.

nonprime attribute

An attribute that is not part of a key.

dependent

An attribute whose value is determined by another attribute.

entity subtype

In a generalization/specialization hierarchy, a subset of an entity supertype. The entity supertype contains the common characteristics and the subtypes contain the unique characteristics of each entity.

right outer join

In a pair of tables to be joined, a join that yields all of the rows in the right table, including the ones with no matching values in the other table. For example, a right outer join of CUSTOMER with AGENT will yield all of the AGENT rows, including the ones that do not have a matching CUSTOMER row. See also left outer join and outer join.

left outer join

In a pair of tables to be joined, a join that yields all the rows in the left table, including those that have no matching values in the other table. For example, a left outer join of CUSTOMER with AGENT will yield all of the CUSTOMER rows, including the ones that do not have a matching AGENT row. See also outer join and right outer join.

repeating group

In a relation, a characteristic describing a group of multiple entries of the same type for a single key attribute occurrence. For example, a car can have multiple colors for its top, interior, bottom, trim, and so on.

relation

In a relational database model, an entity set. Relations are implemented as tables. Relations are related to each other through the sharing of a common entity characteristic (a value in a column).

data integrity

In a relational database, a condition in which the data in the database comply with all entity and referential integrity constraints.

entity set

In a relational model, a grouping of related entities.

overlapping

In a specialization hierarchy, a condition in which each entity instance (row) of the supertype can appear in more than one subtype.

disjoint subtype (nonoverlapping subtype)

In a specialization hierarchy, a unique and nonoverlapping subtype entity set.

generalization

In a specialization hierarchy, the grouping of common attributes into a supertype entity. See specialization hierarchy.

specialization

In a specialization hierarchy, the grouping of unique attributes into a subtype entity. See specialization hierarchy.

dimensions

In a star schema design, qualifying characteristics that provide additional perspectives to a given fact.

identifiers

In an ERM, unique names of each entity instance. In the relational model, such identifiers are mapped to primary keys in tables.

dashboard

In business intelligence, a Web-based system that presents key business performance indicators or information in a single, integrated view with clear and concise graphics.

master data management (MDM)

In business intelligence, a collection of concepts, techniques, and processes for the proper identification, definition, and management of data elements within an organization.

primary key (PK)

In the relational model, an identifier composed of one or more attributes that uniquely identifies a row. Also, a candidate key selected as a unique entity identifier. See also key.

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.

deferred update

In transaction management, a condition in which transaction operations do not immediately update a physical database. Also called deferred write technique.

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.

timestamping

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

checkpoint

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

periodicity

Information about the time span of data stored in a table, usually expressed as current year only, previous years, or all years.

flags

Special codes implemented by designers to trigger a required response, alert end users to specified conditions, or encode values. Flags may be used to prevent nulls by bringing attention to the absence of a value in a table.

optimizer hints

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

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.

drill down

To decompose data into more atomic components—that is, data at lower levels of aggregation. This approach is used primarily in a decision support system to focus on specific geographic areas, business types, and so on. See also roll up.

union-compatible

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

NOT

A SQL logical operator that negates a given predicate.

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.

persistent stored module (PSM)

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

ad hoc query

A "spur-of-the-moment" question.

entity cluster

A "virtual" entity type used to represent multiple entities and relationships in the ERD. An entity cluster is formed by combining multiple interrelated entities into a single abstract entity object. An entity cluster is considered "virtual" or "abstract" because it is not actually an entity in the final ERD.

object/relational database management system (O/R DBMS)

A DBMS based on the extended relational model (ERDM). The ERDM, championed by many relational database researchers, constitutes the relational model's response to the OODM. This model includes many of the object-oriented model's best features within an inherently simpler relational database structure.

lock manager

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

data dictionary

A DBMS component that stores metadata—data about data. Thus, the data dictionary contains the data definition as well as their characteristics and relationships. A data dictionary may also include data that are external to the DBMS. Also known as an information resource dictionary.

concurrency control

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

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.

data processing (DP) manager

A DP specialist who evolved into a department supervisor. Roles include managing technical and human resources, supervising senior programmers, and troubleshooting the program. Also known as a data manager (DM).

anonymous PL/SQL block

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

AVG

A SQL aggregate function that outputs the mean average for a specified column or expression.

COUNT

A SQL aggregate function that outputs the number of rows containing not null values for a given column or expression, sometimes used in conjunction with the DISTINCT clause.

MAX

A SQL aggregate function that yields the maximum attribute value in a given column.

MIN

A SQL aggregate function that yields the minimum attribute value in a given column.

SUM

A SQL aggregate function that yields the sum of all values for a given column or expression.

ORDER BY

A SQL clause that is useful for ordering the output of a SELECT query (for example, in ascending or descending order).

DISTINCT

A SQL clause that produces only a list of values that are different from one another.

GROUP BY

A SQL clause used to create frequency distributions when combined with any of the aggregate functions in a SELECT statement.

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 a subquery.

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.

CREATE TABLE

A SQL command that creates a table's 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.

SELECT

A SQL command that yields the values of all rows or a subset of rows in a table. The SELECT statement is used to retrieve data from tables.

DROP

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

referential integrity

A condition by which a dependent table's foreign key must have either a null entry or a matching entry in the related table. Even though an attribute may not have a corresponding attribute, it is impossible to have an invalid entry.

data redundancy

A condition in which a data environment contains redundant (unnecessarily duplicated) data.

hardware independence

A condition in which a model does not depend on the hardware used in the model's implementation. Therefore, changes in the hardware will have no effect on the database design at the conceptual level.

transitive dependency

A condition in which an attribute is dependent on another attribute that is not part of the primary key.

full functional dependence

A condition in which an attribute is functionally dependent on a composite key but not on any subset of the key.

data independence

A condition in which data access is unaffected by changes in the physical data storage characteristics.

data inconsistency

A condition in which different versions of the same data yield different (inconsistent) results.

mutual exclusive rule

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

logical independence

A condition in which the internal model can be changed without affecting the conceptual model. (The internal model is hardware-independent because it is unaffected by the computer on which the software is installed. Therefore, a change in storage devices or operating systems will not affect the internal model.)

physical independence

A condition in which the physical model can be changed without affecting the internal model.

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 called deadly embrace. See also lock.

completeness constraint

A constraint that specifies whether each entity supertype occurrence must also be a member of at least one subtype. The completeness constraint can be partial or total. Partial completeness means that some supertype occurrences might not be members of any subtype. Total completeness means that every supertype occurrence must be a member of at least one subtype.

implicit cursor

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

data anomaly

A data abnormality in which inconsistent changes have been made to a database. For example, an employee moves, but the address change is not corrected in all files in the database.

end-user presentation tool

A data analysis tool that organizes and presents selected data compiled by the end-user query tool.

end-user query tool

A data analysis tool used to create the queries that access desired information from the data store.

structural dependence

A data characteristic in which a change in the database schema affects data access, thus requiring changes in all access programs.

structural independence

A data characteristic in which changes in the database schema do not affect data access.

data dependence

A data condition in which data representation and manipulation are dependent on the physical data storage characteristics.

key-value

A data model based on a structure composed of two data elements: a key and a value, in which every key has a corresponding value or set of values. The key-value data model is also called the associative or attribute-value data model.

network model

A data model standard created in the late 1960s that represented data as a collection of record types and relationships as predefined sets with an owner record type and a member record type in a 1:M relationship.

entity relationship (ER) model (ERM)

A data model that describes relationships (1:1, 1:M, and M:N) among entities at the conceptual level with the help of ER diagrams. The model was developed by P. Chen in 1975.

object-oriented data model (OODM)

A data model whose basic modeling structure is an object.

star schema

A data modeling technique used to map multidimensional decision support data into a relational database. The star schema represents data using a central table known as a fact table in a 1:M relationship with one or more dimension tables.

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.

operational database

A database designed primarily to support a company's day-to-day operations. Also known as a transactional database or production database.

materialized view

A dynamic table that not only contains the SQL query command to generate rows but stores the actual rows. The materialized view is created the first time the query is run and the summary rows are stored in the table. The materialized view rows are automatically updated when the base tables are updated.

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.

natural key (natural identifier)

A generally accepted identifier for real-world objects. As its name implies, a natural key is familiar to end users and forms part of their day-to-day business vocabulary.

relational diagram

A graphical representation of a relational database's entities, the attributes within those entities, and the relationships among the entities.

specialization hierarchy

A hierarchy based on the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype. Specialization is based on grouping unique characteristics and relationships of the subtypes.

inner join

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

equijoin

A join operator that links tables based on an equality condition that compares specified columns of the tables.

theta join

A join operator that links tables using an inequality comparison operator (<, >, <=, >=) in the join condition.

cross join

A join that performs a relational product (or Cartesian product) of two tables.

prime attribute

A key attribute; that is, an attribute that is part of a key or is the whole key. See also key attributes.

secondary key

A key used strictly for data retrieval purposes. For example, customers are not likely to know their customer number (primary key), but the combination of last name, first name, middle initial, and telephone number will probably match the appropriate table row. See also key.

Unified Modeling Language (UML)

A language based on object-oriented concepts that provides tools such as diagrams and symbols to graphically model a system.

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.

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. See also lock.

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. See also exclusive lock.

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. See also shared lock.

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.

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.

distributed database

A logically related database that is stored in two or more physically independent sites.

Extensible Markup Language (XML)

A metalanguage used to represent and manipulate data elements. Unlike other markup languages, XML permits the manipulation of a document's data elements. XML facilitates the exchange of structured documents such as orders and invoices over the Internet.

automatic query optimization

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

candidate key

A minimal superkey; that is, a key that does not contain a subset of attributes that is itself a superkey. See key.

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 statistics—for example, running the RUNSTAT command in an IBM DB2 database.

eventual consistency

A model for database consistency in which updates to the database will propagate through the system so that all data copies will be consistent eventually.

physical model

A model in which physical characteristics such as location, path, and format are described for the data. The physical model is both hardware- and software-dependent.

extended relational data model (ERDM)

A model that includes the object-oriented model's best features in an inherently simpler relational database structural environment.

Big Data

A movement to find new and better ways to manage large amounts of Web-generated data and derive business insight from it, while simultaneously providing high performance and scalability at a reasonable cost.

composite key

A multiple-attribute key.

file

A named collection of related records.

stored function

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

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.

cascading order sequence

A nested ordering sequence for a set of rows, such as a list in which all last names are alphabetically ordered and, within the last names, all first names are ordered.

recursive query

A nested query that joins a table to itself.

NoSQL

A new generation of database management systems that is not based on the traditional relational database model.

query language

A nonprocedural language that is used by a DBMS to manipulate its data. An example of a query language is SQL.

set theory

A part of mathematical science that deals with sets, or groups of things, and is used as the basis for data manipulation in the relational model.

entity

A person, place, thing, concept, or event for which data can be stored. See also attribute.

Structured Query Language (SQL)

A powerful and flexible relational database language composed of commands that enable users to create database and table structures, perform various types of data manipulation and data administration, and query the database to extract useful information.

design trap

A problem that occurs when a relationship is improperly or incompletely identified and therefore is represented in a way that is not consistent with the real world. The most common design trap is known as a fan trap.

trigger

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

iterative process

A process based on repetition of steps and procedures.

denormalization

A process by which a table is changed from a higher-level normal form to a lower-level normal form, usually to increase processing speed. Denormalization potentially yields data anomalies.

algorithm

A process or set of operations in a calculation. The most common algorithms used in data mining are based on neural networks, decision trees, rules induction, genetic algorithms, classification and regression trees, memory-based reasoning, and nearest neighbor.

normalization

A process that assigns attributes to entities so that data redundancies are reduced or eliminated.

data mining

A process that employs automated tools to analyze data in a data warehouse and other sources and to proactively identify possible relationships and anomalies.

data management

A process that focuses on data collection, storage, and retrieval. Common data management functions include addition, deletion, modification, and listing.

data extraction

A process used to extract and validate data from an operational database and external data sources prior to their placement in a data warehouse.

data filtering

A process used to extract and validate data from an operational database and external data sources prior to their placement in a data warehouse. See data extraction.

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.

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.

monotonicity

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

rule-based optimizer

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

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.

rule-based query optimization algorithm

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

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.

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.

subquery

A query that is embedded (or nested) inside another query. Also known as a nested query or an inner query.

inner query

A query that is embedded or nested inside another query. Also known as a nested query or a subquery.

query

A question or task asked by an end user of a database in the form of SQL code. A specific request for data manipulation issued by the end user or the application to the DBMS.

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.

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. See also left outer join and right outer join.

natural join

A relational operation that links tables by selecting only the rows with common values in their common attribute(s).

recursive relationship

A relationship found within a single entity type. For example, an EMPLOYEE is married to an EMPLOYEE or a PART is a component of another PART.

mandatory participation

A relationship in which one entity occurrence must have a corresponding occurrence in another entity. For example, an EMPLOYEE works in a DIVISION. (A person cannot be an employee without being assigned to a company's division.)

identifying relationship

A relationship in which related entities are existence-dependent. Also called a strong relationship or strong identifying relationship because the dependent entity's primary key contains the primary key of the parent entity.

non-identifying relationship

A relationship in which the primary key of the dependent (many side) entity does not contain the primary key of the related parent entity. Also known as a weak relationship.

weak relationship

A relationship in which the primary key of the related entity does not contain a primary key component of the parent entity. Also known as a non-identifying relationship.

strong (identifying) relationship

A relationship that occurs when two entities are existence-dependent; from a database design perspective, this relationship exists whenever the primary key of the related entity contains the primary key of the parent entity.

dependency diagram

A representation of all data dependencies (primary key, partial, or transitive) within a table.

internal schema

A representation of an internal model using the database constructs supported by the chosen database.

conceptual schema

A representation of the conceptual model, usually expressed graphically. See also conceptual model.

Crow's Foot notation

A representation of the entity relationship diagram that uses a three-pronged symbol to represent the "many" sides of the relationship.

data model

A representation, usually graphic, of a complex "real-world" data structure. Data models are used in the database design phase of the Database Life Cycle.

constraint

A restriction placed on data, usually expressed in the form of rules. For example, "A student's GPA must be between 0.00 and 4.00." Constraints are important because they help to ensure data integrity.

batch update routine

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

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

database performance tuning

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

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.

relational algebra

A set of mathematical principles that form the basis for manipulating relational table contents; the eight main functions are SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE.

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.

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 (I/O) operations between primary and secondary memory. Also called data cache.

data cache

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

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.

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.

desktop database

A single-user database that runs on a personal computer.

data mart

A small, single-subject data warehouse subset that provides decision support to a small group of people.

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.

Boyce-Codd normal form (BCNF)

A special type of third normal form (3NF) in which every determinant is a candidate key. A table in BCNF must be in 3NF. See also determinant.

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.

static SQL

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

correlated subquery

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

data analytics

A subset of business intelligence functions that encompasses a wide range of mathematical, statistical, and modeling techniques with the purpose of extracting knowledge from data.

wildcard character

A symbol that can be used as a general substitute for one or more characters in a SQL LIKE clause condition. The wildcard characters used in SQL are the _ and % symbols

surrogate key

A system-assigned primary key, generally numeric and auto-incremented.

performance tuning

Activities that make a database perform more efficiently in terms of storage and access speed.

DBMS performance tuning

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

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.

binary relationship

An ER term for an association (relationship) between two entities. For example, PROFESSOR teaches COURSE.

participants

An ER term for entities that participate in a relationship. For example, in the relationship "PROFESSOR teaches CLASS," the teaches relationship is based on the participants PROFESSOR and CLASS.

ternary relationship

An ER term used to describe an association (relationship) between three entities. For example, a CONTRIBUTOR contributes money to a FUND from which a RECIPIENT receives money.

unary relationship

An ER term used to describe an association within an entity. For example, a COURSE might be a prerequisite to another COURSE.

object

An abstract representation of a real-world entity that has a unique identity, embedded properties, and the ability to interact with other objects and itself.

RAID

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

field

An alphabetic or numeric character or group of characters that defines a characteristic of a person, place, or thing. For example, a person's Social Security number, address, phone number, and bank balance all constitute fields.

alias

An alternative name for a column or table in a SQL statement.

decision support system (DSS)

An arrangement of computerized tools used to assist managerial decision making within a business.

relationship

An association between entities.

foreign key (FK)

An attribute or attributes in one table whose values must match the primary key in another table or whose values must be null. See key.

superkey

An attribute or attributes that uniquely identify each entity in a table. See key.

composite attribute

An attribute that can be further subdivided to yield additional attributes. For example, a phone number such as 615-898-2368 may be divided into an area code (615), an exchange number (898), and a four-digit code (2368). Compare to simple attribute.

multivalued attributes

An attribute that can have many values for a single entity occurrence. For example, an EMP_DEGREE attribute might store the string "BBA, MBA, PHD" to indicate three different degrees held.

single-valued attribute

An attribute that can have only one value.

atomic attribute

An attribute that cannot be further subdivided to produce meaningful components. For example, a person's last name attribute cannot be meaningfully subdivided.

simple attribute

An attribute that cannot be subdivided into meaningful components. Compare to composite attribute.

hierarchical model

An early database model whose basic concepts and characteristics formed the basis for subsequent database development. This model is based on an upside-down tree structure in which each record is called a segment. The top record is the root segment. Each segment has a 1:M relationship to the segment directly below it.

composite entity

An entity designed to transform an M:N relationship into two 1:M relationships. The composite entity's primary key comprises at least the primary keys of the entities that it connects. Also known as a bridge entity. See also linking table.

key

An entity identifier based on the concept of functional dependence; keys may be classified in several ways. See also superkey, candidate key, primary key (PK), secondary key, and foreign key.

supertype (entity set)

An entity set that contains the commonly shared characteristics of its entities (see subtype). If the entity set can include characteristics that are not common to all entities within the set, the supertype becomes the parent to one or more subtypes in a generalization hierarchy.

subtype (entity set)

An entity that contains unique characteristics (attributes) within a more broadly defined entity known as a supertype. In a generalization hierarchy, a subtype is any entity below a parent entity. For example, PILOT could be a subtype of the supertype EMPLOYEE.

weak entity

An entity that displays existence dependence and inherits the primary key of its parent entity. For example, a DEPENDENT requires the existence of an EMPLOYEE.

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.

multidimensional online analytical processing (MOLAP)

An extension of online analytical processing to multidimensional database management systems.

hash index

An index based on an ordered list of hash values.

unique index

An index in which the index key can have only one associated pointer value (row).

bitmap index

An index that uses a bit array (0s and 1s) to represent the existence of a value or condition.

data warehouse

An integrated, subject-oriented, time-variant, nonvolatile collection of data that provides support for decision making, according to Bill Inmon, the acknowledged "father of the data warehouse."

manual query optimization

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

index

An ordered array of index key values and row ID values (pointers). Indexes are generally used to speed up and facilitate data retrieval. Also known as an index key.

b-tree index

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

database system

An organization of components that defines and regulates the collection, storage, management, and use of data in a database environment.

relational online analytical processing (ROLAP)

Analytical processing functions that use relational databases and familiar relational query tools to store and analyze multidimensional data.

determinant

Any attribute in a specific row whose value directly determines other values in that row.

determinant

Any attribute in a specific row whose value directly determines other values in that row. See also Boyce-Codd normal form (BCNF).

host language

Any language that contains embedded SQL statements.

islands of information

In the old file system environment, pools of independent, often duplicated, and inconsistent data created and managed by different departments.

many-to-many (M:N or *..*) relationship

Associations among two or more entities in which one occurrence of an entity is associated with many occurrences of a related entity and one occurrence of the related entity is associated with many occurrences of the first entity.

one-to-one (1:1 or 1..1) relationship

Associations among two or more entities that are used by data models. In a 1:1 relationship, one entity instance is associated with only one instance of the related entity.

one-to-many (1:M or 1..*) relationship

Associations among two or more entities that are used by data models. In a 1:M relationship, one entity instance is associated with many instances of the related entity.

rules of precedence

Basic algebraic rules that specify the order in which operations are performed. For example, operations within parentheses are executed first, so in the equation 2 + (3 × 5), the multiplication portion is calculated first, making the correct answer 17.

join column(s)

Columns that join two tables. The join columns generally share similar values.

metadata

Data about data; that is, data about data characteristics and relationships. See also data dictionary.

explanatory analytics

Data analysis that provides ways to discover relationships, trends, and patterns among data.

predictive analytics

Data analytics that use advanced statistical and modeling techniques to predict future business outcomes with great accuracy.

object-oriented database management system (OODBMS)

Data management software used to manage data in an object-oriented database model.

unstructured data

Data that exist in their original, raw state; that is, in the format in which they were collected.

semistructured data

Data that have already been processed to some extent.

tuple

In the relational model, a table row.

time-variant data

Data whose values are a function of time. For example, time-variant data can be seen at work when a company's history of all administrative appointments is tracked.

very large databases (VLDBs)

Databases that contain huge amounts of data—gigabyte, terabyte, and petabyte ranges are not unusual.

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.

online analytical processing (OLAP)

Decision support system (DSS) tools that use multidimensional data analysis techniques. OLAP creates an advanced data analysis environment that supports decision making, business modeling, and operations research.

relational model

Developed by E. F. Codd of IBM in 1970, it represented a major breakthrough for users and designers because of its conceptual simplicity. The relational model is based on mathematical set theory and represents data as independent relations. Each relation (table) is conceptually represented as a matrix of intersecting rows and columns. The relations are related to each other through the sharing of common entity characteristics (values in columns).

entity supertype

In a generalization/specialization hierarchy, a generic entity type that contains the common characteristics of entity subtypes.

optional participation

In ER modeling, a condition in which one entity occurrence does not require a corresponding entity occurrence in a particular relationship.

composite identifier

In ER modeling, a key composed of more than one attribute.

entity instance

In ER modeling, a specific table row. Also known as an entity occurrence.

optional attribute

In ER modeling, an attribute that does not require a value; therefore, it can be left empty.

required attribute

In ER modeling, an attribute that must have a value. In other words, it cannot be left empty.

EXISTS

In SQL, a comparison operator that checks whether a subquery returns any rows.

IN

In SQL, a comparison operator used to check whether a value is among a list of specified values.

IS NULL

In SQL, a comparison operator used to check whether an attribute has a value.

LIKE

In SQL, a comparison operator used to check whether an attribute's text value matches a specified string pattern.

nested query

In SQL, a query that is embedded in another query. See subquery.

BETWEEN

In SQL, a special comparison operator used to check whether a value is within a range of specified values.

roll up

In SQL, an OLAP extension used with the GROUP BY clause to aggregate data by different dimensions. Rolling up the data is the exact opposite of drilling down the data. See also drill down.

null

In SQL, the absence of an attribute value. Note that a null is not a blank.

extends

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

table space

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

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. Also known as cluster-indexed table.

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. See index organized table.

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. See index organized table.

dynamic statistical generation mode

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

metrics

In a data warehouse, numeric facts that measure a business characteristic of interest to the end user.

dimension tables

In a data warehouse, tables used to search, filter, or classify facts within a star schema. The fact table is in a one-to-many relationship with dimension tables.

facts

In a data warehouse, the measurements (values) that represent a specific business aspect or activity. For example, sales figures are numeric measurements that represent product or service sales. Facts commonly used in business data analysis include units, costs, prices, and revenues.

fact table

In a data warehouse, the star schema table that contains facts linked and classified through their common dimensions. A fact table is in a one-to-many relationship with each associated dimension table.

extraction, transformation, and loading (ETL)

In a data warehousing environment, the integrated processes of getting data from original sources into the data warehouse. ETL includes retrieving data from original data sources (extraction), manipulating the data into an appropriate form (transformation), and storing the data in the data warehouse (loading).

partial completeness

In a generalization hierarchy, a condition in which some supertype occurrences might not be members of any subtype.

total completeness

In a generalization/specialization hierarchy, a condition in which every supertype occurrence must be a member of at least on

key performance indicators (KPIs)

In business intelligence, quantifiable numeric or scale-based measurements that assess a company's effectiveness or success in reaching strategic and operational goals. Examples of KPI are product turnovers, sales by promotion, sales by employee, and earnings per share.

governance

In business intelligence, the methods for controlling and monitoring business health and promoting consistent decision making.

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.

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.

uniqueness

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

domain

In data modeling, the construct used to organize and describe an attribute's set of possible values.

internal model

In database modeling, a level of data abstraction that adapts the conceptual model to a specific DBMS model for implementation. The internal model is the representation of a database as "seen" by the DBMS. In other words, the internal model requires a designer to match the conceptual model's characteristics and constraints to those of the selected implementation model.

cube cache

In multidimensional OLAP, the shared, reserved memory area where data cubes are held. Using the cube cache assists in speeding up data access.

sparsity

In multidimensional data analysis, a measurement of the data density held in the data cube.

partial dependency

In normalization, a condition in which an attribute is dependent on only a portion (subset) of the primary key.

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.

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.

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.

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.

portal

In terms of business intelligence, a unified, single point of entry for information distribution.

segment

In the hierarchical data model, the equivalent of a file system's record type.

subschema

In the network model, the portion of the database "seen" by the application programs that produce the desired information from the data in the database.

method

In the object-oriented data model, a named set of instructions to perform an action. Methods represent real-world actions, and are invoked through messages.

inheritance

In the object-oriented data model, the ability of an object to inherit the data structure and methods of the classes above it in the class hierarchy.

inheritance

In the object-oriented data model, the ability of an object to inherit the data structure and methods of the classes above it in the class hierarchy. See also class hierarchy.

linking table

In the relational model, a table that implements an M:M relationship. See also composite entity.

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.

DROP TABLE

Permanently deletes a table (and its data)

DROP VIEW

Permanently deletes a view

DROP INDEX

Permanently deletes an index

data

Raw facts, or facts that have not yet been processed to reveal their meaning to the end user.

embedded SQL

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

XML

See Extensible Markup Language (XML).

atomicity

See atomic transaction property.

associative entity

See composite entity.

bridge entity

See composite entity.

deadly embrace

See deadlock.

deferred-write technique

See deferred update.

page

See diskpage.

attribute domain

See domain.

entity occurrence

See entity instance.

Chen notation

See entity relationship (ER) model.

index key

See index.

nonkey attribute

See nonprime attribute.

file group

See table space.

extended entity relationship model (EERM)

Sometimes referred to as the enhanced entity relationship model; the result of adding more semantic constructs, such as entity supertypes, entity subtypes, and entity clustering, to the original entity relationship (ER) model.

COMMIT

The SQL command that permanently saves 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.

OR

The SQL logical operator used to link multiple conditional expressions in a WHERE or HAVING clause. It requires only one of the conditional expressions to be true.

AND

The SQL logical operator used to link multiple conditional expressions in a WHERE or HAVING clause. It requires that all conditional expressions evaluate to true.

slice and dice

The ability to cut slices off a data cube (drill down or drill up) to perform a more detailed analysis.

external model

The application programmer's view of the data environment. Given its business focus, an external model works with a data subset of the global database schema.

subtype discriminator

The attribute in the supertype entity that determines to which entity subtype each supertype occurrence is related.

key attribute

The attributes that form a primary key.

key attributes

The attributes that form a primary key. See also prime attribute.

knowledge

The body of information and facts about a specific subject. Knowledge implies familiarity, awareness, and understanding of information as it applies to an environment. A key characteristic is that new knowledge can be derived from old knowledge.

connectivity

The classification of the relationship between entities. Classifications include 1:1, 1:M, and M:N.

query result set

The collection of data rows returned by a query.

database management system (DBMS)

The collection of programs that manages the database structure and controls access to the data stored in the database.

data store

The component of the decision support system that acts as a database for storage of business data and business model data. The data in the data store have already been extracted and filtered from the external and operational data, and will be stored for access by the end-user query tool for the business data model.

EER diagram (EERD)

The entity relationship diagram resulting from the application of extended entity relationship concepts that provide additional semantic content in the ER model.

database request

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

semantic data model

The first of a series of data models that more closely represented the real world, modeling both data and their relationships in a single structure known as an object. The SDM, published in 1981, was developed by M. Hammer and D. McLeod.

first normal form (1NF)

The first stage in the normalization process. It describes a relation depicted in tabular format, with no repeating groups and a primary key identified. All nonkey attributes in the relation are dependent on the primary key.

American National Standards Institute (ANSI)

The group that accepted the DBTG recommendations and augmented database standards in 1975 through its SPARC committee.

data definition language (DDL)

The language that allows a database administrator to define the database structure, schema, and subschema.

granularity

The level of detail represented by the values stored in a table's row. Data stored at their lowest level of granularity are said to be atomic data.

lock granularity

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

data cube

The multidimensional data structure used to store and manipulate data in a multidimensional DBMS. The location of each data value in the data cube is based on its x-, y-, and z-axes. Data cubes are static, meaning they must be created before they are used, so they cannot be created by an ad hoc query.

relationship degree

The number of entities or participants associated with a relationship. A relationship degree can be unary, binary, ternary, or higher.

relational schema

The organization of a relational database as described by the database administrator.

class hierarchy

The organization of classes in a hierarchical tree in which each parent class is a superclass and each child class is a subclass. See also inheritance.

conceptual model

The output of the conceptual design process. The conceptual model provides a global view of an entire database and describes the main data objects, avoiding details.

enterprise database

The overall company data representation, which provides support for present and expected future needs.

replication

The process of creating and managing duplicate versions of a database. Replication is used to place copies in different locations and to improve access time and fault tolerance.

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.

database recovery

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

partitioning

The process of splitting a table into subsets of rows or columns.

database design

The process that yields the description of the database structure and determines the database components. Database design is the second phase of the Database Life Cycle.

authentication

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

entity integrity

The property of a relational table that guarantees each entity has a unique value in a primary key and that the key has no null values.

information

The result of processing raw data to reveal its meaning. Information consists of transformed data and facilitates decision making.

determination

The role of a key. In the context of a database table, the statement "A determines B" indicates that knowing the value of attribute A means that the value of attribute B can be looked up.

second normal form (2NF)

The second stage in the normalization process, in which a relation is in 1NF and there are no partial dependencies (dependencies in only part of the primary key).

data manipulation language (DML)

The set of commands that allows an end user to manipulate the data in the database. The commands include SELECT, INSERT, UPDATE, DELETE, COMMIT, and ROLLBACK.

class diagram notation

The set of symbols used in the creation of class diagrams.

external schema

The specific representation of an external view; the end user's view of the data environment.

online transaction processing (OLTP)

The systems that support a company's day-to-day operations. Databases that support OLTP are known as OLTP databases, transactional databases, or operational databases.

base table

The table on which a view is based.

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.

synonym

The use of different names to identify the same object, such as an entity, an attribute, or a relationship; synonyms should generally be avoided. See also homonym.

pessimistic locking

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

homonym

The use of the same name to label different attributes. Homonyms generally should be avoided. Some relational software automatically checks for homonyms and either alerts the user to their existence or automatically makes the appropriate adjustments. See also synonym.

physical data format

The way a computer "sees" (stores) data.

logical data format

The way a person views data.

structured data

Unstructured data that have been formatted to facilitate storage, use, and information generation.

predicate logic

Used extensively in mathematics to provide a framework in which an assertion (statement of fact) can be verified as either true or false.

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.

functional dependence

Within a relation R, an attribute B is functionally dependent on an attribute A if and only if a given value of attribute A determines exactly one value of attribute B. The relationship "B is dependent on A" is equivalent to "A determines B," and is written as AB.

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.