252

Організація самостійної роботи студентів при виконанні контрольних робіт та індивідуальних завдань по курсу Організація баз даних

Другое

Информатика, кибернетика и программирование

У методичному посібнику надані структура завдання до контрольної та індивідуальної робіт та приклад виконання завдання для придбання теоретичних та практичних навичок побудови баз даних в системі керування базами даних Visual FoxPro 6.

Украинкский

2012-11-14

515 KB

17 чел.

Міністерство освіти і науки України

Донецький національний технічний університет

МЕТОДИЧНІ  ВКАЗІВКИ

щодо організації самостійної роботи студентів при виконанні контрольних робіт та індивідуальних завдань по курсу «Організація баз даних»

Розглянуто на

засіданні кафедри

протокол № 1 від 31.08.10

Затверджено на засіданні

навчально-видавничної ради ДонНТУ

протокол №     від

Донецьк, ДонНТУ, 2010


Методичні вказівки щодо організації самостійної роботи студентів при виконанні контрольних робіт та індивідуальних завдань по курсу «Організація баз даних» призначені для студентів спеціальностей «Системне програмування» та «Комп'ютерні системи та мережі» денної, заочної та очно-заочної форм навчання - Донецьк, ДонНТУ, 2010.

У методичному посібнику надані структура завдання до контрольної та індивідуальної робіт та приклад виконання завдання для придбання теоретичних та практичних навичок побудови баз даних в системі керування базами даних Visual FoxPro 6. Наведений приклад демонструє типові рішення завдань розробки баз даних (нормалізація таблиць, побудова діаграми «таблиця-зв'язок», розробка SQL запитів, а також макетів звіту для друку).

Методичні вказівки забезпечені також індивідуальними завданнями для проектування та довідковим матеріалом з використання команд мови SQL.

Укладачі: Л. І. Дорожко

Т.В. Завадська

Рецензенти:  доц. Теплінський О.О.

 проф. Аверін Г.В.

Відповідальний за випуск: зав. каф. КІ, проф., д.т.н. В.А. Святний


Вступление

Существует очень простое понятие базы данных как большого по объему хранилища, в которое организация помещает все используемые ею данные и из которого различные пользователи могут их получать, используя различные приложения. Такая единая база данных представляется идеальным вариантом, хотя на практике это решение по различным причинам труднодостижимо. Чаще всего под базой данных понимают любой набор хранящихся в компьютере взаимосвязанных данных.

В основу проектирования БД должны быть положены представления конечных пользователей конкретной организации - концептуальные требования к системе. Именно конечный пользователь в своей работе принимает решения с учетом получаемой в результате доступа к базе данных информации. От оперативности и качества этой информации будет зависеть эффективность работы организации. Данные, помещаемые в базу данных, также предоставляет конечный пользователь.

Основная цель проектирования БД – это сокращение избыточности хранимых данных, а следовательно, экономия объема используемой памяти, уменьшение затрат на многократные операции обновления избыточных копий и устранение возможности возникновения противоречий из-за хранения в разных местах сведений об одном и том же объекте. Так называемый, "чистый" проект БД ("Каждый факт в одном месте"), который является основной целью проектирования, можно создать, используя методологию нормализации отношений. Необходимо помнить, что "плохой" проект БД не удастся исправить никаким программированием.

Исходя из двух главных предназначений БД: сохранность достоверных данных и оперативного получения требуемой информации, были определены цели самостоятельной работы студентов при изучении дисциплины и выполнении индивидуальных заданий и контрольных работ. Во-первых, это глубокая теоретическая проработка проекта БД, при которой необходимо проявить теоретические знания при описании модели данных и практические умения при проведении нормализации таблиц. Во-вторых, изучение и разработка эффективных методов отбора данных в SQL запросах, а так же способов формирования печатных отчетов по отобранным данным.


1. Структура задания к индивидуальной и контрольной работе

Задание к индивидуальной и контрольной работе состоит из двух частей.

В первой части задается список атрибутов объекта, информацию о котором необходимо хранить в базе данных. В соответствии с заданным списком атрибутов составляется таблица, в которую необходимо внести 20-40 строк информации. Количество строк зависит от условия задачи. Внесенная в таблицу информация должна наиболее реально отображать суть описываемого объекта, процесса или явления, быть максимально приближенна реальным ситуациям в повседневной жизни. Данная таблица будет являться контрольным примером данных, хранимых в базе.

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

2. Этапы выполнения работы

Задание к контрольной или индивидуальной работе выбирается из списка вариантов, приведенного в приложении 1. Номер варианта определяется преподавателем. Выполнение задания состоит из 8-и этапов.

1. Составить исходную таблицу - универсальное отношение, в соответствии с описанием данных в задании и внести в нее данные, которые будут служить контрольным примером. Количество строк должно быть не менее 20.

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

3. Провести нормализацию таблиц. Переходы от одной нормальной формы к другой должны быть в строгой последовательности, в соответствии с теорией нормализации. Каждый этап нормализации необходимо аргументировать, указывая на несоответствия таблицы той или иной нормальной форме и пути их устранения.

4. Построить диаграмму «таблица-связь», с указанием наименований, типов и размеров полей; первичных и внешних ключей, связей между таблицами базы данных, ограничений для обеспечения ссылочной целостности и ограничений, определяемых пользователем.

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

6. Привести вид выходного курсора, в котором отразить данные, выбранные из таблиц. Данных в курсоре должно быть достаточно для построения отчета. В макете отчета разрешается применять «текстовые метки» (неизменяемый текст) только в тех случаях, когда эти данные в базе отсутствуют.

7. Привести вид источника для выборки данных из таблиц. В источнике указать, по каким полям таблицы связаны. Разработать команду SELECT для отбора необходимых данных, приведенных в курсоре.

8. Разработать макет отчета для печати документов в соответствии с заданием. При необходимости получать итоговые значения, ввести переменные. Задать таблицу описания функционирования объявленных переменных.

3. Пример выполнения задания

В кафе ведется учет проданных блюд и расхода продуктов на их изготовление,  поставщиков продуктов по следующим позициям: блюдо, вид блюда, количество реализованных порций, цена порции, дата реализации, продукт – составляющий блюдо, единица измерения продукта, расход продукта на одну порцию, поставщик продукта и его место расположения.

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

Сформировать отчет о продуктах, израсходованных на приготовление блюд.

3.1. Составление контрольного примера (универсального отношения)

В соответствии с заданием составляется таблица 3.1, в которой все позиции, по которым хранится информация по работе кафе, выделены в отдельный столбик (поле или атрибут). В таблице выделяется первая строка в описании блюда с указанием названия и вида блюда, стоимости одной порции, даты и количества проданных порций. Далее в нескольких строках приводится описание состава продуктов для изготовления данного блюда.

В таблице учтены следующие условия:

- состав блюда является постоянным во все дни продаж;

- одно блюдо может продаваться каждый день или с перерывами;

- разные блюда могут иметь один и тот же вид;

- разные блюда могут содержать в своем составе одни и те же продукты;

- продукты приобретаются у одних и тех же поставщиков;

- разные продукты могут измеряться одной единицей измерения.

Составленная таблица должна соответствовать правилам построения реляционных таблиц.

1. Каждая таблица состоит из однотипных строк и имеет уникальное имя.

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


Кафе        Таблица 3.1

Блюдо

Вид блюда

Количество порций

Цена порции

Дата продажи

Продукт

Единица измерения

Расход

Поставщик

Расположение поставщика

Котлета

Второе

15

8,50

01.03.10

Мясо

Кг

0,1

Ферма

Село

Лук

Кг

0,01

Поле

Село

Соль

Г

5

Склад

Город

Яйцо

Шт

0,2

Птичник

Село

Суп

Первое

23

4,75

01.03.10

Мясо

Кг

0,05

Ферма

Село

Картофель

Кг

0,1

Поле

Село

Лук

Кг

0,02

Поле

Село

Зелень

Г

20

Поле

Село

Соль

Г

10

Склад

Город

Компот

Десерт

30

1,50

01.03.10

Фрукты

Г

30

Сад

Село

Сахар

Г

10

Склад

Город

Шницель

Второе

17

10,00

02.03.10

Мясо

Кг

0,15

Ферма

Село

Лук

Кг

0,01

Поле

Село

Соль

Г

5

Склад

Город

Яйцо

Шт

0,2

Птичник

Село

Суп

Первое

26

4,75

02.03.10

Мясо

Кг

0,05

Ферма

Село

Картофель

Кг

0,1

Поле

Село

Лук

Кг

0,02

Поле

Село

Зелень

Г

20

Поле

Село

Соль

Г

10

Склад

Город

Компот

Десерт

28

1,50

02.03.10

Фрукты

Г

30

Сад

Село

Сахар

Г

10

Склад

Город


Описание адреса необходимо разделить на 6 колонок: почтовый индекс, страна, город, улица, дом, квартира.

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

4. Столбцам таблицы присваиваются уникальные имена, и в каждом из них размещаются однородные значения данных (даты, фамилии, целые числа или денежные суммы).

5. Полное информационное содержание базы данных представляется в виде явных значений данных, и такой метод представления является единственным. В частности, не существует каких-либо специальных "связей" или указателей, соединяющих одну таблицу с другой.

6. При выполнении операций с таблицей ее строки и столбцы можно обрабатывать в любом порядке безотносительно к их информационному содержанию. Этому способствует наличие имен таблиц и их столбцов, а также возможность выделения любой их строки или любого набора строк с указанными признаками.

В таблице 3.1 правило 3 не соблюдается. Описание продуктов, входящих в состав супа занимает 5 строк. Только первые строки, в которых указана дата продажи, отличаются друг от друга. В остальных четырех строках отличий нет. Для приведения таблицы в соответствие с определением реляционной теории необходимо ввести в каждую строку дату продажи.

Только это изменение может не привести к желаемому результату. Нельзя исключить возможность того, что в один день будут проданы разные блюда с одинаковым содержанием одного и того же продукта. Кроме того, если не указать в каждой строке наименования блюда, то при анализе продукта из случайной строки будет невозможно определить, в состав, какого блюда он входит. Поэтому, таблицу 3.1 необходимо преобразовать к универсальному отношению (таблица 3.2). Эта таблица удовлетворяет всем правилам построения реляционных таблиц.

3.2. Определение первичного ключа и функциональных зависимостей в таблице

Ключ или возможный ключ – это минимальный набор атрибутов, по значениям которых можно однозначно найти требуемую строку. Минимальность означает, что исключение из набора любого атрибута не позволяет идентифицировать строку по оставшимся. Каждая таблица обладает хотя бы одним возможным ключом. Один из них принимается за первичный ключ. При выборе первичного ключа следует отдавать предпочтение несоставным ключам (состоящим из одного атрибута) или ключам, составленным из минимального числа атрибутов. Нецелесообразно также использовать ключи с длинными текстовыми значениями (предпочтительнее использовать целочисленные атрибуты).


Универсальное отношение      Таблица 3.2

Блюдо

Вид блюда

Количество порций

Цена порции

Дата продажи

Продукт

Единица измерения

Расход

Поставщик

Расположение поставщика

Котлета

Второе

15

8,50

01.03.10

Мясо

Кг

0,1

Ферма

Село

Котлета

Второе

15

8,50

01.03.10

Лук

Кг

0,01

Поле

Село

Котлета

Второе

15

8,50

01.03.10

Соль

Г

5

Склад

Город

Котлета

Второе

15

8,50

01.03.10

Яйцо

Шт

0,2

Птичник

Село

Суп

Первое

23

4,75

01.03.10

Мясо

Кг

0,05

Ферма

Село

Суп

Первое

23

4,75

01.03.10

Картофель

Кг

0,1

Поле

Село

Суп

Первое

23

4,75

01.03.10

Лук

Кг

0,02

Поле

Село

Суп

Первое

23

4,75

01.03.10

Зелень

Г

20

Поле

Село

Суп

Первое

23

4,75

01.03.10

Соль

Г

10

Склад

Город

Компот

Десерт

30

1,50

01.03.10

Фрукты

Г

30

Сад

Село

Компот

Десерт

30

1,50

01.03.10

Сахар

Г

10

Склад

Город

Шницель

Второе

17

10,00

02.03.10

Мясо

Кг

0,15

Ферма

Село

Шницель

Второе

17

10,00

02.03.10

Лук

Кг

0,01

Поле

Село

Шницель

Второе

17

10,00

02.03.10

Соль

Г

5

Склад

Город

Шницель

Второе

17

10,00

02.03.10

Яйцо

Шт

0,2

Птичник

Село

Суп

Первое

26

4,75

02.03.10

Мясо

Кг

0,05

Ферма

Село

Суп

Первое

26

4,75

02.03.10

Картофель

Кг

0,1

Поле

Село

Суп

Первое

26

4,75

02.03.10

Лук

Кг

0,02

Поле

Село

Суп

Первое

26

4,75

02.03.10

Зелень

Г

20

Поле

Село

Суп

Первое

26

4,75

02.03.10

Соль

Г

10

Склад

Город

Компот

Десерт

28

1,50

02.03.10

Фрукты

Г

30

Сад

Село

Компот

Десерт

28

1,50

02.03.10

Сахар

Г

10

Склад

Город


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

В универсальном отношении хранится информация о продажах блюд в разные дни. Поэтому, естественным будет назначить первичным ключом  составной ключ, состоящий из двух полей: «Блюдо» и «Дата продажи». Но этого не достаточно, так как в таблице хранится информация и о составе блюда. В указанных двух полях будет столько дубликатов, из скольких продуктов состоит данное блюдо. Поэтому в составной ключ добавляется поле «Продукт». В результате проверки выясняется, что этого достаточно: дубликатов в составном первичном ключе из трех полей нет.

Выбор первичного ключа необходимо проводить продумано и тщательно. Неправильное определение ключа приведет получению «плохого» проекта базы данных.

Первичным ключом в таблице 3.1 назначается составное поле из трех атрибутов: «Блюдо», «Дата продажи» и «Продукт». В таблице поля первичного ключа выделяются «полужирным» шрифтом с подчеркиванием.

В реляционной теории определены два вида зависимостей: функциональные и многозначные.

Функциональная зависимость. Поле В таблицы функционально зависит от поля А той же таблицы в том и только в том случае, когда в любой заданный момент времени для каждого из различных значений поля А обязательно существует только одно из различных значений поля В. Допускается, что поля А и В могут быть составными.

Полная функциональная зависимость. Поле В находится в полной функциональной зависимости от составного поля А, если оно функционально зависит от А и не зависит функционально от любого подмножества поля А.

Многозначная зависимость. Поле А многозначно определяет поле В той же таблицы, если для каждого значения поля А существует хорошо определенное множество соответствующих значений В.

Анализ таблицы 3.2 позволяет установить следующие функциональные зависимости:

- Поля «Вид блюда» и «Цена порции» функционально зависят от поля «Блюдо». Для блюда «котлета» определен его вид – «второе» и цена «8,50», «суп» - «первое» и «4,75» и т.д. Нельзя сказать, что поле «Блюдо» функционально зависит от поля «Вид блюда». Для вида блюда «второе» в примере определены два блюда «котлета» и «шницель». Такая проверка правильности определения функциональных зависимостей является достаточной и должна проводиться для всех определяемых зависимостей.

- Поле «Количество порций» функционально зависит от составного поля из двух атрибутов «Блюдо» и «Дата продажи».

- Поля «Единица измерения» и «Поставщик» функционально зависят от поля «Продукт».

- Поле «Расход» функционально зависит от составного поля из двух атрибутов «Блюдо» и «Продукт».

- Поле «Расположение поставщика» функционально зависит от поля «Поставщик».

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

3.3. Нормализация таблиц

При использовании универсального отношения возникает несколько проблем:

1. Избыточность. Данные практически всех столбцов многократно повторяются.

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

3. Аномалии включения. В БД не может быть записан новый поставщик «Рыбхоз», если поставляемый им продукт «Карп живой» не используется ни в одном блюде.

4. Аномалии удаления. Обратная проблема возникает при необходимости удаления всех продуктов, поставляемых данным поставщиком или всех блюд, использующих эти продукты. При таких удалениях будут утрачены сведения о таком поставщике.

Эти проблемы решаются при нормализации таблиц.

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

В реляционной теории определены пять нормальных форм таблицы. Процесс нормализации заключается в последовательном переходе от N-й нормальной формы таблицы к N+1-й. Каждая нормальная форма является более ограниченной, но и более желательной, чем предшествующая. Это связано с тем, что "(N+1)-я нормальная форма" не обладает некоторыми непривлекательными особенностями, свойственным "N-й нормальной форме". Общий смысл дополнительного условия, налагаемого на (N+1)-ю нормальную форму по отношению к N-й нормальной форме, состоит в исключении этих непривлекательных особенностей.

3.3.1 Первая нормальная форма

Таблица находится в первой нормальной форме (1НФ) тогда и только тогда, когда ни одна из ее строк не содержит в любом своем поле более одного значения и ни одно из ее ключевых полей не пусто.

Все условия 1НФ для таблицы 3.2 выполняются, значит, она находится в 1НФ. Это выполняется практически для всех корректно составленных универсальных отношений.

3.3.2 Вторая нормальная форма

Таблица находится во второй нормальной форме (2НФ), если она удовлетворяет определению 1НФ и все ее поля, не входящие в первичный ключ, связаны полной функциональной зависимостью с первичным ключом.

Поля «Единица измерения» и «Поставщик» функционально зависят от поля «Продукт». Поле «Расположение поставщика» функционально зависит от поля «Поставщик», значит, тоже от поля «Продукт». Поле «Продукт» является подмножеством составного первичного ключа. Здесь наблюдается нарушение 2НФ.

Для приведения таблицы к 2НФ все зависимые поля и часть первичного ключа выделяется в отдельную таблицу – «Справочник продуктов» (таблица 3.3). В нее вводится суррогатный первичный ключ «Код продукта». В таблице 3.2 сочетание значений «Мясо», «Кг», «Ферма», «Село» повторяется многократно. В таблице 3.3 эта строка будет встречаться однократно, ей присваивается код «1». Таким же образом в таблицу 3.3 однократно заносятся все различные сочетания значений в данных полях. Фактически, это различные продукты со своими характеристиками.

Из универсального отношения поля, вошедшие в «Справочник продуктов», удаляются. Для связи с таблицей 3.3 в промежуточную таблицу 3.4 вводится поле внешнего ключа «Код продукта», в которое заносятся коды соответствующих продуктов. Внешний ключ «Код продукта» в таблице 3.3 является частью первичного ключа, вместо удаленного поля «Продукт».

Внешний ключ в таблице помечается курсивом. Тот факт, что он является частью первичного ключа, отмечается «полужирным» шрифтом с подчеркиванием.

Справочник продуктов    Таблица 3.3

Код Продукта

Продукт

Единица измерения

Поставщик

Расположение поставщика

1

Мясо

Кг

Ферма

Село

2

Лук

Кг

Поле

Село

3

Соль

Г

Склад

Город

4

Яйцо

Шт

Птичник

Село

5

Картофель

Кг

Поле

Село

6

Зелень

Г

Поле

Село

7

Фрукты

Г

Сад

Село

8

Сахар

Г

Склад

Город

Процесс нормализации - итерационный. После разбиения таблиц, необходимо проверить соответствие новых таблиц желаемой НФ. В таблице 3.3 первичный ключ является простым полем, значит, эта таблица автоматически находится в 2НФ.

Промежуточная таблица    Таблица 3.4

Блюдо

Вид блюда

Количество порций

Цена порции

Дата продажи

Код Продукта

Расход

Котлета

Второе

15

8,50

01.03.10

1

0,1

Котлета

Второе

15

8,50

01.03.10

2

0,01

Котлета

Второе

15

8,50

01.03.10

3

5

Котлета

Второе

15

8,50

01.03.10

4

0,2

Суп

Первое

23

4,75

01.03.10

1

0,05

Суп

Первое

23

4,75

01.03.10

5

0,1

Суп

Первое

23

4,75

01.03.10

2

0,02

Суп

Первое

23

4,75

01.03.10

6

20

Суп

Первое

23

4,75

01.03.10

3

10

Компот

Десерт

30

1,50

01.03.10

7

30

Компот

Десерт

30

1,50

01.03.10

8

10

Шницель

Второе

17

10,00

02.03.10

1

0,15

Шницель

Второе

17

10,00

02.03.10

2

0,01

Шницель

Второе

17

10,00

02.03.10

3

5

Шницель

Второе

17

10,00

02.03.10

4

0,2

Суп

Первое

26

4,75

02.03.10

1

0,05

Суп

Первое

26

4,75

02.03.10

5

0,1

Суп

Первое

26

4,75

02.03.10

2

0,02

Суп

Первое

26

4,75

02.03.10

6

20

Суп

Первое

26

4,75

02.03.10

3

10

Компот

Десерт

28

1,50

02.03.10

7

30

Компот

Десерт

28

1,50

02.03.10

8

10

В таблице 3.4 поля «Вид блюда» и «Цена порции» функционально зависят от поля «Блюдо». Снова наблюдается нарушение требований 2НФ. Производится дальнейшее разбиение таблицы 3.4.

Справочник блюд Таблица 3.5

Код блюда

Блюдо

Вид блюда

Цена порции

1

Котлета

Второе

8,50

2

Суп

Первое

4,75

3

Компот

Десерт

1,50

4

Шницель

Второе

10,00

Выделяется таблица 3.5 «Справочник блюд» с первичным ключом «Код блюда». Таблица 3.4, из которой удалены поля «Блюдо», «Вид блюда» и «Цена порции», трансформируется в таблицу 3.6. В нее вводится внешний ключ «Код блюда», который будет являться частью составного первичного ключа.

В таблице 3.5 первичный ключ является простым полем, значит, эта таблица находится в 2НФ.

Промежуточная таблица   Таблица 3.6

Код Блюда

Количество порций

Дата продажи

Код Продукта

Расход

1

15

01.03.10

1

0,1

1

15

01.03.10

2

0,01

1

15

01.03.10

3

5

1

15

01.03.10

4

0,2

2

23

01.03.10

1

0,05

2

23

01.03.10

5

0,1

2

23

01.03.10

2

0,02

2

23

01.03.10

6

20

2

23

01.03.10

3

10

3

30

01.03.10

7

30

3

30

01.03.10

8

10

4

17

02.03.10

1

0,15

4

17

02.03.10

2

0,01

4

17

02.03.10

3

5

4

17

02.03.10

4

0,2

2

26

02.03.10

1

0,05

2

26

02.03.10

5

0,1

2

26

02.03.10

2

0,02

2

26

02.03.10

6

20

2

26

02.03.10

3

10

3

28

02.03.10

7

30

3

28

02.03.10

8

10

В промежуточной таблице 3.6 поле «Количество порций» функционально зависит от составного поля из двух атрибутов «Блюдо» и «Дата продажи», а поле «Расход» функционально зависит от составного поля из атрибутов «Блюдо» и «Продукт». Указанные составные поля являются подмножествами первичного ключа. Значит, таблица 3.6 все еще не соответствует 2НФ. При приведении таблицы 3.6 к 2НФ она, фактически, распадается на две таблицы:

- таблица 3.7 – «Продажи»;

- таблица 3.8 – «Калькуляции».

Это является закономерным результатом. Изначально в универсальном отношении были объединены две различные сущности: продажи блюд и их состав. В результате нормализации это неестественное объединение было разорвано.

Продажи  Таблица 3.7

Код Блюда

Количество порций

Дата продажи

1

15

01.03.10

2

23

01.03.10

3

30

01.03.10

4

17

02.03.10

2

26

02.03.10

3

28

02.03.10

Калькуляции  Таблица 3.8

Код Блюда

Код Продукта

Расход

1

1

0,1

1

2

0,01

1

3

5

1

4

0,2

2

1

0,05

2

5

0,1

2

2

0,02

2

6

20

2

3

10

3

7

30

3

8

10

4

1

0,15

4

2

0,01

4

3

5

4

4

0,2

Таблицы 3.3, 3.5, 3.7 и 3.8 удовлетворяют 2НФ.

3.3.3 Третья нормальная форма

Таблица находится в третьей нормальной форме (3НФ), если она удовлетворяет определению 2НФ и не одно из ее неключевых полей не зависит функционально от любого другого неключевого поля.

Таблица находится в нормальной форме Бойса-Кодда (НФБК), если и только если любая функциональная зависимость между его полями сводится к полной функциональной зависимости от возможного ключа.

В справочнике продуктов (таблица 3.3) поле «Расположение поставщика» функционально зависит от неключевого поля «Поставщик». Этим нарушается определений третьей нормальной формы. Для приведения таблицы 3.3 к 3НФ выделяем из нее поля «Расположение поставщика» и «Поставщик» в справочник поставщиков (таблица 3.9). Первичным ключом в ней будет поле «Код поставщика». «Справочник продуктов» примет вид (таблица 3.10), из которой удалены два поля и введен внешний ключ «Код поставщика».

Справочник поставщиков  Таблица 3.9

Код Поставщика

Поставщик

Расположение поставщика

1

Ферма

Село

2

Поле

Село

3

Склад

Город

4

Птичник

Село

5

Сад

Село

Справочник продуктов   Таблица 3.10

Код Продукта

Продукт

Единица измерения

Код поставщика

1

Мясо

Кг

1

2

Лук

Кг

2

3

Соль

Г

3

4

Яйцо

Шт

4

5

Картофель

Кг

2

6

Зелень

Г

2

7

Фрукты

Г

5

8

Сахар

Г

3

В таблице 3.9 поле «Расположение поставщика» находится в функциональной зависимости от поля «Код поставщика» или «Поставщик». Эти два поля являются возможными ключами в таблице, значит, она удовлетворяет НФБК.

В таблице 3.10 поля «Единица измерения» и «Код поставщика» находятся в функциональной зависимости от возможных ключей «Продукт» или «Код Продукта». Она удовлетворяет требованиям НФБК.

3.3.4 Четвертая и пятая нормальные формы

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

Таблица находится в пятой нормальной форме (5НФ) тогда и только тогда, когда в каждой ее полной декомпозиции все проекции содержат возможный ключ. Таблица, не имеющая ни одной полной декомпозиции, также находится в 5НФ.

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

В нормальных формах (4НФ и 5НФ) учитываются многозначные зависимости между полями таблицы.

В приведенных выше таблицах существуют многозначные зависимости между полями:

- в «Справочнике блюд» поле «Вид блюда» многозначно определяет поле «Блюдо», котлета и шницель являются вторым блюдом;

- в «Справочнике поставщиков» поле «Расположение поставщика» многозначно определяет поле «Поставщик»;

- в «Справочнике продуктов» поле «Единица измерения» многозначно определяет поле «Продукт».

Для приведения таблиц к 5НФ формируются справочники видов блюд, расположения поставщиков и единиц измерения. Как и при разбиении таблиц на предыдущих этапах, во вновь созданных таблицах вынесенные значения кодируются, для чего создаются суррогатные первичные ключи. В исходных таблицах вводятся внешние ключи для связывания с вновь созданными таблицами (таблицы 3.11 -3.16).

Справочник видов блюд Таблица 3.11

Код вида блюда

Вид блюда

1

Второе

2

Первое

3

Десерт

Справочник блюд  Таблица 3.12

Код блюда

Блюдо

Код вида блюда

Цена порции

1

Котлета

1

8,50

2

Суп

2

4,75

3

Компот

3

1,50

4

Шницель

1

10,00

Справочник расположения поставщиков    Таблица 3.13

Код расположения поставщика

Расположение поставщика

1

Село

3

Город

Справочник поставщиков  Таблица 3.14

Код Поставщика

Поставщик

Код расположения поставщика

1

Ферма

1

2

Поле

1

3

Склад

2

4

Птичник

1

5

Сад

1

Справочник единиц измерений Таблица 3.15

Код единицы измерения

Единица измерения

1

Кг

2

Г

3

Шт

Справочник продуктов   Таблица 3.16

Код Продукта

Продукт

Код единицы измерения

Код поставщика

1

Мясо

1

1

2

Лук

1

2

3

Соль

2

3

4

Яйцо

3

4

5

Картофель

1

2

6

Зелень

2

2

7

Фрукты

2

5

8

Сахар

2

3

Приведением таблиц к 5НФ нормализация завершается. Таблицы 3.7, 3.8, 3.11-3.16 находятся в 5НФ. Они составляют нормализованную базу данных. В этих таблицах нет многократно повторяющихся данных.

3.4. Составление диаграммы «таблица-связь»

Диаграмма «таблица-связь» является формальным описанием структуры базы данных. На диаграмме описывается структура всех таблиц, связи между ними и ограничения для обеспечения целостности данных.

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

- «Поле» - название поля таблицы символами латинского алфавита без пробелов. Длина имени поля не должна превышать 10 символов.

- «Тип»  - тип данных в поле таблицы задается латинскими буквами (C – символьный, N – числовой тип, D – дата, T – дата-время, L – логический, Y – денежный). Для символьного типа данных рядом с символом указывается длина поля в байтах (С 10 – символьное поле из десяти байт или символов). Для числового поля указывается общее количество разрядов или цифр.  Для дробного числа указывается количество цифр после запятой. Например, N 7 определяет целочисленный тип длиной в 7 цифр, а N 14.3 – дробное число длиной до 14 разрядов, три разряда из которых в дробной части. Для десятичной точки и знака «минус» в отрицательных числах необходимо резервировать по одному разряду.

- «Назначение»  - комментарий, описание данных, хранимых в данном поле.

- «Ограничение»  - задается правило для контроля достоверности данных в поле (ограничение, накладываемое пользователем). На рис. 3.1 для суррогатных ключей в этом поле указано, что оно является автоинкрементным (Incr), при добавлении новой строки в таблицу значение в это поле автоматически формируется системой.

При описании структуры полей следует выделять имена полей таблицы, входящие в первичный ключ «полужирным» шрифтом с подчеркиванием, а для внешнего ключа – курсивом.

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

- Delete – удаление первичного ключа: ограничение. Запрещается удалять первичный ключ, если его значение встречается среди внешних ключей

- Edit – изменение первичного ключа: каскадирование. Первичный ключ изменяется, и изменяются все, связанные с ним внешние ключи

- Insert – добавление внешнего ключа: установление. Разрешено добавлять новый внешний ключ, но его значение устанавливается в NULL.


Диаграмма «таблица-связь» базы данных «Кафе»

Рис. 3.1


3.5. Разработка вида печатных отчетов

По заданию необходимо сформировать два печатных отчета.

1. Распечатать ведомость реализованных блюд за заданный период с указанием общего дохода за день.

2. Сформировать отчет о продуктах, израсходованных на приготовление блюд.

Один из способов формирования отчетов заключается в том, что данные для него по запросу отбираются в курсор. После этого по заранее разработанному макету отчетов строится отчет по данным из курсора. Перед тем, как приступить к конструированию макета, необходимо разработать вид печатного отчета. При проектировании реальной базы данных вид отчета определяется заказчиком или законодательством.

Вид первого отчета представлен на рис. 3.2.

Ведомость реализованных блюд за период

с 01.03.2010 по 02.03.2010

№ п/п

Блюдо

Вид блюда

Количество порций

Цена

Стоимость

     01.03.2010

1 Котлета  Второе  15  8,50        127,50

2 Компот  Десерт  30  1,50          45,00

3 Суп   Первое  23  4,75        109,25

      Всего за день:        281,75

     02.03.2010

1 Шницель  Второе  17         10,00        170,00

2 Компот  Десерт  28  1,50          42,00

3 Суп   Первое  26  4,75        123,50

      Всего за день:         335,50

     Всего за период          617,25

Вид первого отчета        Рис. 3.2

В этом отчете данные сортируются по виду блюда, чтобы расположить последовательно в списке все блюда одного вида, например, «Десерт». Перед печатью списка проданных блюд выводится дата продажи, а после печати – итог за день. В конце отчета приводится итог реализации блюд за период.

Вид второго отчета представлен на рис. 3.3. В этом отчете не предусматривается печать итогов. В правом верхнем углу страницы необходимо проставлять ее номер.

Отчет о продуктах, израсходованных на приготовление блюд

Продукт

Единица измерения

Количество

Зелень

Г

980

Картофель

Кг

4,9

Лук

Кг

1,3

Мясо

Кг

6,5

Сахар

Г

580

Соль

Г

650

Фрукты

Г

1740

Яйцо

Шт

6,4

Вид второго отчета        Рис. 3.3

3.5. Разработка отчета «Ведомость реализованных блюд»

Вид курсора для отчета

Перед написанием запроса необходимо представить, в каком виде должны отбираться данные для построения отчета. В курсоре необходимо разместить данные, которые выводятся в табличном виде в отчете, а также дату продажи блюда, чтобы можно было сгруппировать данные по дням. При выполнении запроса будет рассчитываться стоимость проданных блюд, и заноситься в курсор. В переменных dDateStart и dDateEnd должны быть введены начальная и конечная дата периода. Переменные должны объявляться, как глобальные, чтобы обеспечить их видимость и в запросе, и при формировании отчета.

Вид курсора представлен в таблице 3.17

Вид курсора         Таблица 3.17

Date

Dish

Kinddish

Qty

Price

Summa

01.03.2010

Котлета

Второе

15

8,50

127,50

01.03.2010

Компот

Десерт

23

4,75

45,00

01.03.2010

Суп

Первое

30

1,50

109,25

02.03.2010

Шницель

Второе

17

10,00

170,00

02.03.2010

Компот

Десерт

28

4,75

42,00

02.03.2010

Суп

Первое

26

1,50

123,50

Источник данных для запроса

Данные для выборки по запросу хранятся в различных таблицах базы данных. На рис. 3.4 представлен источник данных для запроса:

- дата продажи и количество проданных блюд хранятся в таблице продаж (Sale) в полях Date и Qty;

- наименование блюда и его цена – поля Name и Price таблицы Dish (Справочник блюд);

- вид блюда – поле Name таблица KindDish (Справочник видов блюд).

Таблицы Sale и Dish связаны по ключам IdDish, а таблицы Dish и KindDish - по ключам IdKind.

В скобках приведены локальные псевдонимы таблиц.

 KindDish (KD)  Dish (DI)  Sale (SA)

Источник данных для запроса       Рис 3.4

Запрос на выборку данных

Select Date, DI.Name as Dish, KD.Name as KindDish, Qty, Price, ;

Qty * Price as Summa;

 From Sale SA Inner Join Dish DI Inner Join KindDish KD ;

 On KD.IdKind = DI. IdKind  On DI.IdDish = SA. IdDish ;

 Where Date >= dDateStart and Date <=dDateEnd;

 Order By Date, KindDish ;

Into Cursor CursorRep1

Запрос на выборку данных осуществляется командой Select. Подробное описание инструкций команды приведено в приложении 2. В приведенном запросе после инструкции Select определяется список полей курсора. Если в источнике данных имя выбираемого поля уникально, его не надо уточнять именем таблицы – поля Date, Qty, Price. В противном случае имя поля необходимо уточнить именем таблицы – DI.Name, KD.Name, где DI и KD - локальные псевдонимы таблиц Dish и KindDish. После указания имени поля можно разместить инструкцию as и назначить имя поля в курсоре. Если не назначать имя поля курсора, то оно будет совпадать с именем поля таблицы. Для вычисляемых полей (Qty * Price as Summa) необходимо назначать имя поля в курсоре, чтобы его можно было использовать в макете отчета.

После инструкции From описывается источник данных, состоящий из трех таблиц связанных внутренним объединением – фраза Inner Join. После инструкций On указываются условия объединения – совпадение значений в полях таблиц. Таблицы объединены в линию (рис. 3.4), при указании вида объединения они перечисляются последовательно справа – налево, можно и наоборот. Условия объединения перечисляются в обратном порядке. После имени таблицы через пробел определяется ее псевдоним, который используется в тексте запроса.

В фильтре после инструкции Where определяются условия включения строк в курсор. Указанное условие разрешает включать в курсор только те строки, у которых дата продажи блюда находится в заданном периоде от dDateStart до dDateEnd.

В инструкции Order By определяется порядок сортировки данных в курсоре. Данные сортируются по дате продажи, при одинаковых значениях даты по виду блюда. Поля для сортировки перечисляются через запятую. По умолчанию сортировка производится в порядке возрастания.

В результате выполнения запроса отобранные данные размещаются в курсор, имя которого задается в инструкции Into Cursor. Вид сформированного курсора должен совпадать с разработанным (Табл. 3.17).

Макет отчета

Перед конструированием макета отчета, надо проанализировать вид печатного документа и разбить его на зоны.

Назначение зон:

REPORT HEADER(TITLE) – заголовок отчета. В этой зоне размещается информация, которую надо напечатать один раз в начале отчета.

PAGE HEADER – заголовок страницы. Размещается информация, которую надо напечатать в начале каждой страницы отчета.

GROUP HEADER – заголовок группы. В этой зоне размещается информация, которую надо печатать, когда начинается новая группа данных. Допускается вложенность до 20 уровней.

DETAIL - зона детализации. Размещается информация, которую нужно печатать для каждой строки из источника данных.

GROUP FOOTER – подвал группы. Здесь распечатывается информация, когда заканчивается текущая группа данных.

PAGE FOOTER – подвал станицы. Размещается информация, которую надо напечатать в конце каждой страницы отчета.

REPORT FOOTER (SUMMARY)– подвал отчета. Размещается информация, которую надо напечатать один раз в конце отчета.

В зоне Title (рис. 3.5) с помощью текстовой метки выводится заголовок отчета «Ведомость реализованных блюд за период c   по», текст которого не будет изменяться в зависимости от заданного периода. В элементах управления Field размещаются имена переменных dDateStart и dDateEnd, для печати дат начала и окончания периода. Поле Field на рисунке показан в виде прямоугольника, в котором записано имя переменной с прочерком.

Отчет может занимать несколько страниц, поэтому в начале каждой страницы требуется печатать заголовок таблицы. В зоне Page Header с помощью графических элементов: прямоугольника и линий прорисовывается заголовок таблицы. Текст заголовка формируется текстовыми метками.

Все данные о продаже блюд в отчете необходимо разбить на группы с одинаковой датой продажи. В заголовке группы данных требуется напечатать дату, затем выводить список блюд, проданных за этот день. В макете отчета производится группировка данных по полю курсора Date. Если в отчете производится группировка данных по полю курсора, данные в курсоре обязательно должны быть отсортированы по этому полю. В противном случае данные о продажах за одну и ту же дату можно будет встретить в разных местах отчета. В зоне Group Header размещается элемент Field, который связывается с полем курсора Date.

Информацию из каждой строки курсора требуется напечатать в одной строке таблицы отчета. В зоне Detail располагаются элементы Field, связанные с соответствующими полями курсора. Номера строки по порядку в курсоре нет, функцию счетчика будет выполнять переменная Npp.

По окончании печати продаж блюд за день необходимо вывести итоговую сумму. В зоне Group Footer 1 располагается текстовая метка «Всего за день:» и элемент Field, связанный с переменной SumD, в которой будет накапливаться сумма продаж за день.

Ведомость реализованных блюд за период

c  dDateStart___    по   dDateEnd____

Title

№ п/п

Блюдо

Вид блюда

Количество порций

Цена

Стоимость

Page Header

Date______

Group Header 1: Date

Npp_

Dish_______

KindDish___

Qty________

Price_______

Summa____

Detail

Всего за день:       

SumD____

Group Footer 1

Page Footer

Всего за период:

SumAll___

Summary

Макет отчета                           Рис 3.5

В конце страницы ничего не печатается, поэтому зона Page Footer остается пустой.

По окончании формирования отчета распечатывается итоговая сумма продаж за период. В зоне Summary располагается текстовая метка «Всего за период:» и элемент Field, связанный с переменной SumAll, в которой будет накапливаться сумма продаж за заданный период.

В макете отчета используются 3 переменные: Npp, SumD, SumAll. В табличном виде необходимо задать правила функционирования этих переменных (табл. 3.18):

- Имя – задается имя переменной;

- Функция – описывается тип выполняемой функции для этой переменной (Count – счетчик, Sum - сумматор);

- Значение – указывается имя поля, значение которого будет накапливаться в сумматоре. Для счетчика то поле остается пустым;

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

- Сброс в начальное значение – задается событие, по которому переменные будут принимать начальное значение. Переменные Npp и SumD обнуляются по окончании печати очередной группы данных (продажи за день). SumAll накапливает сумму по всему периоду, поэтому она должна обнуляться в конце отчета;

- Уничтожать – переменную по окончании отчета можно уничтожить (освободить память), или оставить для дальнейшего использования. В текущем отчете переменные не сохраняются.

Описание переменных                         Таблица 3.18

Имя

Функция

Значение

Начальное значение

Сброс в начальное значение

Уничтожать

Npp

COUNT

__

0

END OF GRUP 1

.T.

SumD

SUM

SUM

0

END OF GRUP 1

.T.

SumAll

SUM

SUM

0

END OF REPORT

.T.

3.6. Разработка отчета «О продуктах, израсходованных на приготовление блюд»

Разработка каждого отчета производится по одной и той же схеме, приведенной выше.

Вид курсора для отчета

В курсоре достаточно иметь 3 поля: Product, Unit, и Qty. В них размещаются данные о названии продукта, его единице измерения. При выполнении запроса будет подсчитываться итоговое количество каждого продукта, затраченное на изготовление всех реализованных блюд, и заноситься в курсор в поле Qty.

Вид курсора представлен в таблице 3.19

Вид курсора              Таблица 3.19

Product

Unit

Qty

Зелень

Г

980

Картофель

Кг

4,9

Лук

Кг

1,3

Мясо

Кг

6,5

Сахар

Г

580

Соль

Г

650

Фрукты

Г

1740

Яйцо

Шт

6,4

Источник данных для запроса

На рис. 3.6 представлен источник данных для запроса:

- наименование продукта хранится в таблице Product (Справочник продуктов) в поле Name;

- наименование единицы измерения – поле Name таблицы Unit (Справочник единиц измерения);

- количество израсходованного продукта – поле, вычисляемое как произведение количества проданных блюд (поле Qty таблицы Sale) и расхода продукта на одно блюдо (поле Qty таблицы Struct).

Таблицы Sale и Struct связаны по ключам IdDish, таблицы Struct и Product - по ключам IdProd, таблицы Product и Unit - по ключам IdUnit.

В скобках приведены локальные псевдонимы таблиц.

 Sale (SA)  Struct (ST)   Product (PR) Unit (UN)

Источник данных       Рис 3.6

Запрос на выборку данных

В курсор необходимо однократно поместить наименование продукта. В источнике данных оно будет встречаться столько раз, сколько раз продавались разные блюда, в состав которых входит этот продукт, в различные дни. Поэтому, в запросе будет производиться группировка данных, чтобы подсчитать общий расход продукта на все блюда за весь период, и показать это в курсоре одной строкой.

Select PR.Name as Product, UN.Name as Unit, Sum(SA.Qty * ST.Qty) as Qty;

 From ( Sale SA Inner Join Struct ST Inner Join Product PR ;

 On ST.IdProd = PR.IdProd  On SA.IdDish = ST. IdDish ) ;

 Inner Join Unit UN  On PR.IdUnit = UN.IdUnit ;

 Group By PR.Name ;

Into Cursor CursorRep2

В списке полей запроса PR.Name и UN.Name являются неизменяемыми при группировке данных. Они просто перечисляются , и им присваиваются имена в курсоре. Третье поле является вычисляемым (SA.Qty * ST.Qty), кроме того все произведения необходимо просуммировать для группы данных (одного продукта). Поэтому произведение является параметром для агрегатной функции Sum, которая обеспечит получение суммы произведений.

В описании источника данных применяются скобки. В скобках описан источник данных из трех линейно объединенных таблиц Sale, Struct и Product, по аналогии с первым запросом. После закрытия скобок этот источник является единым целым, и с ним объединяется таблица Unit. Условие объединения таблицы Unit может включать поле любой таблицы из данного источника. С применением скобок можно описать источник данных любой сложности. Без скобок описать источник данных, в котором одна таблица объединена с тремя и больше не представляется возможным. В данном запросе без скобок можно обойтись, так как таблицы объединены линейно. Скобки используются только для примера.

В инструкции Group By указывается поле или список полей для группировки данных. Для проектируемого отчета требуется получить итоговую сумму расхода каждого продукта, поэтому указано имя поля PR.Name (наименование продукта и справочника продуктов).

В последней инструкции запроса задается имя курсора CursorRep2.

Макет отчета

Отчет о продуктах, израсходованных на приготовление блюд

Title

_PageNo____________

Продукт

Единица измерения

Количество

Page Header

Product__________

Unit___________________

Qty____________________

Detail

Page Footer

Summary

Макет отчета   Рис 3.7

В зоне Title в текстовой метке располагается заголовок отчета.

В зоне Page Header будет распечатываться заголовок таблицы. Перед заголовком размещается элемент Field, связанный с системной переменной _PageNo. Эта переменная инициализируется перед печатью отчета и содержит номер текущей печатаемой страницы.

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

Дополнительные переменные в макете отчета не используются.


Приложение 1.

В А Р И А Н Т Ы   З А Д А Н И Й

  1.  В расписании движения самолетов из аэропорта г.Донецка указаны следующие сведения: номер рейса (5 цифр); тип самолета, аэропорт назначения; дни или даты вылета, транзитные аэропорты, расстояние в км; стоимость билета (взрослый и детский); время вылета, время прилета в пункт назначения, время в полете в часах и мин. Сформировать таблицу рейсов для заданного аэропорта назначения, а также таблицу сведений о пяти рейсах с максимальной продолжительностью полета, вычислив среднюю скорость полета.
  2.  В магазине имеются следующие записи о товарах: название, артикул (две буквы и 4 цифры) единица измерения, номер отдела, цена (грн. и коп), кол-во прибывшего, проданного и оставшегося товара за текущие сутки, время продажи, фамилия продавца. Составить таблицу проданного товара, сгруппировав ее по уменьшению общей стоимости продажи, и таблицу товаров, поданных до 15.00. Определить продавцов, продавших максимальное и минимальное количество товара.
  3.  В магазине радиоэлектроники имеются следующие данные: название прибора, страна-изготовитель, год выпуска, оптовые и розничные цены, гарантийный срок эксплуатации, условия доставки. Необходимо составить список приборов, полученных из Сингапура и выпущенных в заданном году, а также список приборов, гарантийный срок которых более трех лет. Определить среднюю стоимость аппаратуры для заданной страны-изготовителя.
  4.  В аптеке ведется учет лекарственных средств. Имеются следующие данные: название лекарства, страна-изготовитель, цена упаковки, количество упаковок, год выпуска, срок хранения (в годах). Определить список лекарств, не годных к употреблению на заданный год, и пять самых дорогих лекарств. Получить сведения о лекарствах для заданной страны.
  5.  В заводском цеху ведется журнал расхода материалов по следующим данным: наименование материала, поставщик (Украина: город, фирма; зарубежье: страна, город, фирма), код (3 буквы и 6 цифр), расход в сутки, имеющееся количество в цеху. Необходимо вывести список материалов, которые закончатся через заданное количество дней, а также список пяти наименее расходуемых материалов для заданного поставщика.
  6.  На АТС ведется учет междугородних разговоров абонентов по следующим данным: фамилия и инициалы абонента, домашний адрес, номер телефона, страна и город, с которым связывался абонент, стоимость телефонного разговора за месяц (в грн. и коп). Необходимо вывести список из 10-ти абонентов, наиболее широко использующих междугороднюю сеть. Сформировать итоговую таблицу стоимости разговоров для каждого абонента.
  7.  Диспетчер автовокзала отмечает автобусы, прибывшие и ушедшие с вокзала. Журнал ведется по следующим данным: номер рейса, пункт назначения, номер автобуса, фамилия и инициалы водителя, время отправки, время прибытия, путь следования. Составить список автобусов, находящихся в пути на заданное время и определить рейсы, позволяющие достичь нужного города, в том числе учитывать города по пути следования.
  8.  В книге заявок жилищного ремонтного управления содержатся сведения о заявках на ремонт: фамилия и инициалы заявителя, адрес, тип ремонта (малый, средний, капитальный), даты заявки и планового срока выполнения ремонта, фактический срок выполнения. Отпечатать список очередников, у которых ремонт выполнен с опозданием в три и более месяцев, а также сведения о трех первоочередниках на малый ремонт. Сформировать таблицу первоочередников по заданному адресу.
  9.  В часовой мастерской имеются сведения о проведении ремонтов в текущем году: фамилия и инициалы заказчика, его адрес, марка часов, стоимость ремонта, дата поступления в ремонт и плановый срок исполнения, дата окончания ремонта, послеремонтный гарантийный срок. Предусмотреть анализ и печать сведений о задержках выполнения ремонта в заданном месяце. Определить максимальную и среднюю задержки. Сформировать таблицу сведений о проведенном ремонте для заданной марки часов.
  10.  В справочном бюро содержатся следующие сведения: фамилия, имя и отчество, дата рождения, адрес (район, улица, дом, квартира), домашний телефон. Отпечатать сведения о гражданах с заданными фамилией, именем, отчеством или возрастной группой (например, старше 55 лет). Определить трех старейших жителей города. Определить средний возраст жителей по заданному адресу(улица, дом).
  11.  В жилищном управлении имеются следующие сведения о проживающих: адрес (улица, номер дома, номер квартиры), фамилия и инициалы квартиросъемщика, количество проживающих, дата переезда по данному адресу, общая площадь квартиры, полезная площадь. Отпечатать список квартиросъемщиков, у которых имеется лишняя площадь (норма на человека 13м2). Определить пять квартиросъемщиков, которые занимают минимальную площадь в расчете на одного человека.
  12.  В личных карточках рабочих предприятия отражены следующие сведения: табельный номер (2 буквы и 5 цифр),  цех, фамилия и инициалы, дата рождения, профессия, год приема на работу, разряд. Вычислить для заданного цеха общее количество рабочих и количество рабочих, имеющих соответственно разряды 3, 4, 5, 6 (абсолютные и относительные значения в % к общему количеству рабочих в цехе). Определить цех, имеющий в среднем самый молодой состав рабочих.
  13.  Статистические результаты спортсменов на чемпионате мира по хоккею с шайбой содержат следующую информацию: ФИО, команда, возраст, вес, игровое амплуа, количество проведенных матчей, количество голов, количество результативных пасов, количество штрафных минут. Определить 10 самых грубых защитников в возрасте от 18 до 20лет. Сформировать список игроков европейских команд, набравших максимальное количество очков по системе гол + пас.
  14.  Данные о студентах вуза включают в себя следующие сведения: фамилия и инициалы, год рождения, номер студенческого билета (6 цифр), факультет (до 5 букв), группа, название дисциплины и оценки экзаменационной сессии (6 предметов). Для заданной дисциплины определить количество оценок 5, 4, 3 и 2 (абсолютные и относительные значения в %). Отпечатать список отличников. Для каждого студента определить лучший и худший семестр с точки зрения успеваемости.
  15.  По каждому из выпускаемых заводом изделий имеются следующие сведения: номер цеха и наименование, код изделия (2 буквы и 5 цифр), наименование изделия, количество изделий, себестоимость, оптовая цена. Требуется для каждого цеха определить: суммарные затраты, суммарный доход, коэффициент эффективности. Определить в целом по заводу три самых массовых изделия.
  16.  Имеются сведения о составе компьютеров на кафедрах университета: название кафедры, тип компьютера, параметры (тактовая частота, емкость оперативной памяти, емкость винчестера, состав периферийных устройств), стоимость, количество, область использования (учебная работа и/или НИР), год выпуска. Отпечатать список учебных ПЭВМ с тактовой частотой не менее 500 МГц и наличием CD ROM 40x и выше, а также определить их общую стоимость. Указать список трех кафедр, на которых установлено наибольшее количество ПЭВМ для НИР.
  17.  На почтамте имеются следующие данные о подписных периодических изданиях (газетах и журналах): номер квитанции (2 буквы и 4 цифры), фамилия и инициалы подписчика, домашний адрес ( п/о, улица, дом, квартира), индекс издания (5 цифр), наименование издания, номер первого месяца подписки, количество месяцев подписки, стоимость подписки. Отпечатать две группы сведений:

а) по изданиям - индекс, наименование, количество подписчиков, сумма подписки, среднее количество месяцев подписки;

