2. Проектирование структур и баз данных (Кафедра вТиАоаи)

1. Принципы организации и основные типы баз данных.

Приведем в начале общепринятые в литературе определения.

Информация – это любые сведения о каком-либо событии, явлении, процессе, являющаяся объектом некоторых операций: восприятия, передачи, преобразования, хранения, использования.

Данные – это информация, представленная в фиксированной форме, пригодной для хранения и обработки в автоматизированных компьютерных системах.

Предметная область – это часть реального мира, информация о которой представляет интерес.

База данных ( БД ) – это именованная совокупность массивов сложноструктурированных данных, предусматривающая общие принципы их описания, хранения и манипулирования.

Система управления базами данных ( СУБД ) – это пакет прикладных программ, предназначенный для создания, хранения и коллективного использования баз данных.

База данных и соответствующая ей СУБД в единстве составляют банк данных ( БнД ) .

Основные функции СУБД:

Хранение информации и организация ее защиты

Обновление хранимых данных

Поиск и отбор данных по информационным запросам

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

Рассмотрим основные принципы организации БД:

Принцип интеграции – объединение нескольких физических файлов, содержащих данные в единое целое – БД, которая представляется для пользователя одним информационным массивом

Независимость программ обработки от хранимых данных – любое допустимое изменение структуры данных не требует изменения программ для их обработки

Минимальная избыточность – сведение к минимуму дублирования хранимых данных

Непротиворечивость – поддержка смыслового соответствия хранимых данных

Возможность модификации структуры хранимых данных средствами СУБД с автоматическим контролем непротиворечивости и целостности

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

Для разработки информационной модели предметной области необходимо:

Выделить класс объектов, о которых необходимо хранить и пополнять информацию

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

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

Информационная модель ПО

Внутренняя схема данных

Физическая БД

Информационная модель, поддерживаемая СУБД, определяется тремя компонентами:

Допустимой структурой данных – количеством типов объектов, которых можно представить с помощью данной модели

Множеством допустимых операций над данными

Ограничениями для контроля целостности данных – системой требований, которым должна удовлетворять структура любой БД для сохранения непротиворечивости и адекватности отображения предметной области в БД.

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

2.2.1.2. Типы баз данных

По типам записей различают три основных типа СУБД: иерархические (древовидные), сетевые и реляционные.

Общая схема иерархической структуры может быть изображена в виде:

1 уровень

2 уровень

3 уровень

Каждый элемент, называемый узлом, связан только с одним узлом на более высоком уровне, который называется исходным. Ни один узел не имеет более одного исходного. Каждый узел связан с одним или несколькими узлами на более низком уровне, которые называются порожденными. В иерархической модели БД каждая запись представляется в виде нескольких компонентов, связанных отношениями иерархии (как правило, используются 2-уровневые схемы ) :

Основная запись

Подчиненные записи

Пример: База данных «Клиенты банка»

Код Фамилия Баланс - главная запись

Операция 1 Операция 2 Операция 3 - подчиненные записи

Структура подчиненной записи:

Дата Сумма Признак

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

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

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

В сетевой структуре порожденные узлы могут иметь более одного исходного, и таким образом каждый узел может быть связан с любым другим. Приведем примеры схем сетевых структур:

1 уровень

2 уровень

1 уровень

2 уровень

3 уровень

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

Логическая схема двухуровневой сетевой БД может быть в общем виде представлена следующим образом:

Основная запись

Подчиненные записи

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

Пример: БД «Поставщики-товары»

Код Фирма Адрес Р/с

Номер партии товара Наименование Количество Цена

2. Нормализация реляционных баз данных.

Реляционная модель БД

При разработке информационных моделей и БД многих объектов приходится учитывать достаточно большое количество атрибутов, связанных сложной системой взаимосвязей.Соответствующие иерархические и сетевые БД оказываются сложными в построении из-за значительного количества связей между их компонентами, что может приводить к нарушению логического представления данных. Принципиально новый подход был реализован в 1970 г. в предложенной Коддом (Codd ) концепции реляционных БД [1,2]. В ее основе лежит представление БД в виде совокупности нескольких связанных двумерных таблиц , которые называются отношениями ( relation ). Каждый столбец отношения имеет свое уникальное имя и называется полем, он соответствует к-л атрибуту информационной модели. Строка таблицы имеет свой уникальный номер и называется записью. Количество полей называется степенью отношения. Список имен полей называется схемой. Пример записи схемы отношения:

Студент (Номер зачетки, Фамилия, Факультет, Курс, Специальность, Группа)

Каждое отношение должно иметь первичный ключ – идентификатор записей. Значение первичного ключа каждой записи однозначно определяет значения всех остальных неключевых полей. Первичный ключ может состоять из одного или нескольких полей отношения. В приведенном выше примере первичный ключ отношения Студент – это поле Номер зачетки ( оно выделено подчеркиванием). Первичный ключ должен:

Однозначно идентифицировать записи отношения

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

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

Не может быть совпадающих значений первичного ключа ( все записи должны быть уникальными)

Все записи должны иметь одинаковую структуру

Имена всех полей должны быть различными

Порядок следования записей отношения не имеет значения

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

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

Кортеж – строка, атрибут – столбец, домен- множество однородных допустимых значений одного атрибута.

Т.обр., реляционная БД – это набор отношений, связанных между собой. Связь – это ассоциирование двух или более отношений БД. Основные виды связей:

Один-к одному

Один-ко многим

Многие-ко многим ( непосредственно не поддерживается).

В реляционных моделях вопросам целостности отводится особое место. Здесь используютcя понятия первичных ключей и внешних ключей. Предусмотрены два варианта событий при попытке удалить запись из отношения, на которую ссылается внешний ключ связанного с ним ( свяль один-ко многим). Первый – каскадное удаление связанных записей. Второй – ограничение ( запрет удаления связанных записей).

Формальной основой реляционной модели БД являются реляционная алгебра, основанная на теории множеств и рассматривающая специальные операторы над отношениями, и реляционное исчисление, базирующееся на математической логике. Реляционная алгебра замкнута относительно понятий отношения, т.е. выражения реляционной алгебры выполняются над одним или несколькими отношениями и результат их вычисления также представляет собой отношения. Перечислим основные операции реляционной алгебры:

Выборка ( селекция ) отношения :

Результат представляет множество записей, удовлетворяющих заданному условию

Проекция отношения :

Результат содержит некоторые отобранные поля исходного отношения

Объединение отношений :

Результат включает все записи, входящие в хотя бы одно из участвующих в операции отношений

Пересечение отношений :

Результат включает записи, входящие в оба участвующие в операции отношения

Произведение отношений :

Записи результата являются сочетанием полей записей обоих отношений, а количество записей равно произведению количеств исходных

Соединение отношений :

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

Нормализация отношений

В основе процесса проектирования БД лежит метод нормализации – декомпозиции отношения, допускающего избыточность данных, в несколько связанных отношений с меньшей избыточностью. Вводятся опредеделения нормальных форм: 1НФ, 2НФ, 3НФ. Каждой НФ соответствует свой набор ограничений.

Отношение находится в 1НФ, если оно содержит только логически неделимые значения и имеет первичный ключ. Пример:

Оценки ( SP )

Оценка Ном. Зач. Спец-ть Ном. Гр. Код. Пред.

Первичный ключ – это поля (Ном. Зач. , Код.Пред ) . Кроме того, имеет место функциональная зависимость номера группы и специальности. Диаграмма функциональной зависимости для этошо отношения:

SP

Оценка Ном.Зач. Ном.гр.

Код.Пред. Спец-ть

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

SP1 SP2

Ном.зач. Ном.гр. Ном.зач. Оценка

Спец-ть Код.Пред

Отношение находится в 2НФ, если оно уже приведено к 1НФ, и каждый неключевой атрибут функционально полно зависит от первичного ключа. Любое отношение в 1НФ можно свести к набору отношений в 2НФ.

