Базы данных лаборатории хлебной базы

Определения, обозначения и сокращения Введение

1. Техническое задание

1.1 Основания для разработки

1.2 Задание

2. Технический проект

2.1 Неформализованное описание предметной области

2.2 Словарь понятий и терминов

2.3 Структура таблиц базы данных

2.4 Диаграмма сущность-связь для предметной области на логическом уровне

2.5 Ограничения на связи между таблицами базы данных хлебной базы

2.5.1 Функциональные зависимости для сущности groats

2.5.2 Функциональные зависимости для сущности laborants

2.5.3 Функциональные зависимости для сущности silo

2.5.4 Функциональные зависимости для сущности transport

2.5.5 Функциональные зависимости для сущности grain_elevator

2.5.6 Функциональные зависимости для сущности reseivs

2.5.7 Функциональные зависимости для сущности dispatch

2.5.8 Функциональные зависимости для сущности analysis card

2.5.9 Ограничения, накладываемые на связи между сущностями

3. Проектирование оптимальной структуры базы данных методом синтеза

3.1 Исходное множество функциональных зависимостей

3.2 Неизбыточное покрытие множества ФЗ

3.3 Леворедуцированное покрытие множества ФЗ

3.4 Праворедуцированное покрытие множества ФЗ

3.5 Минимальное покрытие

3.6 Редуцированное минимальное кольцевое покрытие

4. Оптимизация запросов к базе данных

4.1 Статистика по таблицам базы данных

4.2 Многотабличный запрос на выборку по условию

4.2.1 Выражение для запроса с использованием реляционной алгебры

4.2.2 Исходное операционное дерево для запроса

4.2.3 Оптимизированное операционное дерево для запроса

4.2.4 Исходный запрос на языке SQL

4.2.5 Оптимизированный запрос на языке SQL

4.2.6 Расчет сложности запроса

4.3 Многотабличный запрос на выборку по условию

4.3.1 Выражение для запроса с использованием реляционной алгебры

4.3.2 Исходное операционное дерево для запроса

4.3.3 Оптимизированное операционное дерево для запроса

4.3.4 Исходный запрос на языке SQL

4.3.5 Оптимизированный запрос на языке SQL

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

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

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

1. Техническое задание

1.1 Основания для разработки Необходимо разработать базу данных, реализующую лабораторию хлебной базы. Основанием для разработки послужило задание на курсовую работу по дисциплине «Базы данных» .

1.2 Задание Спроектировать базу данных для лаборатории хлебной базы. Описать предметную область, разработать словарь понятий и терминов, построить систему функциональных зависимостей. Синтезировать схему базы данных на основании функциональных зависимостей (построение неизбыточного покрытия, построение леворедуцированного неизбыточного покрытия, построение праворедуцированного неизбыточного покрытия, построение классов эквивалентностей, построение минимального покрытия). Построить уточненную концептуальную модель в виде ER-диаграммы, запросы предметной области. Записать минимизированные запросы на языке SQL. Рассчитать сложность запросов.

2. Технический проект

2.1 Неформализованное описание предметной области Хлебная база представляет собой предприятие, основу деятельности которого составляет приемка, хранение и транспортировка круп различного вида и сорта. Крупы отличаются по названию, назначению (виду), сорту, году урожая и месту урожая. Крупы распределяются по партиям, каждая из которых включает крупу только определенного вида, сорта, года урожая и места урожая. Партии круп хранятся на элеваторах. Элеватор характеризуется определенным объемом вместимости и состоит из более мелких по объему хранилищ — силосов. В каждом силосе хранится только одна определенная партия крупы. Каждую партию крупы отправляют на анализ в лабораторию. Все виды лабораторных анализов круп осуществляют лаборанты. Каждый лаборант заполняет лабораторную карту для каждой партии крупы, в которой определяется принадлежность ее к сорту и назначению.

2.2 Словарь понятий и терминов В описанной предметной области можно выделить следующие сущности:

Словарь терминов для объектов и элементов данных таблицы «Groats» представлен в таблице 1.

Таблица 1 Крупа — Groats

Словарь терминов для объектов и элементов данных таблицы «Silo» представлен в таблице 2.

Таблица 2 Силос — Silo

Словарь терминов для объектов и элементов данных таблицы «grain_elevator» представлен в таблице 3.

Таблица 3 Элеватор — grain_elevator

Словарь терминов для объектов и элементов данных таблицы «Transport» представлен в таблице 4.

Таблица 4 Транспорт — Transport

Словарь терминов для объектов и элементов данных таблицы «Dispatch» представлен в таблице 5.

Таблица 5 Отправка — Dispatch

Словарь терминов для объектов и элементов данных таблицы «Reseivs» представлен в таблице 6.

Таблица 6 Получение — Reseivs

Словарь терминов для объектов и элементов данных таблицы «Analysis card» представлен в таблице 7.

Таблица 7 Анализная карточка — Analysis card

Словарь терминов для объектов и элементов данных таблицы «Laborants» представлен в таблице 8.

Таблица 8 Лаборанты — Laborants

Словарь терминов для объектов и элементов данных таблицы «Current_elevator» представлен в таблице 9.

Таблица 9 Текущая заполненность элеваторов — Current_elevator

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

Структура таблицы Dispatch представлена в таблицах 10−11.

Таблица 10

Таблица 11 Структура таблицы Dispatch

Таблица 12 Ключи в таблице Dispatch

Структура таблицы Silo представлена в таблицах 12−13.

Таблица 13 Структура таблицы Silo

Таблица 14 Ключи в таблице Silo