б) по почтовым отделениям - количество изданий, количество подписок, сумма подписок.

  1.  По каждой из лабораторий вуза имеются следующие данные: номер лаборатории – 4 цифры(первая цифра означает номер корпуса), название лаборатории, шифр кафедры (2 цифры), название кафедры, тип лаборатории (учебная или научная), ее площадь, количество рабочих мест. Для заданной кафедры сформировать таблицу: шифр и название кафедры, количество лабораторий (общее, учебных, научных), их площадь, количество рабочих мест. Дать сведения о трех кафедрах, имеющих максимальную общую площадь лабораторий. Определить количество лабораторий в заданном корпусе.
  2.  Турнирная таблица чемпионата страны по футболу содержит следующие сведения: наименование команды, город, фамилия и инициалы тренера, количество проведенных игр, результаты игр (количества побед, поражений, ничьих), количество забитых и пропущенных мячей. Требуется подсчитать количество очков для заданной команды и отпечатать данные о командах в порядке их положения в турнирной таблице. Определить две команды с лучшей разницей забитых и пропущенных мячей.
  3.  Даны записи о расходовании электроэнергии на заводах области. Структура записи: номер завода, наименование завода, район, фамилия и инициалы директора, фамилия и инициалы главного энергетика, расход электроэнергии в тыс.кВт-ч (плановый и фактический). Для каждого завода подсчитать размер отклонения фактического расхода от планового (абсолютное значение и относительное в % с учетом знака отклонения). Отпечатать сведения о двух заводах с максимальным относительным значением экономии электроэнергии. Сформировать таблицу о расходе электроэнергии для заданного района.
  4.  Информация о продаже товаров имеет следующую структуру: наименование магазина и секции, номер чека (буква и 4 цифры), наименование товара, артикул товара (6 цифр), цена товара (в грн. и коп.), количество товара, дата продажи. Требуется определить общую сумму выручки по заданному магазину. Выбрать две секции с максимальным товарооборотом. Сформировать таблицу со сведениями о продаже по заданному товару.
  5.  Для обработки на ЭВМ поступили сведения о производстве деталей за прошлую неделю (неделю считать шестидневной). Структура записи: шифр наряда (5 цифр), дата, цех, участок, табельный номер, вид операции, разряд работы, количества изготовленных и принятых деталей. Для заданного цеха и заданного дня (даты) определить количество бракованных деталей. Определить также день недели, в который количество бракованных деталей было выпущено больше всего. Привести таблицу сведений о производстве деталей для заданного цеха.
  6.  На каждого из спортсменов, заявленных на соревнования по легкой атлетике, представлены следующие данные: регистрационный номер (три цифры), фамилия и инициалы, страна, возраст (лет), рост (см), вид соревнований, личный рекорд, лучший результат сезона. Определить для заданной страны: количество спортсменов, в том числе по интервалам возраста (до 18 лет, от 18 до 20 лет, от 20 до 25 лет, свыше 25 лет), средний возраст спортсменов (в дробной части числа - одна цифра), количество заявленных видов соревнований. Отпечатать сведения о спортсменах, участвующих в заданном виде соревнований.
  7.  На АТС для каждого междугородного разговора используется следующая информация: телефон абонента, его фамилия и инициалы, домашний адрес, заказанный город, расстояние в км, телефон заказа, дата и время разговора (в мин). Сформировать таблицу-счет для оплаты переговоров, считая, что стоимость одной минуты составляет: 10 коп. - до 200 км, 20 коп. - до 500 км, 30 коп. - свыше 500 км. В таблице указать сумму переговоров по каждому городу. Определить три города, для которых время переговоров максимальное.
  8.  В фирменном магазине радиоэлектроники ведется учет продажи телевизоров: дата продажи, номер кассового чека, тип телевизора, стоимость, страна-изготовитель, наличие рекламации покупателя, отметка об обмене телевизора или стоимости гарантийного ремонта (при наличии рекламации). Сформировать таблицу со сведениями о телевизорах, изготовленных в Гонконге, Сингапуре и России, на которые поступили рекламации в течение года после их продажи, а также список трех типов телевизоров с наибольшей суммой выручки.
  9.  В читальном зале небольшой библиотеки указана следующая информация о книгах: шифр, авторы, название книги, издательство, год издания, количество страниц, предметная область использования, количество заказов. Сформировать список книг по вычислительной технике, изданных в издательстве «Вища школа». Определить 10 наиболее популярных книг, посвященных изучению ООП.
  10.  На республиканской студенческой олимпиаде после проверки работ создана база данных с информацией об участниках: фамилия и инициалы, город, предмет (физика/математика/химия/информатика), баллы по каждой из десяти задач. Распечатать данные о трех победителях по каждому из предметов. Определить, какой город принял наиболее активное участие в олимпиаде и студенты какого города выступили лучше других.
  11.  В бухгалтерии предприятия ведется учет зарплаты по форме: год, месяц, отдел, фамилия и инициалы, зарплата, премия, сумма к выдаче (принять налог 20% от зарплаты + премии). Составить по каждому отделу список рабочих с максимальной выплаченной суммой. Сформировать ведомость выдачи зарплаты для заданного месяца и отдела. Определить три отдела с общей максимальной выплаченной суммой.
  12.  На ж/д вокзале имеется информация о проданных билетах: номер поезда, станция назначения, дата и время отправления, количество часов в пути следования, станция, на которую продан билет, и стоимость билета (грн. и коп.), тип места(спальный, купе, плацкарт, общий), станции следования. Определить список поездов, отбывающих из Донецка с 8 до 12 часов. Определить также три номера поездов, на которые продано больше всего билетов в стоимостном выражении. Сформировать таблицу поездов, проходящих через заданную станцию.
  13.  Предприятие, обслуживающее сети кабельного телевидения, использует следующие данные: улица, номер дома, номер квартиры, фамилия и инициалы, тип телевизора, стоимость обслуживания в месяц, оплата за каждый предыдущий месяц в течение года ("Да", "Нет"). Сформировать таблицу полученной арендной платы по каждому дому за 6 мес., а также список должников за 2 и более мес.
  14.  В записной книжке содержатся следующие сведения: фамилия, имя и отчество, адрес (город, улица, дом, квартира), телефон, дата рождения. Сформировать список именинников, чей день рождения наступит не более чем через месяц после заданной даты, а также список юбиляров в течение года, начиная с указанной даты.
  15.  В бюро по обмену квартир используется следующая информация: ФИО, улица, номер дома, номер квартиры, количество комнат, общая площадь, полезная площадь, наличие лифта, наличие телефона. Определить возможные варианты обмена 4=2+2, так, чтобы площадь четырехкомнатной квартиры не отличалась от площади двухкомнатных квартир не более, чем на 10м2, причем все квартиры должны быть с лифтом и телефоном.
  16.  В отделе сбыта завода имеется информация: организация заказчика, шифр товара, наименование товара, цена, количество, плановый и фактический сроки поставки. Сформировать список заказов, по которым задержка поставки превышает 10 дней, а также список заказчиков с указанием общей суммы заказа.
  17.  В почтовом отделении имеются сведения о подписчиках: адрес (улица, дом, квартира), фамилия и инициалы, индекс(5 цифр) и название издания, цена за месяц, начало и конец подписки в месяцах. Отпечатать список подписчиков газеты "Вечерний Донецк" на срок не менее 6 мес., а также сводную таблицу изданий с указанием количества и суммы подписки.
  18.  Годовой отчет швейной фабрики о выпуске продукции содержит сведения: наименование цеха, наименование изделия, артикул, объем выпуска плановый и фактический по товарам I, II и III сорта. Часть изделий выпускается только I или только I и II сортов. Отпечатать таблицу изделий I сорта, по которым выполнен план их выпуска, а также список трех цехов, которые в наибольшей степени не выполнили план.
  19.  В кассах предварительной продажи ж/д билетов имеются следующие сведения: номер поезда, станция назначения, дата отправления, количества мест и проданных билетов (спальных, купейных, плацкартных и общих). В некоторых поездах спальные или общие места могут отсутствовать. Для заданной даты составить список поездов, для которых количество проданных билетов превышает 90 % их общего количества, а также сведения о трех поездах, в которых остались лишь общие места.
  20.  В расписании движения самолетов из аэропорта г.Киева указаны следующие сведения: номер рейса (5 цифр); тип самолета, аэропорт назначения; дни или даты вылета, транзитные аэропорты, расстояние в км; стоимость билета (взрослый и детский); время вылета, время прилета в пункт назначения, время в полете в часах и мин. Сформировать таблицу рейсов для заданного типа самолета, а также расписание по дням недели и по датам.
  21.  Для подведения итогов межсессионного контроля в деканате используется следующая информация: группа, фамилия и инициалы студента, список изучаемых предметов, количество пропусков по каждому предмету по уважительной и неуважительной причинам, результаты аттестации (+/–). Сформировать список студентов, подлежащих отчислению за пропуск по неуважительной причине более 30 часов и список студентов, имеющих три и более неаттестаций.
  22.  В учебном плане специальности изучаемые дисциплины разделены на четыре группы: социально-экономические, фундаментальные, специальные, выборные дисциплины. По каждой дисциплине указана следующая информация: номера семестров, отведенных для изучения дисциплины, форма отчетности(экзамен зачет), количество студентов, изучающих дисциплину, количество недель в семестре, количество часов лекций, практических занятий, лабораторных работ в неделю, информация о курсовых проектах или работах. Сформировать таблицу изучаемых дисциплин по семестрам и рассчитать количество отведенных учебных часов для всех дисциплин каждого семестра.
  23.  В учебном плане специальности изучаемые дисциплины разделены на четыре группы: социально-экономические, фундаментальные, специальные, выборные дисциплины. По каждой дисциплине указана следующая информация: номера семестров, отведенных для изучения дисциплины, форма отчетности(экзамен зачет), количество студентов, изучающих дисциплину, количество недель в семестре, количество часов лекций, практических занятий, лабораторных работ в неделю, информация о курсовых проектах или работах. Сформировать таблицу изучаемых дисциплин за учебный год и рассчитать количество зачетов и экзаменов для каждого семестра.
  24.  В прайс-листе книжного магазина указана следующая информация: номер позиции, автор, название книги, цена, издательство, количество страниц, наличие компакт-диска. Книги сгруппированы по разделам (аппаратное обеспечение, ремонт и обслуживание компьютеров, программирование, сети, информационные технологии и т.п.). Сформировать таблицу продаваемых книг одного издательства и составить несколько вариантов покупки книг разных разделов на заданную сумму.
  25.  В пункте обмена валюты используется следующая информация за последнюю неделю работы: наименование валюты, страна, курс покупки, курс продажи, имеющееся количество на начало дня, количество купленной и проданной валюты, сумма налога с операции продажи(2%). Сформировать данные о работе пункта по каждой валюте.
  26.  Туристическая фирма предлагает клиентам следующую информацию о путевках: страна/страны, город/города, место проживания, срок, необходимость медицинской страховки, наличие санаторного обслуживания, способ проезда (автобус, поезд, самолет), стоимость(взрослые, дети), вид отдыха(туризм, лечение, турне, круиз и т.п.). Сформировать таблицу для клиентов, нуждающихся в санаторном лечении, а также таблицу со сведениями о месте отдыха, куда можно добраться на поезде.
  27.  В гостинице в базе данных содержится следующая информация о номерах: номер, вид номера(люкс, 2-хкомнатный номер, 1-комнатные номера на одного, двух, трех, четырех человек, стоимость, наполненность (занят/свободен, сколько мест занято), пол жильцов(м/ж), оплата(полная, частичная, не оплачено),срок пребывания, дата заселения). Сформировать список жильцов, выселяющихся в ближайшие три дня, а также список незаселенных комнат.
  28.  В расписании движения самолетов указаны следующие сведения: номер рейса (4 цифры); тип самолета, аэропорт назначения; дни или даты вылета, транзитные аэропорты, расстояние в км; стоимость билета (взрослый и детский); время вылета, время прилета в пункт назначения, время в полете в часах и мин. Сформировать таблицу рейсов для заданного аэропорта назначения(в том числе и для транзитного), а также таблицу сведений о пяти рейсах с максимальной наполненностью самолета.
  29.  В программе телепередач указано: название телекомпании, время передачи, название передачи, жанр передачи (художественный фильм, развлекательная передача, научно-популярная передача, новости, концерт и т.п.). Сформировать список художественных фильмов в хронологическом порядке, продолжающихся менее 85мин.
  30.  Врач ведет учет посещений пациентов: дата, Ф.И.О. больного, возраст, пол, диагноз, назначенное лекарство, назначенные процедуры, время следующего посещения. Определить лекарство, наиболее часто используемое для лечения, и составить список десяти наиболее встречаемых диагнозов среди пациентов 20-40 лет.
  31.  Прайс-лист фирмы, торгующей автомобилями, содержит следующие сведения: фирма-производитель, марка автомобиля, объем и мощность двигателя, тип кузова, цвет, цена. Сформировать список автомобилей черного цвета заданной фирмы, имеющих стоимость от 10000 до 30000, и автомобилей заданного цвета и заданного диапазона объема двигателя.
  32.  В зоопарке ведется следующая документация: название животного, вид (хищное/травоядное/всеядное), место обитания, среда обитания, возраст, вес. Сформировать список хищников, обитающих в Южной Америке, вес которых от 20 до 350кг, а также список травоядных в возрасте от 5 до 25 лет, обитающих на деревьях.
  33.  В университете для составления расписания используются следующие данные: номер корпуса, номер аудитории, номер этажа, вид аудитории(лекционная, для проведения практических занятий, лаборатория), количество мест в аудитории. Сформировать список аудиторий с количеством посадочных мест от 20 до 40, находящихся на втором этаже. Определить 5 наиболее крупных лабораторий в заданном корпусе.
  34.  В ботаническом саду ведется следующая документация: название растения, вид, место произрастания, время и срок цветения, тип плодов, средняя продолжительность жизни. Сформировать список однолетних растений, произрастающих в центре Европы и цветущих мае-июне, а также список многолетних бобовых растений.
  35.  В каталоге звезд имеется следующая информация: название звезды, звездная величина, тип, цвет, яркость, температура поверхности, удаленность от Солнца, масса. Сформировать список белых карликов, удаленных не более, чем на 1000 световых лет. Определить 20 наиболее массивных звезд с температурой поверхности от 5000 до 20000С.
  36.  В порту ведется следующая документация: название судна, страна приписки, тип судна, водоизмещение, наименование груза, порт назначения, необходимость загрузки, разгрузки, ремонта и т.п. Определить список судов с наибольшим водоизмещением, следующих в Кейптаун и перевозящих продовольствие. Сформировать список греческих танкеров, нуждающихся в ремонте.
  37.  В справочнике приведены следующие данные: название металла, атомный вес, цвет, свойства (редкоземельный, драгоценный, щелочные и т.п.), температура плавления, плотность, электропроводность. Сформировать список редкоземельных металлов с заданным диапазоном температур плавления. Определить 10 металлов желтого цвета, имеющих наивысшую плотность.
  38.  Статистические результаты спортсменов на чемпионате мира по хоккею с шайбой содержат следующую информацию: ФИО, команда, возраст, вес, игровое амплуа, количество проведенных матчей, количество голов, количество результативных пасов, количество штрафных минут. Определить 10 результативных нападающих, получивших не более 16 минут штрафа. Сформировать список вратарей американских команд, имеющих наименьший показатель количество пропущенных шайб/количество матчей.
  39.  В географическом атласе есть следующие сведения: название вулкана, страна, высота, тип(действующий, потухший, надводный, подводный и т.п. вулкан), диаметр кратера, дата последнего извержения, количество зарегистрированных извержений. Определить 10 крупнейших вулканов, извергавшихся за последние 10 лет не менее пяти раз. Сформировать список потухших вулканов, находящихся в Африке.
  40.  В каталоге лекарственных растений содержатся следующие сведения: название растения, вид, место произрастания, время и срок цветения, тип плодов, время сбора, что собирают(корень, листья цветы и т.п.). Сформировать список растений, произрастающих в центре Украины и цветущих мае-июле, а также список растений, у которых необходимо собирать цветы.
  41.  В читальном зале небольшой библиотеки указана следующая информация о книгах: шифр, авторы, название книги, издательство, год издания, количество страниц, предметная область использования. Сформировать список книг по вычислительной технике, изданных с 1998 по 2001гг. Определить 10 наиболее толстых книг, посвященных изучению языка С++.
  42.  В трамвайно-троллейбусном управлении используется следующая информация о маршрутах: номер маршрута, вид транспорта(трамвай, троллейбус), протяженность маршрута, время прохождения маршрута, количество остановок, список районов, по которым проходит маршрут. Сформировать список маршрутов трамваев, проходящих по Киевскому району. Определить маршруты, на которых транспорт имеет наибольшую среднюю скорость.
  43.  В картинной галерее используется следующая информация: номер зала, автор картины, название картины, страна, год, направление живописи (классицизм, импрессионизм и т.п.). Определить пять залов, в которых находятся максимальное количество французских художников-постимпрессионистов. Сформировать список картин, написанных между 1750-1850гг. итальянскими и испанскими художниками.
  44.  В деканате содержится следующая информация о студентах: ФИО, дата и год рождения, место проживания, место проживания родителей, группа, номер зачетной книжки, успеваемость по предметам. Сформировать список студентов, проживающих в общежитии и принимающих участие в голосовании, и определить 15 студентов пятого курса, имеющих наибольший средний балл.
  45.  Имеются следующая информация о микросхемах (ИС) на складе: система ИС(ТТЛ, МОП, КМОП и т.п.), тип ИС, количество, потребляемая мощность, страна-изготовитель, год выпуска, оптовые и розничные цены, гарантийный срок эксплуатации. Необходимо составить список ИС с потребляемой мощностью менее 100мВт, полученных из Тайваня, а также список процессоров, гарантийный срок которых уже истек.
  46.  В фирме, выполняющей ремонт компьютеров, имеются следующие сведения о проведенной работе: заказчик, его адрес, конфигурация компьютера(процессор, тип м/п, объем памяти, тип и частота процессора), вид ремонта, стоимость ремонта, дата поступления в ремонт и плановый срок исполнения, дата окончания ремонта, послеремонтный гарантийный срок. Сформировать сведения о задержках выполнения ремонта материнских плат VIA в заданном месяце. Определить количество гарантийных ремонтов, связанных с заменой видеокарт. Сформировать таблицу сведений о проведенном ремонте для заданной конфигурации компьютера.
  47.  Итоговые результаты соревнований биатлонистов содержит следующие сведения: ФИО, страна, время, количество штрафных очков за промахи(общее, стрельба стоя и лежа). Сформировать список из 20 спортсменов, бежавших наиболее быстро, и список спортсменов скандинавских стран, допустивших наибольшее число промахов.
  48.  В бюро по обмену квартир используется следующая информация: ФИО, район, улица, номер дома, номер квартиры, количество комнат, общая площадь, полезная площадь. Сформировать список трехкомнатных квартир с общей площадью не менее 80м2 и полезной площадью не менее 60м2. Определить 10 наибольших по площади квартир в Ворошиловском районе.
  49.  В приемной комиссии университета используется следующая информация: факультет, специальность, количество поданных заявлений абитуриентов, план приема, количество абитуриентов, сдавших экзамены. Определить конкурс по каждой специальности до и после сдачи экзаменов, общий конкурс по университету. Сформировать список специальностей, связанных с изучением информационных технологий, конкурс по которым составил после сдачи экзаменов 3-5 человек на место.
  50.  В университете составлено расписание занятий студентов в следующем виде: день недели, номер пары, шифр группы, название дисциплины, аудитория, фамилия преподавателя, вид занятий(лекция, лабораторная работа, практическое занятие). Сформировать список групп, в которых в заданный день на заданной паре проводятся занятия по высшей математике. Определить 5 наиболее загруженных аудиторий заданного корпуса.


