Logo GenDocs.ru

Поиск по сайту:  

Загрузка...

Лекции - База данных, лекционный курс - файл 1.doc


Лекции - База данных, лекционный курс
скачать (1261.5 kb.)

Доступные файлы (1):

1.doc1262kb.25.11.2011 23:01скачать

содержание

1.doc

  1   2   3   4


Т.А. Левчук

БАЗЫ ДАННЫХ




ЛЕКЦИОННЫЙ КУРС





2002

Министерство образования Российской Федерации
Государственное образовательное учреждение

высшего профессионального образования

«Комсомольский-на-Амуре государственный технический университет»
Институт новых информационных технологий

Государственного образовательного учреждения

высшего профессионального образования

«Комсомольский-на-Амуре государственный технический университет»

^ Т.А. Левчук


БАЗЫ ДАННЫХ
ЛЕКЦИОННЫЙ КУРС
Утверждено в качестве учебного пособия

Ученым советом Государственного образовательного учреждения

высшего профессионального образования

«Комсомольский-на-Амуре государственный технический университет»


Комсомольск-на-Амуре 2003


УДК 681.3.07

ББК 32.973.233 я7

Л 381

Левчук Т.А.

Л 381 Базы данных Лекционный курс: Учебное пособие. – Комсомольск-на-Амуре:

ГОУВПО «КнАГТУ», 2003. – 86 с.

В пособии рассматриваются понятия банка данных и базы данных, системы управления базой данных. Дается характеристика моделей представления данных, рассматриваются реляционная модель данных и методы проектирования реляционных баз данных: метод «сущность – связь» и декомпозиционный метод. Рассматриваются вопросы безопасности и целостности баз данных.

Предназначено для студентов специальности 220400 обучающихся по дистанционной технологии обучения.
ББК 32.973.233 я7

Государственное образовательное
учреждение высшего профессионального
образования «Комсомольский-на-Амуре
государственный технический

университет», 2003

Институт новых информационных технологий

Государственного образовательного

учреждения высшего профессионального

образования «Комсомольский-на-Амуре

государственный технический

университет», 2003
^ Памяти Натальи Евгеньевны Василенко –

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

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

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

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

^

1. ВВЕДЕНИЕ В БАНКИ ДАННЫХ




1.1. Понятие банка данных



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

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

Требования к БнД:

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

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

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

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

  • обеспечение взаимной независимости программ и данных;

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

1.2. Компоненты банка данных



БнД является сложной человеко-машинной системой, включающей в свой состав различные взаимосвязанные и взаимозависимые компоненты, а именно:

  • информационная компонента;

  • программные средства;

  • языковые средства;

  • технические средства;

  • организационно–методические средства;

  • администраторы БнД.

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

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

В качестве технических средств для БнД используется ЭВМ.

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

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

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

  • иерархическую;

  • сетевую;

  • реляционную.

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

  • постреляционная;

  • многомерная;

  • объектно-ориентированная.

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

Классификация по типу модели распространяется не только на БД, но и на СУБД и БнД в целом.

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




Рис. 1.1. Классификация СУБД по числу уровней в архитектуре

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



1. Базой данных называют:

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

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

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

2. Система управления базой данных является:

а) компонентом банка данных;

б) системой специальным образом организованных данных;

в) современной формой организации хранения и доступа к информации.

3. Различаются следующие модели данных:

а) даталогическая, физическая, внешняя;

б) иерархическая, сетевая, реляционная;

в) одноуровневая, двухуровневая, трехуровневая.

4. Понятия базы данных и банка данных:

а) нельзя отождествлять;

б) тождественны.

2. МОДЕЛИ ДАННЫХ
2.1. Иерархическая модель
Иерархическая структура представляет совокупность элементов, связанных между собой по определенным правилам. Графическим способом представления иерархической структуры является дерево (рис. 2.1).

Дерево представляет собой иерархию элементов, называемых узлами. Под элементами понимается совокупность атрибутов, описывающих объекты. В модели имеется корневой узел (корень дерева), который находится на самом верхнем уровне и не имеет узлов, стоящих выше него. У одного дерева может быть только один корень. Остальные узлы, называемые порожденными, связаны между собой следующим образом: каждый узел имеет только один исходный, находящийся на более высоком уровне, и любое число (один, два или более, либо ни одного) подчиненных узлов на следующем уровне.

Примером простого иерархического представления может служить административная структура высшего учебного заведения: институт – отделение – факультет – студенческая группа (рис. 2.2).

Рис. 2.2. Пример иерархической структуры

К достоинствам иерархической модели данных относятся эффективное использование памяти ЭВМ и неплохие показатели времени выполнения операций над данными.

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

На иерархической модели данных основано сравнительно ограниченное количество СУБД, в числе которых можно назвать зарубежные системы IMS, PC/Focus, Team-Up и Data Edge, а также отечественные системы Ока, ИНЭС и МИРИС.

^ 2.2. Сетевая модель данных
Отличие сетевой структуры от иерархической заключается в том, что каждый элемент в сетевой структуре может быть связан с любым другим элементом (рис. 2.3). Пример простой сетевой структуры показан на рис. 2.4.




Достоинством сетевой модели данных является возможность эффективной реализации по показателям затрат памяти и оперативности.

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

Наиболее известными сетевыми СУБД являются IDMS, db_VistaIII, СЕТЬ, СЕТОР и КОМПАС.

^ 2.3. Реляционная модель данных
Реляционная модель данных была предложена Е.Ф. Коддом, известным исследователем в области баз данных, в 1969 году, когда он был сотрудником фирмы IBM. Впервые основные концепции этой модели были опубликованы в 1970.

Реляционная база данных представляет собой хранилище данных, организованных в виде двумерных таблиц (рис. 2.5). Любая таблица реляционной базы данных состоит из строк (называемых также записями) и столбцов (называемых также полями).

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

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

1) Каждое значение, содержащееся на пересечении строки и столбца, должно быть атомарным1.

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

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

4) Каждое поле имеет уникальное имя.

5) Последовательность полей в таблице несущественна.

6) Последовательность записей в таблице несущественна.

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

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

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

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

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

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

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

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



Рис. 2.5. Схема реляционной модели данных
К основным недостаткам реляционной модели относятся отсутствие стандартных средств идентификации отдельных записей и сложность описания иерархических и сетевых связей.

Примерами зарубежных реляционных СУБД для ПЭВМ являются: DB2, Paradox, FoxPro, Access, Clarion, Ingres, Oracle.

К отечественным СУБД реляционного типа относятся системы ПАЛЬМА и HyTech.
^ 2.4. Постреляционная модель
Классическая реляционная модель предполагает неделимость данных, хранящихся в полях записей таблиц. Постреляционная модель представляет собой расширенную реляционную модель, снимающую ограничение неделимости данных. Модель допускает многозначные поля – поля, значения которых состоят из подзначений. Набор значений многозначных полей считается самостоятельной таблицей, встроенной в основную таблицу.

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

а)

Накладные Накладные-товары

N накладной

Покупатель




N накладной

Товар

Количество

0373

8723




0373

Сыр

3

8374

8232




0373

Рыба

2

7364

8723




8374

Лимонад

1










8374

Сок

6










8374

Печенье

2










7364

Йогурт

1

б)

Накладные

N накладной

Покупатель

Товар

Количество

0373

8723

Сыр

3







Рыба

2

8374

8232

Лимонад

1







Сок

6







Печенье

2

7364

8723

Йогурт

1


Рис. 2.6. Структуры данных реляционной (а) и постреляционной (б) моделей
Поскольку постреляционная модель допускает хранение в таблицах ненормализованных данных, возникает проблема обеспечения целостности и непротиворечивости данных. Эта проблема решается включением в СУБД соответствующих механизмов.

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

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

Рассмотренная постреляционная модель данных поддерживается СУБД uniVers. К числу других СУБД, основанных на постреляционной модели данных, относятся также системы Bubba и Dasdb.

^ 2.5. Многомерная модель
Многомерный подход к представлению данных появился практически одновременно с реляционным, но интерес к многомерным СУБД стал приобретать массовый характер с середины 90-х годов. Толчком послужила в 1993 году статья Э. Кодда. В ней были сформулированы 12 основных требований к системам класса OLAP (OnLine Analytical Processing – оперативная аналитическая обработка), важнейшие из которых связаны с возможностями концептуального представления и обработки многомерных данных.

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

1) системы оперативной (транзакционной) обработки;

2) системы аналитической обработки (системы поддержки принятия решений).

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

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

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

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

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

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

