This brief article is meant to give a short overview towards basic relational database management system concepts. It serves informational purpose only. You don’t need this in order to build your application. However understanding what your application is doing can help in designing better software.
A relational database system (DBS) consists of the following components:
- A database management system (DMBS), a software able to serve the ACID concept
- A database (DB) which carries relational data
What is the ACID concept?
- Atomicity (all or nothing)
- Consistency (data is consistent before and after a change)
- Isolation (apparent exclusive access)
- Durability (data is persistent)
For the database system to achieve all this, transactions are used. Transactions are a sequence of operations which result in different data changes. Those operations do not change data accessible by other users until a commit is sent to the DBS. Once a commit is done, only changed records can be accessed, no longer the ones before a change was made.
In order to deal with data in terms of a database system we have to distinguish between the following languages, which are part of the overall Structured Query Language (SQL)
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Interactive Query Langauge (IQL)
We use DDL when we create tables, DML when we insert, update or delete data and IQL when we select certain parts of the database.
A transaction commit can be initiated automatically after each SQL operation or manually using the commit command sent to the database. This is useful whenever you want to collectively change certain data bound together by a business case, without letting just portions of it being accessed by users.