Проектирование баз данных "Сложная система, спроектированная наспех, никогда не работает, и исправить её, чтобы заставить работать, невозможно". Законы Мерфи. 16-й закон системантики

Требования к проекту базы данных Основные требования, которым должен удовлетворять проект базы данных (БД): 1. Корректность схемы БД. 2. Обеспечение ограничений на ресурсы вычислительной системы. 3. Эффективность функционирования. 4. Обеспечение защиты данных. 5. Гибкость. 6. Простота и удобство эксплуатации. Удовлетворение первых 4-х требований обязательно для принятия проекта.

Этапы проектирования АИС В создании АИС (автоматизированной информационной системы) можно выделить следующие этапы: Предпроектная подготовка. Проектирование базы данных. Реализация (создание базы данных и прикладного программного обеспечения, ППО). Специалисты, необходимые для выполнения этой работы: Аналитики (специалисты исследуемой предметной области). Пользователи – те работники, для которых создаётся АИС. Проектировщики (разработчики базы данных). Администраторы (системные, базы данных, безопасности и др.) Программисты (разработчики программного обеспечения).

Этапы проектирования БД I. Информационно-логическое (инфологическое) проектирование анализ предметной области; построение модели предметной области; определение границ информационной поддержки; определение групп пользователей. II. Определение требований к операционной обстановке: выбор аппаратной платформы; выбор операционной системы. III. Выбор СУБД и других инструментальных программных средств. выбор СУБД; выбор версии СУБД и архитектуры, в которой она будет работать. IV. Логическое проектирование БД (даталогическое): преобразование схемы предметной области в схему базы данных; создание схем отношений; нормализация отношений. V. Физическое проектирование БД: реализация проекта на DDL-языке выбранной СУБД; создание дополнительных объектов БД (индексов, представлений, триггеров и др.).

I. Инфологическое проектирование Инфологическая модель ПрО включает описание структуры и динамики ПрО, характера информационных потребностей пользователей системы. Описание выполняется в терминах, понятных пользователю и независимых от реализации системы. Обратите внимание: инфологическая модель ПрО не должна зависеть от модели данных, которая будет использована при создании БД. 1. Определение границ предметной области (ПрО). 2. Анализ ПрО. Выполняется на основе документов с помощью специалистов в данной ПрО. 3. Методы анализа: функциональный, * предметный; * метод сущность-связь – entity-relation method, ER-метод. 4. ER-метод, основные понятия: сущность – объект ПрО, сведения о котором необходимо хранить в БД; атрибут – характеристика сущности (свойство сущности); связь – устойчивая ассоциация между сущностями.

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

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

Анализ ПрО с помощью ER-метода Связи между сущностями : Для связи указывается: название, тип (факультативная или обязательная), кардинальность (1:1, 1:n или m:n), степень (унарная, бинарная, тернарная или n-арная). Различают тип связи и экземпляр связи. Примеры обязательной и факультативной связей:

Анализ ПрО с помощью ER-метода Кардинальность связей между сущностями : один-к-одному (1:1); один-ко-многим (1:n); многие-ко-многим (m:n). Примеры связей разной кардинальности:

Анализ ПрО с помощью ER-метода Степень связей между сущностями : унарная – связь между разными экземплярами сущностей одного типа: бинарная – связь между двумя разными типами сущностей: тернарная – связь между тремя разными типами сущностей:

Модель предметной области Совокупность типов сущностей и типов связей между ними характеризует структуру предметной области. Собственно данные представлены экземплярами сущностей и связей между ними. Данные экземпляров сущностей и связей хранятся в базе данных информационной системы, а описание типов сущностей и связей является метаданными. Множества экземпляров сущностей, значения атрибутов сущностей и экземпляры связей между ними могут изменяться во времени. Поэтому каждому моменту времени можно сопоставить некоторое состояние предметной области. Состояния ПО должны подчиняться совокупности правил, которые характеризуют семантику предметной области. В базе данных эти правила могут быть заданы с помощью так называемых ограничений целостности, которые накладываются на атрибуты сущностей, типы сущностей, типы связей и/или их экземпляры. Фактически ограничения целостности – это правила, которым должны удовлетворять значения данных в БД.

Обозначения, используемые в ER-диаграммах

