Контрольная работа по "Информатике". 254

Базы данных и информационные системы

Основы реляционной модели данных

Теория. Основным объектом хранения информации в реляционной модели является таблица. Для таблицы в реляционной модели используется специальный термин — ОТНОШЕНИЕ, чтобы подчеркнуть, что не любая таблица годится для использования в реляционной БД. Таблица может быть названа отношением, если:

• каждое отношение (таблица) имеет собственное имя и строится для описания конкретного объекта из выбранной предметной области реального мира («Товар на складе», -Заказы клиента»). В ней отражается сущность предмета или явления, характеризующего данный объект, и хранится информация о конкретных представителях данного объекта;

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

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

Конструируя таблицу, рекомендуется на первое место поставить ключевое (уникальное) поле, а за ним — поля, раскрывающие содержание ключа, например: [Код Сотрудника] — [Фамилия], [Код Товара] — [Название товара].

Пример построения информационной системы

Рассмотрим пример ведения учета персонала в отделе кадров некоторого условного предприятия: Акционерного коммерческого банка (АКБ) «Буревестник».

Полное наименование информационной системы

Система ведения учета персонала — ИС «Кадры». Разрабатывается на базе СУБД MS Access.

Цель автоматизации

Ведение учета персонала АКБ «Буревестник».

Основные задачи

Ввод данных о сотрудниках и их назначениях. Внесение изменений в информацию.

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

Ведение списков должностей.

Хранение описания организационной структуры (списка отделов).Отдел кадров АКБ «Буревестник» предоставил следующий документ (орфография документа сохранена):

Список сотрудников АКБ «Буревестник»

СОТРУДНИКИ КРЕДИТНОГО ОТДЕЛА  (ТЕЛ.  ОТДЕЛА ДОБ 334)

Иванов, Иван, Москва, Сиреневый б., д.1, к.11,8-916 123-45-67, 01.01.1960, Холост, детей нет, Мужской, Управляющий, 35 ООО RUB + 50 долл. надбавка.

Петр Иванович Петров, Цветной б., д2, к. 22,8-095 456-71-23,02.02.1965, Семейный, Есть дети, Муж, Помощник управл.,12 000 RUB, надб. $ 100

СОТРУДНИКИ ВАЛЮТНОГО ОТДЕЛА  (ТЕЛ.  ОТДЕЛА ДОБ 335)

Сидоров, Петр, Малиновый пр. д.З к.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-67-12,05.05.1930,Вдова, 3, Жен, Главный управляющий,25 000 RUR

Задание 1

Проанализируйте (совместно с преподавателем) информацию, представленную в документе «Список сотрудников АКБ». Разбейте информацию на атомарные единицы, характерные для всей сущности и определите необходимые поля для размещения данных в таблице-отношении. Дайте имя отношению и придумайте имена для каждого его поля.

Задание 2

Самостоятельно. Подберите типы и размеры для всех полей таблицы, исходя из анализа информации в документе «Список сотрудников АКБ "Буревестник"». Выберите поле или группу полей, отвечающих требованиям первичного ключа.

Рис. 1. Таблица для анализа первичной информации из документа «Список сотрудников АКБ "Буревестник"» в процессе проектирования ИС «Кадры».

Рис. 1 (продолжение) Таблица для анализа первичной информации из документа «Список сотрудников АКБ «Буревестник» в процессе проектирования ИС «Кадры»

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

Нормализация таблиц — методика устранения избыточности

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

• выберите и удалите из анализируемой (первой) таблицы группу полей функционально независимых от первичного ключа. Как правило, они заполнены повторяющимися значениями. Это можно сделать на основе правила: «Если некоторую информацию можно хранить и обрабатывать независимо от ключевого поля, то она отражает сущность другого объекта и должна хранится в отдельной таблице»;
• создайте новую (вторую) таблицу на основе этих полей. Определите для нее ключевое поле. Записи в данной таблице будут заполнены только уникальными значениями полей. Повторы исключены;
• дайте новые названия для первой и второй таблиц и определите, какой тип связи между ними. Как правило, там, куда были перемещены повторяющиеся поля, будет связь ОДИН, а в первой таблице — МНОГО;
• добавьте в первую таблицу новое поле для связи ее со второй таблицей, как простое <не ключевое) поле, имеющее тот же тип данных и размер, что и ключевое поле второй таблицы. Обычно его называют полем внешнего (вторичного) ключа (МНОГО);

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

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

