Advanced SQL: Database Administration, Transaction Control, and Performance Tuning

Database Administration (DA) and the Role of the DBA

• Definition of Database Administration (DA): It is the comprehensive process of managing, monitoring, and maintaining a database system. The primary objectives are to ensure data security, integrity, availability, and optimal performance.

• Key DA Activities:     * User and Role Management: Managing authentication, authorization, and privileges.     * Transaction Management: Utilizing commands like COMMIT, ROLLBACK, and SAVEPOINT to control data changes.     * Concurrency Control: Implementing locks to manage data access by multiple users simultaneously.     * Backup and Recovery: Performing tasks to prevent data loss and restore information when necessary.

• The Database Administrator (DBA): The person responsible for these tasks is the DBA. This individual ensures the database operates reliably, securely, and efficiently under all conditions.

Transaction Control

• Definition of a Transaction: A transaction is a sequence of database operations that form one logical unit of work. It follows an "all-or-nothing" principle: either all operations succeed, or none are applied.

• Purpose of Transaction Control: It refers to the set of SQL commands used to manage transactions and ensure that data changes remain accurate, consistent, and recoverable. It allows a DBA to:     * Determine when changes become permanent.     * Undo changes if errors occur.     * Maintain data integrity in multi-user environments.

• Transaction Control Commands:     * COMMIT: Saves all changes permanently and ends the transaction.         * Syntax Example: sql UPDATE account SET balance = balance - 1000 WHERE acc_no = 101; COMMIT;                  * ROLLBACK: Cancels all changes made since the last COMMIT and restores the database to its previous state.         * Syntax Example: sql DELETE FROM student WHERE id = 10; ROLLBACK;                  * SAVEPOINT: Creates a checkpoint within a transaction, allowing for a partial rollback to a specific point rather than undoing the entire transaction.         * Syntax Example: sql SAVEPOINT sp1; UPDATE account SET balance = balance - 500; ROLLBACK TO sp1;             

Detailed Transaction Control Example

• Step $1$: Create Tablesql CREATE TABLE account ( acc_no INT, balance INT );     

• Step $2$: Insert Datasql INSERT INTO account VALUES (1, 5000); INSERT INTO account VALUES (2, 3000); COMMIT;     

• Step $3$: Use SAVEPOINTsql UPDATE account SET balance = balance – 1000 WHERE acc_no = 1; SAVEPOINT sp1;     

• Step $4$: Another Updatesql UPDATE account SET balance = balance + 1000 WHERE acc_no = 2;     

• Step $5$: ROLLBACK to SAVEPOINTsql ROLLBACK TO sp1;     

• Step $6$: COMMIT Final Changessql COMMIT; SELECT * FROM account;     

• Final Resulting State:     * acc_no $1$ | balance $4000$     * acc_no $2$ | balance $3000$

DCL Commands (Data Control Language)

• Definition: DCL commands control access to the database by granting or revoking permissions. They determine "who can do what," ensuring data security and authorized access.

• Context in Real Databases: Since many users work simultaneously and not everyone needs full access, DCL helps to:     * Protect sensitive data.     * Prevent unauthorized access.     * Maintain database security.

• Primary DCL Commands:     * GRANT: Used to provide permissions to users.         * General Syntax: GRANT privilege ON object TO user;         * Common Privileges: SELECT (read), INSERT (add), UPDATE (modify), DELETE (remove), and ALL (full permissions).         * Example: GRANT SELECT, INSERT ON student TO user1; or GRANT ALL ON student TO user1;     * REVOKE: Used to remove previously granted permissions.         * General Syntax: REVOKE privilege ON object FROM user;         * Example: REVOKE INSERT ON student FROM user1; or REVOKE ALL ON student FROM user1;

Types of Locks

Locks are used by the DBA to control concurrent access, ensuring consistency and integrity when multiple users access data simultaneously.

• Row Level Locks:     * Locks only a specific row in a table.     * Allows multiple users to access different rows in the same table at the same time.     * DBMS applies these automatically during modification.     * Example: UPDATE student SET marks = 90 WHERE rollno = 101; (only row $101$ is locked).

• Table Level Locks:     * Locks the entire table, preventing other transactions from reading or writing to it.     * Useful during bulk updates, maintenance, or structure changes.     * Example: LOCK TABLE student IN EXCLUSIVE MODE;

• Shared Lock (S-Lock):     * Allows multiple transactions to read the same data concurrently.     * Prevents any modification until all reading transactions are finished.     * By default, SELECT statements acquire a shared lock.

• Exclusive Lock (X-Lock):     * Grants a transaction sole access for both reading and modification (INSERT, UPDATE, DELETE).     * No other transaction can acquire any lock (shared or exclusive) on that resource while the X-lock is held.     * Maintains integrity by preventing conflicts like lost updates.

• Lock Comparison Table:

Deadlock

• Definition: A deadlock occurs when two or more transactions are waiting indefinitely for each other because each holds a lock on a resource the other needs.

• Development of Deadlock:     1. Transaction A locks Resource $1$ and waits for Resource $2$.     2. Transaction B locks Resource $2$ and waits for Resource $1$.     3. Neither can proceed, creating a cycle of dependency.

• Example Scenario:     * T1 locks Student Row A; T2 locks Student Row B.     * T1 waits for Row B; T2 waits for Row A.