По сравнению с реляционной моделью многомерная организация данных обладает более высокой наглядностью и информативностью. Для иллюстрации на рис. 2.7 приведены реляционное (а) и многомерное (б) представления одних и тех же данных об объемах продаж автомобилей.

Если речь идет о многомерной модели с мерностью больше двух, то не обязательно визуально информация представляется в виде многомерных объектов (трех-, четырех- и более мерных гиперкубов). Пользователю и в этих случаях более удобно иметь дело с двумерными таблицами или графиками. Данные при этом представляют собой «вырезки» из многомерного хранилища данных, выполненные с разной степенью детализации.
а) б)


Модель

Месяц

Объем




Модель

Июнь

Июль

Август

Жигули

июнь

12




Жигули

12

24

5

Жигули

июль

24




Москвич

2

18

No

Жигули

август

5




Волга

No

19

No

Москвич

июнь

2
















Москвич

июль

18
















Волга

июль

19
















Рис. 2.7. Реляционное (а) и многомерное (б) представление данных

Основные понятия многомерных моделей данных: измерение и ячейка.

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

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

В примере на рис. 2.7, б каждое значение ячейки Объем продаж однозначно определяется комбинацией временного измерения Месяц продаж и модели автомобиля. На практике зачастую требуется большее количество измерений. Пример трехмерной модели данных приведен на рис. 2.8.




Рис. 2.8. Пример трехмерной модели
В существующих многомерных СУБД используются две основные схемы организации данных: гиперкубическая и поликубическая.

В поликубической схеме предполагается, что в БД может быть определено несколько гиперкубов с различной размерностью и с различными измерениями в качестве граней. Примером системы, поддерживающей поликубический вариант БД, является сервер Oracle Express Server.

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

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

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

Примерами систем, поддерживающими многомерные модели данных, является Essbase, Media Multi-matrix, Oracle Express Server, Cache. Существуют программные продукты, например Media/MR, позволяющие одновременно работать с многомерными и с реляционными БД.


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

Стандартизированная объектно-ориентированная модель описана в рекомендациях стандарта ODMG-93 (Object Database Management Group – группа управления объектно-ориентированными базами данных).

Рассмотрим упрощенную модель объектно-ориентированной БД. Структура объектно-ориентированной БД графически представима в виде дерева, узлами которого являются объекты. Свойства объектов описываются некоторым стандартным типом или типом, конструируемым пользователем (определяется как class). Значение свойства типа class есть объект, являющийся экземпляром соответствующего класса. Каждый объект-экземпляр класса считается потомком объекта, в котором он определен как свойство. Объект-экземпляр класса принадлежит своему классу и имеет одного родителя. Родовые отношения в БД образуют связную иерархию объектов. Пример логической структуры объектно-ориентированной БД библиотечного дела приведен на рис. 2.9. Здесь объект типа Библиотека является родительским для объектов-экземпляров классов Абонент, Каталог и Выдача. Различные объекты типа Книга могут иметь одного или разных родителей. Объекты типа Книга, имеющие одного и того же родителя, должны различаться, по крайней мере, инвентарным номером (уникален для каждого экземпляра книги), но имеют одинаковые значения свойств isbn, удк, название и автор.

Логическая структура объектно-ориентированной БД внешне похожа на структуру иерархической БД. Основное различие между ними состоит в методах манипулирования данными.

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

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

Наследование, наоборот, распространяет область видимости свойства на всех потомков объекта. Так, всем объектам типа ^ Книга, являющимся потомками объекта типа Каталог, можно приписать свойства объекта-родителя: isbn, удк, название и автор. Если необходимо расширить действие механизма наследования на объекты, не являющиеся непосредственными родственниками (например, между двумя потомками одного родителя), то в их общем предке определяется абстрактное свойство типа abs. Так, определение абстрактных свойств билет и номер в объекте Библиотека приводит к наследованию этих свойств всеми дочерними объектами Абонент, Книга и Выдача. Не случайно поэтому значения свойства билет классов Абонент и Выдача, показанных на рис. 2.9, являются одинаковыми – 00015.

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

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


Рис. 2.9. Логическая структура БД библиотечного дела
Основным достоинством объектно-ориентированной модели данных в сравнении с реляционной является возможность отображения информации о сложных взаимосвязях объектов. Объектно-ориентированная модель данных позволяет идентифицировать отдельную запись базы данных и определять функции их обработки.

Недостатками объектно-ориентированной модели являются высокая понятийная сложность, неудобство обработки данных и низкая скорость выполнения запросов.

К объектно-ориентированным СУБД относятся POET, Jasmine, Versant, O2, ODB-Jupiter, Iris, Orion, Postgres.
^ Тесты для самоконтроля




1. Под элементами в иерархической структуре понимают:

а) совокупность атрибутов, описывающих объекты;

б) данные, организованные в виде двумерных таблиц;

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

2. Каждый элемент может быть связан с любым другим элементом:

а) в иерархической модели;

б) в сетевой модели;

в) в реляционной модели.

3. В таблицах реляционной базы данных:

а) существенна последовательность полей и несущественна последовательность записей;

б) несущественна и последовательность полей, и последовательность записей;

в) несущественна последовательность полей и существенна последовательность записей.

4. В информационных системах оперативной обработки информации эффективны:

а) реляционные СУБД;

б) многомерные СУБД;

в) иерархические СУБД.

5. Многомерность многомерной модели данных означает:

а) многомерность визуализации данных;

б) наличие многозначных полей;

в) многомерное логическое представление структуры информации.

6. Возможность отображения информации о сложных взаимосвязях объектов имеется:

а) в реляционной модели;

б) в постреляционной модели;

в) в объектно-ориентированной модели.

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

а) ключом таблицы;

б) внешним ключом таблицы;

в) первичным ключом таблицы.

8. Неделимость данных – обязательный принцип:

а) постреляционной модели данных;

б) реляционной модели данных;

в) объектно-ориентированной модели.

9. В информационных системах аналитической обработки информации эффективны:

а) реляционные СУБД;

б) многомерные СУБД.

^ 3. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННОЙ БАЗЫ ДАННЫХ

МЕТОДОМ «СУЩНОСТЬ – СВЯЗЬ»
Существуют разные подходы к проектированию БД. Мы рассмотрим два метода проектирования: декомпозиционный и метод «сущность - связь», первым рассмотрим метод «сущность - связь».
^ 3.1. Этапы проектирования
В БД отражается информация об определенной предметной области. Предметной областью называют часть реального мира, представляющую интерес для данного использования. В автоматизированных информационных системах отражение предметной области представляется моделями данных нескольких уровней (число уровней зависит от особенностей СУБД). Независимо от того, поддерживаются ли в явном виде отдельно модели логического и физического уровня, с точки зрения методологии все равно можно выделить эти уровни и соответствующие им этапы проектирова- ния БД.

Первый этап проектирования - инфологическое моделирование. Чтобы спроектировать структуру БД, необходима исходная информация о предметной области. Желательно, чтобы эта информация была представлена в формализованном виде. Описание предметной области, выполненное без ориентации на используемые в дальнейшем программные и технические средства, называется инфологической моделью предметной области (ИЛМ).

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

Третий этап проектирования состоит в привязке ДЛМ к среде хранения с помощью модели данных физического уровня (физической модели). Описание физической структуры БД называется схемой хранения, соответствующий этап проектирования БД – физическим проектированием.

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

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

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




Рис. 3.1. Взаимосвязь этапов проектирования БД
^ 3.2. Общие сведения об инфологическом моделировании
Уточним понятие ИЛМ. Для описания предметной области может использоваться и естественный язык, но его применение имеет много недостатков. Основные: громоздкость описания и неоднозначность трактовки. Поэтому для описания предметной области обычно используют искусственные формализованные языковые средства. В связи со сказанным уточним определение инфологической модели.

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

Требования, предъявляемые к ИЛМ:

  • адекватность отображения предметной области (основное требование);

  • ИЛМ должна быть непротиворечивой;

  • ИЛМ является конечной, но должна обладать свойством легкой расширяемости (для обеспечения ввода новых данных без изменения ранее определенных; то же самое можно сказать и об удалении данных);

  • язык спецификации ИЛМ должен быть одинаково применим как при ручном, так и при автоматизированном проектировании информационных систем;

  • ИЛМ должна легко восприниматься разными категориями пользователей.

Компоненты ИЛМ:

  1. описание объектов и связей между ними (ER – модель);

  2. описание информационных потребностей пользователей;

  3. алгоритмические связи показателей;

  4. лингвистические отношения;

  5. ограничения целостности.