Приложение 2.

Язык SQL – (структурированный язык запросов)

Соглашения

<>

Заключаются обязательный элемент синтаксиса. Текст внутри характеризует его, но не описывает его синтаксис

[]

Заключает один или несколько необязательных элементов, разделенных символом | , из которых нужно выбрать один либо ни одного элемента синтаксиса.

{}

Заключают несколько элементов, разделенных / из которых нужно выбрать точно один.

Элемент может повторяться несколько раз

, …

Элемент может повторяться несколько раз, разделяясь запятыми.

Все остальные элементы синтаксиса ( : () и др.) обязательны при записи синтаксиса конструкций.

Инструкции SQL можно располагать на нескольких строках, но не разрывая лексем языка.

Язык SQL – набор инструкций:

SELECT – отбор строк (записей) и столбцов (полей) из таблицы (отношений) или логических таблиц (результат выполнения запроса выборки).

DELETE – Удаление одной или нескольких строк из таблицы.

INSERT – Выставка одной или нескольких строк в таблицы

SELECT INTO – Создание новой таблицы со значениями из других таблиц.

UPDATE – обновление выборки столбцов таблицы.

Команда SELECT

Выполняет реляционные операции отбора и объединения для создания логических таблиц на основе других таблиц.

Синтаксис

SELECT [ALL | DISTINCT | DISTINCTROW | TOP число [PERSENT]] список выбора 

