Relational databases. The concept of a relational database

The emergence of computer technology in ourModernity marked an information revolution in all spheres of human activity. But in order for all information not to become unnecessary garbage on the global Internet, a database system was invented in which materials are sorted and systematized, making them easy to find and submit to subsequent processing. There are three main types - allocate relational, hierarchical, network databases.

Fundamental models

Returning to the emergence of databases, it is worthto say that this process was quite complicated, it takes its origin along with the development of programmable information processing equipment. Therefore, it is not surprising that the number of their models currently reaches more than 50, but the main of them are considered hierarchical, relational and network, which are still widely used in practice. What do they represent?

The hierarchical database has a treestructure and is composed of data of different levels, between which there are connections. The network database model is a more complex pattern. Its structure is reminiscent of a hierarchical, and the scheme is expanded and improved. The difference between them is that the hereditary data of the hierarchical model can be associated with only one ancestor, and the network can have several. The structure of a relational database is much more complicated. Therefore, it should be disassembled in more detail.

The basic concept of a relational database

This model was developed in the 1970s.Doctor of Science Edgar Codd. It is a logically structured table with fields that describes the data, their relationships with each other, the operations performed on them, and most importantly - the rules that guarantee their integrity. Why is the model called relational? It is based on the relationship (from Lat. Relatio) between the data. There are many definitions of this type of database. Relational tables with information are much easier to organize and inject into processing than in a network or hierarchical model. How to do it? It is enough to know the features, the structure of the model and the properties of relational tables.

The process of modeling and drawing up the basic elements

In order to create your own DBMS, you shoulduse one of the modeling tools, think through what information you need to work with, design tables and relational single and multiple relationships between data, fill in entity cells and establish primary, foreign keys.

Table modeling and relational designdatabases are made using free tools such as Workbench, PhpMyAdmin, Case Studio, dbForge Studio. After a detailed design, you should save the graphically ready relational model and translate it into a ready-made SQL code. At this stage, you can begin working with data sorting, processing and systematization.

Features, structure and terms associated with the relational model

Each source describes its elements in its own way, so for less confusion, I would like to give a little hint:

• relational label = entity;
• Layout = Attributes = Field Name = Entity Column Header;
• entity instance = tuple = record = row of the table;
• attribute value = entity cell = field.

To navigate to the properties of a relational database, you should know which of the basic components it consists of and what they are intended for.

1. EssenceA relational database table can be one, or there can be a whole set of tables that characterize the objects described due to the data stored in them. They have a fixed number of fields and a variable number of entries. The table of the relational database model is composed of rows, attributes, and layout.
2. A record is a variable number of lines displaying data that characterize the object being described. The numbering of entries is made by the system automatically.
3. Attributes - data that demonstrates the description of the columns of the entity.
4. Field. Represents an entity column. Their number is a fixed value set during the creation or modification of a table.

Now, knowing the constituent elements of the table, you can go to the properties of the relational database model:

• The entities of a relational database are two-dimensional. Due to this property, it is easy to make various logical and mathematical operations with them.
• The order of the attribute values ​​and records in a relational table can be arbitrary.
• A column within a single relational table must have its own individual name.
• All data in an entity column has a fixed length and the same type.
• Any record in essence is considered one data element.
• The constituent components of strings are unique. There are no identical rows in the relational entity.

Based on the properties of a relational DBMS, it is clear that the values ​​of attributes must be of the same type, length. Consider the features of attribute values.

Main characteristics of relational database fields

Field names must be unique withinone entity. The types of attributes or fields of relational databases describe which category of data is stored in entity fields. Relational database field must have a fixed size, calculated in characters. The parameters and format of the attribute values ​​determine the manner in which the data is corrected. There is also such a thing as "mask", or "input template". It is intended to define the input data configuration for an attribute value. Certainly when writing the wrong data type in the field should be given an error notification. Also on the field elements are some restrictions - the conditions for checking the accuracy and accuracy of data entry. There is some mandatory attribute value that must be uniquely filled with data. Some attribute strings may be filled with NULL values. It is allowed to enter empty data in the field attributes. Like the error notification, there are values ​​that are automatically filled in by the system - this is the default data. To speed up the search for any data, an indexed field is intended.

Diagram of a two-dimensional relational database table

For a detailed understanding of the control systemmodels using SQL is best to consider the scheme by example. We already know what a relational database is. The entry in each table is one data item. To prevent data redundancy, it is necessary to perform normalization operations.

Basic rules for relational entity normalization

1. The value of the field name for a relational table must be unique, one of a kind (the first normal form is 1NF).

2. For a table that has already been converted to 1NF, the name of any non-identifying column must be dependent on the unique identifier of the table (2NF).

3. For the entire table, which is already in 2NF, each non-identifying field cannot depend on the element of another unrecognized value (3NF entity).

Databases: relational relationships between tables

There are 2 main types of relations of relational tablets:

• «Один-многие».Occurs when one key entry of table No. 1 matches several instances of the second entity. The key icon on one of the ends of the drawn line indicates that the entity is on the “one” side, the other end of the line is often marked with an infinity symbol.

• A “many-many” relationship is formed in the event that an obvious logical interaction with a number of records of another table arises between several rows of one entity.
• If between two entities arisesconcatenation is “one to one”, which means that the key identifier of one table is present in another entity, then one of the tables should be removed, it is redundant. But sometimes programmers deliberately separate two entities solely for security reasons. Therefore, hypothetically, a one-to-one relationship may exist.

The existence of keys in a relational database

Primary and secondary keys definepotential database relationships. Relational relationships of the data model can have only one potential key, this will be the primary key. What is he like? A primary key is an entity column or attribute set that allows you to access data from a specific row. It must be unique, unique, and its fields cannot contain empty values. If the primary key consists of only one attribute, then it is called simple, otherwise it will be a component.

Кроме первичного ключа, существует и внешний (foreign key). Many do not understand what the difference between them. Let us analyze them in more detail using an example. So, there are 2 tables: “Dean's office” and “Students”. The entity “Dean's Office” contains the fields: “Student ID”, “Full Name” and “Group”. The “Students” table has such attribute values ​​as “Full Name”, “Group” and “Average Ball”. Since the student ID cannot be the same for several students, this field will be the primary key. “Full Name” and “Group” from the “Students” table may be the same for several people, they refer to the student ID number from the “Dean's Entity” entity, therefore, they can be used as a foreign key.

Sample Relational Database Model

For clarity, we present a simple example of a relational database model consisting of two entities. There is a table with the name "Dean".

Необходимо провести связи, чтобы получилась full relational database. The entry "IN-41", as well as "IN-72", may be present more than once in the plate "Dean's Office", as well as the last name, first name and patronymic of students in rare cases may coincide, therefore these fields can not be made a primary key. We show the essence of "Students".

Как мы видим, типы полей реляционных баз данных completely different. Both digital and symbolic entries are present. Therefore, in the attribute settings you should specify the values ​​integer, char, vachar, date and others. In the "Dean" table, only the student ID is a unique value. This field can be taken as the primary key. The full name, group, and phone number of the "Students" entity can be taken as a foreign key that refers to the student ID. Connection established. This is an example of a one-to-one model. Hypothetically, one of the tables is superfluous, they can be easily combined into one entity. To prevent student IDs from becoming universally known, the existence of two tables is quite realistic.