Глава 1. Реляционные базы данных и язык SQL

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

Реляционная база данных представляется пользователю как совокупность таблиц и ничего кроме таблиц. На рис.1.1 приведен пример реляционной базы данных ПАНСИОН. Этот простой пример используется для иллюстрации большинства вопросов, рассматриваемых в нашей книге. Поэтому советуем потратить немного времени, чтобы хорошо с ним разобраться^*.

Кладовая пансионата периодически пополняется продуктами из списка, часть которого показана в таблице Продукты. Каждый продукт имеет кроме названия (столбец Продукт) уникальный номер этого продукта (столбец ПР). Химический состав продуктов приведен для 1 кг их съедобной части: основные пищевые вещества (белки, жиры и углеводы) даны в граммах, а минеральные вещества (калий, кальций, натрий) и витамины (B2, PP, C) - в миллиграммах.

В таблице Блюда представлены уникальные номера блюд (столбец БЛ), их названия, коды видов (см. таблицу Вид_блюд), основной продукт (столбец Основа), масса порции в граммах (столбец Выход) и приведенная стоимость в копейках приготовления одной порции (столбец Труд).

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

Таблица Состав связывает между собой таблицы Блюда и Продукты, оговаривая, какая масса (в граммах) того или иного продукта (столбец Вес) должна входить в состав одной порции блюда. Так, порция блюда с номером 12 (Суп молочный) должна состоять из 350 г продукта с номером 7 (Молоко), 35 г продукта с номером 13 (Рис), 5 г продукта с номером 3 (Масло) и 5 г продукта с номером 16 (Сахар).

Шеф-повар ежедневно получает от завхоза сведения о количестве в килограммах имеющихся продуктов и их текущей стоимости (столбцы К_во и Стоимость таблицы Наличие). Используя эти сведения он определяет по таблице Состав перечень тех блюд, которые можно приготовить из этих продуктов, а также калорийность и стоимость таких блюд. При этом стоимость блюда складывается из стоимости и массы продуктов, необходимых для приготовления одной его порции, а также из трудозатрат на ее приготовление (см. таблицу Блюда). Калорийность же определяется по массе и калорийности каждого из продуктов блюда. (Для получения значения калорийности продукта исходят из того, что при окислении 1 г углеводов или белков в организме освобождается в среднем 4.1 ккал, а при окислении 1 г жиров - 9.3 ккал.)

Блюда

Рецепты

БЛ Блюдо В Основа Выход Труд 1 Салат летний З Овощи 200. 3 2 Салат мясной З Мясо 200. 4 3 Салат витаминный З Овощи 200. 4 4 Салат рыбный З Рыба 200. 4 5 Паштет из рыбы З Рыба 120. 5 6 Мясо с гарниром З Мясо 250. 3 7 Сметана З Молоко 140. 1 8 Творог З Молоко 140. 2 9 Суп харчо С Мясо 500. 5 10 Суп-пюре из рыбы С Рыба 500. 6 11 Уха из судака С Рыба 500. 5 12 Суп молочный С Молоко 500. 3 13 Бастурма Г Мясо 300. 5 14 Бефстроганов Г Мясо 210. 6 15 Судак по-польски Г Рыба 160. 5 16 Драчена Г Яйца 180. 4 17 Морковь с рисом Г Овощи 260. 3 18 Сырники Г Молоко 220. 4 19 Омлет с луком Г Яйца 200. 5 20 Каша рисовая Г Крупа 210. 4 21 Пудинг рисовый Г Крупа 160. 6 22 Вареники ленивые Г Молоко 220. 4 23 Помидоры с луком Г Овощи 260. 4 24 Суфле из творога Г Молоко 280. 6 25 Рулет с яблоками Д Фрукты 200. 5 26 Яблоки печеные Д Фрукты 160. 3 27 Суфле яблочное Д Фрукты 220. 6 28 Крем творожный Д Молоко 160. 4 29 "Утро" Н Фрукты 200. 5 30 Компот Н Фрукты 200. 2 31 Молочный напиток Н Молоко 200. 2 32 Кофе черный Н Кофе 200. 1 33 Кофе на молоке Н Кофе 200. 2

БЛ Рецепт 1 Помидоры ... 2 Вареное ... 3 Зелень ме... 4 Вареные р... 5 Филе суда... 6 Мясо варе... 7 Сметану п... 8 Протертый .. 9 Грудинку ... 10 Филе суда... 11 Судак очи... 12 Промытый ... 13 Мясо наре... 14 Говядину ... 15 Подготовл... 16 Сырые яйц... 17 Нарезать ... 18 В протерт... 19 К свежим ... 20 Рис свари... 21 Готовую р... 22 В протерт... 23 Спассеров... 24 В протерт... 25 Очистить ... 26 Не прорез... 27 Запеченны... 28 Яйца разм... 29 Очищенную .. 30 Яблоки оч... 31 Яблоки на... 32 Кофеварку .. 33 Сварить ч...

Поставщики

