It won’t take long for those looking to get into to software development to encounter the term CRUD. The acronym stands for the typical operations used for communicating with database systems; these normally form the basis of database management. But just how are these access functions for data management actually set up? And how much of role does CRUD play when it comes to the databases’...
Hardly any computer or online application can work without a suitable database system. It’s precisely for this reason that it is certainly a good idea to go into a little more detail on this topic. Just as important as the data pool itself is an underlying database management system, without which a database simply wouldn’t work.
What is a DBMS?
A Database Management System – DBMS for short – along with the data pool forms a database system (the general term ‘database’ is frequently used for the latter). With a DBMS like this there is generally software that determines the model of a database system and is therefore the crucial component to even be able to construct, manage and use a database. Only once the database management system has been installed and established can users add and read the data pool desired. Reading and writing permissions as well as general administrative functions are made possible by application-specific interfaces and an appropriate database language. The most well-known of these languages is SQL (Structured Query Language).
The terms ‘Database Management System (DBMS)’ and ‘database’ are often wrongly conflated when describing the software that manages a data pool. In fact, the database is nothing more than the data pool in a structured format, while the DBMS is the crucial component in implementing these structures.
What components does a DBMS have?
A database management system consists of various components, all of which contribute to the software running smoothly. The DBMS can be broken down into three principal components. These are the data dictionary, the data definition language and the data manipulation language.
- Data dictionary: The data dictionary is a concentration of metadata. This metadata contains information about the content of the different data in the data storage. It all serves as a source of information via the user authorisation for the data pools as well as the physical representation. To put it simply, all relevant information about the data saved in a database is stored in the dictionary.
- Data definition language: The data definition language (DDL) has the job of structuring the contents of a database. Individual objects like references, relationships or user permissions can be changed, deleted and created with the help of the definition language.
- Data manipulation language: Data sets contained in a database can be deleted, inserted, changed and read with the help of the data manipulation language (DML). This language also makes it possible to compress and extract this data.
Tasks, functions and requirements of Database Management Systems
The database management system is the most important component in a database system. Without the DBMS the data pool can neither be managed nor monitored. The software is also responsible for managing all reading and writing permissions for the database. One widely used term to describe the functions and requirements for transactions in a database management system is ACID, an acronym for atomicity, consistency, isolation and durability. The terms within ACID cover the most important requirements for a DBMS:
- Atomicity describes the ‘all or nothing’ property of DBMS, whereby only valid queries can take place in the correct sequence and so the entire transaction is carried out correctly.
- Consistency requires successful transactions to leave a stable database behind, making it necessary to constantly verify all transactions.
- Isolation is the term for the requirement that transactions don't ‘get in each other’s way’, which is often guaranteed by certain locking functions.
- Durability means that all data in the DBMS is stored permanently, even after a successful transaction is completed. This is also true, or especially so, for system errors or the DBMS malfunctioning. Transaction logs that simultaneously record all operations in the DBMS are essential to durability.
Below you can find a further breakdown of the functions and demands of a database management system following the ACID model.
|Storing data||Databases store electronic texts, documents, passwords and other information, which can be retrieved with queries.|
|Editing data||Most databases – depending on access permissions – allow the information stored to be edited directly.|
|Deleting data||Data sets contained within databases can be deleted completely. In some cases, data that has been deleted can be retrieved, but in other cases the information is then gone forever.|
|Managing metadata||Information in databases is mostly stored with metadata or metatags. These tidy up the database and make the search function possible, for example. Often, access permissions via metadata are also regulated.The data management follows four fundamental operations: Create, Read/Retrieve, Update and Delete. This concept, known as the ((CRUD principle||websites/web-entwicklung/crud-die-wichtigsten-Datenbankoperationen/)), is the basis for the data management.|
|Data security||Databases must be secure so that unauthorised individuals can’t access the data stored. Along with a high-performance encryption process, careful management – particularly by the main administrator – is essential for data security. Data security generally means taking the technical precautions to prevent the data being manipulated or lost. It is a key concept of data protection.|
|Data integrity||Data integrity means that data within a database complies with certain rules to ensure that the data is correct and to define the business logic of the database. Only in this way can it be ensured that the database as a whole works constantly and consistently. In relational database models there are four of these rules: Area integrity, entity integrity, referential integrity and logical consistency.|
|Multi-user mode||Database applications permit access to the database from various devices. In multi-user mode, data security and the distribution of permissions are fundamental. One challenge for databases in multi-user mode is also how data can be kept consistent with simultaneous reading and writing permissions without seriously affecting performance.|
|Query optimisation||On the technical side, a database must process each query as optimally as possible in order to guarantee good performance. If a database goes ‘in different directions’ with a data query, the general performance of the database system will suffer.|
|Trigger and stored procedures||These processes are mini applications stored inside database management systems, which can be retrieved (‘triggered’) with certain change activities. The aim of this is to improve data integrity, among other things. With relational databases, database triggers and stored procedures are typical processes – the latter can also contribute to system security if users are only allowed to carry out activities with prefabricated procedures.|
|System transparency||System transparency is particularly relevant in dispersed systems: By keeping the data distribution and implementation from the user, using the distributed database is then the same as with a centralised database. Different grades of system transparency reveal or conceal background processes. The necessary function, however, is to make it as simple as possible to use.|
If you are operating your own database, comprehensive data security is extremely important!
What types of DBMS / database models are there?
A database management system is installed on a system in order to be able to manage data sets optimally. As already mentioned, here there are various models that primarily differ in the intended structuring of the data. The choice of DBMS, therefore, also means determining a database model. The following database models are available to choose from:
The most widely used and most popular is the relational database model. In this model, the data is structured in table rows. The advantage of this is the possibility of creating certain relationships between the individual rows and representing these in columns. The approach with a hierarchical database model is different: Here, the different data interacts in parent-child relationships, and so it is structured in a hierarchical system.
The network-oriented database model represents another approach to organising data. Here, the data is structured inside a network. The object-oriented database model works in a different way. Alongside the relationships between the data sets, heredity also plays an important role here. In this way, objects – regulated by the DBMS – can transmit properties to other objects.
Database models that work in a document-oriented way envisage the storage of data sets in different documents.
Database Management Systems: An overview of advantages and disadvantages
As the cornerstones of databases, database management systems are characterised by different strengths and advantages. Like with any other software, though, DBMS also have various weaknesses, as the following list of advantages and disadvantages makes clear.
Advantages of a Database Management System:
- easy management of large data sets
- easy, effective access to stored data
- high flexibility
- data integrity and consistency
- access control for users (data security and protection)
- high availability
Disadvantages of a Database Management System:
- relatively expensive initial investment (incl. additional costs for hardware)
- less efficient for special software
- requires qualified employees (database administrators)
- higher vulnerability due to the data being centralised
Examples of DBMS: The most popular Database Management Systems
A range of different database management systems are available. Here you can find the 15 most popular and widely used DBMS:
- Microsoft Access – relational
- Microsoft SQL Server – relational
- MySQL – relational
- Oracle Database – relational
- OrientDB – document-based
- CouchDB – document-based
- Db2 (IBM) – relational
- IMS (IBM) – hierarchical
- IBM Informix – relational
- MariaDB – relational
- Sybase ASE – relational
- MongoDB – document-oriented
- PostgreSQL – object-relational (Mix of relational and object-oriented approaches)
- Firebird – relational
- InterSystems Caché – object-relational (Mix of relational and object-oriented approaches)
- InterSystems IRIS – object-relational (Mix of relational and object-oriented approaches)