FROM {{имя таблицы [[AS] псевдоним]} | 

             <таблица объединения>}, 

[WHERE <условие отбора>]

[CROUP BY <имя столбца>]

[HAVING <условие отбора>]

[UNION [ALL] <инструкция выбора>]

[ORDER BY {<имя столбца> [ASC | DESC]}, …]

[IN <имя БД> [<строка подключения источника данных>]]

[WITH OWNERACCESS OPTION];

Каждая инструкция заканчивается ;

После ключевого слова SELECT следует выражение, определяющее список полей и набор записей, которые образуют логическую таблицу.

ALL – по умолчанию.

DISTINCT – все записи, удовлетворяющие условия отбора, исключая дублирующиеся  (устраняет избыточность).

DISTINCTROW(Расширение MICROSOFT) по умолчанию. Отбор только тех строк, в которых конкатенация первичных ключей из всех таблиц, участвующих в формировании выходных столбцов являются уникальными. Выходной набор данных может содержать дублирующиеся записи,  но каждая из них получается из уникальной комбинации строк базовых таблиц.

TOP число – первые N записей (число = N).

TOP число [PERSENT]] – первые N %.

Если не определена опция ORDER BY  - опция TOP может не иметь смысла, т.е. если не стоит упорядочивание.

Список выбора – определяет поля базовых таблиц, которые попадут в результирующую таблицу.

