Практикум. 
Математика и информатика

Задача 1. Создание базы данных «ДОГОВОР»

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

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

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

• 1) в поле ШИФР — шифр договора, который однозначно идентифицирует договор;
• 2) в поле ВИД — вид договора (например, купли-продажи, аренды и т.гт.);
• 3) в поле ЗАКАЗЧИК — аббревиатура названия организации — заказчика договора;
• 4) в поле ИСПОЛНИТЕЛЬ — аббревиатура названия организации — исполнителя договора;
• 5) в поле ДАТА_ЗАК — дата заключения договора;
• 6) в поле ДАТА ОКОН — дата выполнения договорных обязательств, указанная в договоре;
• 7) в поле ТИП_ШС — тип штрафных санкций, который определяет способ начисления штрафа при ненадлежащем выполнении договора:
  • • при типе 1 штраф вычисляется как заданный процент от общей суммы договора,
  • • при типе 2 — как процент от суммы за каждый день задержки выполнения договора.

Если тип штрафных санкций не указан, то штраф не начисляется;

• 8) в поле Ш_С — размер штрафных санкций — процент от суммы договора;
• 9) в поле СУММА — сумма договора;
• 10) в поле СОСТ — состояние договора на текущий момент: «д» — действующий, «о» — оконченный;
• 11) в поле ДАТА_ВЫП — дата фактического выполнения договора;
• 12) в поле ЗД — количество дней задержки выполнения договора, определяемая как разница между датой выполнения и датой окончания договора (вычисляется только в том случае, если исполнение договора задержано);
• 13) в ноле ШТРАФ — сумма штрафа за несвоевременное исполнение договора (начисляется по неисполненным договорам при наличии в договоре штрафных санкций);
• 14) в поле ДОЛГ — параметр, по значению которого можно определить, выплачены или нет штрафные санкции по договорам. Указанные параметр принимает только два значения: «да» — штраф не выплачен, «нет» — штраф выплачен.

Информация о контрагентах — организациях — участниках договорных отношений систематизируется в таблице Контрагенты;

• 15) в поле КОД — аббревиатура названия организации — участника договора (однозначно определяет участника договорных отношений);
• 16) в поле ИМЯ полное наименование организации-контрагента;
• 17) в поле АДРЕС — юридический адрес организации-контрагента;
• 18) в поле ТЕЛ — контактный телефон организации-контрагента;
• 19) в поле Б_Р — банковские реквизиты организации-контрагента.

Данные 1—10, 15—19 являются входными, т. е. их значения известны при внесении информации о договоре и контрагентах в базу данных; они являются обязательными, кроме атрибутов 7 и 8, так как штрафные санкции могут отсутствовать. Параметр «состояние договора» является изменяемым в отличие от входных атрибутов 1—9.

Данные 11 — 14 вычисляются при наступлении определенного события — выполнения договора.

Задания

1. Создать файл базы данных «Договор».

Указания. Файл базы данных «Договор» сохранить в папке «БД» в личной папке студента на сервере.

2. Описать структуру таблиц Договоры и Контрагенты.

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

Структура таблицы Договоры представлена в табл. 6.4, таблицы Контрагенты — в табл. 6.5. Значения не указанных в таблице параметров оставить заданными по умолчанию.

3. Установить связь между таблицами Договоры и Контрагенты с обеспечением целостности и каскадным обновлением данных.

Указания. Данные таблицы связаны между собой, так как в полях ЗАК и ИСП таблицы Договоры должны стоять только те кодовые обозначения организаций, которые содержатся в поле КОД таблицы Контрагенты. Если необходимо ввести.

Структура таблицы «Договоры»

Таблица 6.4

Таблица 6.5

Структура таблицы «Контрагенты

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

4. Внести в таблицу Контрагенты сведения об организациях — участниках договорных отношений.

Указания. В режиме таблицы ввести указанные в табл. 6.6 данные.