Структура таблицы grain_elevator представлена в таблицах 14−15.

Таблица 15

Таблица 15 Структура таблицы grain_elevator

Таблица 16 Ключи в таблице grain_elevator

Структура таблицы Transport представлена в таблицах 16−17.

Таблица 17 Структура таблицы Transport

Таблица 18 Ключи в таблице Transport

Структура таблицы Receivs представлена в таблицах 18−19.

Таблица 19 Структура таблицы Receivs

Таблица 20

Таблица 20 Ключи в таблице Receivs

Структура таблицы Groats представлена в таблицах 20−21.

Таблица 21 Структура таблицы Groats

Таблица 22 Ключи в таблице Groats

Структура таблицы Laborants представлена в таблицах 22−23.

Таблица 23 Структура таблицы Laborants

Таблица 24 Ключи в таблице Laborants

Структура таблицы current_elevator представлена в таблицах 24−25.

Таблица 25 Структура таблицы current_elevator

Таблица 26 Ключи в таблице current_elevator

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

Рисунок 1 — Диаграмма сущность-связь

2.5 Ограничения на связи между таблицами базы данных хлебной базы

2.5.1 Функциональные зависимости для сущности groats

Нельзя, чтобы одна партия зерна содержала зерно разного вида;

Num_part->nam;

Нельзя, чтобы одна партия зерна содержала зерно разного типа;

Num_part->needs;

Нельзя, чтобы одна партия зерна содержала зерно разного сорта;

Num_part->sort

Нельзя, чтобы одна партия зерна содержала зерно из различных мест сбора урожая;

Num_part->place;

Нельзя, чтобы одна партия зерна содержала зерно разного года сбора урожая;

Num_part->year;

2.5.2 Функциональные зависимости для сущности laborants

У разных лаборантов не может быть один и тот же табельный номер;

Tab_numb->lastname, name, secondname, birthdate

2.5.3 Функциональные зависимости для сущности silo

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

Num_sil->sort_groat

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

Num_sil->needs

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

Num_sil->type_groat

2.5.4 Функциональные зависимости для сущности transport

Одна и та же машина не может иметь разную предельную вместимость;

Num->lim_weight_c

Одна и та же машина не может работать одновременно в разные смены;

Num->hours

2.5.5 Функциональные зависимости для сущности grain_elevator

Один и тот же элеватор не может иметь различный предельный объем заполнения

Number_el->lim_weight_el

Один и тот же элеватор не может иметь в один и тот же момент времени различный объем фактического заполнения

Number_el-> weight_el

2.5.6

2.5.6 Функциональные зависимости для сущности reseivs

Одна и та же машина не может привозить разные партии зерна;

Num_part->num_trans

Нельзя, чтобы 1 и та же машина привозила в один и тот же день партии крупы разного вида;

Num_part, Num_trans, data->name_groat

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

Num_part, Num_trans, data->from

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

Num_part, Num_trans, data->hours

2.5.7 Функциональные зависимости для сущности dispatch

Одна и та же машина не может увозить разные партии зерна;

Num_part->num_trans

Нельзя, чтобы 1 и та же машина увозила в один и тот же день партии крупы разного вида;

Num_part, Num_trans, data->name_groat

Нельзя, чтобы 1 и та же машина увозила в один и тот же день партии крупы в разные места назначения;

Num_part, Num_trans, data->destin

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

Num_part, Num_trans, data->hours

2.5.8 Функциональные зависимости для сущности analysis card

Одна и та же анализная карточка не может быть заполнена в разные дни;

Num_card->date_an

Одна и та же анализная карточка не может быть заполнена для разного вида крупы;

Num_card ->name_groat

Одна и та же анализная карточка не может быть заполнена для разного типа крупы;

Num_card ->type_groat

Одна и та же анализная карточка не может быть заполнена для крупы разных лет сбора урожая;

Num_card ->year

Одна и та же анализная карточка не может быть заполнена для крупы из разных мест сбора урожая;

Num_card ->place

Одна и та же анализная карточка не может быть заполнена более чем одним лаборантом в один и тот же день.

Num_card ->tab_nam_lab, lastname_lab

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

Num_card-> Tab_num_lab, date_an

Tab_num_lab ->Num_card, date_an

Нельзя, чтобы в 1 и тот же силос привозилась крупа более чем 1 типа;

Number_s->name_groat

Нельзя, чтобы в 1 и тот же силос привозилась крупа более чем 1 вида;

Number_s->needs

Нельзя, чтобы 1 и той же партии зерна соответствовало более 1 анализной карточки;

Num_part->num_card

Нельзя, чтобы разным номерам элеваторов соответствовали один и те же силосы;

Num_el->num_sil

Нельзя, чтобы один силос находился в разных элеваторах;

num_sil -> Num_el

3. Проектирование оптимальной структуры базы данных методом синтеза

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

num_part -> nam, needs, sort, place, year

tab_numb -> name, lastname, secondname, birthdate

num_sil -> sort, needs, type

num -> lim_weight_c, hours

number_el -> lim_weight_el

number_el, data -> occupied

num_part -> num_trans

num_part, num_trans, data -> name_groat, from, hours

number_s -> name_groat

number_s -> needs

num_part -> num_card

num_el -> num_sil

num_part, num_trans, data -> destin, hours

num_card, date_an -> name_groat, type_groat, year

num_card, date_an -> place, tab_num_lab, lastname_lab

Рисунок 2 — Функциональные зависимости БД лаборатории хлебной базы

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

g:

tab_numb -> name, lastname, secondname, birthdate

num_sil -> sort, needs, type

