Comprehensive Guide to Database Management, Big Data, and Data Warehousing

What is Concurrency Control?

Coordination of simultaneous execution of transactions in a multiuser database system to ensure serializability and data integrity.

What are the three main problems associated with concurrency control?

Lost update, uncommitted data, and inconsistent retrievals.

What is a shared lock?

A lock issued when a transaction must read data and no exclusive locks are held on that data.

What is an exclusive lock?

A lock issued when a transaction must update a data item and no locks are currently held on that data item.

What is a transaction?

A logical unit of work that must be entirely completed or aborted.

What are the five properties of transactions?

Atomicity, consistency, isolation, durability, and serializability.

What is Serializability?

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

What does the Time Stamping method entail?

Assigns a global, unique time stamp to each transaction to produce an explicit order of transactions.

What are the properties of the Time Stamping method?

Uniqueness and monotonicity.

What are the disadvantages of Concurrency Control with Time Stamping?

Increases memory needs, processing overhead, and demands system resources.

What is Two-Phase Locking?

A method defining how transactions acquire and relinquish locks, consisting of a growing phase and a shrinking phase.

What are the Two-Phase Locking Governing rules?

No conflicting locks, no unlock before lock in the same transaction, and no data affected until all locks are obtained.

What is the Scheduler?

Establishes the order of operations in concurrent transactions to ensure serializability and isolation.

What is a transaction log?

Keeps track of all transactions in the database for recovery purposes.

What is Lock Granularity?

Refers to the size of the units that can be locked, including database-level, table-level, page-level, row-level, and field-level locks.

What are deadlocks?

Situations where two transactions wait indefinitely for each other to unlock data.

What are the techniques to control deadlocks?

Deadlock avoidance, prevention, and detection.

What are the techniques used in transaction recovery procedures?

Deferred-write technique and write-through technique.

What is Database Recovery?

Restores the database from a given state to a previously consistent state.

What is Concurrency Control with Optimistic Methods?

An approach based on the assumption that most database operations do not conflict, executed without restrictions until committed.

What is Concurrency Control with Locking Method?

Locking methods facilitate isolation of data items used in concurrently executing transactions.

What is a Lock in concurrency control?

A lock guarantees exclusive use of a data item to a current transaction.

What is Pessimistic Locking?

Pessimistic locking uses locks based on the assumption that conflict between transactions is likely.

What is a Lock Manager?

A lock manager is responsible for assigning and policing the locks used by the transactions.

What is a Distributed Database Management System (DDBMS)?

DDBMS governs storage and processing of logically related data over interconnected computer systems where both data and processing functions are distributed among several sites.

What are the transparency features of a DDBMS?

Make all the system's complexities hidden to the end user.

What is Centralized Data Allocation?

Means the entire database is stored at one site.

What is Partitioned Data Allocation?

means the database is divided into two or more disjoined fragments and stored at two or more sites.

What is Replicated Data Allocation?

Replicated data allocation means copies of one or more database fragments are stored at several sites.

What factors influence the decision for data replication?

Factors include database size, frequency of use, and cost.

What is Fully Replicated Database?

Stores multiple copies of each database fragment at multiple sites.

What is Partially Replicated Database?

A partially replicated database stores multiple copies of some database fragments at multiple sites.

What is Unreplicated Database?

An unreplicated database stores each database fragment at a single site.

What is the difference between Distributed Database and Distributed Processing?

Distributed processing shares the database's logical processing among two or more physically independent sites, while a distributed database stores logically related data over multiple sites.

What is Data Fragmentation?

Data fragmentation allows one to break a single object into two or more segments or fragments.

What is Horizontal Fragmentation?

Is the division of a relation into subsets (fragments) of tuples (rows).

What is Vertical Fragmentation?

Is the division of a relation into attribute (column) subsets.

What is Mixed Fragmentation?

Mixed fragmentation is a combination of horizontal and vertical strategies.

What is the CAP Theorem?

Stands for Consistency, Availability, and Partition tolerance, which describes the trade-off between consistency and availability in distributed systems.

What is the Two-Phase Commit Protocol (2PC)?

2PC guarantees that if a portion of a transaction cannot be committed, all changes made at other sites will be undone.

What is Multiple-Site Processing, Multiple-Site Data (MPMD)?

MPMD is a fully distributed database management system that supports multiple data processors and transaction processors at multiple sites.

What is Data Replication?

the storage of data copies at multiple sites served by a computer network.

What is Performance Transparency?

Performance transparency allows a DDBMS to perform as if it were a centralized database.