5. Внести в таблицу Договоры сведения о 25 заключенных договорах, скопировав их из базы данных «ДОГОВОРобразец».

Указания. Открыть файл базы данных «ДОГОВОРобразец» {D:StudentDocumentHOrOBOP образец)]

• скопировать таблицу Договоры в Буфер обмена;

Рис. 633.

Содержание таблицы «Контрагенты».

• 264 Раздел 6. Базы данных. Система управления базами данных MS Access
• • открыть личную базу данных «ДОГОВОР»;
• • вставить таблицу из буфера. В окне «Вставка таблицы» указать имя таблицы — Договоры и установить переключатель Параметры вставки на значение Добавление данных в таблицу.

В результате проведенной операции таблица Договоры примет вид, приведенный в табл. 6.7.

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

• 6. В режиме Конструктора в структуру таблицы Контрагенты вместо кода «СТУДЕНТ» введите свою фамилию, а вместо «ФИО студента» — свои фамилию, имя и отчество. Откройте таблицу Договоры в режиме таблицы и проверьте каскадное обновление данных.
• 7. Создать форму Ввод для внесения данных в таблицу Договоры. Вид формы — «в столбец».
• 8. С помощью формы Ввод внести в базу данных сведения о пяти вновь заключенных договорах, приведенных в табл. 6.8. Вместо текста «СТУДЕНТ» вводите свою фамилию.

Указания. Для ввода новых строк в форме следует открыть пустую запись, нажав кнопку mJ панели навигации по записям таблицы.

• 9. Изменить структуру таблицы Договоры, добавив поля из табл. 6.9.
• 10. Используя Мастер форм, создать форму Выполнено для внесения сведений о выполнении договоров.

В состав указанной формы включить поля: ШИФР, СОСТ, ДАТАВЫП. Вид формы — «в столбец».

Листая записи базы данных, в иоле ШИФР найдите договоры с указанными в табл. 6.10 шифрами. Для найденных записей в поле ДАТАВЫП введите дату фактического выполнения договора, а в поле СОСТ значение «д» (действующий договор) заменить на «о» (оконченный договор).

11. Упорядочить договоры, но дате окончания договора.

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

• 12. Упорядочить договоры по исполнителю.
• 13. Отсортировать договоры, но исполнителю и, но дате окончания договора.

Указания. Для выполнения задания использовать расширенный фильтр.

Содержание таблицы «Договоры».

266 Раздел 6. Базы данных. Система управления базами данных MS Access

Практикум

Таблица 6.8

Г5.

О).

Таблица 6.9

Таблица 6.10

14. Используя запрос на выборку, составить календарный план окончания договоров в марте текущего года, указав дату окончания, шифр, исполнителя, сумму.

Указания.

В режиме Конструктора создать запрос на основе таблицы Договоры, в который включить поля ДАТАОКОН, ШИФР, ИСП, СУММА

Для поля ДАТА ОКОН в строке Условие отбора с помощью построителя выражений задайте соответствующий условию задания оператор сравнения — Between 01.03.уу And 31.03.yy,

Для поля ДАТА ОКОН в строке Сортировка задать сортировку значений по возрастанию (рис. 6.34).

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

Рис. 634.

Указания.

В режиме Конструктора создать запрос на основе таблицы Договоры, в который включить поля ШИФР, СУММА, ДАТАОКОН.

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

Для ноля ШИФР в строке Групповые операции задать операцию Count (подсчитать число), для поля СУММА — Avg (среднее арифметическое).

Для поля ДАТА ОКОН в строке Групповые операции задать операцию Условие, а в строке Условие обора с помощью построителя выражений задать соответствующий условию задания оператор сравнения — Between 01.03.yy And 31.03.уу (рис. 6.35).

16. Определить общую сумму договоров, выполненных в марте текущего года.

Рис. 635.

17. Вычислить количество дней задержки выполнения по оконченным договорам.

Указания.

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

В запрос включить поля ЗД и СОСТ.

