Файл: Тема Информационные технологии обработки баз данных.pdf

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

СУБД общего назначения — это очень сложные программные комплексы, предназначенные для выполнения всей совокупности функций по созданию и эксплуатации баз данных. Основные разработки СУБД принадлежат фирмам Microsoft (Foxpro-DOS/WIN, Access) и Borland
(Paradox-DOS/WIN).
Специализированные СУБД создаются в тех случаях, когда невозможно или нецелесообразно использовать СУБД общего назначения, например, информационно-поисковые системы (Консультант+; Кодекс;
Гарант).
Правила организации информации называют модель данных, под которой понимают совокупность структур данных и операций над ними.
Существует три типа моделей данных иерархическая, сетевая и реляционная.
1. Иерархическая модель – ориентированный граф. Основная идея - каждая запись имеет свой путь от корневой записи. В иерархической модели данных используются только вертикальные линии связи подчинения между узлами данных на разных уровнях. Каждый узел может иметь любое количество связей с подчиненными узлами на нижнем уровне и только одну связь с родительским узлом на верхнем уровне.
2. Сетевая модель – неориентированный граф. Основная идея - каждая запись может быть связана с другой записью. В сетевой модели используются вертикальные и горизонтальные связи подчинения между узлами данных на разных уровнях. Каждый узел может быть связан с любым другим узлом на любом уровне.
3. Реляционная модель – таблица, которая представляет совокупность записей, которые являются совокупностью именованных полей. Основная идея – представить произвольную структуру данных в виде двумерных таблиц. Понятие реляционный модели (relation - отношение) связано с разработками известного специалиста в области баз данных Кодда.

В настоящее время наибольшее распространение получили БД, основанные на реляционной модели.
Базы данных классифицируются и по другим признакам: по месту хранения, способу доступа к данным и архитектуре.
Классификация баз данных по месту хранения.
По технологии обработки базы данных могут подразделяться на централизованные и распределенные.
Централизованная база данных хранится на одной ЭВМ, которая является компонентом сети.
Распределенная база данных состоит из нескольких, возможно пересекающихся или даже дублирующих друг друга, хранимых на разных
ЭВМ вычислительной сети.
Классификация баз данных по способу доступа
По способу доступа к данным базы данных разделяются на базы
данных с локальным доступом и базы данных с удаленным (сетевым)
доступом.
Классификация баз данных по архитектуре
Локальная архитектура реализует централизованное хранение и локальный доступ к данным. Обычно это БД на персональном компьютере.
Архитектура файл-сервер реализует централизованное хранение и сетевой доступ к данным. Данные хранятся на центральной ЭВМ, которая называется Сервером файлов, и совместно используются пользователями
Рабочих станция, для которых реализован сетевой доступ с помощью локальной сети. При большой интенсивности доступа к одним и тем же данным производительность информационной системы падает.
Архитектура клиент-сервер реализует хранение распределенной базы данных в памяти нескольких мощных серверов баз данных, и совместное использование данных Клиентами, которые работают на рабочих станциях. Запросы клиентов к сетевой базе данных

обрабатываются на Сервере приложений. Извлеченные данные транспортируются по сети на рабочие станции для формирования отчетов, форм и диаграмм с помощью сетевых приложений баз данных
Генераторов отчетов. Архитектура клиент-сервер характеризуется высокой производительностью обработки и передачи данных и надежностью функционирования.
На лекции мы сосредоточимся на изучении наиболее распространенной и доступной сегодня СУБД Access из MS OFFICE.
СУБД MS Access – прикладной пакет программ, который предназначен для создания и управления локальными и сетевыми реляционными базами данных, их обработки, сортировки, фильтрации, группировки, агрегирования, визуального представления с помощью экранных форм и текстовый отчетов [5].
Реляционные базы данных
В реляционных БД все данные структурированы (т.е. хранятся) в виде двумерных таблиц, между которыми установлены связи. Каждая таблица представляет собой (т.е. моделирует) информационный объект, называемый сущностью.
Сущность, а, следовательно, и таблица, моделируют множество предметов реального мира (предметной области), которые описываются одним и тем же набором характеристик и следуют одним и тем же правилам, и линиям поведения.
Пример сущности – стол. Это понятие описывает множество столов, существующих в реальной предметной области, причем каждый из столов может быть описан одним и тем же набором характеристик (инвентарный номер, высота, ширина, длина, назначение) и каждый стол следует одним и тем же правилам, и линиям поведения (ни один экземпляр из этого множества не может летать). Стол с инвентарным номером 91235 и

