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

5.3. Генерация базы данных физического уровня в среде субд Access

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

Рис. 28. Пустая БД с именем test_db в СУБД Access

Затем открываем ER-диаграмму в среде ERwin и с помощью списка выбора в стандартной панели инструментов производим переключение между логической и физической моделью (рис.29). При переключении, если физической модели еще не существует, она будет создана автоматически. Теперь надо выбрать СУБД, в которой будем производить генерацию БД физического уровня. Для этого следует выполнить команду DATABASE / Choose database, в появившемся диалоговом окне (рис. 30) выбрать интересующую СУБД Access и щелкнуть по кнопке <ОК>.

Рис. 29. Физическая модель БД в ERwin

Для установления соединения БД изERwincцелевой СУБДAccessнеобходимо выполнить командуDATABASE/Database connection. В появившемся диалоговом окне (рис.31) необходимо указать путь к БД в СУБДAccess, вписать имяadminи нажать кнопкуConnect. Для генерации БД физического уровня в среде СУБДAccessнеобходимо выполнить командуTOOLS / Forward Engineering / Schema Generation.В результате получаем диалог генерации схемы БД (рис. 32), который имеет 3 закладки:

Рис. 30. Диалог выбора СУБД (сервера)

Рис. 31. Диалог присоединения к СУБД Access

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

Рис. 32. Диалог генерации схемы БД

Во вкладке Summary отображаются все опции, заданные во вкладкеOptions. Список опций вSummary можно редактировать так же, как и вOptions.

Comment позволяет внести комментарий для каждого набора опций. Каждый набор опций может быть именован (окноOptionSet, кнопкиNew,RenameиDelete) и использован многократно.

Set ERwinTableDef = ERwinDatabase.CreateTableDef(«prodavec»)

Set ERwinField = ERwinTableDef.CreateField(«IDprod», DB_LONG)

Set ERwinField = ERwinTableDef.CreateField(«fio», DB_TEXT, 20)

Set ERwinField = ERwinTableDef.CreateField(«vozrast», DB_TEXT, 20)

Set ERwinField = ERwinTableDef.CreateField(«adress», DB_TEXT, 20)

‘ CREATE TABLE «tovar»

Set ERwinTableDef = ERwinDatabase.CreateTableDef(«tovar»)

Set ERwinField = ERwinTableDef.CreateField(«IDtov», DB_LONG)

Set ERwinField = ERwinTableDef.CreateField(«naimenovanie», DB_LONG)

Set ERwinField = ERwinTableDef.CreateField(«cena», DB_LONG)

Set ERwinField = ERwinTableDef.CreateField(«proizvoditel», DB_TEXT, 20)

Рис. 33. Программа генерации таблиц БД (SQL-скрипты)

Кнопка Preview вызывает диалог Schema Generation Preview, в котором отображается SQL-скрипт, создаваемый ERwin для генерации системного каталога СУБД (рис. 33).

Кнопка Printдиалога предназначена для вывода на печать создаваемогоERwinSQL-скрипта.

Кнопка Reportсохраняет тот же скрипт вERS- илиSQL-текстовом файле. Эти команды можно в дальнейшем редактировать любым текстовым редактором и выполнять при помощи соответствующей утилиты сервера.

Рис. 34. ДиалогGenerateDatabaseSchema

Нажатие на кнопку Generateприведет к запуску процесса генерации схемы. Возникает диалог связи с базой данных, устанавливается сеанс связи с сервером-базы данных (СУБДAccess), и начинает выполнятьсяSQL-скрипт. При этом возникает диалогGenerate Database Schema(рис. 34).

По умолчанию в диалоге Generate Database Schemaвключена опцияStop If Failure. Это означает, что при первой же ошибке выполнение скрипта прекращается. Щелкнув по кнопкеContinue, можно продолжить выполнение. КнопкаAbortпрерывает выполнение. При выключенной опцииStop If Failureскрипт будет выполняться, несмотря на встречающиеся ошибки.

Для выполнения обратного проектирования следует выбрать пункт меню Tools/Reverse Engineer.

После выполнения скриптов (рис. 33) в среде СУБД Access создается БД физического уровня (рис. 36).

Рис. 36. Структура БД физического уровня в СУБД Access

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

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

22) Инструменты проектирования диаграмм базы данных

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

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

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

1) Dbdiagram.io

Dbdiagram.io – это простой инструмент для рисования диаграмм ER (Entity Relationship) простым написанием кода. Он предназначен для разработчиков и аналитиков данных.

Особенности:

• Вы можете напрямую генерировать операторы SQL.
• Это позволяет вам поделиться своими диаграммами с вашими коллегами.
• Интеграция с веб-фреймворками, такими как Django, Rails и т. Д.
• Возможно создание диаграмм из баз данных SQL.
• Диаграммы Dbdiagram.io можно экспортировать в файлы изображений и PDF.
• Он поддерживает как передовой инжиниринг, так и реверс-инжиниринг.

2) SqlDBM

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

Особенности:

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

3) Dbdesigner.net

Dbdesigner.net – это онлайн-инструмент для проектирования и моделирования схем баз данных. Это приложение позволяет создавать базы данных без подключения единого кода SQL.

Особенности:

• Он имеет удобный пользовательский интерфейс для проектирования структуры базы данных.
• Инструмент предлагает командную совместную работу и обмен проектами в вашей организации.
• Это позволяет импортировать существующую базу данных или начать с нуля.
• Вы можете сотрудничать с вашей командой и работать над моделью вместе.
• Dbdesigner.net поможет вам экспортировать вашу базу данных в форматах PNG и PDF.