Для поля ЗД в строке Обновление с помощью Построителя выражений задайте формулу вычисления задержки: ДАТА ВЫП — ДАТА ОКОН (рис. б. Зб).

Для поля СОСТ в строке Условие обора задайте значение «о» (договор окончен) (рис. 6.37).

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

18. Определить общее количество договоров, выполнение которых задержано.

Рис. 6.36.

Рис. 6.37.

• 19. Составить список фирм, задерживающих выполнение договоров, с указанием количества не выполненных в срок договоров.
• 20. Определить количество и общую сумму не выполненных в срок договоров для произвольного исполнителя.

Указания. Создать запрос с параметром для поля ИСП (рис. 6.38).

Рис. 6.38.

При выполнении запроса задайте в окне ввода параметра код любого исполнителя.

21. Вычислить штрафы по просроченным договорам, используя следующую сложную формулу: если ТИП_ШС = 1, то СУММА*Ш_С/100, если ТИП^ШС = 2, то СУММА* Ш_С/100*ЗД. Для договоров, по которым начислены штрафы, задать значение «да» для указателя задолженности ДОЛГ.

Указания.

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

В запрос включить поля ЗД, ТИП_ШС, ШТРАФ и ДОЛГ.

Для ноля ЗД в строке Условие отбора задайте значение «>0» (исполнение договора задержано).

Для поля ТИП_ШС в строке Условие отбора задайте значение «1» OR «2» (только для указанных типов штрафных санкций должны начисляться штрафы).

Для поля ШТРАФ в строке Обновление задайте указанную формулу вычисления размера штрафа. Для этого с помощью Построителя выражений выберите встроенную функцию ЕСЛИ — Ilf («ехрг»; «truepart»; «falsepart»). Открыв в нижнем окне Построителя выражений таблицу Договор и выбирая щелчком мыши соответствующие поля, вместо условия «ехрг» задайте [Договоры]! [ТИП_ШС] = «1»; вместо выражения «truepart», которое считается когда условие выполнено, — [Договоры]! [СУММА] * [Договоры]! [Ш_С]/100; вместо выражения «falsepart», которое считается, когда условие не выполнено, — [Договоры]![СУММА]*[Договоры]! [Ш_С]/100*[Договоры]![ЗД] (рис. 6.39).

Для поля ДОЛГ в строке Обновление задайте значение Да (рис. 6.40).

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

22. Составить список договоров, но которым выставлены штрафы, указав исполнителя, шифр договора и штраф.

Рис. 6.39.

Рис. 6.40.

Указания. Условие, что штрафы выставлены, означает, что ШТРАФ > О

23. С помощью формы для договоров, имеющих шифры 1441,2134, 4001, внести сведения об уплате штрафа.

Указания.

С помощью Мастера формы создать форму Выплачено с полями ШИФР и ДОЛГ. Вид формы — «в столбец».

В поле ШИФР с помощью кнопки поиска to найти запись с нужным заданным шифром (рис. 6.41).

Рис. 6.41.

В поле ДОЛГ убрать галочку, что означает задать значение для этого поля Нет.

Повторить операцию для других значений поля ШИФР.

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

Указания.

В данном запросе должны участвовать поля и из таблицы Договоры (ШИФР, ВИД, ЗАК, ДАТА_ЗАК, СОСТ),

и из таблицы Контрагенты (ТЕЛ). Обе эти таблицы следует выбрать при составлении запроса.

Между таблицами существуют две связи. Так как нас интересует телефон заказчика, то связь между полем ИСП таблицы Договоры и полем КОД таблицы Контрагенты следует выделить с помощью щелчка мыши и разорвать (удалить) (рис. 6.42).

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

Указания. Выполняется аналогично предыдущему заданию. При составлении запроса следует разорвать (удалить) связь между нолем КОД таблицы Контрагенты и полем ЗАК таблицы Договоры.

Рис. 6.42.

