Як побудувати схему в роботібанку. Від моделі до фізичної БД до MySQL WorkBench. Створення та редагування моделі даних

Всім вебмайстрам доводиться заглиблюватися в програмування та технології створення баз даних після розвитку їх ресурсів.

Поступово починаєш вникати у кожну тонкість, але навчальний матеріал не завжди легко знайти. Плюс до всього, не всі знають про існування корисних програм.

Як створити базу даних MySQL? Можна створювати таблиці вручну та встановлювати між ними зв'язки, але це не так зручно.

Спеціально для цього було створено безкоштовна програма Workbench. З її допомогою можна візуально створювати бази даних MySQL. Змоделювати їх за допомогою софту простіше, зручніше та в рази швидше.

Workbench допоможе створити базу даних MySQL

За допомогою одного простого інструменту не доведеться описувати структуру. Програма автоматично генерує код. Завантажуйте утиліту з цього сайту, вона підходить для будь-якої операційної системи.

Після звичайної установки програми, щоб створити базу даних MySQL необхідно виконати такі дії:

1. Спочатку потрібно відкрити нову модель, це робиться через меню або комбінацією клавіш Ctrl+N:




2. Перший крок при створенні бази даних, це додавання таблиці, тому вибираємо відповідну функцію:




3. Далі виконується заповнення таблиці. Вказуйте ім'я та атрибути, враховуйте, що один із атрибутів це головний ключ, позначений прапорцем. Заздалегідь думайте, як потім таблиці зв'язуватимуться між собою:




4. Заповнивши необхідні дані, створюйте діаграму визначення зв'язків між суб'єктами:



5. Ви побачите таблицю у робочій області. Для зручності можна розгорнути структури таблиць:




6. Тепер необхідно встановити зв'язок між елементами, це робиться спеціальним інструментом на робочій панелі:
7. У результаті у вас мають бути представлені таблиці в робочій області і між ними встановлені зв'язки:




8. Подвійне клацання на зв'язку відкриває вікно, в якому задаються додаткові параметри:

Веб-розробник зростає разом із проектами, які він створює та розвиває. Зі зростанням проектів збільшується складність програмної частини, неминуче зростає кількість оброблюваних нею даних, а також складність схеми даних. Спілкування з іншими вебщиками показує, що величезною популярністю серед нас користуються бази даних MySQL, а для управління ними - відомий PHPMyAdmin. Переходячи від маленьких проектів до великих, від cms до фреймворків, багато хто, як і я, залишаються вірними MySQL. Однак для проектування складної бази даних з великою кількістю таблиць та зв'язків, можливостей PHPMyAdmin катастрофічно не вистачає. Тому я вирішив написати огляд MySQL Workbench - чудова безкоштовна десктопна програма для роботи з MySQL.

У першій частині огляду я розповім про самі основи роботи з програмою, так що можете використовувати цю статтю як керівництво користувача-початківця.Друга частина буде присвячена використанню Workbench у бою під час роботи з віддаленим сервером. У ній я дам базові інструкціїта рекомендації щодо налаштування підключення сервера та синхронізації з ним.

MySQL Workbench- Інструмент для візуального проектування баз даних, що інтегрує проектування, моделювання, створення та експлуатацію БД в єдине безшовне оточення для системи баз даних MySQL.

Мушу сказати, що програма справді чудова. Вона дозволяє швидко та із задоволенням накидати схеми даних проекту, проектувати сутності та зв'язкуміж ними, безболісно впроваджувати зміниу схему і так само швидко та безболісно синхронізуватиїї з віддаленим сервером. А графічний редактор EER-діаграм, що нагадують кумедних тарак, дозволяє побачити загальну картину моделі даних і насолодитися її легкістю та елегантністю:) Після першої ж проби цей інструмент стає незамінним помічником у бойовому арсеналі веб-програміста.

Завантажити MySQL Workbench

Дистрибутив MySQL Workbench доступний на цій сторінці. Найсвіжіша версія програми на момент написання статті Version 6.1. Перед завантаженням потрібно вибрати одну з наступних платформ:

