Databases are relational. The concept of relational database

The advent of computer technology in our time has marked an information revolution in all spheres of human activity. But in order to ensure that all information does not become unnecessary garbage in the global Internet, a database system was invented in which materials are sorted and systematized, as a result of which they are easily found and submitted to subsequent processing. There are three main types - allocate databases relational, hierarchical, network.

Fundamental models

Returning to the origin of databases, it is worth saying that this process was quite complex, it originates with the development of programmable information processing equipment. Therefore it is not surprising that the number of their models at the moment is more than 50, but the main ones are hierarchical, relational and network, which are still widely used in practice. What are they?

The hierarchical database has a tree structure and is compiled from data of different levels, between which there are links. The network model of the database is a more complex template. Its structure resembles a hierarchical structure, and the scheme is expanded and refined. The difference between them is that the hereditary data of the hierarchical model can only be associated with one ancestor, and the network may have several. The structure of the 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 by Dr. Edgar Codd. It is a logically structured table with fields describing the data, their relationships among themselves, the operations performed on them, and most importantly the rules that guarantee their integrity. Why is the model called relational? It is based on relations (from the Latin relatio) between the data. There are many definitions of this type of database. Relational tables with information are much easier to organize and give to processing, rather than in a network or hierarchical model. How can this be done? It is enough to know the features, model structure and properties of relational tables.

The process of modeling and composing the basic elements

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

Modeling tables and designing relational databases is done through free tools, such as Workbench, PhpMyAdmin, Case Studio, dbForge Studio. After detailed design, you should save the graphically ready relational model and translate it into the finished SQL-code. At this stage, you can start 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 a less confusion I would like to give a small clue:

• Relational label = entity;
• Layout = attributes = field names = the title of the columns of the entity;
• Entity instance = tuple = record = row of the label;
• Attribute value = entity cell = field.

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

1. The essence. The table of a relational database can be one, and can be a whole set of tables that characterize the described objects thanks to the data stored in them. They have a fixed number of fields and a variable number of records. The relational database model table is composed of rows, attributes, and layout.
2. A record is a variable number of rows that display data that characterizes the described object. The records are numbered automatically by the system.
3. Attributes are data showing a description of the columns of the entity.
4. Field. Represents an entity column. Their number is a fixed value that is set when the table is created or modified.

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

• The entities of the relational DB are two-dimensional. Due to this property with them, it is easy to do various logical and mathematical operations.
• The order of the values of attributes and records in a relational table can be arbitrary.
• A column within one relational table must have its own individual name.
• All data in the entity column has a fixed length and the same type.
• Any entry in essence is considered as one data item.
• The constituent components of the lines are unique in their kind. There are no identical rows in the relational entity.

Based on the properties of a relational DBMS, it is clear that attribute values must be of the same type, length. Let's consider features of values of attributes.

The main characteristics of relational database fields

Field names must be unique within one entity. The attribute types or relational database fields describe which category data is stored in the entity fields. The relational database field must have a fixed size, counted in characters. The parameters and format of the attribute values determine the manner in which they correct the data. Still there is such concept, as "mask", or "a template of input". It is intended to define the configuration of data entry into the attribute value. It is imperative that an error is reported in the field when an incorrect data type is written. Also, some restrictions are imposed on the field elements - the conditions for checking the accuracy and accuracy of data entry. There is some mandatory value of the attribute, which must be unambiguously filled with data. Some attribute lines can be filled with NULL values. It is allowed to enter blank data in field attributes. Like the error notification, there are values that are filled in by the system automatically - this is the default data. To speed up the search for any data, an indexed field is intended.

A two-dimensional relational database table schema

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

Basic rules for normalizing the relational entity

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

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

3. For the entire table, which is already in the 2NF, each unidentified field can not depend on the element of another unidentified value (3NF entity).

Databases: relational relationships between tables

There are 2 main types of relationships between relational tables:

• «One-many». Occurs when one key entry of Table # 1 matches several instances of the second entity. The key icon at one end of the line indicates that the entity is on the "one" side, the second end of the line is often marked with the symbol of infinity.

• A "multi-lot" relationship is formed when there is an apparent logical interaction between several rows of one entity with a row of records in another table.
• If there is a one-to-one concatenation between two entities, it means that the key identifier of one table is present in the other entity, then one of the tables should be removed, it is superfluous. But sometimes, for security reasons, programmers deliberately divide the two entities. Therefore, hypothetically, a one-to-one relationship can exist.

The existence of keys in a relational database

The primary and secondary keys determine the potential relationship of the database. Relational data model communications can have only one potential key, this is the primary key. What does he represent? The primary key is an entity column or set of attributes, through which you can access the data of a particular row. It must be unique, unique, and its fields can not contain empty values. If the primary key consists of only one attribute, then it is called simple, otherwise it will be a component.

In addition to the primary key, there is also an external key. Many do not understand the difference between them. Let's analyze them in more detail by example. So, there are 2 tables: "Dean's office" and "Students". The essence of the "Deanery" contains fields: "Student ID", "Name" and "Group". The "Students" table has such attribute values as "Name", "Group" and "Average grade". Since the student ID can not be the same for several students, this field will be the primary key. "Name" and "Group" from the table "Students" can be the same for several people, they refer to the student ID number from the entity "Deccan", so they can be used as a foreign key.

An example of a relational database model

For clarity, we give a simple example of a relational database model consisting of two entities. There is a table called "Deccan".

You need to make connections to get a full relational database. The "IN-41" record, like the "IN-72", can be present more than once in the decanter plate, also the surname, first name and patronymic of the students may coincide in rare cases, therefore, these fields can not be made a primary key in any way. Let's show the essence of "Students".

As you can see, the types of relational database fields are completely different. There are both digital and symbolic entries. Therefore, in the attribute settings, you should specify the values of integer, char, vachar, date, and others. In the "Deccan" table, only the student ID is a unique value. This field can be taken as the primary key. The name, group and telephone number from the entity "Students" can be taken as a foreign key referring to the student's ID. Communication 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 ID-numbers of students did not become universally known, it is quite real existence of two tables.