num -> lim_weight_c, hours

number_el -> lim_weight_el

number_el, data -> occupied

num_el -> num_sil

num_part -> nam, needs, sort, place, year, num_trans, num_card

num_part, data -> name_groat, from, destin, hours

number_s -> name_groat, needs

num_card, date_an -> name_groat, type_groat, year, place, tab_num_lab, lastname_lab

1. f=g tab_numb -> name, lastname, secondname, birthdate

Проверяем: f |= tab_numb -> name, lastname, secondname, birthdate

[tab_numb]+f = tab_numb

нет=> Множество ФЗ не меняется

2. f=g num_sil -> sort, needs, type

Проверяем: f |= num_sil -> sort, needs, type

[num_sil]+f = num_sil

нет=> Множество ФЗ не меняется

3. f=g num -> lim_weight_c, hours

Проверяем: f |= num -> lim_weight_c, hours

[num]+f = num

нет=> Множество ФЗ не меняется

4. f=g number_el -> lim_weight_el

Проверяем: f |= number_el -> lim_weight_el

[number_el]+f = number_el

нет=> Множество ФЗ не меняется

5. f=g number_el, data -> occupied

Проверяем: f |= number_el, data -> occupied

Рисунок 3 — Построение неизбыточного покрытия множества ФЗ

[number_el, data]+f = number_el, data, lim_weight_el

нет=> Множество ФЗ не меняется

6. f=g num_el -> num_sil

Проверяем: f |= num_el -> num_sil

[num_el]+f = num_el

нет=> Множество ФЗ не меняется

7. f=g num_part -> nam, needs, sort, place, year, num_trans, num_card

Проверяем: f |= num_part -> nam, needs, sort, place, year, num_trans,

num_card

[num_part]+f = num_part

нет=> Множество ФЗ не меняется

8. f=g num_part, data -> name_groat, from, destin, hours

Проверяем: f |= num_part, data -> name_groat, from, destin, hours

[num_part, data]+f = num_part, data, nam, needs, sort, place, year,

num_trans, num_card нет=> Множество ФЗ не меняется

9. f=g number_s -> name_groat, needs

Проверяем: f |= number_s -> name_groat, needs

[number_s]+f = number_s

нет=> Множество ФЗ не меняется

10. f=g num_card, date_an -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

Проверяем: f |= num_card, date_an -> name_groat, type_groat, year,

place, tab_num_lab, lastname_lab

[num_card, date_an]+f = num_card, date_an

нет=> Множество ФЗ не меняется Рисунок 4 — Построение неизбыточного покрытия множества ФЗ Неизбыточное покрытие:

g:

tab_numb -> name, lastname, secondname, birthdate

num_sil -> sort, needs, type

num -> lim_weight_c, hours

number_el -> lim_weight_el

number_el, data -> occupied

num_el -> num_sil

num_part -> nam, needs, sort, place, year, num_trans, num_card

num_part, data -> name_groat, from, destin, hours

number_s -> name_groat, needs

num_card, date_an -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

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

3.3 Леворедуцированное покрытие множества ФЗ

g: tab_numb -> name, lastname, secondname, birthdate

num_sil -> sort, needs, type

num -> lim_weight_c, hours

number_el -> lim_weight_el

number_el, data -> occupied

num_el -> num_sil

num_part -> nam, needs, sort, place, year, num_trans, num_card

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

Рисунок 6 — Построение леворедуцированного покрытия множества ФЗ

num_part, data -> name_groat, from, destin, hours

number_s -> name_groat, needs

num_card, date_an -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

1. number_el, data -> occupied

1.1. number_el, data -> occupied

Проверяем: g |= data -> occupied

нет=> Множество ФЗ не меняется

1.2. number_el, data -> occupied

Проверяем: g |= number_el -> occupied

нет=> Множество ФЗ не меняется

2. num_part, data -> name_groat, from, destin, hours

2.1. num_part, data -> name_groat, from, destin, hours

Проверяем: g |= data -> name_groat, from, destin, hours

нет=> Множество ФЗ не меняется

2.2. num_part, data -> name_groat, from, destin, hours

Проверяем: g |= num_part -> name_groat, from, destin, hours

нет=> Множество ФЗ не меняется.

3. num_card, date_an -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

3.1. num_card, date_an -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

Проверяем: g |= date_an -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

нет=> Множество ФЗ не меняется.

3.2. num_card, date_an -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

Проверяем: g |= num_card -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab нет=> Множество ФЗ не меняется.

Рисунок 7 — Построение леворедуцированного покрытия множества ФЗ Итог:

g: tab_numb -> name, lastname, secondname, birthdate

num_sil -> sort, needs, type

num -> lim_weight_c, hours

number_el -> lim_weight_el

number_el, data -> occupied

num_el -> num_sil

num_part -> nam, needs, sort, place, year, num_trans, num_card

num_part, data -> name_groat, from, destin, hours

number_s -> name_groat, needs

num_card, date_an -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

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

3.4 Праворедуцированное покрытие множества ФЗ На рисунках 9- 13 представлен ход и результаты построения праворедуцированного покрытия множества функциональных зависимостей базы данных лаборатории хлебной базы.

g: tab_numb -> name, lastname, secondname, birthdate

num_sil -> sort, needs, type

num -> lim_weight_c, hours

number_el -> lim_weight_el

number_el, data -> occupied

num_el -> num_sil

num_part -> nam, needs, sort, place, year, num_trans, num_card

num_part, data -> name_groat, from, destin, hours

Рисунок 9 — Построение праворедуцированного покрытия ФЗ

