Предикаты, использующие выборку. Вложенные запросы

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

Варианты задания.

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

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

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

Варианты задания.

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

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

Определите атрибуты продавцов, продавших хотя бы одно наименование из товаров, проданных данным продавцем.

Кванторные предикаты. Определите атрибуты объектов, для которых категория связанных с ними объектов (не)пуста.

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

Определите атрибуты продавцов, (не) продавших за последний месяц дорогие товары.

(Связанные запросы).

(Внешние) объединения.Определите совпадающие атрибуты объектов из разных таблиц.

Вариант задания. Определите фамилии и возраст покупателей и продавцов, проживающих в данном городе (например, Казани).

МОДИФИКАЦИЯ ТАБЛИЦ

Удалите из таблиц заданные категории объектов.

Вариант задания.

Удалите все сведения о товарах, не пользовавшихся спросом в последнее время (скажем, год).

Удалите сведения о продавцах, не осуществляших никаких продаж за последнее время.

Удалите сведения о покупателях, не делавших покупок за последнее время.

Измените значения атрибутов объектов заданной категории.

Тема 3. Задачи-многоходовки. Представления и транзакции.

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

n ФАМИЛИЯ (30 символов)

n ПАСПОРТ - № паспорта, 10 символов (очевидно, уникальный для каждого человека)

n МАТЬ - № паспорта матери (NULL, если нет сведений)

n ОТЕЦ - № паспорта отца (NULL, если нет сведений)

n СУПРУГ - № паспорта жены/мужа (NULL, если нет сведений)

n ДАТА - дата рождения (тип дата)

n ДОХОД - ежемесячный доход (зарплата, стипендия и т.п.), вещественное 99.999.999,99

n ГОРОД - город проживания (30 символов)

n НАЛОГ - сумма подоходного налога, в % (целое)

Кроме того, в информацию о студентах дополнительно включаются сведения

n ВУЗ - сокращенное название ВУЗа, 10 символов - например, ‘КГУ’, ‘КГМУ’ и т.п.

n ПОЛ - один из символов ‘М’,’Ж’

БД включает таблицы СТУДЕНТЫ, ОТЦЫ и МАТЕРИ, включающую информацию о студентах и их родителях, соответственно.

ПРИМЕЧАНИЕ. Как всегда, считаем, что допустимо применение имен на кириллице.

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

- Выдать упорядоченный по фамилиям список живущих в Казани, Москве и Самаре родителей, с указанием - мать это или отец, чей доход не превышает 300 руб.

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

- Выбрать всю информацию об отцах, чьи доходы выше среднего по городу, в котором они живут.

- Выдать список живущих вне Казани матерей, имеющих не менее - двух сыновей-студентов, учащихся в Казани.

- Выбрать всю информацию об студентах, чьи доходы выше среднего для казанских студентов.

"Многоходовки" – т.е. задачи, решение которых не реализуется легко единственной командой SQL проще решать, используя представления, например:

- Удалить информацию о студентах ВУЗА 'АГУ' а также их родителях - в случае, если у тех нет других детей-студентов (вариант посложнее - включить в число родителей студентов их бабушек и дедушек)

Вариант решения:

- запомнить студентов нужного ВУЗа и ссылки на их отцов,

- запомнить отцов, у которых все дети (если вообще есть таковые) учатся в нужном ВУЗе

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

- удалить информацию о студентах нужного ВУЗа

-Снизить на 5% налог родителям, имеющим более 3 детей-студентов, обучающихся в Казани

Вариант решения:

- Разбив казанских студентов на группы детей одних родителей, отобрать и запомнить ссылки на тех отца и мать, для которых количество их детей больше 3

- Изменить записи о матерях, ссылки на которые были запомнены на первом шаге,

- Изменить записи об отцах, ссылки на которые были запомнены на первом шаге.

-Повысить на 50 руб. стипендию (т.е. доход) студентов, у которых нет хотя бы одного из родителей либо совокупный доход родителей не превышает 1000 руб.

Вариант решения:

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

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

- Повысить на 5% налог отцам, у которых доход жены не ниже среднего для матерей, а совокупный доход детей превышает 1000 руб.

Вариант решения:

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

- Запомнить ссылки на матерей, чей доход больше среднего по матерям