конкретными другими характеристиками (высота=0.8 м, ширина=1.2м и т.д.) является экземпляром данной сущности.
Таким образом, каждая таблица в БД моделирует сущность из описываемой предметной области.
Каждая строка таблицы (называемая записью) описывает экземпляр этой сущности. Каждый столбец таблицы (называемый полем) моделирует одну характеристику сущности. Каждый столбец таблицы предназначен для хранения данных определенного типа: текстовых, числовых, дат, времени, денежных и т.д.
Несмотря на то, что сущностей в реальном мире существует бесконечное множество, большинство сущностей можно отнести к одной из следующих категорий:
― Реальные сущности – это объекты реального мира, существующие физически. Например, стол, самолет, человек, насос и т.д.
― Ролевые сущности (роли) – это термины, определяющие назначение отдельного человека, организации, оборудования и т.д.
Например, мастер цеха, студент, бухгалтерия и т.п.
― Инциденты – это события, случающиеся в реальной жизни.
Например, заказ товара, оплата счета, отгрузка товара, получение путевки и т.д.
― Взаимодействия – это сущности, возникающие из отношений
(связей) сущностей друг с другом.
― Спецификации – это сущности, используемые для представления правил, стандартов или критериев качества.
Поля в таблицах могут относиться к одному из следующих видов:
― Идентифицирующие (или первичные ключи) – это поле или набор полей, однозначно идентифицирующий запись таблицы (экземпляр сущности). Значение первичного ключа в таблице БД должно быть уникальным, т.е. в таблице не должно существовать двух или более

записей с одинаковым значением первичного ключа. Каждая таблица обязательно должна иметь первичный ключ. Первичные ключи обеспечивают установление связи между таблицами.
― Описательные – это поля, содержащие значения, присущие отдельным экземплярам сущности.
― Вспомогательные (или внешние ключи) – это поля, добавляемые в таблицу для организации её связи с другой таблицей. Эти поля добавляют к таблице только на этапе организации связей между таблицами.
Для любого поля таблицы (или группы полей) может быть назначен
индекс, облегчающий поиск нужных строк (записей) таблицы. При индексировании полей таблицы СУБД создает дополнительную служебную таблицу, которая содержит упорядоченные значения индексированных полей и физические адреса записей. Поэтому поиск значений по индексированным полям таблицы осуществляется значительно быстрее, чем по неиндексированным полям. Однако избыточное количество индексов в таблице существенно замедляет процессы записи и модификации такой таблицы, т.к. СУБД вынуждена модифицировать не только саму таблицу, но и соответствующие ей индексные таблицы. Поэтому злоупотреблять индексами при создании таблиц базы данных не следует [1].
Реляционные отношения между таблицами базы данных
Записи различных таблиц могут быть связанными друг с другом логически, т.е. одной записи из первой таблицы могут соответствовать одна или несколько записей из другой таблицы.
На этапе построения реляционной модели данных какой-либо предметной области в целях более компактного представления вместо реальных таблиц будем пользоваться их макетами. Название таблицы указывается в заголовочной области макета, а первичный ключ в макете таблицы выделяется жирным шрифтом и пиктограммой ключа.

Рис.1. Таблица с данными и её макет
Всё многообразие логических отношений между таблицами в реляционных базах данных моделируется тремя видами связей:
–
Один-к-одному (1:1).
–
Один-ко-многим (1:∞).
–
Многие-ко-многим (∞:∞).
Одна из связанных таблиц (на стороне «один») называется главной
таблицей, а вторая называется подчиненной таблицей. Для реализации связей между таблицами используются их поля: первичный ключ в главной таблице и внешний ключ в подчиненной таблице.
Связь Один-к-одному (1:1) означает, что одной записи из главной таблицы соответствует одна запись из подчиненной таблицы. Эта связь между таблицами на практике встречается чрезвычайно редко, т.к. этой связи можно избежать простым слиянием двух таблиц. Однако, если таблица будет содержать слишком много столбцов (например, свыше 15-
20 столбцов), то в этом случае целесообразно создать две таблицы, связав их отношением (1:1). Для реализации связи (1:1) в подчиненной таблице в качестве внешнего ключа необходимо использовать первичный ключ.

Например, в информационных системах кадрового состава организаций для учета личных сведений о сотруднике используется около 100 показателей. Поэтому личные сведения о сотруднике целесообразно разнести по нескольким таблицам, связав их затем отношением (1:1). Для связи можно использовать поле «Табельный номер», которое однозначно идентифицирует каждого сотрудника организации.
Рис.2. Пример связи (1:1) между таблицами
Связь Один-ко-многим (1:∞) означает, что одной записи из главной таблицы соответствует много записей из подчиненной таблицы. На практике это наиболее распространенный вид связи. Для реализации этой связи необходимо первичный ключ из главной таблицы добавить в качестве внешнего ключа к подчиненной таблице. При этом добавленное поле в качестве внешнего ключа к подчиненной таблице обязательно надо индексировать (совпадения допускаются) при создании таблицы в выбранной СУБД. Кроме того, необходимо отслеживать, чтобы тип поля первичного ключа главной таблицы совпадал с типом поля внешнего ключа подчиненной таблицы. Например, таблицы «Континенты» и
«Страны» связаны отношением (1:∞), т.к. одной записи из таблицы
«Континенты» соответствует несколько записей из таблицы «Страны».

