The Re­la­tion­al Database Model

As we know databases belong to the core com­po­nents of every computer system, since every computer program accesses data during its runtime or else generates its own in­for­ma­tion, all of which must be stored reliably, con­sis­tent­ly, and per­ma­nent­ly. This is done using struc­tured databases (DB), which are managed by so-called database man­age­ment systems (DBMS). Database man­age­ment systems are software ap­pli­ca­tions that interact with end users or other programs and make a subset of the database available to them.

Until now, elec­tron­ic data man­age­ment has been dominated by the re­la­tion­al database model. The most commonly used re­la­tion­al database man­age­ment systems (RDBMS) are in al­pha­bet­i­cal order:

• Db2: with Db2, users have a pro­pri­etary re­la­tion­al database man­age­ment system from IBM under com­mer­cial license.
   
• Microsoft SQL Server: the re­la­tion­al database man­age­ment system from Microsoft is available under a paid Microsoft end user license.
   
• MySQL: MySQL is the most widely used open source RDBMS in the world. Since its ac­qui­si­tion by Oracle, MySQL has been marketed under a dual licensing system. The original developer community continues the project under the name MariaDB.
   
• Post­greSQL: with Post­greSQL users can access a free, object-re­la­tion­al database man­age­ment system (ORDBMS). Further de­vel­op­ment is carried out by an open source community.
   
• Oracle Database: the re­la­tion­al database man­age­ment system created by the company of the same name, Oracle is marketed as pro­pri­etary software for a fee.
   
• SQLite: SQLite is a public domain program library con­tain­ing a re­la­tion­al database man­age­ment system.

All systems mentioned are based on a tabular or­ga­ni­za­tion of in­for­ma­tion. But what is it all about? We will introduce you to the basic prin­ci­ples of re­la­tion­al databases and their design using examples and highlight the dif­fer­ences between this type of database from other models.

A central concept of the re­la­tion­al database model is relation. This goes back to the British math­e­mati­cian and database theorist Edgar F. Codd. According to Codd, a relation rep­re­sents a set of entities with the same prop­er­ties. Each relation consists of a series of data records (so-called “tuples”) whose values are assigned to certain at­trib­ut­es.

The following syntax is used to define which at­trib­ut­es a relation schema comprises of and which data type the values assigned to the at­trib­ut­es cor­re­spond to:

R = (A₁ : Typ₁, A₂ : Typ₂ , … , A_n : Typ_n)

The re­la­tion­al schema (R) comprises at­trib­ut­es A₁ to A_n. Each attribute is assigned a data type (Type_1, Type₂ etc.). This can be il­lus­trat­ed by a concrete example.

The following schema defines the at­trib­ut­es of the “employee” relation:

The sample schema includes the at­trib­ut­es employee ID (e_id), surname, first name, SSN (social security number), street, zip code, and locations, and could be used for the internal ad­min­is­tra­tion of personnel data. Each attribute is assigned a data type (string or integer, for example). This means that there are at­trib­ut­es in this relation that expect character strings as values, and those that only accept integer values.

A relation with the schema just defined could now contain the following tuple:

The following example shows what a database table for the above employee schema can look like:

Table: employees

The example table is used to store personnel data and consists of four data records. Each data record contains in­for­ma­tion on exactly one employee.

The database in re­la­tion­al database systems forms the data basis and is struc­tured in tabular form. Its data structure is defined by the database man­age­ment system, which is also re­spon­si­ble for managing read and write accesses. Users interact with the database man­age­ment system using a database language. Each re­la­tion­al database man­age­ment system supports at least one formal language, which can be used to perform the following database op­er­a­tions.

