Smart technology for a strong manufacturing industry with 24/7 uninterrupted production of IIoT solutions for companies.
The first RDBMS systems emerged in the late 70s of the previous century with Oracle being one of the first big vendors providing a RDBMS system. Later on IBM DB2, Informix and other vendors followed. Most known RDBMS databases today are Oracle, Microsoft SQL Server, PostgresQL, MySQL and IBM DB2.
In a RDMBS system we organize data in one or more tables. Each table exists of columns and rows with a unique key identifying each row. We call those rows records and those keys primary keys. Each table represents an entity type such as customer or product. The rows represent instances of that type of entity and the columns represent values attributed to that instance (such as name or unit price).
Rows in a table can be linked to rows in other tables by adding a column for the unique key of the linked row. We call these foreign keys. Apart from keys we can also define unique indices to add additional constraints to the database and we can use standard indices for faster retrieval of data. Those indices are usually implemented through some sort of B-trees, R-trees or bitmaps.
Most RDBMS systems also provide the concept of Stored Procedures: in essence this is executable code stored in the RDBMS database. Also the concept of views is supported: in essence this is a query stored in the RDBMS database and that will be executed at the moment one queries the view. A materialized view on the other hand is a kind of snapshot of an executed query that is stored internally as a kind of table. Refreshing such a materialized view actually means re-executing the underlying query (like in a view) and storing the results in a snapshot table (unlike a common view).
RDBMS databases are inherently ACID-compliant. Every modification on data will transfer the database from one consistent state to another consistent state.
All RDBMS systems support SQL: Structured Query Language. This query language became an international standard in 1987 and has been evolved over time providing powerfull features. Although not all RDBMS apply the same ‘flavour’ of SQL, most of them all support the standard SQL operations.
SQL consists of 4 parts:
DQL: data control language to control access to data stored in the database (e.g. grant, revoke)
DQL: data query language to perform queries on the data in the database (e.g. select)
DML: data manipulation language to add, delete or modify data in the database (e.g. insert, update, delete)
DDL: data definition language to create and modify database objects in the database like tables or indices etc. (e.g. create, drop)
Apart from the standard organization of the data in tables, columns and rows we also have different modeling paradigms for RDBMS.
With normalization we try to minimize the redundancy (duplication) of data. This prevents data manipulation anomalies and loss of data integrity and has been the de-facto standard for OLTP (Online Transactional Processing) databases. So whenever we build a system that requires heavy input from users, normalization of the data structure is a must.
With denormalization we try to minimize the amount of joins required to retrieve data. This prevents heavy query operations and is mainly targeted to optimize querying. This modeling paradigm is mainly used for data warehouses and reporting databases and supports OLAP (Online Analytical Processing) databases. In data warehousing we mostly apply the concept of star schema where data is split in 2 types of tables: facts containing all measure data and dimensions containing all data on a specific axis (time, customer, product) that you would use to query or analyse the system.
Since OLTP and OLAP have completely different use cases we can’t apply them in most cases on one single database. That’s the main reason why in RDBMS systems we need to offload data from OLTP databases to a central OLAP database in order to support the organizational OLTP and OLAP needs.
This led to quite some latency in analytical processing of data since traditionally it requires specific design strategies to be able to offload and transform data from an OLTP database and normalized structure to an OLAP database and denormalized structure. Most data warehouses still use the concept of day-1 data availability.
With technologies like CDC (Change Data Capture) that continuously replicate data from the OLTP database to the OLAP database we can eliminate the latency due to the required offloading of data from the OLTP system. However, we can’t eliminate the transformation processing required to transform the normalized to a denormalized structure. Since the amount of data passing through the CDC ‘hub’ is however much less than if we were to perform a one-time-per-day load, the transformation process will be faster. It requires a well-thought design to incorporate all required relations. Multiple techniques exist to design such a proper load mechanism, balancing out on the consistency criteria previously mentioned.
The existence of numerous NoSQL databases does not mean that RDBMS systems no longer have a purpose.
Advantages:
Strong data consistency (ACID)
Support for complex queries through SQL joins, aggregations, etc.
Standardized ecosystem (SQL is universally known)
Transactions are reliable
Disadvantages:
Limited or no horizontal scalability
Rigid schemas: harder to modify the structure
Poor performance with very large unstructured datasets and not well suited for big data applications
Financial systems
ERP, CRM, MES
Applications where immediate data consistency is crucial