Синтаксис списка выбора:

{* | {выражение [AS имя столбца выходной таблицы]

     | имя таблицы.*

     | имя запроса выборки.*

     | псевдоним.*},…}

* - все поля базовых таблиц в выходную таблицу

* или «все»

имя таблицы.* - все поля конкретной таблицы

имя запроса выборки.* - все поля полученной таблицы в результате запроса выборки.

псевдоним.* - все поля таблицы или запроса, которому присвоен данный псевдоним в опции FROM.

Выражение [+/-]{функция/(выражение) | литерал | имя столбца

                               [{ + | - | * | / | ^ | MOD | &}

                             {функция | (выражение) |  литерал | имя столбца }]

Функция – одна из встроенных стоговых функций языка SQL.

AVG – среднее арифметическое

COUNT – подсчет числа строк

MAX – нахождение максимума с столбце

MIN – нахождение минимума с столбце

SUM – суммирование

ПРИМЕРЫ:

Пример использования псевдонимов:

SELECT a.[Код студента], a.[Дата рождения], a.[Код группы]

FROM [Данные о студентах] AS a;

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

SELECT COUNT(*)

FROM [Данные о студентах] AS a;

Определение группы с максимальным кодом:

SELECT MAX(a.[Код группы])

FROM [Данные о студентах] AS a;

Очередность  полей результирующей таблицы соответствует очередности полей в списке выбора.

Опция FROM

Определение таблицы и/или запросы выборки, которые служат источником данных для созданного запроса и назначить им, если необходимо, альтернативные имена (псевдонимы).

Синтаксис

{{имя таблицы [[AS]псевдоним ]|

   имя запроса [[AS]псевдоним]}|

   таблица объединения}, …

Таблица объединения – таблица, построенная на основе объединения других таблиц, запросов – выборки, таблиц объединения.

Синтаксис таблиц объединения:

{таблица объединения [[AS] псевдоним] |

 имя  запроса выборки [[AS] псевдоним] |

 таблица объединения}

Объединение таблиц

 Опции {INNER | LEFT | RIGHT} JOIN  

      имя таблицы [[AS]псевдоним ]|

    имя запроса [[AS]псевдоним]}|

    таблица объединения}

