Premium

Basic concepts about databases

Before we start learning SQL, let's get acquainted with the basic concepts about databases. This will help us to understand the scope of SQL and its environment. execution.

A database is a set of data stored in a structured way

In fact, this is just a repository of some information, nothing more. Databases themselves would be of no interest if there were no database management systems.

A database management system is a set of language and software tools that performs access to data, allows them to be created, modified and deleted, provides data security, etc.

In simpler terms, a DBMS is a system that allows create databases and manipulate information from them.

The simplest scheme for working with a database:

Database operation scheme

DBMS rating

At the moment, the rating of database management systems based on db-engines is as follows:

  • Oracle - relational DBMS
  • MySQL - relational DBMS
  • Microsoft SQL Server - relational DBMS
  • PostgreSQL - relational DBMS
  • MongoDB - document-oriented DBMS
  • Redis - key-value DBMS
  • IBM Db2 - relational DBMS
  • Elasticsearch - search engine
  • Microsoft Access - relational DBMS
  • SQLite - relational DBMS

It can be noted that 7 of the 10 most popular DBMS are relational.

Relational databases

Relational databases are databases that are based on relational model.

Data in relational structures is organized as a set of tables called relations consisting of columns and rows. Each row of the table represents is a set of related values ​​that refer to the same object or entity. Each row in a table can be labeled with a unique identifier, called the primary key, and rows from multiple tables can be linked using foreign keys.

Relational database example

Relational database example

Features of relational databases

  • The data model in relational databases is predefined and strongly typed
  • Data is stored in tables made up of columns and rows
  • Only one value is allowed at the intersection of each column and row
  • Each column is named and has a specific type followed by values ​​from all rows in that column
  • The columns are arranged in a certain order, which is determined when the table is created
  • The table may not have a single row, but must have at least one column
  • Database queries return results in the form of tables

SQL

SQL - structured query language, which is used as an efficient way to save data, searching for their parts, updating, extracting from the database and deleting.

Access to relational DBMS is carried out precisely thanks to SQL. By using it does all the basic database manipulation, some of them are:

  • Retrieve data from the database
  • Insert records into the database
  • Update entries in the database
  • Delete entries from the database
  • Create new databases
  • Create new tables in the database
  • Create stored procedures in the database
  • Create views in the database
  • Set permissions for tables, procedures and views