Skip to content
CampusPrep
ME-606 · RDBMS Lab/Quick Revision Short Notes

RDBMS Lab (ME-606) - Unit 5 Short Notes

UNIT 5: ADVANCED RDBMS CONCEPTS & PERFORMANCE

5.1 Database Programming Constructs: Stored Procedures & Functions

Stored Procedure (SP): A precompiled set of SQL statements stored in the database, executed via a single call.

  • Purpose: Modularity, security (permission on procedure, not tables), performance (reduced network traffic, execution plan reuse).

  • Syntax (General):

    
CREATE PROCEDURE proc_name

    @param1 datatype = default,

    @output_param OUTPUT datatype OUTPUT

AS

BEGIN

    -- SQL Statements

END

  • Execution: EXEC proc_name param1_value, @output_param OUTPUT;

User-Defined Function (UDF): Returns a single value (scalar) or a table (table-valued).

  • Scalar Function: Returns one data value. Used in SELECT, WHERE, etc.

    
CREATE FUNCTION dbo.fn_GetTax(@salary DECIMAL)

RETURNS DECIMAL

AS

BEGIN

    RETURN @salary * 0.1;

END

  • Table-Valued Function (TVF): Returns a table. Can be used in FROM clause like a view.

    
CREATE FUNCTION dbo.fn_GetEmployeesByDept(@dept_id INT)

RETURNS TABLE

AS

RETURN (SELECT * FROM Employees WHERE DeptID = @dept_id);

Comparison: Stored Procedure vs. Function

| Feature | Stored Procedure | User-Defined Function |

| :--- | :--- | :--- |

| Return Value | Can return 0 or more result sets; uses OUTPUT parameters. | Must return a single value (scalar) or a table (TVF). |

| Invocation | EXEC/CALL statement. | Used within SELECT, JOIN, WHERE, etc. |

| Side Effects | Can modify database state (DML). | Scalar/TVF: Should not modify database state (ideally deterministic). Inline TVF can be optimized like a view. |

| Transaction | Can contain transaction control (BEGIN/COMMIT/ROLLBACK). | Cannot contain transaction control. |

| Error Handling | Can use TRY...CATCH. | Limited error handling. |

5.2 Triggers: Automating Data Integrity & Auditing

Trigger: A special stored procedure that automatically executes in response to a INSERT, UPDATE, or DELETE event on a table.

Types:

  • By Timing: BEFORE (fires before the event) / AFTER (fires after the event). INSTEAD OF (replaces the event, common on views).

  • By Scope: ROW (fires once per affected row) / STATEMENT (fires once per SQL statement).

Syntax (General - AFTER, STATEMENT-level):


CREATE TRIGGER trg_name

ON table_name

AFTER INSERT, UPDATE, DELETE

AS

BEGIN

    -- Logic referencing inserted/deleted pseudo-tables

END

  • inserted Table: Holds new rows (for INSERT/UPDATE).

  • deleted Table: Holds old rows (for DELETE/UPDATE).

Common Use Cases:

  1. Audit Logging: Insert rows into a history/audit table from inserted/deleted.

  2. Enforcing Complex Business Rules: Cross-table validation not possible with CHECK constraints.

  3. Derived Column Maintenance: Auto-populate computed columns or summary tables.

[!TIP] Mutating Table Error: In Oracle, a ROW-level trigger cannot read or modify the table that fired it (the "mutating table"). Use statement-level triggers or compound triggers. Most other RDBMS (like SQL Server, PostgreSQL) allow this but with caution.

5.3 Views: Virtual Tables for Security and Simplicity

View: A virtual table defined by a SELECT query. Stores only the query definition, not data (unless materialized).

Purpose:

  • Data Abstraction: Simplify complex queries (joins, aggregates) into a single object.

  • Security: Restrict access to specific rows/columns. Grant permissions on the view, not base tables.

  • Logical Data Independence: Shield applications from underlying schema changes.

Creation:


CREATE VIEW view_name AS

SELECT column1, column2

FROM table1

JOIN table2 ON ...

WHERE condition;

Updatable Views: A view is updatable if it meets specific criteria (e.g., no aggregates, DISTINCT, GROUP BY, JOIN in certain cases). Rules are RDBMS-specific.

Materialized View (Indexed View in SQL Server):

  • Stores the actual result set physically (like a table).

  • Pros: Dramatically improves query performance for complex, frequently run aggregations.

  • Cons: Storage overhead, maintenance cost on base table DML (must refresh).

  • Creation (SQL Server): CREATE UNIQUE CLUSTERED INDEX on a view with SCHEMABINDING.

Management: ALTER VIEW, DROP VIEW.

5.4 Indexing for Performance Optimization

Index: A database object that speeds up data retrieval (read operations) at the cost of slower writes and extra storage.

Core Trade-off: Read Performance ↑ vs. Write Performance ↓ & Storage ↑

Primary Types:

Type Structure Data Order Number per Table Key Use
Clustered Defines physical order of data rows. Sorted by key. 1 (since data can be sorted one way). Primary Key; range queries (BETWEEN, >).
Non-Clustered Separate structure with pointers to data rows. Sorted by key. Many (up to 999 in SQL Server). Columns in WHERE, JOIN, ORDER BY.

