A transaction is a unit of work that takes the database from one consistent state to another, and it must satisfy the ACID properties. Atomicity guarantees that all of the operations within a transaction either complete together or have no effect at all. Consistency ensures that the database ends in a valid state that respects all constraints. Isolation shields concurrent transactions from each other's intermediate results, and Durability guarantees that, once a transaction commits, its changes survive crashes and power loss. In SQL, transactions are managed with BEGIN, COMMIT, and ROLLBACK.
Isolation is tunable through isolation levels, which trade off consistency for performance. READ UNCOMMITTED permits dirty reads, where a transaction sees uncommitted changes from another. READ COMMITTED prevents dirty reads by only seeing committed data. REPEATABLE READ ensures that, within a transaction, the same query returns the same result each time. SERIALIZABLE offers the strictest isolation, effectively running transactions one after another. The level is set with SET TRANSACTION ISOLATION LEVEL;.
Good schema design supports these guarantees through normalization, a process that reduces redundancy and dependency. The First Normal Form, or 1NF, requires that every column hold atomic values, that there be no repeating groups, and that each table have a primary key. The Second Normal Form, 2NF, builds on 1NF by removing partial dependencies so that non-key attributes depend on the entire primary key. The Third Normal Form, 3NF, goes further by removing transitive dependencies so that non-key attributes do not depend on other non-key attributes. Beyond schema design, the database itself can host reusable logic: stored procedures are precompiled SQL routines that reduce network traffic, while triggers are special procedures that automatically fire on events such as INSERT, UPDATE, or DELETE.