number_s -> name_groat, needs

num_card, date_an -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

1. tab_numb -> name, lastname, secondname, birthdate

1.1. Если удаляем name

Проверяем: f |= tab_numb -> name

нет=> Множество ФЗ не меняется.

1.2. Если удаляем lastname

Проверяем: f |= tab_numb -> lastname

нет=> Множество ФЗ не меняется.

1.3. Если удаляем secondname

Проверяем: f |= tab_numb -> secondname

нет=> Множество ФЗ не меняется.

1.4. Если удаляем birthdate

Проверяем: f |= tab_numb -> birthdate

нет=> Множество ФЗ не меняется.

2. num_sil -> sort, needs, type

2.1. Если удаляем sort

Проверяем: f |= num_sil -> sort

нет=> Множество ФЗ не меняется.

2.2. Если удаляем needs

Проверяем: f |= num_sil -> needs

нет=> Множество ФЗ не меняется.

2.3. Если удаляем type

Проверяем: f |= num_sil -> type

нет=> Множество ФЗ не меняется.

3. num -> lim_weight_c, hours

3.1. Если удаляем lim_weight_c

Проверяем: f |= num -> lim_weight_c

Рисунок 10 — Построение праворедуцированного покрытия ФЗ нет=> Множество ФЗ не меняется.

3.2. Если удаляем hours

Проверяем: f |= num -> hours

нет=> Множество ФЗ не меняется.

4. number_el -> lim_weight_el

4.1. Если удаляем lim_weight_el

Проверяем: f |= number_el -> lim_weight_el

нет=> Множество ФЗ не меняется.

5. number_el, data -> occupied

5.1. Если удаляем occupied

Проверяем: f |= number_el, data -> occupied

нет=> Множество ФЗ не меняется.

6. Если удаляем -> num_sil

6.1. Если удаляем num_sil

Проверяем: f |= num_el -> num_sil

нет=> Множество ФЗ не меняется.

7. num_part -> nam, needs, sort, place, year, num_trans, num_card

7.1. Если удаляем nam

Проверяем: f |= num_part -> nam

нет=> Множество ФЗ не меняется.

7.2. Если удаляем needs

Проверяем: f |= num_part -> needs

нет=> Множество ФЗ не меняется.

7.3. Если удаляем sort

Проверяем: f |= num_part -> sort

нет=> Множество ФЗ не меняется.

7.4. Если удаляем place

Проверяем: f |= num_part -> place

нет=> Множество ФЗ не меняется.

Рисунок 11 — Построение праворедуцированного покрытия ФЗ

7.5. Если удаляем year

Проверяем: f |= num_part -> year

нет=> Множество ФЗ не меняется.

7.6. Если удаляем num_trans

Проверяем: f |= num_part -> num_trans

нет=> Множество ФЗ не меняется.

7.7. Если удаляем num_card

Проверяем: f |= num_part -> num_card

нет=> Множество ФЗ не меняется.

8. num_part, data -> name_groat, from, destin, hours

8.1. Если удаляем name_groat

Проверяем: f |= num_part, data -> name_groat

нет=> Множество ФЗ не меняется.

8.2. Если удаляем from

Проверяем: f |= num_part, data -> from

нет=> Множество ФЗ не меняется.

8.3. Если удаляем destin

Проверяем: f |= num_part, data -> destin

нет=> Множество ФЗ не меняется.

8.4. Если удаляем hours

Проверяем: f |= num_part, data -> hours

нет=> Множество ФЗ не меняется.

9. number_s -> name_groat, needs

9.1. Если удаляем name_groat

Проверяем: f |= number_s -> name_groat

нет=> Множество ФЗ не меняется.

9.2. Если удаляем needs

Проверяем: f |= number_s -> needs

нет=> Множество ФЗ не меняется.

Рисунок 12 — Построение праворедуцированного покрытия ФЗ

10. num_card, date_an -> name_groat, type_groat, year, place, tab_num_lab, lastname_lab

10.1. Если удаляем name_groat

Проверяем: f |= num_card, date_an -> name_groat

нет=> Множество ФЗ не меняется.

10.2. Если удаляем type_groat

Проверяем: f |= num_card, date_an -> type_groat

нет=> Множество ФЗ не меняется.

10.3. Если удаляем year

Проверяем: f |= num_card, date_an -> year

нет=> Множество ФЗ не меняется.

10.4. Если удаляем place

Проверяем: f |= num_card, date_an -> place

нет=> Множество ФЗ не меняется.

10.5. Если удаляем tab_num_lab

Проверяем: f |= num_card, date_an -> tab_num_lab

нет=> Множество ФЗ не меняется.

Удаление зависимостей вида Х->

Итог: g: tab_numb -> name, lastname, secondname, birthdate

num_sil -> sort, needs, type

num -> lim_weight_c, hours

number_el -> lim_weight_el

number_el, data -> occupied

num_el -> num_sil

num_part -> nam, needs, sort, place, year, num_trans, num_card

num_part, data -> name_groat, from, destin, hours

number_s -> name_groat, needs

num_card, date_an -> name_groat, type_groat, year, place, tab_num_lab, lastname_lab

Рисунок 13 — Праворедуцированное покрытие множества ФЗ

3.5 Минимальное покрытие таблица база данные запрос На рисунках 14- 15 представлен ход и результаты построения минимального покрытия множества функциональных зависимостей базы данных лаборатории хлебной базы.

1. Ef (tab_numb):

tab_numb -> name, lastname, secondname, birthdate

2. Ef (num_sil):

num_sil -> sort, needs, type