What is Failure Transparency?

Failure transparency ensures the system will operate in case of network failure.

What are the components a DDBMS must include?

Components include computer workstations, network hardware/software, communications media, transaction processors, and data processors.

What are the functions of a fully distributed DBMS?

Functions include receiving requests, validating, analyzing, decomposing requests, ensuring database consistency, and presenting data.

What are potential problems with centralized DBMS?

Problems include performance degradation, high costs, reliability issues, scalability problems, and organizational rigidity.

What does granularity describe?

Levels of aggregation in data presentation.

What are the basic components of a star schema?

Facts, dimensions, attributes, slice and dice, and attribute hierarchies.

How is a star schema used in a data warehouse?

It uses a large fact table linked to dimensional tables to store and describe transactional data.

What features must OLAP tools have for efficient decision support?

Access to various data sources, aggregated data, advanced navigation, rapid query response, mapping user requests, and support for large databases.

What is OLAP?

Online Analytical Processing that supports decision making, business modeling, and operations research.

What are the main characteristics of OLAP systems?

Multidimensional data analysis, advanced database support, and user-friendly interfaces.

What is the OLAP architecture designed for?

Ease of use while maintaining system flexibility.

What are common performance improvement techniques in a star schema?

Normalizing dimensional tables, maintaining multiple fact tables, denormalizing fact tables, and partitioning/replicating tables.

What is the difference between operational data and decision support data?

Operational data captures daily transactions, while decision support data provides tactical and strategic insights.

What is data analytics?

A subset of BI that uses mathematical and statistical techniques to extract knowledge from data.

What is a data warehouse?

An integrated, subject-oriented, time-variant, nonvolatile collection of data for decision support.

How does a data mart differ from a data warehouse?

A data mart is a smaller, single-subject subset of a data warehouse.

What is the ROLLUP extension in SQL?

Used with GROUP BY to generate aggregates by different dimensions.

What is the CUBE extension in SQL?

Used with GROUP BY to generate aggregates for all combinations of the listed columns.

What is Business Intelligence?

A set of tools and processes for capturing, integrating, storing, and analyzing data to support decision making.

What is Hadoop?

A Java-based framework for distributing and processing large data sets across clusters.

What is HDFS?

Hadoop Distributed File System, used for low-level distributed file processing.

What is MapReduce?

A programming model for processing large data sets in Hadoop.

What does NoSQL mean?

Non-relational database technologies designed to address Big Data challenges.

What is the difference between scaling up and scaling out?

Scaling up involves upgrading existing systems, while scaling out spreads workload across multiple servers.

What are the four major approaches of the NoSQL data model?

Key-value databases, Document databases, Column-oriented databases, Graph databases.

What are key-value databases?

They store data as a collection of key-value pairs organized as buckets.

What are document databases?

They store data in key-value pairs where the value components are encoded documents grouped into collections.

What are column-oriented databases?

They refer to column-centric storage and row-centric storage for organizing data.

What is graph databases?

They store data on relationship-rich data as a collection of nodes and edges.

What are the key assumptions of Hadoop Distributed File System (HDFS)?

High volume, Write-once read-many, Streaming access, Fault tolerance.

What does 'volume' refer to in Big Data?

The quantity of data to be stored.

What does 'velocity' refer to in Big Data?

The speed at which data is entered into the system and must be processed.

What does 'variety' refer to in Big Data?

Variations in the structure of data to be stored.

What is veracity in Big Data?

The trustworthiness of data.

What is value in the context of Big Data?

The degree to which data can be analyzed for meaningful insight.

What types of nodes does Hadoop use?

Data node, Name node, Client node.

What is the purpose of indexes in databases?

They help speed up data access and facilitate searching, sorting, and join operations.

What is the difference between SQL performance tuning and DBMS performance tuning?

SQL performance tuning focuses on client-side query efficiency, while DBMS performance tuning focuses on server-side configuration for optimal resource use.

What are the three phases in which the DBMS processes queries?

Parsing, Execution, Fetching.

What are query processing bottlenecks?

Delays introduced in the processing of an I/O operation that slow the system.

What are the steps to formulate a query?

Identify required columns, source tables, join methods, selection criteria, and output order.

What recommendations would you give for physical storage of databases?

Utilize I/O accelerators, use RAID, minimize disk contention, and partition tables based on usage.

What is a rule-based optimizer?

An optimizer that uses preset rules to determine the best approach to execute a query.

What is a cost-based optimizer?

An optimizer that uses algorithms based on statistics to determine the best execution plan.