ПС Название Статус Город Адрес Телефон 1 СЫТНЫЙ рынок Ленинград Сытнинская, 3 2329916 2 ПОРТОС кооператив Резекне Садовая, 27 317664 3 ШУШАРЫ совхоз Пушкин Новая, 17 4705038 4 ТУЛЬСКИЙ универсам Ленинград Тульская, 3 2710837 5 УРОЖАЙ коопторг Луга Песчаная, 19 789000 6 ЛЕТО агрофирма Ленинград Пулковское ш.,8 2939729 7 ОГУРЕЧИК ферма Паневежис Укмерге, 15 127331 8 КОРЮШКА кооператив Йыхви Нарвское ш., 64 432123

Состав

Поставки

БЛ ПР Вес БЛ ПР Вес БЛ ПР Вес БЛ ПР Вес 1 11 100 9 11 25 16 7 35 24 8 80 1 15 80 9 13 35 16 6 15 24 7 100 1 12 5 9 12 15 16 14 9 24 5 40 1 4 15 9 3 15 16 3 5 24 6 30 2 1 65 10 2 70 17 9 150 24 16 20 2 9 40 10 7 250 17 7 50 24 3 10 2 11 35 10 3 20 17 13 25 24 14 10 2 12 20 10 14 15 17 3 20 25 15 120 2 5 20 10 12 5 17 12 10 25 16 35 2 4 20 11 2 100 17 14 5 25 14 30 3 11 55 11 9 20 18 8 140 25 8 20 3 15 55 11 10 20 18 6 30 25 3 20 3 6 50 11 3 5 18 14 15 26 15 150 3 12 20 11 12 2 18 5 10 26 16 20 3 10 15 12 7 350 18 16 15 26 3 2 3 16 5 12 13 35 19 5 120 27 15 50 4 2 50 12 3 5 19 7 45 27 7 150 4 11 50 12 16 5 19 10 20 27 5 80 4 4 40 13 1 180 19 3 15 27 16 35 4 9 35 13 11 100 20 13 50 27 3 2 4 5 20 13 10 40 20 7 75 28 8 100 4 12 5 13 12 20 20 15 75 28 5 20 5 2 80 13 3 5 20 16 10 28 6 20 5 9 40 14 1 90 20 3 5 28 16 15 5 3 25 14 7 50 21 13 70 28 3 10 5 12 5 14 6 20 21 6 30 29 15 150 6 1 80 14 10 10 21 3 20 29 9 200 6 11 150 14 3 5 21 5 20 29 16 15 6 4 30 14 12 5 21 16 15 30 15 70 6 12 10 14 14 3 22 8 140 30 16 10 7 6 125 15 2 100 22 6 30 31 7 150 7 16 15 15 9 20 22 14 20 31 15 150 8 8 75 15 5 20 22 16 15 31 16 25 8 6 50 15 3 20 22 5 8 32 17 8 8 16 15 15 10 10 23 11 250 33 17 8 9 1 80 15 12 5 23 10 65 33 16 25 9 10 30 16 5 120 23 3 20 33 7 75

ПС ПР Цена К_во 1 9 1 11 1.50 50 1 12 3.00 10 1 15 2.00 170 2 1 3.60 300 2 3 2 5 1.80 100 2 6 3.60 80 2 8 3 7 0.40 200 3 9 3 12 2.50 20 3 15 1.50 200 4 2 4 4 2.04 50 4 13 0.88 150 4 14 4 16 0.94 200 4 17 4.50 50 5 4 3.00 50 5 9 5 10 0.50 130 5 11 5 13 1.20 40 5 14 0.50 70 5 16 1.00 50 6 10 0.70 90 6 11 6 12 7 1 4.20 70 7 3 4.00 250 7 6 2.20 140 7 7 7 8 1.00 150 8 2 8 5 2.00 70 8 11 1.00 100

Продукты

Наличие

ПР Продукт Белки Жиры Углев K Ca Na B2 PP C 1 Говядина 189. 124. 0. 3150 90 600 1.5 28. 0 2 Судак 190. 80. 0. 1870 270 0 1.1 10. 30 3 Масло 60. 825. 90. 230 220 740 0.1 1. 0 4 Майонез 31. 670. 26. 480 280 0 0. 0. 0 5 Яйца 127. 115. 7. 1530 550 710 4.4 1.9 0 6 Сметана 26. 300. 28. 950 850 320 1. 1. 2 7 Молоко 28. 32. 47. 1460 1210 1500 1.3 1. 10 8 Творог 167. 90. 13. 1120 1640 1410 2.7 4. 5 9 Морковь 13. 1. 70. 2000 510 210 0.7 9.9 50 10 Лук 17. 0. 95. 1750 310 180 0.2 2. 100 11 Помидоры 6. 0. 42. 290 140 400 0.4 5.3 250 12 Зелень 9. 0. 20. 340 275 75 1.2 4. 380 13 Рис 70. 6. 773. 540 240 260 0.4 16. 0 14 Мука 106. 13. 732. 1760 240 120 1.2 22. 0 15 Яблоки 4. 0. 113. 2480 160 260 0.3 3. 130 16 Сахар 0. 0. 998. 30 20 10 0. 0. 0 17 Кофе 127. 36. 9. 9710 180 180 0.3 1.8 0

