Module 6 FLASHIES

ANALYZE (PostgreSQL)

A PostgreSQL command used to collect and store statistics about the contents of tables in the database, which the query planner uses to determine the most efficient execution plans for queries.

B+ Tree

A dynamic, tree-structured index that adjusts gracefully under inserts and deletes, making it adaptable and recommended in most situations, used by default in PostgreSQL.

Business Analytics (BA)

The process of using historical data combined with software technologies to make predictions and support business decisions.

Business Intelligence (BI)

A combination of strategies using data, software, and company information to provide business owners with an overview of operations for decision-making.

Commit Protocols

Mechanisms (e.g., Two-Phase Commit) used in distributed transactions to ensure atomicity across multiple sites, meaning a transaction is either committed at all sites or aborted at all sites.

Cost Analysis (Database)

Involves calculating an estimated computational cost for data manipulation and extraction operations within a database, considering various factors like indexing methods and query types.

CUBE (SQL)

An SQL extension to the GROUP BY statement that generates a result set containing all possible combinations of groupings for the specified columns, providing a comprehensive aggregate report.

Data Cube

A multidimensional array used in OLAP to represent data along several dimensions, allowing for complex analytical queries that go beyond two-dimensional tables.

Data Mining

The semi-automatic process of analyzing large databases to find useful patterns, support business decisions, and make predictions, often linked to machine learning.

Data Warehousing

A system that integrates data from various sources into a single, consistent store, specifically structured for query and analysis to support business intelligence and analytics.

DBMS Evaluation

The process of selecting a Database Management System that best meets present and future business requirements and constraints, considering factors like cost, scalability, and ease of use.

Dicing

An OLAP operation that selects a subset of the data cube by choosing a single value for two or more of its dimensions, creating a smaller, more specific cube.

Distributed Database

A database system where data is stored across multiple interconnected nodes, cooperating to perform tasks, offering advantages like redundancy and scalability.

Distributed Transactions

Transactions that access or modify data located at several different sites within a distributed database system, requiring coordination for atomicity.

Drill Down

An OLAP operation that moves from a more general view of the data to a more detailed level, providing finer granularity.

EXPLAIN (PostgreSQL)

A PostgreSQL command that shows the estimated execution plan and cost of a query without actually executing it, aiding in query optimization.

Fragmentation (Data)

The process of breaking down a database or relation into smaller logical units (fragments) that can be distributed and stored across different nodes in a distributed database.

GROUPING SETS (SQL)

An SQL extension to the GROUP BY clause that allows specifying multiple independent grouping sets in a single query, enabling aggregations for different combinations of fields.

Hash Index

An index that uses a hash function to map search keys directly to data locations, efficient for equality comparisons but not range queries.

Heterogeneous Distributed Database

A type of distributed database where different sites may use different schemas and software, posing challenges for query and transaction processing.

Homogeneous Distributed Database

A type of distributed database where all sites use the same DBMS software and are aware of each other, appearing as a single system to the user.

Horizontal Fragmentation (Sharding)

A type of data fragmentation where records (rows) from a table are spread across different locations based on a shard key, while the schema is copied.

Hot (Online) Backup

A physical database backup taken while users can still modify the database, crucial for applications requiring high availability.

Index

A data structure that maps search keys to sets of rows in a database table, speeding up searches and updates but incurring storage overhead.

ISAM (Indexed Sequential Access Method)

A static tree-structured index that does not adjust dynamically to inserts and deletes.

JDBC (Java Database Connectivity)

A Java API that provides a standard way for Java applications to connect to and interact with relational databases.

Logical Backup

A database backup that contains copies of the database schema and data exported in a SQL script file, restored by running the commands in the script.

Master-Master Replication

A replication topology in distributed databases where all nodes can read and write data, and data written in one node replicates to all others.

Master-Slave Replication

A replication topology where data is written to a master node and then copied/replicated to one or more slave nodes, which typically allow only read operations.

MOLAP (Multidimensional OLAP)

OLAP systems that use multidimensional arrays in memory to store data cubes, providing fast query performance.

OLAP (On-line Analytical Processing)

Technology used in data warehousing that allows users to analyze information from multiple database systems, focused on complex analytical queries and decision support.

OLTP (On-line Transaction Processing)

Traditional transaction-oriented database systems focused on managing a large number of short, real-time transactions for data storage and retrieval.

On-Premises DBMS

A database system deployed and managed in-house by an organization, offering total control over hardware and software but with higher initial costs.

Outsourced DBMS

A database system managed by a third-party vendor (e.g., cloud provider), reducing the need for in-house equipment and staff but potentially leading to less control.

Physical Backup

A database backup consisting of copies of the actual data files, typically faster to restore but less portable due to architecture dependency.

Pivot (Rotate)

An OLAP operation that changes the dimensional orientation of the cube, rotating the data axes to view the data from different perspectives and group data by different dimensions.

pg_dump

A PostgreSQL utility for taking logical backups of a single database or database object.

pg_dumpall

A PostgreSQL utility for backing up an entire cluster, including global objects like users and tablespaces.

Query Optimization

The process within a DBMS where the optimizer uses statistics and other information to select the most efficient execution plan for an SQL query.

Reindex (PostgreSQL)

A PostgreSQL maintenance command used to rebuild an index, replacing the old copy.

Replication (Data)

The process of storing a relation or fragment of a relation redundantly in two or more sites to synchronize data between nodes in a distributed database.

ROLAP (Relational OLAP)

OLAP facilities integrated into relational systems, where data is stored in a relational database.

Roll Up

An OLAP operation that moves from a more detailed view of the data to a more general view level, often by aggregating along a dimension.

ROLLUP (SQL)

An SQL extension to the GROUP BY statement that creates hierarchical rollups based on the order of specified fields, generating subtotals and a grand total.

SeqScan (Sequential Scan)

A type of table scan in PostgreSQL that reads the entire table sequentially, typically used when most of the table is needed or no suitable index is available.

Slicing

An OLAP operation that selects a subset of the data cube by choosing a single value for one of its dimensions, creating a smaller cube with one less dimension.

Table Scan

A method used by the database management system to retrieve rows from a table, including sequential scans, index scans, and bitmap scans.

VACUUM (PostgreSQL)

"A PostgreSQL maintenance command that reclaims storage occupied by ""dead tuples"" (deleted or updated rows), which are not physically removed immediately."

Vertical Fragmentation

A type of data fragmentation where a relation is decomposed vertically by attributes (columns), with different columns of a table stored at different locations.

Vertically Scaling

Growing a database by running it on bigger and faster servers with more space, capacity, and performance (e.g., more CPU cores, RAM).