ER – модель (Е (entity) – сущность, R (relationship) – связь) является центральной компонентой ИЛМ. Вопросы ее построения подробно рассматриваются в подраз- деле 3.3.

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

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

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

Важной компонентой ИЛМ являются ограничения целостности. Их рассмотрению посвящен восьмой раздел.
3.3. Построение ER - модели
Для описания ИЛМ используются как языки аналитического (описательного) типа, так и графические средства. Мы воспользуемся последними, как более наглядными.

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

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

Для изображения объектов и их свойств будем использовать следующие обозначения:


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

Объект может обладать только одним значением какого-то свойства (например, каждый человек может иметь только одну дату рождения). Такие свойства будем называть единичными.

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

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

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

Свойства, значения которых могут изменяться со временем (например, ^ Фамилия, Адрес, Телефон), будем называть динамическими и обозначать буквой D.

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

Существует понятие составного свойства (примеры таких свойств: Адрес, состоящий из «улицы», «дома», «квартиры»; ^ Дата рождения, состоящая из «числа», «месяца», «года»). Для его обозначения будем использовать квадрат.

В качестве примера на рис. 3.3 приводится изображение класса объектов Сотрудники и его свойств.



Рис. 3.3. Изображение класса объектов и его свойств
В ИЛМ отображаются не отдельные экземпляры объектов, а классы объектов. Когда в ИЛМ изображено обозначение объекта, то ясно, что речь идет о классе объектов, обладающих описанными свойствами. Поэтому в ИЛМ в большинстве случаев не вводят в явном виде еще и обозначение для класса объектов. Явное изображение последнего необходимо только в том случае, если в предметной области для данного класса объектов фиксируются не только характеристики, относящиеся к отдельным объектам этого класса, но и какие-то интегральные характеристики, относящиеся ко всему классу в целом. Например, если для класса объектов Сотрудники фиксируется не только возраст каждого из сотрудников, но и средний возраст всех сотрудников, то в ИЛМ необходимо отразить не только объект Сотрудник, но и класс объектов Сотрудники (рис. 3.4).



Рис. 3.4. Изображение класса объектов и интегральных

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

Различают связи типа:

  • один к одному (1:1);

  • один ко многим (1:М);

  • многие к одному (М:1);

  • многие ко многим (М:М).

Иногда эти типы связей называют степенью связи.

Кроме степени связи, в ИЛМ для характеристики связи между разными объектами указывается класс принадлежности, который показывает, может ли отсутствовать связь объекта одного класса с каким-либо объектом другого класса. Различают обязательный и необязательный класс принадлежности. Объясним сказанное на конкретных примерах.

Пусть в ИЛМ отображается связь между двумя классами объектов: Сотрудники и Язык иностранный.

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

ER – типов ER - экземпляров


б) Предметной областью является институт, сотрудники которого обязательно должны владеть каким-либо иностранным языком, но никто не владеет более чем одним языком.

Соответствующие диаграммы

ER – типов ER – экземпляров



В рассмотренных случаях между объектами наблюдается связь типа М:1; в случае а класс принадлежности является необязательным для обоих объектов; в случае б – для объекта Сотрудники класс принадлежности является обязательным, что изображается точкой в прямоугольнике.

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

Соответствующие диаграммы

ER – типов ER – экземпляров



В этом случае связь между объектами имеет тип М:М.

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

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

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

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

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

Сложные объекты подразделяют на составные, обобщенные и агрегированные.

Составной объект соответствует отображению связи «целое - часть». Примеры таких объектов: класс – ученики, группа – студенты и т.п.

Для отображения составных объектов в ИЛМ обычно не используются какие-либо специальные условные обозначения, а связь между составным и составляющими его объектами отображается так же, как это было описано выше (например, объекты Группа и Студенты связаны между собой отношением 1:М).

Обобщенный объект отражает наличие связи «род - вид» между объектами предметной области. Например, объекты ^ Студент, Школьник, Аспирант образуют обобщенный объект Учащиеся. Объекты, составляющие обобщенный объект, называются его категориями. Как «родовой» объект, так и «видовые» объекты могут обладать определенным набором свойств. Причем «видовые» объекты обладают всеми теми свойствами, которыми обладает «родовой» объект, плюс свойствами, присущими только объектам этого вида.

Определение родо-видовых связей означает классификацию объектов предметной области по тем или иным признакам. Подклассы могут выделяться в ИЛМ в явном виде (рис. 3.5).



Рис. 3.5. Изображение обобщенного объекта
На рис. 3.5 изображен фрагмент ИЛМ, отражающий обобщенный объект Кадры для высшего учебного заведения. Для обобщенного объекта выделено две категории: Сотрудник и Учащийся. Для обозначения подкласса в схеме использован треугольник. Естественно, что классификация может быть многоуровневой. В рассматриваемом примере подкласс Сотрудник, в свою очередь, может быть классифицирован на Преподаватель и Администрация; Учащийся на Студент и Аспирант.

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

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





Рис. 3.6. Изображение агрегированного объекта
^ 3.4. Общие сведения о даталогическом проектировании
Даталогическое проектирование является проектированием логической структуры БД, что означает определение всех информационных единиц и связей между ними, задание их имен и типов, а также некоторых количественных характеристик (например, длины поля).

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

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

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

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

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

  • Если естественный идентификатор может изменяться со временем (например, фамилия), то это может вызвать много проблем, если, наряду с таким «динамическим» идентификатором, не использовать «статический» искусственный идентификатор.


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

Для перехода от ИЛМ к реляционной можно воспользоваться следующими рекомендациями:

1) Для каждого простого объекта и его единичных свойств строится таблица, атрибутами которой являются идентификатор объекта и реквизиты, соответствующие каждому из единичных свойств:



2) Если у объекта имеются множественные свойства, то каждому из них ставится в соответствие отдельная таблица:




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

  • если многие из объектов обладают рассматриваемым свойством, то его можно хранить в БД так же, как и обычное свойство;

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

4) Если у объекта имеется составное свойство, то составляющие составного свойства либо помещаются в отдельные поля реляционной таблицы, либо в одно поле:




5) Если связь между объектами 1:1 и классы принадлежности обоих объектов являются обязательными, то для отображения данных объектов и связи между ними:

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

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



6) Если связь между объектами 1:1 и класс принадлежности одного объекта является обязательным, а другого – необязательным, то для каждого из этих объектов используют отдельные таблицы, а идентификатор объекта, для которого класс принадлежности является необязательным, добавляется в таблицу, соответствующую тому объекту, для которого класс принадлежности обязательный:



7) Если связь между объектами 1:1 и класс принадлежности каждого объекта является необязательным, то следует использовать три таблицы: по одной для каждого объекта и одну для отображения связи между ними:




8) Если между объектами предметной области имеется связь 1:М и класс принадлежности n – связного объекта является обязательным, то используют две таблицы: по одной для каждого объекта и в таблицу, соответствующую n – связному объекту, добавляется идентификатор 1 – связного объекта:




9) Если между объектами предметной области имеется связь 1:М и класс принадлежности n - связного объекта является необязательным, то создают три таблицы: по одной для каждого объекта и одну для отображения связи между ними:




10) Если между объектами предметной области имеется связь М:М, то для хранения такой информации потребуется три таблицы: по одной для каждого объекта и одна для отображения связи между ними (классы принадлежности могут быть: оба – обязательными, оба - необязательными, один – обязательный, другой – необязательный):


11) Агрегированному объекту, имеющему место в предметной области, в ДЛМ ставится в соответствие одна таблица, атрибутами которой являются идентификаторы всех объектов, «задействованных» в данном агрегированном объекте, а также реквизиты, соответствующие свойствам этого объекта:



Такое объединение информации в одну таблицу возможно только в том случае, если между объектами имеется связь 1:1. Если же между объектами имеется связь М:1 (или М:М), то выделяют по одной таблице для каждого объекта и одну таблицу для связи:

R1 (ИО1, …)

R2 (ИО2, …)

R3 (ИО3, …)

R4 (ИО1, ИО2, ИО3, С1).

12) При отображении обобщенных объектов могут быть приняты разные решения:

  • всему обобщенному объекту может быть поставлена в соответствие одна таблица:





  • каждой из категорий ставится в соответствие отдельная таблица, то есть:

R1 (ИО1, С1, С2, С4, С5)

R2 (ИО1, С1, С2, С6, С7).

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

