CIS 405 chapter 7, 8, 9, and 13

periodicity

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

embedded SQL

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

Systems Development Life Cycle (SDLC)

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

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.

cross join

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

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.

MAX

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

NOT

A SQL logical operator that negates a given predicate.

alias

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

HAVING

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

star schema

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

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

Roll up

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

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.

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.

implicit cursor

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

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.

multidimensional database management system (MDBMS)

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

database role

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

data mart

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

clustered table

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

host language

Any language that contains embedded SQL statements

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.

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

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.

online analytical processing (OLAP)

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

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.

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

BETWEEN

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

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

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

union-compatible

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

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.

metrics

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

dimensions

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

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.

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 __ __ is the most commonly used type of join

differential backup

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

Schema

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

stored function

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

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.

trigger

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

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.

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 removed 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 __ statement is used to retrieve data from tables

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 backup

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

persistent stored module (PSM)

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

Boolean algebra

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

full backup (database dump)

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

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.

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.

materialized view

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

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.

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.

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.

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

inner query

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

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.

batch update routine

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

cursor

A special construct used in procedural SQL to hold the data rows returned by a SQL query. A __ may be considered a reserved area of memory in which query output is stored, like an array holding columns and rows. __ 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. __ __is used to translate the conceptual design into the internal model for a selected database management system.

physical design

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

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

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 __ __ used in SQL are the _ and % symbols.

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 __ is composed of hardware, the DBMS and other software, database(s), people, and procedures.

virtualization

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

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 __ __ 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 call able object that can be invoked by name.

snowflake schema

A type of star schema in which dimension tables can have their own dimension tables. The __ __ 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.

decision support system(DSS)

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

Dynamic SQL

An environment in which the SQL statement is not known in advance, but instead is generated at runtime.In a __ __ 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

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. __ 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. __ includes retrieving data from original data sources, manipulating the data into an appropriate form, and storing the data in the data warehouse.

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.

governance

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

Cube cache

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

sparsity

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

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.

portal

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

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.

slice and dice

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

Data cube

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

scope

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

systems development

The process of creating an information system.

replication

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

database development

The process of database design and implementation

partitioning

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

systems analysis

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

cohesivity

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

Base table

The table on which a view is based.

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.