The relational data model (RDM) is an abstract mathematical model on the basis of which modern databases are designed. Here's the list of several relational DBMS: Postgres, MySQL, Oracle, etc. Knowing about RDM will help you better understand the concept of building modern databases and competently design information systems. Let's explore the main components of RDM.
Important terms
Let's start with some definitions. The RDM is based on the concept of relation. A relation is essentially a two-dimensional table that represents some entity or relationship.
An entity is an abstraction of some object (like a class in OOP), for example, a student, a car, or a building. A relationship defines how entities are interconnected.
A relation consists of attributes and tuples. An attribute is a column in a table, some property of which has a relation. For example, in relation to a student, there are the following attributes: last name, first name, date of birth, and student number. A tuple is a row in a table, a collection of specific attribute values.
For example, for a student relation with the attributes student number, last name, first name, and date of birth, the tuple can be 88881111, Jackson, John, 01.01.2000. A relation with its attributes can be briefly denoted as follows: Student(student number, last name, first name, date of birth).
student number | last name | first name | date of birth |
|---|---|---|---|
88881111 | Jackson | John | 01.01.2000 |
Restrictions
The following restrictions are imposed on the relations in the relational data model:
There cannot be two relations with the same name in a database.
All attributes within the same relation should have different names.
There should only be one value in each cell.
The order of the rows and columns in the relations is not important.
Entity integrity
Integrity is the conformity of a data model with certain rules. There are two kinds of integrity: entity integrity and referential integrity.
The integrity of the entity means that there cannot be two identical tuples (rows) in the table. As in the real world, we cannot have two absolutely identical objects, for example, there are no identical students, they are all distinct. To maintain the integrity of an entity, the concept of a primary key (PK) is introduced.
A primary key is a field or a set of fields that are used to uniquely identify each record in the table, guaranteeing that no two records in are identical. It's important because it allows easy and efficient access to specific records and also it helps maintain the integrity and consistency of data in the table by preventing duplicate values.
For example, let's take a relation between the two tables, Student and Group:
What if we want to add the following record about a student: 88881188, Madison, Peter, 01.02.2002, 132? It will cause a referential integrity violation because we have no group where the group number equals 132.
Referential integrity
Referential integrity occurs when we have two connected relations. The relationship of the relations is provided using a foreign key (FK). A foreign key is an attribute of a relation (subordinate/dependent) that points to the primary key of another relation (main/master). Referential integrity means that there cannot be a foreign key in the main table that points to a nonexistent tuple in the subordinate table.
For example, there are two relations: student and group. The Group has an attribute number, which is the primary key, and the Student relation has the same attribute, which indicates that the student belongs to a certain group. In student relation, the group number is a foreign key. If in the Student table for some tuple the value of the foreign key points to the tuple of a group that is not in the Group table, then there will be a violation of the referential integrity.
Student | ||||
student number | last name | first name | date of birth | group number |
|---|---|---|---|---|
88881111 | Jackson | John | 01.01.2000 | 123 |
88881122 | Peterson | James | 05.06.2001 | 123 |
88881177 | Depp | John | 15.03.1995 | 199 |
Group | |
group number | date of receipt |
|---|---|
123 | 12.04.2019 |
199 | 11.07.2020 |
FK is used to create a many-to-one (one-to-many) relationship.
Pros and cons
One of the main reasons why RDM is so popular is that there is a query language SQL, with which you can easily interact with data. Another reason is that it is based on a mathematical apparatus based on set theory. For large data volumes, DBMS implementations based on other (usually simpler and less general) data models can be more scalable and performant than relational DBMS. One other disadvantage of the relational data model is that it's different from the object data model and requires a translational layer that increases code complexity.
Conclusion
To sum up, the relational data model is a rather complex and general concept that underlies most modern DBMS. The information that you got from this topic reveals the magic power hidden from you under the hood of an information system or a DBMS. Hopefully, it will help you correctly design the database structure and make informed decisions on its modification.
Read more on this topic in Database Design 101 on Hyperskill Blog.