3. Ef (num):

num -> lim_weight_c, hours

4. Ef (number_el):

number_el -> lim_weight_el

5. Ef (number_el, data):

number_el, data -> occupied

6. Ef (num_el):

num_el -> num_sil

7. Ef (num_part):

num_part -> nam, needs, sort, place, year, num_trans, num_card

8. Ef (num_part, data):

num_part, data -> name_groat, from, destin, hours

9. Ef (number_s):

number_s -> name_groat, needs

10. Ef (num_card, date_an):

num_card, date_an -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

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

g: tab_numb -> name, lastname, secondname, birthdate

Рисунок 14 — Построение минимального покрытия множества ФЗ

num_sil -> sort, needs, type

num -> lim_weight_c, hours

number_el -> lim_weight_el

number_el, data -> occupied

num_el -> num_sil

num_part -> nam, needs, sort, place, year, num_trans, num_card

num_part, data -> name_groat, from, destin, hours

number_s -> name_groat, needs

num_card, date_an -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

Рисунок 15 — Минимальное покрытие множества ФЗ

3.6

3.6 Редуцированное минимальное кольцевое покрытие На рисунках 16- 24 представлен ход и результаты построения минимального редуцированного кольцевого покрытия множества функциональных зависимостей базы данных лаборатории хлебной базы.

Минимальное кольцевое покрытие

(tab_numb;) -> name, lastname, secondname, birthdate

(num_sil;) -> sort, needs, type

(num;) -> lim_weight_c, hours

(number_el;) -> lim_weight_el

(number_el, data;) -> occupied

(num_el;) -> num_sil

(num_part;) -> nam, needs, sort, place, year, num_trans, num_card

(num_part, data;) -> name_groat, from, destin, hours

(number_s;) -> name_groat, needs

(num_card, date_an;) -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab.

Рисунок 16 — Построение минимального кольцевого покрытия множества ФЗ Получение кольцевого минимального редуцированого покрытия Естественное характеристическое множество для кольцевого покрытия.

f (C): tab_numb -> name, lastname, secondname, birthdate

num_sil -> sort, needs, type

num -> lim_weight_c, hours

number_el -> lim_weight_el

number_el, data -> occupied

num_el -> num_sil

num_part -> nam, needs, sort, place, year, num_trans, num_card

num_part, data -> name_groat, from, destin, hours

number_s -> name_groat, needs

num_card, date_an -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

Левая редукция или перенос в правую часть

1. Проверка CF-зависимости:

(tab_numb;) -> name, lastname, secondname, birthdate

1.1. Сформированная CF-зависимость:

() -> name, lastname, secondname, birthdate, tab_numb

Характеристические множества не эквивалентны

2. Проверка CF-зависимости:

(num_sil;) -> sort, needs, type

2.1. Сформированная CFзависимость:

() -> sort, needs, type, num_sil

Характеристические множества не эквивалентны

3. Проверка CF-зависимости:

(num;) -> lim_weight_c, hours

3.1. Сформированная CFзависимость:

() -> lim_weight_c, hours, num

Рисунок 17 — Построение редуцированного минимального кольцевого покрытия множества ФЗ Характеристические множества не эквивалентны

4. Проверка CF-зависимости:

(number_el;) -> lim_weight_el

4.1. Сформированная CFзависимость:

() -> lim_weight_el, number_el

Характеристические множества не эквивалентны

5. Проверка CF-зависимости:

(number_el, data;) -> occupied

5.1. Сформированная CFзависимость:

(data;) -> occupied, number_el

Характеристические множества не эквивалентны

5.2. Сформированная CFзависимость:

(number_el;) -> occupied, data

Характеристические множества не эквивалентны

6. Проверка CF-зависимости:

(num_el;) -> num_sil

6.1. Сформированная CFзависимость:

() -> num_sil, num_el

Характеристические множества не эквивалентны

7. Проверка CF-зависимости:

(num_part;) -> nam, needs, sort, place, year, num_trans, num_card

7.1. Сформированная CFзависимость:

() -> nam, needs, sort, place, year, num_trans, num_card, num_part

Характеристические множества не эквивалентны

8. Проверка CF-зависимости:

(num_part, data;) -> name_groat, from, destin, hours

8.1. Сформированная CFзависимость:

(data;) -> name_groat, from, destin, hours, num_part

Рисунок 18 — Построение редуцированного минимального кольцевого покрытия множества ФЗ

Характеристические множества не эквивалентны

8.2. Сформированная CFзависимость:

(num_part;) -> name_groat, from, destin, hours, data

Характеристические множества не эквивалентны

9. Проверка CF-зависимости:

(number_s;) -> name_groat, needs

9.1. Сформированная CFзависимость:

() -> name_groat, needs, number_s

Характеристические множества не эквивалентны

10. Проверка CF-зависимости:

(num_card, date_an;) -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

10.1. Сформированная CFзависимость:

(date_an;) -> name_groat, type_groat, year, place, tab_num_lab,

lastname_lab, num_card

Характеристические множества не эквивалентны

10.2. Сформированная CFзависимость:

(num_card;) -> name_groat, type_groat, year, place, tab_num_lab,

lastname_lab, date_an

Характеристические множества не эквивалентны Проверка покрытия на праворедуцированность

1. Проверка CF-зависимости:

(tab_numb;) -> name, lastname, secondname, birthdate

2.1. Сформированная CFзависимость:

(tab_numb;) -> lastname, secondname, birthdate

Характеристические множества не эквивалентны

2.2. Сформированная CFзависимость:

(tab_numb;) -> name, secondname, birthdate