• Defining data structure: when you define data, a de­scrip­tion of the data structure is stored in the data dic­tio­nary in the database system using metadata. If, for example, a user creates a new table, then a cor­re­spond­ing relation schema is stored in the data dic­tio­nary. The database language vo­cab­u­lary used to define data is called data de­f­i­n­i­tion language (DDL).
  
  
• Define au­tho­riza­tions: each database language provides a syntax that allows per­mis­sions to be assigned or revoked. This is referred to ask the data control language (DCL) – a sub-vo­cab­u­lary of the database language.
  
  
• Define integrity con­di­tions: integrity con­di­tions are required for the state of a database. When integrity con­di­tions are defined, the database ensures that they are met at all times. This is called a con­sis­tent state. A basic integrity condition in re­la­tion­al database systems, for example, is that each data record (tuple) can be uniquely iden­ti­fied.
  
  
• Define trans­ac­tions: if a database is trans­ferred from one con­sis­tent state to another, it is known as a trans­ac­tion. Trans­ac­tions contain a series of in­struc­tions and must always be completed in full. If a trans­ac­tion ter­mi­nates, the database is reset to its initial state (rollback). Each trans­ac­tion begins with the statement to connect to the database. This is followed by commands that initiate the actual data operation and a test step (commit) that ensures the integrity of the database. Op­er­a­tions that endanger integrity are not committed (per­ma­nent­ly written to the database). Finally, the con­nec­tion to the database is closed. The database language vo­cab­u­lary, which data ma­nip­u­la­tion is based on, is known as data ma­nip­u­la­tion language (DML).
  
  
• Define views: a virtual overview of selected data. In the case of a view, the database man­age­ment system creates a virtual table (logical relation) based on physical tables. Users can apply the same database op­er­a­tions to these views as to physical tables. There are different types of views depending on the function of the data view. Views that filer certain rows (selection view) or columns (pro­jec­tion view) from a selected table and views that link different tables together (composite view) are common.

The standard interface for the database op­er­a­tions listed above in the re­la­tion­al database model is the database language SQL (struc­tured query language), which is based on re­la­tion­al algebra.

Database op­er­a­tions like querying, creating, updating, or deleting data are performed using SQL state­ments – a com­bi­na­tion of selected SQL commands. These are se­man­ti­cal­ly based on the English language and so are largely self-ex­plana­to­ry. The following table contains central terms of the re­la­tion­al data model and their equiv­a­lents in SQL ter­mi­nol­o­gy.

A simple query of selected data could be im­ple­ment­ed with SQL according to the following schema, as an example:

First, we use the “SELECT” command to instruct RDBMS to query data. We then define which data we would like to request by spec­i­fy­ing the table and the desired column. In addition, we use “WHERE” to integrate a condition into the SQL statement. We do not want to retrieve all attribute values stored in the column, just the value of a specific data set.

With reference to our example table employees, an SQL statement could look like this:

The SQL statement instructs the RDBMS to retrieve a value from the social security number columb from the “employees” table. As a condition, we have outlined that the value should be taken from the data set for which the attribute value or column e_id cor­re­sponds to the value 3.

The database gives us the result 25 091225 M 463 – Walker McClain’s social security number, who has an ID of 3.

When working with re­la­tion­al databases, users rarely deal with in­di­vid­ual tables. The normal structure is that data is stored in separate tables according to its meaning. This concept un­der­ly­ing the re­la­tion­al database model is as­so­ci­at­ed with the need to link data tables – for example, when data that needs to be queried is stored in different tables.

In principle, all the in­for­ma­tion in a re­la­tion­al database could also be stored in all-en­com­pass­ing table. This would have the advantage of elim­i­nat­ing the need to link database tables, as well as the complex syntax as­so­ci­at­ed with queries across multiple tables. This, however, is the re­la­tion­al database models’ strength. The dis­tri­b­u­tion of in­for­ma­tion to several tables serves to reduce duplicate entries (so-called anomalies) and is called nor­mal­iza­tion. The degree of nor­mal­iza­tion can be de­ter­mined using pre­de­fined normal shapes. Common normal forms for re­la­tion­al database tables are:

1. Nor­mal­form (1NF)

2. Nor­mal­form (2NF)

3. Nor­mal­form (3NF)

Boyce Codd Nor­mal­form (BCNF)

4. Nor­mal­form (4NF)

5. Nor­mal­form (5NF)

Which re­quire­ments apply to the listed normal forms and how to transfer a database from one normal form to another is the subject of our basics of nor­mal­iza­tion.

Re­la­tion­ships between separate database tables are called re­la­tion­ships in the re­la­tion­al database model and are created using keys. Keys link tables together and are the basis for querying or changing data from different tables with one and the same statement.