Моделирование локальных представлений Если ПрО содержит много сущностей (10 и более), то она разбивается на ряд локальных областей (локальных представлений) по 6-7 сущностей. Каждое локальное представление включает в себя информацию, достаточную для обеспечения информационных потребностей одной группы будущих пользователей или решения отдельной задачи. Каждое локальное представление моделируется отдельно, а затем выполняется их объединение (за 1 шаг по попарно). При объединении локальных представлений используют концепции: Идентичность. Два или более элементов модели идентичны, если они имеют одинаковое семантическое значение. Агрегация. Позволяет рассматривать связь между элементами как новый элемент. Обобщение. Позволяет образовывать многоуровневую иерархию обобщений. На этапе объединения локальных представлений необходимо устранить все противоречия.

Объединение локальных представлений Использование обобщения: Например, пусть в объединяемых представлениях присутствуют следующие сущности: ДЕТАЛИ СОБСТВЕННОГО ПРОИЗВОДСТВА ДЕТАЛИ ПОКУПНЫЕ СБОРОЧНЫЕ ЕДИНИЦЫ ПОКУПНЫЕ СБОРОЧНЫЕ ЕДИНИЦЫ СОБСТВЕННОГО ПРОИЗВОДСТВА Их можно объединить так :

Результаты инфологического проектирования Концептуальная инфологическая модель ПрО. Она фиксируется в виде общей ER-диаграммы предметной области. Модели локальных представлений – это внешние инфологические модели (внешние схемы). Правила (ограничения) целостности, которым должны удовлетворять сущности ПО, атрибуты сущностей и связи между ними. Часть этих правил реализуется в схеме базы данных, другие – с помощью программного обеспечения. Перечень групп пользователей системы. Каждая группа выполняет определённые задачи и обладает разными правами доступа к системе. Внешние спецификации функций (процессов), которые будет выполнять АИС.

Определение требований к операционной обстановке На этом этапе производится: оценка требований к вычислительным ресурсам, необходимым для функционирования системы; выбор типа и конфигурации ЭВМ; выбор типа и версии операционной системы (ОС). Выбор зависит от таких показателей, как: примерный объём данных в БД; динамика роста объёма данных; характер запросов к данным (извлечение и обновление отдельных записей, обработка групп записей, обработка отдельных отношений или соединение отношений); интенсивность запросов к данным по типам запросов; требования ко времени отклика системы по типам запросов; режим работы (интерактивный, пакетный или режим реального времени).

Выбор СУБД Наиболее важные критерии выбора СУБД: тип модели данных, которую поддерживает данная СУБД, адекватность модели данных структуре рассматриваемой ПО; характеристики производительности СУБД; запас функциональных возможностей для дальнейшего развития информационной системы; степень оснащённости СУБД инструментарием для персонала администрирования данными; удобство и надежность СУБД в эксплуатации; наличие специалистов по работе с конкретной СУБД; стоимость СУБД и дополнительного программного обеспечения. Также может выбираться дополнительное программное обеспечение (например, CASE-средства: ERWin, BPWin и т.п.).

Логическое проектирование РБД Преобразование ER-диаграммы в схему базы данных. Правила преобразования: 1.Каждый тип сущности преобразуется в таблицу БД. В таблицу вносятся все атрибуты, относящиеся к данному типу сущности. 2.Бинарная связь 1:n (между сущностями разных типов) реализуется с помощью внешнего ключа между двумя таблицами

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

Логическое проектирование РБД Преобразование ER-диаграммы в схему базы данных. Правила преобразования: 4.Связь 1:1 реализуется в рамках одной таблицы. Исключение из этого правила составляют ситуации, когда связанные сущности существуют независимо друг от друга. 5.Унарная связь 1:n (между сущностями одного типа) реализуется с помощью внешнего ключа, определённого в той же таблице, что и первичный ключ.

Логическое проектирование РБД Преобразование ER-диаграммы в схему базы данных. Правила преобразования: 6.Бинарная связь типа n:m реализуется с помощью промежуточной таблицы.

Логическое проектирование РБД Преобразование ER-диаграммы в схему базы данных. Правила преобразования: 7.Унарная связь n:m реализуется с помощью промежуточной таблицы. На этом этапе возможно ещё выявление нереализуемых и необычных связей (связи 1:n, обязательные в обе стороны; взаимоисключающие связи и др.).