• Microsoft Windows (доступні MSI Installerта ZIP архів)
• Ubuntu Linux
• Fedora
• Red Hat Enterprise Linux / Oracle Linux
• Mac OS X

Після вибору платформи вам пропонують зареєструватися або авторизуватись у Oracle. Якщо не хочете, унизу є посилання "No thanks, just start my download"- тисніть на неї;)

Початок роботи

Стартовий екран програми відображає основні напрямки її функціональності - проектування моделей баз даних та їх адміністрування:

У верхній частині екрана знаходиться список підключень до MySQL серверамваших проектів, а список останніх відкритих моделей даних – у нижній частині екрану. Робота зазвичай починається з створення схеми данихабо завантаження існуючої структури у MySQL Workbench. Приступимо до роботи!

Створення та редагування моделі даних

Для додавання моделі натискаємо плюсик поряд із заголовком "Models" або вибираємо "File → New Model" (Ctrl + N):

На цьому екрані вводимо ім'я бази даних, вибираємо стандартне кодування і, якщо потрібно, заповнюємо поле коментаря. Можна приступати до створення таблиць.

Додавання та редагування таблиці

Список баз даних проекту та список таблиць у межах бази даних розташовуватиметься у вкладці "Physical Schemas".Щоб створити таблицю, двічі клацаємо на "+Add Table":

Відкриється зручний інтерфейс для редагування списку полів та їх властивостей. Тут ми можемо встановити назву поля, тип даних, а також встановити для полів різні атрибути: призначити поле первинним ключем (PK), позначити його Not Null (NN), бінарним (BIN), унікальним (UQ)та інші, встановити для поля авто-інкремування (AI)і значення за замовчуванням (Default).

Управління індексами

Додавати, видаляти та редагувати індекси таблиць можна у вкладці "Indexes"інтерфейсу управління таблицею:

Вводимо назву індексу, вибираємо його тип, потім галочками позначаємо у потрібному порядку список полів, що беруть участь у цьому індексі. Порядок полів відповідатиме порядку, в якому було проставлено галочки. У цьому прикладі я додав унікальний індексдо поля username.

Зв'язки між таблицями

Встановлення зовнішніх ключів та зв'язування таблиць можливе лише для таблиць InnoDB(Ця система зберігання даних вибирається за умовчанням). Для керування зв'язками в кожній таблиці знаходиться вкладка "Foreign Keys":

Для додавання зв'язку відкриваємо вкладку "Foreign Keys" дочірньої таблиці, вводимо ім'я зовнішнього ключа та вибираємо таблицю-батька. Далі в середній частині вкладки у графі Columnвибираємо поле-ключ із дочірньої таблиці, а у графі Referenced Column- відповідне поле з батьківської таблиці (тип полів має збігатися). При створенні зовнішніх ключів у дочірній таблиці автоматично створюються відповідні індекси.

В розділі "Foreign Key Options"настроюємо поведінку зовнішнього ключа при зміні відповідного поля (ON UPDATE)та видаленні (ON DELETE)батьківського запису:

• RESTRICT- видавати помилку при зміні / видаленні батьківського запису
• CASCADE- оновлювати зовнішній ключ при зміні батьківського запису, видаляти дочірній запис при видаленні батька
• SET NULL- Встановлювати значення зовнішнього ключа NULLпри зміні/видаленні батька (неприйнятно для полів, у яких встановлено прапор NOT NULL!)
• NO ACTION- не робити нічого, проте за фактом ефект аналогічний RESTRICT

У наведеному прикладі я додав до дочірньої таблиці UserProfileзовнішній ключ для зв'язку з батьківською таблицею User. Під час редагування поля userIdта видалення позицій з таблиці Userаналогічні зміни будуть автоматичновідбуватися і зі зв'язаними записами з таблиці UserProfile.

Під час створення проекту до бази даних часто потрібно додавати стартові дані. Це може бути кореневі категорії, користувачі-адміністратори тощо. В управлінні таблицями MySQL Workbench для цього є вкладка "Inserts":

Як видно з прикладу, якщо перед записом в базу даних до даних потрібно застосувати якусь функцію MySQL, це робиться за допомогою синтаксису \func functionName("data")наприклад, \func md5("password").