Рисунок – БД «Борей»

Упр. 1. Нормализация исходной таблицы

Теория. Выбираем из таблицы для анализа первичной информации (см. рис. 1) поля [Отдел] и [Телефон отдела]. Они функционально не зависят от первичного ключа [Фамилия]. Они заполнены повторяющимися значениями, что вызывает избыточность. На первый взгляд в нашем примере избыточность не очень большая. Но представим, что каждый отдел характеризуется не только наличием телефона, а также сведениями о его номере, местонахождении, площади занимаемых помещений, начальнике отдела, уровне подчинения и т.д. В этом случае для каждого нового сотрудника мы вынуждены были бы в такой таблице повторять всю информацию об атрибутах отдела. Если бы какой-то атрибут изменился (например, телефон отдела), то это нужно было бы поменять во всех записях о сотрудниках из данного отдела (аномалия обновления). При этом также возникают аномалии вставки и удаления. Мы не можем вставить информацию об отделе [Отдел]=Бухгалтерия. пока не появился сотрудник из данного отдела, определяющий первичный ключ. При удалении записи о сотруднике, например, по фамилии [Фамилия]=Синичкина, «рвется информация об отделе [Отдел] Административный.

Самостоятельно. Проанализируйте связь группы полей [Должность] и [Оклад] с привычным ключом [Фамилия]. Определите новые таблицы. Дайте имена таблицам — информационным объектам. Определите в них (создайте в каждой) первичное ключевое поле.

Задание 3

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

^{ТАБЛИЦЫ — объекты хранения данных}

Теория. Сведения о представителях (экземплярах) каждой сущности из выбранной предметной области в БД записываются в отдельную таблицу — основной объект хранения структурированной, типизированной информации определенного размера. При работе с данными из нескольких таблиц устанавливаются связи между таблицами на основе специальных ключевых полей: первичного и внешнего ключа.

Упр. 2. Создание таблиц

Постройте с помощью КОНСТРУКТОРА БД, состоящую из совокупности трех таблицах «Сотрудники», «Должности», «Отделы», используя результат деления информации на части (Задание 3).

 Порядок выполнения упражнения

1. Загрузите программу MS Access.
2. Создайте новую БД.
3. Сохраните файл БД с именем «Кадры.mdb» в свою рабочую папку.
4. Создайте с помощью КОНСТРУКТОРА последовательно три таблицы: «Сотрудники». «Должности», «Отделы».
5. Откройте КОНСТРУКТОР для каждой новой таблицы и определите в нем основные свойства полей (см. Задание 3): имя поля 1, тип данных 2, размер поля 3 (см. иллюстрацию ниже)
6. . Определите в каждой таблице специальным символом поле первичного ключа (контекстное меню — > Ключевое поле).
7. Для числовых значений в полях [Пол] и [Семейное положение] создайте кодификаторы (справочники) соответствия чисел и их текстового описания. Например, 0 — «Не семейный», 1 — «Семейный», 2 — «В разводе» и т.д.

СВЯЗИ между таблицами

Связь «Один-ко-многим»

Теория. Обратимся к результату выполнения упр. 2. Теперь, чтобы обозначить принадлежность сотрудника отделу и занимаемую им должность, нужно указать соответствующие значения первичных ключей [Код_Отдела] и [Код_Должности] во внешних ключах таблицы «Сотрудники». Используя значения внешних ключей, можно получить всю недостающую информацию (например, об окладе или рабочем телефоне) из соответствующих таблиц «Должности» и «Отделы».

