Краткое руководство по системам управления базами данных

SQL – это язык структурированных запросов. СУРБД – система управления реляционными базами данных. Существуют следующие разновидности баз данных:

Система управления файлами
Иерархические
Сетевые
Реляционные
Объектно-ориентированные
Гибридные

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

2) Сетевые базы данных – являются расширением иерархических баз данных. Иерархические базы данных из-за большого количества недостатков просуществовали недолго и были заменены на сетевые базы данных.Сетевые базы данных представляют собой организацию данных в виде железнодорожных путей, где каждая крупная станция имеет связи с несколькими другими станциями. В сетевых базах данных имеется связь многие ко многим. Недостатком сетевых баз данных является сложность разработки больших приложений.

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

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

Первичные ключи

Строки в реляционной базе данных неупорядоченные. Для выбора в таблице конкретной строки создается один или несколько столбцов, значения которых во всех строках уникальны. Такой столбец называется первичным ключом.
Первичный ключ (primary key) – является уникальным значением в столбце. Никакие из двух записей таблицы не могут иметь одинаковых значений первичного ключа.
По способу задания первичных ключей различают логические (естественные) ключи и суррогатные (искусственные).
Логический ключ – представляет собой значение, определяющее запись естественным образом.
Суррогатный ключ – представляет собой дополнительное поле в базе данных, предназначенное для обеспечения записей первичным ключом.

Нормализация базы данных

Нормализацией схемы базы данных – называется процедура, производимая над базой данных с целью удаления в ней избыточности.

Централизованная архитектура

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

Архитектура клиент-сервер

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

Трехуровневая архитектура интернета

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

Кластерная модель

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

Как работают базы данных.

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

Язык структурированных запросов SQL позволяет производить следующие операции:

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

Достоинства системы управления базами данных MySQL:

Скорость выполнения запросов.
СУБД MySQL разработана с использованием языков C/C++ и оттестирована более чем на 23 платформах.
Открытый код доступен для просмотра и модернизации всем желающим.
Высокое качество и устойчивость работы.
Поддержка API для различных языков программирования
Наличие встроенного сервера. СУБД MySQL может быть использован как с внешним сервером, поддерживающим соединение с локальной машиной и с удаленным хостом, так и в качестве встроенного сервера.
Широкий выбор типов таблиц позволяет реализовать оптимальную для решаемой задачи производительность и функциональность.
Локализация выполнена корректна.
Совместимость с другими базами данных и полностью удовлетворяет стандарту SQL.

Индексы

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

Существует несколько видов индексов:

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

Типы и структура таблиц

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

MyISAM

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

Frm – содержит структуру таблицы, в файле данного типа хранится информация об именах и типах столбцов и индексов.
Myd – файл, в котором содержатся данные таблицы.
Myi – файл, котором содержатся индексы таблицы.

Особенности типа таблиц MyISAM:

Данные хранятся в кросс-платформенном формате, это позволяет переносить базы данных с сервера непосредственным копированием файлов, минуя промежуточные форматы.
Максимальное число индексов в таблице составляет 64. Каждый индекс может состоять максимум из 16 столбцов.
Для каждого из текстовых столбцов может быть назначена своя кодировка.
Допускается индексирования текстовых столбцов, в том числе и переменной длины.
Поддерживается полнотекстовый поиск.
Каждая таблица имеет специальный флаг, указывающий правильность закрытия таблиц. Если сервер останавливается аварийно, то при его повторном старте незакрытые флаги сигнализируют о возможных сбойных таблицах, сервер автоматически проверяет их и пытается восстановить.

MERGE

Тип таблиц MERGE позволяет сгруппировать несколько таблиц типа MyISAM в одну. Такой тип таблиц применяется для снятия ограничения на объем таблиц MyISAM. Таблицы MyISAM, которые подвергаются объединению в одну таблицу MERGE, должны иметь одинаковую структуру, то есть, одинаковые столбцы и индексы, а также порядок их следования.
При создании таблицы типа MERGE будут образованы файлы структуры таблицы с расширением frm и файлы с расширением mrg. Файл mrgсодержит список индексных файлов, работа с которыми должна осуществляться как с единым файлом.

MEMORY (HEAP)

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

Ограничения MEMORY таблиц:

Индексы используются только в операциях сравнения совместимо с операторами = и <=>, с другими операторами, такими как > или < индексирование столбцов не имеет смысла
Возможно использование только неуникальных индексов.
Можно использовать записи фиксированной длины, поэтому в них не допустимы столбцы типов TEXT и BLOD.
В версиях, предшествующих MySQL 4.0.2, не поддерживается индексирование столбцов, содержащих NULL-значения.

EXAMPLE