Логическое проектирование РБД Составление схем отношений. Определение первичных ключей (ПК): 1.При наличии потенциальных ключей ПК выбирается из них. Обычно, берется тот ключ, по которому чаще всего происходит обращение к данным. Если такого нет, то выбирается ключ, занимающий меньше памяти. 2.Если потенциальных ключей нет, назначается суррогатный ПК (он не несет смысловой нагрузки). Некоторые СУБД позволяют определять значения такого ключа как AUTOINCREMENT, т.е. числовое поле, значение которого начинается с 1 и автоматически увеличивается на 1 при добавлении новой записи. 3.Составной ПК назначается в том случае, если необходимо реализовать ограничение целостности "уникальность".

Логическое проектирование РБД Определение типов данных атрибутов. О бщие рекомендации: Для коротких символьных значений и символьных строк фиксированной длины следует выбирать тип CHAR. Для символьных строк переменной длины нужно выбирать тип VARCHAR с указанием максимально возможной длины хранимого значения. Для числовых атрибутов, не участвующих в сложных расчётах, нужно использовать основной числовой тип реляционных СУБД – тип NUMBER, указывая реально необходимое количество разрядов. Для числовых атрибутов, которые участвуют в сложных расчётах, следует использовать такие числовые типы, которые хранят данные в машинном (двоичном) представлении. Для числовых атрибутов, имеющих ведущие нули, следует выбирать тип CHAR, а не числовой тип, иначе ведущие нули будут потеряны. Для хранения дат нужно выбирать тип DATE или его варианты (DATETIME, например). Для хранения больших объектов (графических, звуковых и т.п.) следует выбирать специальные типы данных, перечень которых зависит от СУБД. Для семантически одинаковых полей разных таблиц нужно выбирать одинаковые типы данных. Во многих СУБД для упрощения типизации данных можно создать специальные типы данных (create type) и использовать их в качестве типов полей таблиц.

Логическое проектирование РБД Определение и реализация ограничений целостности: Если какое-либо ограничение целостности может быть включено в структуру БД (на языке DCL), то его надо реализовать именно так! Рассмотрим различные типы ограничений целостности в языке SQL: Уникальность значения первичного ключа (PRIMARY KEY). Уникальность ключевого поля или комбинации значений ключевых полей (UNIQUE). Обязательность/необязательность значения (NOT NULL/NULL). Задание условия на значения атрибутов (CHECK). Определение домена атрибута на основе значений другого атрибута: множество значений некоторого атрибута отношения является подмножеством значений другого атрибута этого или другого отношения (внешний ключ, FOREIGN KEY).

Логическое проектирование РБД Определение и реализация ограничений целостности. Если какое-либо ограничение целостности (ОЦ) нельзя реализовать средствами DCL, то возможны следующие способы его реализации: С помощью процедурных объектов БД (триггеров, trigger). Программно (т.е. через приложение). Для большей гарантии соблюдения ОЦ желательно проектировать программу так, чтобы внесение изменений в данные и проверка ОЦ выполнялись в одном единственном месте. Вручную. Ручная процедура обязательно должна быть описана в документации (в руководстве пользователя). Необходимо обратить особое внимание на поля таблиц, для которых домен определён как список возможных значений. Это ограничение целостности можно реализовать в виде: CHECK( IN ( )). Но такой подход имеет следующий недостаток: добавление нового значения в список потребует изменения схемы отношения (команда ALTER TABLE). Можно поступить до-другому: вынести этот список значений в отдельное отношение. Например, список типов образования (начальное, неполное среднее, среднее, средне-специальное, незаконченное высшее, высшее) для таблицы СОТРУДНИКИ. Таблица ТИПЫ ОБРАЗОВАНИЯ будет состоять из одного поля Название типа, определённого как первичный ключ. Тогда поле Образование таблицы СОТРУДНИКИ станет внешним ключом.

Физическое проектирование РБД При использовании СУБД Oracle примерная последовательность создания объектов БД следующая: 1.Создание БД (create database). 2.Создание пользователей (create user). 3.Создание пользовательских типов (create type). 4.Создание кластеров и таблиц (create cluster, create table). 5.Создание представлений (create view). 6.Создание синонимов (create synonym). 7.Создание последовательностей (create sequence). 8.Назначение прав доступа (grant). 9.Заливка данных (Oracle Loader, imp.exe,…). 10.Создание индексов (create index). 11.Создание процедур и функций (create procedure, create function). 12.Создание триггеров (create trigger).