A transaction is a sequence of SQL operations executed as a single unit of work. Transactions ensure ACID properties (Atomicity, Consistency, Isolation, Durability), meaning that either all operations in a transaction are completed successfully, or none are. You can control transactions with `BEGIN`, `COMMIT`, and `ROLLBACK` commands.
An index in SQL is a database object that improves the speed of data retrieval operations on a table by providing a fast lookup mechanism. Indexes are typically created on columns that are frequently queried. However, they can also slow down `INSERT` and `UPDATE` operations.
A stored procedure is a set of SQL statements that can be saved and reused. They encapsulate business logic within the database, improve performance, and reduce network traffic. You can call them with parameters to execute complex operations. For example:
```sql
CREATE PROCEDURE GetEmployeeDetails(@id INT)
AS
BEGIN
SELECT * FROM employees WHERE id = @id;
END;
```
Normalization is the process of organizing a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller tables and defining relationships between them. The normal forms (1NF, 2NF, 3NF, etc.) are guidelines for designing a normalized database.
A foreign key is a column (or a set of columns) in one table that references the primary key in another table. It establishes a relationship between the two tables, ensuring referential integrity. For example, the `department_id` in an `employees` table might be a foreign key referencing the `id` column in a `departments` table.
