Реляционная модель
Реляционная модель базы данных была предложена в 1969 г. математиком и научным сотрудником фирмы IBM Э.Ф. Коддом (E.F. Codd). Некоторые начальные сведения о реляционных базах данных содержатся в обзорной статье “БД и СУБД ” 2. Поскольку в настоящее время именно реляционные базы данных являются доминирующими, в этой статье (а также в статьях “Описание данных ”, “Обработка данных ” и “Проектирование БД ” 2) подробно рассматриваются наиболее существенные понятия реляционной модели.
Сразу отметим, что теория реляционных баз данных изначально была сформулирована на строгом математическом языке, и именно строгие, формально определенные математические понятия наилучшим образом описывают суть вещей. Вместе с тем в большинстве случаев можно без особого ущерба пожертвовать строгостью терминологии в пользу прозрачности изложения, что мы и будем стараться делать.
Основная идея реляционной модели заключается в следующем. База данных состоит из ряда неупорядоченных таблиц (в простейшем случае - из одной таблицы). Таблицами можно манипулировать посредством непроцедурных (декларативных) операций - запросов , результатами которых также являются таблицы.
Нередко слово “реляционная” (relational ) в термине “реляционная модель” трактуют, основываясь на том, что в реляционной базе данных устанавливаются связи (relate ) между таблицами. Такое объяснение удобно, но оно не является точным. В оригинальной системе терминов Кодда термины связи (relations ), атрибуты (attributes ) и кортежи (tuples ) употреблялись там, где большинство из нас пользуется более привычными терминами таблицы, столбцы (поля) и строки (записи).
При построении инфологической модели предметной области (см. “БД и СУБД ”, “Проектирование БД ” 2) выделяются сущности (объекты), описываются их свойств а (характеристики, атрибуты), существенные для целей моделирования, и устанавливаются связи между сущностями. На этапе перехода от инфологической к даталогической реляционной модели как раз и появляются таблицы. Как правило, каждая сущность представляется одной таблицей. Каждая строка таблицы (одна запись) соответствует одному экземпляру сущности, а каждое поле описывает некоторое свойство (атрибут) .
Например, если нам требуется хранить информацию о людях, включающую фамилию каждого, имя, отчество, ИНН, страну проживания и дату рождения, то сущностью является именно человек, а указанные данные - атрибутами. Сама сущность естественным образом становится названием таблицы.
Таблица “Человек”
Реляционная модель требует, чтобы каждая строка таблицы была уникальной, т.е. чтобы любые две строки различались значением хотя бы одного атрибута.
Традиционная табличная форма удобна, когда требуется представить сами данные. Если же, как в приведенном выше примере, интересует лишь структура - имена полей, то с точки зрения наглядности, удобства использования в схемах и экономии места удобнее изображать ее следующим образом:
Ключом таблицы называется поле или группа полей, содержащие уникальные в рамках данной таблицы значения . Ключ однозначно определяет соответствующую строку таблицы. Если ключ состоит из одного поля, его часто называют простым , если из нескольких - составным . В приведенном выше примере ключом является поле ИНН (мы считаем известным тот факт, что ИНН в пределах страны являются уникальными).
Рассмотрим пример таблицы с составным ключом. На сайтах прогнозов погоды нередко представляют информацию следующим образом: для каждой даты указывают прогнозируемую температуру ночью, утром, днем и вечером. Для хранения указанной информации можно использовать таблицу следующего вида:
В этой таблице ни поле Дата, ни Время суток, ни Температура не являются ключами - в каждом из этих полей значения могут повторяться. Зато комбинация полей Дата+Время суток является уникальной и однозначно определяет строку таблицы. Это и есть составной ключ.
Нередко встречается ситуация, в которой выбор ключа не является однозначным. Вернемся к первому примеру. Допустим, в дополнение к фамилии, имени, отчеству, ИНН, дате рождения требуется хранить серию и номер общегражданского паспорта и серию и номер заграничного паспорта. Таблица будет иметь следующий вид.
В этой таблице можно выбрать целых три ключа. Один из них - простой (ИНН), два другие - составные (Серия+Номер общегражданского паспорта и Серия+Номер заграничного паспорта). В такой ситуации разработчик выбирает наиболее удобный с точки зрения организации БД ключ (в общем случае - ключ, на поиск значения которого требуется наименьшее время). Выбранный ключ в этом случае часто называют главным, или первичным , ключом, а другие комбинации столбцов, из которых можно сделать ключ, - возможными , или альтернативными, ключами. Отметим, что хотя бы один возможный ключ в таблице имеется всегда, так как строки не могут повторяться и, следовательно, комбинация всех столбцов гарантированно является возможным ключом.
При изображении таблиц первичные ключи таблиц принято выделять. Например, соответствующие поля часто подчеркивают. А Microsoft Access выделяет ключевые поля полужирным шрифтом.
Еще чаще, чем с неоднозначностью выбора ключа, разработчики сталкиваются с отсутствием ключа среди данных, которые требуется хранить. Подобный факт может быть установлен в процессе анализа предметной области. Например, если требуется хранить простой список людей - имена, фамилии, отчества и даты рождения, то ключа в этом наборе атрибутов нет вовсе - мыслимой является ситуация, когда у двух различных людей указанные данные совпадают полностью. В таком случае приходится искусственно вводить дополнительное поле, например, уникальный номер человека. Такой ключ в литературе иногда называют суррогатным . Нередко суррогатный ключ вводят и из соображений эффективности. Если, например, в таблице имеется длинный составной ключ, то разработчики часто вводят дополнительный короткий числовой суррогатный ключ и именно его делают первичным. Нередко так поступают даже при наличии простого ключа, имеющего “неудобный” (неэффективный для поиска) тип данных, например, строковый. Подобные операции уже не имеют отношения к теории, но сплошь и рядом встречаются на практике.
Внимательный читатель, возможно, обратит внимание на то, что ключ практически всегда можно расширить (если только в него не входят все поля таблицы) за счет включения избыточных полей. Формально такой ключ останется ключом, но с практической точки зрения это лишь игра понятиями. Такие ключи и за возможные-то не считают, поскольку всегда необходимо стремиться к минимизации длины (сложности) ключа.
Нормальные формы, нормализация
Не всякая таблица, которую мы можем нарисовать на бумаге или в Word’е, может быть таблицей реляционной базы данных. И не всякая таблица, которая может использоваться в реляционной базе данных, является правильной с точки зрения требования реляционной модели.
Во-первых, требуется, чтобы все данные в пределах одного столбца имели один и тот же тип (о типах см. “Описание данных ” 2). С этой точки зрения приведенный ниже пример не имеет смысла даже обсуждать:
Во-вторых, требуется, чтобы в таблице был назначен первичный ключ .
Указанные требования являются необходимыми, но недостаточными. В теории реляционных баз данных вводятся понятия так называемых “нормальных форм” - требований к организации данных в таблицах. Нормальные формы нумеруются последовательно, по мере ужесточения требований. В правильно спроектированной БД таблицы находятся как минимум в третьей нормальной форме. Соответственно, мы рассмотрим первые три нормальные формы. Напомним, что мы имеем дело с таблицами, удовлетворяющими двум сформулированным выше основным требованиям.
Первая нормальная форма (1НФ)
Первая нормальная форма предписывает, что все данные, содержащиеся в таблице, должны быть атомарными (неделимыми ). Перечень соответствующих атомарных типов данных определяется СУБД. Требование 1НФ совершенно естественное. Оно означает, что в каждом поле каждой записи должна находиться только одна величина, но не массив и не какая-либо другая структура данных. Приведем осмысленный пример таблицы, которая не находится в 1НФ. Пусть у нас имеются списки оценок учеников по некоторому предмету.
Так как значение поля Оценки не является атомарным, таблица не соответствует требованиям 1НФ.
О возможном способе представления списка оценок написано в методических рекомендациях к статье “Проектирование БД” 2.
Вторая нормальная форма (2НФ)
Говорят, что таблица находится во второй нормальной форме, если она находится в 1НФ и каждый не ключевой столбец полностью зависит от первичного ключа. Другими словами, значение каждого поля должно полностью определяться значением первичного ключа. Важно отметить, что зависимость от первичного ключа понимается именно как зависимость от ключа целиком, а не от отдельной его составляющей (в случае составного ключа). Приведем пример таблицы, которая не находится во 2НФ. Для этого вернемся к примеру прогноза погоды и дополним таблицу еще одним столбцом - временем восхода солнца (это вполне правдоподобный пример, такого рода информация часто приводится на сайтах прогноза погоды).
Как мы помним, данная таблица имеет составной ключ Дата+Время суток. Поле Температура полностью зависит от первичного ключа - с ним проблем нет. А вот поле Восход зависит лишь от поля Дата, Время суток на время восхода естественным образом не влияет.
Здесь уместно задаться вопросом: а в чем практический смысл 2НФ? Какая польза от этих ограничений? Оказывается - большая. Допустим, что в приведенном выше примере разработчик проигнорирует требования 2НФ. Во-первых, скорее всего возникнет так называемая избыточность - хранение лишних данных. Ведь если для одной записи с данной датой уже хранится время восхода, то для всех других записей с данной датой оно должно быть таким же и хранить его, вообще говоря, незачем.
Обратим внимание на слова “должно быть”. А если не будет? Ведь на уровне БД это никак не контролируется - ключ в таблице составной, одинаковые даты могут быть (и по смыслу скорее всего будут). И никакие формальные ограничения (а наше понимание, что “такого не может быть”, к таковым не относится) не запрещают указать разное время восхода для одной и той же даты.
Третья нормальная форма (3НФ)
Говорят, что таблица находится в 3НФ, если она соответствует 2НФ и все не ключевые столбцы взаимно независимы.
Взаимную зависимость столбцов удобно понимать следующим образом: столбцы являются взаимно зависимыми, если нельзя изменить один из них, не изменяя другой.
Приведем пример таблицы, которая не находится в 3НФ. Рассмотрим пример простой записной книжки для хранения домашних телефонов людей, проживающих, возможно, в различных регионах страны.
В этой таблице присутствует зависимость между не ключевыми столбцами Город и Код города, следовательно, таблица не находится в 3НФ.
Отметим, что наличие указанной выше зависимости разработчик определяет, анализируя предметную область, - никакими формальными методами подобную коллизию увидеть нельзя. При изменении свойств предметной области зависимость между столбцами может и исчезнуть. Например, если в пределах одного города вводятся различные коды (как 495 и 499 в Москве), соответствующие столбцы перестают быть связанными с точки зрения нарушения требований 3НФ.
В теории реляционных баз данных рассматриваются и формы высших порядков - нормальная форма Бойса - Кодда, 4НФ, 5НФ и даже выше. Большого практического значения эти формы не имеют, и разработчики, как правило, всегда останавливаются на 3НФ.
Нормализация БД
Нормализация представляет собой процесс приведения таблиц базы данных к выбранной нормальной форме. Нормализация до 2НФ, как правило, сводится к декомпозиции - разбиению одной таблицы на несколько. Нормализация до 3НФ обычно может быть выполнена удалением зависимых (вычисляемых) столбцов. В некоторых случаях при нормализации до 3НФ приходится также производить декомпозицию.
Многотабличные БД, связи между таблицами, внешние ключи
На практике однотабличные базы данных встречаются достаточно редко, поскольку с точки зрения моделирования базой данных предметной области наличие одной таблицы означает наличие одной сущности. В свою очередь, наличие нескольких сущностей обычно означает наличие связей между ними.
Не ставя целью полное проектирование БД, рассмотрим пример, позволяющий продемонстрировать связи в многотабличных БД.
Пусть мы имеем дело со школой, в которой есть ученики, сгруппированные по классам, и учителя, преподающие некоторые предметы. У нас сразу выделяются четыре сущности: ученики, учителя, классы и предметы. Эти сущности уже дают нам четыре таблицы.
Далее нам требуется решить вопрос об атрибутах сущностей - какую именно информацию мы будем хранить. Поскольку наш пример носит исключительно демонстрационный характер, постараемся минимизировать объем хранимой информации. Договоримся для каждого ученика хранить фамилию и имя, для класса - номер параллели и букву, идентифицирующую класс внутри параллели, для учителя - фамилию, имя и отчество, для предмета - только его название.
Теперь нам следует решить вопрос с первичными ключами. Таблицы учеников и учителей в принципе не имеют ключа, поэтому мы введем в них суррогатный числовой ключ - номер. Таблицы классов и предметов, вообще говоря, имеют ключи. В таблице классов ключ составной, его образуют атрибуты Номер параллели+Буква, а в таблице предметов простой ключ состоит из единственного поля - названия предмета. Вспомним, что, говоря о ключах, мы упоминали о том, что суррогатные ключи часто добавляют из соображений эффективности, стремясь избавиться от составных ключей или ключевых полей неудобных типов, например, строковых. Так мы и поступим. Добавим в каждую из таблиц суррогатный числовой ключ.
В результате мы получим следующий набор таблиц, соответствующих описываемым сущностям.
Понимая, с какой предметной областью имеем дело, мы знаем, что наши сущности существуют не сами по себе - они связаны некоторыми отношениями, которые мы обозначили выше. Но как их связать технически? Тут не обойтись без введения дополнительных полей и даже дополнительных таблиц. Разберемся с отношениями между сущностями по порядку.
Чтобы отнести ученика к некоторому классу, заведем в таблице “Ученик” дополнительное поле Номер класса. (Понятно, что его тип должен полностью совпадать с типом поля Номер класса в таблице “Класс”.) Теперь мы можем связать таблицы “Ученик” и “Класс” по совпадающим значениям полей Номер класса (мы не случайно назвали эти поля одинаково, на практике так часто поступают, чтобы легко ориентироваться в связывающих полях). Заметим, что одной записи в таблице “Класс” может соответствовать много записей в таблице “Ученик” (и на практике скорее всего соответствует - трудно представить себе класс из одного ученика). О таких таблицах говорят, что они связаны отношением “один ко многим ”. А поле Номер класса в таблице “Ученик” называют внешним ключом . Как видим, назначение внешних ключей - связывание таблиц. Отметим, что внешний ключ всегда ссылается на первичный ключ связанной таблицы (т.е. внешний ключ находится на стороне “многих”). Связанный с внешним первичный ключ называют родительским , хотя этот термин используется реже.
Проиллюстрируем сказанное схемой в стиле Microsoft Access (подробнее о “Схеме данных” Access написано в статье “Описание данных” 2).
Теперь вспомним об учителях и предметах. Анализируя предметную область (только так - ведь истинное положение вещей из самой формальной модели извлечь невозможно), мы замечаем, что тип связи между сущностями “учитель” и “предмет” иной, нежели рассмотренный выше. Ведь не только один предмет могут вести много учителей, но и один учитель может вести много предметов. Таким образом, между этими сущностями имеется связь “многие ко многим ”. Тут уже не обойтись введением дополнительных полей (попробуйте!). Связи “многие ко многим” всегда разрешаются посредством введения дополнительной таблицы. А именно, организуем таблицу “Учитель-Предмет”, имеющую следующую структуру:
Таблица “Учитель-Предмет”
Эта таблица имеет составной ключ, образованный из двух ее полей. И таблица “Учитель”, и таблица “Предмет” связаны с данной таблицей отношением “один ко многим” (разумеется, в обоих случаях “многие” находятся на стороне “Учитель-Предмет”). Соответственно, в таблице “Учитель-Предмет” имеются два внешних ключа (оба - части составного первичного ключа, что не воспрещается), служащие для связи с соответствующими таблицами.
На практике, помимо рассмотренных отношений “один ко многим” и “многие ко многим”, встречается и отношение “один к одному ”. С точки зрения теории такое отношение интереса не представляет, так как две таблицы, связанные отношением “один к одному”, всегда можно просто объединить в одну. Тем не менее в реальных базах данных отношение “один к одному” применяется для оптимизации обработки данных. Проиллюстрируем сказанное примером.
Допустим, мы храним очень много разнообразной информации о людях - данные их всевозможных документов, телефоны, адреса и пр. Скорее всего боRльшая часть этих данных будет использоваться очень редко. А часто нам потребуются лишь фамилия, имя, отчество и телефон. Тогда имеет смысл организовать две таблицы и связать их отношением “один к одному”. В одной (небольшой) таблице хранить часто используемую информацию, в другой - остальную. Естественно, что таблицы, связанные отношением “один к одному”, имеют один и тот же первичный ключ.
Правила целостности
Реляционная модель определяет два общих правила целостности базы данных: целостность объектов и ссылочная целостность.
Правило целостности объектов очень простое. Оно требует, чтобы первичные ключи таблиц не содержали неопределенных (пустых) значений .
Правило ссылочной целостности требует, чтобы внешние ключи не содержали несогласованных с родительскими ключами значений . Возвращаясь к рассмотренному выше примеру, мы должны потребовать, например, чтобы ученики относились лишь к классу, номер которого указан в таблице “Классы”.
Большинство СУБД умеют следить за целостностью данных (разумеется, это требует соответствующих усилий и от разработчика на этапе описания структур данных). В частности, для поддержания ссылочной целостности используются механизмы каскадирования операций. Каскадирование подразумевает, в частности, то, что при удалении записи из “родительской” таблицы, связанной с другой таблицей отношением “один ко многим”, из таблицы “многих” автоматически (самой СУБД, без участия пользователя) удаляются все связанные записи. И это естественно, ведь такие записи “повисают в воздухе”, они более ни с чем не связаны.
Индексация
Индексация - крайне важная с точки зрения практического применения, но факультативная с позиции чистой теории вещь. Основное назначение индексации - оптимизация (убыстрение) поиска (и, соответственно, некоторых других операций с базой данных). Индексация в любом случае требует дополнительных ресурсов (на физическом уровне чаще всего создаются специальные индексные файлы). Операции, связанные с модификацией данных, индексация может даже замедлять, поэтому индексируют обычно редко изменяемые таблицы, в которых часто производится поиск.
Индексный файл очень похож на индекс обычной книги. Для каждого значения индекса хранится список строк таблицы, в которых содержится данное значение. Соответственно, для поиска не надо просматривать всю таблицу - достаточно заглянуть в индекс. Зато при модификации записей может потребоваться перестроить индекс. И на это уходит дополнительное время.
Разумеется, и речи не идет о том, чтобы излагать теорию реляционных баз данных в рамках базового курса информатики! Тем не менее эта статья очень важна для нашей энциклопедии, поскольку в данном случае мы имеем дело с материалом, который не может быть в полном объеме изложен на уроках, но учитель владеть им должен. Почему?
Во-первых, потому что ряд понятий изучаются как раз в рамках базового курса. Это и табличное представление данных, и ключи таблиц. А все мы знаем, что очень трудно грамотно и точно изложить лишь некоторые понятия, не представляя общей картины.
Во-вторых, выполняя с детьми простые запросы к базам данных (соответствующий материал изложен в статье “Обработка данных” 2), необходимо иметь дело с правильными с точки зрения реляционной теории таблицами. Не требуется объяснять ученикам, что эти таблицы правильные, а “вот если бы…, то таблица была бы неправильной”, но недопустимо использовать плохие примеры.
В профильном курсе информатики ситуация может быть принципиально иной. Важнейшая и крайне продуктивная форма работы в профильных классах - проектная. В рамках учебных проектов можно и нужно разрабатывать несложные базы данных, и здесь не обойтись без основ изложенной теории. Необходимо, однако, учитывать следующее:
Моделируемые предметные области должны быть не слишком большими;
Они должны быть очень хорошо знакомы учащимся (в этом смысле изрядно поднадоевший всем проект “Школа” - не худший выбор!);
Наивно ожидать, что, прослушав основы теории, ученики смогут что-то спроектировать сами. Каждый шаг необходимо проходить вместе с ними, подробно аргументируя свои действия.
II. Сетевая модель
III. Реляционная модель
запись поле
иерархических и сетевых моделей внешних ключей
4. Реляционная модель данных
Реляционная БД
* Отношение
* Атрибут столбца (поля) таблицы.
* Тип данных
* Связь ключом.
* Объединение
Основными функциями РСУБД являются:
· Определение данных
· Обработка данных
· Управление данными
Microsoft Access
Окно БД в Access
|
Режимы работы с объектами
Кнопки для работы с объектами БД расположены на Панели инструментов окна БД:
Открыть – позволяет перейти в режим редактирования таблицы, выполнения запроса, загрузки формы, построения отчета, запуска макроса.
Конструктор – обеспечивает переход к режиму настройки выбранного объекта.
Создать – позволяет приступить к созданию нового объекта выбранного типа.
7. Работа с таблицами
Чтобы создать таблицу, нужно перейти к списку таблиц и нажать кнопку Создать . Появится новое диалоговое окно Новая таблица :
Таблицу в Access можно создать несколькими способами:
· построить новую таблицу «с нуля», воспользовавшись Конструктором ;
· запустить Мастер таблиц – специальную программу, предлагающую создать таблицу в пошаговом режиме на базе типовых решений, имеющихся в Access;
· импортировать таблицу БД из файла какой-либо программы, например, FoxPro или Excel.
Задание имени поля
Имя поля задается в столбце Имя поля . Имя может содержать не более 64 знаков, при этом допустимы любые символы, кроме точки, восклицательного знака и угловых скобок. Повторение имен полей не допускается.
Определение типа данных
Для каждого поля необходимо указать тип данных, содержащихся в нем. Тип данных выбирается из списка, который можно вызвать щелчком мыши в столбце Тип данных . Access оперирует следующими типами данных:
Ø Текстовый – для хранения обычного текста с максимальным количеством символов 255.
Ø Поле MEMO – для хранения больших объемов текста до 65 535 символов.
Ø Числовой – для хранения действительных чисел.
Ø Дата/время – для хранения календарных дат и текущего времени.
Ø Денежный – эти поля содержат денежные суммы.
Ø Счетчик – для определения уникального системного ключа таблицы. Обычно используется для порядковой нумерации записей. При добавлении в таблицу новой записи значение этого поля увеличивается на 1 (единицу). Значения в таких полях не обновляются.
Ø Логический – для хранения данных, принимающих значения: Да или Нет.
Ø Поле объекта OLE – для хранения объектов, созданных в других приложениях.
Описание свойств полей
Как уже отмечалось, характеристики отдельных полей определяются в области свойств поля (вкладка Общие ). Каждое поле имеет определенный набор свойств – в зависимости от типа поля. Некоторые типы полей имеют схожие наборы свойств полей. Ниже перечислены основные свойства полей.
Ø Размер поля – максимальная длина текстового поля (по умолчанию 50 знаков) или тип данных числового поля. Рекомендуется задавать минимально допустимое значение этого свойства, потому что обработка данных меньшего размера выполняется быстрее.
Если тип данных – числовой, допустимы следующие значения свойства Размер поля :
Замечание . В случае преобразования поля в меньшее по размеру, может произойти потеря данных.
Ø Формат поля – формат отображения данных на экране или печати. Как правило, используется формат, заданный по умолчанию.
Ø Число десятичных знаков – задает для числового и денежного типа данных число десятичных знаков после запятой.
Ø Маска ввода – определяет форму, в которой данные вводятся в поле (средство автоматизации ввода данных).
Ø Подпись – обозначение для поля, которое будет использоваться для отображения поля в таблице, форме или отчете. Если это значение не определено, в качестве подписи будет взято имя поля.
Ø Значение по умолчанию – стандартное значение, которое автоматически вводится в поле при формировании новой записи данных.
Ø Условие на значение – задает ограничения на вводимые значения, тем самым позволяет осуществлять контроль над правильностью ввода данных.
Ø Сообщение об ошибке – задает текст сообщения, выводимый на экран в случае нарушения условия на значение.
Ø Обязательное поле – определяет, может ли данное поле содержать значения Null (т.е. оставаться пустым), или нужно обязательно вводить в это поле данные.
Ø Индексированное поле – используется для операций поиска и сортировки записей по значению, хранящемуся в данном поле, а также для автоматического исключения дублирования записей. Поля типа MEMO , Объект OLE и Гиперссылка не могут индексироваться.
Определение ключевого поля
После задания характеристик всех полей следует выбрать, по крайней мере, одно ключевое поле. Как правило, в качестве ключевых полей указываются поля, которые имеют неповторяющиеся данные или создаются поля с типом данных Счетчик . В любом случае, поле ключа не должно содержать повторяющихся данных. Чтобы определить ключ, необходимо выделить нужное поле (или поля) и нажать кнопку Ключевое поле Правка . Слева от маркера появится изображение ключа.
Сохранение таблицы
Перед вводом информации спроектированную таблицу необходимо сохранить: нажать кнопку Сохранить на панели инструментов или соответствующую команду в п. м. Файл и ввести название таблицы, после чего на экране появляется вопрос «Создать ключевое поле сейчас?» (Да или Нет)
Если выбирается ответ «Да », то Access создаст автоматически поле с именем «Код» и типом данных Счетчик , если «Нет », – то таблица будет создана без ключевого поля. В этом случае необходимо открыть созданную таблицу в режиме Конструктора и определить «вручную» ключевое поле.
Ввод данных
Чтобы перевести таблицу в режим ввода информации, нужно перейти в режим Таблицы . Поля заполняются последовательно. Переход от одного поля к другому удобно выполнять клавишей Tab (или комбинацией Shift+Tab – в обратном направлении). Если при проектировании таблицы для некоторых полей были предусмотрены значения по умолчанию, эти значения автоматически появятся в соответствующих полях. Записи в таблице можно перемещать, копировать и удалять теми же способами, что и в электронных таблицах, то есть сначала выделить строки, а потом выполнить необходимую операцию. Столбец можно выделить щелчком мыши по заголовку. Столбцы можно перемещать вправо и влево, пользуясь методом drag and drop (перетащить и бросить).
При необходимости можно вернуться в режим Конструктора . Это дает возможность что-либо подправить в структуре таблицы.
Сортировка данных в таблице
Данные, находящиеся в таблице, можно отсортировать в порядке возрастания или убывания. Для этого нужно поместить курсор мыши в любую ячейку столбца, значения которого будут отсортированы и из п. м. Записи выбрать команду Сортировка или нажать на панели соответствующую кнопку.
8. Создание связей между таблицами БД
Связь между таблицами устанавливается путем определения в одной таблице (подчиненной ) поля, соответствующего ключу другой таблицы (главной ). Установленная связь свяжет записи, содержащие в заданном поле одинаковые значения. Созданные связи позднее Access будет использовать в запросах, формах или отчетах.
Замечания.
Ø Оба связываемых поля должны иметь одинаковый тип данных .
Ø Свойства Размер поля для обоих связываемых полей числового типа должны быть одинаковыми.
Ø Если ключевым полем главной таблицы является поле с типом данных Счетчик , то это поле можно связать с числовым полем подчиненной таблицы. При этом для числового поля связанной таблицы для свойства Размер поля должно быть задано значение Длинное целое .
Целостность данных
Целостность данных – это набор правил, которые поддерживают корректность связей между записями в связанных таблицах и обеспечивают защиту данных от случайных изменений или удалений.
Эти правила включают:
Ø В подчиненной таблице нельзя вводить записи, которые не связаны с записью главной таблицы.
Ø В главной таблице нельзя изменять значение ключевого поля, если в подчиненной таблице существуют записи, которые с ней связаны.
Ø В главной таблице нельзя удалять записи, если в подчиненной таблице существуют связанные с ней записи.
Каскадные операции
Целостность данных в связанных таблицах обеспечивают каскадные операции двух видов:
Ø операции каскадного обновления;
Ø операции каскадного удаления.
Эти операции можно включать и выключать путем установки соответствующих флажков: «Каскадное обновление связанных полей» и «Каскадное удаление связанных полей».
Если установлен флажок «Каскадное обновление связанных полей», то любые изменения в значении ключевого поля в главной таблице, которая стоит на стороне «один» в отношениях 1:М, ведут к автоматическому обновлению соответствующих значений во всех связанных записях.
При установке флажка «Каскадное удаление связанных таблиц» при удалении записи из главной таблицы обеспечивается автоматическое удаление связанных записей в подчиненных таблицах.
Удаление (изменение) связей
Ø Открыть окно Схема данных ;
Ø активизировать левой кнопкой мыши связь, которую необходимо удалить (изменить);
Ø правой кнопкой мыши вызвать контекстно-зависимое меню и выбрать команду Удалить (Изменить ) соответственно.
9. Типы отношений между таблицами
Существует три типа отношений между таблицами:
Один-к-одному (1:1). Значению ключа в каждой записи в главной таблице могут соответствовать значения в связанном поле только в одной записи подчиненной таблицы. В этом случае связь между таблицами может быть установлена только через ключевые поля обеих таблиц.
Один-ко-многим (1:М). Значению ключа в каждой записи в главной таблице могут соответствовать значения в связанном поле (полях) в нескольких записях подчиненной таблицы. Этот тип отношения довольно часто используется в реляционных БД.
Много-ко-многим (М:М). Возникает между двумя таблицами, когда одна запись с первой таблицы А (выходная связь) может быть связана больше чем с одной записью другой таблицы В (принимающая), в свою очередь, одна запись с другой таблицы может быть связана больше чем с одной записью первой таблицы. Эта схема реализуется только при помощи третьей соединительной таблицы, ключ связи которой состоит, как минимум, из двух полей. Эти поля являются полями внешнего ключа в таблицах А и В. Первичный ключ для соединительной таблицы – это обычно комбинация из внешних ключей.
Если между таблицами имеются связи типа М:М, создается дополнительная таблица пересечений, с помощью которой связь М:М будет сведена к двум связям типа 1:М. Accеss не позволяет определить прямую связь М:М между двумя таблицами.
10. Формирование запросов
Запуск запроса
Для запуска запроса на исполнение из окна Конструктора надо на панели инструментов нажать кнопку «Запуск » ! или выполнить команду Запрос/Запуск . Результаты выборки данных по запросу выводятся на экран в режиме таблицы.
Формирование Условий отбора
Список операторов используемых при задании выражений следующий:
Ø операторысравнения:
= (равно)
<> (не равно)
> (больше)
>= (не меньше)
< (меньше)
<= (не больше)
BETWEEN – позволяет задать диапазон значений. Синтаксис: Between «Выражение»And «Выражение» (например: BETWEEN 10 And 20 означает тоже, что и логическое выражение>= 10 AND <= 20).
IN – позволяет задавать используемый для сравнения список значений (операндом является список, заключенный в круглые скобки). Например: IN ("Брест", "Минск", "Гродно") означает тоже самое, что и логическое выражение "Брест" OR "Минск" OR "Гродно".
Ø логические операторы:
АND (например: >=10 AND <=20)
OR (например: <50 OR >100)
NOT (например: Is Not Null – поле, содержащее какое-либо значение).
Ø операторLIKE – проверяет соответствие текстового или Memo поля по заданному шаблону символов.
Таблица символов шаблона
Примеры использования оператора Like :
LIKE "С *" – строки, начинающиеся с символа С;
LIKE "[ A - Z ] #" – любой символ от А до Z и цифра;
LIKE "[! 0 - 9 ABC] * # #" – строки, начинающиеся с любого символа кроме цифры или букв А, В, С и заканчивающиеся на 2 цифры;
Сложные критерии выборки
Часто приходится выбирать записи по условию, которое задается для нескольких полей таблицы или по нескольким условиям для одного поля. В этом случае применяются «И-запросы» (выбор записей только при условии выполнения всех условий) и«ИЛИ-запросы» (выбор записей при выполнении хотя бы одного из условий).
При задании «ИЛИ-запроса » каждое условие выборки должно размещаться на отдельной строке Бланка запроса .
При задании «И-запроса » каждое условие выборки должно размещаться на одной строке, но в разных полях Бланка запроса .
Эти операции могут быть заданы явно с помощью операторовOR иAND соответственно.
Функции Iif() и Format()
Функция IIf(условие; еслиИстина; еслиЛожь) – возвращает один из двух аргументов в зависимости от результата вычисления выражения.
Функция Format(выражение; инструкция форматирования) – возвращает строку, содержащую выражение, отформатированное согласно инструкциям форматирования.
Для выражений даты/времени можно применять следующие символы в инструкции форматирования:
I. Иерархическая модель
II. Сетевая модель
III. Реляционная модель
В реляционной модели информация представляется в виде прямоугольных таблиц. Каждая таблица состоит из строк и столбцов и имеет имя, уникальное внутри БД. В свою очередь, каждая строка (запись ) такой таблицы содержит информацию, относящуюся только к одному конкретному объекту, а каждый столбец (поле ) таблицы имеет уникальное для своей таблицы имя.
Реляционные базы данных (РБД), в отличие от иерархических и сетевых моделей , позволяют организовывать связи между таблицами в любой момент. Для этого в РБД реализован механизм внешних ключей . В каждой таблице БД имеется хотя бы одно поле, служащее ссылкой для другой таблицы. В терминологии РБД такие поля называются полями внешних ключей. С помощью внешних ключей можно связывать любые таблицы БД на любом этапе работы с БД.
4. Реляционная модель данных
Реляционная БД (РБД) – это совокупность простейших двумерных логически взаимосвязанных таблиц-отношений, состоящих из множества полей и записей, отражающих некоторую предметную область.
Реляционная модель данных была предложена Е. Коддом, известным американским специалистом в области баз данных. Основные концепции этой модели были впервые опубликованы в 1970 г. Будучи математиком по образованию, Кодд предложил использовать для обработки данных аппарат теории множеств (объединение, пересечение, разность, декартово произведение). Он показал, что любое представление данных сводится к совокупности двумерных таблиц особого вида, известного в математике как отношение (по-английски – relation, отсюда и название – реляционные базы данных).
Одна из главных идей Кодда заключалась в том, что связь между данными должны устанавливаться в соответствии с их внутренними логическими взаимоотношениями. Второй важный принцип, предложенный Коддом, заключается в том, что в реляционных системах одной командой могут обрабатываться целые файлы данных, в то время как ранее одной командой обрабатывалась только одна запись.
Базовые понятия реляционных баз данных (РБД)
* Отношение – информация об объектах одного типа, например, о клиентах, заказах, сотрудниках. В реляционной БД отношение хранится в виде таблицы.
* Атрибут – определенная часть информации о некотором объекте – например, адрес клиента или зарплата сотрудника. Атрибут обычно хранится в виде столбца (поля) таблицы.
* Тип данных – понятие, которое в реляционной модели полностью эквивалентно соответствующему понятию в алгоритмических языках. Набор поддерживаемых типов данных определяется СУБД и может сильно различаться в разных системах.
* Связь – способ, которым связана информация в одной таблице с информацией в другой таблице. Связи осуществляются с помощью совпадающих полей, которые называются ключом.
* Объединение – процесс объединения таблиц или запросов на основе совпадающих значений определенных атрибутов.
Правила (нормализации) построения реляционной БД
Нормализация представляет собой процесс реорганизации данных путем ликвидации повторяющихся групп и иных противоречий с целью приведения таблиц к виду, позволяющему осуществлять непротиворечивое и корректное редактирование данных. Окончательная цель нормализации сводится к получению такого проекта БД, в котором каждый факт появляется только в одном месте, т.е. исключена избыточность информации.
1. Каждое поле любой таблицы должно быть уникальным.
2. Каждая таблица должна иметь уникальный идентификатор (первичный ключ ), который может состоять из одного или нескольких полей таблицы.
3. Для каждого значения первичного ключа должно быть одно и только одно значение любого из столбцов данных, и это значение должно относиться к объекту таблицы (т.е. в таблице не должно быть данных, которые не относятся к объекту, определяемому первичным ключом, а также информация в таблице должна полностью описывать объект).
4. Должна иметься возможность изменять значения любого поля (не входящего в первичный ключ), и это не должно повлечь за собой изменения другого поля (т.е. не должно быть вычисляемых полей).
5. Системы управления базами данных (СУБД)
Поддержание баз данных в компьютерной среде осуществляют программные средства – системы управления базами данных (database management system), которые представляют собой совокупность программных и языковых средств общего или специализированного назначения, необходимых для создания баз данных на машинных носителях, поддержания их в актуальном состоянии и организации доступа к ним различных пользователей в условиях принятой технологии обработки данных.
СУБД – это управляющие программы, которые обеспечивают все манипуляции с базами данных: создание базы, ее ведение, ее использование многими пользователями и др., т. е. реализуют сложный комплекс функций по централизованному управлению базой данных и обслуживают интересы пользователей.
СУБД можно рассматривать как программную оболочку, которая находится между базой данных и пользователем. Она обеспечивает централизованный контроль защиты и целостности данных, доступ к данным, их обработку, формирование отчетов на основе базы данных и другие операции и процедуры.
Реляционная система управления базами данных (РСУБД)
Набор средств для управления РБД называется реляционной системой управления базами данных , которая может содержать утилиты, приложения, службы, библиотеки, средства создания приложений и другие компоненты. Будучи связанной посредством общих ключевых полей, информация в РБД может объединяться из множества таблиц в единый результирующий набор.
Основными функциями РСУБД являются:
· Определение данных – какая информация будет храниться, задать структуру БД и их тип.
· Обработка данных – можно выбирать любые поля, сортировать и фильтровать данные. Можно объединять данные и подводить итоги.
· Управление данными – корректировать и добавлять данные.
6. Общая характеристика СУБД ACCESS
Microsoft Access – это функционально полная реляционная СУБД, в которой предусмотрены все необходимые средства для определения и обработки данных, а также для управления ими при работе с большими объемами информации. Различные ее версии входят в состав программного пакета MS Office и работают в среде Windows (3.11/95/98/2000/XP).
Окно БД в Access
После создания нового файла БД или открытия существующего в рабочей области окна Access появляется окно базы данных:
|
Для лучшего понимания стоимостной оптимизации мы рассмотрим три распространённых способа объединения двух таблиц и увидим, как даже простой запрос на объединение может превратиться в кошмар для оптимизатора. В нашем рассмотрении мы будем ориентироваться на временнỳю сложность, хотя оптимизатор вычисляет «стоимость» в ресурсах процессора, памяти и операциях ввода/вывода. Просто временнáя сложность - понятие приблизительное, а для определения необходимых ресурсов процессора нужно подсчитывать все операции, включая добавление, операторы if, умножение, итерации и т.д.
Кроме того:
Мы говорили о них, когда рассматривали В-деревья. Как вы помните, индексы уже отсортированы. К слову, есть и другие виды индексов, например, битовые (bitmap index). Но они не дают выигрыша с точки зрения использования процессора, памяти и дисковой подсистемы по сравнению с индексами В-деревьев. Кроме того, многие современные БД могут динамически создавать временные индексы для текущих запросов, если это поможет уменьшить стоимость выполнения плана.
4.4.2. Способы обращений
Прежде чем применять операторы объединения, нужно сначала получить необходимые данные. Сделать это можно следующими способами.
В первой части статьи мы уже выяснили, что временнáя сложность запроса диапазона определяется как M + log(N), где N - количество данных в индексе, а М - предположительное количество строк внутри диапазона. Значения обеих этих переменных нам известны благодаря статистике. При сканировании диапазона считывается лишь часть индекса, поэтому данная операция стоит меньше по сравнению с полным сканированием.
SELECT LASTNAME, FIRSTNAME from PERSON WHERE AGE = 28
Если у нас есть индекс для колонки возраста, то оптимизатор воспользуется индексом для поиска всех 28-летних, а затем запросит ID соответствующих строк таблицы, поскольку индекс содержит информацию только о возрасте.
Допустим, у нас другой запрос:
SELECT TYPE_PERSON.CATEGORY from PERSON, TYPE_PERSON
WHERE PERSON.AGE = TYPE_PERSON.AGE
Для объединения с TYPE_PERSON будет использоваться индекс по колонке PERSON. Но поскольку мы не запрашивали информацию у таблицы PERSON, то и обращаться к ней по ID строк никто не будет.
Данный подход хорош только при небольшом количестве обращений, поскольку он дорог с точки зрения ввода/вывода. Если вам нужно часто обращаться по ID, то лучше воспользоваться полным сканированием.
Итак, мы знаем, как получить данные, пришла пора их объединить. Но сначала давайте определимся с новыми терминами: внутренние зависимости и внешние зависимости . Зависимостью может быть:
Чаще всего стоимость A JOIN B не равна стоимости B JOIN A.
Предположим, что внешняя зависимость содержит N элементов, а внутренняя - М. Как вы помните, оптимизатору известны эти значения благодаря статистике. N и M являются кардинальными числами зависимостей .
Работает он так: для каждой строки внешней зависимости ищутся совпадения по всем строкам внутренней зависимости.
Пример псеводокода:
Nested_loop_join(array outer, array inner)
for each row a in outer
for each row b in inner
if (match_join_condition(a,b))
write_result_in_output(a,b)
end if
end for
end for
Поскольку здесь двойная итерация, временнáя сложность определяется как О(N*M).
Для каждой из N строк внешней зависимости нужно считать М строк внешней зависимости. То есть этот алгоритм требует N + N*M чтений с диска. Если внутренняя зависимость достаточно мала, то можно поместить её целиком в память, и тогда на долю дисковой подсистемы придётся только M + N чтений. Так что рекомендуется делать внутреннюю зависимость как можно компактнее, чтобы загнать в память.
С точки зрения временнόй сложности разницы никакой.
Также можно заменить внутреннюю зависимость индексом, это позволит сэкономить операции ввода/вывода.
Если внутренняя зависимость не влезает в память целиком, можно использовать другой алгоритм, более экономно использующий диск.
Пример алгоритма:
// improved version to reduce the disk I/O.
nested_loop_join_v2(file outer, file inner)
for each bunch ba in outer
// ba is now in memory
for each bunch bb in inner
// bb is now in memory
for each row a in ba
for each row b in bb
if (match_join_condition(a,b))
write_result_in_output(a,b)
end if
end for
end for
end for
end for
В данном случае временнáя сложность остаётся той же, зато снижается количество обращений к диску: (количество групп внешней + количество групп внешней * количество групп внутренней). С увеличением размера групп ещё больше уменьшается количество обращений к диску.
Примечание: в этом алгоритме при каждом обращении считывается больший объём данных, но это не играет роли, поскольку обращения последовательные.
Алгоритм следующий:
(М / Х) * (N / X) + стоимость_создания_хэш-таблицы(М) + стоимость_хэш-функции * N
А если хэш-функция создаёт достаточное маленькие блоки, то временнáя сложность будет равна О(М + N).
Есть ещё один способ хэш-объединения, более экономно расходующий память и не требующий больше операций ввода/вывода:
Операцию объединения можно разделить на два этапа:
Алгоритм сортировки слиянием уже обсуждался выше, в данном случае он вполне себя оправдывает, если вам важно экономить память.
Но бывает, что наборы данных поступают уже отсортированными, например:
Эта операция очень похожа на операцию слияния при процедуре сортировки слиянием. Но вместо выбора всех элементов обеих зависимостей мы выбираем только равные элементы.
Если обе зависимости ещё нужно отсортировать, то временнáя сложность равна O(N * Log(N) + M * Log(M)).
Этот алгоритм работает хорошо, потому что обе зависимости уже отсортированы, и нам не приходится перемещаться по ним туда-обратно. Однако здесь допущено некоторое упрощение: алгоритм не обрабатывает ситуации, когда одни и те же данные встречаются многократно, то есть когда происходят многократные совпадения. В реальности используется более сложная версия алгоритма. Например:
MergeJoin(relation a, relation b) relation output integer a_key:=0; integer b_key:=0; while (a!=null and b!=null) if (a < b) a_key++; else if (a > b) b_key++; else //Join predicate satisfied write_result_in_output(a,b) //We need to be careful when we increase the pointers integer a_key_temp:=a_key; integer b_key_temp:=b_key; if (a != b) b_key_temp:= b_key + 1; end if if (b != a) a_key_temp:= a_key + 1; end if if (b == a && b == a) a_key_temp:= a_key + 1; b_key_temp:= b_key + 1; end if a_key:= a_key_temp; b_key:= b_key_temp; end if end while
Если бы существовал самый лучший способ объединения, то не существовало бы всех этих разновидностей. Так что ответ на этот вопрос зависит от кучи факторов:
4.4.4. Упрощённые примеры
Допустим, нам нужно объединить пять таблиц, чтобы получить полное представление о неких людях. Каждый человек может иметь:
SELECT * from PERSON, MOBILES, MAILS,ADRESSES, BANK_ACCOUNTS
WHERE
PERSON.PERSON_ID = MOBILES.PERSON_ID
AND PERSON.PERSON_ID = MAILS.PERSON_ID
AND PERSON.PERSON_ID = ADRESSES.PERSON_ID
AND PERSON.PERSON_ID = BANK_ACCOUNTS.PERSON_ID
Оптимизатору нужно найти наилучший способ обработки данных. Но есть две проблемы:
Исходя из описанного, какие есть варианты действий?
Так как же БД делает выбор?
4.4.5. Динамическое программирование, «жадный» алгоритм и эвристика
Реляционная БД использует разные подходы, о которых было сказано выше. И задачей оптимизатора является поиск подходящего решения в течение ограниченного времени. В большинстве случаев оптимизатор ищет не наилучшее, а просто хорошее решение.
Брутфорс может подойти в случае с маленькими запросами. А благодаря способам исключения ненужных вычислений даже для запросов среднего размера можно использовать грубую мужскую силу. Это называется динамическим программированием.
Его суть заключается в том, что многие планы исполнения очень похожи.
На этой иллюстрации все четыре плана используют поддерево A JOIN B. Вместо вычисления его стоимости для каждого плана, мы можем посчитать его лишь раз и затем использовать эти данные столько, сколько нужно. Иными словами, с помощью мемоизации мы решаем проблему перекрытия, то есть избегаем лишних вычислений.
Благодаря такому подходу вместо временнόй сложности (2*N)!/(N+1)! мы получаем «всего лишь» 3 N . Применительно к предыдущему примеру с четырьмя объединениями это означает уменьшение количества вариантов с 336 до 81. Если взять запрос с 8 объединениями (небольшой запрос), то уменьшение сложности будет с 57 657 600 до 6 561.
Если вы уже знакомы с динамическим программированием или алгоритмизацией, можете поиграться с этим алгоритмом:
Procedure findbestplan(S)
if (bestplan[S].cost infinite)
return bestplan[S]
// else bestplan[S] has not been computed earlier, compute it now
if (S contains only 1 relation)
set bestplan[S].plan and bestplan[S].cost based on the best way
of accessing S /* Using selections on S and indices on S */
else for each non-empty subset S1 of S such that S1 != S
P1= findbestplan(S1)
P2= findbestplan(S - S1)
A = best algorithm for joining results of P1 and P2
cost = P1.cost + P2.cost + cost of A
if cost < bestplan[S].cost
bestplan[S].cost = cost
bestplan[S].plan = “execute P1.plan; execute P2.plan;
join results of P1 and P2 using A”
return bestplan[S]
Динамическое программирование можно использовать и для более крупных запросов, но придётся вводить дополнительные правила (эвристику), чтобы уменьшить число возможных планов:
Жадные алгоритмы
Но если запрос очень велик, или если нам нужно крайне быстро получить ответ, используется другой класс алгоритмов - жадные алгоритмы.
В данном случае план исполнения запроса строится пошагово с помощью некоего правила (эвристики). Благодаря ему жадный алгоритм ищет наилучшее решение для каждого этапа по отдельности. План начинается с оператор JOIN, а затем на каждом этапе добавляется новый JOIN в соответствии с правилом.
Рассмотрим простой пример. Возьмём запрос с 4 объединениями 5 таблиц (A, B, C, D и E). Несколько упростим задачу и представим, что единственным вариантом является объединение с помощью вложенных алгоритмов. Будем использовать правило «применять объединение с наименьшей стоимостью».
Данный алгоритм называется «алгоритм ближайшего соседа ».
Не будем углубляться в подробности, но при хорошем моделировании сложности сортировки N*log(N) данная проблема может быть . Временнáя сложность алгоритма равна О(N*log(N)) вместо О(3 N) для полной версии с динамическим программированием. Если у вас большой запрос с 20 объединениями, то это будет 26 против 3 486 784 401. Большая разница, верно?
Но есть нюанс. Мы предполагаем, что если найдём наилучший способ объединения двух таблиц, то получим самую низкую стоимость при объединении результатом предыдущих объединений со следующими таблицами. Однако даже если A JOIN B будет самым дешёвым вариантом, то (A JOIN C) JOIN B может иметь стоимость ниже, чем (A JOIN B) JOIN C.
Так что если вам позарез нужно найти самый дешёвый план всех времён и народов, то можно посоветовать многократно прогонять жадные алгоритмы с использованием разных правил.
Другие алгоритмы
Если вы уже сыты по горло всеми этими алгоритмами, то можете пропустить эту главу. Она не обязательна для усвоения всего остального материала.
Многие исследователи занимаются проблемой поиска наилучшего плана исполнения запроса. Зачастую пытаются найти решения для каких-то конкретных задач и шаблонов. Например, для звездообразных объединений, исполнения параллельных запросов и т.д.
Ищутся варианты замены динамического программирования для исполнения больших запросов. Те же жадные алгоритмы являются подмножеством эвристических алгоритмов. Они действуют сообразно правилу, запоминают результат одного этапа и используют его для поиска лучшего варианта для следующего этапа. И алгоритмы, которые не всегда используют решение, найденное для предыдущего этапа, называются эвристическими.
В качестве примера можно привести генетические алгоритмы:
В БД используются и такие эвристические алгоритмы, как симулированная нормализация (Simulated Annealing), итеративное улучшение (Iterative Improvement), двухфазная оптимизация (Two-Phase Optimization). Но не факт, что они применяются в корпоративных системах, возможно, их удел - исследовательские продукты.
4.4.6. Настоящие оптимизаторы
Тоже необязательная глава, можно и пропустить.
Давайте отойдём от теоретизирования и рассмотрим реальные примеры. Например, как работает оптимизатор SQLite . Это «лёгкая» БД, использующая простую оптимизацию на основе жадного алгоритма с дополнительными правилами:
Прочие условия (GROUP BY, DISTINCT и т.д.) обрабатываются простыми правилами.
4.4.7. Кэш плана запросов
Поскольку составление плана требует времени, большинство БД хранят план в кэше плана запросов. Это помогает избежать ненужных вычислений одних и тех же этапов. БД должна знать, когда именно ей нужно обновить устаревшие планы. Для этого устанавливается некий порог, и если изменения в статистике его превышают, то план, относящийся к данной таблице, удаляется из кэша.
Диспетчер запросов исполняет запрос и нуждается в данных из таблиц и индексов. Он запрашивает их у диспетчера данных, но тут есть две трудности:
Вместо того, чтобы получать данные напрямую от файловой системы, исполнитель запросов обращается за ними к диспетчеру кэша. Тот использует содержащийся в памяти буферный пул, что позволяет радикально увеличить производительность БД. В цифрах это трудно оценить, поскольку многое зависит от того, что вам нужно:
Однако тут мы сталкиваемся с другой проблемой. Диспетчеру кэша нужно положить данные в память ДО того, как они понадобятся исполнителю запросов. Иначе тому придётся ждать их получения с медленного диска.
5.1.1. Упреждение
Исполнитель запросов знает, какие данные ему понадобятся, поскольку ему известен весь план, то, какие данные содержатся на диске и статистика.
Когда исполнитель обрабатывает первую порцию данных, он просит диспетчер кэша заранее подгрузить следующую порцию. А когда переходит к её обработке, то просит ДК подгрузить третью и подтверждает, что первую порцию можно удалить из кэша.
Диспетчер кэша хранит эти данные в буферном пуле. Он также добавляет к ним сервисную информацию (триггер, latch), чтобы знать нужны ли они ещё в буфере.
Иногда исполнитель не знает, какие данные ему будут нужны, или некоторые БД не имеют подобного функционала. Тогда используется спекулятивное упреждение (speculative prefetching) (например, если исполнитель запрашивает данные 1, 3, 5, то наверняка запросит в будущем 7, 9, 11) или последовательное упреждение (sequential prefetching) (в данном случае ДК просто подгружает с диска следующую по порядку порцию данных.
Нельзя забывать, что буфер ограничен объёмом доступной памяти. То есть для загрузки одних данных нам приходится периодически удалять другие. Заполнение и очистка кэша потребляет часть ресурсов дисковой подсистемы и сети. Если у вас есть часто исполняемый запрос, то было бы контрпродуктивно каждый раз загружать и очищать используемые им данные. Для решения данной проблемы в современных БД используется стратегия замены буфера.
5.1.2. Стратегии замены буфера
Большинство БД (по крайне мере, SQL Server, MySQL, Oracle и DB2) используют для этого алгоритм LRU (Least Recently Used). Он предназначен для поддержания в кэше тех данных, которые недавно использовались, а значит велика вероятность, что они могут понадобиться снова.
Для облегчения понимания работы алгоритма примем, что данные в буфере не блокируются триггерами (latch), а значит могут быть удалены. В нашем примере буфер может хранить три порции данных:
Улучшения алгоритма
Чтобы этого не произошло, в некоторых БД используются специальные правила. Согласно документации Oracle :
«Для очень больших таблиц обычно используется прямой доступ, то есть блоки данных считываются напрямую, чтобы избежать переполнения буфера кэша. Для таблиц среднего размера может использоваться как прямой доступ, так и чтение из кэша. Если система решит использовать кэш, то БД помещает блоки данных в конец списка LRU, чтобы предотвратить очистку буфера».
Также используется улучшенная версия LRU - LRU-K. В SQL Server применяется LRU-K при К = 2. Суть этого алгоритма в том, что при оценке ситуации он учитывает больше информации о прошлых операциях, а не только запоминает последние использованные данные. Буква К в названии означает, что алгоритм принимает во внимание, какие данные использовались последние К раз. Им присваивается определённый вес. Когда в кэш загружаются новые данные, то старые, но часто используемые не удаляются, потому что их вес выше. Конечно, если данные больше не используются, то они всё-таки будут удалены. И чем дольше данные остаются невостребованными, тем сильнее уменьшается со временем их вес.
Вычисление веса довольно накладно, поэтому в SQL Server используется LRU-K при К равном всего лишь 2. При некотором увеличении значения К эффективность алгоритма улучшается. Вы можете ближе познакомиться с ним благодаря .
Другие алгоритмы
Конечно, LRU-K не единственное решение. Существуют также 2Q и CLOCK (оба похожи на LRU-K), MRU (Most Recently Used, в котором используется логика LRU, но применяется другое правило, LRFU (Least Recently and Frequently Used) и т.д. В некоторых БД можно выбирать, какой алгоритм будет использоваться.
5.1.3. Буфер записи
Мы говорили только о буфере чтения, но БД используют и буферы записи, которые накапливают данные и сбрасывают на диск порциями, вместо последовательной записи. Это позволяет экономить операции ввода/вывода.
Помните, что буферы хранят страницы
(неделимые единицы данных), а не ряды из таблиц. Страница в буферном пуле называется «грязной», если она модифицирована, но не записана на диск. Есть много разных алгоритмов, с помощью которых выбирается время записи грязных страниц. Но это во многом связано с понятием транзакций.
5.2.1. «Под кислотой» (игра слов, если кто не понял)
ACID-транзакция (Atomicity, Isolation, Durability, Consistency) - это элементарная операция, единица работы, которая удовлетворяет 4 условиям:
Во время выполнения какой-либо транзакции можно исполнять различные SQL-запросы на чтение, создание, обновление и удаление данных. Проблемы начинаются, когда две транзакции используют одни и те же данные. Классический пример - перевод денег со счёта А на счёт Б. Допустим, у нас есть две транзакции:
Многие БД не обеспечивают полную изолированность по умолчанию, поскольку это приводит к огромным издержкам в производительности. В SQL используется 4 уровня изолированности:
Например, транзакция А выполняет
SELECT count(1) from TABLE_X
Потом транзакция Б добавляет в таблицу Х и коммитит новые данные. И если после этого транзакция А снова выполняет count(1), то результат будет уже другим.
Это называется фантомным чтением.
Это называется неповторяемым чтением (non-repeatable read).
Это называется грязным чтением.
Большинство БД добавляют собственные уровни изолированности (например, на основе снэпшотов, как в PostgreSQL, Oracle и SQL Server). Также во многих БД не реализованы все четыре вышеописанных уровня, особенно чтение незафиксированных данных.
Пользователь или разработчик может сразу же после установления соединения переопределить уровень изолированности по умолчанию. Для этого достаточно добавить очень простую строчку кода.
5.2.2. Управление параллелизмом
Главное, для чего нам нужны изолированность, согласованность и атомарность, это возможность осуществлять операции записи над одними и теми же данными (добавлять, обновлять и удалять).
Если все транзакции будут только читать данные, то смогут работать одновременно, не влияя на другие транзакции.
Если хотя бы одна транзакция изменяет данные, читаемые другими транзакциями, то БД нужно найти способ скрыть от них эти изменения. Также нужно удостовериться, что сделанные изменения не будут удалены другими транзакциями, которые не видят изменённых данных.
Это называется управлением параллелизмом.
Проще всего просто выполнять транзакции поочерёдно. Но такой подход обычно неэффективен (задействуется лишь одно ядро одного процессора), и к тому же теряется возможность масштабирования.
Идеальный способ решения проблемы выглядит так (при каждом создании или отмене транзакции):
5.2.3. Диспетчер блокировок
Для решения вышеописанной проблемы во многих БД используются блокировки (locks) и/или версионность данных.
Если транзакции нужны какие-то данные, то она блокирует их. Если другой транзакции они тоже потребовались, то её придётся ждать, пока первая транзакция не снимет блокировку.
Это называется эксклюзивной блокировкой.
Но слишком расточительно использовать эксклюзивные блокировки в случаях, когда транзакциям нужно всего лишь считать данные. Зачем мешать чтению данных? В таких случаях используются совместные блокировки. Если транзакции нужно считать данные, они применяет к ним совместную блокировку и читает. Это не мешает другим транзакциям тоже применять совместные блокировки и читать данные. Если же какой-то из них нужно изменить данные, то ей придётся подождать, пока все совместные блокировки не будут сняты. Только после этого она сможет применить эксклюзивную блокировку. И тогда уже всем остальным транзакциям придётся ждать её снятия, чтобы считывать эти данные.
Диспетчер блокировок - это процесс, который применяет и снимает блокировки. Они хранятся в хэш-таблице (ключами являются блокируемые данные). Диспетчер знает для всех данных, какие транзакции применили блокировки или ждут их снятия.
Взаимная блокировка (deadlock)
Использование блокировок может привести к ситуации, когда две транзакции бесконечно ожидают снятия блокировок:
Здесь транзакция А эксклюзивно заблокировала данные 1 и ожидает освобождения данных 2. В то же время транзакция Б эксклюзивно заблокировала данные 2 и ожидает освобождения данных 1.
При взаимной блокировке диспетчер выбирает, какую транзакцию отменить (откатить назад). И решение принять не так просто:
Представим хэш-таблицу в виде диаграммы, как на иллюстрации выше. Если на диаграмме присутствует циклическая связь, то взаимная блокировка подтверждена. Но поскольку проверять на наличие циклов достаточно дорого (ведь диаграмма, на которой отражены все блокировки, будет весьма большой), зачастую используется более простой подход: использование таймаутов. Если блокировка не снимается в течение определённого времени, значит транзакция вошла в состояние взаимной блокировки.
Перед наложением блокировки диспетчер также может проверить, не приведёт ли это к возникновению взаимной блокировки. Но чтобы однозначно на это ответить, тоже придётся потратиться на вычисления. Поэтому подобные предпроверки зачастую представлены в виде набора базовых правил.
Двухфазная блокировка
Проще всего полная изолированность обеспечивается, когда блокировка применяется в начале и снимается в конце транзакции. Это означает, что транзакции перед началом приходится дожидаться снятия всех блокировок, а применённые ею блокировки снимаются лишь по завершении. Такой подход можно применять, но тогда теряется куча времени на все эти ожидания снятия блокировок.
В DB2 и SQL Server применяется протокол двухфазной блокировки, при котором транзакция делится на две фазы:
До транзакции А X = 1 и Y = 1. Она обрабатывает данные Y = 1, которые были изменены транзакцией В уже после начала транзакции А. В связи с принципом изолированности транзакция А должна обрабатывать Y = 2.
Цели, решаемые с помощью этих двух простых правил:
Конечно, реальные БД используют более сложные системы, больше видов блокировок и с большей гранулярностью (блокировки строк, страниц, партиций, таблиц, табличных пространств), но суть та же.
Версионность данных
Ещё один способ решения проблемы конфликта транзакций - использование версионности данных.
Оба подхода - блокировки и версионность - имеют плюсы и минусы, многое зависит от того, в какой ситуации они применяются (больше чтений или больше записей). Можете изучить очень хорошую презентацию , посвящённую реализации мультиверсионного управления параллелизмом в PostgreSQL.
В некоторых БД (DB2 до версии 9.7, SQL Server) используются только блокировки. Другие, вроде PostgreSQL, MySQL и Oracle, используются комбинированные подходы.
Примечание: версионность оказывает интересное влияние на индексы. Иногда в уникальном индексе появляются дубликаты, индекс может содержаться больше записей, чем строк в таблице и т.д.
Если часть данных считывается при одном уровне изолированности, а потом он увеличивается, то вырастает количество блокировок, а значит теряется больше времени на ожидания транзакций. Поэтому большинство БД не используют по умолчанию максимальный уровень изолированности.
Как обычно, за более подробной информацией обращайтесь к документации: MySQL , PostgreSQL , Oracle .
5.2.4. Диспетчер логов
Как мы уже знаем, ради увеличения производительности БД хранит часть данных в буферной памяти. Но если сервер падает во время коммита транзакции, то находящиеся в памяти данные будут потеряны. А это нарушает принцип надёжности транзакций.
Конечно, можно всё писать на диск, но при падении вы останетесь с недописанными данными, а это уже нарушение принципа атомарности.
Любые изменения, записанные транзакцией, должны быть отменены или завершены.
Это делается двумя способами:
В больших БД с многочисленными транзакциями теневые копии/страницы занимают невероятно много места в дисковой подсистеме. Поэтому в современных БД используется лог транзакции. Он должен размещаться в защищённом от сбоев хранилище.
Большинство продуктов (в частности, Oracle, SQL Server , DB2 , PostgreSQL , MySQL и SQLite) работают с логом транзакции через протокол WAL (Write-Ahead Logging). Данные протокол содержит три правила:
За выполнением этих правил следит диспетчер логов. Логически он расположен между диспетчером кэша и диспетчером доступа к данным. Диспетчер логов регистрирует каждую операцию, выполняемую транзакциями, до момента записи на диск. Вроде верно?
НЕВЕРНО! После всего, через что мы с вами прошли в этой статье, пора бы уже запомнить, что всё связанное с БД подвергается проклятью «эффекта базы данных». Если серьёзно, то проблема в том, что нужно найти способ писать в лог, при этом сохраняя хорошую производительность. Ведь если лог транзакций работает медленно, то он тормозит все остальные процессы.
ARIES
В 1992 год исследователи из IBM создали расширенную версию WAL, которую назвали ARIES. В том или ином виде ARIES используется большинством современных БД. Если вы захотите поглубже изучить этот протокол, можете проштудировать соответствующую работу .
Итак, ARIES расшифровывается как A lgorithms for R ecovery and I solation E xploiting S emantics. У этой технологии две задачи:
Как это возможно? Чтобы ответить на это, нужно сначала разобраться с тем, какая информация сохраняется в логе.
Логи
Каждая операция (добавления/удаления/изменения) во время выполнения транзакции ведёт к появлению записи в логе. Запись содержит:
Например, если была проведена операция обновления, то в UNDO записывается предыдущее значение/состояние изменённого элемента (физический UNDO) или обратная операция, позволяющая вернуться в предыдущее состояние (логический UNDO). В ARIES используется только логический, с физическим работать очень тяжело.
Насколько известно, UNDO не используется только в PostgreSQL. Вместо этого используется сборщик мусора, убирающий старые версии данных. Это связано с реализацией версионности данных в этой СУБД.
Чтобы вам было легче представить состав записи в логе, вот визуальный упрощённый пример, в котором выполняется запрос UPDATE FROM PERSON SET AGE = 18;. Пусть он исполняется в транзакции номер 18:
Каждый лог имеет уникальный LSN. Связанные логи относятся к одной и той же транзакции, причём линкуются они в хронологическом порядке (последний лог списка относится к последней операции).
Буфер логов
Чтобы запись в лог не превратилась в узкое место системы, используется буфер логов.
Когда исполнитель запросов запрашивает модифицированные данные:
Политики STEAL и FORCE
Для увеличения производительности шаг номер 5 нужно делать после коммита, поскольку в случае сбоя всё ещё возможно восстановить транзакцию с помощью REDO. Это называется «политика NO-FORCE».
Но БД может выбрать и политику FORCE ради уменьшения нагрузки во время восстановления. Тогда шаг номер 5 выполняется до коммита.
Также БД выбирает, записывать ли данные на диск пошагово (политика STEAL) или, если диспетчер буфера должен дождаться коммита, записать всё разом (NO-STEAL). Выбор зависит от того, что вам нужно: быструю запись с долгим восстановлением или быстрое восстановление?
Как упомянутые политики влияют процесс восстановления:
Итак, как нам можно использовать наши замечательные логи? Предположим, что новый сотрудник порушил БД (правило №1: всегда виноват новичок!). Вы её перезапускаете и начинается процесс восстановления.
ARIES восстанавливает в три этапа:
Если LSN(страницы_на_диске)>=LSN(записи_в_логе), то значит данные уже были записаны на диск перед сбоем. Но значение было перезаписано операцией, которая была выполнена после записи в лог и до сбоя. Так что ничего не сделано, на самом деле.
Если LSN(страницы_на_диске) Повтор выполняется даже для транзакций, которые будут откачены, потому что это упрощает процесс восстановления. Но современные БД наверняка этого не делают.
Если транзакция отменена «вручную», или диспетчером блокировок, или из-за сбоя сети, то этап анализа не нужен. Ведь информация для REDO и UNDO содержится в двух таблицах, размещённых в памяти:
Этап анализа нужен как раз для восстановления обеих таблиц с помощью информации из лога транзакций. Для ускорения этого этапа в ARIES используются контрольные точки. На диск время от времени записывается содержимое обеих таблиц, а также последний на момент записи LSN. Так что во время восстановления анализируются только логи, следующие после этого LSN.
Если вы внимательно прочитали весь вышеизложенный материал, то наверняка получили представление о том, насколько велики возможности баз данных. Однако в этой статье не затронуты другие важные проблемы:
Итак, если вас кто-нибудь спросит, а как же работают базы данных, то вместо того, чтобы плюнуть и уйти, вы можете ответить:
Теги: Добавить метки
Примечание переводчика: хоть статья довольно старая (опубликована 2 года назад) и носит громкое название, в ней все же дается хорошее представление о различиях реляционных БД и NoSQL БД, их преимуществах и недостатках, а также приводится краткий обзор нереляционных хранилищ.
В последнее время появилось много нереляционных баз данных. Это говорит о том, что если вам нужна практически неограниченная масштабируемость по требованию, вам нужна нереляционная БД.
Если это правда, значит ли это, что могучие реляционные БД стали уязвимы? Значит ли это, что дни реляционных БД проходят и скоро совсем пройдут? В этой статье мы рассмотрим популярное течение нереляционных баз данных применительно к различным ситуациям и посмотрим, повлияет ли это на будущее реляционных БД.
Реляционные базы данных существуют уже около 30 лет. За это время вспыхивало несколько революций, которые должны были положить конец реляционным хранилищам. Конечно, ни одна из этих революций не состоялась, и одна из них ни на йоту не поколебала позиции реляционных БД.
Причины такого доминирования неочевидны. На протяжении всего существования реляционных БД они постоянно предлагали наилучшую смесь простоты, устойчивости, гибкости, производительности, масштабируемости и совместимости в сфере управлении данными.
Однако чтобы обеспечить все эти особенности, реляционные хранилища невероятно сложны внутри. Например, простой SELECT запрос может иметь сотни потенциальных путей выполнения, которые оптимизатор оценит непосредственно во время выполнения запроса. Все это скрыто от пользователей, однако внутри РСУБД создает план выполнения, основывающийся на вещах вроде алгоритмов оценки стоимости и наилучшим образом отвечающий запросу.
Сегодня ситуация немного другая. Разнообразие приложений растет, а с ним растет и важность перечисленных особенностей. И с ростом количества баз данных, одна особенность начинает затмевать все другие. Это масштабируемость. Поскольку все больше приложений работают в условиях высокой нагрузки, например, таких как веб-сервисы, их требования к масштабируемости могут очень быстро меняться и сильно расти. Первую проблему может быть очень сложно разрешить, если у вас есть реляционная БД, расположенная на собственном сервере. Предположим, нагрузка на сервер за ночь увеличилась втрое. Как быстро вы сможете проапгрейдить железо? Решение второй проблемы также вызывает трудности в случае использования реляционных БД.
Реляционные БД хорошо масштабируются только в том случае, если располагаются на единственном сервере. Когда ресурсы этого сервера закончатся, вам необходимо будет добавить больше машин и распределить нагрузку между ними. И вот тут сложность реляционных БД начинает играть против масштабируемости. Если вы попробуете увеличить количество серверов не до нескольких штук, а до сотни или тысячи, сложность возрастет на порядок, и характеристики, которые делают реляционные БД такими привлекательными, стремительно снижают к нулю шансы использовать их в качестве платформы для больших распределенных систем.
Чтобы оставаться конкурентоспособными, вендорам облачных сервисов приходится как-то бороться с этим ограничением, потому что какая ж это облачная платформа без масштабируемого хранилища данных. Поэтому у вендоров остается только один вариант, если они хотят предоставлять пользователям масштабируемое место для хранения данных. Нужно применять другие типы баз данных, которые обладают более высокой способностью к масштабированию, пусть и ценой других возможностей, доступных в реляционных БД.
Эти преимущества, а также существующий спрос на них, привел к волне новых систем управления базами данных.
Впрочем, как бы вы его не называли, этот «новый» тип баз данных не такой уж новый и всегда применялся в основном для приложений, для которых использование реляционных БД было бы непригодно. Однако без потребности веба и «облака» в масштабируемости, эти системы оставались не сильно востребованными. Теперь же задача состоит в том, чтобы определить, какой тип хранилища больше подходит для конкретной системы.
Реляционные БД и хранилища типа ключ-значение отличаются коренным образом и предназначены для решения разных задач. Сравнение характеристик позволит всего лишь понять разницу между ними, однако начнем с этого:
Характеристики хранилищ
Реляционная БД | Хранилище типа ключ-значение |
---|---|
База данных состоит из таблиц, таблицы содержат колонки и строки, а строки состоят из значений колонок. Все строки одной таблицы имеют единую структуру. |
Для доменов можно провести аналогию с таблицами, однако в отличие от таблиц для доменов не определяется структура данных. Домен – это такая коробка, в которую вы можете складывать все что угодно. Записи внутри одного домена могут иметь разную структуру. |
Модель данных 1 определена заранее. Является строго типизированной, содержит ограничения и отношения для обеспечения целостности данных. |
Записи идентифицируются по ключу, при этом каждая запись имеет динамический набор атрибутов, связанных с ней. |
Модель данных основана на естественном представлении содержащихся данных, а не на функциональности приложения. |
В некоторых реализация атрибуты могут быть только строковыми. В других реализациях атрибуты имеют простые типы данных, которые отражают типы, использующиеся в программировании: целые числа, массива строк и списки. |
Модель данных подвергается нормализации, чтобы избежать дублирования данных. Нормализация порождает отношения между таблицами. Отношения связывают данные разных таблиц. |
Между доменами, также как и внутри одного домена, отношения явно не определены. |
Доступ к данным
Реляционная БД | Хранилище типа ключ-значение |
---|---|
Данные создаются, обновляются, удаляются и запрашиваются с использованием языка структурированных запросов (SQL). |
Данные создаются, обновляются, удаляются и запрашиваются с использованием вызова API методов. |
SQL-запросы могут извлекать данные как из одиночной таблица, так и из нескольких таблиц, используя при этом соединения (join’ы). |
Некоторые реализации предоставляют SQL-подобный синтаксис для задания условий фильтрации. |
SQL-запросы могут включать агрегации и сложные фильтры. |
Зачастую можно использовать только базовые операторы сравнений (=, !=, <, >, <= и =>). |
Реляционная БД обычно содержит встроенную логику, такую как триггеры, хранимые процедуры и функции. |
Вся бизнес-логика и логика для поддержки целостности данных содержится в коде приложений. |
Благодаря тому, что такие хранилища легко и динамически расширяются, они также пригодятся вендорам, которые предоставляют многопользовательскую веб-платформу хранения данных. Такая база представляет относительно дешевое средство хранения данных с большим потенциалом к масштабируемости. Пользователи обычно платят только за то, что они используют, однако их потребности могут вырасти. Вендор сможет динамически и практически без ограничений увеличить размер платформы, исходя из нагрузки.
Другие аргументы в пользу использования хранилищ типа ключ-значение, наподобие «Реляционные базы могут стать неуклюжими» (кстати, я без понятия, что это значит), являются менее убедительными. Но прежде чем стать сторонником таких хранилищ, ознакомьтесь со следующим разделом.
Другое преимущество реляционных БД заключается в том, что они вынуждают вас пройти через процесс разработки модели данных. Если вы хорошо спроектировали модель, то база данных будет содержать логическую структуру, которая полностью отражает структуру хранимых данных, однако расходится со структурой приложения. Таким образом, данные становятся независимы от приложения. Это значит, что другое приложение сможет использовать те же самые данные и логика приложения может быть изменена без каких-либо изменений в модели базы. Чтобы проделать то же самое с хранилищем типа ключ-значение, попробуйте заменить процесс проектирования реляционной модели проектированием классов, при котором создаются общие классы, основанные на естественной структуре данных.
И не забудьте о совместимости. В отличие от реляционных БД, хранилища, ориентированные на использование в «облаке», имеют гораздо меньше общих стандартов. Хоть концептуально они и не отличаются, они все имеют разные API, интерфейсы запросов и свою специфику. Поэтому вам лучше доверять вашему вендору, потому что в случае чего, вы не сможете легко переключиться на другого поставщика услуг. А учитывая тот факт, что почти все современные хранилища типа ключ-значение находятся в стадии бета-версий 2 , доверять становится еще рискованнее, чем в случае использования реляционных БД.
Эти ограничения не страшны для простой логики (создание, обновление, удаление и извлечение небольшого количества записей). Но что если ваше приложение становится популярным? Вы получили много новых пользователей и много новых данных, и теперь хотите сделать новые возможности для пользователей или каким-то образом извлечь выгоду из данных. Тут вы можете жестко обломаться с выполнением даже простых запросов для анализа данных. Фичи наподобие отслеживания шаблонов использования приложения или системы рекомендаций, основанной на истории пользователя, в лучшем случае могут оказаться сложны в реализации. А в худшем - просто невозможны.
В таком случае для аналитики лучше сделать отдельную базу данных, которая будет заполняться данными из вашего хранилища типа ключ-значение. Продумайте заранее, каким образом это можно будет сделать. Будете ли вы размещать сервер в облаке или у себя? Не будет ли проблем из-за задержек сигнала между вами и вашим провайдером? Поддерживает ли ваше хранилище такой перенос данных? Если у вас 100 миллионов записей, а за один раз вы можете взять 1000 записей, сколько потребуется на перенос всех данных?
Однако не ставьте масштабируемость превыше всего. Она будет бесполезна, если ваши пользователи решат пользоваться услугами другого сервиса, потому что тот предоставляет больше возможностей и настроек.
Одной из ключевых особенностей SimpleDB является использование модели конечной констистенции (eventual consistency model). Эта модель подходит для многопоточной работы, однако следует иметь в виду, что после того, как вы изменили значение атрибута в какой-то записи, при последующих операциях чтения эти изменения могут быть не видны. Вероятность такого развития событий достаточно низкая, тем не менее, о ней нужно помнить. Вы же не хотите продать последний билет пяти покупателям только потому, что ваши данные были неконсистентны в момент продажи.
Скорее всего вы будете использовать именно это хранилище данных при разработке с помощью Google AppEngine. Однако в отличии от SimpleDB, вы не сможете использовать AppEngine Datastore (или BigTable) вне веб-сервисов Google.
Для всех остальных требований лучше выбрать старые добрые реляционные СУБД. Так обречены ли они? Конечно, нет. По крайней мере, пока.
1 - по моему мнению, здесь больше подходит термин «структура данных», однако оставил оригинальное data model.
2 - скорее всего, автор имел в виду, что по своим возможностям нереляционные БД уступают реляционным.
3 - возможно, данные уже устарели, статья датируется февралем 2009 года.
Появление компьютерной техники в нашей современности ознаменовало информационный переворот во всех сферах человеческой деятельности. Но для того, чтобы вся информация не стала ненужным мусором в глобальной сети Интернет, была изобретена система баз данных, в которой материалы сортируются, систематизируются, в результате чего их легко отыскать и представить последующей обработке. Существуют три основные разновидности - выделяют базы данных реляционные, иерархические, сетевые.
Возвращаясь к возникновению баз данных, стоит сказать, что этот процесс был достаточно сложным, он берет свое начало вместе с развитием программируемого оборудования обработки информации. Поэтому неудивительно, что количество их моделей на данный момент достигает более 50, но основными из них считаются иерархическая, реляционная и сетевая, которые и до сих пор широко применяются на практике. Что же они собой представляют?
Иерархическая имеет древовидную структуру и составляется из данных разных уровней, между которыми существуют связи. Сетевая модель БД представляет собой более сложный шаблон. Ее структура напоминает иерархическую, а схема расширенная и усовершенствованная. Разница между ними в том, что потомственные данные иерархической модели могут иметь связь только с одним предком, а у сетевой их может быть несколько. Структура реляционной базы данных гораздо сложнее. Поэтому ее следует разобрать более подробно.
Такая модель была разработана в 1970-х годах доктором науки Эдгаром Коддом. Она представляет собой логически структурированную таблицу с полями, описывающую данные, их отношения между собой, операции, произведенные над ними, а главное - правила, которые гарантируют их целостность. Почему модель называется реляционной? В ее основе лежат отношения (от лат. relatio) между данными. Существует множество определений этого типа базы данных. Реляционные таблицы с информацией гораздо проще систематизировать и придать обработке, нежели в сетевой или иерархической модели. Как же это сделать? Достаточно знать особенности, структуру модели и свойства реляционных таблиц.
Для того чтобы создать собственную СУБД, следует воспользоваться одним из инструментов моделирования, продумать, с какой информацией вам необходимо работать, спроектировать таблицы и реляционные одно- и множественные связи между данными, заполнить ячейки сущностей и установить первичный, внешние ключи.
Моделирование таблиц и проектирование реляционных баз данных производится посредством бесплатных инструментов, таких как Workbench, PhpMyAdmin, Case Studio, dbForge Studio. После детальной проектировки следует сохранить графически готовую реляционную модель и перевести ее в готовый SQL-код. На этом этапе можно начинать работу с сортировкой данных, их обработку и систематизацию.
Каждый источник по-своему описывает ее элементы, поэтому для меньшей путаницы хотелось бы привести небольшую подсказку:
Для перехода к свойствам реляционной базы данных следует знать, из каких базовых компонентов она состоит и для чего они предназначены.
Теперь, зная составляющие элементы таблицы, можно переходить к свойствам реляционной модели database:
Исходя из свойств понятно, что значения атрибутов должны быть одинакового типа, длины. Рассмотрим особенности значений атрибутов.
Названия полей должны быть уникальными в рамках одной сущности. Типы атрибутов или полей реляционных баз данных описывают, данные какой категории хранятся в полях сущностей. Поле реляционной базы данных должно иметь фиксированный размер, исчисляемый в символах. Параметры и формат значений атрибутов определяют манеру исправления в них данных. Еще есть такое понятие, как "маска", или "шаблон ввода". Оно предназначено для определения конфигурации ввода данных в значение атрибута. Непременно при записи неправильного в поле должно выдаваться извещение об ошибке. Также на элементы полей накладываются некоторые ограничения - условия проверки точности и безошибочности ввода данных. Существует некоторое обязательное значение атрибута, которое однозначно должно быть заполнено данными. Некоторые строки атрибутов могут быть заполнены NULL-значениями. Разрешается ввод пустых данных в атрибуты полей. Как и извещение об ошибке, есть значения, которые заполняются системой автоматически - это данные по умолчанию. Для ускорения поиска любых данных предназначено индексированное поле.
Для детального понимания модели с помощью SQL лучше всего рассмотреть схему на примере. Нам уже известно, что представляет собой реляционная БД. Запись в каждой таблице - это один элемент данных. Чтобы предотвратить избыточность данных, необходимо провести операции нормализации.
1. Значение названия поля для реляционной таблицы должно быть уникальным, единственным в своем роде (первая нормальная форма - 1НФ).
2. Для таблицы, которая уже приведена к 1НФ, наименование любого неидентифицирующего столбца должно быть зависимым от уникального идентификатора таблицы (2НФ).
3. Для всей таблицы, что уже находится в 2НФ, каждое неидентифицирующее поле не может зависеть от элемента другого неопознанного значения (3НФ сущности).
Существует 2 основных реляционных табличек:
Первичный и вторичный ключи определяют потенциальные отношения базы данных. Реляционные связи модели данных могут иметь только один потенциальный ключ, это и будет primary key. Что же он собой представляет? Первичный ключ - это столбец сущности или набор атрибутов, благодаря которому можно получить доступ к данным конкретной строки. Он должен быть уникальным, единственным, а его поля не могут содержать пустых значений. Если первичный ключ состоит всего из одного атрибута, тогда он называется простым, в ином случае будет составляющим.
Кроме первичного ключа, существует и внешний (foreign key). Многие не понимают, какая между ними разница. Разберем их более детально на примере. Итак, существует 2 таблицы: «Деканат» и «Студенты». Сущность «Деканат» содержит поля: «ID студента», «ФИО» и «Группа». Таблица «Студенты» имеет такие значения атрибутов, как «ФИО», «Группа» и «Средний бал». Так как ID студента не может быть одинаковым для нескольких студентов, это поле и будет первичным ключом. «ФИО» и «Группа» из таблицы «Студенты» могут быть одинаковыми для нескольких человек, они ссылаются на ID номер студента из сущности «Деканат», поэтому могут быть использованы в качестве внешнего ключа.
Для наглядности приведем простой пример реляционной модели базы данных, состоящей из двух сущностей. Существует таблица с названием «Деканат».
Необходимо провести связи, чтобы получилась полноценная реляционная база данных. Запись "ИН-41", как и "ИН-72", может присутствовать не единожды в табличке "Деканат", также фамилия, имя и отчество студентов в редких случаях могут совпадать, поэтому данные поля никак нельзя сделать первичным ключом. Покажем сущность «Студенты».
Как мы видим, типы полей реляционных баз данных совершенно различаются. Присутствуют как цифровые записи, так и символьные. Поэтому в настройках атрибутов следует указывать значения integer, char, vachar, date и другие. В таблице "Деканат" уникальным значением является только ID студента. Данное поле можно взять за первичный ключ. ФИО, группа и телефон из сущности "Студенты" могут быть взяты как внешний ключ, ссылающийся на ID студента. Связь установлена. Это пример модели со связью «один к одному». Гипотетически одна из таблиц лишняя, их можно легко объединить в одну сущность. Чтобы ID-номера студентов не стали всеобще известными, вполне реально существование двух таблиц.