• 26. Используя запрос из задания 14, составить параметрический запрос для составления календарного плана окончания договоров на любой период времени.
• 27. Составить запрос для определения количества и средней суммы договоров, заключенных в произвольный период времени.
• 28. Определить количество действующих договоров для каждой пары «Заказчик — Исполнитель».
• 29. Составить список договоров, выполнение которых задержано, указав шифр договора, штраф, наименование и телефон исполнителя, наименование и телефон заказчика.
• 30. Определить среднюю задержку по каждому исполнителю.
• 31. На основе запроса, построенного в задании 26, создать отчет «План окончания договоров» за май. Сгруппировать данные, но исполнителю. В каждой группе данных подвести итоги — рассчитать общую сумму всех договоров в группе.

Указания. Данные в отчете группируются по полю ИСП, сортируются по возрастанию по полю ДАТА ОКОН. Тип макет отчета — ступенчатый. Стиль отчета — деловой.

Задача 2. Создание базы данных «АБИТУРИЕНТ».

Задание 1.

• 1. Разработать информационную структуру базы данных «АБИТУРИЕНТ», которая содержит следующие сведения об абитуриентах:
  • а) анкетные сведения'.

регистрационный номер (шифр);

фамилию, имя, отчество; год рождения;

учебное заведение (школа, гимназия, лицей, училище, техникум)',

год окончания;

наличие медали или диплома с отличием;

город;

адрес;

телефон;

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

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

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

2. Подготовить анкетные сведения о 20 абитуриентах^[1].

Задание 2.

• 1. Создать базу данных и занести в нее анкетные сведения о 20 абитуриентах.
• 2. Проверить правильность заполнения компьютерных анкет и внести необходимые коррективы.
• 3. Упорядочить анкеты по:
  • а) ФИО;
  • б) учебному заведению;
  • в) городу.
• 4. Определить число абитуриентов, окончивших учебное заведение ранее года поступления.
• 5. Определить число иногородних абитуриентов и составить их список.
• 6. Составить алфавитный список абитуриентов, имеющих медаль или диплом с отличием, указав ФИО, учебное заведение и год его окончания.
• 7. Внести результаты сдачи очередного вступительного экзамена и составить список абитуриентов, допущенных к следующему экзамену.

Указание. Считать, что первым сдают экзамен по истории Отечества. «Медалисты», сдавшие его на «отлично», считаются зачисленными.

• 8. Повторить и. 7 для каждого экзамена.
• 9. Вычислить средний балл по каждому экзамену.
• 10. Определить средний балл каждого абитуриента (исключить «медалистов», получивших «отлично» по истории Отечества).
• 11. Произвести зачисление при проходном среднем балле 4,5.
• 12. Составить алфавитный список зачисленных абитуриентов.

Контрольные вопросы

• 1. Реляционные БД: информационная структура, понятия атрибута, области допустимых значений, записи, поля.
• 2. Системы управления базами данных (СУБД): назначение и функции.
• 3. СУБД MS Access: объекты и средства их создания.
• 4. Типы полей и их свойства.
• 5. Ключевое поле, его назначение и использование.
• 6. Индексирование (упорядочение) записей в базе данных.
• 7. Структура реляционной БД. Связь между таблицами, виды связей.
• 8. Средства MS Access для установления связей между таблицами. Целостность данных и ее обеспечение.
• 9. Формы: назначение, средства создания, использование.
• 10. Пополнение и коррекция данных в СУБД MS Access.

И. Средства поиска записи по ключу и внесение изменений в СУБД MS Access.

• 12. Сортировка записей.
• 13. Назначение и виды запросов в СУБД MS Access
• 14. Порядок формирования запроса на выборку.
• 15. Вычисления в запросе. Способы группировки и групповые функции.
• 16. Запрос на обновление.
• 17. Запросы с параметрами: назначение и создание.

• [1] Студент допускается к выполнению заданий при выполненной домашней работы.