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

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

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

 

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

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

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

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

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

 

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

 

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

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

Система ведения учета персонала — ИС «Кадры». Разрабатывается на базе СУБД 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,к. 11	8-916-123-45-67	01.01.1960	Холост	0	Муж	$50 US
	ПЕТРОВ	Петр	Цветной б., д. 2, к. 22	8-495-456-71-23	02.02.1965	Семейный	1	Муж	$ 100 US
	СИДОРОВ	Петр	Малиновый пр. д. 3, к. 33	8-903-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	Жен	$0US
Размер

 

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

 

Имя поля→	Должность	Оклад	Отдел	Телефон отдела
Тип данных
	Управляющий	15 000RUR	КРЕДИТНЫЙ	доб 334
	Помощник управляющего	12 000 RUR	КРЕДИТНЫЙ	доб 334
	Помощник управляющего	12 000RUR	ВАЛЮТНЫЙ	доб 335
	Управляющий	15 000RUR	ВАЛЮТНЫЙ	доб 335
	Главный управляющий	25 000 RUR	АДМИНИСТРАТИВНЫЙ	доб 333
Размер

 

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

 

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

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

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

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

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

 

 

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

 

 

 

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

 

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

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

Задание 3

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

 

 

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

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

 

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

 

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

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

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

 

 

 

 

 

 

 

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

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

 

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

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