Возникает вопрос: как показать СУБД, что значения для внешнего ключа [Код_Отдела] нужно искать в таблице «Отделы». Для правильной работы этого механизма необходимо построить Схему данных, отображающую взаимосвязи между таблицами, т.е. установить СВЯЗЬ между внешним ключом [Код_Отдела] в таблице «Сотрудники» и первичным ключом [Код_Отдела] в таблице «Отделы». При этом, очевидно, что единственному, уникальному значению из ключевого поля [Код_Отдела] в таблице «Отделы» может соответствовать много записей с этим же значением в таблице «Сотрудники». Другими словами выполняется условие: в одном отделе работает много сотрудников. То же самое относится и к должности: одну должность имеют разные сотрудники из разных отделов. Такой тип называется «Один-ко-многим» (1→∞). Дополнительно можно «поручить» СУБД поддерживать целостность такой связи, означает, что СУБД будет следить за значением внешнего ключа при создании или изменении любой записи в таблице, находящейся на стороне связи «-ко-многим», и проверять, есть ли запись в таблице на стороне «Один-» с таким значением первичного ключа. Если нет, то СУБД не позволит создать/изменить такую запись.

Например, нельзя сделать запись о новом сотруднике [Фамилия]=’Волков’ в таблице «Сотрудники» с назначением на [Должность]=’Главный бухгалтер’ в новый [Отдел]=’Бухгалтерия’, до тех пор, пока еще нет соответствующих записей в таблицах «Должности» и «Отделы». Аналогично, нельзя удалить запись о некотором отделе в таблице «Отделы», если в таблице «Сотрудники» есть записи о сотрудниках, работающих в данном отделе.

Связь «Многие-ко-многим»

Теория. Если посмотреть на пример из упр. 2 с другой стороны, то между таблицей «Должности» и таблицей «Отделы» возникает связь «Многие-ко-многим» (∞<->∞). Она реализует условие, что много сотрудников с одной должностью могут работать в разных отделах. И, наоборот: в одном отделе могут работать сотрудники с разными должностями. Реляционная СУБД не может обеспечить целостную связь типа «Многие-ко-многим» между двумя таблицами. Для этого ей необходима третья таблица, в которой будут установлены две связи типа «Один-ко-многим»/

Например, в нашем случае такой третьей таблицей является таблица «Сотрудники».

Связь «Один-к-одному»

Теория. Если собрать дополнительные сведения у сотрудников (например, об образовании, увлечениях и т.д.), то для них можно создать дополнительную таблицу со связью «один-к-одному» (1<->1) на основе первичного ключа [КодСотрудника].

Упр. 3. Создание связей

Определите и установите связи между таблицами так, чтобы информация в БД отвечала всем требованиям нормализации.

Порядок выполнения задания

1. Закройте все открытые таблицы или конструкторы таблиц, так как создавать или изменять связи между открытыми таблицами нельзя.
2. На панели инструментов нажмите кнопку «Схема данных» или выберите пункт основного меню «Сервис» — > «Схема данных».

3. Если в БД до этого никаких связей не определялось, автоматически будет открыто диалоговое окно Добавление таблицы. Если нужно добавить таблицы для создания связей, а диалогового окна Добавление таблицы на экране нет, нажмите кнопку «Отобразить на панели инструментов. Если таблицы, между которыми нужно создать связь уже отображены, перейдите к шагу 5.

4. Дважды щелкните имена таблиц, для которых требуется определить связи. Затем закройте диалоговое окно Добавление таблицы. Повторно добавленные таблицы можно Для этого необходимо указать на них курсором и, вызвав контекстное меню правой клавишей мыши, выбрать команду Скрыть. (Смотрите иллюстрацию ниже)

5. Выберите ключевое поле таблицы «Отделы» со стороны «Один-» создаваемой связи и перетащите его, удерживая нажатой левую клавишу мыши, совместив с именем поля нужного внешнего ключа в таблице «Сотрудники» со стороны «-КО-МНОГИМ».

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

6. Самостоятельно определите связь для оставшихся таблиц «Должности» и «Сотрудники».

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

ФОРМА — объект обработки и представления данных на экране

Теория. Под процессом обработки информации в ИС прежде всего понимаются следующие процедуры: ввод, просмотр, выборка некоторого подмножества данных, сортировка, группировка, изменение (исправление), расчет производных параметров и итогов. Все эти действия можно делать с помощью объектов СУБД ФОРМЫ. Для одной БД может быть разработано сколько угодно форм в зависимости от поставленных обработки информации. При удалении формы удаляется процедура обработки, графическое оформление, но информация из БД не исчезает, так как хранится в объектах другого типа — в таблицах.