Database tables such as the “employees” table example allow different ap­proach­es to query in­di­vid­ual values or entire data records. The focus is on so-called keys. In the re­la­tion­al database model, a key is a set of at­trib­ut­es that are suitable for uniquely iden­ti­fy­ing a data record.

In relation to the example table shown above, the following key allows you to uniquely identify a tuple:

A key with the values

Here, keys are suitable for iden­ti­fy­ing the data record for employee Walker McClain without con­tra­dic­tion. Keys like this are called superkeys. However, superkeys are of little im­por­tance in practice. One reason for this is that superkeys often contain more at­trib­ut­es than are necessary for iden­ti­fi­ca­tion. In other words, superkeys are not minimal.

So, re­la­tion­al databases work with the smallest possible subsets of a con­ceiv­able super key which are key can­di­dates. A table can have several key can­di­dates through which data records can be uniquely iden­ti­fied.

The query example in the previous section already showed that the data records in the “employees” table can be iden­ti­fied without con­tra­dic­tion just by the employee ID. Another key candidate in the example table is the social insurance number. However, a key (last name, first name) would not be a suitable key candidate, since this com­bi­na­tion of at­trib­ut­es cannot be uniquely assigned to an employee, since there could be several employees in a company with the name Walker McClain. Iden­ti­fi­ca­tion using this kind of key would therefore not be unique. However, it is not possible for two employees to share the same employee ID or social security number.

The following key candidate can then be de­ter­mined for the example table shown above:

Re­la­tion­al database tables are usually struc­tured so that one of the possible key can­di­dates specifies the data records sequence. This key candidate is called the primary key. In practice, primary keys are usually se­quen­tial IDs. With m_id, our example table also has one of these IDs.

Since keys uniquely identify records in re­la­tion­al database tables, they are ideal for cor­re­lat­ing different tables in a database. To do this, you include the primary key of one table as a foreign key in the other table.

The following table contains data that a company may have entered for its own fleet. The primary key of the table “vehicle” is a con­sec­u­tive vehicle_id.

Table 3: vehicle

To map which employees use which company car, you need to link the car table with the employees table – for example, by in­te­grat­ing the primary key in the car table (the vehicle_id) as a foreign key in the employee table.

Table: coworker

The employee table now shows that the employee Schmidt uses a company car with vehicle_id 3. Employee Cohn drives a vehicle with vehicle_id 2, while Muller and McClain share the car with vehicle_id 1.

If you now want to determine which employee has to have his company car serviced next time, you would have to query both the “Employees” and “Vehicles” tables. Since both tables are related to each other using foreign keys, this can be done with only one query. Database op­er­a­tions that span multiple tables are im­ple­ment­ed in the re­la­tion­al database model using a JOIN.

A JOIN is a database operation that allows several database tables to be queried si­mul­ta­ne­ous­ly. The data from selected tables is combined into a result set and filtered according to user-defined con­di­tions.

Two op­er­a­tions of re­la­tion­al algebra form the math­e­mat­i­cal basis of SQL JOIN: the Cartesian product and selection. Users determine which data of the queried tables is included in the result set by choosing the JOIN type and using a selection condition.

The most important JOIN types include:

• INNER JOIN
• OUTER JOIN
• SELF JOIN

In­de­pen­dent of this, EQUI JOINS and NON EQUI JOINs should be dis­tin­guished.

We have an article about SQL JOIN which explains how SQL JOINs work with re­la­tion­al database tables and what to consider when choosing a JOIN type.

Re­la­tion­al databases based on SQL must be dis­tin­guished from others that do not adhere to the rigid table structure, and pursue al­ter­na­tive ap­proach­es to data struc­tur­ing. Among the most prominent of these are object databases and document-based systems.

At the end of the 1980s, a new database model was in­tro­duced with object databases, which then took up the concept of object-oriented pro­gram­ming and enabled data storage in the form of objects. However, this approach has not really been suc­cess­ful. Instead, concepts of object ori­en­ta­tion have been in­cor­po­rat­ed into the de­vel­op­ment of re­la­tion­al database systems. These results are products with object-re­la­tion­al ex­ten­sions that allow abstract data types to be stored in the re­la­tion­al database model.