13) При отображении составного объекта также возможны разные решения:

  • если речь идет о составе изделий, то между «изделием» и «деталью» имеется связь типа М:М (одна деталь может входить в разные изделия и, наоборот, в изделие входят разные детали) – в этом случае для отображения связи «целое – часть» можно использовать три таблицы. В первой двух будет храниться информация о самих объектах, в третьей – информация о связи между ними и характере этой связи (для состава изделия это могут быть следующие поля: «что входит», «куда входит», «количество»);

  • если речь идет о составе какой-то организации, то между объектами скорее всего будет связь типа 1:М – в этом случае для отображения связи «целое – часть» можно использовать рекомендации пунктов 8 и 9.

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




Рис. 3.7. ER – модель предметной области

Рассмотрим упрощенный пример предметной области, ER – модель которой изображена на рис. 3.7.

Соответствующая ей даталогическая модель базы данных может иметь следующий вид:

Факультет (^ Код_фак, Кр_наим_фак, Полн_наим_фак);

Кафедра (Код_каф, Кр_наим_каф, Полн_наим_каф, Код_фак);

Сотрудник (Таб_номер, ФИО, Дата_рождения, Пол, Код_каф, Должность, Уч_степень);

Студент (Таб_номер, ФИО, Дата_рождения, Пол, Код_фак, Дата_поступления, Ступень_обучения);

Ин_яз (Код_языка, наименование_языка);

Знание_ин_яз (Таб_номер, Код_языка, Степень_владения);

Дети (Таб_номер, Имя, Дата_рождения).


Тесты для самоконтроля



1. Инфологической моделью называют:

а) описание логической структуры БД с точки зрения конкретного пользователя;

б) часть реального мира, представляющую интерес для данного проектирования;

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

2. Логические связи между элементами данных безотносительно к их содержанию и среде хранения отображаются:

а) в физической модели;

б) в даталогической модели;

в) в инфологической модели.

3. Схемой хранения называют:

а) описание логической структуры БД;

б) описание физической структуры БД;

в) описание логической структуры БД с точки зрения конкретного пользователя.

4. В список компонент ИЛМ входит:

а) информационная компонента;

б) описание объектов и связей между ними;

в) описание информационных потребностей пользователей.

^ 5. В список компонент БнД входит:

а) СУБД;

б) технические средства;

в) ограничения целостности.

6. Класс принадлежности указывается:

а) для характеристики связи между разными объектами;

б) при необходимости фиксации каких-либо характеристик, относящихся к классу объектов в целом;

в) если объект участвует во многих связях.

7. Родо-видовые связи между объектами предметной области отражаются:

а) в составном объекте;

б) в агрегированном объекте;

в) в обобщенном объекте.

8. Обозначение для класса объектов в явном виде вводится:

а) если естественный идентификатор объекта не обладает свойством уникальности;

б) для характеристики связи между разными объектами;

в) при необходимости фиксации каких-либо характеристик, относящихся к классу объектов в целом.
^ Задания для самостоятельного выполнения
Дана инфологическая конструкция (варианты а - д):

а) б)




в) г)


д)



Построить соответствующую ей реляционную схему.

4. ЯЗЫКИ ЗАПРОСОВ
4.1. Реляционная алгебра
В основе реляционной модели лежит математическое понятие теоретико-множественного отношения. Теоретико-множественное отношение представляет собой подмножество декартова произведения доменов. Доменом называется набор значений элементов данных одного типа, отвечающий поставленным условиям (например, домен ФИО определяется на базовом типе строк символов, но в число его значений могут входить только те строки, которые могут изображать имя).

Декартовым произведением k доменов (D1, D2,…, Dk), (обозначается D1×D2×…×Dk), называется множество всех кортежей вида (V1, V2, …, Vk) длины k, таких, что V1∈D 1, V2∈D2, …, Vk∈Dk.

Например, пусть D1 = {1, 2, 3}; D2 = {a, b, c, d}

Тогда





1,a

1,b

1,c

1,d




в

матричном

виде

D1×D2 =

2,a

2,b

2,c

2,d




3,a

3,b

3,c

3,d

или:

D1×D2 = {(1,a), (1,b), (1,c), (1,d), (2,a), (2,b), (2,c), (2,d), (3,a), (3,b), (3,c), (3,d)}

Отношением называют некоторое подмножество декартова произведения доменов (предполагаются только конечные отношения).

Примеры отношений:

1) {(1,a), (1,c), (1,b)} - подмножество декартова произведения доменов D1×D2;

2) Ø.

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

  • реляционную алгебру;

  • реляционное исчисление.

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

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

1) Объединением двух отношений (r ∪ s) является отношение, содержащее все кортежи, которые принадлежат либо r, либо s, либо им обоим.

Данная операция применяется к отношениям с одной и той же схемой.

^ Схемой отношения r называется конечное множество имен атрибутов {A1, A2, …, An}.

Примеры:

a) Пусть k и m – отношения со схемой ABC:


k

(A

B

C)




m

(A

B

C)




a1

b1

c1







a1

b2

c1




a1

b2

c1







a2

b2

c1




a2

b1

c2







a2

b2

c2


Тогда объединение данных отношений:


k ∪ m =

(A

B

C)




a1

b1

c1




a1

b2

c1




a2

b1

c2




a2

b2

c1




a2

b2

c2


б) Пусть отношение R1 – множество поставщиков из Москвы, а R2 – множество поставщиков, которые поставляют деталь D1:

R1 R2

№_П

ФИО_П

Город_П




№_П

ФИО_П

Город_П

Р1

Иванов И. И.

Москва




Р1

Иванов И. И.

Москва

Р4

Петров П. П.

Москва




Р2

Сидоров С. С.

Киев


Тогда объединение данных отношений даст поставщиков из Москвы, либо поставщиков, которые поставляют деталь D1, либо тех и других:

R1 ∪ R2

№_П

ФИО_П

Город_П

Р1

Иванов И. И.

Москва

Р4

Петров П. П.

Москва

Р2

Сидоров С.С.

Киев


2) Разностью двух отношений (обозначается r - s) является отношение, содержащее все кортежи, которые принадлежат r, но не принадлежат s.

Операция применяется к отношениям с одной и той же схемой.

Примеры:

а)

k – m =

(A

B

C)




a1

b1

c1




a2

b1

c2


б) Разность отношений R1 и R2 даст поставщиков из Москвы, не поставляющих деталь D1:

R1 – R2

№_П

ФИО_П

Город_П

Р4

Петров П. П.

Москва

3) Пересечением двух отношений (обозначается r ∩ s) является отношение, содержащее все кортежи, которые принадлежат одновременно r и s.

Операция применяется к отношениям с одной и той же схемой.

Примеры.

а)

k ∩ m =

(A

B

C)




a1

b2

c1


б) Пересечение отношений R1 и R2 даст всех поставщиков из Москвы, поставляющих деталь D1:

R1 ∩ R2

№_П

ФИО_П

Город_П

Р1

Иванов И. И.

Москва










Рис. 4.1. Основные операции реляционной алгебры
4) Декартовым произведением отношения r степени k1 и отношения s степени k2 (r × s) является отношение степени (k1 + k2), содержащее такие кортежи, первые k1 элементов которых принадлежат r, а последние k2 элементов принадлежат s.

Примеры:

а) Даны отношения k и m


k

(A

B)




m

(C

D)




a1

b1







c1

d1




a2

b1







c2

d1
















c2

d2


Тогда

k × s =

(A

B

C

D)




a1

b1

c1

d1




a1

b1

c2

d1




a1

b1

c2

d2




a2

b1

c1

d1




a2

b1

c2

d1




a2

b1

c2

d2


б) Пусть отношение R1 – множество поставщиков, а S1 – множество деталей:

R1 S1

№_П

ФИО_П

Город_П




№_Д

Название

Вес

Город_Д

Р1

Иванов И. И.

Москва




Д1

гайка

12

Москва

Р2

Сидоров С. С.

Киев




Д3

винт

17

Ростов













Д6

шпилька

19

Москва


Тогда декартово произведение данных отношений:

R1 × S1

№_П

ФИО_П

Город_П

№_Д

Название

Вес

Город_Д

Р1

Иванов И.И.

Москва

Д1

гайка

12

Москва

Р1

Иванов И.И.

Москва

Д3

винт

17

Ростов

Р1

Иванов И.И.

Москва

Д6

шпилька

19

Москва

Р2

Сидоров С.С.

Киев

Д1

гайка

12

Москва

Р2

Сидоров С.С.

Киев

Д3

винт

17

Ростов

Р2