ПР К_во Стоим 1 108 429.84 2 0 0.00 3 73 274.61 4 39 97.46 5 61 111.83 6 88 206.60 7 214 83.08 8 92 82.80 9 0 0.00 10 77 46.30 11 46 51.70 12 13 34.96 13 54 51.14 14 91 43.77 15 117 189.92 16 98 96.14 17 37 166.50

Вид_блюд	Трапезы	Меню	Выбор	Выбрано
В Вид З Закуска С Суп Г Горячее Д Десерт Н Напиток	Т Трапеза 1 Завтрак 2 Обед 3 Ужин	Т В БЛ Т В БЛ Т В БЛ 1 З 3 2 З 1 3 З 6 1 З 6 2 З 6 3 З 8 1 Г 19 2 С 9 3 Г 20 1 Г 21 2 С 12 3 Г 16 1 Н 31 2 Г 14 3 Н 30 1 Н 32 2 Г 16 3 Н 31 2 Г 18 2 Д 26 2 Д 28	СМ Т В БЛ 2 1 З 3 2 1 Г 19 2 1 Н 31 2 2 З 1 2 2 С 12 2 2 Г 16 2 2 Д 26 2 3 З 8 2 3 Г 21 2 3 Н 32	СМ Т БЛ 1 1 3 1 1 21 . 2 2 16 2 2 26 . 3 1 6 . 32 3 30

Рис. 1.1. Основные таблицы базы данных ПАНСИОН

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

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

Таблица Поставки связывает между собой таблицы Продукты и Поставщики, оговаривая, какое количество продукта (столбец К_во) и по какой цене поставил тот или иной поставщик. Отсутствие в строке цены и количества говорит о том, что поставщик ПС может поставлять продукт ПР, но в данный момент не осуществил такой поставки.

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

• всякому столбцу таблицы присвоено имя, которое должно быть уникальным для этой таблицы;
• столбцы таблицы упорядочиваются слева направо, т.е. столбец 1, столбец 2, ..., столбец n. С математической точки зрения это утверждение некорректно, потому что в реляционной системе столбцы не упорядочены. Однако с точки зрения пользователя, порядок, в котором определены имена столбцов, становится порядком, в котором должны вводиться в них данные, если не предварять при вводе каждое значение именем соответствующего столбца (подробнее это описано в Приложении А литературы [2]);
• строки таблицы не упорядочены (их последовательность определяется лишь последовательностью ввода в таблицу);
• в поле на пересечении строки и столбца любой таблицы всегда имеется только одно значение данных и никогда не должно быть множества значений (правда, это "атомарное" значение может быть достаточно объемным, например, таким, как рецепт блюда);
• всем строкам таблицы соответствует одно и то же множество столбцов, хотя в определенных столбцах любая строка может содержать пустые значения (NULL-значения), т.е. может не иметь значений для этих столбцов;
• все строки таблицы обязательно отличаются друг от друга хотя бы единственным значением, что позволяет однозначно идентифицировать любую строку такой таблицы;
• при выполнении операций с таблицей ее строки и столбцы можно обрабатывать в любом порядке безотносительно к их информационному содержанию.

Почему же база данных, составленная из таких таблиц, называется реляционной? А потому, что отношение - relation - просто математический термин для обозначения неупорядоченной совокупности однотипных записей или таблиц определенного специфического вида, описанного выше. Таким образом, можно, например, сказать, что база данных ПАНСИОН состоит из одиннадцати отношений.

Реляционные системы берут свое начало в математической теории множеств. Они были предложены в конце 1968 года доктором Э.Ф.Коддом из фирмы IBM, который первым осознал, что можно использовать математику для придания надежной основы и строгости области управления базами данных.

Нечеткость многих терминов, используемых в сфере обработки данных, заставила Кодда отказаться от них и придумать новые или дать более точные определения существующим. Так, он не мог использовать широко распространенный термин "запись", который в различных ситуациях может означать экземпляр записи, либо тип записей, запись в стиле Кобола (которая допускает повторяющиеся группы) или плоскую запись (которая их не допускает), логическую запись или физическую запись, хранимую запись или виртуальную запись и т.д. Вместо этого он использовал термин "кортеж длины n" или просто "кортеж", которому дал точное определение. В литературе [2,3] можно подробно познакомиться с терминологией реляционных баз данных, а здесь мы будем использовать неформальные их эквиваленты:

таблица	для отношения,
строка или запись	для кортежа,
столбец или поле	для атрибута.

Мы также принимаем, по определению, что "запись" означает "экземпляр записи", а "поле" означает "имя и тип поля".

^* Так как иллюстративная база данных создавалась для лекционного курса в 1988 году, когда существовали "смешные" цены, а также исчезнувшие названия статусов (коопторг) и городов (Ленинград), то автор пытался несколько раз ее модифицировать. Однако поняв, что изменение цен, статусов и названий идет быстрее, чем подготовка и, тем более, выпуск издания, он решил сохранить в книге старые цены и названия.

Предисловие | Содержание | 1.2