Duration: 2 hours (Lecture: 2 hours, Lab: 2 hours)

Syllabus Topics:

• Lecture:
  • Indexes, Benefit of Indexes, Type of Indexes
  • Temporary Tables
  • ACID Properties
  • Concept of Database Instance and Schema
  • MySQL Storage Engines (InnoDB, MyISAM, and others)
• Lab:
  • Indexes, Temporary Tables
  • All other SQL Commands Revision

Lecture Notes (In-Depth)

1. Indexes, Benefit of Indexes, Type of Indexes

• Indexes:
  • Database structures that improve the speed of data retrieval operations on tables.
  • Analogous to an index in a book, allowing quick lookup of rows based on column values.
  • Created on columns frequently used in WHERE, JOIN, GROUP BY, or ORDER BY clauses.
  • Syntax: CREATE INDEX index_name ON table_name (column);.
• Benefits of Indexes:
  • Faster Queries: Reduces the number of rows scanned for SELECT, JOIN, and WHERE operations.
  • Efficient Sorting: Speeds up ORDER BY and GROUP BY operations.
  • Improved Join Performance: Facilitates faster matching of rows across tables.
  • Constraints Enforcement: Primary Key and Unique constraints automatically create indexes.
• Drawbacks:
  • Increased storage space for index data.
  • Slower INSERT, UPDATE, and DELETE operations due to index maintenance.
  • Overhead for large, infrequently queried tables.
• Types of Indexes:
  • Primary Index: Automatically created for the Primary Key column (unique, non-NULL).
    • Example: emp_id in CREATE TABLE employees (emp_id INT PRIMARY KEY);.
  • Unique Index: Ensures all values in the indexed column(s) are unique (allows NULLs).
    • Syntax: CREATE UNIQUE INDEX idx_email ON employees (email);.
  • Non-Unique Index: Speeds up queries without enforcing uniqueness.
    • Syntax: CREATE INDEX idx_dept_id ON employees (dept_id);.
  • Composite Index: Covers multiple columns, useful for queries with multiple conditions.
    • Syntax: CREATE INDEX idx_dept_salary ON employees (dept_id, salary);.
  • Full-Text Index: Used for full-text searches on text columns (e.g., TEXT or VARCHAR).
    • Syntax: CREATE FULLTEXT INDEX idx_notes ON employees (notes);.
    • Example: SELECT * FROM employees WHERE MATCH(notes) AGAINST('project');.
  • Spatial Index: For spatial data types (e.g., GIS applications, not covered in detail here).
• Key Notes:
  • Use SHOW INDEXES FROM table_name; to view indexes.
  • Use EXPLAIN SELECT ...; to analyze if an index is used in a query.
  • Drop an index with DROP INDEX index_name ON table_name;.

2. Temporary Tables

• Purpose: Temporary tables store data for the duration of a session or transaction, automatically dropped when the session ends.

• Syntax: CREATE TEMPORARY TABLE table_name (column_definitions);.

• Characteristics:

  • Visible only to the session that creates them.
  • Do not conflict with permanent tables of the same name.
  • Ideal for intermediate results in complex queries or stored procedures.

• Example:

  CREATE TEMPORARY TABLE temp_employees (
      emp_id INT,
      name VARCHAR(50)
  );
  INSERT INTO temp_employees VALUES (1, 'Alice');
  
  

• Key Notes:

  • Cannot create indexes on temporary tables in some MySQL versions (e.g., pre-8.0).
  • Use DROP TEMPORARY TABLE table_name; to explicitly drop (optional, as they auto-drop).

3. ACID Properties

• Definition: ACID (Atomicity, Consistency, Isolation, Durability) properties ensure reliable database transactions.