Сидоров С.С.

Киев

Д6

шпилька

19

Москва


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

Пусть r отношение со схемой R, A – атрибут в R и a – элемент из домена А. Тогда – операция выборки в r кортежей, в которых значение A равно a. В условии выборки можно использовать константы, логические операции и операции сравнения.

Примеры:

а)



№_Д

Название

Вес

Город_Д

Д1

гайка

12

Москва


б) Дано отношение R1

R1

№_П

№_Д

Количество

Р1

Д1

300

Р1

Д3

400

Р2

Д1

200

Р2

Д4

500

Р3

Д1

400


Тогда



№_П

№_Д

Количество

Р3

Д1

400


6) Проекция (применяется к одному отношению) - операция выбора подмножества столбцов. Пусть r – отношение со схемой R, Х – подмножество из R. Проекция r на X есть отношение , полученное вычеркиванием столбцов, соответствующих атрибутам в R – X, и исключением из оставшихся столбцов повторяющихся строк.

Примеры.

а) Пусть k - отношение со схемой АВС:


k

(A

B

C)




a1

b1

c1




a1

b2

c1




a2

b1

c2


Тогда запись означает, что из каждого кортежа, принадлежащего k, формируется кортеж длины 2 из третьего и первого его атрибутов в указанном порядке:


=

(C

A)




повторяющиеся кортежи

исключаются




c1

a1




c2

a2


Записи эквивалентна запись .

б) Пусть S1 - отношение, содержащее информацию о деталях:

S1

№_Д

Название

Вес

Город_Д

Д1

гайка

12

Москва

Д3

винт

17

Ростов

Д6

шпилька

19

Москва


Тогда



№_Д

Город_Д

Д1

Москва

Д3

Ростов

Д6

Москва

7) Деление. Пусть r – отношение со схемой R, s – отношение со схемой S и S ⊆ R. Положим = R – S. Частное от деления r на s (r ÷ s) – это максимальное подмножество множества , такое, что декартово произведение и s содержится в r.

Примеры:

а) Даны отношения k и m


k

(A

B

C

D)




m

(C

D)




a1

b1

c1

d2







c2

d1




a2

b2

c2

d1







c4

d1




a3

b2

c3

d3
















a2

b2

c4

d1
















a1

b3

c5

d4
















a4

b1

c6

d5













Тогда


k ÷ m =

(A

B)




a2

b2


б) Имеется отношение

Право


Пилот

Тип_самолета

Иванов

707

Иванов

727

Иванов

747

Петров

707

Петров

727

Сидоров

707

Сидоров

727

Сидоров

747

Сидоров

1011

Макаров

727


Требуется найти тех пилотов, которые имеют право управлять всеми типами самолета из некоторого множества q.

q

Тип_самолета

707

727

747


Тогда

Право ÷ q

Пилот

Иванов


Сидоров


8) Соединение.

Естественное соединение. Пусть r – отношение со схемой R, s – отношение со схемой S и R ∪ S = T. Естественным соединением отношений r и s (r ⊲⊳ s) является отношение q со схемой T, содержащее кортежи, каждый являющийся комбинацией кортежа из r и кортежа из s с равными (R ∩ S) – значениями.

Примечание. Если R ∩ S = ∅, то r ⊲⊳ s даст декартово произведение r и s.

Примеры:

а) Даны отношения k и m


k

(A

B)




m

(B

C)




a1

b1







b1

c2




а1

b2







b2

c1




a2

b1














Тогда

k ⊲⊳m =

(A

B

C)




a1

b1

c2




a2

b1

c2




a1

b2

c1



б) Даны отношения R1 и S1

R1 S1

№_П

ФИО_П

Город_П




№_Д

Название

Вес

Город_Д

Р1

Иванов И. И.

Москва




Д1

гайка

12

Москва

Р2

Сидоров С. С.

Киев




Д3

винт

17

Ростов













Д6

шпилька

19

Москва


Естественное соединение R1 и S1 по атрибуту ^ Город (в отношении R1 – это Город_П, а в отношении S1 – Город_Д):

R1 ⊲⊳ S1

№_П

ФИО_П

Город

№_Д

Название

Вес

Р1

Иванов И.И.

Москва

Д1

гайка

12

Р1

Иванов И.И.

Москва

Д6

шпилька

19


^ Тета – соединение. При естественном соединении отношения могут комбинироваться только по одноименным столбцам и должны комбинироваться по всем таким столбцам.

Примечание. В предыдущем примере вначале была применена операция переименования атрибутов ^ Город_Д и Город_П на Город.

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

Тета – соединение отношений r и s по столбцам i и j представляет собой множество кортежей в декартовом произведении r и s, таких, что i-й элемент r находится в связи Θ с j–элементом s.

Если Θ является оператором «=», то эта операция называется эквисоединением.

Примеры

а) Пусть R1 – отношение, содержащее информацию о рейсах из города «а» в город «в», S1 – отношение, содержащее информацию о рейсах из города «в» в город «с»:

R1


Номер

Время_вылета

Время_прибытия

60

9.40

11.45

91

12.50

14.47

112

16.05

18.15

306

20.30

22.25

420

21.15

23.11



S1

Номер

Время_вылета


Время_прибытия

11

8.30

9.52

60

12.25

13.43

156

16.20

17.40

158

19.10

20.35



Требуется узнать, какие рейсы из «a» в «в» сочетаются с рейсами из «в» в «c».

Для этого необходимо соединить те кортежи, для которых Время_прибытия в отношении R1 меньше Время_вылета в отношении S1.
Тета – соединение отношений R1 и S1:

Номер

Время_вылета

Время_прибытия

Но­мер

Время_вылета

Время_прибытия

60

9.40

11.45

60

12.25

13.43

60

9.40

11.45

156

16.20

17.40

60

9.40

11.45

158

19.10

20.35

91

12.50

14.47

156

16.20

17.40

91

12.50

14.47

158

19.10

20.35

112

16.05

18.15

158

19.10

20.35


б) Даны отношения Маршрут и Адрес

Маршрут

Номер

Пункт_отправления

Пункт_назначения

84

Чикаго

Нью-Йорк

109

Нью-Йорк

Лос-Анджелес

117

Атланта

Бостон

213

Нью-Йорк

Бостон

214

Бостон

Нью-Йорк


Адрес

Пилот

Аэропорт

^

отношение указывает для каждого пилота адрес базового аэропорта, к которому он приписан


Терхьюн

Нью-Йорк

Темпл

Атланта

Тейлор

Атланта

Тарбелл

Бостон

Тодд

Лос-Анджелес

Трумен

Чикаго





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

Эквисоединение отношений ^ Маршрут и Адрес по столбцам Пункт_отправления и Аэропорт:


Номер

Пункт_отправления

Пункт_назначения

Пилот

Аэропорт


84

Чикаго

Нью-Йорк

Трумен

Чикаго


109

Нью-Йорк

Лос-Анджелес

Терхьюн

Нью-Йорк

117

Атланта

Бостон

Темпл

Атланта

117

Атланта

Бостон

Тейлор

Атланта

213

Нью-Йорк

Бостон

Терхьюн

Нью-Йорк

214

Бостон

Нью-Йорк

Тарбелл

Бостон


Основное отличие эквисоединения от естественного в том, что при последнем соединяемые столбцы не повторяются.

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

^ 4.2. Структурированный язык запросов SQL
Запрос представляет собой специальным образом описанное требование, определяющее состав производимых над БД операций по выборке, удалению или модификации хранимых данных.

Для подготовки запросов с помощью различных СУБД чаще всего используются два основных языка описания запросов:

  • ^ QBE (Query By Example) – язык запросов по образцу;

  • SQL (Structured Query Language) – структурированный язык запросов.

По возможностям манипулирования данными при описании запросов указанные языки практически эквивалентны. Отличаются языки способом формирования запросов: язык QBE предполагает ручное или визуальное формирование запроса, в то время как использование SQL означает программирование запроса.

Язык ^ SQL имеет несколько стандартов, наиболее распространенными из которых являются SQL-89 и SQL-92. Язык предназначен для выполнения операций над таблицами (создание, удаление, изменение структуры) и над данными таблиц (выборка, изменение, добавление и удаление), а также некоторых сопутствующих операций. SQL является непроцедурным языком и не содержит операторов управления, организации подпрограмм, ввода-вывода и т.п. В связи с этим SQL автономно не используется, обычно он погружен в среду встроенного языка программирования СУБД (например, FoxPro СУБД Visual FoxPro, ObjectPAL СУБД Paradox, Visual Basic for Applications СУБД Access).

