Database. Relational database

What are databases (DB) and why are they needed

Database (DB) is a program that allows you to store and process information in a structured way.
The database is a separate, independent program that is not part of the programming language. You can save any information in the database so that you can access it later.

Usage example

Databases are needed to store information. To get a complete understanding of the need to use a database in modern web programming, you need to answer three questions:

1. What information do we store and why?
2. In what form and how should this information be stored?
3. How and in what way can you get access to this information?

Suppose you decide to make a website where each user can keep a personal diary of observing the weather in their city.
Such a site must have at least one input form with the following fields: city, date, temperature, cloudiness, weather phenomenon, and so on.
Every day, the observer writes down the weather readings in this form so that at some point in the future he will return to the site and see what the weather was like a month or even a year ago.
It follows from this example that the programmer must somehow save the data from the form for later use.

In addition to the usual viewing of a monthly weather diary in the form of a table, you can make a more complex project.
For example, to make an electronic diary something qualitatively different from its paper counterpart, it would be nice to add opportunities for simple analysis: to show which day was the coldest in November or how long the longest series of cloudy days was.
It turns out that the data should not only be stored somehow, but also be able to process and analyze it.
It is for these purposes that databases exist.

How information is stored in the database

The entire storage structure is based on three concepts:

• Database;
• Table;
• Recording.

Database

A database is a high-level concept that refers to the aggregation of a collection of data stored for a single purpose.
If we make a modern website, then all its data will be stored inside one database. For an online weather diary site, you will also need to create a separate database.

table

In relation to the database, the table is a nested object. That is, one database can contain many tables.
An analogy from the real world can be a cabinet (database) inside which there are many boxes (tables).
Tables are used to store one type of data, such as a list of cities, site users, or a library catalog.
A table can be represented as a regular sheet in an Excel table, that is, a collection of rows and columns.
Surely everyone has dealt with spreadsheets (MS Excel) at least once.
By filling in such a table, the user defines the columns, each of which has a heading. Information is stored in strings.
It is the same in the database: when creating a new table, it is necessary to describe what columns it consists of and give them names.

Recording

A record is a row in a spreadsheet.
It is an indivisible entity that is stored in a table. When we save the web form data from the site, we are actually adding a new record to some of the database tables. A record consists of fields (columns) and their values. But the meanings cannot be anything.
When defining a column, the programmer must specify the type of data that will be stored in this column: text, numeric, logical, file, etc. This is necessary so that data of the wrong type is not written to the database in the future.

Putting it all together to understand what keeping a weather diary would look like when a database is involved.

1. Let's create a new database for the site and name it "weather_diary".
2. Let's create a new table in the database named "weather_log" and define the following columns there:
   • City (type: text);
   • Day (type: date);
   • Temperature (type: number);
   • Cloudiness (type: number; from 0 (no clouds) to 4 (full clouds));
   • Whether there was rainfall (type: true or false);
   • Comment (type: text).
3. When saving the form, we will add a new record to the weather_log table, and fill in all the fields with information from the form fields.

Now you can be sure that the observations of our users will not be lost, and they can always be accessed.

Relational database

The English word "relation" can be translated as connection, relationship.
And the definition "relational databases" means that tables in this database can enter into relationships and be in connection with each other.
What are these connections?
For example, one table can refer to another table. This is often required to reduce volume and avoid duplication of information.
In a scenario with a weather diary, the user enters the name of their city. This name is saved along with the weather data.
But you can do it differently:

1. Create a new table named "cities".
2. All cities in Russia are known, so they can all be added to one table.
3. Redesign the form by changing the city input field from a text field to a “select” type field so that the user does not enter the city, but selects it from the list.
4. When saving a weather record, put a link to the corresponding record from the cities table in the city field.

So we will solve two problems at once:

• We will reduce the amount of stored information, since the weather records will no longer contain the name of the city;
• Avoid duplication: all users will choose one of the predefined cities, which will eliminate typos.

There are different types of relationships between tables in a database.
In the example above, a one-to-many relationship was used, since many weather records can correspond to one city, but not vice versa!
There are other types of relationships: one-to-one and many-to-many, but they are used much less often.