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