With the changes to the Internet that were brought about by web 2.0, the re­la­tion­al database model came under fire at the turn of the mil­len­ni­um. With the framework of a NoSQL movement (short for not only SQL), al­ter­na­tive models like document-oriented databases were developed. The aim of this movement was to develop powerful database concepts for data-intensive ap­pli­ca­tions.

Object databases and document-oriented databases differ from the re­la­tion­al database model, primarily in how the data stock is stored and how stored data can be accessed.

The object-oriented database model provides for the storage of data as objects. Objects are modulated in the same way as object-oriented pro­gram­ming. An object defined as an entity contains:

• The prop­er­ties (at­trib­ut­es) required to describe the entity
• Links (re­la­tion­ships) to other objects
• Functions that allow access to the stored data (methods)
• An object can be defined as a group of data whose interface can be used to access data. Objects are abstract data types.

The object-oriented database man­age­ment system (ODBMS) au­to­mat­i­cal­ly assigns an ID to each object. This makes it possible to uniquely identify the object and address it with methods. This object ID is state-in­de­pen­dent, which is to say it is decoupled from the object values. This makes it possible to give two objects with the same data (the same status) two different IDs. This clearly dif­fer­en­ti­ates the object-oriented database model rom the re­la­tion­al model, in which each tuple can be iden­ti­fied from its data (e.g. by a primary key).

Another char­ac­ter­is­tic of the object-oriented database model is data en­cap­su­la­tion. Stored data can only be accessed using the pre­vi­ous­ly defined methods. The data en­cap­su­lat­ed in the object is then protected against charges through undefined in­ter­faces.

Database struc­tures are defined in the object-oriented database model using a hi­er­ar­chi­cal clas­si­fi­ca­tion system. In object-oriented pro­gram­ming, a class is a set of objects that have the same char­ac­ter­is­tics. Each object class is based on a class de­f­i­n­i­tion. This schema specifies the at­trib­ut­es and methods of all objects in the class and so de­ter­mines how they are created and changed.

Users interact with the ODBMS using an SQL-based query language for object databases: the object query language (OQL). The result of an OQL query is not a result set as with SQL, but a list of those objects that meet the con­di­tions of the OQL statement.

Known im­ple­men­ta­tions of the object-oriented database model are Realm, ZODB, and Perst.

Object-oriented databases were developed as a solution to a problem in ap­pli­ca­tion de­vel­op­ment called object-re­la­tion­al impedance mismatch.

