МИНИСТЕРСТВО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ ПЕРМСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИСТЕТ Кафедра "Математическое моделирование систем и процессов" МУНИЦИПАЛЬНОЕ ОБЩЕОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ЛИЦЕЙ №1 ВВЕДЕНИЕ В РЕЛЯЦИОННЫЕ БАЗЫ ДАННЫХ И ЯЗЫК SQL Учебно-методическое пособие Пермь 2001 УДК 681.3.016(075.8) Ф32 Рецензенты: Доктор экономических наук, профессор Пермского государственного университета В. И. Аверин Доктор технических наук, профессор Пермского государственного технического университета Н.А. Труфанов Федосеев С.А. Ф32 Введение в реляционные базы данных и язык SQL: Учебно- метод, пособие / Перм. гос. техн. ун-т. Пермь, 2001. - 41 с. Рассматриваются основные вопросы теории реляционной модели данных, проектирования реляционных баз данных, а также основные команды языка SQL. Приводятся задания для самостоятельного решения, способствующие усвоению курса. Рекомендуется студентам технических и экономических специальностей вузов, а также учащимся старших классов школ с углубленным изучением математики и информатики. Печатается по постановлению методической комиссии кафедры информатики лицея №1. УДК 681.3.016(075.8) © Пермский государственный технический университет, 2001 © Лицей№1, 2001 ЧАСТЬ I. Введение в реляционные базы данных Основные идеи современных информационных технологий базируются на концепции, согласно которой данные должны быть организованы в базы данных с целью адекватного отображения изменяющегося реального мира и удовлетворения информационных потребностей пользователей. Эти базы данных создаются и функционируют под управлением специальных программных комплексов, называемых системами управления базами данных (СУБД). Увеличение объема и структурной сложности хранимых данных, расширение круга пользователей информационных систем привели к широкому распространению наиболее удобных и сравнительно простых для понимания реляционных (табличных) баз данных, которые пришли на смену иерархическим и сетевым базам данных [8]. Первые реляционные программные продукты стали появляться в конце 1970-х - начале 1980-х годов. Сегодня таких продуктов более 200 [3]. Среди них такие СУБД, как DB2 корпорации IBM; ORACLE корпорации Oracle; INGRES компании Ingres Division of The ASK Group Inc.; SYBASE компании Sybase Inc.; IB Database компании Borland и многие другие. Принципы реляционной модели данных заложены в 1969-1970-х годах доктором Е.Ф. Коддом (E.F. Codd), в то время исследователем, работавшим в корпорации IBM. В конце 1968 года Е.Ф. Кодд, математик по образованию, впервые осознал, что математические дисциплины можно использовать для приведения в область управления базами данных строгих принципов и точности; именно таких качеств и недоставало этой области в то время. Что же такое реляционная модель? Достаточно точно ее характеризует следующее определение [3]: реляционная модель - это способ представления данных и правила работы с таким представлением. Точнее, реляционная модель связана с тремя аспектами данных: структурой, целостностью и обработкой данных. 1. Структура реляционной базы данных Реляционной называется такая база данных [4], которая воспринимается ее пользователями, как совокупность таблиц (и ничего иного кроме таблиц). Пример реляционной базы данных (РБД) "Поставки" приведен на рис. 1. Термин "реляционная" происходит от английского слова relation (отношение). Отношение - математический термин для обозначения таблицы, которая по сути является отношением (связью) между столбцами. Таким образом, РБД "Поставки" состоит из трех отношений (таблиц). Следует заметить, что между таблицами нет никаких явных связей, например, ссылок на физические адреса хранения в запоминающих устройствах. Таблицы состоят из строк (записей, кортежей) и столбцов (полей, атрибутов). Номер Номер фирмы Название Город Капитал продукта Название Сорт 1 Интер Москва 4 000 000 1 Икра 1 2 Аврора С.-Петербург 5 000 000 2 Икра 2 3 Дапьрыба Владивосток 1 300 000 3 Спагетти 1 4 Вита Москва 700 000 4 Апельсины 2 Склад Номер Номер Номер Количество партии фирмы продукта 1 1 3 100 2 2 4 200 3 3 1 450 4 3 2 80 Рис. 1. Реляционная база данных "Поставки" Пересечение строки и столбца образует элементарное значение данных, которое является наименьшей единицей данных в РБД. Такие значения рассматриваются как атомарные, т.е. они неразложимы. Атомарность значений в таблицах РБД обеспечивается недопущением повторяющихся групп. Повторяющаяся группа [4] - это столбец, который содержит множество значений данных в одной или нескольких строках, вместо ровно одного значения в каждой строке (рис. 2). Склад Продукт Номер Номер Название Сорт фирмы продукта 2 4 Икра 1,2 3 1,2 Спагетти 1 Рис. 2. Примеры повторяющихся групп 2. Целостность реляционных данных 2.1. Ключи Согласно математическому определению множество не содержит совпадающих элементов. В соответствии с этим таблица как множество строк в произвольный момент времени не может содержать строки, являющиеся дубликатами друг друга. Поэтому каждая таблица РБД обязательно содержит один или несколько столбцов, все значения или комбинации значений которых различны. Такой столбец или совокупность столбцов называется первичным ключом (primary key, обозначается pk). Например, столбец "Номер фирмы" является первичным ключом таблицы "Фирма", столбец "Номер продукта" - это первичный ключ таблицы "Продукт", а столбец "Номер партии" - первичный ключ таблицы "Склад" (рис. 1). Дадим общее определение ключа [4]. Пусть Г - таблица со столбцами Аи А2, ..., А„. Говорят, что множество столбцов А,у Aj, ..., Ак таблицы Г является возможным ключом тогда и только тогда, когда удовлетворяются два независящих от времени условия: уникальность и минимальность. Уникальность предполагает, что в произвольный момент времени никакие две различные строки таблицы Гне имеют одни и те же значения для Ah Ар ..., Ак. Минимальность предполагает, что ни один из столбцов Ait Aj, ..., Ак не может быть изъят из ключа без нарушения условия уникальности. В таблице "Продукт" (рис. 1) можно выделить ключ, состоящий из столбцов "Название" и "Сорт" В таблице "Склад" (рис. 1) можно выделить ключ, состоящий из трех столбцов: "Номер фирмы", "Номер продукта" и "Количество" Однако эта триада не будет удовлетворять условию минимальности, так как для образования ключа достаточно двух столбцов: "Номер фирмы" и "Номер продукта". Все ключи, отличные от первичного, называются альтернативными (alternate key, обозначается а к). Выше приведены примеры именно альтернативных ключей. Следует отметить, что таблица может иметь сразу несколько альтернативных ключей. Рассмотрим столбец "Номер фирмы" таблицы "Склад" (рис. 1). Ясно, что появление некоторого значения этого столбца, например 2, должно допускаться, только если это значение есть в столбце первичного ключа "Номер фирмы" таблицы "Фирма" В противном случае нельзя считать, что РБД находится в целостном состоянии. Например, в столбце "Номер фирмы" таблицы "Склад" в данный момент не может быть значения 7, так как в таблице "Фирма" нет строки со значением первичного ключа 7. Столбец "Номер фирмы" в таблице "Склад" является внешним ключом. В общем случае внешний ключ (foreign key, обозначается fk) - это столбец таблицы Г/ значение которого должно обязательно совпадать со значением первичного ключа некоторой другой таблицы Гг. Заметим, что внешний ключ и соответствующий ему первичный ключ должны иметь одну и ту же область определения, например множество целых чисел. 2.2. Правила целостности Сформулируем два правила целостности для РБД [4]. 1. Целостность по сущностям: не допускается, чтобы столбец первичного ключа какой-либо таблицы содержал неопределенное значение (null). Содержимое строк таблицы соответствует некоторым объектам реального мира. Например, строки таблицы "Фирма" (рис. 1) содержат данные о реальных поставщиках продуктов. Таким образом, если целостность по сущностям нарушена и первичный ключ не определен (имеет значение null для одной или нескольких строк), то это, по существу, эквивалентно отсутствию индивидуальности у объектов реального мира. 2. Целостность по ссылкам: если таблица Гг содержит внешний ключ fk, соответствующий первичному ключурк другой таблицы Г/, то каждое значение fk в Т2 должно: а) либо быть равным значению рк для некоторой строки таблицы Г/, б) либо быть неопределенным (иметь значение null). Случаю а) соответствуют примеры со столбцами "Номер фирмы" {fk) и "Номер продукта" {fk) из таблицы "Склад", соответствующие столбцам "Номер фирмы" {рк) таблицы "Фирма" и "Номер продукта" {рк) таблицы "Продукт" (рис. 1). Для случая б) можно привести пример с работником, который временно не относится ни к одному из подразделений предприятия. 2.3. Правила внешних ключей Чтобы обеспечить целостность РБД, удаление данных из таблиц РБД должно подчиняться одному’ из двух правил [3,4]: 1. Каскадное удаление: при удалении строки из таблицы Г/ удаляются также все строки из таблиц Г?,..., Г*, соответствующие строке из Г/ по внешнему ключу. Например, при удалении фирмы "Дальрыба" из таблицы "Фирма" в соответствии с правилом каскадного удаления должны быть удалены строки из таблицы "Склад" со значениями первичного ключа "Номер партии" 3 и 4 (рис. 1). 2. Ограниченное удаление: при удалении строки из таблицы Гу проверяется наличие в таблицах ..., 7* строк, соответствующих строке из Гу по внешнему ключу. Если такие строки в таблицах Гг,Г* имеются, то удаление отвергается. В соответствии с правилом ограниченного удаления фирму "Дальрыба" из таблицы "Фирма" в данный момент удалить нельзя, так как в таблице "Склад" есть строки со значениями первичного ключа "Номер партии" 3 и 4, для которых внешний ключ "Номер фирмы" имеет значение 3 (рис. 1). Только после удаления этих строк из таблицы "Склад" можно будет удалить фирму "Дальрыба" из таблицы "Фирма". Аналогично формулируются правила обновления данных в таблицах РБД. Читателю предлагается проделать это самостоятельно. 3. Обработка данных Третья часть реляционной модели, определяющая правила обработки данных, - это операторы. Основным компонентом этой части модели является так называема реляционная алгебра, которая состоит из набора операторов, использующих отношения (таблицы) в качестве аргументов и возвращающих отношения (таблицы) в качестве результата. Базис реляционной алгебры образуют шггь операторов: выборка, проекция, произведение, объединение и вычитание. Эти операторы не выражаются друг через друга и через них можно определить другие операторы реляционной алгебры. Более подробно теория реляционной алгебры изложена в [3,4]. б С помощью реляционных операторов выполняются реляционные операции, т.е. операции, которые обрабатывают данные в реляционной форме. Для описания реляционных операций, в частности, используется язык SQL (см. пример в п. 6.2.8). 4. Проектирование РБД 4.1. Принцип нормализации Проектирование РБД подчинено принципу нормализации, смысл которого заключается в том, что каждый факт должен храниться в одном месте. Например, в таблице "Фирма" РБД "Поставки" (рис. 1) принцип нормализации нарушен, так как несколько раз повторяется название Москвы. В РБД "Клиенты" (рис. 3) этого нарушения нет, так как города вынесены в отдельную таблицу. Однако в РБД "Клиенты" принцип нормализации нарушен в таблице "Сотрудник", где дважды повторяется должность "директор". Нормализация обеспечивает не только компактность, но и простоту' модификации данных. Например, если поменяется телефонный код Москвы, то в РБД "Клиенты" его необходимо изменить только в одной строке одной таблицы "Город" Более строго, руководство по нормализации - это набор стандартов проектирования данных, называемых нормальными формами. Общепринятыми считаются пять нормальных форм, хотя их было предложено значительно больше [1, 3, 8]. Далее рассмотрим три наиболее важных: первую, вторую и третью нормальные формы (1НФ, 2НФ, ЗНФ) [6]. Город Фирма Номер Название Код города Номер Номер фирмы Название Телефон города 1 Москва 095 2 Владивосток 4231 1 Квазар 965-81-34 1 3 С.-Петербург 812 2 Балтика 320-00-07 3 4 Екатеринбург 3432 3 Дальснаб 24-18-95 2 5 Омск 3812 4 Бифлекс 110-67-20 1 Сотрудник Номер / Номер сотрудника ФИО Должность фирмы 1 Иванов И.И. директор 4 2 Сидорова М.Н. гл. бухгалтер 4 3 Петров П.П. директор 2 Рис. 3. Реляционная база данных "Клиенты" В качестве примера рассмотрим РБД "Заказы книг" (рис. 4), изначально состоящую из одной таблицы "Заказ". Будем полагать, что заказчик не заказывает одну и ту же книгу в один и тот же день более одного раза. Тогда в таблице "Заказ" в качестве первичного ключа можно принять совокупность столбцов "Заказчик", "Дата заказа" и "ISBN". При этом схема таблицы "Заказ" будет иметь вид (столбцы, образующие первичный ключ, подчеркнуты): Заказ (заказчик, дата заказа. ISBN, название, автор, количество, цена, сумма) Таблица находится в первой нормальной форме тогда и только тогда, когда на любом пересечении строки и столбца находится единственное (атомарное) значение. Заказ Заказчик Дата ISBN Название Автор Кол-во Цена Сумма заказа ООО "Заря" 01.03.2001 966-506 История Блюм И.Ф. 10 55 550 России ЧП Петров 07.02.2001 103-900 Банковское Кац Л.Я. I 120 120 дело ЗАО "Юг" 01.03.2001 645-378 Фрукты и Лямин В.Т. 100 15 150 овощи ЧП Иванов 12.02.2001 103-900 Банковское Кац Л.Я. 2 120 240 дело Рис. 4. Реляционная база данных "Заказы книг", таблица "Заказ" в 1НФ Таким образом таблица "Заказ", представленная на рис. 4, уже находится в 1НФ. Отметим, что в этой таблице столбцы "Название", "Автор" и "Цена" могут быть определены частью первичного ключа, а именно "ISBN", тогда как столбец "Количество" зависит от всего ключа (соответственно полная и частичная функциональная зависимость от первичного ключа [3]). Следовательно, таблица "Заказ" не находится в 2НФ. Таблица находится во второй нормальной форме тогда и только тогда, когда она находится в первой нормальной форме и все ее ыеключевые столбцы полностью функционально зависят от первичного ключа. Для избавления от частичной функциональной зависимости первоначальную таблицу разбиваем на две - "Заказ" и "Книга" (рис. 5). При этом схемы таблиц "Заказ" и "10шга" будут иметь вид: Заказ (заказчик, дата заказа. ISBN, количество, сумма) KHueadSBN. название, автор, цена) Заметим, что возможны дальнейшие упрощения: столбцы "Количество" и "Сумма" в таблице "Заказ" являются взаимозависимыми, поэтому таблица "Заказ" не находится в ЗНФ. Таблица находится в третьей нормальной форме тогда и только тогда, когда она находится во второй нормальной форме и никакой из неключевых столбцов не является зависимым ни от какого другого неключевого столбца. Заказчик Дата ISBN Кол-во Сумма заказа ООО "Заря" 01.03.2001 966-506 10 550 ЧП Петров 07.02.2001 103-900 1 120 ЗАО "Юг" 01.03.2001 645-378 100 150 ЧП Иванов 12.02.2001 103-900 2 240 Книга ISBN Название Автор Цена 966-506 История Блюм И.Ф. 55 России 103-900 Банковское Кац Л.Я. 120 дело 645-378 Фрукты и Лямин В.Т. 15 овощи 103-900 Банковское Кац Л.Я. 120 дело Рис. 5. Реляционная база данных "Заказы книг", таблицы "Заказ" и "Книга" в 2НФ Поскольку в нашем примере столбец "Сумма" фактически является избыточным, то для получения таблицы "Заказ" в ЗНФ этот столбец можно просто удалить. Окончательно схемы таблиц "Заказ" и "Книга", находящихся в ЗНФ, будут иметь вид: Заказ (заказчик, дата заказа. ISBN, количество) Knu?.a(ISBN, название, автор, цена) Иногда для получения ЗНФ необходимо выразить зависимость между неключевыми столбцами в виде отдельных таблиц. Так, данные для сотрудников, работающих на различных проектах, можно хранить в таблице "Сотрудник" со следующей схемой: Сотрудник(табельпый номер, телефон, ставка, N.9 проекта, дата окончания) Очевидно, что таблица с такой схемой находится в 2НФ. Однако "№ проекта" и "Дата окончания" являются зависимыми столбцами. Разобьем таблицу "Сотрудник" на две: "Участник проекта" и "Проект" со схемами: Участник проекта(табельный номер, телефон, ставка, № проекта) Проект fNQ проекта, дата окончания) Теперь эти таблицы находятся в ЗНФ. В заключение зафиксируем алгоритм приведения ненормализованных таблиц в ЗНФ (рис. 6). Следует отметить, что принцип нормализации не является строго обязательным и в некоторых случаях может быть нарушен, например, для ускорения выборки данных из базы. Рис. 6. Алгоритм приведения ненормализованных таблиц в ЗНФ 4.2. Сущности Любой объект реального мира, информацию о котором мы хотим разместить в РБД, будем называть сущностью (entity). Каждая сущность характеризуется определенным набором данных, для хранения которого в РБД должна быть создана соответствующая таблица. Таким образом, при проектировании РБД термины "сущность" и "таблица" являются эквивалентными. Следует отметить, что при проектировании вместо термина "столбец" чаще используют аналогичный термин "поле". Сущности подразделяют натри основных класса [4]: 1) стержни - это независимые сущности. Например, таблицы "Фирма" и "Продукты" в РБД "Поставки" (рис. 1), таблица "Город" в РБД "Клиенты" (рис. 3); 2) ассоциации - это связи между двумя и более сущностями. Например, таблица "Склад" в РБД "Поставки" (рис. 1); 3) характеристики - это сущности, цель которых описание или уточнение других сущностей, например, таблица "Сотрудник" в РБД "Клиенты" (рис. 3). Ю