- Изменить соответственно информацию об отцах, попавших в первый список, у которых жена попала во второй список

Тема 4. Проектирование "реальной" БД.

Информационная система "Сборочное предприятие".

НЕФОРМАЛЬНАЯ МОДЕЛЬ

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

Предприятие состоит из несколькихподразделений - цехов и участков,в которых трудятся работникиразличных профессий. Работники осуществляют, в соответствии с производственным планом предприятия, сборочныеи иныеоперациипо изготовлению готовых изделий из компонент. Компонентами могут служить изделия собственного производства и исходные материалы (сырье). Все операцииосуществляются в согласии с установленными нормам затрат труда и расхода материалов. Оплата труда работников производиться согласно их выработке и установленным тарифам. Исходные материалы поступают на склады предприятия от поставщиков в соответствии с заключенными с ними договорам. Готовые изделия отгружают со склада покупателямсогласноихзаказам.

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

ВЗАИМОСВЯЗЬ ПОНЯТИЙ

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

- Одно ПОДРАЗДЕЛЕНИЕ может включать в себя другие ПОДРАЗДЕЛЕНИЯ (цеха состоят из участков)

- Каждый РАБОТНИК обладает некоторой ПРОФЕССИЕЙ

- У каждого РАБОТНИКа (кроме директора) имеется непосредственный начальник (другой РАБОТНИК)

- МАТЕРИАЛЫ участвуют в ОПЕРАЦИЯХ в качестве компонент

- ИЗДЕЛИЯ также участвуют в ОПЕРАЦИЯХ в качестве компонент

- ИЗДЕЛИЯявляются также результатом ОПЕРАЦИЙ

- ОПЕРАЦИИ производятся некоторым УЧАСТКОМ (их исполнителем )

- РАСХОД всегда связан с некоторым МАТЕРИАЛОМ

- РАСХОД материала осуществляется при выполнении некоторой ОПЕРАЦИИ

- РАСХОД материала осуществляется некоторым участком - исполнителем операций

- МАТЕРИАЛЫ поставляются по ДОГОВОРУ

- ДОГОВОР заключается с ПОСТАВЩИКОМ

- ПОСТАВКА осуществляется ПОСТАВЩИКОМ

- ПОСТАВКА осуществляется на СКЛАД

-

МАТЕРИАЛЫ( исходные материалы и детали производства)

- уникальный код материалы;

- наименование;

- характеристика;

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

- цена за единицу.

ИЗДЕЛИЯ ( готовые изделия и сборочные единицы собственного производства)

- уникальный код изделия;

- наименование;

- характеристика;

- цена

СБОРКА (пооперационный процесс изготовления изделий и сборочных единиц из более простых компонент)

- код компоненты

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

- номер операции процесса сборки;

- используемое количество на операцию;

- номер участка исполнителя;

РАСХОД (нормы расхода материалов )

- код сборочной единицы или готового изделия;

- код компоненты (используемой при изготовлении детали или в монтажных работах при сборке изделия);

- номер операции;

- номер цеха (исполнителя работ);

- номер участка;

- единица измерения;

- норма расхода материала.

ЗАТРАТЫ ( нормы затрат труда)

- код изделия ;

- номер операции;

- номер цеха (исполнителя работ);

- номер участка;

- код профессии рабочего;

- код условий труда и тарифный разряд работы;

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

- время штучное, в мин.

ТАРИФЫ

- уникальный код условий труда и тарифный разряд работы;

- часовая тарифная ставка, в рублях

ПРОФЕССИИ

- уникальный код профессии;

- наименование профессии.

ПОДРАЗДЕЛЕНИЯ

- номер цеха;

- наименованиецеха или участка.

ПЛАН (производственный план предприятия)

- код изделия;

- распределение плана по всем месяцам - выпуск , в штуках, в январе, феврале и т.д.;

- дата начала действия плана.

ДОГОВОРЫ ( на поставку компонент)

- код поставщика;

- уникальный номер договора;

- код компонента - материала или покупной детали;

- единица измерения;

- план поставки на год, в шт.;

- распределение плана по всем месяцам - поставки в январе, феврале и т.д.;;

- дата начала действия договора.

РАБОТНИКИ (личный состав, штат предприятия)

