 |
Delete from Dad where Id in (Select Id from Dad1) and Id in (Select Id from Dad2)
|
|
|
|
4. …
5. Delete from Students where Institute=’АГУ’
6. Замечание. Стандарт ANSI SQL запрещает в командах модификации подзапрос – в том числе, неявный (через представление) - к модифицируемым таблицам. Если реализация следует в этом стандарту – прибегнуть к курсорам - Select Distinct Dad from Students where Institute=’АГУ’ into cursor Dad1 etc.
- Снизить на 5% налог родителям, имеющим более 3 детей-студентов, обучающихся в Казани
Вариант решения:
- Разбив казанских студентов на группы детей одних родителей, отобрать и запомнить ссылки на тех отца и мать, для которых количество их детей больше 3
- Изменить записи о матерях, ссылки на которые были запомнены на первом шаге,
- Изменить записи об отцах, ссылки на которые были запомнены на первом шаге.
1. CreateView Parents as Select Mum, Dad,Count(*) from Students Group BY Mum, Dad Having Count(*)>3
2. Update Mum set Tax= Tax-5 where Id in (Select Mum from Parents)
3. Update Dad set Tax= Tax-5 where Id in (Select Dad from Parents)
4.
- Повысить на 50 руб. стипендию (т.е. доход) студентов, у которых нет хотя бы одного из родителей либо совокупный доход родителей не превышает 1000 руб.
Вариант решения:
- Подсчитать и запомнить совокупный доход всех родителей (вместе со ссылками на отца и мать), если он не более заданной суммы,
- Изменить соответственно информацию о студентах, для которых ссылки на родителей либо пусты, либо попали в запомненный список
1. Create View LowIncome as Student.Id as Id, Dad, Mum, Dad.Income+Mum.Income from Mums,Dads,Students where Student.Mum=Mum.Id and Student.Dad=Dad.Id and Dad.Income+Mum.Income<=1000
2. Update Students set Income=Income+50 where IsNull(Mum) or IsNull(Dad) or
Id in (Select Id from LowIncome)
- Повысить на 5% налог отцам, у которых доход жены не ниже среднего для матерей, а совокупный доход детей превышает 1000 руб.
Вариант решения:
- Сгруппировав по отцам, подсчитать совокупный доход студентов по группе, запомнить ссылки на отцов, если подсчитанный доход больше заданной суммы.
|
|
|
- Запомнить ссылки на матерей, чей доход больше среднего по матерям
- Изменить соответственно информацию об отцах, попавших в первый список, у которых жена попала во второй список
Create View HignIncomeDads as Select Sum(Income),Dad from Students GroupBy Dad Having Sum(Income) >1000
Create View HignIncomeMum as Select Id from Mum where Income>(Select AVG(Income) from Mum)
Create View HignIncomeParents as Select Distinct Dad,Mum from Students where Dad in (Select Dad from HighIncomeDad) and Mum in (Select Id from HighIncomeMum)
Update Dads set Tax=Tax+5 where Id in (Select Distinct Dad from HignIncomeParents)
Тема 4. Проектирование "реальной" БД.
Информационная система "Сборочное предприятие".
НЕФОРМАЛЬНАЯ МОДЕЛЬ
После тщательного изучения проблемной области и опроса экспертов - специалистов предприятия, вы обладаете следующей информацией о его структуре и функционировании.
Предприятие состоит из нескольких подразделений - цехов и участков, в которых трудятся работники различных профессий. Работники осуществляют, в соответствии с производственным планом предприятия, сборочныеи иные операции по изготовлению готовых изделий из компонент. Компонентами могут служить изделия собственного производства и исходные материалы (сырье). Все операцииосуществляются в согласии с установленными нормам затрат труда и расхода материалов. Оплата труда работников производиться согласно их выработке и установленным тарифам. Исходные материалы поступают на склады предприятия от поставщиков в соответствии с заключенными с ними договорам. Готовые изделия отгружают со склада покупателям согласноих заказам.
Упражнение. Попробуйте сами выделить базовые понятия, их свойства и взаимосвязи на основе неформального и неполного описания. Разумеется, такое выделение далеко не однозначно. Обоснуйте (защитите) свой вариант, сравнив его с предложенным ниже.
ВЗАИМОСВЯЗЬ ПОНЯТИЙ
|
|
|
Дополнительно, каждое из выделенных базовых понятий и процессов и их взаимосвязи более детально характеризуется в соответствующих справочниках и учетных книгах предприятия следующим образом:
- Одно ПОДРАЗДЕЛЕНИЕ может включать в себя другие ПОДРАЗДЕЛЕНИЯ (цеха состоят из участков)
- Каждый РАБОТНИК обладает некоторой ПРОФЕССИЕЙ
- У каждого РАБОТНИКа (кроме директора) имеется непосредственный начальник (другой РАБОТНИК)
- МАТЕРИАЛЫ участвуют в ОПЕРАЦИЯХ в качестве компонент
- ИЗДЕЛИЯ также участвуют в ОПЕРАЦИЯХ в качестве компонент
- ИЗДЕЛИЯ являются также результатом ОПЕРАЦИЙ
- ОПЕРАЦИИ производятся некоторым УЧАСТКОМ (их исполнителем)
- РАСХОД всегда связан с некоторым МАТЕРИАЛОМ
- РАСХОД материала осуществляется при выполнении некоторой ОПЕРАЦИИ
- РАСХОД материала осуществляется некоторым участком - исполнителем операций
- МАТЕРИАЛЫ поставляются по ДОГОВОРУ
- ДОГОВОР заключается с ПОСТАВЩИКОМ
- ПОСТАВКА осуществляется ПОСТАВЩИКОМ
- ПОСТАВКА осуществляется на СКЛАД
-
МАТЕРИАЛЫ (исходные материалы и детали производства)
- уникальный код материалы;
- наименование;
- характеристика;
- единица измерения
- цена за единицу.
ИЗДЕЛИЯ (готовые изделия и сборочные единицы собственного производства)
- уникальный код изделия;
- наименование;
- характеристика;
- цена
СБОРКА (пооперационный процесс изготовления изделий и сборочных единиц из более простых компонент)
- код компоненты
- код сборочной единицы или готового изделия, в которую входит данная компонента;
- номер операции процесса сборки;
- используемое количество на операцию;
- номер участка исполнителя;
РАСХОД (нормы расхода материалов)
- код сборочной единицы или готового изделия;
- код компоненты (используемой при изготовлении детали или в монтажных работах при сборке изделия);
- номер операции;
- номер цеха (исполнителя работ);
- номер участка;
- единица измерения;
- норма расхода материала.
ЗАТРАТЫ (нормы затрат труда)
- код изделия;
- номер операции;
- номер цеха (исполнителя работ);
- номер участка;
- код профессии рабочего;
- код условий труда и тариф ный разряд работы;
- дополнение - время на подготовку и заключение работы, в мин.;
|
|
|
- время штучное, в мин.
ТАРИФЫ
- уникальный код условий труда и тарифный разряд работы;
- часовая тарифная ставка, в рублях
ПРОФЕССИИ
- уникальный код профессии;
- наименование профессии.
ПОДРАЗДЕЛЕНИЯ
- номер цеха;
- наименование цеха или участка.
ПЛАН (производственный план предприятия)
- код изделия;
- распределение плана по всем месяцам - выпуск, в штуках, в январе, феврале и т.д.;
- дата начала действия плана.
ДОГОВОРЫ (на поставку компонент)
- код поставщика;
- уникальный номер договора;
- код компонента - материала или покупной детали;
- единица измерения;
- план поставки на год, в шт.;
- распределение плана по всем месяцам - поставки в январе, феврале и т.д.;;
- дата начала действия договора.
РАБОТНИКИ (личный состав, штат предприятия)
- номер цеха;
- табельный номер рабочего;
- код профессии;
- р азряд рабочего;
- часовая тарифная ставка;
- семейное поло жение;
- фамилия с инициалами.
ВЫРАБОТКА (учет выработки работников)
- номер цеха;
- номер учас тка;
- код изделия;
- номер операции;
- табельный номер работника;
- количество годных деталей;
- количество брак ованных деталей;
- процент оплаты брака;
- дата выполнения работ.
ПОСТАВЩИКИ
- уникальный код поставщика;
- наименование поставщика;
- адрес поставщика.
ПОСТАВКА компонент
- номер склада;
- код поставщика;
- код компоненты;
- единица измерения;
- количество;
- дата поступления;
- уникальный номер документа.
ОТГРУЗКА готовой продукции
- номер склада;
- код поку пателя;
- код готового изделия;
- единица измерения;
- количество;
- дата отгрузки;
- уникальный номер документа.
ПОКУПАТЕЛИ
- уникальный код покупателя;
- наименование;
- город;
- почтовый адрес.
СКЛАДЫ
- номер склада;
- фамилия материально ответственного лица;
- код детали - компоненты или изделия;
- единица измерения;
- количество, имеющееся на складе;
|
|
|
- дата последней операции.
ЗАРПЛАТА (бухгалтерский учет начисления и удержания по зарплате)
- табельный номер работника;
- сумма начисления;
- сумма удержания;
- дата выдачи.
ЗАКАЗЫ (договоры на отгрузку готовой пpодукции покупателям)
- код покупателя;
- уникальный номер заказа;
- код изделия;
- единица измерения;
- план поставки на год, в шт.;
- распределение плана по всем месяцам - поставки в январе, феврале и т.д.;
- дата начала действия договора.
Упражнение. Постройте формальную модель предприятия в форме базы данных, определив таблицы КОМПОНЕНТЫ, ИЗДЕЛИЯ и т.д., используя выделенные слова (код, наименование, характеристика, единица, цена и т.д.) в качестве имен полей (здесь и далее мы для удобства используем кириллические имена произвольной длины; если ваша СУБД не поддерживает соответствующие идентификаторы, используйте латинскую транскрипцию и сокращения). Выясните, какие поля (или группа полей) являются первичными и внешними ключами. Правила целостности и корректности значений полей и записей таблиц (в частности, допустимость неопределенных значений) определите самостоятельно, исходя из содержательного смысла таблиц и отношений.
Упражнение. Проведите в компьютерном классе деловую игру, распределив роли директора предприятия, бухгалтера, мастера, поставщика, заказчика и т.п. по предлагаемому или - выдуманному самостоятельно "сценарию":
Заказчик - директору: "По нашему договору №…, от …. ваше предприятие недопоставило … изделий "…". Если Вы не поставите требуемые изделия в течении … дней, мы обратимся в суд"
Директор - заказчику "Минутку-минутку, сейчас уточним…У меня почему-то стоит другая дата…"
Директор - кладовщику "Сколько у нас на складе изделий "…"? Не хватает?"
Директор - начальнику цеха "Сможем в течении … дней собрать недостающие.. штук изделий "…"?
Далее следуют обращения
- начальника цеха на склад в поиске нужных компонент,
- начальника цеха - к директору с просьбой повысить тарифные расценки за срочную работу,
- директора - в бухгалтерию, с вопросом о финансовых возможностях предприятия выполнить эту просьбу,
- директора к поставщикам с просьбой срочно поставить недостающие компоненты,
- и т.д. - импровизируйте!
Разумеется, ваша игра должна быть достаточно деловой, т.е. серъезной - все обращения должны подкрепляться конкретными цифрами, датами, наименованиями, ссылками и т.д. - проще говоря, реальной работой по формированию запросов к БД и ее модификации.
|
|
|
ЛИТЕРАТУРА.
1) М.Нагао, Т.Катаяма, С.Уэмура. Структуры и базы данных – М.,Мир, 1986 – 196 с.
2) М.Грабер. Введение в SQL
3) А.Горев. Visual FoxPro 5.0. Книга для программистов – М., ТОО «Эдэль», 1997 – 552 с.
4) М.Антонович, Visual FoxPro для Windows, BINOM Publishers, 1996 – 688 с.
5) М.Базиян. Использование Visual FoxPro 6. Вильямс, 2000 - 925 с.
6) З.Пэддок, Дж.Петерсен, Р.Тэлмейдж. Visual FoxPro 6. Разработка корпоративных приложений. Изд-во ДМК 2000, 588 с.
Приложение1. Нормальные формы БД.
Приложение 2.
(выполнено, под руководством автора, студенткой А.Юсуповой в качестве одного из разделов дипломной работы)
|
|
|
