Основы SQL. Тема 1.1: История возникновения

05 апреля 2022

Тема 1.1
Основы SQL:
История возникновения

Всем добрый день! Сегодня мы начинаем цикл занятий по языку программирования SQL. Выясним, что такое базы и модели данных, как их создавать и, разумеется, как ими оперировать. На этом занятии мы с вами разберемся: откуда появились базы данных? И почему они появились такими, какими мы их видим и используем в настоящий момент?

Ссылка на вебинар внизу страницы

Содержание:

Развитие систем хранения данных.
Эволюция моделей данных.
Система управления базами данных (СУБД).
Реляционная структура данных.
Операции реляционной алгебры.
SQL.
Целостность данных.

Вычислительный процесс

Совокупность взаимосвязанных действий, преобразующих входящие данные в исходящие.

Соотношение сложности представления обрабатываемых данных и алгоритма вычислений определяет два класса задач:

вычислительные задачи;
Y=X2+5X
X и Y — определенные числа, которые являются здесь элементарными единицами данных (элементами данных)
задачи обработки данных
Начисление заработной платы.
Начисленная сумма = (кол-во отработанных дней в месяц * Оклад) / кол-во рабочих дней в месяце

Развитие систем хранения данных

Этапы развития систем хранения данных

История развития баз данных насчитывает более 50 лет.

0-й этап.
Файловые системы.
1958 г.
1-й этап.
Монолитная архитектура.
1960 — 1980 гг.
2-й этап.
Архитектура «файл-сервер».
1975 — 1995 гг.
3-й этап.
Трехуровневая архитектура (клиент-сервер).
1985 г. — н.в.
4-й этап.
Облачные технологии.
2012 г. — н.в.

0-й этап. Файловые системы.

Фа́йловая систе́ма (file system) — порядок, определяющий способ организации, хранения и именования данных на носителях информации

Недостатки применения файловых систем:

Избыточность данных.
Несогласованность данных.
Зависимость структур данных и прикладных программ.

Магнитные диски впервые были реализованы в 1956 году в исследовательской лаборатории корпорации IBM, расположенной в Сан-Хосе (Калифорния), где был выпущен серийный дисковый накопитель
IBM 350 — первое устройство с подвижной головкой для чтения и записи.

Первая развитая файловая система была разработана фирмой IBM для ее серии System/360 в 1964 году

В 60-х годах прошлого столетия появилась необходимость в надежной модели хранения и обработки данных. В первую очередь эти данные генерировались банками и финансовыми организациями. В то время не существовало единых стандартов работы с данными и моделями, да и работа как таковая заключалась в ручном упорядочении и организации хранящейся информации.

У банков худо-бедно получалось записывать информацию о транзакциях в виде файлов в заранее подготовленную структуру. У каждой организации было собственное понимание того, как все это должно выглядеть и работать. Не было таких понятий, как консистентность (англ. data consistency), целостности данных (англ. data integrity). В файлах часто встречались дубликаты данных клиентов и их транзакций, которые необходимо было каким-то образом уточнять и приводить в порядок, делалось это в основном вручную. В целом все проблемы того времени в отношении работы с данными можно разделить на несколько основных видов:

представление структуры в каждом файле было различным;
необходимо было согласовывать данные в разных файлах, чтобы обеспечить непротиворечивость информации;
сложность разработки и поддержки приложений, работающих с конкретными данными, и их обновления при изменении структуры файла.

1-й этап. Монолитная архитектура

В 1968 году компания IBM разработала первую промышленную СУБД: IBM IMS (Information Management System)
В задачу IMS входила обработка спецификации изделия для ракеты Сатурн-5 и кораблей Аполлон.
В качестве носителя информации использовалась магнитная лента, а в качестве структуры данных — иерархическая модель.

Получаемые в результате библиотеки, реализующие дополнительные индивидуальные средства управления данными, являлись существенной частью информационных систем и практически повторялись от одной системы к другой.

Стремление выделить и обобщить общую часть информационных систем, ответственную за управление сложно структурированными данными, вылилось в реализацию новых программных систем, названных впоследствии Системами Управления Базами Данных (СУБД), а сами хранилища информации, которые работали под управлением данных систем, назывались базами данных.