Створення EER діаграми (діаграми "сутність-зв'язок")

Для представлення схеми даних, сутностей та їх зв'язків у графічному вигляді у MySQL Workbench існує редактор EER-діаграм. Для створення діаграми у верхній частині екрана керування базою даних двічі клацаємо на іконку "Add Diagram":

У його інтерфейсі можна створювати та редагувати таблиці, додавати між ними зв'язки різних типів. Щоб додати таблицю на діаграму, що вже існує в схемі, просто перетягніть її з панелі "Catalog Tree".

Для експорту схеми даних у графічний файлВиберіть "File → Export", а потім один із варіантів (PNG, SVG, PDF, PostScript File).

Імпорт існуючої схеми даних (з SQL дампа)

Якщо у нас вже є схема даних, її можна легко імпортувати в MySQL Workbench для подальшої роботи. Для імпорту моделі із SQL файлу вибираємо "File → Import → Reverse Engineer MySQL Create Script...", після чого вибираємо потрібний SQL файл і тиснемо "Execute >"

У MySQL Workbench також передбачено імпорт і синхронізація моделі даних нарямую з віддаленим сервером. Для цього потрібно створити підключення дистанційного доступудо MySQL, про які я розповім у продовженні цього огляду .

Демо-проект із статті доступний для завантаження за цим посиланням. Бажаю успіхів та гарних тарган схем!

Яким би не був розробник баз даних: початківець (особливо) або бородатий професіонал, йому завжди простіше і наочніше уявляти те, над чим він працює, що розробляє. Особисто я відношу себе до першої категорії і для розуміння матеріалу мені хотілося б бачити візуально те, що я проектую/розробляю.

На сьогоднішній день існують різні програмита інструменти, які справляються з подібним завданням: якісь краще, якісь гірші. Але сьогодні я хотів би трохи розповісти про MySQL WorkBench - інструмент для візуального проектування баз даних, що інтегрує проектування, моделювання, створення та експлуатацію БД в єдине безшовне оточення для системи баз даних MySQL, який є наступником DBDesigner 4 з FabForce. Wikipedia. MySQL WorkBench поширюється у двох варіантах: OSS - Comunity Edition(поширюється під ліцензією LGPL) та SE - Standard Edition- версія, за яку розробники просять гроші. Але я думаю, що багатьом достатньо буде і OSSверсії (особливо новачкам і тим, хто не хоче або вважає недоцільним платити за ПЗ, а також прихильникам програм з відкритим вихідним кодом), Тим більше, що OSS версія має багатий функціонал.

Отже, як видно з назви, цей інструмент призначений для роботи з MySQL базами даних,підтримує велику кількість різних типів моделей MySQL (див. скріншот нижче) та стане незамінним інструментом для кращого розуміння та вивчення реляційних БД (зокрема MySQL) для новачків:

Таким чином, будь-який розробник MySQL знайде те, що потрібно. Крім того MySQL WorkBenchдозволяє підключати існуючу базу даних, виконувати SQL запитиі SQL скрипти, редагувати і керувати об'єктами БД. EER моделібази даних. Інакше кажучи - це візуальне уявлення всіх зв'язків між таблицями Вашої БД, яке, за потреби, легко можна як SQL скрипта, відредагувати чи створити нове уявлення. Але про це трохи згодом. Для початку подивимося як виглядає головне око MySQL WorkBench(5.2.33 rev 7508)):
Для того щоб створити EER-модель Вашої БД вибираємо " Create New EER ModelУ результаті з'явиться вкладка, в якій ми можемо додавати/створювати діаграми, таблиці, види, процедури; задавати різні права доступу для користувачів; створювати модель за допомогою SQL скриптів. Виглядає дана вкладка таким чином:
Процес створення таблиць і БД не розглядатимемо, т.к. тут все просто. Наведу лише остаточний варіант готової моделі (див. на скріншотах нижче). Причому якщо навести курсор на лінію зв'язку (пунктирна лінія) таблиць, то "зв'язок", первинний ключ, а також зовнішній ключ виділяться іншим кольором. Якщо навести курсор на якусь таблицю, то виділиться сама таблиця, і навіть усі зв'язки, які належать обраної таблиці.

