Database Tutorial

The original design of relational databases focused on the storage and transaction integrity of inserts, updates, and deletes. The designers ensured that all SQL statements guaranteed ACID transaction compliance. However, they added Transaction Control Language (TCL) to ensure that developers could extend ACID transaction compliance across multiple INSERT, UPDATE, and DELETE statements.

Transaction Control Language (TCL) consists of:

COMMIT Statement

The COMMIT statement instructs the database to make any recent changes permanent.

ROLLBACK Statement

The ROLLBACK statement undoes any changes back to the last COMMIT, DDL statement, or beginning of a connection or session generically; however, the ROLLBACK TO savepoint only undoes all changes after the point where the SAVEPOINT was set.

SAVEPOINT Statement

The SAVEPOINT statement sets a point, like a bookmark, that you can roll back subsequent statement to.

These are useful inside imperative programming languages, like Python, C#, PHP, or Java. The TCL statements let external programs manage groups of INSERT, UPDATE, and DELETE statements as transactional groups. Any database that supports all these constructs is typically at least a thin database.

You promote a database from a thin architecture to a thick database when you include an imperative language inside the database, like PL/SQL (Procedure Language/Structured Query Language). Oracle, PostgreSQL, SQL Server, and IBM DB2 all support imperative languages inside the database, which makes them thick databases.

For example, in a thick database a web programmer may call a stored procedure or function instead of a Python library or Java class to simplify the complexity of the data model. Stored procedures or functions, Python libraries, or Java classes may all be Application Programming Interfaces (APIs). A thick database supports an imperative programming language that stores API logic inside the database; whereas, a thin database only supports external libraries and classes.

A thick database solution places the critical transaction control on the server rather than the client-server drivers that support external languages like Python, C#, or Java. The Oracle Database is considered a thick database because it supports a native imperative language that interacts directly with its SQL engine. Thick databases have recently been marketed as intelligent databases because the API logic resides in the data catalog or dictionary.