Рис.3. Пример связи (1:∞) между таблицами
Для реализации связи (1:∞) к таблице «Страны» (подчиненная таблица) добавлено поле «Код_континента» в качестве внешнего ключа, являющегося первичным ключом в таблице «Континенты» (главная таблица).
Связь Многие-ко-многим (∞:∞) означает, что одной записи из первой таблицы соответствует много записей из второй таблицы, и в то же время одной записи из второй таблицы соответствует много записей из первой.
Такой вариант логической связи между сущностями (таблицами) на практике встречается довольно часто. Однако, ни одна из современных реляционных СУБД не имеет средств реализации такого рода связи. Для реализации связи (∞:∞) её надо разбить на две связи (1:∞), добавив в базу данных дополнительную таблицу. Эта дополнительная таблица называется
ассоциативной и относится к категории сущностей – «Взаимодействия».
Несмотря на то, что ассоциативная таблица добавляется в базу данных исключительно для реализации связи (∞:∞), она в базе данных обладает равными правами с другими таблицами, т.е. должна обязательно иметь первичный ключ и может участвовать в свою очередь в других связях с таблицами базы данных, т.е. может иметь и внешние ключи [2].
Например, к рассмотренным выше таблицам «Континенты» и
«Страны» добавим таблицу «Полезные ископаемые». Как установлено выше, таблицы «Континенты» и «Страны» связаны отношением (1:∞).
Определим связь между добавленной таблицей «Полезные ископаемые» и таблицей «Страны»: каждая страна может иметь множество полезных

ископаемых в своих недрах, в то же время одно и то же полезное ископаемое может добываться в различных странах. Следовательно, таблицы «Страны» и «Полезные ископаемые» связаны отношением (∞:∞).
Для реализации этой связи в базу данных следует добавить дополнительную (ассоциативную) таблицу. Назовем эту таблицу
«Полезные ископаемые в странах». Добавленная таблица связана отношением (1:∞) с таблицей «Страны» и отношением (1:∞) с таблицей
«Полезные ископаемые». Из таблицы «Страны» добавим в ассоциативную таблицу в качестве внешнего ключа поле «Код страны», а из таблицы
«Полезные ископаемые» добавим поле «Код полезного ископаемого» также в качестве внешнего ключа. Кроме этих полей, включим в ассоциативную таблицу «Полезные ископаемые в странах» еще описательное поле «Разведанные запасы».
Поскольку в ассоциативной таблице поля «Код страны» и «Код полезного ископаемого» будут повторяться, но их сочетание будет уникальным, то в качестве первичного ключа в ассоциативной таблице выберем эти два поля, т.е. первичный ключ в ассоциативной таблице
должен быть обязательно составным (составной первичный ключ назначается в конструкторе таблицы). Получается реляционная модель данных, где связь (∞:∞) между таблицами «Страны» и «Полезные ископаемые» разбита на две связи (1:∞) через ассоциативную таблицу
«Полезные ископаемые в странах».
Рис.4. Связь (∞:∞) между таблицами «Страны» и «Полезные ископаемые»

Нормализация базы данных
Итак, после определения таблиц, полей, индексов и связей между таблицами следует посмотреть на проектируемую базу данных в целом и проанализировать, используя правила нормализации, с целью устранения логических ошибок. Главная цель нормализации базы данных - устранение избыточности и дублирования информации.
Теория нормализации реляционных баз данных была разработана в конце 70-х годов 20 века, по которой выделяются шесть нормальных форм.
Первая нормальная форма:

запрещает повторяющиеся столбцы (содержащие одинаковую по смыслу информацию)

запрещает множественные столбцы (содержащие значения типа списка и т.п.)

требует определить первичный ключ для таблицы, то есть тот столбец или комбинацию столбцов, которые однозначно определяют каждую строку.
Вторая нормальная форма.
Требует, чтобы не ключевые столбцы таблиц зависели от первичного ключа в целом, но не от его части.
Третья нормальная форма.
Требует, чтобы не ключевые столбцы в ней не зависели от других не ключевых столбцов, а зависели только от первичного ключа. Самая распространенная ситуация в данном контексте — это расчетные столбцы, значения которых можно получить путем каких-либо манипуляций с другими столбцами таблицы.
Нормальная форма Бойса-Кодда.
Требует, чтобы в таблице был только один потенциальный первичный ключ. Чаще всего у таблиц, находящихся в третьей нормальной форме, так и бывает, но не всегда. Если обнаружился второй столбец