Глава 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СЫТНЫЙрынокЛенинградСытнинская, 32329916
2ПОРТОСкооперативРезекнеСадовая, 27317664
3ШУШАРЫсовхозПушкинНовая, 174705038
4ТУЛЬСКИЙуниверсамЛенинградТульская, 32710837
5УРОЖАЙкоопторгЛугаПесчаная, 19789000
6ЛЕТОагрофирмаЛенинградПулковское ш.,82939729
7ОГУРЕЧИКфермаПаневежисУкмерге, 15127331
8КОРЮШКАкооперативЙыхвиНарвское ш., 64432123

Состав Поставки
БЛПРВесБЛПРВесБЛПРВесБЛПРВес
111100911251673524880
115809133516615247100
1125912151614924540
14159315163524630
216510270179150241620
29401072501775024310
2113510320171325241410
21220101415173202515120
252010125171210251635
242011210017145251430
311551192018814025820
315551110201863025320
365011351814152615150
312201112218510261620
310151273501816152632
3165121335195120271550
4250123519745277150
411501216519102027580
444013118019315271635
493513111002013502732
452013104020775288100
412513122020157528520
5280133520161028620
5940141902035281615
53251475021137028310
512514620216302915150
618014101021320299200
611150143521520291615
643014125211615301570
6121014143228140301610
7612515210022630317150
71615159202214203115150
887515520221615311625
865015320225832178
81615151010231125033178
918015125231065331625
910301651202332033775
ПСПРЦенаК_во
19
1111.5050
1123.0010
1152.00170
213.60300
23
251.80100
263.6080
28
370.40200
39
3122.5020
3151.50200
42
442.0450
4130.88150
414
4160.94200
4174.5050
543.0050
59
5100.50130
511
5131.2040
5140.5070
5161.0050
6100.7090
611
612
714.2070
734.00250
762.20140
77
781.00150
82
852.0070
8111.00100

ПродуктыНаличие
ПР ПродуктБелкиЖирыУглевKCaNaB2PPC
1Говядина189.124.0.3150906001.528.0
2Судак190.80.0.187027001.110.30
3Масло60.825.90.2302207400.11.0
4Майонез31.670.26.48028000.0.0
5Яйца127.115.7.15305507104.41.90
6Сметана26.300.28.9508503201.1.2
7Молоко28.32.47.1460121015001.31.10
8Творог167.90.13.1120164014102.74.5
9Морковь13.1.70.20005102100.79.950
10Лук17.0.95.17503101800.22.100
11Помидоры6.0.42.2901404000.45.3250
12Зелень9.0.20.340275751.24.380
13Рис70.6.773.5402402600.416.0
14Мука106.13.732.17602401201.222.0
15Яблоки4.0.113.24801602600.33.130
16Сахар0.0.998.3020100.0.0
17Кофе127.36.9.97101801800.31.80
ПР К_воСтоим
1108429.84
200.00
373274.61
43997.46
561111.83
688206.60
721483.08
89282.80
900.00
107746.30
114651.70
121334.96
135451.14
149143.77
15117189.92
169896.14
1737166.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). Таблицы Выбор объединяются по мере их создания в общую таблицу Выбрано, по которой определяют, сколько порций того или иного блюда надо приготовить для каждой трапезы.

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

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

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

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

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

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

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

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

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

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