О днако в SP1 из-за функциональной зависимости Ном.гр. Спец-ть возникают проблемы при операциях INSERT, DELETE ( нельзя указать специальность данной группы, пока не введены данные о студентах; при удалении информации о группе пропадает информация о специальности ). Для решения проблемы SP1 разделяется на две проекции :

Н ом.Зач. Ном.гр. Ном.Гр. Спец-ть

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

3. Структура таблиц локальных СУБД Access.

MS ACCESS представляет собой локальную СУБД реляционного типа, предназначенную для создания и ведения баз данных как в автономном режиме, так и в локальной одноранговой сети.

Приведем кратко основные характеристики.

Допустимые типы полей:

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

Числовой тип – возможны различные варианты:

Целый размера 1 байт

Целый размера 2 байта

Целый размера 4 байта

Вещественный размера 4 байта

Вещественный размера 8 байт

Размер числового поля задает максимальную допустимую величину хранимых чисел. Хранимые в числовом поле данные могут быть представлены в различных форматах: основном, с фиксированной точкой, экспоненциальный, процентный.

Денежный ( 8 байт) – предназначен для хранения финансовой информации. Он имеет 15 разрядов целой части и 4 разряда в дробной.

Логический ( 1 байт) –может хранить данные типа истина/ложь. Возможно представление логических данных в виде Да/Нет либо True/False.

Текстовый ( не более 255 байт)

Дата/Время ( 8 байт)

МЕМО – предназначен для обработки и вывода больших текстовых массивов размером до 64К. В самом поле содержатся только ссылки, а сами массивы хранятся отдельно в специальных файлах.

OLE – предназначен для внедрения или связывания объектов различных типов и отображения их в формах и отчетах. В полях этого типа содержатся ссылки на соответствующие объекты. В качестве объектов обычно выступают графические файлы форматов .JPG , .BMP.

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

Каждая таблица должна иметь первичный ключ. Он предназначен для однозначной идентификации любой записи таблицы. Первичный ключ может быть трех типов: счетчик, простой ключ, составной ключ.

Счетчик – в соответствующее поле при добавлении записи автоматически вносится ее номер и рассматривается как ее идентификатор-ключ.

Простой ключ – состоит их одного поля

Составной ключ – состоит из нескольких полей.

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

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

1 : 1 - один-к –одному

1 : М - один-ко-многим

М : N – многие-ко-многим

Связь типа 1:1 предполагает, что внешние ключи обеих таблиц совпадают с первичными. В этом случае между записями таблиц устанавливается взаимно однозначное соответствие, и они могут быть объединены в одну.

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

Т акой тип связи обозначается двунаправленной стрелкой

причем двойная стрелка указывает на внешний ключ подчиненной таблицы.

Важную роль для этого типа связи играет принцип обеспечения целостности данных. Он требует выполнения следующих условий при корректировке данных связанных таблиц:

В подчиненную таблицу не может быть добавлена запись с несуществующим в главной таблице значением внешнего ключа

В главной таблице нельзя удалить запись, если не удалены все связанные с ней записи в подчиненной таблице

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

Режим обеспечения целостности данных задается при разработке схемы данных. При этом возможно также указание возможности каскадного обновления и удаления связанных записей таблиц ( при этом удаление записи главной таблицы автоматически приводит к удалению всех связанных с ней записей подчиненной таблицы )

Третий возможный тип связи – многие-ко-многим – непосредственно не реализован в ACCESS. Однако он может быть организован с помощью создания связывающей таблицы с составным внешним ключом, как показано на схеме:

A B

K _A K _B

K _C = K _A + K _B

C

4. Формирование запросов на языке SQL.

Запрос ( Query ) – это постоянно хранящееся в БД требование на выборку данных из одной или нескольких таблиц либо выполнения определенных действий по преобразованию структуры таблиц и обновлению хранимых данных. Каждый запрос представляет собой фрагмент программного кода на языке SQL. Запросы используются как средство представления данных для пользовательского интерфейса либо как средство для обработки данных.