INNER – генерирует внутреннее объединение. Результат: все строки из базовых таблиц или запросов выборки, удовлетворяющие условию объединения.

LEFT – левое объединение. Все строки из левой таблицы или запроса, объединенного со строками правой таблицы или запроса, для которой выполняется условие объединения. Если в правой таблице соответствующих строк не найдено то в качестве незаполненных значений полей используется значение NULL.

RIGHT – правое объединение. Все строки правой таблицы или запроса, объединенного со строками левой таблицы или запроса, для которой выполняется условие объединения. Если в левой таблице соответствующих строк не найдено то в качестве незаполненных значений полей используется значение NULL.

Условное объединение – логическое выражение со значениями (истина/ложь) обычно операции сравнения значений полей левых и правых таблиц.

Замечание:

Если в FROM таблицы или запросы выборки перечисляются через запятую, то генерируется декартово (прямое) произведение двух таблиц, т.е.  каждая строка первой таблицы по очереди объединения со всеми строками правой таблицы.

ПРИМЕРЫ:

Задача 1. Выдать список студентов которые уже распределены по группам

SELECT [Данные о студентах].[Код студента], [Данные о студентах].[Код группы], [Группы].[Группа]

FROM Группы INNER JOIN [Данные о студентах] ON [Группы].[Код группы]=[Данные о студентах].[Код группы];