Рисунок 19 — Построение редуцированного минимального кольцевого покрытия множества ФЗ

Характеристические множества не эквивалентны

2.3. Сформированная CFзависимость:

(tab_numb;) -> name, lastname, birthdate

Характеристические множества не эквивалентны

2.4. Сформированная CFзависимость:

(tab_numb;) -> name, lastname, secondname

Характеристические множества не эквивалентны

2. Проверка CF-зависимости:

(num_sil;) -> sort, needs, type

3.1. Сформированная CFзависимость:

(num_sil;) -> needs, type

Характеристические множества не эквивалентны

3.2. Сформированная CFзависимость:

(num_sil;) -> sort, type

Характеристические множества не эквивалентны

3.3. Сформированная CFзависимость:

(num_sil;) -> sort, needs

Характеристические множества не эквивалентны

3. Проверка CF-зависимости:

(num;) -> lim_weight_c, hours

4.1. Сформированная CFзависимость:

(num;) -> hours

Характеристические множества не эквивалентны

4.2. Проверка CF-зависимости:

(num;) -> lim_weight_c

Характеристические множества не эквивалентны

4. Проверка CF-зависимости:

(number_el;) -> lim_weight_el

5.1. Сформированная CFзависимость:

Рисунок 20 — Построение редуцированного минимального кольцевого покрытия множества ФЗ

(number_el;)

Характеристические множества не эквивалентны

5. Проверка CF-зависимости:

(number_el, data;) -> occupied

6.1. Сформированная CFзависимость:

(number_el, data;)

Характеристические множества не эквивалентны

6. Проверка CF-зависимости:

(num_el;) -> num_sil

7.1. Сформированная CFзависимость:

(num_el;)

Характеристические множества не эквивалентны

7. Проверка CFзависимости:

(num_part;) -> nam, needs, sort, place, year, num_trans, num_card

8.1. Сформированная CFзависимость:

(num_part;) -> needs, sort, place, year, num_trans, num_card

Характеристические множества не эквивалентны

8.2. Сформированная CFзависимость:

(num_part;) -> nam, sort, place, year, num_trans, num_card

Характеристические множества не эквивалентны

8.3. Сформированная CFзависимость:

(num_part;) -> nam, needs, place, year, num_trans, num_card

Характеристические множества не эквивалентны

8.4. Сформированная CFзависимость:

(num_part;) -> nam, needs, sort, year, num_trans, num_card

Характеристические множества не эквивалентны

8.5. Сформированная CFзависимость:

(num_part;) -> nam, needs, sort, place, num_trans, num_card

Характеристические множества не эквивалентны Рисунок 21 — Построение редуцированного минимального кольцевого покрытия множества ФЗ

8.6. Сформированная CFзависимость:

(num_part;) -> nam, needs, sort, place, year, num_card

Характеристические множества не эквивалентны

8.7. Сформированная CFзависимость:

(num_part;) -> nam, needs, sort, place, year, num_trans

Характеристические множества не эквивалентны

8. Проверка CFзависимости:

(num_part, data;) -> name_groat, from, destin, hours

9.1. Сформированная CFзависимость:

(num_part, data;) -> from, destin, hours

Характеристические множества не эквивалентны

9.2. Сформированная CFзависимость:

(num_part, data;) -> name_groat, destin, hours

Характеристические множества не эквивалентны

9.3. Сформированная CFзависимость:

(num_part, data;) -> name_groat, from, hours

Характеристические множества не эквивалентны

9.4. Сформированная CFзависимость:

(num_part, data;) -> name_groat, from, destin

Характеристические множества не эквивалентны

9. Проверка CFзависимости:

(number_s;) -> name_groat, needs

10.1. Сформированная CFзависимость:

(number_s;) -> needs

Характеристические множества не эквивалентны

10.2. Сформированная CFзависимость:

(number_s;) -> name_groat

Характеристические множества не эквивалентны

10. Проверка CFзависимости:

Рисунок 22 — Построение редуцированного минимального кольцевого покрытия множества ФЗ

(num_card, date_an;) -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

11.1. Сформированная CFзависимость:

(num_card, date_an;) -> type_groat, year, place, tab_num_lab,

lastname_lab

Характеристические множества не эквивалентны

11.2. Сформированная CFзависимость:

(num_card, date_an;) -> name_groat, year, place, tab_num_lab,

lastname_lab

Характеристические множества не эквивалентны

11.3. Сформированная CFзависимость:

(num_card, date_an;) -> name_groat, type_groat, place,

tab_num_lab, lastname_lab

Характеристические множества не эквивалентны

11.4. Сформированная CFзависимость:

(num_card, date_an;) -> name_groat, type_groat, year,

tab_num_lab, lastname_lab

Характеристические множества не эквивалентны

11.5. Сформированная CFзависимость:

(num_card, date_an;) -> name_groat, type_groat, year, place,

lastname_lab

Характеристические множества не эквивалентны

11.6. Сформированная CFзависимость:

(num_card, date_an;) -> name_groat, type_groat, year, place,

tab_num_lab

Характеристические множества не эквивалентны Рисунок 23 — Построение редуцированного минимального кольцевого покрытия множества ФЗ Минимальное редуцированное кольцевое покрытие

C (tab_numb;) -> name, lastname, secondname, birthdate

(num_sil;) -> sort, needs, type

(num;) -> lim_weight_c, hours

(number_el;) -> lim_weight_el

(number_el, data;) -> occupied

(num_el;) -> num_sil

(num_part;) -> nam, needs, sort, place, year, num_trans, num_card

