Где сделать физическую модель базы данных

Физическая модель базы данных

Каждая таблица состоит из строк и столбцов и предназначена для хранения информации об однотипных объектах.

Пример таблицы, содержащей информацию о студентах:

Ключи

Выделяют следующие виды ключей:

• По структуре ключа:
  • Простые (англ. simple) — состоящие из одного поля,
  • Составные (англ. composite) — состоящие из нескольких полей.
  • Естественные (англ. natural) — приходят из реальных данных,
  • Суррогатные (англ. surrogate) — генерируются СУБД или придумываются специально для идентификации записи. Использование суррогатного ключа может быть обусловлено тем, что естественный ключ является длинным, составным или вовсе отсутствует.
  • Основной (англ. primary) — ключ, используемый для того, чтобы ссылаться на таблицу. Использование различных ключей для ссылок считается плохой практикой.
  • Дополнительные (англ. secondary) — ключи, позволяющие поддерживать уникальность поля или набора полей. Их не следует использовать для ссылок на таблицу.

  Так, например, идентификатор студента будет простым суррогатным ключом, а серия и номер паспорта — составным естественным ключом.

  Ссылки

  Определение:

  Внешним ключом (или ссылкой, англ. foreign key) называют набор столбцов, соответствующий (как правило, основному) ключу другой или той же самой таблицы.

  

  Пример ссылки на другую таблицу

  • Простые (англ. simple) — ссылки, состоящие из одного поля,
  • Составные (англ. composite) — ссылки, состоящие из нескольких полей.

  Графическая нотация

  

  Пример графической нотации для таблицы $Students$

  

  Пример внешней ссылки

  Таблицы обозначаются прямоугольниками. В заголовке указывается имя таблицы.

  Таблица состоит из трех столбцов со следующим содержанием:

  • Имя поля
  • Тип поля
  • Свойства поля

  Обозначение	Свойство поля
  M	Обязательное (англ. mandatory)
  O	Необязательное (англ. optional)
  PK	Основной ключ (англ. primary key)
  Kn	Дополнительный ключ $n$ (англ. key)
  FKn	Внешний ключ $n$ (англ. foreign key)

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

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

  Литература

  • Дейт К. Введение в системы баз данных (глава 14)
  • Уидом Д., Ульман Д. Основы реляционных баз данных (глава 2)

  Создание физической модели

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

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

  Функциональная зависимость. Атрибут В сущности Е функционально зависит от атрибута А сущности Е, если и только если каждое значение А в Е связало с ним точно одно значение В в Е. Другими словами, А однозначно определяет В.

  Полная функциональная зависимость. Атрибут Е сущности В полностью функционально зависит от ряда атрибутов А сущности Е, если и только если В функционально зависит от Л и не зависит ни от какого подряда А.

  Существуют следующие виды нормальных форм:

  • Первая нормальная форма

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

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

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

  1.1. Поддержка нормализации в ERWin

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

  Поддержка первой нормальной формы В модели каждая сущность или атрибут идентифицируется с помощью имени. В ERWin поддерживает корректность имен следующим образом:

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

  Создание физической модели

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

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

  Таблица 7.1. Сопоставление компонентов логической и физической модели

  Логическая модель	Физическая модель
  Сущность	Таблица
  Атрибут	Столбец
  Логический тип (текст, число, дата, blob)	Физический тип (корректный тип, зависящий от выбранной СУБД)
  Первичный ключ	Первичный ключ, индекс РК
  Внешний ключ	Внешний ключ, индекс FK
  Альтернативный ключ	АК-индекс — уникальный, непервичный индекс
  Правило бизнес- логики	Триггер или сохраненная процедура
  Взаимосвязи	Взаимосвязи, определяемые использованием FK-атрибутов

  Денормализация

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

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

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

  ERWin существует возможность выделения элементов логической модели таким образом, чтобы они не появлялись на физическом уровне.

  • Таблицы, столбцы, индексы и домены можно создавать только на физическом уровне. В

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

  • Разрешение связей «многие-ко-многим». При разрешении этих связей в логической модели ERWin добавляет ассоциированные сущности и позволяет добавить в них атрибуты. При разрешении связей в логической модели автоматически разрешаются связи и в физической модели.

  Пример

  Нормализуем полученную в предыдущей лабораторной работе БД до третьей нормальной формы. Для приведения БД в первую нормальную форму необходимо выполнить условие, при котором все атрибуты содержат атомарные значения. Рассмотрим атрибуты сущности «Студент». Студент может иметь несколько адресов электронной почты и несколько телефонных номеров, что является нарушением первой нормальной формы. Необходимо создать отдельные сущности «E-mail» и «Телефон» и связать их с сущностью «Студент» (рис. 7.1).

  

  Рис. 7.1. ERD-диаграмма БД студентов в первой нормальной форме

  Проверим соответствие БД второй нормальной форме. Все неключевые атрибуты полностью должны зависеть от первичного ключа. Нетрудно заметить, что это условие выполняется для всех сущностей БД; следовательно, можно сделать вывод о том, что она находится во второй нормальной форме.

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

  

  Рис. 7.2. ERD-диаграмма БД студентов в третьей нормальной форме

  Перейдем к построению физической модели. Перед построением физической модели необходимо выбрать тип базы данных в меню при создании физической модели. Выберем в качестве DataBase Microsoft Access 97 или 2000, получив физическую модель, сгенерированную ERWin по умолчанию (рис. 4.4).

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

  Таблица 7.2. Свойства колонок таблиц физической модели БД студентов

  Создание физической модели базы данных

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

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

  

  Рис. 6.15. Фрагмент физической модели базы данных в нотации IDEF1X

  Кроме уточнения типов данных и приведения названий таблиц и полей в соответствие требованиям СУБД, физическую модель отличает возможность включения в нее представлений, индексов, хранимых процедур, триггеров и прочих объектов БД. В качестве примера на рис. 6.16 показано использование представления (англ. view, подробнее о представлениях см. гл. 7), выводящего название помещения и название компьютера. Представление на диаграмме изображается овалом с пунктирной границей. При проектировании БД в среде ERwin, при использовании визуального конструктора для создания представления, на диаграмме будут автоматически определяться связи между созданным представлением и таблицами или другими объектами, из которых берутся данные. Автоматически генерируемый код представления на языке SQL можно уточнить или полностью переделать.

  

  Рис. 6.16. Использование представлений в физической модели

  Следующий шаг после того, как физическая модель БД построена в ERwin (или другом подобном CASE-средствс), заключается в создании самой БД. Для этого требуется подключиться к выбранному серверу (рис. 6.17), после чего создать на нем схему БД (в ERwin меню Action → → Forward Engineer → Schema. ).

  

  Рис. 6.17. Настройка подключения к выбранному серверу БД в Erwin Data Modeler v. 9

  Моделирование данных: обзор

  В работе мы с коллегами часто видим как компании сталкиваются с проблемой управления данными – когда таблиц и запросов становится сильно много и управлять всем этим очень сложно. В таких ситуациях мы рекомендуем моделировать данные. Чтобы разобраться, что это такое – я перевела статью-обзор про моделирование данных от Towards Data Science, в которой кроме основных терминов и понятий можно найти наглядный пример использования моделирования данных в ритейле. Вперед под кат!

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

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

  Моделирование данных можно сравнить со строительством дома. Допустим, компании ABC необходимо построить дом для гостей (база данных). Компания вызывает архитектора (разработчик моделей данных) и объясняет требования к зданию (бизнес-требования). Архитектор (модельер данных) разрабатывает план (модель данных) и передает его компании ABC. Наконец, компания ABC вызывает инженеров-строителей (администраторов баз данных и разработчиков баз данных) для строительства дома.

  Ключевые термины в моделировании данных

  Сущности и атрибуты. Сущности — это «вещи» в бизнес-среде, о которых мы хотим хранить данные, например, продукты, клиенты, заказы и т.д. Атрибуты используются для организации и структурирования данных. Например, нам необходимо хранить определенную информацию о продаваемых нами продуктах, такую как отпускная цена или доступное количество. Эти фрагменты данных являются атрибутами сущности Product. Сущности обычно представляют собой таблицы базы данных, а атрибуты — столбцы этих таблиц.

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

  Сущность пересечения. Если между сущностями есть связь типа «многие ко многим», то можно использовать сущность пересечения, чтобы декомпозировать эту связь и привести ее к типу «многие к одному» и «один ко многим».

  Простой пример: есть 2 сущности — телешоу и человек. Каждое телешоу может смотреть один или несколько человек, в то время как человек может смотреть одно или несколько телешоу.

  

  Эту проблему можно решить, введя новую пересекающуюся сущность «Просмотр записи»:

  

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

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

  

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

  

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

  

  Основные этапы моделирования данных:

  

  Реляционное vs размерное моделирование

  В зависимости от бизнес-требований ваша модель данных может быть реляционной или размерной. Реляционная модель — это метод проектирования, направленный на устранение избыточности данных. Данные делятся на множество дискретных сущностей, каждая из которых становится таблицей в реляционной базе данных. Таблицы обычно нормализованы до 3-й нормальной формы. В OLTP приложениях используется эта методология.

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

  Кейс

  Компания ABC имеет 200 продуктовых магазинов в восьми городах. В каждом магазине есть разные отделы, такие как «Товары повседневного спроса», «Косметика», «Замороженные продукты», «Молочные продукты» и т.д. В каждом магазине на полках находится около 20 000 отдельных товаров. Отдельные продукты называются складскими единицами (SKU). Около 6 000 артикулов поступают от сторонних производителей и имеют штрих-коды, нанесенные на упаковку продукта. Эти штрих-коды называются универсальными кодами продукта (UPC). Данные собираются POS-системой в 2 местах: у входной двери для покупателей, и у задней двери, где поставщики осуществляют доставку.

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

  Разработайте модель данных для анализа операций этой продуктовой сети.

  Решение

  Шаг 1. Сбор бизнес-требований

  Руководство хочет лучше понимать покупки клиентов, фиксируемые POS-системой. Модель должна позволять анализировать, какие товары продаются, в каких магазинах, в какие дни и по каким акционным условиям. Кроме того, это складская среда, поэтому необходима размерная модель.

  Шаг 2: Идентификация сущностей

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

  • Date Dimension
  • Product Dimension
  • Store Dimension
  • Promotion Dimension

  Количество, которое необходимо рассчитать (например, объем продаж, прибыль и т.д), будет отражено в таблице с фактическими продажами.

  Шаг 3: Концептуальная модель данных

  Предварительная модель данных будет создана на основе информации, собранной о сущностях. В нашем случае она будет выглядеть так:

  

  Шаг 4: Доработка атрибутов и создание логической модели данных

  Теперь необходимо завершить работу над атрибутами для сущностей. В нашем случае дорабатываются следующие атрибуты:

  Date Dimension:

  

  Product:

  

  Store:

  

  Promotion:

  

  Sales Fact:

  • Номер транзакции.
  • Объем продаж (например, количество банок овощного супа с лапшой).
  • Сумма продаж в долларах: количество продаж * цена за единицу.
  • Стоимость в долларах: стоимость продукта, взимаемая поставщиком.
  • Сумма валовой прибыли в долларах: доход от продаж — затраты.

  Логическая модель данных будет выглядеть так:

  

  Шаг 5: Создание физических таблиц в базе данных

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

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

  • моделирование данных
  • интернет-маркетинг
  • веб-аналитика
  • работа с данными
  • Веб-аналитика
  • Интернет-маркетинг
  Похожие публикации:

  1. Как вернуть в заметках текст на айфон
  2. Как закачать музыку на айпад с компьютера
  3. Как строку занести в массив c
  4. Что пишут на c в 2021