Задача 2. Выдать список всех групп и студентов которые уже распределены по группам

SELECT [Данные о студентах].[Код студента], [Данные о студентах].[Код группы], [Группы].[Группа]

FROM Группы LEFT  JOIN [Данные о студентах] ON [Группы].[Код группы]=[Данные о студентах].[Код группы];

Задача 3. Выдать список  студентов с указанием  групп (если студенты не распределены по группам, они включатся в этот выходной результат)

SELECT [Данные о студентах].[Код студента], [Данные о студентах].[Код группы], [Группы].[Группа]

FROM Группы RIGHT  JOIN [Данные о студентах] ON [Группы].[Код группы]=[Данные о студентах].[Код группы];

Опция WHERE

Синтаксис:

WHERE <спецификация выбора строк>

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

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

Синтаксис предиката:

[NOT] предикат [{AND | OR | XOR |  EQV | JMP} предикат]

Предикат сравнения:

  1.  Выражение { < | <= | > | >= | <> | = }

Пример:

Выдать список студентов с названием групп 11101 (ОКН00а)

SELECT [Код студента], [Код группы]

FROM [Данные о студентах]

WHERE [Код группы]=11101;

SELECT [Студенты].[ФИО], [Группы].[Группа]

FROM Группы INNER JOIN (Студенты INNER JOIN [Данные о студентах] ON [Студенты].[код студента]=[Данные о студентах].[Код студента]) ON [Группы].[Код группы]=[Данные о студентах].[Код группы]

WHERE [Группы].[Код группы]=11101;

  1.  Предикат BETWEEN

Синтаксис:

Выражение 1 [NOT] BETWEEN выражение 2 AND выражение 3

Определяет, принадлежит (не принадлежит) значение выражения 1 отрезку выр.2 выр.3

Типы выражение должны быть совместимы

BETWEEN эквивалентна запись через сравнение:

A BETWEEN  B  AND  C

A>= B AND A <=C

A  NOT BETWEEN  B  AND  C

A< B OR A >C

Результат считается неопределенным, если одно из выражений предикатов сравнения или BETWEEN значение NULL.

Пример:

Выдать список студентов групп ПО (коды групп 11500-111600)

SELECT [Студенты].[ФИО], [Группы].[Группа]

FROM Группы INNER JOIN (Студенты INNER JOIN [Данные о студентах] ON [Студенты].[код студента]=[Данные о студентах].[Код студента]) ON [Группы].[Код группы]=[Данные о студентах].[Код группы]

WHERE [Группы].[Код группы]>11500 AND [Группы].[Код группы]<11600;

SELECT [Студенты].[ФИО], [Группы].[Група]

FROM Группы INNER JOIN (Студенты INNER JOIN [Данные о студентах] ON [Студенты].[код студента]=[Данные о студентах].[Код студента]) ON [Группы].[Код группы]=[Данные о студентах].[Код группы]

WHERE [Группы].[Код группы] BETWEEN 11500 AND 11600;

  1.  Предикат IN

Определяет, равно ли некоторое значение одному из значений в списке значений или возвращаемых подчиненных запросов.

Синтаксис:

Выражение  [NOT] IN {выражение | (подчиненный запрос) | ({литерал}, …)

         

Выражение1 IN выражение2

эквивалентно

       Выражение1 =  выражение2

 

Выражение1 IN (подчиненный запрос)

эквивалентно:

Выражение1 ANY (подчиненный запрос)

Пример:

Выдать список студентов занимающихся в группах  ОКН00а и ОКН01б (коды групп 11101, 111105)

SELECT [Студенты].[ФИО], [Группы].[Группа]

FROM Группы INNER JOIN (Студенты INNER JOIN [Данные о студентах] ON [Студенты].[код студента]=[Данные о студентах].[Код студента]) ON [Группы].[Код группы]=[Данные о студентах].[Код группы]

WHERE [Группы].[Код группы] In (11101,11105);

  1.  Предикат LIKE

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

Синтаксис:

Имя столбца [NOT] LIKE  образец

В образце возможно использование символов шаблона.

Символ шаблона

Назначение

?

Любой одиночный символ

*

Любое, включая нулевое, количество произвольных символов

#

Любая одиночная цифра

[…]

Символ из списка

Символы ? * #  могут сравниваться как символы из списка.

A LIKE *   - отберет все

A LIKE *[*]*  - те строки в которых есть символ *

A LIKE *[ [ ]*  - те строки в которых есть символ [

Строковые и прописные буквы не различаются.

Пример:

Выдать список студентов фамилии которых начинаются на буквы (А, В, К, Т)

SELECT [Студенты].[ФИО]

FROM Студенты INNER JOIN [Данные о студентах] ON [Студенты].[код студента]=[Данные о студентах].[Код студента]

WHERE ((([Студенты].[ФИО]) LIKE "[А,В,К,Т]*"));

  1.  Предикат NULL

Определяет, что значение выражения равно NULL

Синтаксис:

Выражение IS [NOT] NULL;

Пример:

Выдать список студентов которые распределены по группам

SELECT [Студенты].[ФИО], [Группы].[Группа]

FROM Студенты INNER JOIN (Группы INNER JOIN [Данные о студентах] ON [Группы].[Код группы]=[Данные о студентах].[Код группы]) ON [Студенты].[код студента]=[Данные о студентах].[Код студента]

WHERE [Группы].[Группа] IS NOT NULL;

  1.  Предикаты SOME, ANY, ALL

Сравнивают значение выражения со всеми значениями таблицы, возвращенного подчиненным запросом.

Синтаксис:

Выражение {< / <= …} {SOME | ANY | ALL} (подчиненный запрос)

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

Если SOME или ANY (они эквивалентны), то дает истину, если значение выражения совпадает хотя бы с одним значением из столбца, возвращаемого подчиненным запросом.

  1.  Предикат EXISTS

Проверяет существование, по крайней мере одной строки, которая удовлетворяет условиям отбора в подчиненном запросе.

Предложение ORDER BY

Задает порядок расположения строк в выходной таблице.

Синтаксис:

ORDER BY {{имя столбца | номер столбца [ASC | DESC]},…}

ASCпо умолчанию, в порядке возрастания

DESC – в порядке убывания.

– столбца порядковый номер столбца в списке выбора, начинается с 1.

Замечание:

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

Если в столбце, по которому идет упорядочивание, присутствуют, NULL значения – то соответствующие строки группируются, либо в начале списка, либо в конце, в зависимости от диалекта SQL.   

Пример:

Выдать список студентов упорядоченный в обратном порядке по группам, а по фамилиям в естественном порядке

SELECT Группы.Группа, Студенты.ФИО

FROM Студенты INNER JOIN (Группы INNER JOIN [Данные о студентах] ON Группы.[Код группы] = [Данные о студентах].[Код группы]) ON Студенты.[код студента] = [Данные о студентах].[Код студента]

ORDER BY Группы.Группа DESC , Студенты.ФИО;

Предложение GROUP BY

Позволяет определить подмножество значений отдельного поля в терминах другого поля и применить агрегатные функции к полученному множеству.

(формирует группы строк имеющие одинаковые значения данного столбца (столбцов))

Синтаксис:

GROUP  BY имя столбца

HAVING

Определяет группы строк, сформированные предложением GROUP  BY, которые будут  включены в выходной набор данных в случае истинного условия отбора.

Синтаксис:

HAVVING <условие отбора группы строк>

Условие отбора единое для всей группы строк.

IN

Определяет источник данных базовых таблиц запроса указанных в FROM  и распространяется на все таблицы, в том числе и в подчиненных запросах.  (ссылка возможна только на 1 базу банных).

Внешним источником данных  могут служить:

БД Microsoft Access файлы в формате dBASE и FoxPro, Paradox или любой БД, поддерживающий стандарт обмена открытого доступа к данным (ODBC).

Синтаксис для MS ACCESS:

IN «Диск\путь\имяБД»

Синтаксис для подключенных к ODBC :

IN [ODBC; DATABASE =  <БД по умолчанию>, UID = Пользователь PWD = пароль, DSN = имя пользователя]

Квадратные скобки элементы синтаксиса.

Если какой-либо из параметров подключения, кроме первого опущен, то по стандарту ODBC выведется диалоговое окно, с соответствующем запросом.

WITH OWNERACCESS OPTION

Следует включать в инструкцию для конкретного выполняемого запроса в корпоративной БД, когда регламентированы права доступа к таблицам.

Например, полный доступ к таблицам закрыт, но разрешено выполнять некоторые типы запросов к этим таблицам.

UNION

Служит для объединения выходных данных двух или более инструкций SELECT в единстве множества строк и столбцов.

Синтаксис:

Инструкция SELECT

UNION [ALL]

Инструкция SELECT

[UNION [ALL]

Инструкция SELECT]…

ORDER BY {{имя столбца | номер столбца [ASC | DESC]},…}

Если указана опция ALL, то в объединение объединяются все строки, в том числе и повторяющиеся (по умолчанию повторяющиеся строки убираются). Упорядочивание производится по всей объединенной таблице.

Все логические таблицы, участвующие в объединении, должны быть согласованы по количеству столбцов и по их типу с учетом порядка расположения. (для числовых – тип и размер, для символьных – количество выделенных символов, одинаковые размещения (запрет на NULL значения)).

Замечание:

Имена выходным столбцам не присваиваются. (расширение MS – имена из  первой SELECT)

Пример:

Выдать общий список мастеров и студентов

SELECT  Студенты.ФИО

FROM Студенты

UNION SELECT Мастера.Мастера

FROM  Мастера;

Инструкция SELECTINTO

Запрос на создание таблицы. Создает новую таблицу со значениями, полученными на основе запроса выборки.

Синтаксис:

SELECT [ALL | DISTINCT | DISTINCTROW | TOP число [PERSENT]] список выбора INTO <имя создаваемой таблицы> IN [спецификация источника данных]

-\\- (тело инструкции SELECT)

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

Если таблица с таким именем уже существует в базе данных, то выводится диалоговое окно, с запросом на замену.

Если таблица создается в другой базе данных в спецификации источника данных.

Инструкция DELETE

Удаляет одну или несколько строк в таблице или запросе.

Синтаксис

DELETE [*| <имя таблицы>.*]

FROM {{имя таблицы [[AS] псевдоним]} | <таблица объединения>}, 

[WHERE <условие отбора>]

[IN <спецификация доступа к внешней базе данных>]]

Записи могут удаляться только в одной таблице, если в FROM определена только одна таблица, то после ключевого слова DELETE можно ничего не ставить.

Если в FROM определены несколько таблиц, то записи могут удаляться, только из той таблицы, которая находиться со стороны «многие» связи 1 ко многим или с любой стороны связи 1 к 1, имя этой таблицы указывается после ключевого слова DELETE,

Если WHERE отсутствует, то удаляются все записи, а если присутствует, то только те, которые прошли условие отбора.

Инструкция INSERT

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

Синтаксис

INSERT INTO  <имя таблицы> (<имя столбца>,…)

{VALUES ({<литерал>},…)|<инструкция выбора SELECT>}

[IN <спецификация доступа к внешней базе данных>]]

Если INSERT используется с VALUES, то добавляется одна строка, если с инструкцией выбора, то добавляется столько строк, сколько инструкция выбора возвратит.

Если список столбцов после имени таблицы, отсутствует, то новые значения заносятся во все столбцы таблицы в порядке, определенном при ее создании, иначе в порядке, определенном в списке столбцов.

Если некоторые столбцы таблицы отсутствуют в списке столбцов, и для них не было назначено значение по умолчанию, то в них заносится NULL.

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

Список столбцов (список всех столбцов таблицы) при его отсутствии должен быть согласован по количеству и типу со списком <литералов> в случае VALUES или со списком выбора в инструкции выбора.

Инструкция UPDATE

Запрос на обновление в указанной  таблице или запросе.

Синтаксис

UPDATE  <имя таблицы> [[AS] псевдоним] | 

                 <имя запроса> [[AS] псевдоним] |

                 <таблица объединения>}

SET <имя столбца>={[<выражение | NULL>]},…

[WHERE <условие отбора>]

[IN <имя БД> [<строка подключения источника данных>]]

Обновляются значения в столбцах перечисленные в SET для всех записей, если WHERE отсутствует или только для тех, которые прошли условие отбора.

Если в инструкции используется несколько таблиц, то изменения можно вносить, только в столбцы одной из них, которая стоит со стороны «многие», при «1 ко многим» или любое со стороны «1 к 1»

Новые значения должны быть согласованы по типу с соответствующими столбцами.


Dish

Справочник блюд

Поле

Тип

Назначение

Ограничение

IdDish

N 3

Код блюда

Incr

Name

C 20

Блюдо

IdKind

N 2

Код вида

Price

N 6.2

Цена порции

> 0

KindDish

Справочник видов блюд

Поле

Тип

Назначение

Ограничение

IdKind

N 2

Код вида блюда

Incr

Name

C 20

Вид блюда

Sale

Продажи

Поле

Тип

Назначение

Ограничение

IdDish

N 3

Код блюда

Date

D2

Дата

Qty

N 6.2

Количество порций

>=0

Struct

Калькуляции

Поле

Тип

Назначение

Ограничение

IdDish

N 3

Код блюда

IdProd

N 4

Код продукта

Qty

N 6.3

Расход

> 0

Product

Справочник продуктов

Поле

Тип

Назначение

Ограничение

IdProd

N 4

Код продукта

Incr

Name

C 15

Продукт

IdUnit

N 1

Код ед.изм

IdSup

N 3

Код поставщик

Unit

Справочник единиц измерений

Поле

Тип

Назначение

Ограничение

IdUnit

N 1

Код ед.изм

Incr

Name

C 20

Ед.  измер.

Supplier

Справочник поставщиков

Поле

Тип

Назначение

Ограничение

IdSup

N 3

Код

Incr

Name

C 30

Поставщик

IdAr

N 2

Код располож.

Area

Справочник расположения

Поле

Тип

Назначение

Ограничение

IdAr

N 2

Код располож.

Incr

Name

C 20

Располо- жение

 IdKind

 Name

  IdDish

  Name

  Price

  IdKind

  IdDish

  Date

  Qty

  IdDish

  Qty

  IdDish

  IdProd

  Qty

  IdProd

  Name

  IdUnit

  IdUnit

  Name


 

А также другие работы, которые могут Вас заинтересовать

77599. Реферат. Написание реферата 96.5 KB
  Модели защиты рефератов Как готовить рефераты с помощью интернета Общие требования к реферату методические рекомендации для студента Содержание и оформление разделов реферата Варианты оформления различных видов произведений печати в списке литературы реферата...
77601. Валюта жүйесінің мәні және дамуы 41.56 KB
  Қазақстанның әлемдік экономикалық қатынастарда басқа елдермен сауда, экономикалық қарым-қатынасқа түсуі үшін, шетелдермен экспорт пен импорта, әлемдік капитал ағымдарының іске асуына және шетел валюталарының еркін айналымын ұйымдастыруда...
77605. Лефортово в 20 веке 1.71 MB
  История столичного района Лефортово (бывший Калининский район г. Москвы) в высшей степени поучительна. Как в фокусе здесь сходятся равнодействующие всех влияний, складывающих национальный облик Россиянина. Как зеркало отражает она развитие страны, внешнее и внутреннее изменение города.