Introduction to databases

History of databases: database and DBMS

A long time ago in one American country, in a large IT company, the task of storing very large amounts of data once arose. What's so hard about that, you ask? After all, large companies have money, which means you can buy more hard drives - and that's it.

Great idea, but as they say, the devil is in the details. Large amounts of data had to be not only stored, but also changed, and various samples were made from them. Moreover, you need to select different data from different places, and also do it quickly and beautifully.

In general, everything is as usual: customers wanted something that does not exist, and the decision to buy hard drives did not work this time.

Therefore, the programmers of this IT company knocked out a bigger budget for themselves, carried out research work, and when the budget was over, they presented a presentation where it was proposed to store data in the form of a Database. In a database, all data is stored in tables, and each table is stored in a separate file. Everything ingenious is simple and everything works.

But such a presentation did not suit the customers, and nit-picking continued:

• Where's the money, Lebowski?
• Why is the Database better than the good old storage of data in the form of a set of files?
• We need a really good solution, not that's it!
• And it took the entire budget?

There was dead silence in the presentation room. However, unexpectedly for everyone, the situation was saved by a junior developer, who said that a special program was attached to the database - a DBMS (Database Management System), which can:

• Create, modify and delete databases
• Make changes to databases, namely: add new records, delete old ones and change them
• And also super fast to perform various selections of any data

And programmers are even finishing designing a special super-language for filtering data - SQL . And they say that with the help of this magical SQL language, any manager can easily filter and get any data from the database.

The programmers just haven't finished designing the language yet , so there's not a word about SQL and DBMS in the presentation.

To everyone's joy, the customers were impressed by the explanation they heard, expressed several conflicting ideas about what this new language should be like, and even allocated a budget for its refinement.

Managers and the Technical Director began to congratulate each other on the successful completion of the project, and immediately went to celebrate this event, not fully realizing that the junior programmer in this whole story is the most important person ...

Popular DBMS and MySQL

At the moment (summer 2022) there are hundreds of popular DBMS, so it will be useful for you to learn about some of them. What should I start with…

On the one hand, the enterprise DBMS market has been around for decades. Therefore, it has both recognized leaders and promising newcomers. On the other hand, business requirements are constantly growing, so approaches to building IT infrastructures of companies are constantly changing.

Now you won’t surprise anyone with a database from a cluster of a couple of thousand servers with horizontal and vertical sharding, distributed transactions, as well as an IT department of a couple of dozen system administrators who go and change server hard drives according to a predetermined schedule.

In general, if you work for a large company, then most likely they pay a lot of money for their databases, and use something like this:

Oracle Database

Oracle released its first database back in 1979 (43 years ago). She immediately bore the name Oracle 2.0 to convince customers that the product is not new, but time-tested.

The current version of the database is Oracle 21 c , where the letter c is from the word cloud , which, as it were, hints that Oracle is keeping up with the times and its solutions are perfectly compatible with all cloud technologies.

Microsoft SQL Server

Microsoft makes a lot of money selling not Windows, but server solutions for medium and large businesses. Microsoft Office, Microsoft SharePoint, Microsoft Server, etc. And, of course, all this requires a good database. Therefore, a lot of companies, using Microsoft products and solutions, are forced to use its database.

Microsoft's DBMS is simply called SQL Server , so if you hear the question “Have you worked with SQL Server?” at an interview, then know that this is not an abstract DBMS, but Microsoft SQL Server.

PostgreSQL

This is a good free DBMS, which is often used not only by small and medium-sized companies, but also by large corporations. This is one of the databases that Amazon AWS offers as a DB-as-service.

MySQL

MySQL is a very popular free and open source database. It is well documented, works fast, and supports a wide range of features. To date, the 8th version of this DBMS is available.

In 2008, it was bought by Sun, which was bought by Oracle in 2009. And it only benefited her - the product is constantly developing and improving.

We will learn how to work with databases using MySQL Community Server 8.0 as an example .

Tables in a database: columns and columns

Different DBMS can store data in different formats, so we will take the simplest and most classic option - the MySQL DBMS.

And immediately the question is: how to store, for example, any accounting documents? The easiest option is to store each document as an Excel file. Then all related documents can be stored in one folder. This is how MySQL works.

Another analogy: in the Java language, you have classes and packages in your project , but at the same time, classes and packages are represented on disk as files and folders . Something similar exists in MySQL.

MySQL data is stored in the form of tables , which are combined into databases , but at the same time, tables and the database are presented on disk as files and folders . The database is a folder, and the files in it are tables.

Each table consists of columns and rows . Example:

Important! Each column has a name and a data type , so all cells in the same column must store values ​​of the same type .

An analogy can be drawn between a table in MySQL and a class in Java. A table is a class, a table, like a class, has a unique name. Table columns are class fields; columns, like fields, have a unique name and type. Table rows are class instances in Java.

A class can exist without objects, and a table can exist without rows. In Java, you can create a new object, and in MySQL, you can add a new row to a table. You can change the field values ​​of an object, and in MySQL you can change the values ​​in a row.

It is even more correct to say that a table is not a class, but a collection of objects of a certain class. If in MySQL we have an Employee table with data on employees, then in Java we would use the ArrayList collection.

And, of course, the question immediately arises: how to get certain data from this collection?