Database basics and use cases in production

What is a database?

A database is a system for the digital storage and management of larger amounts of data. Databases must be able to store information permanently, structured and error-free. At the same time, they enable users and software applications to access the stored data in a targeted manner..

Definition

Definition and components of a database

A database is made up of two core elements: A software for managing the database (database management system) and the stored data itself (formally referred to as a database in the narrower sense). Together, the database management system and the stored data are also referred to as a database system.

The database management system (DBMS) is responsible for all tasks relating to the creation, administration and querying of the database. This also includes functions such as performance optimization of the database and the management of permissions.

Communication between other applications or users and the database management system (and therefore also the retrieval of data) is made possible by using a database language.

The most common form of database is the relational database using the SQL database language. In the section „What are the different types of databases?“ we go into detail about this type of database.

Relevance

Why are databases so important?

Databases are an essential component of modern IT infrastructures and many software systems themselves.

In practice, they store all kinds of information, including customer or product data, transaction details or articles on a website. Suitable interfaces are used to make the stored information available to other users, services or programs.

The ability of a database to store information permanently, efficiently, error-free and securely is particularly important.

Functionality

What functions do databases provide?

A database management system (DBMS) fulfills various functions. At the center is the ability to save, modify and delete data records. To do this, the DBMS accepts suitable commands and ensures that they are executed correctly and, if applicable, the requested results are returned.

Many databases support structured and complex queries for retrieving or manipulating data.

To make communication with other applications more efficient and secure, so-called „triggers“ and „stored procedures“ are used.

A database trigger initiates a predefined action in the database when a certain event occurs (e.g. inserting, changing or deleting data). These triggers can, for example, prevent actions or change data themselves, either before, after or instead of the triggering event.

Stored procedures are complex instructions or sequences of instructions that can be stored by the DBMS and triggered with a single command. This allows many individual commands to be combined. Stored procedures also contribute to the security of a database system by limiting the available commands to predefined actions.

The DBMS is also responsible for optimizing the operation and performance of the database and ensuring the security and integrity of the stored data. This is also associated with enabling multi-user operation: If several users access the database at the same time, it must be ensured that inconsistencies do not occur due to simultaneous changes to data. For this purpose, a DBMS can temporarily block access to certain data.

The database management system is also responsible for managing user permissions and performing backups.

manubes-Logo

Digital production management in the cloud

Our manubes platform allows manufacturers to aggregate, organize and visualize production data in the cloud.

With easy-to-use tools for data modeling, interactive visualization and process automation, manubes is a powerful digitalization platform tailored to industrial production.

manubes users profit from worldwide access via web browser, maximum security for production data and support for industrial standard interfaces.

Categorization

What are the different types of databases?

Databases can be classified according to various criteria. These include, for example, the storage location of the data (on-premise databases vs. cloud databases), the license type (open source databases vs. commercial/proprietary databases) or the specialization (time series databases, geographic databases, …).

A common categorization differentiates database systems by their database model, i.e. the way in which data is organized and stored within the database. The most utilized database model is the relational database model, using the database language SQL. However, so-called NoSQL databases are also used as an alternative in various areas. Both models are explained below.

The database model is determined by the database management system.

The reason for using a certain type of database usually corresponds to specific requirements. In many areas such as healthcare and banking, error-free and consistent data is essential for many processes. On the other hand, for applications such as big data analytics, high performance and scalability may be required when analyzing huge amounts of data, while minor inconsistencies are accepted.

Relational databases

The most popular type of database is the relational database. In a relational database, data is stored in tables, with the columns of the table containing various attributes and each row representing a data record. The following illustration shows a simple example of a table in a relational database:

Table with customer data in a relational database

In this example, each customer has three attributes: A customer ID, a name and an email address.

The customer ID is the so-called primary key: In a relational database table, each data record (in this case a customer) is uniquely identified by a primary key.

This key can in turn appear as an attribute in other data tables in order to reference a data record (here a customer):

Table with order data in a relational database

The CustomerID column contains primary keys from the „Customers“ table to refer to the customer who placed the order. This is referred to as a foreign key.

The common database language for relational databases is SQL (Structured Query Language), which is based on relational algebra. SQL commands can be used to perform complex data queries and many other database operations.

Different database providers implement the SQL language standard in different ways. Relational database management systems such as MySQL or PostgreSQL are therefore also referred to as SQL dialects, as they implement their own extensions to the SQL standard.

MySQL, Microsoft SQL Server and Oracle are among the best-known examples of relational database systems.

Relational databases offer a high degree of data integrity and consistency as well as security when carrying out complex transactions. They are therefore particularly important for business-critical processes.

NoSQL

NoSQL databases offer an alternative to the relational database model. The term refers to databases that are not based on the relational database model. NoSQL databases may store data in various, often less structured ways. One example are document-oriented databases, where data is stored as individual documents that do not necessarily share the same structure.

Thanks to their horizontal scaling, NoSQL databases are particularly suitable for larger volumes of less structured data. One area of application is video streaming where NoSQL databases are used to store user profiles, metadata and for caching and processing various streaming data in real-time.

Relational databases have performance problems when data is stored in large quantities and frequently accessed at the same time. In contrast, NoSQL databases offer high scalability, but often lack guarantees in terms of data consistency and transaction security. Many NoSQL databases use the „eventual consistency“ model, in which data is not immediately consistent.

The best-known NoSQL databases include MongoDB, Cassandra and Redis.

manubes-Logo

Digital production management in the cloud

Our manubes platform allows manufacturers to aggregate, organize and visualize production data in the cloud.

With easy-to-use tools for data modeling, interactive visualization and process automation, manubes is a powerful digitalization platform tailored to industrial production.

manubes users profit from worldwide access via web browser, maximum security for production data and support for industrial standard interfaces.

Production

Databases in industrial production

Reliable databases are also essential in industrial production. Data relating to production orders, resources and products must be stored and managed in a structured manner, as must sensor and machine data that provides information on the status of production and possible deviations from the ideal state.

Many software systems used in production planning, production control and production monitoring integrate their own database systems to manage the relevant data. Examples include manufacturing execution systems (MES) or enterprise resource planning systems (ERP systems).

The management of raw materials, semi-finished products and finished products also requires the use of databases. Special software systems that are used in this area and use their own databases include merchandise management systems (MMS) and supply chain management systems (SCM systems).

Tasks such as maintenance planning, quality assurance and safety checks also require access to complete and accurate data.

In addition, production lines or even individual machines can be equipped with local databases. These often store operational, machine and sensor data such as information on the production time, production quantity and other parameters.

Interfaces for communication with databases

Appropriate interfaces are required so that data can be exchanged between the various applications in modern production environments.

A widely used interface is the standardized Open Database Connectivity (ODBC) API which was developed specifically for communication with relational databases. With ODBC, applications are able to access various different types of relational databases via SQL commands. An ODBC driver translates these requests into the respective language of the proprietary database system.

MES, ERP and other software systems in production that integrate their own databases often support industrial standard interfaces such as OPC UA or REST, which enable manufacturer-independent data exchange with machines, devices and other software applications.

In addition, the widespread use of database systems such as MySQL and Microsoft SQL Server means that many applications and platforms integrate the corresponding interfaces directly. Our cloud platform manubes also enables seamless communication with MySQL and Microsoft SQL Server databases.

Discover manubes!

Cloud-based production management with manubes: Our innovative platform offers specialized tools for connecting production systems, managing and visualizing production data and automating production processes. manubes users benefit from a powerful infrastructure, worldwide access and maximum security.