(num_part, data;) -> name_groat, from, destin, hours

(number_s;) -> name_groat, needs

(num_card, date_an;) -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

Естественное характеристическое множество

f (C):

tab_numb -> name, lastname, secondname, birthdate

num_sil -> sort, needs, type

num -> lim_weight_c, hours

number_el -> lim_weight_el

number_el, data -> occupied

num_el -> num_sil

num_part -> nam, needs, sort, place, year, num_trans, num_card

num_part, data -> name_groat, from, destin, hours

number_s -> name_groat, needs

num_card, date_an -> name_groat, type_groat, year, place,

tab_num_lab, lastname_lab

Рисунок 24 — Редуцированное минимальное кольцевое покрытие множества ФЗ Таким образом, структура базы данных лаборатории хлебной базы разрабатывалась методом синтеза с помощью построения неизбыточного покрытия, праворедуцированного покрытия, леворедуцированного покрытия, минимального покрытия и редуцированного минимального кольцевого покрытия множества ФЗ. В ходе построения покрытий было установлено, что множества функциональных зависимостей не изменятся, значит, структура базы данных спроектирована достаточно оптимально. На рисунке 25 показаны результирующие таблицы базы данных и их ключи.

R0 = (tab_numb, name, lastname, secondname, birthdate) K0 ={tab_numb}

R1 = (num_sil, sort, needs, type) K1 = { num_sil }

R2 = (num, lim_weight_c, hours) K2 = { num }

R3 = (number_el, lim_weight_el) K3 = { number_el }

R4 = (number_el, data, occupied) K4 = { number_el, data }

R5 = (num_el, num_sil) K5 = { num_el }

R6 = (num_part, nam, needs, sort, place, year, num_trans, num_card)

K6 = { num_part }

R7 = (num_part, data, name_groat, from, destin, hours) K7 = { num_part,

data }

R8 = (number_s, name_groat, needs) K8 = { number_s }

R9 = (num_card, date_an, name_groat, type_groat, year, place,

tab_num_lab, lastname_lab) K9 = { num_card, date_an }

Рисунок 25 — Структура базы данных лаборатории хлебной базы, полученная методом синтеза

4. Оптимизация запросов к базе данных

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

— groats (nam, needs, sort, year, place, num_part);

— laborants (tab_num, lastname, name, secondname, birth_date);

— analysis_card (num_card, date_an, lastname_lab, tab_num_lab, num_part_gr, name_groat_an, type_groat_an, place, year);

— transport (num, hours, lim_weight_c);

— grain_elevator (number_el, lim_weight_el);

— current_elev (data, occupied, number_el);

— silo (number_s, lim_weight_s, weight_s, name_groat, type_groat, num_elev, num_part);

— receivs (num_part, num_trans, from, hourrs, name_groat, dat, num_sil);

— dispatch (num_part, num_trans, destin, hourrs, name_groat, data).

Общее число записей в каждой из таблиц:

— T (Groats)=100.

— T (Laborants)=10.

— T (Analysis_card)=100.

— T (Transport)=40.

— T (Grain_elevator)=4.

— T (Current_elev)=50.

— T (Silo)=100.

— T (Receivs)=100.

— T (Dispatch)=100.

a.

4.2 Многотабличный запрос на выборку по условию Найти все партии крупы, полученные начиная с 30−08−10, анализные карточки для которых заполнял лаборант с табельным номером 10.

4.2.1 Выражение для запроса с использованием реляционной алгебры Выражение для запроса с использованием реляционной алгебры показано на рисунке 26.

pr (Groats. nam, needs, sort, year, place, num_part) sel (receivs.dat >{^2010;08−30}

& analysis_card.tab_num_lab=10) (groats/

receivs, analysis. card)

Рисунок 26 — Выражение для запроса с использованием реляционной алгебры

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

Рисунок 27 — Исходное операционное дерево запроса

4.2.3 Оптимизированное операционное дерево для запроса Оптимизированное операционное дерево для многотабличного запроса на выборку по условию одной строки представлено на рисунке 28.

Рисунок 28 — Оптимизированное операционное дерево запроса

4.2.4 Исходный запрос на языке SQL

Текст исходного запроса на языке SQL показан на рисунке 29.

Рисунок 29 — Исходный запрос на языке SQL

4.2.5 Оптимизированный запрос на языке SQL

Текст оптимизированного запроса на языке SQL показан на рисунке 30.

Рисунок 30 — Оптимизированный запрос на языке SQL

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

Число записей в каждом столбце таблицы analysis_card:

— V (num_part_gr, analysis_card)=100.

— V (name_groat_an, analysis_card)=50.

— V (type_groat, analysis_card)=20.

— V (place, analysis_card)=100.

— V (year, analysis_card)=20.

— V (tab_num_lab, analysis_card)=50.

Длина одной записи в поле каждого из перечисленных типов:

— L (num_part_gr)=4 байтa.

— L (name_groat_an)=30 байт.

— L (type_groat)=30 байт.

— L (place)=40 байт.

— L (year)=4 байтa.

— L (tab_num_lab)=4 байта.

— L (lastname_lab)=40 байт.

— L (date_an)=8 байт.

— L (num_card)=4 байт.

Расчет сложности подзапроса q21отображен на рисунке 31.

Рисунок 31- Сложность подзапроса q21

Для подзапроса q22 выпишем статистику по таблицам и произведем расчеты. Число записей в каждом столбце, необходимом для запроса, из таблицы reseivs:

— V (num_part, reseivs)=100.

— V (name_groat, reseivs)=50.

— V (dat, reseivs)=50.

