Database Management DBAS32100

Transaction A consistently sees the same balance, even if Transaction B makes changes during its execution.

A problem where a transaction sees different results when reading the same data multiple times.

Ensures that transactions appear as if they ran one after the other, preventing any interference.

A transaction reads data that has not yet been committed

A transaction re-reads a range of data that has change in number, but does not reflect on the first read.

Ensures a transaction sees the same data throughout its duration, preventing non-repeatable reads, but phantom reads are still possible.

The highest isolation level that ensures transactions execute sequentially. This prevents all types of anomalies (dirty reads, non-repeatable reads, and phantom reads).

Transaction reads data before the transaction modifying that data has been committed (making it non-atomic); incorrect, temporary data may lead to faulty conclusions.

Transaction reads committed data; no temporary, incomplete data is visible.

A mechanism to control how data is accessed in concurrent transactions, hence reducing errors.

A transaction is a group of database operations (like insertions, updates, deletions) that are treated as a single, indivisible unit. It's either completed entirely, or none of its operations take effect.

Atomic means that a transaction is indivisible. It's either fully applied to the database or completely discarded, preventing partial updates.

COMMIT is a command that makes all changes within a transaction permanent in the database.

ROLLBACK is a command that undoes all changes made within a transaction, returning the database to the state before the transaction began.

ROLLBACK essentially reverses the effects of each operation in a transaction, bringing the database back to its previous state—either the last committed point or the start of the session.

SAVE POINT is a marker within a transaction that allows you to rollback to a specific point within the ongoing transaction. This is useful for partial rollbacks.

The order in which you insert data into a database doesn't necessarily guarantee the order you'll retrieve it. You might need to use 'ORDER BY' to retrieve data in a specific order.

No, updates are not permanently saved to the database until a COMMIT command is issued. Until then, they can be undone with a ROLLBACK.

A setting that controls how transactions interact with each other, preventing anomalies like dirty reads, non-repeatable reads, and phantom reads.

They ensure data consistency and integrity by preventing conflicting data accesses, especially in multi-user database systems.

If Transaction A rolls back, the changes it made to the database are undone, and the data reverts to its previous state. Any transaction that read the uncommitted data from Transaction A will have inaccurate information.

A second read within a transaction finds different data because another transaction changed the data, even if the first transaction hasn't committed yet.

The transaction sees an inconsistent view of the data. Changes by another transaction might not be reflected in the first transaction's result set.

Isolation levels control how transactions access data in a multi-user environment, minimizing data inconsistencies and ensuring accuracy.

Transactions may rely on incomplete or incorrect data leading to errors or inconsistent outcomes.

By controlling how data is accessed, isolation levels prevent inconsistent results due to concurrent transactions.

A set of properties (Atomicity, Consistency, Isolation, Durability) that ensure reliable database transactions. These properties guarantee data integrity and consistency even when multiple transactions occur simultaneously.

They help maintain data integrity and consistency in multi-user environments, ensuring reliable and accurate data processing.

Different isolation levels offer varying levels of protection from data inconsistency issues, ranging from Read Uncommitted to Serializable.

A transaction is considered atomic if it either completes entirely (all components succeed) or fails completely (one or more components fail). It cannot partially succeed or fail.

A transaction must bring the database to a valid state. It shouldn't introduce incorrect data or violate the database's rules and constraints.

Transactions happen independently of each other. One transaction shouldn't interfere with another, even if they access the same data.

Once a transaction is successfully completed, its changes are permanently recorded in the database and survive any system crashes or failures.

A database design principle that aims to eliminate data redundancy and update anomalies by ensuring that all non-key attributes (attributes not part of the primary key) depend only on the entire primary key, not just part of it.

Data Definition Language (DDL) statements, such as CREATE TABLE or ALTER TABLE, are inherently atomic. They either fully complete or entirely rollback, leaving the database in a consistent state.

ACID properties ensure data integrity and consistency in a multi-user, concurrent environment. They prevent data corruption, ensure accurate data representation, and guarantee a predictable and reliable database system.

These functions perform calculations on a set of rows in a table, returning a single value. They help answer questions like 'How many students have a GPA above 4?' or 'What is the maximum grade in the class?'

This function creates calculations for each unique value in a column or group of columns. It's used for questions like 'What is the average student GPA for each program?' or 'How many students are in each class?'

This creates groups based on all possible combinations of values from multiple columns. Each combination forms a unique group, leading to more detailed calculations

This clause selects the rows that will be included in your calculations before grouping. It filters rows based on certain criteria.

This clause filters the results after grouping. It's used for selecting groups based on a condition calculated from the grouped data.

This operation generates cross-tabulation values for all possible combinations of your selected columns, including null rows.

CUBE operation returns a comprehensive overview of all possible combinations of values in selected columns, including rows with null values to represent absence of data.

CUBE helps you analyze data across different dimensions, revealing patterns, trends, insights, and relationships within your data.

A SQL function that groups data based on multiple columns and produces subtotal values for each group hierarchy. It's helpful for summarizing data with nested relationships, like sales by day, month, and year.

A SQL function that allows you to create multiple groups within a single query. It allows grouping by different combinations of columns, creating a flexible report.

The ROLLUP function allows you to easily create subtotals for multiple levels of grouping within your data, similar to using a nested GROUP BY clause.

The GROUP SETS function enables you to create a report with multiple group combinations for different analyses. You can group by specific sets of columns, providing more flexibility.

ROLLUP creates subtotals based on hierarchical grouping, while GROUP SETS allows you to define specific combinations of columns for grouping, providing more flexibility in data reporting.

A SQL function that returns an integer representing the group set based on null values in a GROUP BY clause. Helps to identify different group levels by combining null and non-null columns.

Cubes in SQL are a way to represent multi-dimensional data where different combinations of attributes like Class, Color, and Manufacturing Year can be grouped together. It helps analyze data across multiple dimensions.

Cubes can be created by grouping data based on different combinations of attributes. You can group by single attributes, multiple attributes, or the entire data set. This provides different levels of granularity for data analysis.

Cubes offer different levels of data aggregation, allowing you to analyze data at various granularities. You can find patterns, trends, and insights by grouping data based on different attributes or combinations.

You can sort the results of cube queries using the GROUPING_ID function to order the output by group set. This brings all records belonging to the same group together in the result set.

You can group data by individual attributes (like 'class') or combinations of attributes (like 'class AND color'). Different groupings provide different levels of information and insights.

Set operations like UNION, INTERSECT, and EXCEPT can be used to combine, overlap, or remove data from different cubes, allowing for flexible manipulation and analysis of data.