Операторы языка ^ SQL можно условно разделить на два подъязыка: язык определения данных (Data Definition LanguageDDL) и язык манипулирования данными (Data Manipulation LanguageDML). Основные операторы языка SQL представлены в табл. 4.1.

Таблица 4.1

Операторы языка SQL

Вид

Название

Назначение

DDL

CREATE TABLE

Создание таблицы

DROP TABLE

Удаление таблицы

ALTER TABLE

Изменение структуры таблицы

^ CREATE INDEX

Создание индекса

DROP INDEX

Удаление индекса

DML

SELECT

Выборка записей

UPDATE

Изменение записей

INSERT

Вставка новых записей

DELETE

Удаление записей


Рассмотрим основные операторы языка SQL, реализованного в Access.

Инструкция CREATE TABLE.

Назначение: создание новой таблицы.

Синтаксис:

CREATE [TEMPORARY] TABLE таблица (поле_1 тип [(размер)] [NOT NULL]

[индекс_1] [, поле_2 тип [(размер)] [NOT NULL] [индекс_2] [, ...]]

[,составной_индекс [, ...]])

Аргументы инструкции CREATE TABLE:

таблица - имя создаваемой таблицы;

поле_1, поле_2 - имена одного или нескольких полей, создаваемых в новой таблице. Таблица должна содержать хотя бы одно поле;

тип - тип данных поля в новой таблице;

размер - размер поля в знаках (только для текстовых и двоичных полей);

индекс_1, индекс_2 - предложение CONSTRAINT, предназначенное для создания простого индекса;

составной_индекс - предложение CONSTRAINT, предназначенное для создания составного индекса.

Дополнительные сведения:

  • Если для поля добавлено ограничение NOT NULL, то при добавлении новых записей это поле должно содержать допустимые данные.

  • Предложение CONSTRAINT устанавливает различные ограничения на поле и может быть использовано для определения ключа. Кроме того, для создания ключа или дополнительного индекса для существующей таблицы можно использовать инструкцию CREATE INDEX.

  • Создаваемая временная (TEMPORARY) таблица будет доступна только в том сеансе, котором эта таблица была создана. По завершении данного сеанса она автоматически удаляется.

Например, инструкция

TABLE Разделы (Код_раздела INTEGER NOT NULL, Название TEXT(30) NOT NULL);

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

Предложение CONSTRAINT.

Назначение: создание или удаление индексов в инструкциях CREATE TABLE и ALTER TABLE.

Существуют два типа предложений CONSTRAINT: одно для создания простого индекса (по одному полю), а второе для создания составного индекса (по нескольким полям).

Синтаксис предложения CONSTRAINT для создания простого индекса:

CONSTRAINT имя {^ PRIMARY KEY | UNIQUE | NOT NULL |

REFERENCES внешняя таблица [(внешнее_поле_1, внешнее_поле_2)]

[ON UPDATE CASCADE | SET NULL]

[ON DELETE CASCADE | SET NULL]}

Синтаксис предложения CONSTRAINT для создания составного индекса:

CONSTRAINT имя

{PRIMARY KEY (ключевое_1[, ключевое_2 [, ...]]) |

UNIQUE (уникальное_1[, уникальное_2 [, ...]]) |

NOT NULL (непустое_1[, непустое_2 [, ...]]) |

FOREIGN KEY [NO INDEX] (ссылка_1[, ссылка_2 [, ...]])

REFERENCES внешняя таблица [(внешнее_поле_1 [, внешнее_поле_2 [, ...]])]

[^ ON UPDATE CASCADE | SET NULL]

[ON DELETE CASCADE | SET NULL]}

Аргументы предложения CONSTRAINT:

имя - имя создаваемого индекса;

ключевое_1, ключевое_2 - имена одного или нескольких полей, которые следует обозначить как ключевые;

уникальное_1, уникальное_2 - имена одного или нескольких полей, которые следует включить в уникальный индекс;

непустое_1, непустое_2 - имена одного или нескольких полей, в которых запрещаются значения Null;

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

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

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

Предложение CONSTRAINT позволяет создавать для поля индекс одного из описанных ниже типов:

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

  • Для создания ключа таблицы, состоящего из одного или нескольких полей, используется зарезервированные слова ^ PRIMARY KEY. Все значения ключа таблицы должны быть уникальными и отличаться от значения Null. Кроме того, в таблице может быть только один ключ.

Примечание. Зарезервированные слова PRIMARY KEY нельзя использовать при создании индекса в таблице, в которой уже определен ключ; в противном случае возникнет ошибка.

  • Для создания внешнего ключа используются зарезервированные слова ^ FOREIGN KEY. Если ключ внешней таблицы состоит из нескольких полей, необходимо использовать предложение CONSTRAINT, предназначенное для создания составного индекса. При этом следует перечислить все поля, содержащие ссылки на поля во внешней таблице, а также указать имя внешней таблицы и имена полей внешней таблицы, на которые ссылаются поля, перечисленные выше, причем в том же порядке. Если адресуемые поля являются ключами внешней таблицы, указывать эти поля не следует. Ограничения для внешних ключей определяют конкретные действия, выполняемые в случае изменения значения соответствующего ключа:

Например, инструкция

CREATE TABLE Книги (Код_книги TEXT(7) NOT NULL CONSTRAINT ключ2 PRIMARY KEY, Раздел TEXT(5) NOT NULL, Автор TEXT(40) NOT NULL, Название TEXT(50) NOT NULL);

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

Инструкция

CREATE TABLE Выдача_возврат (Код_книги TEXT(7) NOT NULL, Код_читателя TEXT(5) NOT NULL, Дата_выдачи DATETIME NOT NULL, Дата_возврата DATETIME, CONSTRAINT Ключ4 PRIMARY KEY (Код_книги, Дата_выдачи));

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

Инструкция ALTER TABLE.

Назначение: изменение структуры таблицы, созданной с помощью инструкции CREATE TABLE.

Синтаксис:

ALTER TABLE таблица {ADD {COLUMN поле тип [(размер)] [NOT NULL]

[CONSTRAINT индекс] | ALTER COLUMN тип поля[(размер)] |

CONSTRAINT составной_индекс} |

DROP {COLUMN поле | CONSTRAINT имя_индекса} }

Аргументы инструкции ALTER TABLE:

таблица - имя изменяемой таблицы;

поле - имя поля, добавляемого в таблицу или заменяемого в таблице или удаляемого из нее;

тип - тип данных поля;

размер - размер поля в знаках (только для текстовых и двоичных полей);

индекс - индекс для поля;

составной_индекс - описание составного индекса, добавляемого к таблице;

имя_индекса - имя составного индекса, который следует удалить.

С помощью инструкции ALTER TABLE существующую таблицу можно изменить несколькими способами:

  • Добавить новое поле в таблицу с помощью зарезервированных слов ^ ADD COLUMN. В этом случае необходимо указать имя поля, его тип и (для текстовых и двоичных полей) необязательный размер. Например, инструкция

ALTER TABLE Книги ADD COLUMN Год INTEGER NOT NULL;

добавит в таблицу Книги целочисленное поле Год.

  • Изменить тип существующего поля с помощью зарезервированных слов ^ ALTER COLUMN. В этом случае необходимо указать имя поля, его тип и (для текстовых и двоичных полей) необязательный размер. Например, инструкция ALTER TABLE Разделы ALTER COLUMN Код_раздела TEXT(5);

позволит в таблице Разделы изменить тип поля Код (первоначально определенный как INTEGER), переопределив это поле как текстовое.

  • Добавить составной индекс с помощью зарезервированных слов ^ ADD CONSTRAINT.

  • Удалить поле с помощью зарезервированных слов DROP COLUMN. В этом случае необходимо указать только имя поля. Например, инструкция

ALTER TABLE Книги DROP COLUMN Год;

удалит из таблицы Книги поле Год.

  • Удалить составной индекс с помощью зарезервированных слов ^ DROP CONSTRAINT. В этом случае необходимо указать только имя составного индекса, следующее за зарезервированным словом CONSTRAINT.

Инструкция CREATE INDEX.

Назначение: создание нового индекса для существующей таблицы.

Синтаксис:

CREATE [ UNIQUE ] INDEX индекс

ON таблица (поле [ASC|DESC][, поле [ASC|DESC], ...])

