Lesson 2: Relational Database Management System Basics

ByDr.-Ing. Erik Neitzel
Lesson 2: Relational Database Management System Basics

This lesson gives an introduction towards basic relational database management system concepts. It serves theoretical purpose only. You don’t need this in order to build an application. However understanding what your WordPress system is doing “under the hood” can help put other lessons in context.

Elements, concept and languages of a database system

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 – there is no state in between a state before and after a desired change)
  • Consistency (data is consistent before and after a change – regardless of how many internal transactions may be necessary for the system to preserve that secure state)
  • Isolation (apparent exclusive access – you application can access the whole database even if another application is doing the same, without disturbing one another)
  • Durability (data is persistently written to its carrier medium at all times)

For the database system to achieve all this, transactions are used. Transactions are a sequence of operations which result in different states of data. 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 table 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.

A bloggers perspective toward database concepts

For you as a blogger it is important to understand when database actions are performed and which category they belong to:

  • Installing your WordPress installation creates many database tables and fills them with initial data, some of which are derived from your basic installation input (e.g. your blogs name, your admin users email address or your personal password) (DDL).
  • Each WordPress plugin you activate will most likely create new tables in your database (DDL).
  • As soon as you save a post certain database tables (e.g. wp_posts and wp_post_metadata) will be updated (DML).
  • Whenever you log into your blog software, your user data (user name and password) will be checked for a match within a datbase table (e.g. wp_users when using WordPress) (IQL).
  • When a user visits your website, PHP scripts are called which read data out of your database which is used to deliver seemingly static HTML to the visitors web browser (IQL)

The last point already shows why you as both blogger and WordPress administrator will most likely use caching plugins. Those will pre-load all needed database contents and created static HTML to be served to the browser on each request. That way we prevent the whole database requesting (IQL) processes from being being repeated over and over again for each request without actually having new content in the database. Those plugins have to rebuild that static HTML only when you change a posts content.

The next lessons:

Dr.-Ing. Erik Neitzel

Search

Newest articles

Meta