2-й этап. Архитектура «файл-сервер»

Файл-сервер только извлекает данные из файла (файлов) базы данных и передает их клиенту для дальнейшей обработки.

Особенности:

Значительный сетевой трафик.
Неважно, сколько записей из базы данных нужны клиенту — файлы базы данных передаются в самом общем случае целиком.
Безопасность не может быть обеспечена на должном уровне.

Первый персональный компьютер модели 5100 производства фирмы IBM выпущен в 1975 году. Он был более компактным, чем мэйнфреймы, имел встроенные монитор, клавиатуру и накопитель на магнитной ленте, и предназначался для решения научно-инженерных задач.

Первым массовым персональным компьютером производства фирмы IBM, выпущенным в 1981 году, стал IBM PC модели 5150, положивший начало семейству наиболее распространённых современных персональных компьютеров.

Спрос на развитые удобные программы обработки данных заставлял поставщиков программного обеспечения поставлять все новые системы, которые принято называть настольными (desktop) СУБД.

Наличие на рынке большого числа СУБД, выполняющих сходные функции, потребовало разработки методов экспорта, импорта и открытых форматов хранения данных. Так появились первые коммерческие СУБД с реляционной моделью данных.

Основанные на реляционном подходе СУБД для персональных компьютеров принято считать системами второго поколения.

В 80-х годах были созданы различные коммерческие реляционные СУБД — например, DB2 или SQL/DS корпорации IBM, Oracle и др. Большинство СУБД имели развитый и удобный пользовательский интерфейс, предлагающий интерактивный режим работы с БД, как в рамках описания БД, так и в рамках проектирования запросов.

3-й этап. Трехуровневая архитектура (клиент-сервер)

Основной объем обработки данных сосредоточен на сервере.
Клиент использует запросы SQL.

Стандарты:

SQL-89;
SQL-92;
SQL-99;
SQL:2003;
SQL:2006;
SQL:2008.

Третий этап развития СУБД связывают с распространением локальных и глобальных компьютерных сетей. На сегодняшний день третий этап можно считать незавершённым.

SQL-89, SQL-92, SQL-99, SQL:2003, SQL:2006, SQL:2008

Информационные системы с клиент-серверной архитектурой позволяют избежать проблем файл-серверных приложений. При такой архитектуре сервер базы данных, расположенный на компьютере-сервере, обеспечивает выполнение основного объема обработки данных. Клиентское приложение формирует запросы к серверу базы данных, как правило, в виде инструкций языка SQL. Сервер извлекает из базы запрошенные данные и передает на компьютер клиента. Главное достоинство такого подхода — значительно меньший объем передаваемых данных.

Большинство конфигураций информационных систем типа «клиент-сервер» использует двухуровневую модель, в которой клиент обращается к серверу.

Безопасность же современных серверов баз данных, организованная в нескольких направлениях: с помощью самой операционной системы; с использованием схем, имен входов, ролей, шифрования базы данных и т. д.; путем ограничения доступа пользователей через представления, заслуживает похвалы. В настоящее время архитектура «клиент-сервер» широко признана и находит применение для организации работы приложений как для рабочих групп, так и для информационных систем масштаба предприятия.

4-й этап. Облачные технологии

Database as a Service (Daas) — концепция предоставления масштабируемой базы данных как сервиса.

Клиент приобретает возможность хранения/получения информации из данной БД без необходимости её настройки, администрирования и привлечения дополнительных ресурсов, требуемых для обеспечения возможности масштабируемости самой БД.

Эволюция моделей данных

Модель данных — это абстрактное, самодостаточное, логическое определение объектов, операторов и прочих элементов, в совокупности составляющих абстрактную машину доступа к данным, с которой взаимодействует пользователь.

Теория СУБД включает, по меньшей мере, три аспекта:

аспект структуры: методы описания типов и логических структур данных в базе данных;
аспект обработки: методы манипулирования данными;
аспект целостности: методы описания и поддержки целостности базы данных.

Другими словами:

cтруктурный аспект — данные в базе воспринимаются пользователем как таблицы (и никак иначе);
аспект целостности — таблицы отвечают определенным условиям целостности;
аспект обработки — в распоряжении пользователя есть операторы манипулирования данными (сокращения, проекции, объединения), которые генерируют новые таблицы на основании уже имеющихся.

Иерархическая модель данных

Модель данных, где используется представление базы данных в виде древовидной (иерархической) структуры, состоящей из объектов (данных) различных уровней.

запись может иметь только одного «предка»;
возможны отношения только «один к одному» или «один ко многим»;
невозможность реализации отношения «многие ко многим»;
проблема дублирования;
сложность в поддержке.

Иерархическая модель, в которой данные были организованы в виде древовидной структуры. Иерархическая модель была востребованной, но не гибкой. В ней каждая запись могла иметь только одного «предка», даже если отдельные записи могли иметь несколько «потомков». Из-за этого базы данных представляли только отношения «один к одному» или «один ко многим». Невозможность реализации отношения «многие ко многим» могла привести к проблемам при работе с данными и усложняла модель. Более того, вопросы консистентности данных и отсутствия дублирования информации здесь вообще не стояли. Первая иерархическая СУБД называлась IMS от IBM.

Сетевая модель данных

Модель данных, являющаяся расширением иерархического подхода, — у потомка может быть любое число предков.

Достоинства:

эффективность реализации по памяти.

Недостатки:

высокая сложность схемы БД;
«жесткость» схемы БД;
модель не является полностью независимой от приложения.

Реляционная модель данных

Реляционная модель использует таблицы и базируется на двух утверждениях:

база данных должна состоять из таблиц и только из таблиц. Только содержимое таблиц определяет операции БД;
описание данных и манипуляции над ними должны быть независимыми от способа хранения данных на нижнем уровне.

Среди достоинств этой модели стоит выделить:

отсутствие дублирования данных;
исключение ряда ошибок и аномалий данных, которые есть в других моделях;
все данные представлены как факты, хранящиеся в виде отношений со столбцами и строками.

Not Only SQL (4 типа)

Ключ-значение (Key-value) — наиболее простой вариант хранилища данных, использующий ключ для доступа к значению в рамках большой хэш-таблицы.
Документно-ориентированное хранилище, в котором данные, представленные парами ключ-значение, сжимаются в виде полуструктурированного документа из тегированных элементов, подобно JSON, XML.
Колоночное хранилище, которое хранит информацию в виде разреженной матрицы, строки и столбцы которой используются как ключи.
Графовое хранилище представляют собой сетевую базу, которая использует узлы и рёбра для отображения и хранения данных.

Система управления базами данных (СУБД)

Понятие «База данных»

База данных — это совокупность связанных данных, организованных по определенным правилам, предусматривающим общие принципы описания, хранения и манипулирования, независимая от прикладных программ.

Отличительные признаки БД:

база данных хранится и обрабатывается в вычислительной системе;
данные в базе данных логически структурированы (систематизированы);
база данных включает метаданные, описывающие логическую структуру БД.

Рассмотрим различные определения базы данных:

Базой данных является представленная в объективной форме совокупность самостоятельных материалов, систематизированных таким образом, чтобы эти материалы могли быть найдены и обработаны с помощью электронной вычислительной машины.

База данных — организованная в соответствии с определенными правилами и поддерживаемая в памяти компьютера совокупность данных, характеризующая актуальное состояние некоторой предметной области и используемая для удовлетворения информационных потребностей пользователей.

База данных — совокупность данных, хранимых в соответствии со схемой данных, манипулирование которыми выполняют в соответствии с правилами средств моделирования данных.

База данных — некоторый набор перманентных (постоянно хранимых) данных, используемых прикладными программными системами какого-либо предприятия.

База данных — совокупность взаимосвязанных данных, совместно хранимых в одном или нескольких компьютерных файлах.

База данных — совместно используемый набор логически связанных данных (и описание этих данных), предназначенный для удовлетворения информационных потребностей организации.

База данных — это совокупность связанных данных, организованных по определенным правилам, предусматривающим общие принципы описания, хранения и манипулирования, независимая от прикладных программ.