[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

Аргументы:

индекс - имя создаваемого индекса;

таблица - имя существующей таблицы, для которой создается индекс;

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

  • Зарезервированное слово UNIQUE используется для запрета совпадения значений индексированных полей в разных записях.

  • Необязательное предложение WITH позволяет задать условия на значения. Например:

  • С помощью параметра DISALLOW NULL запретить значения Null в индексированных полях новых записей.

  • С помощью параметра IGNORE NULL запретить включение в индекс записей, имеющих значения Null в индексированных полях.

  • С помощью зарезервированного слова ^ PRIMARY назначить индексированные поля ключом. Такой индекс по умолчанию является уникальным, следовательно, зарезервированное слово UNIQUE можно опустить. Зарезервированное слово PRIMARY нельзя использовать при создании нового индекса в таблице, в которой уже определен ключ; в противном случае возникнет ошибка.

Например, инструкция

^ CREATE INDEX Ключ1 ON Разделы(Код_раздела) WITH PRIMARY;

определит в таблице Разделы поле Код_раздела как ключевое.

Инструкция

CREATE INDEX Индекс ON Книги(Автор);

создаст в таблице Книги индекс по полю Автор.

Инструкция DROP.

Назначение: удаление таблицы, процедуры, представления из базы данных или удаление индекса из таблицы.

Синтаксис:

DROP {TABLE таблица | INDEX индекс ON таблица | PROCEDURE процедура | VIEW представление}

Аргументы:

таблица - имя таблицы, которую следует удалить или из которой следует удалить индекс;

процедура - имя удаляемой процедуры;

представление - имя удаляемого представления;

индекс - имя индекса, удаляемого из таблицы.

Например, инструкция

^ DROP INDEX Индекс ON Книги;

удалит из таблицы Книги индекс по полю Автор.

Инструкция

DROP TABLE Книги;

удалит из базы данных таблицу Книги.

Инструкция SELECT.

Назначение: возвращение данных из базы данных в виде набора записей.

Синтаксис:

SELECT [предикат] {* | таблица.* | [таблица.]поле_1

[AS псевдоним_1] [, [таблица.]поле_2 [AS псевдоним_2] [, ...]]}

^ FROM выражение [, ...] [IN внешняя_база_данных]

[WHERE... ]

[GROUP BY... ]

[HAVING... ]

[ORDER BY... ]

[WITH OWNERACCESS OPTION]

Аргументы:

предикат - один из следующих предикатов отбора: ALL, DISTINCT, DISTINCTROW или TOP. Предикаты используются для ограничения числа возвращаемых записей. По умолчанию используется предикат ALL;

* - указывает, что выбраны все поля заданной таблицы или таблиц;

таблица - имя таблицы, из которой должны быть отобраны записи;

поле_1, поле_2 - имена полей, из которых должны быть отобраны данные. Если включить несколько полей, они будут извлекаться в указанном порядке;

псевдоним_1, псевдоним_2 - имена, которые станут заголовками столбцов вместо исходных названий столбцов в таблице;

выражение - имена одной или нескольких таблиц, которые содержат отбираемые данные;

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

Минимальный синтаксис инструкции ^ SELECT:

SELECT поля FROM таблица.

Например, инструкция

SELECT * FROM Книги;

отберет все поля из таблицы Книги.

Инструкция

^ SELECT Книги.Код_книги, Книги.Автор, Книги.Название FROM Книги;

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

Предложение ^ WHERE определяет, какие записи из таблиц, перечисленных в предложении FROM, следует включить в результат выполнения инструкции SELECT. Предложение WHERE не является обязательным, однако, если оно присутствует, то должно следовать после предложения FROM.

Например, инструкция

^ SELECT Книги.Автор, Книги.Название

FROM Книги

WHERE (Книги.Автор=”Конан Дойл”);

отберет из таблицы Книги все книги Конан Дойла.

С помощью инструкции

^ SELECT Выдача_возврат.Код_книги

FROM Выдача_возврат

WHERE (Выдача_возврат.Дата_возврата IS NULL);

можно найти все коды книг, которые находятся на руках (выданы).

Предложение ORDER BY сортирует записи, полученные в результате запроса, в порядке возрастания (^ ASC) или убывания (DESC) на основе значений указанного поля или полей.

Синтаксис:

ORDER BY поле_1 [ASC | DESC] [, поле_2 [ASC | DESC]][, ...].

Если предложение ORDER BY содержит несколько полей, то сначала записи сортируются по первому полю. Затем записи, имеющие совпадающие значения в первом поле, сортируются по второму полю и т. д. По умолчанию используется порядок сортировки по возрастанию (от «A» до «Я» и от 0 до 9). Предложение ORDER BY не является обязательным.

Например, инструкция

^ SELECT Выдача_возврат.Код_книги

FROM Выдача_возврат

WHERE (Выдача_возврат.Дата_возврата IS NULL)

ORDER BY Выдача_возврат.Код_книги;

выведет коды книг, находящихся на руках, в алфавитном порядке.

Предложение GROUP BY объединяет записи с одинаковыми значениями в указанном списке полей в одну запись. Если инструкция ^ SELECT содержит статистическую функцию SQL, например Sum или Count, то для каждой записи будет вычислено итоговое значение.

Синтаксис:

GROUP BY группируемыеПоля.

При использовании предложения GROUP BY все поля в списке полей инструкции SELECT должны быть либо включены в предложение GROUP BY, либо использоваться в качестве аргументов статистической функции SQL. Значения NULL которые находятся в полях, заданных в предложении GROUP BY, группируются и не опускаются. Однако статистические функции SQL не обрабатывают значения NULL. Предложение GROUP BY не является обязательным.

Например, инструкция

SELECT Книги.Раздел, Count(Книги.Раздел) AS [Количество книг]

FROM Книги

GROUP BY Книги.Раздел;

позволит подсчитать количество книг из каждого раздела.

А с помощью инструкции

^ SELECT Выдача_возврат.Код_книги, Count(Выдача_возврат.Код_книги) AS [Сколько раз брали книгу]

FROM Выдача_возврат

GROUP BY Выдача_возврат.Код_книги;

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

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

Синтаксис:

HAVING условиеГруппировки.

Например, инструкция

^ SELECT Выдача_возврат.Код_книги, Count(Выдача_возврат.Код_книги) AS [Сколько раз брали книгу]

FROM Выдача_возврат

GROUP BY Выдача_возврат.Код_книги

HAVING Count(Выдача_возврат.Код_книги)>5;

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

Нередко для построения запроса необходимы данные не из одной, а из нескольких таблиц. Для этого используется какая-либо операция SQL: INNER JOIN, LEFT JOIN или RIGHT JOIN.

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

Синтаксис:

FROM таблица_1 INNER JOIN | LEFT JOIN | RIGHT JOIN таблица_2 ON таблица_1.поле_1 оператор таблица_2.поле_2,

где оператор – любой оператор сравнения.

Например, инструкция

^ SELECT Книги.Код_книги, Книги.Раздел, Книги.Автор, Книги.Название

FROM Книги LEFT JOIN Книги_на_руках ON Книги.Код_книги = Книги_на_руках.Код_книги

WHERE (Книги_на_руках.Код_книги IS NULL);

выдаст список книг, имеющихся в данный момент в библиотеке (не выданных). В приведенной инструкции Книги_на_руках - это имя запроса, отбирающего коды всех выданных книг.

Инструкция UPDATE.

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

Синтаксис:

UPDATE таблица

SET новое_значение

WHERE условие_отбора;

Аргументы инструкции UPDATE:

таблица - имя таблицы, данные в которой следует изменить;

новое_значение - выражение, определяющее значение, которое должно быть вставлено в указанное поле обновленных записей;

условие_отбора - выражение, отбирающее записи, которые должны быть изменены. При выполнении этой инструкции будут изменены только записи, удовлетворяющие указанному условию.

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

Например, инструкция

^ UPDATE Выдача_возврат SET Выдача_возврат.Дата_возврата = DATE()

WHERE (Выдача_возврат.Код_книги=”Д1”);

изменит значение NULL поля Дата_возврата для книги с кодом Д1 на значение текущей даты.

Инструкция INSERT INTO.

Назначение: добавление записи или записей в таблицу.

Синтаксис запроса на добавление нескольких записей:

INSERT INTO назначение [(поле_1[, поле_2[, ...]])] [IN внешняя_база_данных]

SELECT [источник.]поле_1[, поле_2[, ...]]

FROM выражение

Синтаксис запроса на добавление одной записи:

^ INSERT INTO назначение [(поле_1[, поле_2[, ...]])]

VALUES (значение_1[, значение_2[, ...]])

Аргументы инструкции INSERT INTO:

назначение - имя таблицы или запроса, в который добавляются записи;

поле_1, поле_2 - имена полей для добавления данных, если они следуют за аргументом назначение; имена полей, из которых берутся данные, если они следуют за аргументом источник;

внешняя_база данных - путь к внешней базе данных;

источник - имя таблицы или запроса, откуда копируются записи;

выражение - имена таблицы или таблиц, откуда вставляются данные. Это выражение может быть именем отдельной таблицы или результатом операции INNER JOIN, LEFT JOIN или RIGHT JOIN а также сохраненным запросом;

значение_1, значение_2 - значения, добавляемые в указанные поля новой записи. Каждое значение будет вставлено в поле, занимающее то же положение в списке: значение_1 вставляется в поле_1 новой записи, значение_2 - в поле_2 и т. д. Каждое значение текстового поля следует заключать в кавычки.

Например, инструкция

INSERT INTO Утерянные_книги (Код_книги)

SELECT Книги.Код_книги

FROM Книги

WHERE (Книги.Название="Мудрость отца Брауна");

добавит в таблицу Утерянные_книги код соответствующей книги.

Инструкция

NSERT INTO Книги (Код_книги, Раздел, Автор, Название)

VALUES ("Д5", "Д", "Неизвестный", "Интересная");

добавит в таблицу Книги информацию по новой книге.

Инструкция DELETE.

Назначение: удаление записей из одной или нескольких таблиц, перечисленных в предложении ^ FROM, которые удовлетворяют предложению WHERE.

Синтаксис:

DELETE [таблица.*]

FROM таблица

WHERE условие_отбора

Аргументы инструкции DELETE:

таблица - необязательное имя таблицы, из которой удаляются записи;

таблица - имя таблицы, из которой удаляются записи;

условие_отбора - выражение, определяющее удаляемые записи.

Дополнительные сведения:

  • Инструкция ^ DELETE особенно удобна для удаления большого количества записей.

  • Инструкцию DELETE можно использовать для удаления записей из таблиц, связанных отношением «один ко многим» с другими таблицами. Операции каскадного удаления приводят к удалению записей из таблиц, находящихся на стороне отношения «многие», когда в запросе удаляется соответствующая им запись на стороне «один». Например, в отношении между таблицами Книги и Выдача_возврат, таблица Книги расположена на стороне «один», а таблица Выдача_возврат - на стороне «многие». Если разрешить каскадное удаление, то удаление записи из таблицы Книги приведет к удалению соответствующих записей из таблицы Выдача_возврат.

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

Примечание. Записи, удаленные с помощью запроса на удаление, нельзя восстановить.

Например, инструкция

^ DELETE Выдача_возврат.*

FROM Выдача_возврат

WHERE ((Выдача_возврат.Дата_выдачи < #31/12/2000#) AND (Выдача_возврат.Дата_возврата IS NOT NULL));

удалит из таблицы Выдача_возврат все записи по книгам, выданным до 31 декабря 2000 года и возвращенным в библиотеку.

В заключение отметим, что, по словам Дейта, язык ^ SQL является гибридом реляционной алгебры и реляционного исчисления. В нем есть и элементы алгебры, и элементы исчисления. Кроме того, язык SQL обладает реляционной полнотой.


^ Тесты для самоконтроля



1. Отношением называют:

а) набор значений элементов данных одного типа;

б) множество кортежей;

в) некоторое подмножество декартова произведения доменов.

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