If objects from an object-oriented pro­gram­ming language (e.g. C#, C++, or Java) are to be stored in re­la­tion­al database, in­com­pat­i­bil­i­ties in­evitably occur due to fun­da­men­tal dif­fer­ences between the two pro­gram­ming paradigms.

• Re­la­tion­al databases do not support object-oriented concepts like classes and in­her­i­tance
• State-in­de­pen­dent object iden­ti­fi­ca­tion cannot be im­ple­ment­ed in the re­la­tion­al database model
• The data en­cap­su­la­tion pro­tec­tion mechanism is not available in the re­la­tion­al database model

One approach to avoid these in­com­pat­i­bil­i­ty problems is to do without re­la­tion­al databases and use an object database instead in object-oriented ap­pli­ca­tion pro­gram­ming. However, this is in­evitably ac­com­pa­nied by the dis­ad­van­tage that data en­cap­su­lat­ed in objects is not available in­de­pen­dent­ly of the as­so­ci­at­ed ap­pli­ca­tion. Added to this is the low dis­tri­b­u­tion of object databases. Most tools and in­ter­faces for analyzing data sets are still designed for re­la­tion­al databases and do not support the object-oriented data model.

However, ap­pli­ca­tion de­vel­op­ers who do not want to give up the ad­van­tages of re­la­tion­al data storage can com­pen­sate for in­com­pat­i­bil­i­ties using object-re­la­tion­al mappers (O/R mappers). Object-re­la­tion­al mapping func­tion­al­i­ties (ORM) are im­ple­ment­ed in libraries. These create an ab­strac­tion layer between the object-oriented ap­pli­ca­tion and the data stored in tables.

Numerous re­la­tion­al database man­u­fac­tur­ers also equip their products with functions that com­pen­sate for in­com­pat­i­bil­i­ties in object-oriented pro­gram­ming. Database systems of this kind are known as “object-re­la­tion­al.”

An object-re­la­tion­al database system is a re­la­tion­al database system that has been enhanced with object ori­en­ta­tion. The proven prin­ci­ples of the re­la­tion­al database model are extended to abstract data types such as objects.

To enable the man­age­ment of abstract data types, object-re­la­tion­al databases extend the re­la­tion­al database model by:

• Complex, user-defined data types
• Type con­struc­tors
• Functions and methods

While re­la­tion­al databases are es­sen­tial­ly limited to al­phanu­mer­ic data types, user-defined data types can also be used to manage complex mul­ti­me­dia files. Type con­struc­tors allow you to derive new data types from existing basic types. Since the SQL database language does not allow functions to be generated, object-re­la­tion­al database systems must provide ex­ten­sions that can be used to define access and pro­cess­ing functions for complex data types.

At the turn of the mil­len­ni­um, object-re­la­tion­al ex­ten­sions like struc­tured types were included in newer versions of the SQL standard. However, not all DBMSs support these. Well-known database systems that provide ex­ten­sions are IBM Db2, Oracle Database, and Microsoft SQL Server.

While re­la­tion­al databases store data in database tables, the document-oriented database model is based on a het­ero­ge­neous database of in­di­vid­ual documents. These can be struc­tured documents like JSON, YAML, or XML files, or un­struc­tured files like binary large objects (BLOBs) – such as mage, video, or audio files.

If struc­tured documents are available, data is stored in the form of key/value pairs. A concrete value is assigned to each key. In this context, the key term is used syn­ony­mous­ly with the term attribute and has nothing to do with the keys in the re­la­tion­al database system. Values can be any in­for­ma­tion. Lists and arrays with nested data are also possible values.

For example, a document in JSON format (JavaScript Object Notation), that is used to store employee data could look like this:

Several documents can be grouped into col­lec­tions. For example, the employee document displayed could be “Employee” together with other parts of the col­lec­tion.

Queries are im­ple­ment­ed using functions – for example, through JavaScript. Database man­age­ment systems that are document-oriented also assign a unique ID to each document. However, unlike in the re­la­tion­al database model, there is no database schema covering the entire database. Documents in a document-based database do not have to comply with a normal form, nor are there pre­de­fined struc­tur­al features that must apply to all documents. In principle, each document can be struc­tured dif­fer­ent­ly. However, we recommend that you create documents in a schema that cor­re­sponds to the ap­pli­ca­tion during ap­pli­ca­tion de­vel­op­ment to create the pre­req­ui­sites for specific queries.

Re­la­tion­ships such as linking database tables in the re­la­tion­al database model cannot be im­ple­ment­ed with document-oriented databases. Although it is possible to manually enter the document ID as a reference in another document, document-oriented database man­age­ment systems do not offer JOINs. You would have to program the cor­re­spond­ing query options yourself.

Document-oriented database systems are par­tic­u­lar­ly suitable for pro­cess­ing large amounts of data with a het­ero­ge­neous structure and a low net­work­ing re­quire­ment. This model of data storage is then par­tic­u­lar­ly useful for big data scenarios.

Re­la­tion­al database systems ensure that the con­di­tions specified in the table de­f­i­n­i­tions are fulfilled at all times. This leads to com­par­a­tive­ly slow write speeds when pro­cess­ing large amounts of data. NoSQL database systems do not have such strict data con­sis­ten­cy re­quire­ments and are more suitable for large ar­chi­tec­tures in which many database instances operate in parallel.

Web ap­pli­ca­tions are also in­creas­ing­ly using document-oriented databases. However, if strong net­work­ing is required, document based data storage is more complex. In this case, users should use re­la­tion­al database systems.

Examples of document-oriented databases are BaseX, CouchDB, eXist, MongoDB, and RavenDB.

There are plenty of good reasons why re­la­tion­al databases have become the standard in elec­tron­ic data pro­cess­ing. The following aspects highlight the benefits:

• Simple data model: re­la­tion­al databases are based on a data model that is com­par­a­tive­ly easy to implement and manage. Plenty of in­for­ma­tion – like customer data, order lists, or account movements – that companies may want to store long-term can be rep­re­sent­ed easily using the table structure that the re­la­tion­al database model is based on.
  
  
• Low data re­dun­dan­cy: the re­la­tion­al database model specifies precisely defined rules for re­dun­dan­cy avoidance with the various normal forms. If nor­mal­iza­tion re­quire­ments are con­sis­tent­ly im­ple­ment­ed, re­la­tion­al database systems more or less enable re­dun­dan­cy-free data storage. This sim­pli­fies the main­te­nance and servicing of data, since changes only have to be made in one place.
  
  
• High data con­sis­ten­cy: nor­mal­ized re­la­tion­al databases enable con­sis­tent data storage and so con­tribute to data con­sis­ten­cy. Re­la­tion­al database systems also offer functions that allow integrity con­di­tions to be defined and checked. Trans­ac­tions that endanger data con­sis­ten­cy are excluded.
  
  
• Quantity-oriented data pro­cess­ing: the re­la­tion­al database system is based on quantity-oriented data pro­cess­ing whereby each entity is broken down into atomic values. This makes it possible to link different entities through their content, as well as complex database queries like JOINs.
  
  
• Uniform query language: for queries con­cern­ing re­la­tion­al databases, the data base language SQL, stan­dard­ized by a committee from the ISO and IEC, was developed. The purpose of this stan­dard­iza­tion is that ap­pli­ca­tions can be developed and executed mostly in­de­pen­dent­ly from the un­der­ly­ing database man­age­ment system. However, support for SQL still varies greatly depending on the DBMS.

Depending on what situation you are using a re­la­tion­al database for, ad­van­tages like the simple table-based data model and the dis­tri­b­u­tion of data to several linked tables can also be in­ter­pret­ed as a dis­ad­van­tage. Fur­ther­more, central features of the re­la­tion­al data model are difficult to reconcile with modern re­quire­ments for ap­pli­ca­tion pro­gram­ming (like object ori­en­ta­tion, mul­ti­me­dia, and big data).

• Tabular data display: not all data types can be com­pressed into the kind of rigid schema required by in­ter­con­nect­ed two-di­men­sion­al tables (impedance mismatch). Abstract data types and un­struc­tured data that occur in con­nec­tion with mul­ti­me­dia ap­pli­ca­tions and big data solutions cannot be mapped in the re­la­tion­al database model.
  
  
• No hi­er­ar­chi­cal database schema: unlike object databases, re­la­tion­al databases offer no option to implement database schemata with hi­er­ar­chi­cal­ly struc­tured classes. Concepts like sub­or­di­nate entities that inherit prop­er­ties from higher-level entities cannot be im­ple­ment­ed with them. For example, you cannot create sub-tuples with them. All tuples in a re­la­tion­al database are on the same hierarchy level.
  
  
• Data seg­men­ta­tion: the basic principle of a re­la­tion­al database systems of dividing in­for­ma­tion into separate tables (nor­mal­iza­tion) in­evitably leads to the data being segmented. Related data is not nec­es­sar­i­ly stored together. This database design results in complex queries across multiple tables at ap­pli­ca­tion level. The resulting high number of queried segments usually also has a negative impact on per­for­mance.
  
  
• Poorer per­for­mance compared to NoSQL databases: the re­la­tion­al database model places high demands on data con­sis­ten­cy, at the expense of write speed for trans­ac­tions.

The re­la­tion­al database model is clear, math­e­mat­i­cal­ly sound, and has proven itself in practical use for more than 40 years. Despite this, data storage in struc­tured tables is not always up to the re­quire­ments of modern in­for­ma­tion tech­nol­o­gy.

Par­tic­u­lar­ly when it comes to the man­age­ment of large amounts of data in the context of big data analyses and storing abstract data types, classic re­la­tion­al systems get pushed to their limits. This is where spe­cial­ized systems like object databases or concepts developed within the framework of the NoSQL movement score points. However, the re­la­tion­al database model cannot be com­plete­ly written off.

In business areas where trans­ac­tion data pro­cess­ing is at the fore­ground, re­la­tion­al databases in par­tic­u­lar offer numerous ad­van­tages. Data on customer campaigns or marketing measures can be ideally mapped in tabular systems. Users also benefit from syntax that enables complex queries despite being rel­a­tive­ly simple.