Существует множество и других определений, отражающих скорее субъективное мнение тех или иных авторов о том, что означает этот термин в их понимании, однако общепризнанная единая формулировка отсутствует. Наиболее часто используются следующие отличительные признаки:

База данных хранится и обрабатывается в вычислительной системе. Таким образом, любые некомпьютерные хранилища информации (архивы, библиотеки, картотеки и т. п.) базами данных не являются.

Данные в базе данных логически структурированы (систематизированы) с целью обеспечения возможности их эффективного поиска и обработки в вычислительной системе. Структурированность подразумевает явное выделение составных частей (элементов), связей между ними, а также типизацию элементов и связей, при которой с типом элемента (связи) соотносится определенная семантика и допустимые операции.

База данных включает метаданные, описывающие логическую структуру БД в формальном виде (в соответствии с некоторой метамоделью). В соответствии с ГОСТ Р ИСО МЭК ТО 10 032−2007, «постоянные данные в среде базы данных включают в себя схему и базу данных. Схема включает в себя описания содержания, структуры и ограничений целостности, используемые для создания и поддержки базы данных. База данных включает в себя набор постоянных данных, определенных с помощью схемы. Система управления данными использует определения данных в схеме для обеспечения доступа и управления доступом к данным в базе данных».

Из перечисленных признаков только первый является строгим, а другие допускает различные трактовки и различные степени оценки. Можно лишь установить некоторую степень соответствия требованиям к БД.

Многие специалисты указывают на распространенную ошибку, состоящую в некорректном использовании термина база данных вместо термина система управления базами данных. Эти понятия, следовательно, необходимо различать.

СУБД и ее назначение

СУБД — программный комплекс поддержки интегрированной совокупности данных, предназначенный для создания, ведения и использования базы данных несколькими пользователями (прикладными программами).

Основная задача СУБД — дать пользователю базы данных возможность работать с ней не вникая во все подробности работы на уровне аппаратного обеспечения.

Состав СУБД

ядро отвечает за управление данными во внешней и оперативной памяти и журналирование;
процессор языка базы данных, обеспечивает оптимизацию запросов на извлечение и изменение данных и создание, как правило, машинно-независимого исполняемого внутреннего кода;
подсистему времени исполнения интерпретирует программы манипуляции данными, создающие пользовательский интерфейс с СУБД;
сервисные программы обеспечивающие ряд дополнительных возможностей по обслуживанию информационной системы.

Концептуальная архитектура PostgreSQL

Функции СУБД

Определение структуры создаваемой базы данных, ее инициализация и проведение начальной загрузки.
Предоставление пользователям возможности манипулирования данными (выборка необходимых данных, выполнение вычислений, разработка интерфейса ввода/вывода, визуализация)
Обеспечение независимости прикладных программ и данных (логической и физической независимости).
Защита логической целостности базы данных.
Защита физической целостности.
Управление полномочиями пользователей на доступ к базе данных.
Синхронизация работы нескольких пользователей.
Управление ресурсами среды хранения.
Поддержка деятельности системного персонала.

Двенадцать правил Дейта

Локальная автономия
Независимость от центрального узла
Непрерывные операции.
Прозрачность расположения
Прозрачная фрагментация
Прозрачность тиражирования
Обработка распределенных запросов
Обработка распределенных транзакций
Независимость от оборудования
Независимость от операционных систем
Прозрачность сети
Независимость распределенной системы от баз данных

Физическая организация данных

Файлы, объединенные в файловые группы;
Журналы (хранятся отдельно от данных);
Страницы, объединенные в экстенты (группа из восьми физически последовательных страниц файла базы данных);
Записи (строки);

Логическая организация данных

Логическая запись — поименованная совокупность элементарных данных, имеющая смысловую завершённость. Каждая запись в содержит один и тот же набор полей.
Логический файл — организованное определенным образом множество логических записей.

Реляционная структура данных и операции реляционной алгебры

Реляционная структура