Данный тип таблиц является заглушкой: можно создать таблицу данного типа, но хранить или получить из нее данные нельзя. При создании таблиц данного тип, точно также как и в случае MEMORY, создается один файл с расширением frm, в котором определяется структура таблицы.
EXAMPLE был введен для удобства сторонних разработчиков и демонстрирует, каким образом следует создавать собственные типы таблиц.

BDB (BerkeleyDB)

Таблицы типа BDB обслуживаются транзакционным обработчиком Berkeley DB, разработанным компанией Sleepycat. При создании таблиц данного типа формируются два файла: первый с расширением frm, в котором определяется структура базы данных, а второй с расширением db, в котором размещаются данные и индексы.

Особенности типа BDB:

Для каждой таблицы ведется журнал. Это позволяет значительно повысить устойчивость базы и увеличить вероятность успешного восстановления после сбоя.
Таблицы BDB хранятся в виде бинарных деревьев. Такое представление замедляет сканирование таблицы и увеличивает занимаемое место на жестком диске по сравнению с другими типами таблиц. С другой стороны, поиск отдельных значений в таких таблицах осуществляется быстрее.
Каждая таблица BDB должна иметь первичный ключ, в случае его отсутствия создается скрытый первичный ключ, снабженный атрибутом AUTO_INCREMENT.
Поддерживаются транзакции на уровне страниц.
Подсчет числа строк в таблице при помощи встроенной функции count() осуществляется медленнее, чем для MyISAM, так как в отличие от последних, для BDB-таблиц не поддерживается подсчет количества строк в таблице, и MySQL вынужден каждый раз сканировать таблицу заново.
Ключи не являются упакованными, и ключи занимают больше места.
Если таблица займет все пространство на диске, то будет выведено сообщение об ошибке и выполнен откат транзакции.
Для обеспечения блокировок таблиц на уровне операционной системы в файл db в момент создания таблицы записывается путь к файлу. Это приводит к тому, что файлы нельзя перемещать из текущего каталога в другой каталог.
При создании резервных копий таблиц необходимо использовать утилиту mysqldump или создать резервные копии всех db файлов и файлов журналов. Обработчик таблицы хранит незавершенные транзакции в файлах журналов, их наличие требуется при запуске сервера MySQL.

InnoDB

Данный тип таблиц обеспечивает высокую производительность и устойчивое хранение данных в таблицах объемом вплоть до 1 Тбайт и нагрузкой на
сервер до 800 вставок/обновлений в секунду.Особенности таблиц типа InnoDB:

Таблицы не создаются в базах данных, и для каждой из таблиц не выделяется отдельный файл данных. Исключение – файл определения с расширением frm, который создается по умолчанию. Все таблицы хранятся в едином табличном пространстве, поэтому имена таблиц должны быть уникальными.
Хранение данных в едином табличном пространстве позволяет снять ограничение на объем таблиц, так как файл с таблицами может быть разбит не несколько частей и распределен по нескольким дискам или даже хостам.
Данный тип таблиц поддерживает автоматическое восстановление после сбоев.
Обеспечивается поддержка транзакций.
Единственный тип таблиц, поддерживающий внешние ключи и каскадное удаление.
Выполняется блокировка на уровне отдельных записей.
Расширенная поддержка кодировок.
Рушатся при достижении объема в несколько гигабайт, однако заметно уступают в скорости и не поддерживают полнотекстовый поиск.

NDB Cluster

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

CSV

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

FEDERATED

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

BLACKHOLE

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

Транзакции

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

Репликация

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

Хранимые процедуры

Хранимые процедуры позволяют объединить последовательность запросов и сохранить их на сервере.
Преимущества хранимых процедур:

Повторное использование кода
Сокращение сетевого трафика.
Безопасность.
Простота доступа.
Выполнение деловой логики.

Триггеры

Триггер – эта хранимая процедура, привязанная к событию на изменения содержимого таблицы: вставка, обновление, удаление.

Представления

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

Представление CHARACTER_SETS

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

Представление COLLATIONS

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

Представление COLLATION_CHARACTER_SET_APPLICABILITY

Содержит всевозможные комбинации кодировок и сортировок, доступные текущему пользователю.

Представление COLUMN_PRIVILEGES

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

Представление COLUMNS

Содержит информацию о доступных текущему пользователю столбцах во всех таблицах всех баз данных.

Представление KEY_COLUMN_USAGE

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

Представление ROUTINES

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

Представление SCHEMA_PRIVILEGES

Содержит глобальные привилегии всех пользователей сервера MySQL.

Представление SCHEMATA

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

Представление STATISTICS

Содержит разнообразную информацию об индексах.

Представление TABLE_CONSTRAINTS

Содержит информацию об ограничивающих индексах, которые имеют ограничение уникальности значения (PRIMARY KEY, UNIQUE) или ограничение внешнего ключа (FOREIGN KEY).