Для того, щоб відредагувати таблицю, достатньо натиснути ПКМ по потрібній таблиці і вибрати " Edit Table...". В результаті внизу вікна з'явиться додаткова область редагування таблиці, в якій можна змінити ім'я таблиці, стовпці, зовнішні ключі та багато чого іншого. Для того, щоб експортувати таблицю в SQL-скрипт достатньо натиснути ПКМ по потрібній нам таблиці вибрати " Copy SQL to Clipboard", а потім із буфера обміну вставити в потрібне місце/програму/файл.

А тепер безпосередньо про установці MySQL WorkBench. Природно, що спочатку потрібно завантажити MySQL WorkBench. Для цього переходимо на сторінку завантаження MySQL WorkBench , внизу сторінки в списку вибираємо потрібну нам операційну систему. В результаті нам буде запропоновано кілька варіантів завантаження:

• для ОС Windowsможна завантажити MSI інсталятор, zip архів програми, а також архів з вихідним кодом. Для цієї ОС MySQL WorkBenchможна завантажити лише для 32-розрядної версії Windows;
• для користувачів Ubuntuвибір трохи багатший, ніж для користувачів ОС Windows - нам пропонують завантажити MySQL WorkBenchдля Ubuntu версій 10.04, 10.10 (на момент написання статті) та 32- або 64-розрядних версій deb пакетів;
• для rpm-basedдистрибутивів, а в даному випадку це Fedora, Suse Linux та RedHat/Oracle Linux, MySQL WorkBenchпредставлені збірки для 32- та 64-розрядної ОС;
• не забули і користувачів Macintosh - для них є збірка тільки для 32-розрядної ОС;
• ну і природно можна завантажити вихідний кодпрограми;

Отже, вибираємо необхідний варіант завантаження та натискаємо DownLoad. Потім нас люб'язно попросять представитися: для зареєстрованих користувачів – ввести логін та пароль, для новачків – зареєструватися. Якщо Ви не бажаєте представлятися, то вибираємо трохи нижче варіант " » No thanks, just take me to the downloads!" і вибираємо найближче дзеркало для завантаження. Крім того, перед встановленням подбайте про те, щоб у вас був встановлений MySQL Client,.а інакше MySQL WorkBench відмовиться встановлюватись.

Про що потрібно пам'ятати та знати користувачам Linux:

Звичайно, як і для випадку з ОС Windows не забуваємо про MySQL Client. Для користувачів Ubuntu - необхідно качати версію програми, відповідно до версії Вашої Ubuntu. Під час установки уважно дивимося на повідомлення про помилку, якщо такі з'явилися, в яких, напевно, буде сказано про те, яких пакетів не вистачає у Вашій ОС. Про це читаємо нижче.

Як справи з rmp-base дистрибутивами, я на жаль не знаю, т.к. жодного разу такими дистрибутивами не користувався, але думаю, що приблизно так само, як і з debian-based.

Можливо Ви помітили, що відсутня збірка MySQL WorkBenchдля ОС Debian GNU/Linux . Але, як показала практика – нічого страшного. Для установки MySQL WorkBenchу Debian 6.0 (Squeeze) ми скористаємося deb- пакетом для Ubuntu 10.04(Не забуваємо про розрядність Вашої ОС: x86 або x64). Нагадаю, для того, щоб встановити скачаний deb пакет можна скористатися утилітою gdebiабо ввести в консолі від імені рута команду:

# dpkg -i mysql-workbench-gpl-5.2.33b-1ubu1004-amd64.deb Наприклад, у мене під час встановлення MySQL WorkBench виникла така помилка:
dpkg: залежності пакетів не дозволяють налаштувати пакет mysql-workbench-gpl:
mysql-workbench-gpl залежить від libcairomm-1.0-1 (>= 1.6.4), однак:
Пакет libcairomm-1.0-1 не встановлено.
mysql-workbench-gpl залежить від libctemplate0, однак:
Пакет libctemplate0 не встановлений.
mysql-workbench-gpl залежить від libgtkmm-2.4-1c2a (>= 1:2.20.0), однак:
Пакет libgtkmm-2.4-1c2a не встановлено.
mysql-workbench-gpl залежить від libpangomm-1.4-1 (>= 2.26.0), однак:
Пакет libpangomm-1.4-1 не встановлено.
mysql-workbench-gpl залежить від libzip1 (>= 0.9), однак:
Пакет libzip1 не встановлено.
mysql-workbench-gpl залежить від python-paramiko, проте:
Пакет python-paramiko не встановлено.
mysql-workbench-gpl залежить від python-pysqlite2, однак:
Пакет python-pysqlite2 не встановлено.
dpkg: не вдалося обробити параметр mysql-workbench-gpl (--install):
проблеми залежностей - залишаємо не налаштованим
Під час обробки наступних пакетів відбулися помилки:
mysql-workbench-gpl

Для усунення цієї помилки мені достатньо було набрати в консолі команду для встановлення деяких пакетів:

# aptitude install libzip1 libcairomm-1.0-dev libctemplate0 libgtkmm-2.4-1c2a

Для встановлення, наведених вище пакетів, будуть потрібні ще додаткові пакети, які менеджер aptлюб'язно запропонує завантажити. Після встановлення всіх пакетів MySQL WorkBench встановлюється без проблем.

Все: MySQL WorkBench благополучно встановлений та готовий для вивчення роботи.

upd:
Якщо не помиляюся, то починаючи з Ubuntu 12.04, MySQL WorkBench можна знайти в репозиторіях дистрибутива. Отже, процес установки проходить набагато простіше і без будь-яких милиць.
Для встановлення MySQL WorkBench достатньо в терміналі ввести команду:
sudo aptitude install mysql-workbench

Увага, оскільки WorkBench оновився, то я написав , Що складається з теорії та практики побудови БД з WorkBench.

В розділі "Foreign Key Options"настроюємо поведінку зовнішнього ключа при зміні відповідного поля (ON UPDATE)та видаленні (ON DELETE)батьківського запису:

• RESTRICT– видавати помилку при зміні / видаленні батьківського запису
• CASCADE– оновлювати зовнішній ключ при зміні батьківського запису, видаляти дочірній запис при видаленні батьків
• SET NULL– встановлювати значення зовнішнього ключа NULLпри зміні/видаленні батька (неприйнятно для полів, у яких встановлено прапор NOT NULL!)
• NO ACTION- не робити нічого, проте за фактом ефект аналогічний RESTRICT

Збереження з моделі в реальну/фізичну базу даних

“File → Export→ Forward Engineer MySQL Create Script…”

Зазначаємо необхідні галочки, мені потрібна була лише одна Generate INSERT Statements for Tables. Якщо потрібно зберегти скрипт у файл, пропишіть директорію в полі зверху.

У наступному вікні можна налаштувати – які об'єкти ми експортуватимемо. Якщо уважно придивитися, то у нас створено лише 2 таблиці.

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

Тиснемо на “будиночок” у верхньому лівому кутку програми…

Потім 2 рази натискаємо на MyConnection….

Перед нами відкривається така вкладка.

Це наше з'єднання з сервером, тут ми будемо виконувати наш скрипт. Зверніть увагу зліва бази даних, які були створені в програмі WorkBench….

Тепер, потрібно дати команду цей скрипт виконати, для цього тиснемо у верхньому меню Query Execute (All or Selection)

Отже, якщо все нормально, то в нижньому вікні output ви побачите всі "зелені галочки". А коли натиснете Refresh у контекстному менюУ списку баз даних, то побачите новостворену базу mydatabase1.

Насамкінець, побудуємо ER діаграму. ER розшифровується як Entity Relation - вдала модель "Сутність - зв'язок", яка, зокрема, розроблялася Пітером Ченом. Отже, повертаємось на вкладку моделі та тиснемо на Add Diagramm…

Ми створили зв'язок один до багатьох. На одному факультеті можуть навчатися кілька студентів. Зверніть увагу, що зв'язок біля таблиці Students розщеплюється – це означає “до багатьох”.

Отже, ми створили модель з неї через виконання скрипта – реальну базу з таблицями. Також створили діаграму ER.