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

Российский государственный геологоразведо чный университет имени Серго Орджоникидзе Геофизический факультет ОБОРНЕВ Е.А. Основы проектирования баз данных для информационных систем Теория. Лекции. Лабораторный практикум. Часть 1 Москва - 2012 ББК 32.81 К 26 Рецензент(ы): Карпов В.А. профессор, Факультета финансов и банковского дела РАНХ и ГС при президенте РФ. Белов А.В. профессор кафедры кибернетики Московского института экономики и математики (МИЭМ). К 26 Оборнев Е.А. Основы проектирования баз данных для информационных систем. Практикум в упражнениях и задачах: Учебное пособие. – М.: Издательский дом Российского государственного геологоразведочного университета, 2012. – 106 с. Практикум составлен на основе стандарта ЕН.Ф.02 Информатика Специальность 011200 Геофизика. В него включены следующие темы и разделы: общие сведения об информации; процессы сбора, передачи, обработки и накопления информации; технические и программные средства реализации информационных процессов; модели решения функциональных и вычислительных задач; алгоритмические языки и программирование; базы данных; программное обеспечение и технологии программирования; локальные и глобальные сети ЭВМ; основы защиты информации; методы защиты информации; компьютерный практикум. © Оборнев Е.А. 2012. 2 Содержание Введение........................................................................................................................................................ 5 ЧАСТЬ 1. УПРАЖНЕНИЯ И ЗАДАЧИ .................................................................................................... 6 Основы реляционной модели данных.................................................................................................. 9 Пример построения информационной системы ............................................................................... 11 ЗАДАНИЕ 1.......................................................................................................................................... 12 ЗАДАНИЕ 2.......................................................................................................................................... 13 Нормализация таблиц – методика устранения избыточности ............................................................. 15 Упр. 1. Нормализация исходной таблицы ......................................................................................... 17 ЗАДАНИЕ 3.......................................................................................................................................... 18 ТАБЛИЦЫ – объекты хранения данных ................................................................................................ 19 Упр. 2. Создание таблиц...................................................................................................................... 19 Задача «Расписание» ........................................................................................................................... 21 Контрольные вопросы: ........................................................................................................................ 22 CВЯЗИ между таблицами ........................................................................................................................ 23 Упр. 3. Создание связей ...................................................................................................................... 25 Контрольные вопросы: ........................................................................................................................ 28 ФОРМА – объект обработки и представления данных на экране ...................................................... 29 Упр. 4-ф. Создание форм .................................................................................................................... 30 ЗАПРОС – объект построения временных таблиц с отобранной информацией ............................... 36 Упр. 4-з. Создание запросов ............................................................................................................... 37 ОТЧЕТ – основной объект представления данных в формате печатной страницы ........................... 39 Упр. 4-о. Создание отчетов ................................................................................................................. 40 3 Задача «Недвижимость»...................................................................................................................... 44 Контрольные вопросы: ........................................................................................................................ 45 Средства защиты от ошибок оператора .................................................................................................. 46 Задача «Ремонт»................................................................................................................................... 55 Контрольные вопросы: ........................................................................................................................ 56 Организация вычислений в БД ................................................................................................................ 57 Упр. 8. Сложные формы ...................................................................................................................... 65 Задача «Услуги»................................................................................................................................... 70 Контрольные вопросы: ........................................................................................................................ 76 Разграничение доступа к информации ................................................................................................... 77 Упр. 10. Назначение прав доступа в формах..................................................................................... 77 Получение итоговых значений в БД ....................................................................................................... 82 Упр. 11. Запросы с итогами ................................................................................................................ 82 Задача «Налог»..................................................................................................................................... 85 Контрольные вопросы: ........................................................................................................................ 86 Контрольные вопросы: ........................................................................................................................ 86 Упр. 12. Отчеты с итогами .................................................................................................................. 87 Контрольные вопросы: ........................................................................................................................ 91 Связь «Многие-ко-многим» ..................................................................................................................... 92 Упр. 13. Кадры с приказами................................................................................................................ 93 Упр. 14. Самостоятельная работа ..................................................................................................... 101 4 Введение Предлагаемый вниманию читателей практикум состоит из сборника упражнений, задач и методических рекомендаций, составленных по результатам многолетней работы авторов со студентами экономико-управленческих специальностей в различных вузах г. Москвы. В представленном пособии авторы применяют базовый педагогический принцип – изучение некоторого раздела знаний «от задачи». То есть, во главу изучаемой темы или выполняемого упражнения ставится простая и понятная (не абстрактная) задача или бизнесситуация. Это повышает мотивацию студентов к изучению данной темы. Вместе с тем задача строится так, чтобы охватить и прояснить важные теоретические вопросы. В данное пособие включены темы, которые относятся к разделам углубленной информатики или специальной (экономической) информатики. Для подавляющего числа прикладного программного обеспечения и компьютерных информационных систем (ИС) основополагающей технологией является технология баз данных (БД). Уверенность в значимости данного учебного раздела и сложности его освоения, а также трудности в решении вопроса, связанного с проектированием БД в управлении и экономике, привела авторов к разработке настоящего практикума. 5 ЧАСТЬ 1. ТЕОРИЯ, УПРАЖНЕНИЯ И ЗАДАЧИ. 6 Базы данных и информационные системы Краткое введение в проблему. Теория и примеры  Теория. В узком смысле информационная система (ИС) представляет собой комплекс систем хранения и программ обработки информации. Функции ИС состоят в:  поддержке надежного хранения информации,  правильной обработке (преобразовании и вычислении) информации, характерной для данной предметной области, *  предоставлении пользователям ИС удобного интерфейса . В широком смысле ИС включает в себя также аппаратные и телекоммуникационные средства, а также обслуживающий ее персонал и систему менеджмента. В дальнейшем мы будем использовать термин ИС в узком смысле, часто используя вместо него термин БД, имея ввиду, что ИС построена на технологиях БД. Для хранения информации ИС использует технологии баз данных (БД), а для обработки информации – инструменты и объекты системы управления базами данных (СУБД). БД – это информационная модель, позволяющая упорядоченно хранить структурированные * Интерфейс – средства взаимодействия пользователей с операционной системой компьютера или пользовательской программой. 7 данные, обладающие заданным типом и размером и относящиеся к определенной предметной области. Наибольшее распространение получила реляционная (табличная) модель данных. СУБД является программой (службой), осуществляющей взаимодействие между пользователем и базой данных. Она обеспечивает разработку и создание структуры БД, обработку запросов пользователя, подготовку отчетов и другие прикладные задачи. Механизм БД может быть скрыт за интерфейсом ИС. Например, на кассах магазинов установлены аппараты, считывающие штрих-коды продуктов. Информация о ценах товаров автоматически извлекается из БД, а сам факт продажи отражается в журнале продаж той же базы данных. В итоге обработки информации о продаже покупатель получает чек – отчет о покупке, где отражены сведения из нескольких таблиц хранения информации. Другим примером БД является библиотечная система, где хранится информация о всех книгах и журналах. При этом для учета наличия книги в хранилище, нужно вести учет не только наименований изданий, но и каждого экземпляра, ведя учет выдачи и возврата книг в специальном журнале. Страховая компания вводит и хранит данные обо всех лицах и объектах страхования. Отдельным информационным объектом в этой базе данных является журнал регистрации страховых случаев с занесением значений заранее определенного набора параметров, характеризующих данный тип случая. ИС страховой компании вычисляет на основании этих параметров размер страхового возмещения пострадавшему. Ярким примером ИС, использующей БД, является банковская система. Для простоты можно рассмотреть только часть банковской ИС, обслуживающую расчеты по пластиковым карточкам. БД хранит информацию о лице – владельце карты, о картах, принадлежащих это8 му лицу (может быть несколько счетов и, соответственно, карт). В БД ведется журнал операций по картам (зачисление-приход, списание-расход средств, дата, время, цель платежа, пункт обслуживания). ИС может информировать владельца карты обо всех этих операциях, генерируя SMS на указанный в личных данных владельца номер сотового телефона. ИС вычисляет остаток на счете и блокирует операции, при которых этот остаток может стать отрицательным, если это дебетная карта. Основы реляционной модели данных  Теория. Основным объектом хранения информации в реляционной модели является таблица. Для таблицы в реляционной модели используется специальный термин – ОТНОШЕНИЕ, чтобы подчеркнуть, что не любая таблица годится для использования в реляционной БД. Таблица может быть названа отношением, если: каждое отношение (таблица) имеет собственное имя и строится для описания конкретного объекта из выбранной предметной области реального мира («Товар на складе», «Заказы клиента»). В ней отражается сущность предмета или явления, характеризующего данный объект, и хранится информация о конкретных представителях данного объекта; информация сущности, разбивается на отдельные атомарные (неделимые) элементы, которые образуют поля (атрибуты сущности) и определяют структуру колонок отношения-таблицы. Факты (информация), описывающие конкретный экземпляр сущности, помещаются в соответствующие поля. Каждое поле имеет уникальное имя и определено типом и размером хранимой в нем информации. Нельзя поместить в одну колонку отношениятаблицы данные разных типов; 9 совокупность всех полей, описывающих отдельный экземпляр, образует запись (строку) таблицы. Каждая запись должна отличаться от другой на основе уникального значения, записанного в ключевое поле (первичный ключ). Невозможно сохранить в отношении две дублирующиеся записи. Проектируя структуру таблицы, нет необходимости сразу выстраивать правильный порядок чередования ее полей-колонок. Внутри одной ИС существует множество задач, в которых необходим различный вид и порядок представления информации. Для этих целей в СУБД существуют другие объекты: запросы, формы, отчеты. Конструируя таблицу, рекомендуется на первое место поставить ключевое (уникальное) поле, а за ним – поля, раскрывающие содержание ключа, например: [Код Сотрудника]* – [Фамилия], [Код Товара] – [Название товара]. * Квадратными скобками выделены имена полей БД. 10 Пример построения информационной системы Рассмотрим пример ведения учета персонала в отделе кадров некоторого условного предприятия: Акционерного коммерческого банка (АКБ) «Буревестник». Полное наименование информационной системы Система ведения учета персонала – ИС «Кадры». Разрабатывается на базе СУБД MS Access. Цель автоматизации Ведение учета персонала АКБ «Буревестник». Основные задачи Ввод данных о сотрудниках и их назначениях. Внесение изменений в информацию. Обеспечение системы хранения и обработки информации о сотрудниках. Поддержание целостности данных о персонале организации. Формирование экранных и бумажных отчетов по персоналу по заданным образцам. Ведение списков должностей. Хранение описания организационной структуры (списка отделов). 11 Отдел кадров АКБ «Буревестник» предоставил следующий документ (орфография документа сохранена): Список сотрудников АКБ «Буревестник» СОТРУДНИКИ КРЕДИТНОГО ОТДЕЛА (ТЕЛ. ОТДЕЛА ДОБ 334) Иванов, Иван, Москва, Сиреневый б., д.1, к.11,8-916 123-45-67, 01.01.1960, Холост, детей нет, Мужской, Управляющий, 35 000 RUB + 50 долл. надбавка. Петр Иванович Петров, Цветной б., д2, к. 22,8-095 456-71-23,02.02.1965, Семейный, Есть дети, Муж, Помощник управл.,12 000 RUB, надб. $ 100 СОТРУДНИКИ ВАЛЮТНОГО ОТДЕЛА (ТЕЛ. ОТДЕЛА ДОБ 335) Сидоров, Петр, Малиновый пр. д.3 к.33, 8-903 234-56-71, 04.04.1970, В разводе, 2 ребенка (девочка и мальчик), М, Пом. управляющего ,19 000 RUB+150 Птичкина, Галя, Цветной б., д2, к. 22,8-095 456-71-23,03.03.1966,Семейная, мальчик 5 лет, Ж, Управляющий,15 000 RUB+$250 US СОТРУДНИКИ АДМИНИСТРАТИВНОГО ОТДЕЛА (ТЕЛ. ОТДЕЛА ДОБ 333) Синичкина, Мария Ивановна, Цветной б., д.2, к. 22,8-926345-6712,05.05.1930,Вдова,3,Жен, Главный управляющий,25 000 RUR ЗАДАНИЕ 1 Проанализируйте (совместно с преподавателем) информацию, представленную в документе «Список сотрудников АКБ». Разбейте информацию на атомарные единицы, характерные для всей сущности и определите необходимые поля для размещения данных в таблице-отношении. Дайте имя отношению и придумайте имена для каждого его поля. 12 ЗАДАНИЕ 2  Самостоятельно. Подберите типы и размеры для всех полей таблицы, исходя из анализа информации в документе «Список сотрудников АКБ “Буревестник”». Выберите поле или группу полей, отвечающих требованиям первичного ключа. Рис. 1. Таблица для анализа первичной информации из документа «Список сотрудников АКБ “Буревестник”» в процессе проектирования ИС «Кадры». Имя поля Тип данных Фамилия Имя Адрес Телефон домашний Дата рождения Семейное положение Дети Пол Надбавка ИВАНОВ Иван Сиреневый б., д. 1, к. 11 8-916 123-45-67 01.01.1960 Холост Муж $ 50 US ПЕТРОВ Петр Цветной б., д. 2, к. 22 8-495 456-71-23 02.02.1965 Семейный 1 Муж $ 100 US СИДОРОВ Петр Малиновый пр. 8-903 д. 3, к. 33 234-56-71 04.04.1970 В разводе 2 Муж $ 150 US ПТИЧКИНА Галина Цветной б., д. 2, к. 22 8-495 456-71-23 03.03.1966 Семейная 1 Жен $ 250 US Цветной б., д. 2, к. 22 8-926 345-67-12 05.05.1930 Вдова 3 Жен $ 0 US СИНИЧКИНА Мария Размер 13 Рис. 1. (продолжение) Таблица для анализа первичной информации из документа «Список сотрудников АКБ “Буревестник”» в процессе проектирования ИС «Кадры». Имя поля Должность Оклад Отдел Телефон отдела Тип данных Управляющий 15 000 RUR КРЕДИТНЫЙ доб 334 Помощник управляющего 12 000 RUR КРЕДИТНЫЙ доб 334 Помощник управляющего 12 000 RUR ВАЛЮТНЫЙ доб 335 Управляющий 15 000 RUR ВАЛЮТНЫЙ доб 335 Главный управляющий 25 000 RUR АДМИНИСТРАТИВНЫЙ доб 333 Размер Созданную таблицу необходимо проанализировать на предмет появления избыточности, и возможности проявления аномалий вставки, обновления и удаления. Избыточность ведет к увеличению объема и возможным ошибкам при обновлении хранимых данных, а аномалии приводят к невозможности правильного хранения и обработки информации. Для устранения избыточности и аномалий таблицу разбивают на части – нормализуют и строят связи между образовавшимися частями. 14 Нормализация таблиц – методика устранения избыточности  Теория. Под нормализацией понимается процесс или методика перехода к табличному представлению информации – нормальным формам таблиц, информация в которых представлена наиболее оптимально. Этот процесс носит творческий (нестрогий) характер, где можно выделить лишь следующие основные (подходы) или этапы: Выберите и удалите из анализируемой (первой) таблицы группу полей функционально независимых от первичного ключа. Как правило, они заполнены повторяющимися значениями. Это можно сделать на основе правила: «Если некоторую информацию можно хранить и обрабатывать независимо от ключевого поля, то она отражает сущность другого объекта и должна хранится в отдельной таблице». Создайте новую (вторую) таблицу на основе этих полей. Определите для нее ключевое поле. Записи в данной таблице будут заполнены только уникальными значениями полей. Повторы исключены. Дайте новые названия для первой и второй таблиц и определите, какой тип связи между ними. Как правило, там, куда были перемещены повторяющиеся поля, будет связь ОДИН, а в первой таблице – МНОГО. Добавьте в первую таблицу новое поле для связи ее со второй таблицей, как простое (не ключевое) поле, имеющее тот же тип данных и размер, что и ключевое поле второй таб15 лицы. Обычно его называют полем внешнего (вторичного) ключа (МНОГО). Значения ключей из второй таблицы добавьте в каждую запись первой в новое поле внешнего ключа, руководствуясь информацией из исходной (не разбитой на две) таблицы.  Самостоятельно. Проанализируйте пример распределения информации по таблицам из учебной БД «Борей». Определите и перечислите пары таблиц, связи между которыми определяется как «Многие-ко-многим». 16 Упр. 1. Нормализация исходной таблицы  Теория. Выбираем из шаблона таблицы для анализа первичной информации (см. рис. 1) поля [Отдел] и [Телефон отдела]. Они функционально не зависят от первичного ключа [Фамилия]. Они заполнены повторяющимися значениями, что вызывает избыточность. На первый взгляд в нашем примере избыточность не очень большая. Но представим, что каждый отдел характеризуется не только наличием телефона, а также сведениями о его номере, местонахождении, площади занимаемых помещений, начальнике отдела, уровне подчинения и т.д. В этом случае для каждого нового сотрудника мы вынуждены были бы в такой таблице повторять всю информацию об атрибутах отдела. Если бы какой-то атрибут изменился (например, телефон отдела), то это нужно было бы поменять во всех записях о сотрудниках из данного отдела (аномалия обновления). При этом также возникают аномалии вставки и удаления. Мы не можем вставить информацию об отделе [Отдел]=Бухгалтерия, пока не появился сотрудник из данного отдела, определяющий первичный ключ. При удалении записи о сотруднике, например, по фамилии [Фамилия]=Синичкина, теряется информация об отделе [Отдел]=Административный.  Самостоятельно. Проанализируйте связь группы полей [Должность] и [Оклад] с первичным ключом [Фамилия]. Определите новые таблицы. Дайте имена таблицам – информационным объектам. Определите в них (создайте в каждой) первичное ключевое поле. 17 ЗАДАНИЕ 3  Самостоятельно. В результате анализа шаблона таблицы с исходными данными, разделите ее на три независимые части. Определите названия и первичные ключи для каждой новой таблицы. Определите типы связи между новыми таблицами. Добавьте специальные поля для построения связей – внешние ключи в таблицы на стороне «-ко многим». Табл. Табл. Имя поля Тип Размер П_Кл Имя поля Тип Размер Тип Размер П_Кл Должность Оклад Фамилия Имя Адрес Телефон Домашний Дети Дата Рождения Семейное Положение Пол Надбавка Табл. Имя поля П_Кл Отдел Телефон Отдела П_Кл =Первичный ключ Вн_Кл =Внешний ключ 18 ТАБЛИЦЫ – объекты хранения данных  Теория. Сведения о представителях (экземплярах) каждой сущности из выбранной предметной области в БД записываются в отдельную таблицу – основной объект хранения структурированной, типизированной информации определенного размера. При работе с данными из нескольких таблиц устанавливаются связи между таблицами на основе специальных ключевых полей: первичного и внешнего ключа. Упр. 2. Создание таблиц Постройте с помощью КОНСТРУКТОРА БД, состоящую из совокупности трех  1. 2. 3. 4. 5. 6. таблиц «Сотрудники», «Должности», «Отделы», используя результат деления информации на части (Задание 3). Порядок выполнения упражнения Загрузите программу MS Access. Создайте новую БД. Сохраните файл БД с именем «Кадры.mdb» в свою рабочую папку. Создайте с помощью КОНСТРУКТОРА последовательно три таблицы: «Сотрудники», «Должности», «Отделы». Откройте КОНСТРУКТОР для каждой новой таблицы и определите в нем основные свойства полей (см. Задание 3): имя поля , тип данных , размер поля  (см. иллюстрацию на следующей странице). Определите в каждой таблице специальным символом поле первичного ключа  (контекстное меню – > Ключевое поле). 19 7. Для числовых значений в полях [Пол] и [Семейное положение] создайте кодификаторы (справочники) соответствия чисел и их текстового описания. Например, 0–«Не семейный», 1–«Семейный», 2–«В разводе» и т.д. 20 З адача «Расписание» Проанализируйте информацию о расписании. Ответьте на вопрос: «Эта таблица отвечает требованиям таблицы-отношения?» Составьте структуру новой таблицы-отношения по аналогии с решением в Задании 2, не производя нормализацию. РАСПИСАНИЕ занятий для студентов 1 курса очной формы обучения юридического факультета (2006/2007 учебный год) Ауд. 310 1 сентября пятница 9.00-10.20 10.35-11.55 12.25-13.45 14.05-15.25 9.00-10.20 4 сентября понедельник 10.35-11.55 12.25-13.45 14.05-15.25 15.35-16.55 История отечественного государства и права – лекция проф. Яковлев Л.С. Теория государства и права – лекция проф. Оксамытный В.В. Математика – лекция проф. Усков Л.Ф. Теория государства и права – лекция проф. Оксамытный В.В. История отечественного государства и права – лекция проф. Яковлев Л.С. Построение таблицы «Расписание» Ключ Имя поля Тип поля Размер 21  Контрольные вопросы: 1. Что такое ИС? Примеры ИС. 2. Для чего предназначены БД? 3. Какие существуют модели хранения данных? 4. Что такое НОРМАЛИЗАЦИЯ и что является ее результатом? 5. Основное предназначение СУБД. Объекты СУБД. 6. Основные свойства полей таблиц. 7. Какие типы данных могут использоваться для построения таблиц? 8. Какие типы данных предназначены для числовой информации? 9. Какие типы данных предназначены для текстовой информации? 10. Какие виды чисел можно хранить с помощью числового типа? 11. Какие размеры существуют для целых чисел? 12. Какой тип данных подходит для поля [Почтовый индекс]=123411? 13. Какой тип данных подходит для поля [Площадь квартиры]=104,4 кв.м.? 14. Зачем необходим денежный тип данных, если имеется числовой? 15. Что такое ключевое поле? Какие бывают ключи в таблицах? 16. Почему в качестве ключа часто используются значения «Счетчика»? 17. Какое поле не может стать ключевым? 22 CВЯЗИ между таблицами Связь «Один-ко-многим»  Теория. Обратимся к результату выполнения упр. 2. Теперь, чтобы обозначить принадлежность сотрудника отделу и занимаемую им должность, нужно указать соответствующие значения первичных ключей [Код_Отдела] и [Код_Должности] во внешних ключах таблицы «Сотрудники». Используя значения внешних ключей, можно получить всю недостающую информацию (например, об окладе или рабочем телефоне) из соответствующих таблиц «Должности» и «Отделы». Возникает вопрос: как показать СУБД, что значения для внешнего ключа [Код_Отдела] нужно искать в таблице «Отделы». Для правильной работы этого механизма необходимо построить Схему данных, отображающую взаимосвязи между таблицами, т.е. установить СВЯЗЬ между внешним ключом [Код_Отдела] в таблице «Сотрудники» и первичным ключом [КодОтдела] в таблице «Отделы». При этом, очевидно, что единственному, уникальному значению из ключевого поля [КодОтдела] в таблице «Отделы» может соответствовать много записей с этим же значением в таблице «Сотрудники». Другими словами выполняется условие: в одном отделе работает много сотрудников. То же самое относится и к должности: одну должность имеют разные сотрудники из разных отделов. Такой тип связи называется «Один-ко-многим» (1). Дополнительно можно «поручить» СУБД поддерживать целостность такой связи. Это означает, что СУБД будет следить за значением внешнего ключа при создании или измене23 нии любой записи в таблице, находящейся на стороне связи «-ко-многим», и проверять, есть ли запись в таблице на стороне «Один-» с таким значением первичного ключа. Если нет, то СУБД не позволит создать/изменить такую запись. Например, нельзя сделать запись о новом сотруднике [Фамилия]=Волков в таблице «Сотрудники» с назначением на [Должность]=Главный бухгалтер в новый [Отдел]=Бухгалтерия, до тех пор, пока еще нет соответствующих записей в таблицах «Должности» и «Отделы». Аналогично, нельзя удалить запись о некотором отделе в таблице «Отделы», если в таблице «Сотрудники» есть записи о сотрудниках, работающих в данном отделе. Связь «Многие-ко-многим»  Теория. Если посмотреть на пример из упр. 2 с другой стороны, то между таблицей «Должности» и таблицей «Отделы» возникает связь «Многие-ко-многим» (). Она реализует условие, что много сотрудников с одной должностью могут работать в разных отделах. И, наоборот: в одном отделе могут работать сотрудники с разными должностями. Реляционная СУБД не может обеспечить целостную связь типа «Многие-ко-многим» между двумя таблицами. Для этого ей необходима третья таблица, в которой будут установлены две связи типа «Один-ко-многим» (1). Например, в нашем случае такой третьей таблицей является таблица «Сотрудники». Связь «Один-к-одному»  Теория. Если собрать дополнительные сведения у сотрудников (например, об образовании, увлечениях и т.д.), то для них можно создать дополнительную таблицу со связью «один-к-одному» (11) на основе первичного ключа [КодСотрудника]. 24 Упр. 3. Создание связей Определите и установите связи между таблицами так, чтобы информация в БД отвечала всем требованиям нормализации.  Порядок выполнения задания 1. Закройте все открытые таблицы или конструкторы таблиц, так как создавать или изменять связи между открытыми таблицами нельзя. 2. На панели инструментов нажмите кнопку «Схема данных» или выберите пункт основного меню «Сервис» – > «Схема данных». 3. Если в БД до этого никаких связей не определялось, автоматически будет открыто диалоговое окно Добавление таблицы. Если нужно добавить таблицы для создания связей, а диалогового окна Добавление таблицы на экране нет, нажмите кнопку «Отобразить таблицу» на панели инструментов. Если таблицы, между которыми нужно создать связь, уже отображены, перейдите к шагу 5. 4. Дважды щелкните имена таблиц, для которых требуется определить связи. Затем закройте диалоговое окно Добавление таблицы. Повторно добавленные таблицы можно скрыть. Для этого необходимо указать на них курсором и, вызвав контекстное меню правой клавишей мыши, выбрать команду Скрыть.  Смотрите иллюстрацию на следующей странице. 25 5. Выберите ключевое поле таблицы «Отделы» со стороны «Один-» создаваемой связи и перетащите его, удерживая нажатой левую клавишу мыши, совместив с именем поля нужного внешнего ключа в таблице «Сотрудники» со стороны «-комногим». В диалоговом окне Изменение связей установите флажок только для пункта Обеспечение целостности данных.  Смотрите иллюстрацию на следующей странице. 26 6. Самостоятельно определите связь для оставшихся таблиц «Должности» и «Сотрудники». В итоге Схема данных должна выглядеть как на рисунке. 27  Контрольные вопросы: Что определяет связь «Один-ко-многим»? Что определяет связь «Многие-ко-многим»? Как реализуется связь «Многие-ко-многим» в реляционной БД? Если таблицы А и В, имеющие связь типа «Многие-ко-многим», связываются через таблицу С, то на стороне каких таблиц будет стоять «один», и каких – «многие»? 5. Придумайте пример БД, в которой таблицы А и В имеют связь «Один-ко-многим», и пара таблиц В и С также имеют связь «Один-ко-многим». 6. Придумайте пример БД, в которой таблицы А и В имеют связь«Один-ко-многим», а пара таблиц A и С имеют связь «Многие-ко-многим». 7. Что такое связь «Один-к-одному»? 8. С какой целью может использоваться связь «Один-к-одному»? 9. Как создать связь между таблицами в MS Access? 10. Может ли таблица быть связана сама с собой? 11. Как можно изменить и удалить связь в MS Access? 12. Зачем используется свойство «Обеспечение целостности данных»? 13. Как работает каскадное обновление связанных полей? 14. Как работает каскадное удаление связанных полей? 1. 2. 3. 4. 28 ФОРМА – объект обработки и представления данных на экране  Теория. Под процессом обработки информации в ИС прежде всего понимаются следующие процедуры: ввод, просмотр, выборка некоторого подмножества данных, сортировка, группировка, изменение (исправление), расчет производных параметров и итогов. Все эти действия можно делать с помощью объектов СУБД ФОРМЫ. Для одной БД может быть разработано сколько угодно форм в зависимости от поставленных задач обработки информации. При удалении формы удаляется процедура обработки, графическое оформление, но информация из БД не исчезает, так как хранится в объектах другого типа – в таблицах. Чтобы ввести данные примера (см. рис. 1), необходимо создать как минимум три формы: по одной форме для каждой таблицы, сконструированной в упр. 2. Начинать ввод следует с таблицы, находящейся на стороне «Один-» связи «Один-ко-многим». Она является «главной», из нее подставляются значения первичного ключа в поле внешнего ключа «подчиненной» таблицы. И только затем создаются и заполняются формы для таблиц, у которых в линии связи стоит «-комногим». В нашем случае следует начинать с форм «Отделы» и «Должности». Формы можно создавать двумя способами: с помощью конструктора и с помощью мастера. Механизм мастера относится к категории CASE технологий. Эта технология реализует концепцию «программирование без алгоритмического языка». Мы будем пользоваться МАСТЕРОМ на начальном этапе построения, а затем переходить в КОНСТРУКТОР, если потребуются изменения и дополнения. 29 Упр. 4-ф. Создание форм Создайте формы с помощью МАСТЕРА для ввода информации в БД «Кадры». Необходимо сделать по одной форме для каждой таблицы для ввода и редактирования данных.  Порядок выполнения задания 1. Выбираем в основном рабочем окне БД «Кадры» объект ФОРМЫ. 2. Вначале создадим формы для таблиц, у которых в связи стоит «Один-» – это таблицы «Должности» и «Отделы». 3. Запускаем команду Создание формы с помощью мастера для создания формы на основе полей из таблицы «Должности». МАСТЕР создания форм помогает выбрать саму таблицу и определится с необходимым набором полей таблицы, помещаемых в форму.  Смотрите иллюстрацию на следующей странице. 30 Из раскрывающегося списка Таблицы и запросы выберите название таблицы «Должности». Переместите все поля из списка Доступные поля в окно списка Выбранные поля. Нажмите кнопку «Далее». В общем случае, здесь можно выбирать все поля или только часть необходимых полей из одной таблицы, а также разные поля из разных таблиц.  Внимание: Нельзя выбирать поля из таблиц и запросов одновременно. 4. Выберите внешний вид для данной формы – ленточный. 31  Смотрите иллюстрацию на следующей странице.  Для форм с малым количеством полей обычно выбирают табличное представление данных (вид ленточный или табличный). Ленточный, в отличие от табличного вида, позволяет делать многоярусную таблицу и использовать зону примечаний для вычислений. Для форм с большим количеством полей используют представление данных в виде бланка (карточки) в один столбец. На бланке (в один столбец) мы видим все поля только одной записи, а в таблице (табличный, ленточный виды) поля уместившихся на экране нескольких записей. 5. Стиль формы (графическое оформление) выберите самостоятельно. 6. Назовите созданную форму «Штатное расписание». Имя формы всегда выбирайте (не доверяя мастеру), исходя из смысла решаемой задачи. 7. С помощью созданной формы заполните таблицу «Должности» контрольными дан32 ными из шаблона таблицы «Кадры» (см. рис. 1). Обратите внимание, что для поля [Код Должности] требуется придумать значения кодов, так как их нет в таблице на рис.1. Поле [Код Должности] – поле первичного ключа, поэтому все значения должны отличаться, иначе возникнет ошибка, связанная с условием на уникальность значения ключевого поля. В таблице «Должности» не должно быть дублирующихся записей. Это означает, что необходимо выбирать из таблицы (рис. 1) неповторяющиеся значения должностей и связанных с ними окладов. Если кодификатор должностей специально не разрабатывается, то обычно применяют нумерацию по порядку, поэтому для таких полей часто выбирают тип поля – Счетчик (автонумератор). Создайте форму «Структура организации» для таблицы «Отделы». ВыСтрока с обозначением  (звездочка) предназначена для новых берите все поля таблицы данных. 33 «Отделы» и вид – табличный. 9. Заполните таблицу «Должности», используя созданную форму, тестовыми данными из примера (см. рис. 1). Необходимо выбирать из таблицы неповторяющиеся значения.  Внимание. При вводе информации в поле [Телефон отдела] мы не пишем слово «Доб» (добавочный), т.к. для данного поля определен тип поля Текстовый и Размер 3 символа. Приставка «Доб» перед номером является избыточной, повторяющейся во всех записях таблицы Отделы, ее незачем хранить. Мы добавим эти символы в описании Маски ввода и Формата поля в упр. 5. 10. Создайте форму «Карточка сотрудника» для ввода информации в таблицу «Сотрудники». Для данной формы выберите все поля таблицы «Сотрудники» и определите вид формы в один столбец. 11. С помощью созданной формы внесите данные обо всех сотрудниках, используя документ «Список сотрудников АКБ» (Задание 1). 34 При заполнении внешних ключей данной формы вам понадобится информация о кодовых значениях из таблиц «Отделы» и «Должности». Для удобства откройте все три созданные формы и расположите их рядом на экране. Это позволит правильно вводить данные в поля [Код Отдела] и [Код Должности] При заполнении полей [Пол] и [Семейное положение] необходимо использовать кодификаторы из упр. 2.  Внимание. Если при заполнении тестовыми данными, в какой либо форме появятся ошибки, то закройте форму, затем удалите ее, исправьте ошибки в конструкторе таблиц, а затем, заново создайте эту форму. 35 ЗАПРОС – объект построения временных таблиц с отобранной информацией  Теория. Объект ЗАПРОС в СУБД используется для отбора отдельных полей одной или нескольких связанных таблиц, а также для сортировки и выбора записей по некоторым логическим условиям. Запросы представляются как временно создаваемые таблицы и поэтому они могут служить источником данных для создания форм и отчетов. Запросы используют для создания структуры новой таблицы, исключения повторяющихся данных, создания перекрестных таблиц и т.д. Запросы также позволяют сгруппировать, обновить или удалить одновременно несколько записей, выполнить вычисления итоговых или новых полей. Создание запросов ведется в двух основных режимах работы: Построение схемы запроса на основе примера с помощью визуального конструктора QBE (Querry By Example), который позволяет с помощью графических средств строить и наглядно видеть структуру запроса. Альтернативным методом формирования запроса является написание его на специальном структурном языке (SQL). Мы будем пользоваться МАСТЕРОМ построения запросов на начальном этапе их создания, а затем переходить в конструктор QBE для его корректировки. 36 Упр. 4-з. Создание запросов Создайте запрос с помощью МАСТЕРА, отображающий объединенную информацию из всех таблиц БД «Кадры».  Порядок выполнения задания 1. В окне БД «Кадры» перейдите в объекты ЗАПРОСЫ. Создайте с помощью МАСТЕРА запрос, объединяющий всю информацию БД «Кадры». На первом шаге работа мастера идентична построению форм. 2. Добавьте для этого запроса в список Выбранные поля все поля (кроме кодовых) из всех трех таблиц: «Сотрудники», «Должности», «Отделы». 3. Выберите «Подробный вид запроса» (выбор каждого поля каждой записи). 4. Впишите имя запроса «Отдел кадров». Посмотрите полученный результат. Таблица подробного запроса отображает всю информацию из БД. Эта таблица соответствует исходному примеру данных (рис. 1), на основе которого были построены все таблицы нашей БД «Кадры». Результат выполнения запроса «Отдел кадров» доказывает правильность построенной схемы данных.  Смотрите иллюстрацию на следующей странице. В таблице представлен результат выполнения запроса. 37 Продолжение таблицы. 38 ОТЧЕТ – основной объект представления данных в формате печатной страницы  Теория. Для представления данных и их распечатки определенным образом используется объект СУБД ОТЧЕТ. С помощью этого объекта можно создать и напечатать отчет, группирующий данные и вычисляющий итоги, или отчет для распечатки почтовых (товарных) наклеек. Отчет может брать данные из заготовленного запроса или в процессе построения (с помощью мастера) подготовить такой запрос из разных таблиц. При этом надо помнить, что смешивание полей таблиц и полей запросов при построении отчета недопустимо. Отчет может выглядеть как таблица, но чаще используется представление в виде иерархической структуры. Конкретные отчеты, как и формы, можно создавать двумя способами: с помощью КОНСТРУКТОРА и с помощью МАСТЕРА. Мы будем пользоваться МАСТЕРОМ на начальном этапе построения, при работе которого будут определяться группировки данных и вычисление итоговых значений. Переход в режим КОНСТРУКТОРА будем использовать, когда потребуется изменить вид представления информации. 39 Упр. 4-о. Создание отчетов Постройте отчет с помощью МАСТЕРА, в котором отражается вся информация о сотрудниках АКБ «Буревестник», сгруппированная по отделам.  Порядок выполнения задания 1. Откройте в окне БД «Кадры» объекты ОТЧЕТЫ и выполните команду Создание отчета с помощью мастера. 2. На первом шаге выберите все поля из запроса «Отдел кадров». 3. Выберите вид представления данных по – Отделы. 4. Группировку, сортировку и итоги пропустите. 5. Выберите Макет по левому краю 1. 6. Имя отчета «Сотрудники по отделам». 7. Откройте отчет в КОНСТРУКТОРЕ. 8. Перестройте отчет, руководствуясь примером его структуры, показанной на следующей странице. Расширьте или сузьте рамки полей, так чтобы они соответствовали размеру информации в каждом поле.  Смотрите иллюстрацию на следующей странице. 40 Отредактируйте отчет таким образом, чтобы он помещался в ширину на страницу размера А4.  Смотрите иллюстрацию на следующей странице. 41 Пример распечатки отчета о сотрудниках АКБ «Буревестник», сгруппированных по отделам. 42 Проанализируйте, есть ли несоответствия вида представленных данных в созданных объектах: запросах, формах и отчете и в документе «Список сотрудников АКБ “Буревестник”» (рис. 1). Запишите все замечания в таблицу. ПОЛЕ НЕСООТВЕТСТВИЯ И ИСПРАВЛЕНИЯ Оклад Телефон отдела Фамилия Адрес Телефон домашний Дата рождения Семейное положение Пол Надбавка Код Отдела Код Должности 43 Задача «Недвижимость» Проанализируйте два примера объявлений о продаже недвижимости и предложите структуру БД для хранения подобной информации. Ленинградское ш. 60 км от МКАД, дер. Рубчиха, дом 6х10, сруб под крышей, ИЖС, участок 13,5 сот., крайний к лесу, очень живописное место, эл-во по границе, газ планируется, асфальт до деревни. Цена 50 тыс.$ АН "Домострой" 970-10-02, www.dom.ru Дата выхода объявления в газете: 08.06.2007 Цена: $ 50 000 Тип предложения: продам Регион: Московская обл. » Ленинградское напр. Московская обл. » Ленинградское ш. Дата: 07.06 10:19 Дата выхода объявления в газете: 08.06.2007 Варшавское ш. Участок, 100 км от МКАД, 24 сотки, красивая местность, недалеко р. Ока, дер. Семеновская, 24000$ "Оргкомитет" лиц.000005(006) 8-910-460-68-49, 8910-460-68-31 Людмила Михайловна Дата выхода объявления в газете: 08.06.2007 Цена: $ 24 000 Тип предложения: продам Регион: Московская обл. » Курское напр. Московская обл. » Варшавское ш. Дата: 07.06 10:19 Дата выхода объявления в газете: 08.06.2007Московская обл. » Ленинградское ш. 44 Дата: 07.06 10:19 Дата выхода объявления в газете: 08.06.2007  Контрольные вопросы: 1. Дайте определение БД и приведите примеры БД из жизненного опыта. 2. Каковы основные задачи, решаемые с помощью БД? 3. Каково назначение СУБД? 4. Перечислите основные объекты СУБД на примере СУБД MS Access. 5. Чем определяется структура таблиц БД? 6. Для чего используются запросы? 7. Перечислите основные виды запросов. 8. Для чего используются формы? 9. Чем определяется внешний вид формы? 10. Если БД состоит из трех связанных таблиц, сколько может быть различных форм? 11. Для чего используются отчеты? 12. Какова связь между объектами «Отчет» и «Запрос»? 13. Чем определяется внешний вид отчета? 14. Для чего используются объекты страницы? 15. Зачем нужны макросы и модули? 45 Средства защиты от ошибок оператора СУБД имеет множество механизмов для обеспечения правильности и непротиворечивости данных при их вводе, обработке и хранении. Ниже рассматриваются такие дополнительные свойства полей как «Формат», «Маска ввода», «Значение по умолчанию», «Условие на значение», «Сообщение об ошибке». Формат. Большинство СУБД имеет заготовленные стандартные форматы, например, для отображения даты и времени. Однако, разработчику предоставляется возможность создавать свои, пользовательские форматы. Маска ввода обеспечивает соответствие данных определенному формату при вводе, а также заданному типу значений. Для управления форматом поля и маской ввода используются специальные управляющие символы. В приложении Microsoft Access в свойстве поля можно построить с помощью определенного набора символов описание пользовательского формата и маски ввода. Значение по умолчанию позволяет описать значение, которое будет автоматически введено в поле при создании новой записи. Условие на значение задает правило, по которому проверяется значение поля при вводе в него значения. Можно также ввести текстовое сообщение об ошибке, которое будет выведено на дисплей пользователю, если правило будет нарушено. 46 Упр. 5. Дополнительные свойства полей Используя дополнительные свойства полей, отредактируйте с помощью КОНСТРУКТОРА все таблицы БД «Кадры» для устранения несоответствий вида представления информации.  Порядок выполнения упражнения 1. Используя HELP, выпишите определение для каждого специального символа (выражения). Символ Описание Символ Описание Символ ? @ # dd 9 # ### ddd # ##0 mmm 00.00.00;0 ” RUR” yy \- L 47 Описание 2. Просмотрите таблицу с примерами возможных дополнительных свойств полей. Она составлена с использованием правил и специальных управляющих символов. Ввод некоторых из этих свойств нужно будет сделать в следующих пунктах упражнения. Поле Фамилия Дата рождения Свойство Формат > Маска ввода Значение по умолчанию Условие на значение Сообщение об ошибке Телефон Надбавка @-(@@@)-@@@dd,ddd-mmm-yy @@-@@ “$ ”#“ US” 9"-("999")-"000\00.00.00;0 00\-00 “$ ”##0“ US” Оклад Телефон отдела # ###” RUR” “Доб “@@@ # ##0” RUR” “Доб “000 1000 =0 >=1000 Не моложе 18 лет Не должна быть Больше либо меньше нуля равно МРОТ 3. Задайте специальные форматы для полей [Надбавка], [Оклад] и [Телефон отдела]. 4. Создайте маску ввода для поля [Телефон]. 5. Определите поля, в которые должен обязательно осуществляться ввод данных, иначе запись БД не имеет смысла. Например, запись о сотруднике с пустым полем [Фамилия] или без значения даты в поле [Дата рождения]. Просмотрите все поля в КОНСТРУКТОРЕ таблиц и установите в нужном случае свойство «Обязательное поле» Да 48  Смотрите иллюстрацию на следующей странице. 6. Для защиты данных от неправильного ввода используйте свойства полей «Условие на значение» и «Сообщение об ошибке». Например, для поля [Оклад] условием на значение может быть: больше минимального размера оплаты труда (МРОТ), определенного Трудовым кодексом РФ, а сообщением об ошибке – следующая строка: «Оклад должен быть больше либо равен МРОТ». 7. Откройте таблицы в режиме просмотра. Информация в них должна отображаться также как и в документе «Список сотрудников АКБ» на рис. 1. Пример на следующей странице. При необходимости добавьте новые форматы, маски ввода. 8. Откройте формы «Штатное расписание», «Структура организации», «Карточка сотрудника». Как видно, изменения, коснувшиеся таблиц, не отразились в формах, т.к. формы создавались на основе старых, не исправленных таблиц. 49  Внимание. Удалите только формы (не таблицы). 9. Проверьте, что информация в таблицах сохранилась. Пример отображения информации в таблицах после внесения изменений. 50 Упр. 6. Подстановка значений  Теория. Механизм подстановки является мощным средством защиты данных от ошибок оператора при вводе связанных значений. При этом в поле подстановки, которое связано с другой (главной) таблицей или фиксированным списком используется элемент управления – Поле со списком вместо обычного поля . Такой элемент управления требует определения источника данных, на основе которого формируются строки раскрывающегося списка (Источник строк). Источник строк должен представлять собой, тем или иным образом организованную таблицу. В нашем упражнении нас будут интересовать два вида источников строк: «Таблица или запрос» и «Список значений». В первом случае для подстановки в поле используются значения из другой таблицы (либо собственно таблица БД, либо временно созданная таблица–запрос). Как правило, таблица, из которой мы подставляем значение, имеет не один столбец, а несколько. Поэтому необходимо указать, сколько у нас столбцов (параметр «Число столбцов»), и какой столбец из числа выбранных мы будем подставлять в исходное поле подстановки (параметр «Присоединяемый столбец»). Подстановку из источника «Таблица или запрос» можно делать только для поля внешнего ключа, там, где в связи обозначено «-ко многим». Во втором случае «Список значений» мы должны собственноручно создать список в виде текста с разделителями, заполнив его значениями пар – ключа и его описания, из которых первое будет выбираться для подстановки, а второе для отображения пользователю. 51 Значения в этом списке должны быть разделены знаком «;» – точка с запятой. Задание: Для устранения возможных ошибок ввода и уменьшения объема хранимой информации используйте механизм подстановки из фиксированных списков.  Порядок выполнения задания 1. Для полей [Семейное положение] и [Пол] создайте подстановки на основе фиксированных списков. Для поля: Сем_Пол 1 2 3 Семейное положение Название_Сем_Пол Не семейный Семейный В разводе Овдовевший Для поля: Пол -1 Логическое значение: -1 52 Пол Имя_пола Женский Мужской False (Нет) True (Да) ОБЩИЕ Формат поля Значение по умолчанию =0 ПОДСТАНОВКА Тип элемента управления Тип источника строк Источник строк Присоединенный столбец Число столбцов Ширина столбцов Поле со списком Список значений 0; “Женский”;-1; “Мужской” 1 2 0;5 * *Если ширину, какого либо столбца задать 0, то этот столбец будет скрыт при выводе на экран 2. В КОНСТРУКТОРЕ таблицы «Сотрудники» в разделе подстановка для полей [Код Отдела] и [Код Должности] создайте подстановки из таблиц «Отделы» и «Должности» соответственно. 53 3. Откройте таблицу «Сотрудники» в режиме просмотра и проверьте, что ваши подстановки правильно работают. 54 Задача «Ремонт» Проанализируйте объявление о предложении услуг по ремонту недвижимости и создайте фиксированные списки для полей подстановки в структуре БД «Ремонт», предназначенной для хранения информации о предложениях по ремонту. Косметический и комплексный ремонт. Малярно-отделочные работы. Быстро и недорого. Качественный ремонт квартиры, дома, офиса. Выполняются все виды работ: электрика, малярные, плотницкие, плиточные, гипсокартоновые. Цены: 1 слой шпаклевки, покраски – 50 руб. Клейка обоев – 100 руб. Выполняются все виды электромонтажных работ: одна точка – 300 руб. Выравнивание стен и потолков, гипсокартон, ламинат, плитка, сантехника, электрика. Новостройки, ванная – «под ключ». Отдельно за штробовку денег не берем. Укладка плитки – 400 руб. Сжатые сроки, умеренные цены, гарантия, лицензия. Комплектация материалами. Бесплатно: 1) Консультации и помощь для снижения расходов при ремонте. 2) Составление сметы. 3) Консультации по подбору черновых строительных и финишных отделочных материалов. Уборка помещения после ремонта. Михаил&Ko – профессионалы с 10-летним опытом. Соблюдение сроков работ. т. 798-66-31, т. 8-916-556-81-90 Регион: Москва 55  Контрольные вопросы: 1. Чем отличаются свойства «Формат поля» и «Маска ввода»? 2. Как может выглядеть «Маска ввода» для Автомобильного номера=АБ-123-77-RU? 3. Когда имеет смысл использовать «Значение по умолчанию»? 4. Когда имеет смысл использовать «Условие на значение»? 5. Как определить «Условие на значение», если дата должна быть в пределах 200Х года? 6. Когда имеет смысл сделать поля обязательными для заполнения? 7. Что такое индексированное поле в таблице? 8. Зачем используется механизм подстановки? 9. Назовите возможные источники списков для подстановки. 10. Почему при подстановке используют более одного столбца? 11. Как связана подстановка и установленные связи между таблицами? 12. Как сделать, чтобы при подстановке кода, было видно только его содержательное описание, а не его значение? 13. Приведите примеры подстановок, с которыми вы встречались при использовании информационных систем. 56 Организация вычислений в БД В силу принципа минимизации избыточности в таблицах не хранится ничего, что можно получить путем расчетов по значениям полей таблиц. Это не означает, что вычисления в БД невозможны, вычисления могут производиться в любых других объектах, кроме таблиц. В формах, запросах, отчетах, макросах и модулях используются «свободные поля», переменные, для которых можно написать выражение (формулу), по которой производится вычисление. При этом следует помнить, что вычисления производятся на основании значений тех полей, которые включены в соответствующий объект – ФОРМУ, ЗАПРОС и т.д. Если какое либо поле должно участвовать в вычислениях, но не должно быть видно пользователю, в значение его свойства «Вывод на экран» устанавливается «Нет». Имеется два вида вычислений – по строке и по столбцу. В первом случае формула (выражение) может включать в себя поля строки данных ФОРМЫ, ЗАПРОСА, ОТЧЕТА, константы и функции. Функции могут быть взяты из встроенного набора функций СУБД, либо быть написаны программистом специально для прикладной задачи. Вычисления «по столбцу» – это итоги, которые вычисляются по значениям выборки, вошедшей в соответствующий объект: подчиненную форму, запрос, отчет. Таким итогом чаще всего бывает СУММА, но возможно вычисление статистик: среднего значения, минимального, максимального и т.п. 57 Упр. 7. Вычисляемые поля На основе хранящейся информации нужно вычислить возраст сотрудников на текущую дату.  Порядок выполнения задания 1. Создайте с помощью МАСТЕРА новые формы для всех отредактированных таблиц (см. упр. 4-ф). Новые формы воспримут все изменения, которые мы сделали в КОНСТРУКТОРЕ таблиц. 2. Откройте КОНСТРУКТОР для формы «Карточка сотрудника». 3. Отредактируйте рамки полей и надписей формы таким образом, чтобы информация полностью размещалась в них (например, для полей [Телефон] и [Адрес] необходимо раздвинуть горизонтальную границу). 4. Расширьте с помощью курсора область, находящуюся между «Заголовок формы» и «Область данных». Выберите на Панели элементов элемент «Надпись» и поместите его в область «Заголовок формы». Впишите в рамку надписи полное название этой формы «Карточка сотрудника». 5. Внесите в форму «Карточка сотрудника» свободное поле 2, используя Панель элементов 1. 6. Откройте палитру свойств для данного поля (контекстное меню Свойства). Впишите в строку свойств Данные 3 этого поля выражение для расчета возраста, используя построитель (Свойства–Данные–Построитель 4).  Смотрите иллюстрацию на следующей странице. 58 Пример экрана во время размещения свободного поля в конструкторе формы. 59 60 Пример построения вычисляемого выражения с помощью построителя в свободном 61 поле. 7. В расчетной формуле используйте значение сегодняшней даты, которую возвращает функция Date(), поле [Дата рождения], и функцию Year(number), которая вычисляет количество лет от разности числа дней (number) между датами. Она автоматически прибавляет 1900, поэтому в нашем выражении это число вычитается. 8. Измените надпись для свободного поля с «ПолеNN» на «Возраст». Перейдите в режим просмотра формы. Проверьте работу построенного выражения, выбирая других сотрудников. Поле [Возраст] должно автоматически вычисляться для новых значений, при изменении значения поля [Дата рождения] у разных сотрудников.  Самостоятельно. 1. Создайте новое свободное поле [Прожито], в котором постройте выражение, вычисляющее, сколько секунд прожил каждый сотрудник. Для полей [Возраст] и [Прожито] для свойства Формат определите формат вывода данных таким образом, чтобы рядом с цифрами стояли слова «лет» и «секунд» соответственно. Значения секунд должны выводится по группам цифр с разделением на тысячные позиции. 2. Добавьте двух новых сотрудников – студентов на [Должность]=Практикант ([Оклад] = 5 тыс. руб.) в отделы Валютный и Кредитный. Для заполнения полей значениями выберите в качестве первого студента-практиканта себя, а в качестве второго – своего товарища. 62 3. Добавьте новых сотрудников, доведя их численность до 10-ти человек.  Смотрите иллюстрацию на следующей странице. 63 Пример формы с добавленными расчетными полями и заголовком в режиме просмотра данных формы. 64 Упр. 8. Сложные формы Разработайте формы для просмотра информации, сгруппированной по заданному признаку. Для таблиц, имеющих связь «Один-ко-многим», одной записи в главной таблице («Отделы» или «Должности») соответствует несколько записей в подчиненной («Сотрудники»). Форма, содержащая поля из обеих связанных таблиц, может быть представлена в виде сложной (иерархической), состоящей из двух частей – главной и подчиненной. Создайте сложные формы с помощью МАСТЕРА. Имена подчиненной и главной формы совпадают, но подчиненная имеет окончание «ПФ» или «Подчиненная форма».  Порядок выполнения задания 1. Создайте форму «Отдел и его сотрудники» с полями: [Код Отдела], [Отдел], [Телефон Отдела], [Фамилия], [Надбавка]. 2. Вид представления данных – по таблице «Отдел» с подчиненной формой. Внешний вид подчиненной формы ленточный. Стиль выберите по своему вкусу. Имя формы – «Отдел и его сотрудники», имя подчиненной формы – «Отдел и его сотрудники ПФ». 3. Проверьте работу созданной формы. Листая названия отделов, вы должны видеть только тех сотрудников, которые в них работают. 65 4. Рассчитайте сумму надбавок по отделу по формуле Sum([Надбавка]), добавив свободное поле в раскрытую область примечаний подчиненной формы. Сложная форма с итогами в режиме КОНСТРУКТОРА. 66 5. Добавьте в главной форме заголовок «Отдел и его сотрудники», используя элемент управления Надпись. 6. Создайте еще одну форму «Сотрудники по должностям» с полями: [Код Должности], [Должность], [Оклад], [Фамилия], [Дата Рождения]. 67 Внешний вид подчиненной формы табличный. Стиль выберите по своему усмотрению. Имя подчиненной формы «Сотрудники по должностям ПФ». 7. Рассчитайте возраст в подчиненной форме, добавив свободное поле в Область данных подчиненной формы (выражение для расчета возраста, см. упр. 7). 8. Добавьте в главной форме заголовок «Сотрудники по должностям».  Самостоятельно. Дополнительное задание. Создание форм в режиме КОНСТРУКТОРА. 1. В КОНСТРУКТОРЕ таблицы «Сотрудники» добавьте поле [Фото] для размещения фотографии сотрудника (тип данных – поле объекта OLE). 2. В КОНСТРУКТОРЕ формы «Карточка сотрудника» добавьте это поле из Списка полей (или добавьте элемент Присоединенная рамка объекта и выберите в строке данные поле [Фото]). Задайте размер этого поля 3х4 см. Выберите значение – «Вписать в рамку» для свойства «Установка размера» поля [Фото]. 3. Создайте в графическом редакторе (например в Paint) рисунки-портреты сотрудников (или возьмите из MS Clip Gallery) и добавьте эти «фото» в каждую запись таблицы «Сотрудники», используя измененную форму «Карточка сотрудника».  Смотрите иллюстрацию на следующей странице. 68 Измененная форма «Карточка сотрудника» в режиме КОНСТРУКТОРА. 69 Задача «Услуги» Проанализируйте прейскурант провайдера и разработайте БД, учитывающую информацию о предоставленных услугах и количестве пользователей, оплативших данную услугу в текущем месяце. Составьте выражение, подсчитывающее сумму поступлений в у.е. Прейскурант услуг Интернет-провайдера “Релком. ДС” Тарифный план ДОМЕН МИНИМУМ ЭКОНОМ Описание услуги - 3 Мб дискового пространства; - станд. скрипты; SSI; - льготный тариф на выход в Интернет (время соединения, пп. 3.6.). - 20 Мб дискового пространства; - количество субдоменов не ограничено; - 3 почтовых ящика по 2 Мб; - антивирусная проверка почты; - станд. скрипты; - SSI; - льготный тариф на выход в Интернет (время соединения, пп. 3.6.). - 40 Мб дискового пространства; - количество субдоменов не ограничено; - 5 почтовых ящиков по 2 Мб; - антивирусная проверка почты; - станд. скрипты; - SSI; - льготный тариф на выход в Интернет (время соединения, пп. 3.6.). 70 Цена 1 у.е./мес 3 у.е./мес. 5 у.е./мес. Упр. 9. Сложные запросы Для обработки информации из БД нужно выбрать часть информации, удовлетворяющей некоторому условию.  1. 2. 3. 4. Порядок выполнения задания Условия отбора в запросе Создайте новый Подробный запрос с помощью МАСТЕРА. Выберите в него из разных таблиц поля [Фамилия], [Имя], [Дата рождения], [Пол], [Дети], [Должность], [Отдел]. Имя нового запроса «Женщины, имеющие детей». Откройте созданный запрос в КОНСТРУКТОРЕ и в строке Условие отбора для поля [Пол] укажите значение соответствующее женскому полу (фиксированный список), а для поля [Дети] логическое выражение >0 (больше нуля). Проверьте правильность выполнения запроса, для чего закройте КОНСТРУКТОР и откройте запрос на просмотр.  Смотрите иллюстрацию на следующей странице. 71 Вид запроса в КОНСТРУКТОРЕ. Иллюстрация к пункту 3. Вычисления в запросе 1. Скопируйте запрос «Отдел кадров» (см. упр. 4-з) и назовите его «Кадры с вычислениями». 2. Откройте в КОНСТРУКТОРЕ запрос «Кадры с вычислениями». 3. Найдите свободные столбцы или добавьте новые (основное меню -> Вставка -> Столбцы) и создайте вычисляемые поля, используя построитель выражений (контекстное меню -> Построить). 72 4. В первой строке столбца, которое называется Поле, постройте выражения для расчета возраста сотрудника и его зарплаты по известным формулам (см. упр. 7). Закройте КОНСТРУКТОР. 5. Откройте запрос для просмотра информации. При открытии появится диалоговое окно для ввода нового значения в поле [Курс]. Проверьте правильность расчета. 73 Запросы с параметром в условии 1. Чтобы иметь возможность задавать параметры условий отбора при выполнении запроса (без перехода в режим КОНСТРУКТОРА), используйте при построении условия вместо конкретного значения название переменной величины, взятое в квадратные скобки, например, [Укажите возраст сотрудника]. 2. Откройте запрос «Кадры с вычислениями» и, удалив колонку «Зарплата», сохраните его. Скопируйте этот запрос и назовите новый запрос «Сотрудники по возрасту». 3. Откройте запрос «Сотрудники по возрасту» в КОНСТРУКТОРЕ. 4. Используйте в новом запросе только поля [Фамилия], [Имя], [Пол], [Должность], [Отдел], [Возраст], а все другие удалите. 5. В поле [Возраст] в строке «Условие отбора» напишите фразу в квадратных скобках >[Старше какого возраста?]. При открытии этого запроса появляется диалоговое окно «Введите значение параметра» для ввода данных.  Смотрите иллюстрацию на следующей странице. 74 Таким будет результат исполнения запроса при вводе в окно параметра «40». 75  Контрольные вопросы: 1. Для решения каких задач используются формы? 2. Как можно создать форму? 3. Как форма связана с запросом? 4. Какие разделы могут содержать формы? 5. Чем отличается главная форма от подчиненной? 6. Какие три режима отображения формы существуют? 7. Какие элементы управления можно использовать для отображения данных? 8. Как изменить свойства элемента управления? 9. Какое свойство связывает элемент управления с данными из таблицы или запроса? 10. Как изменить базовый запрос формы, и когда это бывает необходимо? 11. Почему в созданной форме могут не отображаться данные? 12. Почему в элементе управления может появиться сообщение «#Ошибка?» или «#Имя?»? 13. Какой элемент управления предназначен для поиска данных? 14. В каком элементе управления можно проводить вычисления? 76 Разграничение доступа к информации Упр. 10. Назначение прав доступа в формах Определим для нашей БД «Кадры», что инспектор по кадрам может вносить и изменять личные данные о сотрудниках (кроме надбавки), а руководство не может менять эту информацию, но зато может перевести работника на другую должность или в другой отдел.  Порядок выполнения задания 1. Используя Проводник, скопируйте два раза БД «Кадры» в файлы с именами «Кадры_таблицы» и «Кадры_инспектор». 2. Откройте новую БД «Кадры_таблицы» и удалите в ней все формы и запросы. После этого закройте БД. 3. Откройте БД «Кадры_инспектор» и добавьте в нее новые таблицы с помощью меню Файл/Внешние данные/Связь с таблицами, используя в качестве источника данных файл «Кадры_таблицы.mdb». В окне таблицы БД «Кадры_инспектор» появятся новые таблицы с указателем ►, обозначающим, что они внешние: ►Сотрудники1, ►Отделы1, ►Должности1. Удалите старые таблицы без указателя и переименуйте новые, убрав в каждом имени нумератор 1. Проверьте, что Схема данных отображает прежние связи между таблицами. 77 Это сделано для того, чтобы установить различным пользователям различные базы MS Access (разные файлы mdb), с определенным для данного рабочего места набором форм и прав доступа. При этом все эти базы будут работать с общими таблицами из файла «Кадры_таблицы.mdb», расположенном на общем для всех пользователей сетевом ресурсе. Создайте в БД «Кадры_инспектор» с помощью МАСТЕРА новую форму «Для редактирования инспектором ОК», содержащую полную информацию о сотруднике. Для этого выберите последовательно все поля из таблиц «Сотрудники», «Должности» и «Отделы» (кроме первичных ключей). Вид представления данных –Сотрудники форму с представлением в один столбец. одиночная форма. При этом вы получите 4. Откройте созданную форму в КОНСТРУКТОРЕ и отредактируйте размеры и расположение рамок полей и их надписей. Добавьте с помощью Панели элементов два свободных поля. Обратите внимание, что при добавлении нового (свободного) поля оно именуется «ПолеNN», и автоматически рядом с ним создается надпись с таким же именем. В КОНСТРУКТОРЕ в режиме отображения свойств новых полей замените их имена на «Зарплата» и «Курс». Переименуйте соответственно с именами и Надписи новых полей. Зарплата сотрудников рассчитывается на основе выражения в строке свойств ->Данные =[Оклад] + [Надбавка]*[Курс]. 5. Создайте заголовок формы: «Для редактирования инспектором ОК». 78 6. Определите формат поля [Зарплата] с обозначением дробного десятичного числа с разделителем групп на тысячи # ###“ руб.” и числом десятичных знаков 2. Для поля [Курс] необходимо указать маску ввода и формат поля ##,##“ Руб/$” с числом десятичных знаков 2. Между символами #,# ставится запятая или точка (разделитель десятичных знаков) в зависимости от национальных установок в операционной системе вашего компьютера. 7. Откройте отредактированную форму в режиме просмотра. Зарплата будет рассчитана только после ввода значения в поле [Курс]. Похожую ситуацию мы уже встречали в упр. 9 «Вычисления в запросе», п. 5. 8. Проверьте, что в построенной форме, имеется возможность редактирования всех сведений в существующих записях, а также добавление новых. Измените величину оклада у сотрудника Иванова и убедитесь, что это привело к изменению оклада у Птичкиной. Такое действие приводит к непредвиденным результатам, ошибкам, т.к. [Оклад] принадлежит сущности «Должность» и не должен быть доступен для изменения из фор79 мы, имеющей в качестве главной сущность «Сотрудники». 9. Запретите в этой форме изменения в полях [Надбавка], [Код Отдела], [Код Должности], [Отдел], [Телефон Отдела], [Должность], [Оклад] и [Зарплата], установив значение свойства «Доступ» для этих полей в положение «Нет». 10. Скопируйте файл БД «Кадры_инспектор» в файл с именем «Кадры_руководитель». От80 кройте новую БД «Кадры_руководитель» и удалите все формы, кроме формы «Для редактирования инспектором ОК». 11. В новой БД форма для работы руководства с данными о сотрудниках будет отличаться от формы для инспектора только правами доступа. Поэтому переименуйте форму «Для редактирования инспектором ОК», в «Для просмотра руководством АКБ». 12. Закройте в новой форме доступ ко всем полям кроме [Надбавка], [Сотрудники_Код Отдела], [Сотрудники_Код Должности], [Оклад] и [Курс]. 13. Проверьте, что в построенной форме «Для просмотра руководством АКБ», руководство имеет возможность переводить сотрудников в другие отделы и менять их должность и надбавку. Допустим, Руководитель АКБ Синичкина решила перевести Птичкину на должность Главного управляющего в отдел Административный с обнулением надбавки, а себя уволить в связи с выходом на пенсию. Первую задачу мы легко выполним в нашей БД «Кадры_руководитель». Решение второй задачи мы рассмотрим в следующих упражнениях, когда разрешим проблему связи «Многие-ко-многим». 81 Получение итоговых значений в БД Упр. 11. Запросы с итогами  Внимание. Упражнение выполняется в исходной базе данных «Кадры.mdb».  Порядок выполнения задания Создание итогового запроса с помощью МАСТЕРА 1. Выберите в новый запрос поля [Отдел], [Оклад], [Возраст] из запроса «Кадры с вычислениями». 2. В качестве итогов задайте расчет суммы окладов, среднего значения возраста сотрудников и подсчет числа сотрудников, работающих в отделе. 3. Сохраните новый итоговый запрос с именем «ФОТ по отделам» (ФОТ – фонд оплаты 82 труда). Перейдите в режим «Редактирование запроса в режиме КОНСТРУКТОРА». 4. Посмотрите, что в отличиe от подробного запроса, в итоговом появилась строка «Групповая операция». В этой строке для поля [Отдел] установлено значение Группировка, для поля [Оклад] – Sum (сумма), для поля [Возраст] – Avg (среднее значение). 5. В новой колонке «Count-Сотрудники» построено новое выражение: Count(*). 6. Добавьте в запрос (не создавая нового запроса) поле [Пол]. Установите группировку по этому полю. Проверьте, что теперь итоги подводятся отдельно для мужчин и женщин каждого отдела. 83  Самостоятельно. Дополнительное задание. Выполнить один из вариантов по согласованию с преподавателем. Создайте запросы, в которых вычисляются следующие величины: 1. ФОТ по должностям. 2. Сумма льгот по заработанной плате, приходящаяся на сотрудниц каждого отдела, если женщинам за каждого ребенка доплачивают 2 500 руб. 3. Количество мужчин призывного возраста по должностям. 4. Количество сотрудников пенсионного возраста работающих в каждом отделе. 5. *Количество сотрудников в каждом отделе во всех возрастных интервалах от 10 до 90 лет (10–20; 20–30; … и т.д.). Для этого используйте в запросе следующее выражение: Int([Возраст]/10)*10. Оно означает, что в каждом расчетном числе прожитых лет после деления на 10 мы отбрасываем дробную часть функции перевода числа в целое значение Int(число), а затем вновь умножаем. После этой операции реальный возраст приводится к нижнему десятку. Например, Иванову 44 года. После деления получим 4,4. После округления – 4, а после умножения – 40. Теперь достаточно для этого поля установить (с помощью контекстного меню) в строке Групповые операции значение «Группировка» и мы получим деление сотрудников по диапазонам возрастов. * Задача повышенной сложности 84 Задача «Налог» Проанализируйте документ «Ставки транспортного налога» и разработайте выражения для условий запроса к БД «Автовладельцы» для подсчета суммы налоговых поступлений в текущем году. СТАВКИ ТРАНСПОРТНОГО НАЛОГА на территории Ленинградской области для физических и юридических лиц, введенные с 01.01.2003 г. (в соответствии с законом Ленинградской области от 22.11.2002 г., № 51-оз). Налоговая ставка (в Наименование объекта налогообложения рублях) Автомобили легковые с мощностью двигателя (с каждой лошадиной силы): до 100 л.с. (до 73,55 кВт) включительно 10,00 свыше 100 до 150 л.с. (свыше 73,55 до 110,33 кВт) включительно свыше 150 до 200 л.с. (свыше 110,33 до 147,1 кВт) включительно свыше 200 до 250 л.с. (свыше 147,1 до 183,9 кВт) включительно свыше 250 л.с. (свыше 183,9 кВт) 20,00 40,00 62,00 65,00 85  Контрольные вопросы: 1. Для чего используются запросы? 2. Какие существуют механизмы в СУБД для создания запросов? 3. Как можно задать условие отбора в запросе? 4. Как обеспечить выполнение двух условий для одного поля? 5. Как обеспечить выполнение хотя бы одного условия из двух для разных полей? 6. Как добавить таблицу для использования в запросе? 7. Для чего используются групповые операции? 8. Как добавить вычисляемое поле в запрос? 9. Для чего используются перекрестные запросы? 10. Что представляет собой язык SQL? 11. Как рассчитать в запросе возраст по данным поля [Дата рождения]? 12. Как найти записи, начинающиеся с определенной буквы? 13. Зачем в запросе есть механизм сортировки записей? 14. Что позволяют получить итоговые запросы? 15. Какие групповые операции возможны в запросе? 16. Как работают запросы на объединение? 86 Упр. 12. Отчеты с итогами Создайте с помощью МАСТЕРА отчет с итогами.  Порядок выполнения задания 1. Выбираем все поля из запроса «Кадры с вычислениями». 2. Добавим уровни группировки вначале по отделам. 3. Порядок сортировки выберем один – по возрастанию значений поля [Фамилия], т.е. в алфавитном порядке. 4. Откроем Итоги и укажем расчет: суммы по [Оклад] и среднего значения по [Возраст]. 5. Вид отчета лучше выбрать с выравниванием по левому краю, а ориентацию листа отчета альбомную т.к. в отчете много полей. 6. Имя отчета «ФОТ Сотрудников по отделам». Просмотрите полученный отчет. 7. Откройте отчет в КОНСТРУКТОРЕ и поправьте поля, которые не полностью умещаются на листе отчета, и отредактируйте рамки этих полей и надписи к ним. 87 8. Поля =Sum([Зарплата]) и =Avg([Возраст]) и их надписи Sum и Avg переместите из Примечаний группы ‘отдел’ в область Заголовка группы ‘отдел’. 9. В области данных расположите поля в два яруса для более компактного представления данных. В этом же порядке расположите надписи в области заголовков группы ‘отдел’.  Смотрите иллюстрацию на следующей странице. 88 Примерно так должен выглядеть отчет в режиме просмотра. 89 Задача «Монеты» Проанализируйте прейскурант по операциям с монетами и предложите структуру БД для хранения и актуализации данной информации. Постройте структуру отчета таким образом, чтобы отчет выглядел аналогично приведенному примеру. ОПЕРАЦИИ С МОНЕТАМИ ИЗ ДРАГОЦЕННЫХ МЕТАЛЛОВ Металл, проба Серебро 925 Золото 900 Серебро 999 Серебро 999 Золото 916.7 Золото 916.7 Серебро 999 Серебро 925 Серебро 925 Наименование монеты Номинал на монете Монеты Банка России Соболь-95 3 рубля Червонец 10 рублей Монеты Австралии Кролик (австрал.)-99 1 австрал. доллар Австрал. Кукабурра-03 1 австрал. доллар Монеты Великобритании Полсоверена (англ.)-05 0,5 фунта стерлингов Соверен (англ.)-05 1 фунт стерлингов Монеты Замбии Слон (замб.)-03 5000 замб. квачей Монеты Белоруссии "Маленький принц " (бел.)-05 20 белорус. рублей "Снежная королева" (бел.)-05 20 белорус. рублей 90 Содержание химически чистого драгоценного металла, г 31.1 7.74 31,1 31.1 3.66 7.32 31.1 26,15 26,15  Контрольные вопросы: 1. Какой объект БД готовит данные для отчета? 2. Какие разделы могут содержать отчеты? 3. Как задать группировку записей в отчете? 4. Каково максимальное число уровней группировки в отчете MS Access? 5. Какая функция используется для подсчета числа записей в группе? 6. Как добавить надпись в отчете? 7. Как изменить источник данных у элемента управления в отчете? 8. Какие разделы отчета могут быть использованы для вычисления итогов? 9. Какие вычисления возможны в отчетах? 10. Чем определяется форма отчета? 11. Чем определяется размер страницы отчета? 12. Как добавить новые поля в отчет? 91 Связь «Многие-ко-многим» Введение в проблему Созданная нами БД «Кадры» работает правильно, как мы убедились по предыдущим упражнениям, но не позволяет решать ряд управленческих задач. Например, она не может отслеживать историю кадровых перемещений сотрудников, так как у в таблице «Сотрудники» есть только по одному полю [Код_Отдела] и [Код_Должности], являющихся внешними ключами, связывающими эту таблицу с таблицами «Отделы» и «Должности». Реальная ИС «Кадры» должна фиксировать движение сотрудников из отдела в отдел, смену должности, увольнение. Возникает задача развития БД «Кадры» для удовлетворения потребностей кадровой службы. Требуется разработать новый проект ИС, позволяющий сохранять историю перемещений сотрудников по службе от приема на работу, включая увольнение. На основе предыдущего проекта БД «Кадры» нужно создать новую БД «Кадры с приказами», решающую поставленную задачу.  Теория. Если мы хотим решить эту задачу, то между таблицами «Должности» и «Сотрудники» обнаруживается связь «Многие-ко-многим». Иными словами, одну и ту же должность могут занимать многие сотрудники, и один сотрудник теперь может занимать последовательно разные должности. Между таблицами «Отделы» и «Сотрудники» также существует связь «Многие-ко-многим»: в отделе работает много сотрудников и сотрудник банка 92 может поработать последовательно во многих отделах. Проблема состоит в том, что реляционная СУБД не поддерживает обеспечения целостности по связи «Многие-ко-многим». Действительно, попробуйте в рамках существующей схемы данных попытаться сохранить все должности, которые занимал сотрудник в течение его работы в АКБ «Буревестник». Можно попытаться ввести в таблицу «Сотрудники» поля [Код_должности_1] [Код_должности_2] …[Код_должности_N]. Однако, что делать, если вдруг сотрудник перемещался по службе N+1 раз? Более того, чтобы эти коды были внешними ключами таблицы «Сотрудники», нужно установить много связей между таблицами «Сотрудники» и «Должности». Но этого не позволит сделать реляционная СУБД, так как она может следить за обеспечением целостности только одной связи между двумя таблицами. Поэтому для решения данной проблемы необходимо создать новую таблицу, в которой будет храниться информация о назначениях и перемещениях сотрудников. То есть, нужно перестроить базу данных, из старой создать новую, внеся в нее необходимые изменения. Упр. 13. Кадры с приказами Для конкретизации задач, решаемых в новой БД, предположим, что нам нужно провести следующие операции: 1. Сделать в записи о приеме на работу всех имеющихся сотрудников с 01.01 текущего года. 2. Уволить Синичкину с 01.02 по собственному желанию в связи с выходом на пенсию. 3. Переместить на должность Главного управляющего Птичкину. 4. Переместить Сидорова с должности Помощника управляющего Валютным отделом 93 на должность Управляющего того же отдела c 01.02. 5. Изменить структуру нашей организации и добавить отделы Бухгалтерия и Кадры. 6. Изменить штатное расписание и добавить должности Главного бухгалтера и Инспектора отдела кадров. 7. Принять на работу в эти отделы Зайцева и Волкова. 8. Принять с 01.03 двух практикантов на работу в отделы Кредитный и Бухгалтерия и через месяц поменять практикантов местами. СОТРУДНИКИ ОТДЕЛЫ ДОЛЖНОСТИ КодС Фамилия КодО Отдел КодД Должность 1 Иванов 1 Кредитный 1 Управляющий 2 Петров 2 Валютный 2 Пом. управляющ 3 Сидоров 3 АХО 3 Гл. упраляющ. 4 Птичкина 4 Бухгалтерия 4 Гл. бухгалтер 5 Синичкина 5 Кадров 5 Инспектор ОК Практикант Бухгалтер 6 Зайцев 6 7 Волков 7 Для того, чтобы выполнить операции 1–8 нам придется создать дополнительную таблицу «Журнал приказов». 94  Порядок выполнения задания 1. Откройте в проводнике папку, где хранится файл БД «Кадры». Создайте копию БД «Кадры», переименовав файл «копия_Кадры.mdb» в файл «Кадры с приказами.mdb». 2. Откройте новую БД «Кадры с приказами». Она ни чем не отличается от предыдущей БД «Кадры». Постройте в КОНСТРУКТОРЕ новую таблицу «Журнал приказов» на основе следующей структуры: Ключ Имя поля Тип данных Размер/Примечание П.Кл КодПрик Счетчик Номер приказа Вн.Кл Вн.Кл Вн.Кл Код Сотрудника Код Отдела Код Должности Тип Приказа Дата Приказа Надбавка Числовой Числовой Числовой Числовой Дата/время Денежный Длинное целое Байт Целое Байт Надбавка в рублях 3. Удалите все старые связи в схеме данных БД «Кадры с приказами»: между таблицами «Отделы», «Сотрудники» и «Должности». Для этого откройте Схему данных, выделите курсором каждую связь и в контекстном меню выберите команду «Удалить». 4. Добавьте в Схему данных таблицу «Журнал приказов». 95 5. Удалите ненужные, уже имеющиеся в «Журнале приказов» поля из таблицы «Сотрудники»: внешние ключи [Код_Должности] и [Код_Отдела], а также поле [Надбавка]. 6. Создайте новые связи между таблицами «Журнал приказов» и «Сотрудники», «Журнал приказов» и «Отделы», «Журнал приказов» и «Должности» на основе первичных (П.Кл) и внешних ключевых (Вн.Кл) полей. Установите свойство «Обеспечение целостности данных» для новых связей. См. упр. 3. 7. Определите подстановки в таблице «Журнал приказов» для внешних ключей. См. упр. 6. 8. Задайте подстановку в поле [Тип Приказа] на основе фиксированного списка. Посмотрите в упр. 6 аналогичную подстановку, например, в поле [Семейное положение]. Таблица фиксированных значений для «типа приказа» выглядит следующим образом: 1 Принят 2 Переведен 3 Уволен 9. Задайте для поля [Дата Приказа] значение по умолчанию равное текущей дате. Вспомните, какая функция возвращает текущую дату, мы ее использовали в выражении для вычисления возраста. 10. Удалите все старые запросы и формы кроме трех форм «Штатное расписание», «Карточка сотрудника», «Структура организации». Отредактируйте форму «Карточка сотрудника», удалив из нее поля, более не принадлежащие таблице «Сотрудники». 11. Создайте новую форму с именем «Журнал приказов» ленточного вида для заполнения таблицы «Журнал приказов». С ее помощью занесите информацию о перемещениях сотрудников. Можно воспользоваться примером перемещений «Журнал приказов». 96 Журнал приказов Код приказа 1 Иванов Кредитный Управляющий Тип Прика- Дата Приза каза Принят 1 января 2 Петров Кредитный Пом. управляющего Принят 1 января 1 тыс. руб 3 Сидоров Валютный Пом. управляющего Принят 1 января 1 тыс. руб 4 Птичкина Валютный Управляющий Принят 1 января 1 тыс. руб 5 Синичкина Административный Кадров Зайцев Главный управляющий Принят 1 января 0 тыс. руб Инспектор отдела кадров Принят 1 января 1 тыс. руб Главный бухгалтер Принят 1 января 1 тыс. руб Главный управляющий Уволен 1 февраля 0 тыс. руб Главный управляющий Переведен 1 февраля 2 тыс. руб 6 Код Сотрудника Код Отдела Бухгалтерия Код Должности Надбавка 1 тыс. руб 7 Волков 8 9 Синичкина Административный Птичкина Административный 10 Сидоров Валютный Управляющий Переведен 1 февраля 2 тыс. руб 11 Студент1 Бухгалтерия Практикант Принят 1 марта 0 тыс. руб 12 Студент2 Кредитный Практикант Принят 1 марта 0 тыс. руб 13 Студент1 Кредитный Практикант Переведен 1 апреля 0 тыс. руб 14 Студент2 Бухгалтерия Практикант Переведен 1 апреля 0 тыс. руб 15 Студент1 Кредитный Практикант Уволен 1 мая 1 тыс. руб  Внимание. При заполнении «Журнала приказов» необходимо учитывать, что даты приказов должны быть согласованы и идут по возрастанию. Под согласованностью подразумевается, что первая запись о сотруднике – это запись о его приеме на работу и последующие записи о перемещениях имеют даты позднее даты его приема, а дата увольнения этого сотрудника является самой поздней. 97 12. Создайте простой запрос «Движение сотрудников», в который соберите из всех таблиц все поля, кроме полей внешних и первичных ключей. Сортировка по дате приказа. Проверьте, что данный запрос правильно выводит информацию. 13. Создайте новый запрос «Движение сотрудников с зарплатами», содержащий следующие поля:  [Код_Сотрудника], [Фамилия], [Должность], [Отдел], [Тип Приказа], [Дата Приказа], [Надбавка] и [Оклад]  Зарплата:=Вычисляемое поле (Для удобства дальнейшей работы постройте выражение c фиксированным значением курса, исключив свободное поле [Курс]). 14. Для определения количества принятых, переведенных и уволенных сотрудников создайте в запросе «Движение сотрудников с зарплатами» три дополнительные колонки [Приняты], [Переведены], [Уволены]. Постройте вычисления в них так, чтобы в каждой из них стояли 1, если [Тип приказа] соответствует наименованию колонки, и 0, если тип приказа другой. Для данной задачи используйте в Построителе условный оператор: ->Встроенные функции->Управление-> IIf(«expr»;«truepart»;«falsepart»).  Вычисляемое выражение Приняты: =IIf([Тип приказа]=1;1;0)  Вычисляемое выражение Переведены: =IIf([Тип приказа]=2;1;0)  Вычисляемое выражение Уволены: =IIf([Тип приказа]=3;1;0) 15. Для выборки сотрудников, работающих в АКБ «Буревестник» на текущую дату, создайте запрос «Список работающих», в котором отберите последние записи о каждом 98 сотруднике (группировка по [Код_Сотрудника], в остальных колонках функция Last) и исключите уволенных. Рассчитайте зарплату каждого работающего. 16. Создайте отчет с итогами «О текучести кадров в отделах» на основе запроса «Движение сотрудников с зарплатами» с группировкой по отделам и сортировкой по [Фамилии]. Итоги подведите суммированием значений в полях: [Приняты], [Переведены], [Уволены]. 17. Создайте отчет «Фонд оплаты труда» на текущую дату, используя в качестве источника запрос «Список работающих». Группировка по должностям, итоги по полю [Зарплата]. В заголовке отчета должна автоматически проставляться дата его составления.  Самостоятельно  Совет. Общее правило подготовки отчета – сначала сконструировать запрос, отбирающий нужную информацию, сортирующий ее по выбранному полю, производящий необходимые вычисления в дополнительных вычисляемых колонках запроса, и только затем строить на его основе отчет. Хотя MS Access позволяет строить отчет, оперируя полями, взятыми из таблиц, это все равно приводит к построению запроса, который, правда, скрыт от пользователя. 1. Постройте отчет о текучести кадров в определенном году. Он похож на запрос п.16, однако, нужно обеспечить выборку записей «Журнала приказов», относящихся только к определенному году, вводимому пользователем. Этот год должен отображаться в заго99 ловке отчета. 2. Создайте новый запрос «Льготы по зарплате сотрудников», скопировав запрос «Список сотрудников». В КОНСТРУКТОРЕ добавьте в запрос следующие поля: [Дети] и [Пол]. Вычислите поле [Льготы]: IIf(AND([Дети]>0, [Пол]=женский); 13%[Дети]*[МРОТ];0). Это структура вычислений, а не готовая формула. 3. Создайте новый запрос «Адреса сотрудников» на основе запроса «Движение сотрудников с зарплатами» со следующими полями: a. [Фамилия], [Имя] – используются для выражения Сотрудник:=[Фамилия] +” ”+Left([Имя];1)+”.” b. Возраст:=Вычисляемое поле (Вычисляет возраст), c. [Адрес], d. [Телефон домашний]. 4. Откройте запрос для просмотра. Информация выводится правильно, но записи о сотрудниках повторяются столько раз, сколько у него было переводов. Для устранения повторений введите группировку по полю [Фамилия], а для всех остальных полей определите в строке «Группировка» функцию Last, которая выводит последнюю запись. 5. Постройте отчет на основании запроса «Адреса сотрудников». О сотрудниках должна выводиться только информация: фамилия, инициалы, возраст, адрес и телефон. В итогах отчета должно указываться общее количество сотрудников в списке отчета. 100 Упр. 14. Самостоятельная работа Спроектировать ИС «Контроль поручений». Создать для нее новую БД «КП.mdb». ИС должна вести учет и хранить информацию о выполняемых поручениях сотрудниками АКБ «Буревестник». Пример информации о поручениях сотрудников – в таблицах. ПОРУЧЕНИЕ Составление Бизнес-плана Банка Анализ Бизнес-плана заемщика Подготовка договора с заемщиком Финансовый анализ баланса заемщика 1. ВЫПОЛНЯЕТ Иванов 2. 1. 2. 3. 1. Птичкина Иванов Петров Птичкина Сидоров 1. Сидоров 2. Петров 3. Иванов СОТРУДНИК Иванов Птичкина Петров 1. 2. 3. 1. 2. 1. 1. Сидоров 2. 3. 101 ВЫПОЛНЯЕТ Финансовый анализ баланса заемщика Составление Бизнес-плана Банка Анализ Бизнес-плана заемщика Анализ Бизнес-плана заемщика Составление Бизнес-плана Банка Анализ Бизнес-плана заемщика Финансовый анализ баланса заемщика Подготовка договора с заемщиком Финансовый анализ баланса заемщика Новая БД будет включать следующие таблицы:  «Сотрудники» – скопируем таблицу «Сотрудники» из файла «Кадры.mdb»  «Список поручений» – новая таблица.  «Поручения сотруднику» – новая (третья) таблица, через которую разрешается проблема связи «Многие-ко-многим».  Порядок выполнения задания 1. Создайте в режиме КОНСТРУКТОРА новые таблицы: Структура таблицы «Список поручений»: КодП Счетчик (ключ) – код поручения Поручение Текст (20 символов) Стоимость Денежный, Формат/Маска ##.###« тыс. руб.» Премия Числовой (Байт), Формат/Маска ##«%» Структура таблицы «Поручения сотруднику»: КодПС Счетчик (ключ) – код поручения сотруднику КодП Числовой (Длинное целое) – Внешний ключ, подстановка из таблицы «Список Поручений» КодС Числовой (Длинное целое) – Внешний ключ, подстановка из таблицы «Сотрудники» ДатаПоруч Дата/Время (Условие на значение >= сегодняшней дате; значение по умолчанию равно сегодняшней дате) 102 СрокИсп ОтмОбИсп Число (Байт), Формат поля: ##« дней» Логическое, Подстановка: Список значений: -1; «Выполнено»; 0; «Не выполнено»; значение по умолчанию соответствует состоянию не выполненного поручения. 2. Создайте с помощью мастера простые формы для ввода информации в таблицы «Список Поручений» и «Поручения Сотруднику». 3. Используя созданные формы, заполните таблицы данными из приведенных контрольных примеров: «Список поручений» и «Поручения сотруднику». КодП 1 2 3 4 НазвПоруч Составление Бизнес-плана Банка Анализ Бизнес-плана заемщика Подготовка договора с заемщиком Финансовый анализ баланса заемщика Пример таблицы «Список поручений» 103 Стоимость 10 тыс. руб. 15 тыс. руб 5,5 тыс. руб 50 тыс. руб Премия 10% 15% 5% 15% КодП КодС ДатаПоруч 1 1 2 2 2 3 4 4 4 Иванов Птичкина Иванов Петров Птичкина Сидоров Сидоров Петров Иванов 01.MM.YY 01.MM.YY 03.MM.YY 03.MM.YY 03.MM.YY 07.MM.YY 09.MM.YY 09.MM.YY 09.MM.YY СрокИсп 7 дней 14 дней 3 дней 1 дней 2 дней 3 дней 12 дней 13 дней 14 дней ОтмОбИсп Не выполнено Не выполнено Не выполнено Не выполнено Не выполнено Не выполнено Не выполнено Не выполнено Не выполнено Пример таблицы «Поручения сотруднику» Внимание: все даты относятся к текущему (YY) году, MM – текущий месяц +1. 4. Создайте с помощью МАСТЕРА сложные формы на основе всех полей из таблиц: «Список поручений», «Сотрудники», «Поручения сотруднику»:  по поручениям,  по сотрудникам. 5. Проведите вычисления в новых (свободных) полях:  количество поручений одного сотрудника и количество сотрудников, имеющих одинаковые поручения (Встроенные функции, статистические –> Count(«expr»)) 104  даты исполнения поручения для каждого сотрудника и максимальную дату исполнения поручения (Встроенные функции, статистические –>Max(«expr»)) 6. Создайте с помощью МАСТЕРА запросы:  подробный запрос, включающий фамилии сотрудников, их должности, названия поручений, дату поручения, отметку об исполнении с условием отбора только тех поручений, срок исполнения которых наступит завтра;  запрос с итогами премии, выплачиваемой каждому сотруднику за все выполненные проекты месяца. 7. Создайте с помощью МАСТЕРА отчет с итогами:  группировка по поручениям с указанием сотрудников, даты назначения поручения и даты исполнения, отметки о выполнении, с расчетом среднего срока выполнения поручения;  группировка по сотрудникам с выводом списка всех, уже выполненных каждым сотрудником поручений, расчетом суммы премии каждого сотрудника и итоговой суммы премий по организации. 105 Контрольная работа Проектирование БД, имеющей между таблицами связь «Многие-ко-многим» Задание: Построить базу данных, в которой можно сохранить информацию, указанную в таблицах, а также обозначенную в названии задания и показанную стрелками на эскизе. (Вариант «Z101» на стр. 108).  Порядок выполнения задания 1. Нарисовать эскиз схемы данных, в котором указать название для каждой таблицы. Далее перечислить все имена полей (в столбик), входящих в каждую таблицу. Рядом с каждым именем поля написать тип данных и размер поля. При определении типа и размера поля необходимо опираться на данные приведенного примера из таблиц вашего варианта. Определить необходимые ключевые поля (Первичные ключи) и пометить их словом (Ключ). Добавить необходимые Внешние ключи. Нарисовать необходимые связи между таблицами. На связях нужно подписать 1 и М, стрелки линий должны касаться конкретных полей, первичного и внешнего ключей (см. упр. 3). Предоставить эскиз схемы данных на проверку преподавателю, и только после этого переходить к следующему пункту. 106 2. Загрузить программу MS Access, сохранить в своей папке файл БД с именем «Контрольная.mdb». 3. Создать в КОНСТРУКТОРЕ все необходимые таблицы. 4. Для полей, указанных в варианте контрольной работы, использовать маски ввода, форматы и условия на значения. 5. Реализовать в таблицах механизм подстановки для внешних ключей и полей, требующих выбора из фиксированного списка. 6. Отредактировать схему данных с указанием обеспечения целостности данных. 7. Создать простые формы для всех таблиц. Заполнить таблицы с помощью простых форм, включающих все поля каждой таблицы. 8. Построить сложную форму (главная с подчиненной), в соответствии с условиями варианта. Выполнить предложенные вычисления в форме. 9. Создать указанный в задании варианта запрос. 10. Сформировать отчет с группировкой и итогами. 107 Пример задания на контрольную работу. Вариант: Z101 Предметная область: Учет товаров, купленных клиентом. Товары, которые купили клиенты: Иванов (пылесос, утюг); Петров (утюг); Сидоров (пылесос, утюг, фен); Иванова (пылесос, утюг, монитор); Петрова (утюг, монитор, принтер); Галкина (пылесос). Эскиз задания ДатаПокупки 01.01.2000 01.03.2000 01.05.2001 01.11.2001 01.12.2001 01.12.2002 Телефон Клиент Товар Цена Категория* (495) 111-22-33 (499) 222-33-44 (495) 333-44-55 (816) 444-55-66 (488) 555-66-77 (495) 123-45-67 Иванов Петров Сидоров Иванова Петрова Галкина Пылесос Утюг Фен Монитор Принтер 2 500.55 руб 1 500.66 руб 1 600.77 руб 5 000.88 руб 3 500.99 руб Бытовая Бытовая Бытовая Компьютерная Компьютерная Дополнительные свойства полей Условия на значения в поле: [ДатаПокупки] не может быть позднее сегодняшнего числа. Маска/формат: данные должны быть представлены в соответствии с эскизом. Поля с фиксированным списком значений: помечены звездочкой (*) Вычисления в форме: сумма общей скидки на заказанные товары с учетом 5% дисконта на все товары. Запрос с условием отбора: все товары, купленные до 2002 года. Отчет с группировкой и итогами: сгруппировать купленные товары по клиентам и подсчитать количество товарных позиций для каждого клиента. 108 Автор(ы):доцент кафедры Информатики и ГИС, к.ф.-м.н. Е.А. Оборнев Рецензент(ы): Карпов В.А. профессор, Факультета финансов и банковского дела РАНХ и ГС при президенте РФ. Белов А.В. профессор кафедры кибернетики Московского института экономики и математики (МИЭМ). Программа обсуждена на заседании кафедры информатики и геоинформационных систем Дата __.__.20__ г. Зав. кафедрой Л.З.Бобровников Программа одобрена на заседании Ученого совета геологоразведочного факультета Дата __.__.20__ г. Протокол № 2 Декан ГФФ Л.З.Бобровников 109 Учебное издание Оборнев Евгений Александрович Основы проектирования баз данных для информационных систем Теория. Лекции. Лабораторный практикум. Част 1 Учебное пособие для проведения практических занятий Москва 2012 Корректор М.А. Давыдова Подписано в печать Формат 60х90/16 Тираж 100 экз. Издательский №____ Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования «Российский государственный геологоразведочный университет имени Серго Орджоникидзе» (МГРИ - РГГРУ) 117997, г. Москва, ул. Миклухо-Маклая, д. 23 тел.(495) 433-62-56 (канцелярия) http://www.msgpa.ru e-mail: office@msgpa.edu.ru 110