Представление TABLE_PRIVILEGES

Содержит информацию о табличных привилегиях.

Представление TABLES

Содержит список таблиц и их характеристики.

Представление USER_PRIVILEGES

Содержит информацию о глобальных привилегиях базы данных.

Представление VIEWS

Содержит информацию о глобальных привилегиях базы данных.

Реляционные базы данных

Реляционная модель базы данных состоит из трех частей:
Структурная часть – описывает, какие объекты рассматриваются реляционной моделью. Реляционная база данных состоит из набора отношений. Схемой реляционной базы данных называется набор заголовков отношений, входящих в базу данных.
Целостная часть – описывает ограничения специального вида, которые должны выполняться для любых отношений в любых реляционных базах данных. Это целостность сущностей и целостность внешних ключей.
Манипуляционная часть – описывает два эквивалентных способа манипулирования реляционными данными – реляционную алгебру и реляционное исчисление.

Термины реляционных баз данных.

Реляционный термин	Описание
Отношение	Таблица
Заголовок отношения	Заголовок таблицы
Тело отношения	Тело таблицы
Атрибут отношения	Наименование столбца (поля) таблицы
Кортеж отношения	Строка (запись) таблицы
Степень отношения	Количество столбцов таблицы
Мощность (кардинальность) отношения	Количество строк таблицы
Домен	Базовый или пользовательский тип данных

Атрибуты

Атрибуты сущности – это именованная характеристика, являющаяся некоторым свойством сущности.

При проектировании атрибутов полезно задавать такие вопросы:

Какие данные о сущности мы хотим хранить?
Какие свойства есть у экземпляра этой сущности, даже если вокруг нее больше ничего нет?
Есть ли у экземпляра этой сущности только один экземпляр этой вещи?
Может ли изменяться описанная атрибутом характеристика сущности с течением времени?

Бинарные связи

Бинарные связи – это связи, в которые вступают ровно две сущности. Важнейшее свойство связи – кардинальное число.

Типы бинарных связей:

Связь типа «один-к-одному» означает, что один экземпляр первой сущности связан не более чем с одним экземпляром второй сущности и, наоборот, один экземпляр второй сущности связан не более чем с одним экземпляром первой сущности.
Связь типа «один-ко-многим» означает, что один экземпляр первой сущности связан с несколькими экземплярами второй сущности, но при этом один экземпляр второй сущности связан не более чем с один экземпляром первой сущности.
Связь типа «много-ко-многим» означает, что каждый экземпляр первой сущности может быть связан с несколькими экземплярами второй сущности, и каждый экземпляр второй сущности может быть связан с несколькими экземплярами первой сущности. Эта связь должна быть заменена двумя связями типа один-ко-многим путем создания промежуточной сущности.

Ролевые связи

Ролевые связи необходимы, когда:

Экземпляры одной и той же сущности вступают в связи между собой.
В зависимости от значения одного из атрибутов сущности по-разному определяется само множество других ее атрибутов.
В зависимости от значения одного из атрибутов сущности она по-разному вступает в связи с другими сущностями.

Рекурсивные связи

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

Различают три варианта рекурсивной связи:

Рекурсивная связь «один-к-одному», моделирующая цепочку.
Рекурсивная связь «один-ко-многим» или иерархическая рекурсивная связь.
Рекурсивная связь «много-ко-многим» или сетевая рекурсивная связь.

Логическое проектирование и оптимизация

OLTP – обработка транзакций в режиме реального времени. Способ организации БД, при котором система работает с небольшими по размерам транзакциями, но идущими большим потоком, и при этом клиенту требуется от системы минимальное время отклика. Примерами OLTP приложений могут быть системы складского учета, системы заказов билетов, банковские системы, выполняющие операции по переводу денег.
Особенности OLTP приложений:

Транзакций очень много.
Транзакции выполняются одновременно.
При возникновении ошибки транзакция должна целиком откатиться и вернуть систему к состоянию, которое было до начала транзакции (не должно быть ситуации, когда деньги сняты со счета, но не поступили на другой счет).
Все запросы к базе данных, которые должны выполняться в реальном времени, состоят из команд вставки, обновления, удаления.

OLAP системы характеризуются следующими признаками:

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

Уровни моделирования реляционной базы данных

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

Вводятся следующие понятия:

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

Критерии оценки качества логической модели

Критерии важные с точки зрения получения качественной базы данных:
1. Адекватность базы данных предметной области.
2. Скорость выполнения операций обновления данных.
3. Скорость выполнения операций выборки данных.
4. Легкость разработки и сопровождения базы данных.
5. Отсутствие неоправданной избыточности данных.

Физическое представление базы данных

Проблемы, которые приходится решать при проектировании физического представления базы данных:

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