• Handling Deadlocks: The DBMS detects deadlocks, automatically rolls back one of the involved transactions, and releases its locks to allow others to continue.

Synonyms in SQL

• Definition: A synonym is an alias or alternative name given to a database object (table, view, sequence, procedure, or function).

• Uses and Benefits:     * Simplify long or complex names.     * Hide schema or owner names.     * Enhance security.     * Improve code readability.     * Facilitate access to objects in other schemas.

• Creation Syntax:     * CREATE SYNONYM synonym_name FOR schema_name.object_name;

• Example:sql CREATE TABLE student (rollno NUMBER, name VARCHAR2(20)); CREATE SYNONYM stud FOR student; SELECT * FROM stud;     

SQL Sequences

• Definition: User-defined objects designed to generate a series of numeric values, typically used for primary keys and unique identifiers.

• Key Features:     * Automatic generation of unique values.     * Configurable for ascending or descending order.     * Reusable across multiple tables.     * Reduces overhead of manual unique value generation.

• Creation Syntax:sql CREATE SEQUENCE sequence_name START WITH initial_value INCREMENT BY increment_value MINVALUE minimum_value MAXVALUE maximum_value CYCLE|NOCYCLE;          * Initial_Value: The starting number.     * Increment_Value: Step value for each sequence generation.     * Cycle: Restarts from the beginning after reaching the MAXVALUE.     * Nocycle: Throws an exception if MAXVALUE is exceeded.

• Integration Example:sql CREATE SEQUENCE my_sequence START WITH 1 INCREMENT BY 1; INSERT INTO customers VALUES (my_sequence.NEXTVAL, 'Riya', 32, 'Ahmedabad', 2000); INSERT INTO customers VALUES (my_sequence.NEXTVAL, 'Siya', 25, 'Delhi', 1500);     

• Alter Sequence: Used to modify property values (except start value).     * Syntax: ALTER SEQUENCE sequence_name INCREMENT BY value MAXVALUE value MINVALUE value CYCLE | NOCYCLE;     * Example: ALTER SEQUENCE my_sequence INCREMENT BY 2;

Indexes

• Definition: A database object used to speed up data retrieval. It functions like a book index: instead of scanning the full table (full table scan), it jumps directly to the data.

• Types of Indexes:     * Unique Index: Ensures no duplicate values in the indexed column, maintaining integrity.         * Syntax: CREATE UNIQUE INDEX index_name ON table_name(column_name);         * Example: CREATE UNIQUE INDEX idx_rollno ON student(rollno);     * Composite Index: Created on more than one column. It is optimized for queries that filter using multiple columns together.         * Syntax: CREATE INDEX index_name ON table_name(column1, column2);         * Example: CREATE INDEX idx_name_city ON customer(name, city);

SQL Views

• Definition: A virtual table created from the result of a SELECT query. It does not store physical data itself but displays data from underlying tables.

• Types of Views:     * Simple View: Created from a single table.     * Complex View: Created using multiple tables (involving Joins or aggregate functions).

• Operations:     * Creating/Replacing: CREATE OR REPLACE VIEW view_name AS SELECT ... FROM ... WHERE ...;     * Updating: A view is updateable only if it is based on one table and does not contain GROUP BY, JOIN, DISTINCT, or aggregate functions.         * Example: UPDATE emp_view SET name = 'Amit' WHERE emp_id = 101;     * Dropping: DROP VIEW view_name; (Removes the view, but underlying table data remains).     * Altering: Allows modification of definition without recreation.         * Example: ALTER VIEW myView (id, first_name, city) AS SELECT id, upper(first_name), city FROM employee;

Practical Implementations

Practical 1: Transaction and DCL Control

CREATE TABLE account (
    acc_no NUMBER PRIMARY KEY,
    name VARCHAR2(20),
    balance NUMBER
);
INSERT INTO account VALUES (101, 'Amit', 5000);
INSERT INTO account VALUES (102, 'Neha', 3000);
COMMIT;
UPDATE account SET balance = balance - 1000 WHERE acc_no = 101;
SAVEPOINT sp1;
UPDATE account SET balance = balance + 1000 WHERE acc_no = 102;
ROLLBACK TO sp1;
COMMIT;
GRANT SELECT ON account TO HR;
GRANT SELECT ON account TO SCOTT;
REVOKE SELECT ON account FROM HR;

Practical 2: Sequences, Indexes, and Views

CREATE TABLE students (
    id NUMBER,
    name VARCHAR2(20),
    department VARCHAR2(10),
    marks NUMBER
);
CREATE SEQUENCE stud_seq START WITH 1 INCREMENT BY 1;
INSERT INTO students VALUES (stud_seq.NEXTVAL, 'Amit', 'IT', 85);
INSERT INTO students VALUES (stud_seq.NEXTVAL, 'Neha', 'EC', 90);
INSERT INTO students VALUES (stud_seq.NEXTVAL, 'Rahul', 'IT', 78);
INSERT INTO students VALUES (stud_seq.NEXTVAL, 'Priya', 'EC', 88);
COMMIT;
CREATE INDEX idx_dept_marks ON students(department, marks);
CREATE OR REPLACE VIEW it_students AS 
SELECT id, name, marks FROM students 
WHERE department = 'IT' AND marks > 80;