Каждая таблица состоит из однотипных строк и имеет уникальное имя.
Строки имеют фиксированное число полей (столбцов) и значений (множественные поля и повторяющиеся группы недопустимы). Иначе говоря, в каждой позиции таблицы на пересечении строки и столбца всегда имеется в точности одно значение или ничего.
Строки таблицы обязательно отличаются друг от друга хотя бы единственным значением, что позволяет однозначно идентифицировать любую строку такой таблицы.
Столбцам таблицы однозначно присваиваются имена, и в каждом из них размещаются однородные значения данных (даты, фамилии, целые числа или денежные суммы).

Отношение — это таблица с данными.
Кортеж — строка таблицы или запись.
Домен — столбец таблицы или колонка.
Атрибут — значения, которое могут принимать элементы домена.

Основной элемент реляционной модели — это кортеж. Кортеж — это упорядоченный набор элементов, каждый из которых принадлежит определенному множеству или, иначе говоря, имеет свой тип. Совокупность однородных по структуре кортежей образует отношение.

Операции реляционной алгебры

Кодд описал восемь основных операций реляционной алгебры, позволяющих манипулировать с кортежами:

Для любых множеств:

Объединение;
Пересечение;
Разность;
Декартово произведение.

Только для отношений:

Выборка;
Проекция;
Соединение;
Деление.

Свойство реляционной алгебры — это ее замкнутость, то есть операции над отношениями задаются таким образом, чтобы результат сам был отношением.

Для любых множеств. Объединение

Операция над двумя отношениями, в результате которой получается новое отношение, состоящее из всех кортежей исходных отношений. Общие для исходных отношений кортежи в новом отношении встречаются только по одному разу. .

select A, B from R
UNION
select A, B from S

Для любых множеств. Пересечение

Операция над двумя отношениями, в результате которой получается новое отношение, состоящее из кортежей, принадлежащих обоим исходным отношениям.

select A, B from R
INTERSECT
select A, B from S

Для любых множеств. Разность

Операция над двумя отношениями, в результате которой получается новое отношение, состоящее из кортежей, принадлежащих первому отношению и не принадлежащих второму.

Обратите внимание! В следующем примере не хватает одного картежа. Какого?)

Правильно, a2 b1 ;)

select A, B from R
EXCEPT
select A, B from S

Для любых множеств. Декартово произведение

Операция над двумя отношениями, в результате которой получается новое отношение, состоящее из всех возможных кортежей, являющихся попарными сочетаниями кортежей исходных отношений.

select A, B, C, D
from R, S

Только для отношений. Выборка

Выборка производится над кортежами одного отношения. Результат выборки — новое отношение, состоящее из кортежей исходного отношения, удовлетворяющих заданному условию.

select A, B
from R
WHERE R.A = 'a1'

Только для отношений. Проекция

Проекция производится над кортежами одного отношения. Результат проекции — новое отношение содержащее только заданные атрибуты исходного отношения.

select A, C
from R

Только для отношений. Соединение

Операция над двумя отношениями, имеющими общие атрибуты, в результате которой получается новое отношение, состоящее из всех атрибутов исходных отношений и объединяющее только те кортежи исходных отношений в которых значения общих атрибутов совпадают.

select A, M, N, B
from R, S
WHERE R.M = S.N

То же самое:

select A, M, N, B
from 
R INNER JOIN S on R.M = S.N

Только для отношений. Деление

Результатом операции деления (R / S) является набор кортежей (строк) отношения R, которые соответствуют комбинации всех кортежей отношения S.

SELECT DISTINCT A, B 
from R 
WHERE
NOT EXIST

Вторая версия:

SELECT * from S 
WHERE NOT EXIST
S.c = R.c AND
S.d = R.d

* Низкая производительность
* Непригоден для деления без остатка
* Некорректное поведение при «делении на 0»

SELECT DISTINCT A, B 
FROM R 
LEFT JOIN S ON S.c = R.c. AND S.d = R.d
GROUP BY A, B
HAVING
     Count (Distinct S.c, S.d) = (Select Count(*) from S)
     AND
     Count (Distinct R.a, R.b) = (Select Count(*) from S)

Реляционная алгебра. Выводы

Реляционная алгебра — это язык операций, выполняемых над отношениями — таблицами реляционной базы данных. Операции реляционной алгебры позволяют на основе одного или нескольких отношений создавать другое отношение без изменения самих исходных отношений.

