What are indexes? What is a transaction? What is ACID? What is a foreign key and why is it needed?

Indexes are data structures in a database that speed up data search and sorting. They work like pointers to table rows, allowing quick access to the needed records without scanning the entire table.

Transaction is a sequence of operations with a database that is executed as a single unit. A transaction either completes successfully (commit) or is completely rolled back (rollback), ensuring data integrity.

ACID is a set of properties of transactions:

• Atomicity: all operations of a transaction are completed fully or not at all.
• Consistency: a transaction transforms the database from one valid state to another.
• Isolation: concurrent transactions do not affect each other.
• Durability: after a transaction is committed, its changes are saved even in case of failures.

Foreign key is a constraint in a table that enforces referential integrity by linking a column (or columns) to the primary key of another table. It is used to maintain relationships between tables and prevent inconsistent data, such as adding a record with a non-existent foreign key value.