4.1. Basic concepts of relational databases

The basic concepts of relational databases are data type, domain, attribute, tuple, primary key, and relationship.

To begin, let us show the meaning of these concepts using the example of the EMPLOYEES relationship, which contains information about the employees of a certain organization:

4.1.1. Data type

The concept of data type in the relational data model is fully adequate to the concept of data type in programming languages. Typically, modern relational databases allow storing character, numeric data, bit strings, specialized numeric data (such as "money"), as well as special "temporal" data (date, time, time interval). The approach to extending the capabilities of relational systems with abstract data types is developing quite actively (for example, the systems of the Ingres / Postgres family possess the corresponding capabilities). In our example, we are dealing with three types of data: character strings, integers, and money.

4.1.2. Domain

The concept of a domain is more specific to databases, although it has some similarities with subtypes in some programming languages. In its most general form, a domain is defined by specifying some basic data type, to which the domain elements belong, and an arbitrary logical expression applied to the data type element. If the evaluation of this logical expression gives the result "true", then the data element is a domain element.

The most correct intuitive interpretation of the concept of a domain is the understanding of a domain as an acceptable potential set of values ​​of a given type. For example, the domain "Names" in our example is defined on the basic type of character strings, but only those strings that can represent a name can be included in the number of its values ​​(in particular, such strings cannot begin with a soft sign).

The semantic load of the domain concept should also be noted: the data are considered comparable only when they belong to the same domain. In our example, the values ​​of the domains "Pass numbers" and "Group numbers" refer to the type of integers, but are not comparable. Note that in most relational DBMS the concept of domain is not used, although in Oracle V.7 it is already supported.

4.1.3. Relationship scheme, database scheme

A relationship schema is a named set of pairs {attribute name, domain name (or type, if the concept of a domain is not supported)}. The degree or "arity" of the relational scheme is the power of this set. The degree of the relation EMPLOYEES is equal to four, that is, it is 4-ary. If all attributes of the same relationship are defined on different domains, it is sensible to use the names of the corresponding domains to name the attributes (remembering, of course, that this is just a convenient way of naming and does not eliminate the differences between the concepts of domain and attribute).

A database schema (in a structural sense) is a set of named relationship schemas.

4.1.4. Tuple, attitude

A tuple corresponding to a given relationship schema is a set of pairs {attribute name, value} that contains one entry for each attribute name belonging to the relationship schema. "Value" is a valid domain value for this attribute (or data type, if the concept of a domain is not supported). Thus, the degree or "arity" of the tuple, i.e. the number of elements in it coincides with the "arity" of the corresponding relational scheme. Simply put, a tuple is a set of named values ​​of a given type.

A relation is a set of tuples corresponding to a single relational pattern. Sometimes, not to be confused, they say "relationship-scheme" and "relationship-instance", sometimes the relationship scheme is called the relationship header, and the relation as a set of tuples is the relationship body. In fact, the concept of a relationship scheme is closest to the concept of a structured data type in programming languages. It would be quite logical to allow the relationship scheme to be separately defined, and then one or more relations with this scheme.

However, this is not accepted in relational databases. The name of the relation schema in such databases always coincides with the name of the corresponding instance relation. In classic relational databases, after defining a database schema, only instance relationships change. They can appear new and deleted or modified existing tuples. However, in many implementations, it is allowed to change the database schema: identify new and change existing relational schemes. This is called evolution of the database schema .

The usual everyday representation of a relationship is a table, the heading of which is a relationship diagram, and the strings are tuples of an instance-relationship; in this case, the attribute names name the columns of this table. Therefore, sometimes they say "table column", meaning "relationship attribute". When we turn to the practical issues of the organization of relational databases and management tools, we will use this everyday terminology. This terminology is followed in most commercial relational DBMSs.

A relational database is a set of relations whose names coincide with the names of the relationship schemes in the database schema.

As you can see, the basic structural concepts of the relational data model (except for the domain concept) have a very simple intuitive interpretation, although in the theory of relational databases all of them are defined absolutely formally and precisely.