Database Test 3

internal deployment of a system to the members of the development team for initial testing of its functionalities

Alpha release

Defines how transactions acquire and relinquish locks. Guarantees serializability but doesn't prevent deadlocks.

2 phase locking

each record summarizes mulitple facts

Aggregated fact tables

the information collected and used in support of analytical tasks

Analytical Information

all operations of a transaction must be completed

Atomicity

Deployment of a system to a selected group of users to test the usability of the system

Beta release

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.

Binary lock

Temporary storage areas in primary memory.

Buffers (Trans Recovery)

operations in which DBMS writes all its updated buffers to disk

Checkpoints

Coordination of the simultaneous transactions execution in a mulitprocessing database system. Objective is to ensure serializability of transactions in a multiuser environment.

Concurrency Control

Restricts output of query to only rows matching the criteria. Normally expressed withing WHERE and HAVING clauses.

Conditional Expressions

Permanence of a database's consistent state.

Consistency

One in which all data integrity constraints are satisfied

Consistent state

Intended for use by higher level decision makers within an organization.Contains an organized easy-to-read display of a number of critically important queries describing the performance of the organization.

Executive dashboard

The DBMS environment must be properly configured to respond to clients requests in the fastest way possible, while making optimum use of existing resources.

DBMS performance tuning

-Automatically expanded in predefined increments known as extends. -Grouped in file groups or table spaces.

Data Files

A data store based on the same principles as a data warehouse but with a more limited scope

Data Mart

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

Data cache

the detection and correction of low quality data

Data cleansing

-Source Systems (operational databases) -Extraction-Transformation-Load (ETL) Infrastructure -Data Warehouse -Front-End (BI) applications

Data warehouse components

retrieves selected analytically useful data from the operational data sources.

Data warehouses

Restores database to previous consistent state.

Database recovering management

Occurs when two transactions wait for each other to unlock data. Possible if one of the transactions wants to obtain an exclusive lock on a data item.

Dead lock

each record refers to a single fact

Detailed fact table

Only transaction log is updated

Differed write technique

contain descriptions about the subjects of the business

Dimension tables

designs subject oriented analytical databases, such as datawarehouses or data marts.

Dimensional Modeling

makes the granularity of the data in the query result finer

Drill down

Set of attributes within a dimension where an attribute is related to one or more attributes at a lower level but only related to one item at a higher level

Drill hierarchy

makes the granularity of the data in the query result coarser

Drill up

Once transactions are committed, they cannot be undone

Durability

The fact that the datwarehouse provides an organization wide view of analytically useful info it contains.

Enterprise-wide

The infrastructure that facilitates the retrieval of data from operational databases into datawarehouses. Extraction, Transformation, and Loading

ETL

Access is specifically reserved for the transaction that locked object. Must be used when potential for conflict exists.

Exclusive lock

DBMS executes the query using chosen execution plan.

Execution (Query Processing)

contain measures related to the subject of analysis and the foreign keys.

Fact tables

DBMS fetches the data and sends it back to the client.

Fetching (Query Processing)

Used to provide access to data warehouses for users who are engaging in indirect use

Front End Application

Transaction acquires all required locks without unlocking any data

Growing phase

Larger time horizon in the data warehouse than in the operational databases.

Historical

Has its own source systems and ETL infrastructure.

Independent Data mart

Facilitate searching, sorting, and using aggregate functions as well as join operations. Crucial in speeding up data access.

Indexes

data warehouse integrates the analytically useful data from the various operational databases.

Integrated

Data used during the transaction cannot be used by second transaction until the first is completed.

Isolation

each row represents a line item of a particular transaction

Line item detailed fact table

loading the extracted, transformed, and quality assured data into the target data warehouse

Load

guarantees exclusive use of a data item to a current transaction. Required to prevent another data from reading inconsistent data.

Lock

indicates the level of lock use

Lock Granularity

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

Lost update

ensures that time stamp values always increase

Monotonicity

When multiple subjects of analysis can share the same dimensions, a dimensional model contains more than one fact table

Multiple facts in a dimensional model

Envisions a data warehouse as an integrated analytical database modeled by using the traditional database modeling techniques of ER modeling and relational modeling, resulting in a normalized relational database schema

