A database management system is software that provides interaction of various external programs with data and additional services (logging, recovery, backup, etc.), including SQL. It is a software layer between data and external programs that work with it. In this part, we will answer the questions of what is SQL, what is SQL server, and create the first program to interact with a DBMS.
Types of databases
There are several types of DBMSs according to the way they organize data storage:
Hierarchical. Data is organized as a tree structure. An example is a file system that starts from the root of the disk and then grows with branches of different types of files and folders of varying degrees of nesting.
Networking. A modification of the hierarchical one, each node can have more than one parent.
Object-oriented. Data is organized in the form of classes/objects with their attributes and principles of interaction in accordance with the OOP.
Relational. Data of this type of DBMS is organized in tables. Tables can be linked to each other, and the information in them is structured.
SQL
External programs form queries to the DBMS using the Structured Query Language. What is SQL and how does it differ from other programming languages? One of the features of SQL is its declarative nature. That is, SQL is a declarative language. This means that when we enter commands, i.e., create queries to the SQL server, we describe what we want to get, not how we want to get it. By sending the server the SELECT * FROM CUSTOMER query (approximate translation from SQL into Russian: “make a selection from the COSTUMER table, the selection consists of all rows in the table”), we will get the data for all users. It doesn’t matter how and from where the server downloads and generates the data we are interested in. The main thing is to formulate the query correctly.
What is a SQL server and how does it work? Interaction with a DBMS is based on the client-server principle. Some external program sends a query in the form of SQL statements and commands, and the DBMS processes it and sends a response. For the sake of simplicity, let’s assume that SQL Server = DBMS.
If you know how to drive a car of one brand, you will most likely be able to drive others without any problems. The basics of driving are the same everywhere, except for small details. It is the same for SQL servers from different manufacturers – each of them has its own version of SQL, but it meets the specified standards (SQL92, SQL2003…). We will use SQL92 operators and commands. The main SQL operators are divided into the following groups:
- Data Definition Language (DDL) – data definition. Creating a database structure and its objects;
- Data Manipulation Language (DML) – the actual interaction with data: inserting, deleting, modifying and reading;
- Transaction Control Language (TCL) – transaction management;
- Data Control Language (DCL) – managing access rights to data and database structures.
In the 80s of the last century, personal computers such as PC XT/AT conquered the market. This was largely due to the modularity of their design. This means that a user could simply change one or another component of his or her computer (processor, video card, disks, etc.). This remarkable feature has been preserved to this day: we change the video card and update the driver (sometimes it updates itself automatically). Most often, nothing bad happens with such manipulations, and existing programs will continue to work with the system without reinstallation. The same is true for working in Java with a DBMS. To standardize work with SQL servers, you can interact with it through a single point – JDBC (Java DataBase Connectivity). It is an implementation of the java.sql package for working with DBMSs. Manufacturers of all popular SQL servers provide JDBC drivers for them. Consider the diagram below. The program uses class instances from java.sql . Then we provide the necessary commands to get/modify the data. Next, java.sql interacts with the DBMS through the jdbc driver and returns the finished result.