SQL – это язык обработки БД стандарта ANSI [5]. Он тесно интегрирован с реляционной моделью данных может быть использован для манипулирования данными как в локальной БД ACCESS, так и в распределенных БД типа клиент/сервер ( ORACLE, INFORMIX, MS SQL SERVER ).

Создание запросов в ACCESS возможно двумя способами:

Непосредственным формированием предложения на языке SQL

В режиме конструктора интерактивным способом с помощью метода QBE (Query By Example).

Запросы запускаются на выполнение непосредственно пользователем из программной среды ACCESS в режиме просмотра или с помощью соответствующих операторов вызова из программного кода. Все запросы могут быть отнесены к двум категориям:

Запросы DDL – служат для создания, дополнения и удаления таблиц

Запросы DML – служат для представления данных из одной или нескольких таблиц в требуемой форме в соответствии с заданным условием отбора.

Язык SQL состоит из инструкций, предложений и операций. Рассмотрим сначала запросы категории DML. Они содержат следующие инструкции:

SELECT – отбор записей в соответствии с заданным условием

INSERT – добавление в таблицу новых записей

UPDATE – изменение значений указанных полей

DELETE – удаление записей из таблиц

Основные предложения языка SQL:

FROM – используется для указания таблиц, из которых выбираются данные

WHERE – определяет условие отбора

ORDER BY – определяет порядок следования отобранных записей ( сортировка)

ON – служит указателем внешних ключей связи таблиц

Перечислим теперь типы операций языка SQL: арифметические, логические и операции отношения.

Арифметические операции: + , - , * , /

Логические операции: AND, OR, NOT

Операции отношения: > , < , >=, <=, = , <>, BETWEEN, LIKE, IN

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

AVG - среднее значение поля

SUM – сумма значений поля

COUNT – количество записей

Рассмотрим сначала запросы на выборку, как наиболее часто используемые. Общий вид запроса, основанного на нескольких таблицах :

SELECT < список полей > FROM < таблица1 > < тип связи> < таблица2> ON

< внешние ключи > WHERE < условие> ORDER BY < поле>

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

SELECT < список полей > FROM < таблица > WHERE < условие >

Указание внешних ключей связанных таблиц состоит из их имен и условия связи: таблица1.ключ1=таблица2.ключ2

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

<поле > < операция отношения > <значение >

Примеры условий:

Фамилия=”Иванов”

Количество>=10

Дата < #04.05.01#

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

Пример:

Дата BETWEEN ( [T1] AND [T2]) , либо: [T1] < Дата < [T2]

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

Рассмотрим теперь возможные типы связи. Для указания типа связи двух таблиц используются инструкции объединения INNER JOIN, LEFT JOIN, RIGHT JOIN. Общий вид:

<левая таблица > <инструкция объединения > <правая таблица >

Имеется три вида объединений:

Внутреннее объединение INNER JOIN – в запрос включаются только те записи, в которых значения ключей связи совпадают

Левое объединение LEFT JOIN – в запрос включаются все записи первой указанной в запросе таблицы( левой) и только те записи правой таблицы, в которых значения ключей связи совпадают ( при несовпадении ключей связи добавляются пустые значения полей – тип NULL) .

Правое объединение RIGHT JOIN – в запрос включаются все записи второй указанной в запросе таблицы ( правой) и только те записи левой таблицы, в которых значения ключа связи совпадают.

Рассмотрим теперь пример запроса, основанного на двух связанных таблицах

Товары( Код_товара, Название, Цена)

Заказы( Номер, Код_товара, Количество, Дата ) (ключи связи – поля

Код_товара , тип связи – один-ко-многим )

SELECT Товары.Название, Товары.Цена, Заказы.Количество, Заказы.Дата FROM Товары LEFT JOIN Заказы ON Товары.Код_товара=Заказы.Код_товара

Рассмотрим еще один пример запроса, основанного на трех связанных таблицах:

Компьютеры(Код, Модель, Цена )

Заказы( Номер, Код, Количество, Код_фирмы, Дата)

Фирмы( Код_фирмы, Фирма, Реквизиты)

Приведем схему связей таблиц:

К омпьютеры Фирмы