Фрагментация данных и размещение
Распределенная база данных (РБД) представляет собой базу данных, отдельные части которой размещены (возможно, с дублированием) на нескольких ЭВМ сети. При проектировании РБД обязательно выполняется фрагментация и размещение данных.
Фрагментация – это разбиение целостного объекта глобального типа на несколько частей, называемых фрагментами. Размещение – это отображение каждого фрагмента на одну или более ЭВМ. Конфигурация и эксплуатационные характеристики РБД существенно зависят от размещения фрагментов данных по ЭВМ сети. Размещение объектов может быть избыточным и безызбыточным. В последнем случае каждый фрагмент отображается точно на одну ЭВМ, а в первом – на одну или более ЭВМ.
В случае избыточного размещения разработчик должен выбрать степень дублирования каждого фрагмента. Выгоды, получаемые от дублирования, пропорциональны объему операций выборки данных и обратно пропорциональны количеству обновлений. При большом количестве производящихся выборок в случае отказа от передач по сети получаем очевидное ускорение, особенно если данные остаются практически неизменными. Если же данные часто меняются, то поддержание целостности БД требует распространения изменений на все копии данных. Однако если система допускает временные нарушения целостности, стратегия дублирования фрагментов становится более предпочтительной. Преимущества дублирования уменьшаются с увеличением стоимости хранения фрагментов. Вместе с тем дублирование повышает устойчивость системы против отказов, так как независимая потеря нескольких копий одной и той же информации маловероятна; в случае порчи обычных рабочих копий в прикладной системе могут использоваться другие копии.
И к РБД и к обычным БД предъявляются общие требования такие, как обеспечение быстрой обработки типовых и незапланированных запросов, безопасности, секретности, прозрачности логической и физической структуры данных и др.,
Помимо этого к РБД в связи с распределенностью данных предъявляются требования прозрачности:
распределенной структуры БД;
совместного доступа к данным;
распределенной обработки.
Первое требование предполагает независимость конечных пользователей и программ от способа размещения информации на ЭВМ сети. Это означает, что конечные пользователи и прикладные программисты должны иметь возможность формулировать запросы к РБД аналогично запросам к централизованной базе данных, то есть без учета распределения данных по узлам сети. Локализация данных, декомпозиция запросов и композиция результатов должны выполняться системой без участия пользователя.
"Прозрачность" совместного доступа к данным означает поддержку целостности РБД при одновременной модификации одних и тех же данных несколькими пользователями.
Под "прозрачностью" распределенной обработки понимается независимость пользователей и прикладных программ от типа ЛВС и применяемого сетевого программного обеспечения. В процессе работы пользователь не должен учитывать, что его запрос будет обрабатываться в вычислительной сети, возможно, на нескольких ЭВМ. Коммуникационные функции должны быть скрыты от пользователя.
Администрирование и доступ пользователей к РБД осуществляются с помощью системы управления распределенной базой данных (СУРБД), которая, исходя из упомянутых требований, обеспечивает выполнение следующих функций:
автоматическое определение ЭВМ, на которой хранятся требуемые в запросе данные;
декомпозицию распределенных запросов на частные подзапросы к БД отдельных ЭВМ;
планирование обработки запросов;
передачу частных подзапросов и их исполнение на удаленных ЭВМ;
прием результатов выполнения частных подзапросов, полученных в результате декомпозиции запросов на поиск и чтение данных из РБД, и композицию общего результата;
поддержание в согласованном состоянии копий дублированных данных на различных ЭВМ сети;
управление параллельным доступом к РБД многих пользователей;
обеспечение целостности РБД.
В связи с большей сложностью структуры РБД появляется необходимость хранения помимо собственно данных, составляющих БД, еще и данных об их фрагментации, размещении, дублировании, актуальности хранимых копий. Такие данные называются "метаданными", а структура, предназначенная для их хранения – "мета базой данных". В терминах MS SQL сервера мета БД называется "репозиторий".
Язык доступа к данным SQL
Стандартные команды
Язык SQL – это язык структурированных запросов к реляционным базам данных. В 1974-1975 г.г. Коддом – работником компании IBM – начата теоретическая разработка реляционной базы данных. В рамках проекта System/R в компании IBM был спроектирован, а затем создан первый прототип реляционной базы данных. Тогда же был разработан язык доступа к данным, получивший название SEQUEL (Structured English Query Language).
Вторая реализация проекта была предложена для опытной эксплуатации в 1978-1979 г.г. Здесь язык доступа уже имел другое название - SQL.
В 1982 году компания IBM начала поставку на рынок коммерческого продукта SQL/Data System. А в 1983 году IBM выпустила новую реляционную СУБД Database 2 (DB2). Язык SQL, предложенный IBM, стал неофициальным стандартом.
Опубликование в 1986 году стандарта SQL (ANSI / ISO) официально утвердило SQL. Тем не менее, при одном названии существуют значительные различия в реализациях этого языка в разных СУБД, с чем приходится мириться. Происходит это потому, что, во-первых, язык развивается, а, во-вторых, разработчики пытаются внести в язык конструкции, позволяющие расширить функциональность и/или повысить производительность системы.
В настоящее время название языка (как языка запросов на получение данных) уже не совсем отвечает его возможностям. Сейчас SQL позволяет реализовать все функции СУБД: организацию данных (создание баз, таблиц и т.п.), извлечение данных (собственно, запросы, хотя и весьма сложные), модификацию данных (изменение значений, добавление, удаление), управление доступом (запреты и разрешения для разных пользователей), совместное использование данных (блокировки, обновления, транзакции), обеспечение целостности данных (каскадные удаления и т.п.).
В стандарте ANSI SQL имеется 6 основных типов команд:
1. Команды языка определения данных
data definition language DDL
create table – создать таблицу
alter table – изменить таблицу
drop table – удалить таблицу
create index – создать индекс
alter index – изменить индекс
drop index – удалить индекс
2. Команды языка обработки данных
Data manipulation language DML
insert – вставка записей с таблицу
update – обновить данные на сервере
delete – удалить строки таблицы
3. Команды языка запросов данных
data query language DQL
select – выполнить запрос на выборку из таблиц
4. Команды языка управления данными
data control language DCL
grant – предоставить привилегии
revoke – отменить привилегии
5. Команды языка обработки транзакций
transaction processing language TPL
begin transaction – начать транзакцию
comit transaction – завершить транзакцию
save transaction – создать точку сохранения внутри транзакции
6. Команды языка управления курсором
cursor control language CCL
declare cursor
fetch into
update where current
В MS SQL Jet не поддерживаются команды CCL и DCL.
В дальнейшем будем считать, что у нас есть база данных MS Access, в которой содержатся следующие таблицы:
Автор – поля: Код, Имя, Телефон, Адрес
Книга – поля: Код, Название, Автор, Жанр, Объем
Издание – поля: Книга, Год, Тираж
В таблицах «Автор» и «Книга» есть поле Код, необходимое для реализации связей. В таблице «Книга» поле «Автор» содержит значение поля «Код» таблицы «Автор», указывая таким образом из записи таблицы «Книга» на конкретную запись таблицы «Автор», устанавливая между ними связь.
В таблице «Издание» есть поле «Книга», реализующее связь с таблицей «Книга».
Язык запросов
Самый простой запрос – запрос на выбор всех записей из одной таблицы
«SELECT * FROM Автор»
Этот запрос вернет набор всех строк таблицы «Автор», причем в каждой строке имеются все поля таблицы.
Можно задать выбор не всех полей, а только некоторых. Например, в нашей задаче нет требования показа адреса автора. Тогда мы можем использовать следующий запрос:
«SELECT Имя, Телефон, Код FROM Автор»
Как видите, порядок полей тоже можно изменять. Если мы получаем данные с удаленного компьютера, то сокращение объема передаваемой по сети информации увеличивает производительность системы и усиливает защищенность (т.к. не передаются, возможно, конфиденциальные сведения).
Можно задать выбор не всех строк таблицы. Так нам может потребоваться запрос, возвращающий книги некоторого жанра. Запрос
«SELECT Название, Объем, Автор FROM Книга WHERE Жанр=”детектив”»
вернет все строки таблицы «Книга», в поле «Жанр» которых стоит значение «детектив». Обратите внимание на то, что в выражении where указано поле, не входящее в список полей select. То есть это поле есть в исходной таблице, как раз по значению этого поля происходит отбор записей, но в конечный набор его нет смысла включать, т.к. его значение у всех записей выборки одинаковое.
В полученном наборе записей вместо фамилии автора имеется его код, что совершенно не приемлемо для нормального пользователя, но мы к этому вернемся чуть позже.
В выражении раздела where можно использовать логические операции, операции сравнения и множественные операции. Перечислим их: равно =, не равно < >, больше >, меньше <, меньше либо равно <=, больше либо равно >=, и AND, или OR, между BETWEEN, похоже LIKE, содержится во множестве IN.
Так как в поле «Автор» таблицы «Книга» содержатся коды авторов (коды записей таблицы «Автор»), то следующий запрос вернет все детективы, написанные автором, код которого равен 2.
«SELECT Название, Объем FROM Книга WHERE Жанр=”детектив” AND Автор=2»
Маловероятно, что подобная форма запроса подойдет пользователю. Скорее всего, пользователь задаст фамилию автора книг. Но фамилии хранятся в другой таблице! Таким образом, нам необходим запрос, одновременно выбирающий данные из двух таблиц. Получить список всех детективов с фамилиями авторов можно следующим образом:
«SELECT Автор.Имя, Книга.Название, Книга.Объем FROM Книга, Автор WHERE Книга.Жанр=”детектив” AND Автор.Код=Книга.Автор»
В этом запросе три отличительных новшества. Во-первых, имена полей теперь специфицированные, то есть дополненные именем таблицы, из которой они взяты. И это неудивительно, если подумать о том, что имена полей в разных таблицах могут совпадать, как, например поле с именем «Код» в нашем случае. Второй важный момент состоит в том, что если мы выбираем последовательно строки из таблицы «Книга» и хотим присоединить к выбранным полям поле из другой таблицы («Автор»), то нам необходимо указать правило нахождения соответствующей записи в таблице «Автор». Это правило тоже может быть записано в разделе WHERE, в нашем примере это следующее: Автор.Код=Книга.Автор. А для того чтобы составить сложное выражение нам потребовалась логическая операция AND – третий важный момент.
Очевидно, что можно выбирать данные и из трех (и более) таблиц. Построим запрос, возвращающий список авторов и их книг, изданных в 2008 году с указанием тиража.
«SELECT Автор.Имя, Книга.Название, Издание.Тираж FROM Книга, Автор, Издание WHERE Книга.Код=Издание.Книга AND Автор.Код=Книга.Автор AND Издание.Год=2008»
Такой список выглядит не очень хорошо потому, что он никак не упорядочен. По-видимому, удобнее видеть рядом все книги одного автора, а авторов хочется упорядочить по фамилии. Таким образом, нам требуется инструкция ORDER BY. В диалектах SQL могут устанавливаться ограничения на то, что следует за этой директивой. Иногда это – только набор полей, а иногда – произвольное выражение, включающее даже вами написанные функции. Хотя произвольные выражения обычно гораздо дольше вычисляются.
«SELECT Автор.Имя AS авт, Книга.Название AS назв, Издание.Тираж FROM Книга, Автор, Издание WHERE Книга.Код=Издание.Книга AND Автор.Код=Книга.Автор AND Издание.Год=2008 ORDER BY авт, назв»
В приведенном запросе записи упорядочены сначала по имени автора, а потом по названию книги. Можно упорядочить записи по возрастанию и по убыванию. Обратите внимание на то, что используются так называемые псевдонимы, например, «авт» для «Автор.Имя». Чаще всего псевдонимы применяют для сокращения имени поля. Псевдоним совершенно необходим в том случае, когда в запросе указывается вычисляемое поле. В следующем запросе мы получим объем изданных страниц по каждой книге, равный объему книги в страницах, умноженному на объем тиража в штуках книг.
«SELECT Автор.Имя AS авт, Книга.Название AS назв, Издание.Тираж * Книга.Объем AS квостраниц FROM Книга, Автор, Издание WHERE Книга.Код=Издание.Книга AND Автор.Код=Книга.Автор AND Издание.Год=2008 ORDER BY квостраниц DESC»
Упорядочивать можно по возрастанию и по убыванию, в последнем случае необходимо указать слово DESC, в первом можно (но не обязательно) указать ASC. Направление сортировки указывается после каждого выражения для упорядочивания.
Теперь составим запрос, возвращающий список всех авторов, книги которых были изданы в 2008 году. В предыдущем запросе мы указывали названия всех книг, считали страницы. Теперь все это не нужно.
«SELECT Автор.Имя AS авт FROM Книга, Автор, Издание WHERE Книга.Код=Издание.Книга AND Автор.Код=Книга.Автор AND Издание.Год=2008 ORDER BY авт»
Запрос выше вернет почти то, что нам необходимо. Если же в 2008 году издавались две или более книг некоторого автора, то его фамилия встретится два и более раз. Это нам не нужно. Избавиться от лишних записей помогает директива DISTINCT (единственный).
«SELECT DISTINCT Автор.Имя AS авт FROM Книга, Автор, Издание WHERE Книга.Код=Издание.Книга AND Автор.Код=Книга.Автор AND Издание.Год=2008 ORDER BY авт»
Директива DISTINCT обеспечивает уникальность каждой записи в выходном наборе. Причем уникальность определяется по полям, указанным в SELECT. Иногда необходимо узнать, различались ли исходные записи. В этом случае следует использовать директиву DISTINCT ROW. В результате последнего рассмотренного нами запроса будет получаться одна строка и в случае, если в 2008 году несколько раз издавалась одна и та же книга, и в случае, если издавались разные книги одного автора. Если же использовать директиву DISTINCT ROW, то во втором случае появятся несколько строк в результате (столько, сколько разных книг одного автора издавались в 2008 году).
По умолчанию часто используется директива ALL (все записи), хотя она может быть указана явно. Есть еще директива TOP n, позволяющая отобрать n первых записей. С ее помощью можно, например, выбрать 10 самых дешевых товаров или 25 книг с самым большим тиражом, изданных нашим издательством.
«SELECT TOP 25 Автор.Имя AS авт, Книга.Название AS назв, Издание.Тираж AS трж FROM Книга, Автор, Издание WHERE Книга.Код=Издание.Книга AND Автор.Код=Книга.Автор ORDER BY трж DESC»
Директива JOIN позволяет извлекать данные сразу из нескольких таблиц. Она появилась в стандарте ISO92. Ее можно рассматривать как расширение возможностей, предоставляемых директивой WHERE. Мы уже неоднократно обращались к нескольким таблицам с помощью WHERE. Но, например, последний запрос может быть записан по-другому, при помощи внутреннего объединения.
«SELECT TOP 25 Автор.Имя AS авт, Книга.Название AS назв, Издание.Тираж AS трж FROM Книга INNER JOIN Автор ON Автор.Код=Книга.Автор INNER JOIN Издание ON Книга.Код=Издание.Книга ORDER BY трж DESC»
Хотя возвращаемый результат в обоих случаях одинаков, преимуществ у последней формы записи объединения таблиц два. Во-первых, синтаксически четче указан способ, при помощи которого отыскиваются соответствующие записи в дополнительной таблице (равенство значений двух полей вынесено в отдельное синтаксическое место). Во-вторых, директива JOIN может использоваться со словами LEFT OUTER и RIGHT OUTER, задавая таким образом внешнее объединение. Слово OUTER можно при этом не писать. Попробуем разобраться с тем, что нам дает внешнее объединение. Пусть мы, составляя план издательства на следующий год, хотим получить список всех авторов, книги которых когда-либо издавались нашим издательством с указанием книги, изданной в текущем 2009 году. Если мы воспользуемся директивой WHERE или INNER JOIN, то получим список только авторов, книги которых издавались в 2009 году. Внешнее объединение позволяет выбрать все записи из основной таблицы, даже если во вспомогательной таблице не найдется соответствующих записей. В этом случае незаполненные поля выборки имеют значение null.
«SELECT Автор.Имя AS авт, Книга.Название AS назв, Издание.Тираж AS трж FROM Автор LEFT JOIN Книга ON Автор.Код=Книга.Автор LEFT JOIN Издание ON Книга.Код=Издание.Книга ORDER BY авт DESC»
Директива RIGHT JOIN объявляет, что из правой таблицы объединения должны выбираться все записи, даже, если в левой (указанной в директиве FROM) таблице не найдется соответствующих записей. Следующий оператор вернет то же самое, что и предыдущий.
«SELECT Автор.Имя AS авт, Книга.Название AS назв, Издание.Тираж AS трж FROM Издание RIGHT JOIN Книга ON Книга.Код=Издание.Книга RIGHT JOIN Автор ON Автор.Код=Книга.Автор ORDER BY авт DESC»
Имена-псевдонимы можно давать не только полям, но и таблицам. Это можно делать с целью сокращения имени. Псевдонимы для таблиц необходимы в том случае, если в запросе используется одна и та же таблица два или более раз, то есть при составлении одной выходной записи выбираются значения полей из двух или более записей одной таблицы. Чаще всего это необходимо при работе с так называемыми рекурсивными таблицами. В каждой записи рекурсивной таблицы есть ссылка на другую запись этой же таблицы. Рекурсивные таблицы используются для хранения информации об объектах, имеющих иерархическую структуру. Например, узел изделия входит как часть в другой узел. Например, велосипед состоит из рамы 1шт., колеса 2 шт., руль 1 шт. Колесо, в свою очередь, состоит из спицы 18 шт., обода 1 шт. и т.д. Еще пример, пусть есть таблица работников предприятия. Для каждого работника мы можем указать непосредственного начальника, тоже являющегося работником предприятия. Запрос, возвращающий список работников предприятия с указанием для каждого их непосредственного руководителя, дважды откроет таблицу «Работники».
«SELECT рбт.Имя, рук.Имя FROM Работник рбт INNER JOIN Работник рук ON рбт.подчиняется=рук.Код»
Обратите внимание на то, что если в последнем запросе изменить INNER на LEFT, то в результат попадет и директор предприятия. В первом же запросе мы получаем только тех работников, у которых есть начальник.
В операторе SELECT допускается использовать вычисляемые поля. Причем существуют так называемые агрегирующие функции: SUM(), MAX(), MIN() и другие. Значение этой функции считается по нескольким записям исходных таблиц. Какие это будут записи, определяется директивой GROUP BY (группировка по). Результат упорядочивается по выражению группировки. Понимать такой запрос следует так: группируются все строки, имеющие одинаковые значения в полях, указанных после GROUP BY. Запрос ниже возвратит суммарный по годам тираж нашего издательства, упорядоченный по годам.
«SELECT SUM(Издание.Тираж) AS трж FROM Издание GROUP BY Издание.Год»
Следующий запрос возвратит список книг с суммарным за все годы объемом тиража.
«SELECT Книга.Название, SUM(Издание.Тираж) AS трж FROM Издание INNER JOIN Книга ON Книга.Код=Издание.Книга GROUP BY Книга.Код»
Еще один запрос возвратит список книг с суммарным за каждый год объемом тиража. Это интересно при условии, что одна и та же книга может переиздаваться дважды и более раз в год.
«SELECT Книга.Название, SUM(Издание.Тираж) AS трж FROM Издание INNER JOIN Книга ON Книга.Код=Издание.Книга GROUP BY Книга.Код, Издание.Год»
В последних запросах мы получали все строчки. Можно из них отобрать некоторые, удовлетворяющие заданному условию. Директива WHERE для этого не подходит, т.к. она используется для отбора записей в исходных таблицах. А нам необходимо указать условие для результирующих строк. Это делается при помощи директивы HAVING. Обратите внимание на то, что директиву HAVING имеет смысл использовать в том случае, если в запросе есть агрегирующая функция. Не является синтаксической ошибкой применение этой директивы и без агрегирующей функции, тогда ее действие даст тот же результат, что и WHERE. Следующий запрос возвратит только те книги, суммарный тираж которых превышает 100000 экземпляров.
«SELECT Книга.Название, SUM(Издание.Тираж) AS трж FROM Издание INNER JOIN Книга ON Книга.Код=Издание.Книга GROUP BY Книга.Код HAVING трж>100000»
В стандарте есть еще одно объединение: объединение по строкам – UNION. Предположим, нам необходимо составить список людей, которых мы хотим поздравить с Новым годом. В этот список должны войти работники нашего предприятия (издательства), а также авторы, книги которых издавались нашим издательством.
«SELECT Имя, место_жит as Адрес FROM Работник
UNION SELECT Имя, Адрес FROM Автор»
В конкретных СУБД может быть следующее ограничение: объединяемые таблицы должны иметь одинаковую структуру: порядок и типы полей, т.к. этого требует имеющаяся реализация. Если же данные в таблицах имеют разный тип, можно попробовать составлять вычисляемые поля так, чтобы обеспечить соответствие. В некоторых СУБД не поддерживается такой подход, там можно найти выход, применяя так называемые вложенные запросы.
Немного уточним предыдущий запрос. Пусть в список поздравляемых будут внесены не все авторы, а те, книги которых чаще всего издавались нашим издательством (наши постоянные клиенты).
«SELECT Имя, место_жит as Адрес FROM Работник
UNION SELECT Имя, Адрес FROM Автор WHERE Автор.Код IN (
SELECT Авт FROM (
SELECT TOP 20 Книга.Автор as Авт, Издание.Книга, SUM(Издание.Тираж) as трж FROM Издание GROUP BY Издание.Книга INNER JOIN Книга ON Книга.Код=Издание.Книга))»
Количество вложенных запросов и уровней вложенности не ограничено.