Normalized Data warehouse

querying and presenting data from data warehouses and/or data marts for analytical purposes

Online Analytical Processing (OLAP)

updating (i.e. inserting, modifying and deleting), querying and presenting data from databases for operational purposes

Online Transaction Processing (OLTP)

the information collected and used in support of day to day operational needs in businesses and other organizations

Operational Information

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

Optimizer hints

DMBS parses the query and chooses the most efficient access/execution plan.

Parsing (Query Processing)

reorganizes the values displayed in the original query result by moving values of a dimension column from one axis to another.

Pivot

The actual deployment of a functioning system

Production release

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

SQL cache

Generate SQL query that returns the correct answer in the least amount of time. Client Side

SQL performance tuning

Purpose is to establish order of operations within which concurrent transactions are executed. Interleaves execution of database operations: ensures serializability and isolation.

Scheduler

Ensures that the schedule for the concurrent execution of several transactions yields consistent results

Serializability

Concurrent transactions are granted read access on basis of a common lock

Shared lock

Transaction releases all locks and cannot obtain any new lock

Shrinking phase

adds, replaces, or eliminates specified dimension attributes from the already displayed results.

Sice and dice

Dimension that contains attributes whose values can change.

Slowly changing dimension

Changes the value in the dimension's record. used most often when a change in a dimension is the result of an error.

Slowly changing dimension type 1

Creates a new additional dimension record using a new value for the surrogate key every time a value in a dimension record changes.

Slowly changing dimension type 2

Involves creating a previous and current column in the dimension table for each column where changes are anticipated.

Slowly changing dimension type 3

A star schema that contains the dimensions that are normalized. Not used in dimensional modeling.

Snowflake Model

The result of dimensional modeling is a dimensional schema containing facts and dimensions

Star Schema

The datawarehouse is a database containing analytically useful info. Any database is ______ with is structure represented in its metadata.

Structured Repository

The fundamental difference in the purpose of an operational database system and a datawarehouse.

Subject-oriented

In a star schema, all dimension tables are given a simple non-composite system-generated key. Typically auto-incremement values.

Surrogate key

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

Time Stamping

Data warehouse contains slices of data from different periods of time across its time horizon.

Time variant

each row represents a particular transaction

Transaction level detailed fact table

Stores a record of transaction.

Transaction log

COMMIT statement is reached ROLLBACK statement is reached End of program is reached Program is abnormally terminated

Transaction sequence must continue until

transforming the structure of extracted data in order to fit the structure of the target data warehouse model

Transformation

Two transactions are executed concurrently First transaction rolled back after second already accessed uncommitted data

Uncommitted data

Ensures that no equal time stamp values can exist

Uniqueness

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.

Wait/die

In concurrency control,ensures transaction logs are written before any data is actually updated.

Write-Ahead-Log Protocol

A query optimizer technique that uses an algorithm based on statistics about the objects being accessed. Adds up processing cost, I/O costs, resources costs to derive the total cost.

cost-based optimizer

number of different values a column could possibly have

data sparsity

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.

database performance tuning

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

database request

Data warehouse as a collection of dimensionally modeled intertwined data marts (i.e. constellation of dimensional models) that integrates analytically useful information from the operational data sources

dimensionally modeled data warehouse

the retrieval of analytically useful data from the operational data sources that will eventually be loaded into the data warehouse

extraction

1st transaction accessed data, 2nd transaction alters data, 1st transaction accesses the data again.

inconsistent retrievals

A low-level data access operation that reads or writes data to and from computer devices.

input/output request

Responsible for assigning and policing the locks used by transactions

lock manager

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

optimistic approach

ensure physical disk failure will not impair ability to recover

redundant transaction logs

Uses preset rules and points to determine the best approach to execute a query

rule-based optimizer

Interleaved execution of transactions yields the same results as the serial execution of the transactions

serializable schedule

operational databases that provide analytically useful information for the data warehouse's subjects of analysis

source systems

Logical unit of work that must be either entirely completed or aborted.

transaction

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.

wound/wait

Database is immediately updated by transaction operations during transaction's execution

write-through technique