Реляционная алгебра = реляционные отношения + реляционные операции

Реляционная алгебра:

формальный язык манипулирования данными в реляционной модели;
процедурный язык, описывает как находить данные;
практически не используется в БД в качестве языка запросов;
формальная основа для оптимизации запросов.

Недостатки реляционной алгебры

Избыточность 8 операций, необходимым набором является 5 операций:
•Объединение;
•Вычитание;
•Произведение;
•Проекция;
•Выборка;
Остальные операции (пересечение, соединение, деление) можно представить комбинацией пяти необходимых.
Приведенных операций недостаточно для построения реальной системы обработки данных. Необходимы расширения — создание и переименование атрибутов, сравнение, присвоение и т. д.

SQL

Structured Query Language (SQL) — декларативный язык структурированных запросов.

Подмножества SQL:

•Data Definition Language (DDL) — язык определения данных;
•Data Manipulation Language (DML) — язык манипулирования данными;
•Data Query Language (DQL) — язык запросов;
•Data Control Language (DCL) — язык управления доступом к данными;
•Transaction Control Language (TCL) — управления транзакциями;

Диалекты языка SQL (расширения SQL)

Язык SQL — это стандарт, он реализован во всех реляционных базах данных, но у каждой СУБД есть расширение этого стандарта, есть собственный язык работы с данными.

Transact-SQL (сокращенно T-SQL) — используется в Microsoft SQL Server;
PL/SQL (Procedural Language / Structured Query Language) — используется в Oracle Database;
PL/pgSQL (Procedural Language/PostGres Structured Query Language) — используется в PostgreSQL.

Целостность данных (Constraint)

Ограничение целостности — cоответствие имеющейся в базе данных информации её внутренней логике, структуре и всем явно заданным правилам.

Ограничение — формулировка требования к данным на уровне поля или таблицы.

Два способа обеспечения целостности:

Декларативный — критерии, которым должны удовлетворять данные, задаются при определении объекта (сущности) и являются частью определения схемы базы данных.
Процедурный — критерии описываются в пакетах операторов, выполнение которых определят целостность данных

Декларативные способы

Первичный ключ (PRIMARY KEY) — набор атрибутов, позволяющий однозначно идентифицировать сущность в рамках таблицы, правило ссылочной целостности
Внешний ключ (SECONDARY KEY) — атрибут, правило ссылочной целостности
Типы данных используемый для представления домена
Ограничение уникальности значения (UNIQUE)
Ограничение значения по умолчанию (DEFAULT)
Ограничение проверки значений (CHECK)
Ограничение обязательности значения домена (NOT NULL)

Процедурные способы

Триггеры — специальный тип хранимой процедуры, автоматически выполняемой при определенных условиях.

Назначение — предотвращение вставки, изменения или удаления записи, отвечающее некоторым условиям.

• обращение к данным в связанной таблице
• проверка дельты обновления
• формирование сообщений/уведомлений

Типы Триггеров по событиям:

•Insert
•Delete
•Update
•Одновременное возникновение и совпадение событий из списка выше

ACID

Транзакция — это набор последовательных операций с базой данных, соединенных в одну логическую единицу.

Свойства транзакции:

•Atomicity или атомарность (A) — неделимость, либо будут выполнены все действия транзакции, либо никакие.
•Consistency или согласованность (C) — если до выполнения транзакции данные в БД находятся в неком состоянии, то они будут в этом же состоянии и после выполнения транзакции.
•Isolation или изоляция (I) — позволяет скрывать изменения, внесенные одной операцией транзакции при возникновении явления гонок.
•Durability или долговечность (D) — если транзакция выполнена, и даже если в следующий момент произойдет сбой в системе, результат сохранится.

⟵ Назад

Вам также может быть интересно

Исследование ИТ-рынка России

Аутсорсинг разработки ПО vs создание программного обеспечения собственными силами

ИТ-аутсорсинг: проблемы и решения

Функциональное тестирование ПО. Часть 1

Zabbix 5: сущность и принципы применения

Цифровизация предприятий. Кооперация производственных организаций и ИТ-компаний