Other Key Types:

  • Single-Column vs. Composite (Multi-Column): Composite indexes follow the left-most prefix rule. An index on (ColA, ColB, ColC) is used for queries filtering on ColA, (ColA, ColB), but not on ColB alone.

  • Unique Index: Enforces uniqueness (like a UNIQUE constraint).

  • Filtered Index (SQL Server): Index on a subset of rows (WHERE condition). Saves space, improves index maintenance.

  • Covering Index: A non-clustered index that contains all columns required by a query, eliminating the need to access the base table ("key lookup").

Index Creation & Considerations:


CREATE INDEX idx_name ON table_name (column1, column2);

  • Selectivity: Ratio of distinct values to total rows. High selectivity (e.g., UserID) is best for indexes.

  • Column Order: In composite indexes, place most selective/frequently filtered columns first.

  • Fragmentation: Over time, page splits cause logical order mismatch. Requires REORGANIZE (light) or REBUILD (heavy) maintenance.

Execution Plans: Use EXPLAIN (PostgreSQL/MySQL) or "Display Estimated Execution Plan" (SQL Server) to see if the optimizer uses an index seek/scan.

5.5 Database Tuning & Query Optimization

Goal: Reduce query execution time and resource consumption.

Process:

  1. Identify Slow Queries: Use query logs, DMVs (sys.dm_exec_query_stats), or monitoring tools.

  2. Analyze Execution Plan: Look for:

    • Table/Index Scans (bad for large tables) vs. Seeks (good).

    • Key Lookups (consider covering index).

    • Expensive Operators: Sort, Hash Match (large datasets).

    • Implicit Conversions (prevents index use).

  3. Optimize Query:

    • Avoid SELECT *; specify columns.

    • Use EXISTS() instead of IN() for subqueries with NULLs or large datasets.

    • Prefer JOIN over correlated subqueries for better optimization.

    • Be cautious with LIKE '%prefix' (leading wildcard prevents index use).

    • Minimize use of functions on indexed columns in WHERE clause (e.g., WHERE YEAR(DateCol) = 2023).

  4. Update Statistics: Query optimizer uses table/index statistics to choose plans. Auto-update is typical, but manual UPDATE STATISTICS may be needed after bulk changes.

  5. Transaction Log Impact: Large, unlogged operations or long-running transactions can fill the log and cause blocking. Manage log growth and VACUUM/CHECKPOINT (PostgreSQL) or CHECKPOINT (SQL Server) frequency.

5.6 Introduction to Database Administration (DBA) Tasks

User & Privilege Management (RBAC):

  • Principle of Least Privilege: Grant minimum necessary permissions.

  • Roles: Group privileges. Create role, grant to role, add users to role.

    
CREATE ROLE app_readonly;

GRANT SELECT ON schema.* TO app_readonly;

GRANT app_readonly TO user1;

  • Direct Grants: GRANT SELECT, INSERT ON table TO user;

  • Revoke: REVOKE SELECT ON table FROM user;

Backup & Recovery Fundamentals:

  • Full Backup: Complete copy of the database. Foundation for all restores.

  • Differential Backup: Only backs up data changed since last full backup. Faster, smaller.

  • Transaction Log Backup: Backs up all log records since last log backup. Enables point-in-time recovery. Requires full recovery model.

  • Recovery Models: SIMPLE (no log backups), FULL (all backups, point-in-time), BULK_LOGGED (minimal logging for bulk ops).

Security Concepts:

  • Authentication: Verifying identity (SQL Auth vs. Windows/AD Auth).

  • Authorization: What the authenticated user can do (permissions/roles).

  • DBA Role: Ultimate control over server/database security, performance, availability.

5.7 Emerging Trends & NoSQL Context (Brief Overview)

RDBMS (ACID):

  • Structured: Fixed schema (tables, columns, types).

  • ACID Transactions: Atomicity, Consistency, Isolation, Durability. Strong consistency.

  • SQL: Standardized query language for complex joins and ad-hoc queries.

  • Use Case: Financial systems, ERP, traditional OLTP.

NoSQL (BASE):

  • Flexible Schema: Document, Key-Value, Column-Family, Graph.

  • BASE Model: Basically Available, Soft state, Eventual consistency. Prioritizes availability and partition tolerance over strong consistency (per CAP theorem).

  • Horizontal Scaling: Designed to scale out across commodity servers (sharding).

  • Use Case: Big data, real-time web apps, IoT, content management, social graphs.

Examples:

  • Document: MongoDB (JSON-like documents).

  • Key-Value: Redis (in-memory, ultra-fast).

  • Column-Family: Cassandra (wide-column, high write throughput).

  • Graph: Neo4j (relationships as first-class citizens).

Modern Context:

  • Polyglot Persistence: Using multiple database technologies (RDBMS + NoSQL) for different parts of an application.

  • NewSQL: Attempts to provide ACID transactions with horizontal scalability (e.g., Google Spanner, CockroachDB).

Go to where you left off?

Quick Add to Notes

Save questions, your own notes and screenshots into notes filed by unit. It takes a free account.

Create free account

Have an account? Log in