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

Глава 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