- номер цеха;

- табельный номер рабочего;

- код профессии;

- разрядрабочего;

- часовая тарифная ставка;

- семейное положение;

- фамилия с инициалами.

ВЫРАБОТКА(учет выработки работников)

- номер цеха;

- номер участка;

- код изделия;

- номер операции;

- табельный номер работника;

- количество годных деталей;

- количество бракованных деталей;

- процент оплаты брака;

- дата выполнения работ.

ПОСТАВЩИКИ

- уникальный код поставщика;

- наименование поставщика;

- адрес поставщика.

ПОСТАВКА компонент

- номер склада;

- код поставщика;

- код компоненты;

- единица измерения;

- количество ;

- дата поступления;

- уникальный номер документа.

ОТГРУЗКА готовой продукции

- номер склада;

- код покупателя;

- код готового изделия;

- единица измерения;

- количество;

- дата отгрузки;

- уникальный номер документа.

ПОКУПАТЕЛИ

- уникальный код покупателя;

- наименование;

- город;

- почтовый адрес.

СКЛАДЫ

- номер склада;

- фамилия материально ответственного лица;

- код детали - компоненты или изделия;

- единица измерения;

- количество, имеющееся на складе;

- дата последней операции.

ЗАРПЛАТА (бухгалтерский учет начисления и удержания по зарплате)

- табельный номер работника;

- сумма начисления;

- сумма удержания;

- дата выдачи.

ЗАКАЗЫ (договоры на отгрузку готовой пpодукции покупателям)

- код покупателя;

- уникальный номер заказа;

- код изделия;

- единица измерения;

- план поставки на год, в шт.;

- распределение плана по всем месяцам - поставки вянваре, февралеи т.д.;

- дата начала действия договора.

Упражнение. Постройте формальную модель предприятия в форме базы данных, определив таблицы КОМПОНЕНТЫ, ИЗДЕЛИЯ и т.д., используя выделенные слова (код, наименование, характеристика, единица, цена и т.д.) в качестве имен полей (здесь и далее мы для удобства используем кириллические имена произвольной длины; если ваша СУБД не поддерживает соответствующие идентификаторы, используйте латинскую транскрипцию и сокращения). Выясните, какие поля (или группа полей) являются первичными и внешними ключами. Правила целостности и корректности значений полей и записей таблиц (в частности, допустимость неопределенных значений) определите самостоятельно, исходя из содержательного смысла таблиц и отношений.

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

Заказчик - директору: "По нашему договору №…, от …. ваше предприятие недопоставило … изделий "…". Если Вы не поставите требуемые изделия в течении … дней, мы обратимся в суд"

Директор - заказчику "Минутку-минутку, сейчас уточним…У меня почему-то стоит другая дата…"

Директор - кладовщику "Сколько у нас на складе изделий "…"? Не хватает?"

Директор - начальнику цеха "Сможем в течении … дней собрать недостающие .. штук изделий "…"?

Далее следуют обращения

- начальника цеха на склад в поиске нужных компонент,

- начальника цеха - к директору с просьбой повысить тарифные расценки за срочную работу,

- директора - в бухгалтерию, с вопросом о финансовых возможностях предприятия выполнить эту просьбу,

- директора к поставщикам с просьбой срочно поставить недостающие компоненты,

- и т.д. - импровизируйте!

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

ЛИТЕРАТУРА.

1) М.Нагао, Т.Катаяма, С.Уэмура. Структуры и базы данных – М.,Мир, 1986 – 196 с.

2) М.Грабер. Введение в SQL

3) А.Горев. Visual FoxPro 5.0. Книга для программистов – М., ТОО “Эдэль”, 1997 – 552 с.

4) М.Антонович, Visual FoxPro для Windows, BINOM Publishers, 1996 – 688 с.

ЗАМЕЧАНИЯ - СДЕЛАТЬ ДО ИЗДАНИЯ (ТВЕРДОЙ КОПИИ)

1) Теоретический материал желательно проиллюстрировать схемами.

2) Пример в первой теоретической части повторить во второй – уже в синтаксисе SQL

ПОЖЕЛАНИЯ - СДЕЛАТЬ В СЛЕДУЮЩЕМ ИЗДАНИИ.