Чтобы ввести данные примера (см. рис. 1), необходимо создать как минимум три формы: по одной форме для каждой таблицы, сконструированной в упр. 2. Начинать ввод следует с таблицы, находящейся на стороне «Один-» связи «Один-ко-многим». Она является «главной», из нее подставляются значения первичного ключа в поле внешнего ключа «подчиненной» таблицы. И только затем создаются и заполняются формы для таблиц у которых в линии связи стоит «-ко-многим». В нашем случае следует начинать «Отделы» и «Должности».

Формы можно создавать двумя способами: с помощью конструктора и с помощью Механизм мастера относится к категории CASE-технологий. Эта технология концепцию «программирование без алгоритмического языка». Мы будем пользоваться МАСТЕРОМ на начальном этапе построения, а затем переходить в КОНСТРУКТОР, если потребуются изменения и дополнения.

Упр. 4-ф. Создание форм

Создайте формы с помощью МАСТЕРА для ввода информации в БД «Кадры». Необходимо сделать по одной форме для каждой таблицы для ввода и редактирования данных.

Порядок выполнения задания

1. Выбираем в основном рабочем окне БД «Кадры» объект ФОРМЫ.
2. Вначале создадим формы для таблиц, у которых в связи стоит «Один-» — это таблицы «Должности» и «Отделы».
3. Запускаем команду Создание формы с помощью мастера для создания формы на основе полей из таблицы «Должности».
4. Из раскрывающегося списка Таблицы и запросы выберите название таблицы «Должности».
5. Переместите все поля из списка Доступные поля в окно списка Выбранные поля.

Нажмите кнопку «Далее».

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

6. Выберите внешний вид для данной формы — ленточный.

Для форм с малым количеством полей обычно выбирают табличное представление данных (вид ленточный или табличный).

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

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

5. Стиль формы (графическое оформление) выберите самостоятельно.
6. Назовите созданную форму «Штатное расписание». Имя формы всегда выбирайте (не доверяя мастеру), исходя из смысла решаемой задачи.
7. С помощью созданной формы заполните таблицу «Должности» контрольными данными из таблицы «Кадры» (см. рис. 1). 
   

Обратите внимание, что для поля [Код Должности] требуется придумать значения, так как их нет в таблице на рис. 1. Поле [Код Должности] —поле первичного ключа, все значения должны отличаться, иначе возникнет ошибка, связанная с условием уникальности значения ключевого поля.

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

Строка с обозначением * (звездочка)предназначена для новых данных.

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

Создайте форму «Структура организации» для таблицы «Отделы». Выберите все поля таблицы «Отделы» и вид — табличный.

8. Заполните таблицу «Должности», используя созданную форму, тестовыми данными из примера (см. рис. 1). Необходимо выбирать из таблицы неповторяющиеся; значения.

Внимание. При вводе информации в поле [Телефон отдела] мы не пишем слово «Доб» (добавочный), т.к. для данного поля определен тип поля Текстовый и Размер 3 символа. Приставка «Доб» перед номером является избыточной, повторяющейся во всех записях таблицы Отделы, ее незачем хранить. Мы добавим эти символы в описании Маски ввода и Формата поля в упр. 5.

10. Создайте форму «Карточка сотрудника» для ввода информации в таблицу «Сотрудники». Для данной формы выберите все поля таблицы «Сотрудники» и определите вид формы в один столбец.

С помощью созданной формы внесите данные обо всех сотрудниках, используя документ «Список сотрудников АКБ» (Задание 1).

При заполнении внешних ключей данной формы вам понадобится информация о кодовых значениях из таблиц «Отделы» и «Должности». Для удобства откройте все три созданные формы и расположите их рядом на экране. Это позволит правильно вводить данные в поля [Код Отдела] и [Код Должности].

При заполнении полей [Пол] и [Семейное положение] необходимо использовать из упр. 2.

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