— V (num_trans, reseivs)=40.

— V (from, reseivs)=100.

— V (hours, reseivs)=2.

— V (num_sil, reseivs)=100.

Длина одной записи в поле каждого из перечисленных типов:

— L (num_part)=4 байтa.

— L (name_groat)=30 байт.

— L (dat) =8 байт.

— L (num_trans) =4 байта.

— L (from) =40 байт.

— L (hours) =4 байта.

— L (num_sil) =4 байта.

Расчет сложности подзапроса показан на рисунке 32.

Рисунок 32 — Сложность подзапроса q22

Для подзапроса q23 статистика по таблице не меняется, поскольку осуществляется операция проекции над результатом подзапроса q22.

Рисунок 33 — Сложность подзапроса q23

Для подзапроса q24 статистика по таблице не меняется, поскольку осуществляется операция проекции над результатом подзапроса q21.

Рисунок 34 — Сложность подзапроса q24

Подзапрос q25 — естественное слияние результатов подзапросов q23 и q24. Расчет сложности подзапроса q25 показан на рисунке 35.

Рисунок 35 — Сложность подзапроса q25

Подзапрос q26 не меняет статистику подзапроса q25, поскольку это операция проекции. Расчет сложности подзапроса q26 показан на рисунке 36.

Рисунок 36 — Сложность подзапроса q26

Подзапрос q27 не меняет статистику по таблице groats, поскольку это операция проекции.

Число записей в каждом столбце из таблицы groats:

— V (num_part, groats)=100;

— V (name, groats)=50;

— V (sort, groats)=15;

— V (needs, groats)=20;

— V (place, groats)=100;

— V (year, groats)=20.

Длина одной записи в поле каждого из перечисленных типов:

— L (num_part)=4 байтa;

— L (nam)=30 байт;

— L (needs)=30 байт;

— L (sort)= 4 байтa;

— L (place)=40 байт;

— L (year)=4 байтa.

Расчет сложности подзапроса q27 показан на рисунке 37.

Рисунок 37 — Сложность подзапроса q27

Подзапрос q28 — естественное слияние результатов подзапросов q27 и q26. Расчет сложности подзапроса q28 показан на рисунке 38.

Рисунок 38 — Сложность подзапроса q28

Сложность всего оптимизированного запроса рассчитывается как сумма сложностей промежуточных подзапросов. Таким образом, она составляет: Vol (q2)=448+3234+1122+224+102+102+6400+102=11 734 байта.

4.3

4.3 Многотабличный запрос на выборку по условию Найти всех лаборантов, работавших с анализами 17 и 10 партий крупы.

4.3.1 Выражение для запроса с использованием реляционной алгебры

pr (laborants.tab_num, lastname, name, secondname, birthdate) sel (analylis_card.num_part = 10 V analylis_card.num_part = 17) (laborants/

analysis_card)

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

Рисунок 4. Исходное операционное дерево запроса

4.3.3

4.3.3 Оптимизированное операционное дерево для запроса Оптимизированное операционное дерево для многотабличного запроса на выборку по условию одной строки представлено на рисунке 5.

Рисунок 5. Оптимизированное операционное дерево для запроса

4.3.4 Исходный запрос на языке SQL

SELECT * FROM laborants INNER JOIN analysis_card ON

analysis_card.tab_num_lab=laborants.tab_num ;

WHERE (analysis_card.num_part_gr = 10 OR

analysis_card.num_part_gr = 17);

INTO Cursor q

Browse

4.3.5

4.3.5 Оптимизированный запрос на языке SQL

SELECT * FROM laborants

INTO Cursor q1

Browse

SELECT * FROM analysis_card

WHERE (analysis_card.num_part_gr = 10 OR

analysis_card.num_part_gr = 17);

INTO Cursor q2

Browse

SELECT * FROM q1 INNER JOIN q2 ON

q2.tab_num_lab=q1.tab_num;

INTO Cursor q

Browse

4.3.6 Расчет сложности запроса Для подзапроса q1

Число записей в каждом столбце таблицы laborants:

V (tab_num, laborants)=10;

V (lastname, laborants)=10;

V (name, laborants)=10;

V (secondname, laborants)=10;

V (birth_date, laborants)=10;

Длина одной записи в поле каждого из перечисленных типов:

L (tab_num)=4 байтa;

L (lastname)=40 байт;

L (name)=40 байт;

L (secondname)=40 байт;

L (birth_date)=8 байт.

Сложность запроса:

T (q1)=T (laborants)=10.

Статистика по запросу:

Vol (q1)=T (q1)*L (q1);

L (q1) = L (tab_num) + L (lastname) + L (name) + L (secondname) + +L (birth_date) = 4+40+40+40+8 = 132 байтa.

Vol (q1)=10*132=1320 байт.

Для подзапроса q2

Число записей в каждом столбце, необходимом для запроса, из таблицы analysis_card:

V (num_card, analysis_card)=100;

V (date_an, analysis_card)=40;

V (lastname_lab, analysis_card)=10;

V (tab_num_lab, analysis_card)=10;

V (num_part_gr, analysis_card)=100;

V (name_groat_an, analysis_card)=20;

V (num_part, analysis_card)=100.

Длина одной записи в поле каждого из перечисленных типов:

L (num_part)=4 байтa;

L (name_groat)=30 байт;

L (tab_num_lab)= 4 байтa.

Сложность запроса:

T (q2−1)=T (analysis_card)/V (analysis_card, num_part)=100/100=1.

T (q2−2)= T (analysis_card)/V (analysis_card, num_part)=100/100=1.