а) отношением;

б) доменом;

в) декартовым произведением k доменов.

3. В результате разности двух отношений получится отношение, содержащее записи, принадлежащие:

а) первому отношению;

б) одновременно двум отношениям;

в) либо первому отношению, либо второму, либо им обоим.

4. К числу операций, применяемых к отношениям с одной и той же схемой, относится операция:

а) объединения;

б) деления;

в) пересечения.

5. Соединяемые столбцы повторяются:

а) при естественном соединении;

б) при эквисоединении;

в) при тета – соединении.

6. Отношения могут комбинироваться только по одноименным столбцам:

а) при тета – соединении;

б) при естественном соединении;

в) при эквисоединении.

7. К одному отношению применяются следующие операции:

а) выборка, проекция;

б) объединение, разность, пересечение;

в) произведение, деление.


^ Задания для самостоятельного выполнения
1. Даны отношения: r, содержащее информацию о женщинах – читательницах библиотеки, и s, содержащее информацию о читателях – должниках.

r

Код

ФИО

Адрес

Телефон

Ж1

Петрова В.

Гамарника 1-1




Ж2

Иванова М.

Интернациональный 1-1

34-56-78

Ж3

Петрова М.

Ленина 1-2




s

Код

ФИО

Адрес

Телефон

М1

Иванов П..

Пирогова 1-1

2-34-56

М3

Петров П.

Ленина 1-2




Ж1

Петрова В.

Гамарника 1-1




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

Выдача Книги

Читатель

Код книги

Дата выдачи




Код книги

Иванов А.

Д1

01.09.02




Д1

Петров Б.

П1

01.09.02




П2

Сидоров В.

Д1

08.09.02







Макаров Г.

П2

09.09.02







Иванов А.

П1

08.09.02







Сидоров В.

П2

15.09.02







Петров Б.

Д2

15.09.02







Найти читателей, которые брали все указанные книги.
3. Используя инструкции ^ SQL,

а) создать таблицу Читатели с пятью полями: целочисленным полем Код и текстовыми полями определенного размера: Пол, Фамилия, Адрес, Телефон. Для всех полей, кроме последнего, определить обязательный ввод данных;

б) изменить структуру таблицы Читатели, переопределив тип поля Код с целочисленного на текстовый;

в) определить в таблице Читатели поле Код как ключевое;

г) создать индекс по полю Пол;

д) найти всех читателей, имеющих телефон;

е) подсчитать количество женщин и мужчин – читателей библиотеки;

ж) изменить фамилию какой-либо читательницы библиотеки;

з) добавить в таблицу информацию о новом читателе библиотеки;

и) удалить из таблицы информацию обо всех мужчинах – читателях библиотеки;

к) удалить из таблицы индекс по полю Пол;

л) удалить таблицу Читатели.
5. НОРМАЛИЗАЦИЯ ОТНОШЕНИЙ
5.1. Сущность нормализации
Под нормализацией отношений понимают процесс построения оптимальной структуры таблиц и связей в реляционной базе данных. Теория нормализации основана на том, что определенный набор таблиц обладает лучшими свойствами при включении, модификации и удалении данных, чем все остальные наборы таблиц, с помощью которых могут быть представлены те же данные.

^ Нормализованным отношением называют отношение, каждый домен которого содержит только атомарные значения.

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

Рассмотрим БД для консультанта университета, состоящую из одной таблицы Консультант со следующими полями: № студента, ФИО студента, № комнаты, № телефона, № курса, Семестр1, Оценка:



№ сту­дента

ФИО студента

№ комнаты

№ телефона

№ курса

Семестр

Оценка

3215

Васильев О.И.

115

2136

ТВПС

5

4

3215

Васильев О.И.

115

2136

МСиС

5

4

3215

Васильев О.И.

115

2136

БД

6

5

3215

Васильев О.И.

115

2136

ТВПС

6

5

3462

Воловик В.А.

206

2344

ТВПС

7

5

3462

Воловик В.А.

206

2344

БД

6

5

3462

Воловик В.А.

206

2344

ООП

6

3

3567

Борисов И.Ю.

115

2136

ВМ

7

4

3567

Борисов И.Ю.

115

2136

ПиП

5

4

3567

Борисов И.Ю.

115

2136

ОС

5

3

4756

Гатаулин А.Е.

254

3321

ООП

4

5



Проблема включения. Когда у консультанта появляется новый консультируемый им студент, для него необходимо включить в БД кортеж с пустыми ячейками атрибутов: Семестр, Оценка, что повлечет за собой аномалии при поиске и редактировании данных (например, в результате запроса «Выдать список фамилий и номеров студентов, получивших хотя бы одну оценку ниже 3» в число таких студентов попадут такие, которые не закончили ни одного курса).

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

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

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

Выясним различие между дублированием данных и избыточным дублированием данных.

Рассмотрим отношение СлужащийНачальник:

№ служащего

Начальник

125

Джонс

138

Смит

195

Смит

200

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


№ служащего

Начальник

125

Джонс

138

Смит

195




200



  1   2   3   4



Скачать файл (1261.5 kb.)

Поиск по сайту:  

© gendocs.ru
При копировании укажите ссылку.
обратиться к администрации