Прежде чем приступить к изучению курса баз данных, необходимо дать все основные определения.
Модель данных – абстракция, описывающая структуру (организацию) данных и методы их обработки.
База данных – совокупность данных, хранимых в соответствии со схемой данных, манипулирование которыми выполняют в соответствии с правилами моделирования данных.
Система управления базами данных (СУБД) – совокупность программных и лингвистических средств общего или специального назначения, обеспечивающих управление созданием и использованием баз данных.
Каждый день мы сталкиваемся с огромным количеством информации. Это могут быть формулы по математике, оценки за контрольные, даты по истории, цены на выпечку в буфете. Чтобы легче ориентироваться во всем этом разнообразии данных, каждый из нас стремиться их упорядочить, разложить на простые составляющие. Для этого создаются различные схемы, графики и таблицы. Например, гораздо проще анализировать рост цен на булочки с корицей, построив график зависимости их цены от текущей даты. Или запоминать даты по истории, выписав их в столбик, в виде таблицы.
Такой подход не только делает наглядным работу с информацией, но и структурирует её. Данные, хранящиеся в таблице Microsoft Excel гораздо проще обрабатывать, чем аналогичные данные, сохраненные через запятую в блокноте. Однако при большом объеме данных, использование обычных таблиц (например, в программе Microsoft Excel) становится неэффективным. Для решения подобных задач возможно разработать по определенным правилам базу данных, позволяющую решать задачи хранения и обработки данных более эффективно.
В данном курсе будет рассматриваться объектно-реляционная СУБД PostgreSQL. PostgreSQL является одной из наиболее популярных СУБД в настоящее время. Одной из основных её особенностей является открытый исходный код.
Базы данных строятся на основе некоторой модели данных – абстракции, описывающей структуру данных и методы их обработки. Существуют различные виды моделей данных. Наиболее известными являются иерархическая, сетевая и реляционные модели. Наибольшую распространенность в настоящее время получила реляционная модель данных. Реляционная модель данных основана на понятии отношения (relation). В теории баз данных оно соответствует таблице, состоящей из столбцов (атрибутов) и строк (кортежей).
Среди атрибутов возможно выделить подмножество, обладающее свойствами уникальности и неизбыточности. Такое подмножество называется Потенциальным ключом. Из него возможно выбрать один ключ, который будет идентифицировать конкретную запись в таблице. Такой ключ называется первичным (PK, от Primary Key). Если в качестве ключа выбирается дополнительный атрибут, содержащий порядковый номер записи или некоторое независимое от содержимого значения, то такое ключ называется суррогатным.
В реляционной базе данных таблицы могут быть связаны между собой. Они будут соотноситься как главные и подчиненные. Одной записи главной таблицы может соответствовать множество записей из подчиненной. Связь происходит посредством первичного и внешнего ключей. Внешний ключ – поле подчиненной таблицы, соответствующее первичному ключу главной таблицы.
Рассмотрим простой пример. Предположим, база данных состоит из двух таблиц – Группа, содержащая информацию об учебной группе вуза и Студент – содержащая информацию о студенте, обучающемся в вузе. В качестве первичного ключа выберем Номер группы в таблице Группа – он будет уникальным во всем вузе и Номер студенческого билета, который является уникальным для каждого из студентов. Каждый студент вуза обязательно состоит в одной конкретной группе. В одной группе может быть много студентов. Таким образом, возможно связать между собой эти две таблицы. Таблица Группа будет главной, а Студент – подчиненной. Для связи в таблице Студент создадим поле «Номер группы» и назначим его внешним ключом. Данное поля обязательно должно содержать одно из значений «Номера группы» из таблицы группа – студент не может быть прикреплен к несуществующей группу в вузе.
СУБД PostgreSQL представляет собой клиент-серверную архитектуру. Рабочий сеанс PostgreSQL включает следующие взаимодействующие процессы:
- Главный серверный процесс, управляющий файлами баз данных, принимающий подключения клиентских приложений и выполняющий различные запросы клиентов к базам данных.
- Клиентское приложение пользователя, с помощью которого выполняются операции в базе данных.
Как и в других типичных клиент-серверных приложениях, клиент и сервер могут располагаться на разных компьютерах. В этом случае они взаимодействуют по сети TCP/IP. Важно не забывать это и понимать, что файлы, доступные на клиентском компьютере, могут быть недоступны (или доступны только под другим именем) на компьютере-сервере.
Взаимодействие между клиентским приложением и сервером происходит посредством запросов.
Работу с PostgreSQL возможно осуществить из терминала или с помощью графического приложения. В данном курсе будет рассматриваться инструмент pgAdmin.
PostgreSQL использует метод аутентификации ident.
Аутентификация – процедура проверки подлинности, например, проверка подлинности пользователя путем сравнения введенного им пароля с паролем, сохраненным в базе данных.
Это означает, что выполняется привязка ролей PostgreSQL с соответствующей системной учетной записью ОС Linux. Таким образом, чтобы подключиться к базе данных PostgreSQL из терминала по сети TCP/IP необходимо это выполнять от учетной записи, чье имя совпадает с именем роли в PostgreSQL.
В ходе установки была создана учетная запись пользователя postgres, которая связана с используемой по умолчанию ролью Postgres. Чтобы использовать Postgres, вы можете войти в эту учетную запись.
Для этого в командной строке выполните следующую команду:
sudo -i -u postgresСистема предложит ввести пароль для текущего пользователя. После этого произойдет переключение на учетную запись postgres. Далее возможно подключение к командной строке PostgreSQL:
psqlПосле ввода данной команды будет произведено соединение с сервером PostgreSQL. По умолчанию, произойдет подключение к базе данных, название которой совпадает с именем роли – postgres. Таким образом, в системе определено три разные сущности с одинаковым именем postgres – имена учетной записи Linux, роли в PostgreSQL и базы данных.
Для работы с командной строкой PostgreSQL необходимо использовать специализированные команды. Некоторые из них приведены ниже.
\connect db_name- подключение к базе данных db_name\dt- вывести все таблицы\dt+- вывести все таблицы с описанием\l- вывести список баз данных\dS- вывести системные таблицы\dv- вывести представления\dn- вывести все схемы\du- вывести всех пользователей\d имя_таблицы- вывести информацию о таблице\o- пересылка результатов запроса в файл\di- вывести все индексы\help- вывести справочник SQL\i-запуск команды из внешнего файла, например \i /my/directory/my.sql\?- вывести справочник psql\q- выход из терминала psql
Если выводимые на экран данные будут превышать допустимые размеры терминала, то произойдёт открытие текстового редактора, в котором будет выведена данная информация. Для выхода из него нажмите клавишу q.
После запуска программы pgAdmin вам будет предложено ввести пароль, указанный при установке программы. Если программа была установлена администратором – спросите пароль у преподавателя.
Основное окно программы выглядит следующим образом:
Для подключения к серверу дважды щелкните на название сервера в окошке слева
При удачном подсоединении появляются три новые вкладки
Первая вкладка – Database содержит всю информацию о хранимых базах данных. На текущий момент база данных всего одна – postgres
Вторая вкладка – Login/Group Roles. В ней содержаться все созданные роли и группы, в которые данные роли могут входить. Это предназначено для разделения прав пользователей базы данных, например, между администратором и программистом. По умолчанию создана одна роль – postgres.
Третья вкладка – Tablespaces. В ней располагаются табличные пространства, которые определяют физическое расположение данных. Например, табличные пространства возможно использовать, чтобы расположить архивные данные на медленных носителях, а данные, с которыми идет постоянная работа, на быстрых. При инициализации создается два табличных пространства - pg_default, для хранения данных по умолчанию и pg_global для хранения общих объектов.
Работа с базами данных будет осуществляться с помощью языка программирования SQL. В отличии от знакомых вам императивных языков программирования С, С++, Python, Pascal и т.п. SQL является декларативным языком.
Декларативное программирование (от declare - описание) — парадигма программирования, в которой задаётся спецификация решения задачи, то есть описывается ожидаемый результат, а не способ его получения.
Сравним между собой два подхода. Предположим, что нам необходимо найти в некоторой базе данных, содержащей информацию о студентах вуза, всех молодых людей по имени Александр. Напишем на псевдо-языке программирования решение данной задачи.
| Императивный подход | Декларативный подход |
|---|---|
| Для всех строчек таблицы Студент Если (имя студента = Александр) То выведи информацию о нем на экран |
Выбери строчек таблицы Студент Из (имя студента = Александр) Где выведи информацию о нем на экран |
Как можно увидеть из таблицы, в первом случае мы задаем последовательность действий, которые приведут к желаемому результату. Во втором случае – описываем результат того, что хотим получить.
Приведем более простой пример. Предположим, мы хотим приготовить на обед салат овощей. Императивный подход к решению задачи выглядит следующим образом:
Купить огурцы, помидоры, лук, редис, оливковое масло; Порезать огурцы, помидоры, лук, редис; Полить оливковым маслом.
При декларативном подходе описание будет звучать так: хочу на обед салат из свежих овощей, заправленный оливковым маслом.
Язык SQL включает в себя операторы, инструкции, вычисляемые функции. Операторы SQL делятся на:
- операторы определения данных (Data Definition Language, DDL)
- операторы манипуляции данными (Data Manipulation Language, DML)
- операторы определения доступа к данным (Data Control Language, DCL)
- операторы управления транзакциями (Transaction Control Language, TCL)
Более подробно данные операторы будут рассмотрены в дальнейшем.
Для того, чтобы создать запрос на языке SQL в программе pgAdmin необходимо воспользоваться утилитой Query tool. Для этого перейдите во вкладку Databases – postgres и нажмите на символ 
.
Перед вами откроется командное окно, в которое возможно вводить запросы и запускать их на выполнение.
Обратим внимание на строку с подключением. Она записана в формате «база данных/роль@сервер». Для данного примера база данных называется postgres, пользователь – postgres, сервер – PostgreSQL 15
При работе с базами данных важно разделять права доступа между различными пользователями. В первую очередь это необходимо в целях безопасности. Например, рядовой сотрудник не должен иметь возможности удалить или испортить базу данных. С подобными разграничениями мы сталкивались при работе с электронной системой ОРИОКС. При подключении в качестве студента имеется возможность лишь просматривать оценки, но при авторизации в качестве преподавателя – их выставлять и редактировать.
Авторизация — процесс предоставления пользователю или группе пользователей определенных разрешений, прав доступа и привилегий в компьютерной системе.
В СУБД PostgreSQL разграничение доступа реализуется с помощью понятий роли и привилегий.
Каждому пользователю в СУБД назначается роль, обладающая определенными привилегиями. Например, определенной роли возможно выделить привилегию только на чтение данных из таблиц.
Для создания роли используется оператор CREATE ROLE
CREATE ROLE имя [ [ WITH ] параметр [ ... ] ]
Здесь параметр:
| SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT предел_подключений
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'пароль'
| VALID UNTIL 'дата_время'
| IN ROLE имя_роли [, ...]
| IN GROUP имя_роли [, ...]
| ROLE имя_роли [, ...]
| ADMIN имя_роли [, ...]
| USER имя_роли [, ...]
| SYSID uidПодробно о каждом из параметров возможно прочитать в приложении к документации PostgreSQL.
Создадим роль, название которой будет содержать ваши инициалы и наделим её правами администратора. Для этого скопируем следующий скрипт в рабочую область и запустим его с помощью символа 
. Имя пользователя и пароль должны быть выбраны вами.
CREATE ROLE "SAB" WITH
LOGIN
SUPERUSER
CREATEDB
CONNECTION LIMIT -1
PASSWORD '123456';В теории баз данных существует разделение между понятиями пользователь (user) и роль (role). Пользователь – физическое лицо, которому могут быть выделены особые привилегии – роли. Однако, в последних версиях PostgreSQL данные определения имеют одинаковый смысл
Данный скрипт создает роль SAB, наделяет её привилегиями на вход, создание базы данных и делает её суперпользователем. Созданный пользователь может подключаться неограниченное число раз и имеет пароль для входа 123456.
Обратите внимание, что после успешного выполнения запроса в поле Messages появилась информация об этом.
Перейдем непосредственно к работе с базой данных. Описание предметной области учебной базы данных расположено в приложении.
Первым делом необходимо создать базу данных. Для этого существует команда CREATE DATABASE. Её синтаксис представлен ниже.
CREATE DATABASE имя
[ [ WITH ] [ OWNER [=] имя_пользователя ]
[ TEMPLATE [=] шаблон ]
[ ENCODING [=] кодировка ]
[ LC_COLLATE [=] категория_сортировки ]
[ LC_CTYPE [=] категория_типов_символов ]
[ TABLESPACE [=] табл_пространство ]
[ ALLOW_CONNECTIONS [=] разр_подключения ]
[ CONNECTION LIMIT [=] предел_подключений ]
[ IS_TEMPLATE [=] это_шаблон ] ]Создадим учебную базу данных с информацией о студентах вуза. Для этого выполним запрос:
CREATE DATABASE students;Обновим информацию о базах данных на сервере. Для этого щелкнем по строке Databases в левой колонке правой кнопкой мыши и выберем пункт Refresh.
Раскроем выпадающий список и убедимся, что появилась новая база данных.
Нажмем на созданную базу данных и создадим новый экземпляр Query tool. Обратите внимание, что теперь произведено подключение к базе данных students.
Подключимся к базе данных от имени созданной нами выше роли. Для этого нажмем на строку с соединением и в выпавшем окне выберем строку < New Connection >
Создадим новое соединение
После успешного подключения обратим внимание на то, что имя пользователя изменилось.
Создадим таблицы в базе данных. Для этого откроем скрипт «create_database.sql», нажав на символ 
и запустим его. Если все выполнилось верно, то в поле сообщений появится строчка: «CREATE TABLE Query returned successfully»
Аналогично откроем файл «insert_students.sql» и заполним базу данными.
Данные скрипты будут более подробно рассмотрено в следующих лабораторных работах. Убедимся в том, что все таблицы были созданы. Для этого в выпадающем списке найдем созданные таблицы по пути: «Servers – PostgreSQL 15 – students – Schemas – public – Tables». Нажав правой кнопкой мыши на название таблицы, мы можем выбрать некоторые действия. Рассмотрим некоторые из них.
- Count Rows возвращает число строк в таблице.
- View/Edit Data позволяет вывести на экран содержимое таблицы.
Дважды щелкнув по любой ячейке таблицы, возможно изменить её значение.
После внесенных изменений необходимо зафиксировать их, нажав на символ 
или клавишу F6.
В качестве демонстрации работы приложения, взаимодействующего с учебной базой данных на курсе предложена программа ORIOKS Simulator. Данная программа написана на языке С++ в среде QT Creator. Более подробно о программе вы можете прочитать в приложении.
Используя программу PgAdmin создайте учебную базу данных Students. Подробно о создании базы данных описано здесь.
Подключитесь к созданной учебной базе данных из терминала psql. С помощью основных команд изучите её. Какой размер на диске занимает таблица student?
Используя программу PgAdmin создайте и изучите учебную базу данных Students:
- Ознакомьтесь со схемой данных.
- Ознакомьтесь с содержимым таблиц БД.
- Определите число строк в каждой из таблиц.
Определите, какие таблицы в базе данных Students являются главными, а какие для них подчиненными.
Создайте новую роль «“Ваши инициалы” junior». Выделите ей привилегии на просмотр данных. Подключитесь от её имени к базе данных Students и попробуйте удалить её с помощью запроса:
DROP DATABASE students;Удалось ли вам это сделать?
Запустите программу «ORIOKS Simulator». Обратите внимание на сообщение, которое было выведено в лог файл. Объясните полученную ошибку.
Изучите метод void SqlService::databaseConnect(), расположенный в файле Sources/sql_service/sqlservise.cpp. Внесите изменения в файл Header/sql_service/configuratedatabase.h, чтобы подключиться к созданной ранее базе данных.
Войдите в учебную базу данных из-под разных пользователей – студент, преподаватель, методист. Сравните интерфейсы данных пользователей.
Предположим, что студентка группы ИТД-33 Коровина Мария Георгиевна пересдала экзамен по Колористике на 5 Исправьте любым из возможных способов её оценку.