4) Визуальная Парадигма

Visual Paradigm – это инструмент проектирования и управления базой данных. Это программное обеспечение помогает команде разработчиков создавать приложения быстрее.

Особенности:

• Он имеет редактор перетаскивания диаграмм.
• Этот инструмент позволяет вам экспортировать базу данных из ERD (Entity Relationship Diagram).
• Он содержит REST API для проектирования базы данных.
• Вы можете работать со своей командой над одним проектом одновременно.
• Визуальная парадигма позволяет вам создать свой отчет.
• Он предоставляет мастера для создания базы данных шаг за шагом.
• Вы можете использовать Visual Paradigm в ОС Windows, MacOS и Linux.

5) Erwin Data Modeler

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

Особенности:

• Это позволяет извлекать данные из CRM, ERP и т. Д. Для точного моделирования.
• Erwin Data Modeler имеет простую в использовании графическую среду.
• Он автоматически сравнивает модель и базу данных.
• Вы можете управлять структурированными и неструктурированными данными из любой базы данных.

6) Moon Modeler

Moon Modeler – это инструмент для проектирования MariaDB и других баз данных. Это позволяет рисовать диаграммы и генерировать код SQL-сервера.

Особенности:

• Это позволяет вам проектировать вложенную структуру для проектирования базы данных.
• Этот инструмент имеет предопределенные настройки, которые помогут вам эффективно рисовать модели данных.
• Вы можете экспортировать диаграммы в печатный формат PDF.
• Moon Modeler позволяет связать схему базы данных с JSON (JavaScript Object Notation).
• Вы можете скачать это приложение для Windows или Linux.
• Это поможет вам в создании ERD.

7) Люсидчарт

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

Особенности:

• Этот инструмент позволяет вам соединять живые данные с вашими диаграммами.
• Lucidchart обеспечивает безопасность вашей диаграммы с помощью шифрования.
• Он легко интегрируется с MS Office, G Suite, Atlassian (приложение отслеживания проблем) и т. Д.
• Вы можете работать с вашей командой на любом устройстве на разных платформах.
• Это помогает вам легко управлять учетными записями пользователей.
• Вы можете импортировать данные для автоматического построения организационных диаграмм.

8) QuickDBD

QuickDBD это программа, которая поможет вам быстро нарисовать диаграмму базы данных. Это поможет вам сделать ваш документ профессиональным.

Особенности:

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

9) Мир жаб

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

Особенности:

• Быстрый доступ к ключевым данным для анализа.
• Он может легко выявлять различия, сравнивая и синхронизируя серверы, данные и схемы.
• Откат транзакций непосредственно из журнала транзакций без необходимости восстановления из резервной копии.
• Получите мощные возможности настройки запросов.
• Выполните сценарии и фрагменты T-SQL для многочисленных экземпляров и серверов.
• Автоматизируйте повторяющиеся процессы, такие как сравнение данных и схем.

10) Датаедо

Dataedo – это приложение, которое позволяет создавать словари данных, диаграммы ER и сценарии сервера документов. Это позволяет вам легко документировать ваши реляционные базы данных.

Особенности:

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

11) Вертабело

Vertabelo – это инструмент для визуального проектирования баз данных. Это поможет вам создать базу данных на логическом и физическом уровне.

Особенности:

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

12) Dmodelaid

DModelAid – это онлайн-программа для документирования дизайна базы данных в виде интерактивной диаграммы. Это поможет вам получить большое количество записей из базы данных с помощью запросов SQL.

Особенности:

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

13) SchemaSpy

SchemaSpy – это программное обеспечение на основе Java, которое анализирует метаданные схемы в вашей базе данных. Это поможет вам упростить процесс проектирования базы данных.

Особенности:

• Он поддерживает JDBC (Java Database Connectivity) совместимые СУБД.
• Вы можете создать диаграмму ER для внешних ключей.
• Schemaspy может создавать базу данных в HTML.

14) DeZign

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

Особенности:

• Он предоставляет простой в использовании и надежный инструмент моделирования данных для разработчиков.
• Вы можете использовать этот инструмент для визуального составления Диаграммы отношений сущностей (ERD).
• Перемещение большой диаграммы с панорамированием и масштабированием окна.
• Он поддерживает ряд методов моделирования данных.
• Последняя версия этого инструмента поможет вам уменьшить количество ошибок при разработке баз данных.
• DeZign использует ERD для графического проектирования базы данных.
• Возможен экспорт диаграмм в растровые изображения, PNG, JPEG и GIF.

15) Конструктор баз данных для MySQL

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

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

В работе мы с коллегами часто видим как компании сталкиваются с проблемой управления данными – когда таблиц и запросов становится сильно много и управлять всем этим очень сложно. В таких ситуациях мы рекомендуем моделировать данные. Чтобы разобраться, что это такое – я перевела статью-обзор про моделирование данных от 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: Идентификация сущностей

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

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

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

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

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

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

Date Dimension:

Product:

Store:

Promotion:

Sales Fact:

Объем продаж (например, количество банок овощного супа с лапшой).

Сумма продаж в долларах: количество продаж * цена за единицу.

Стоимость в долларах: стоимость продукта, взимаемая поставщиком.

Сумма валовой прибыли в долларах: доход от продаж — затраты.

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

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

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

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

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

При описании логической модели базы данных используются такие термины, как "сущность", "экземпляр", "связь", "атрибут". При переходе от логической к физической модели реляционной БД нужно будет работать с таблицами, записями, полями и прочими объектами и ограничениями, поддерживаемыми выбранной СУБД. При этом для построения диаграмм для физической модели в среде 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