Transactional vs. Analytical Databases

20 question-and-answer flashcards covering key ideas, definitions, and distinctions between OLTP transactional databases and OLAP analytical databases.

What is the primary purpose of a transactional (OLTP) database?

To handle real-time, day-to-day operational transactions such as sales, banking, and inventory updates.

Which four ACID properties safeguard data integrity in transactional databases?

Atomicity, Consistency, Isolation, Durability.

Why do transactional databases use locks, concurrency control, and isolation levels?

To prevent conflicting updates and ensure multiple users can safely access and modify data at the same time.

What database structure (tables, rows, columns) do most transactional systems rely on?

A relational schema.

Which workload type—OLTP or OLAP—is characterized by frequent, short-duration transactions?

OLTP (Online Transaction Processing).

What happens during a ‘rollback’ in transaction management?

All changes made in a transaction are undone, restoring the database to its previous consistent state.

What is the main goal of an analytical (OLAP) database?

To store large volumes of historical data and provide fast, complex query responses for business intelligence and decision support.

How is data physically organized in most analytical databases to speed up read-heavy queries?

Using columnar data storage, where each column is stored contiguously.

Why is in-memory processing valuable for analytical databases?

It keeps data in RAM, dramatically reducing latency and speeding up query execution.

What does parallel processing enable in an analytical database?

The simultaneous execution of many operations across multiple CPU cores, improving performance on large datasets.

What is a data warehouse in the context of analytical databases?

A back-end repository optimized for storing and organizing data used for analysis and reporting, not for day-to-day transactions.

What role does OLAP software play relative to a data warehouse?

It provides front-end tools for slicing, dicing, and analyzing warehouse data from multiple perspectives.

Give two data-reduction techniques commonly used by analytical databases.

Data deduplication and data compression (other valid answers: data pruning, indexing).

Why are UPDATE, INSERT, and DELETE statements uncommon in a data warehouse?

Because the data has already been validated during ETL from transactional sources, so it is mostly read-only for analysis.

State one key difference between response-time goals of OLTP and OLAP systems.

OLTP focuses on sub-second responses for individual transactions; OLAP focuses on fast execution of complex, long-running analytical queries.

Which database type—transactional or analytical—is best suited for trend analysis and forecasting?

Analytical databases.

What is meant by the ‘front end’ of a database system?

The user interface or application layer through which users interact with data without touching the underlying DBMS directly.

What does the ‘back end’ of a database system include?

The DBMS software and physical storage devices that manage and store the data, hidden from end-user access.

Name one industry that commonly relies on transactional databases for mission-critical operations.

Banking (other acceptable answers: e-commerce, airline reservations, healthcare, inventory management).

How do analytical databases help organizations achieve competitive advantage?

By uncovering trends, patterns, and correlations that inform better strategic decisions and optimize operations.