Database Indexing Concepts Quiz

A structured collection of data managed by a database management system (DBMS) primarily used for transactional operations like retrieving, updating, and managing current data.

A system for integrating and storing large amounts of structured data from multiple sources, typically used for analytics and reporting.

A storage repository that holds vast amounts of raw, unstructured, semi-structured, and structured data in its original format, enabling flexibility for analytics and machine learning.

The process of organizing data in a database to reduce redundancy and improve data integrity.

The process of combining tables that have been normalized and potentially adding redundancy to improve query performance and simplify data retrieval by reducing the number of complex joins needed.

The process of extracting data from a source, transforming it into a desired format, and loading it into a target system, typically a data warehouse.

A database where data is structured before being stored.

A database where structuring data happens when it's being retrieved.

A database architecture where a single server (the master) handles write operations, and other servers (slaves) replicate the data for read operations.

A database architecture where all servers can handle both read and write operations, with changes synchronized across all nodes.

A database architecture where all servers are equal, with no designated master. Writes and reads are distributed across all servers using quorum-based mechanisms.

A database approach where data is guaranteed to be consistent across all nodes immediately after a write.

A database approach where data is eventually guaranteed to be consistent across all nodes, but not immediately.

The process of automatically switching to a backup server if the primary server fails.

Distributing incoming traffic across multiple servers to maximize efficiency and prevent overload of a single server.

Deploying replica databases in different geographic locations to ensure service continuity in case of a regional outage.

Guarantees that a database remains in a valid state before and after a transaction, even if the transaction fails.

Ensures that all replicas of the data will converge to the same state over time, but not immediately.

Guarantees that all nodes in a distributed database reflect the most recent data immediately after a write operation, ensuring that all read operations retrieve the latest data.

A database feature that allows users to customize the level ofconsistency for read and write operations based on the application's needs.

Ensures data is copied and maintained across multiple servers or nodes for reliability, availability, and performance.

Minimizes downtime by having backups ready to take over if the original server fails.

A system with high availability for customers, even if some data might be temporarily inconsistent.

A system with strong consistency, even if there are some limitations on availability under certain conditions.

Indexes that store summary information about a range of rows, like minimum and maximum values, to speed up queries over large data ranges.

Indexes that improve query performance by providing a sorted order for data access, allowing for efficient searching and ranging.

Indexes that are designed to store complex data structures like JSONs and support efficient querying with complex attributes.

Indexes that are optimized for specific functions or subsets of data. They enhance query performance by targeting only relevant data.

A technique that uses data from indexes to reduce the number of rows needed to be scanned during query evaluation.

The process of combining tables that have been normalized, possibly adding redundancy, to improve query performance and simplify data retrieval. Instead of using joins to access related data, it stores the data together in a single table.

Denormalization enhances read performance by reducing the need for joins, since related data is stored together, accelerating data retrieval for read-intensive applications.

Denormalization simplifies database queries by consolidating related data into a single table or document. It reduces the number of relationships to manage, making the structure more streamlined.

It enables faster query execution by eliminating the need to access multiple tables with complex relationships. This is especially beneficial for large datasets as it reduces the amount of data that needs to be processed.

By aggregating and storing relevant information together, denormalization simplifies the process of generating reports and performing analytics without complex data transformations.

In partitioned databases, denormalization embeds or duplicates related data within the same partition, eliminating expensive and slow join operations across partitions.

Denormalization improves query performance by reducing the need for multiple partition accesses, as frequently accessed data is stored together. This streamlines the retrieval process.

Denormalization can help optimize partitioning strategies by grouping related data into the same partition based on the partitioning key. This ensures that queries only need to access a single partition.

Denormalization simplifies the management of high write volumes by minimizing dependencies between partitions. Writes can be efficiently applied directly to a single denormalised record.

Potential data quality issues in the source system can lead to inaccuracies or incomplete data in the denormalized database.

Different schemas, data formats, or structures between source and destination systems require careful mapping and transformation during denormalization migration.

Migrating large volumes of data can cause downtime, impacting users' accessibility to the system. Minimizing disruption is crucial.

Errors during migration, such as interrupted processes or mismatched schemas, can lead to data loss or corruption.

The CAP theorem describes the trade-offs between consistency, availability, and partition tolerance in distributed systems.

All nodes in a distributed system view the same data at the same time, ensuring data integrity. However, this can lead to performance issues, as updates need to be synchronized across all nodes.

The system remains operational, responding to requests even during failures. Ensuring availability may allow outdated data to be served, potentially leading to inconsistencies.

The system continues operating even if communication between different parts of the system is interrupted. This is critical for distributed systems, but prioritizing it often involves trade-offs with consistency or availability.

These systems prioritize consistency and partition tolerance but may compromise availability. This is suitable for applications requiring accurate data, like financial systems.

These systems prioritize availability and partition tolerance but may allow inconsistent data. This is suitable for applications where speed is critical and stale data is acceptable, such as caching systems.

These systems prioritize consistency and availability, but cannot tolerate partitions. They are suitable for centralized systems, not distributed ones.

Dividing a large table into smaller pieces called partitions based on criteria like range, list, or hash values, allowing for efficient management of large datasets.

Improved performance by targeting specific partitions instead of scanning the entire table, simplified data management through partition-specific operations, enhanced loading and indexing through parallelization, and cost-effective storage by allocating resources based on data frequency.

Splits a table into separate tables containing specific columns, allowing for efficient access to different data subsets for different applications.

Distributes rows across multiple tables or nodes, ensuring each partition has the same schema and distributing data evenly across servers for high availability.

Divides data based on a range of values (e.g., dates or IDs), enabling efficient filtering and retrieval.

Categorizes data into distinct groups based on predefined values or criteria.

Uses a hash function to distribute data evenly across partitions, ensuring balanced load and optimized resource utilization.

A database index type commonly used for equality and range queries, providing general-purpose efficiency and fast lookups.

An index optimized for equality searches, suitable for finding exact matches like customer IDs, providing quick lookups for specific values but not suitable for range queries.

An index designed to handle composite or non-atomic data like JSON, arrays, and full-text search, efficient for retrieving data based on complex criteria and allowing for faster and more robust queries.

An index used to search for data based on geographic locations or network addresses, commonly used for spatial data queries.

An index designed for large sequential datasets, such as timestamped data or logs, providing efficient performance for range queries and low storage requirements.

Horizontal partitioning (sharding) is suitable for global customer transaction data due to its ability to distribute data across servers based on customer regions, improving scalability and reducing query loads.

Combining horizontal partitioning (sharding) with range partitioning to further subdivide data within each shard by time ranges (e.g., monthly or yearly) enhances query performance and simplifies archiving.

A financial system with global customers would benefit from sharding to distribute data across servers based on customer regions, reducing query loads and enhancing scalability. Further optimization can be achieved by using range partitioning to subdivide data within each shard based on time ranges for efficient historical data management and simplified archiving.