Libelle IT Glossary Part 23: What is a DBMS? Using the Oracle Database as an example

The electronic processing of data is an essential component in times of Big Data. The concept of electronic databases as logically structured systems has been an important technology since the 1960s.

Without a suitable database system, most companies today can no longer function. Regardless of whether it is a computer or web application, there is a database behind most applications. Anyone who takes a closer look at the subject will quickly realise that a database management system is an essential part of today's IT landscape.

But what is a database management system?

A database management system, or DBMS for short, is the core of a database system and forms the interface between end users and databases. It enables flexible organisation and secure access to data as well as efficient management of affiliations and access rights. In addition, the DBMS also takes care of the administration of data, including the structure and order of the data, but also their dependencies.

What are the components of a DBMS?

The various components of a database management system are essential for the smooth functioning of the software. The DBMS basically consists of three components:

• Data Dictionary: as an essential component of relational databases, it serves as a data catalogue containing all the metadata of a database. It contains information about database objects and data records as well as their relationships to other objects and data, information about authorisations or even time-related information such as change operations and data types.
• Data definition language: The data definition language (DDL) forms the basis for structuring the contents in a database. It can be used to create, delete or change various objects such as tables, references, relationships or user rights.
• Data Manipulation Language: The Data Manipulation Language (DML) is a language for managing data within a database. Unlike DDL, which focuses on defining the data structure, DML allows users to access, query, modify, delete or insert data in the database. The DML also includes operations such as filtering, sorting and grouping data and can be implemented in various programming languages such as SQL, Java and Python.
• Data Manipulation Language: The Data Manipulation Language (DML) is a language for managing data within a database. Unlike DDL, which focuses on defining the data structure, DML allows users to access, query, modify, delete or insert data in the database. The DML also includes operations such as filtering, sorting and grouping data and can be implemented in various programming languages such as SQL, Java and Python.

Despite these fixed components, database systems differ depending on the manufacturer and accordingly have individual strengths and weaknesses, depending on which aspects were particularly emphasised during development. Often the systems differ in the database languages used, the database model chosen and the associated architecture.

To name but a few, SAP SE, Oracle, Microsoft or even IBM have shaped and driven the market of database systems. Since the 2000s, open source database systems such as MySQL or PostgreSQL have been gaining in importance.

In the 23rd part of our Libelle IT Glossary, we take a look at Oracle's database management system, the latest version of which, 23c, is in the starting blocks.

Relational database management system Oracle Database

In the enterprise IT market, Oracle is one of the largest vendors whose best-known product is probably a relational database management system (RDBMS), the Oracle Database. This database software is at the heart of many IT environments and supports a mix of transaction processing, business intelligence and analytics applications.

Originating from a CIA project called "Oracle" in 1975 (source), Oracle Corp. In 1979, Oracle Corp. was the first company to bring an RDBMS to market. After the introduction of the RDBMS technology, Oracle expanded its product portfolio over the years through its own developments and numerous acquisitions.

Oracle Database - Database Architecture

The Oracle Database uses SQL (Standard Query Language), like other relational database management systems (RDBMS), a standardised programming and query language. It is used by database administrators (DBA), data analysts and other IT professionals to manage databases and query the data stored in them.

A special feature is that this software contains and uses a proprietary extension to the SQL standard developed by Oracle: PL/SQL. Such proprietary extensions are quite common among database software manufacturers. The core component of the Oracle Database is the row and column table structure. These allow related data elements in different tables to be linked together.

This saves users from having to store the same data in multiple tables to meet processing requirements. In doing so, the relational model relies on a number of integrity constraints to maintain data accuracy. These checks are part of a broader adherence to the principles of atomicity ("atomicity"), consistency ("consistency"), isolation ("isolation") and durability ("durability") - known as ACID properties. They ensure that transactions in database systems are executed reliably and consistently.

• Atomicity means that a transaction is executed as a whole or not at all. If a transaction fails or is aborted, all changes made within the scope of the
  transaction are undone (rollback).
• Consistency means that a transaction transfers the database system from one consistent state to another consistent state.The integrity of the data is guaranteed, i.e. no inconsistent states may arise.
• Isolation means that each transaction is executed in isolation from other transactions as if it were the only transaction in the system.This prevents transactions from influencing each other or producing inconsistent results.
• Durability means that changes made by a transaction are permanently stored in the database system even after a system crash or power failure.The Oracle architecture consists of various components that work together to ensure the functionality of the database.

The most important components are:

1. Oracle Instance: The Oracle instance is the process that controls the database. It includes the memory area for the database, the System Global Area (SGA), and the process memory, Program Global Area (PGA). The instance is responsible for starting, monitoring and shutting down the database and ensures that the database operation runs smoothly.
2. Oracle Database: The Oracle database is the physical storage for data, including tables, indexes and other objects. There are several types of databases, including a Container Database (CDB) and a Non-Container Database (Non-CDB). Multiple pluggable databases (PDBs) can be managed in a CDB. The new CDB architecture was introduced with Oracle Database Release 12.1.0.2. The architecture of the non-CDB, on the other hand, was given the status "deprecated" in this release. Functionalities that receive this status are still supported for a certain and defined period of time. After this time, the functionality will not be further developed. Since Release 20c, non-CDB is no longer supported. In the CDB architecture, the CDB itself is the container that contains several PDBs. Each PDB is a separate, self-contained database within the CDB that has its own schemas and users and behaves like a separate database. However, each PDB has access to the shared resources of the CDB, such as the system database, the Oracle Managed Files and the shared storage structures. By using a CDB with multiple PDBs, multiple applications can run within a single Oracle database, saving resources and simplifying administration.
3. Oracle Client: The Oracle Client is the software component installed on the user machine that allows users to access the Oracle database. The Oracle Client provides an interface between the application software and the database.
4. Oracle Client: The Oracle Client is the software component installed on the user machine that allows users to access the Oracle database. The Oracle Client provides an interface between the application software and the database.

Databases consist of a mix of physical and logical storage structures.
Physical storage structures include:

• Data files: "Data files" are an important part of the Oracle database architecture. They represent the physical storage space for the database files that store database objects such as tables, indexes and other structures. Each datafile can have a specific size, which is set when it is created, and can be dynamically expanded when more storage space is needed. Datafiles can be organised in different ways, depending on the requirements of the application and the database size. One way is to store the datafiles in a single file system or on a RAID group. Another possibility is to distribute datafiles on different physical devices or on different file systems for better performance and scalability. The management of datafiles is handled by the database itself and usually does not require manual intervention by the user. Oracle provides several tools that DBAs can use to manage and monitor the size and locations of datafiles to ensure that the database operates efficiently and reliably.
• Control Files: Oracle Control Files are a type of "control file" in an Oracle database. They contain important information for managing and controlling the database, including the names and locations of data files, undo management information, redo log information and other metadata. The control files are read when the database is started and serve as the basis for storing and retrieving data in the database. They are therefore an important part of database administration and are backed up regularly to enable database recovery in the event of an emergency. Oracle databases typically use multiple control files to provide greater resilience. If one control file becomes corrupted, the database can continue to operate using the other control files. It is also possible to add new control files or remove existing control files to change the configuration of the database.
• Redo log files: In an Oracle database management system, redo log files are used to record all changes made to the database after the last commit. The redo logs are continuously updated as transactions are executed and serve as recovery points in the event of a system failure or data loss. If a failure occurs, the redo logs can be used to reset the database to the time before the failure and restore all changes since then. The redo logs consist of a series of log files that are used in a cyclical order and can be archived as needed to serve as an additional backup copy.

Logical memory structures include:
* Data blocks: Oracle data blocks are the basic storage units in the Oracle database. They have a fixed size and are used to store data in the database. Each data block consists of a header, the data and a footer. The header contains metadata such as the block number and type, while the footer contains information about the integrity of the block. A data block can contain different types of records, e.g. rows of tables, index data or undo information. The size of the data blocks can be configured in the Oracle database, but is 8 KB by default.
* Extents: In Oracle, an extent refers to a group of contiguous data blocks used to store data in a table or index. An extent is always created as a contiguous block within an Oracle database and can consist of several consecutive Oracle data blocks. When a new object such as a table or index is created in an Oracle database, an extent is reserved by default. When the space in this extent is completely used up, the system automatically reserves more contiguous extents to provide more storage for the object. The size of an extent can be configured to meet the requirements of the database. A larger extent size can help reduce the number of extents required by the database and thus improve performance. On the other hand, a smaller extent size can help save storage space, especially if the database contains many small objects. Overall, efficient management of extents plays an important role in the performance and scalability of Oracle databases. * Segments: In Oracle, a segment is a logical storage object created by a table, index or other database structure to organise the records it contains. A segment consists of at least one extent, a block group area consisting of consecutive blocks of data. This means that a segment has at least one extent and thus a fixed number of data blocks.
* Depending on the type of segment, there are different types of segments in Oracle, such as data segments for tables and clusters, index segments for indices, undo segments for undo data, temporary segments for temporary tables and global temporary tables.
* Tablespaces: In Oracle, a tablespace is a logical storage area in which database objects such as tables, indices, clusters or LOB objects can be stored. A tablespace consists of at least one datafile or one tempfile that are physically stored on the data carrier.

By using tablespaces, different storage needs of database objects can be considered and managed. For example, large tables can be stored in one tablespace with large datafiles, while smaller tables can be stored in another tablespace with smaller datafiles.
There are different types of tablespaces in Oracle, including system tablespaces, which contain system objects such as the data dictionary, and user tablespaces, which contain objects created by users. Temporary tablespaces can also be created to store temporary data sets, such as sorting operations on queries or indexing.

Oracle's product portfolio has grown steadily and today includes several types of databases, applications for multiple business lines, data analysis software, middleware, computer systems, data storage devices, development software and other technologies. Oracle is also establishing itself in the area of cloud computing, after initial hesitation. With the acquisition of Cerner, Oracle is also becoming a strong player in the healthcare sector.

Which databases does Libelle support?

Our software solutions support all common databases:
* IBM DB2
* MariaDB
* Microsoft SQL Server
* MySQL
* Oracle
* PostgreSQL
* SAP ASE
* SAP MaxDB
* SAP HANA

More information about Libelle solutions