Database Transactions and ACID Properties

Study Notes

Transaction Properties - ACID

Atomicity: Guarantees all transactions complete successfully or none at all; unsuccessful transactions are aborted, and changes are rolled back.
Consistency: Ensures all successful transaction changes are accurately reflected in the database.
Isolation: Transactions operate independently, preventing changes in one transaction from affecting others until committed.
Durability: Committed changes persist even after system failures.

SQL Aggregate Functions

Count of Functions: Seven aggregate functions available in SQL.
AVG(): Calculates the average of specified column values.
COUNT(): Returns the total number of rows in a table.
MAX(): Identifies the largest value from records.
MIN(): Identifies the smallest value from records.
SUM(): Computes the total sum of specified column values.
FIRST(): Retrieves the first value in a specified column.
LAST(): Retrieves the last value in a specified column.

Scalar Functions in SQL

Definition: Return a single value based on input values.
UCASE(): Converts text to uppercase.
LCASE(): Converts text to lowercase.
MID(): Extracts a substring from a text field.
FORMAT(): Specifies a display format for data.
LEN(): Returns the length of a text field.
ROUND(): Rounds a numeric value to a specified number of decimal places.

SQL Triggers

Definition: Stored procedures that respond to specific database actions (INSERT, UPDATE, DELETE).
Components: Consist of actions and events; certain actions trigger a corresponding event.
Syntax: CREATE TRIGGER name {BEFORE|AFTER} (event [OR..]} ON table_name [FOR [EACH] {ROW|STATEMENT}] EXECUTE PROCEDURE functionname {arguments}

Views in SQL

Definition: Virtual tables based on fields from one or more tables.
Syntax: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

Updating Views

Method: Use CREATE OR REPLACE VIEW to update an existing view.
Syntax: CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

SQL Privileges

Commands: Managed through GRANT and REVOKE commands to control user access to database objects.
GRANT Command: Provides specific database access rights to users.
Syntax: GRANT privilege_name ON object_name TO {user_name|PUBLIC|role_name} [WITH GRANT OPTION]
REVOKE Command: Denies or removes access rights to users.
Syntax: REVOKE privilege_name ON object_name FROM {user_name|PUBLIC|role_name}

Types of Privileges

System Privileges: Allow users to perform actions on specific types of objects (e.g., administrative tasks, creating or altering tables/views).
Object Privileges: Allow users to perform actions on specific database objects (e.g., SELECT, INSERT, UPDATE, DELETE).

SQL Injection

Definition: A security vulnerability where malicious SQL code is injected into an input field, allowing attackers to execute unauthorized database operations.
Purpose: Typically used against data-driven applications to access sensitive data or perform administrative tasks.

Description

This quiz covers the fundamental properties of database transactions, known as ACID properties. It focuses on concepts such as Atomicity, Consistency, Isolation, and Durability, ensuring a solid understanding of transaction management in databases. Test your knowledge of these critical aspects of transaction processing.