Стратегія автоматизації предметної області

У якості логічній моделі бази даних була обрана реляційна модель, оскільки саме реляційна модель використовується у більшості розвинених СКБД.

Для перетворення концептуальної моделі, представленої у вигляді мови ER–моделювання, у реляційну модель, був використаний наступний алгоритм.

• Крок 1. Перетворення сутностей у таблиці. Кожна сутність перетворюється у таблицю. Ім’я сутності представляється у вигляді семантично осмисленого імені у латинському алфавіті.
• Крок 2. Перетворення атрибутів у стовпці. Кожний атрибут перетвориться в стовпець. Ім’я атрибуту представляється у вигляді семантично осмисленого імені у латинському алфавіті. У цей момент уточнюється формат представлення значень стовпця. Факультативні атрибути стають NULL-стовпцями. Обов'язкові атрибути стають NOT NULL-стовпцями.
• Крок 3. Подання унікальних ідентифікаторів ключами таблиць. Складові унікального ідентифікатора сутності стають первинним ключем таблиці. Нагадаємо, що сутність може мати більш ніж один унікальний ідентифікатор Тому вибирається той, котрий використовується найбільше часто. Всі інші унікальні ідентифікатори приймають обмеження цілісності UNIQUE NOT та NOT NULL.

 

 

 

 

 

 

 

 

 

 

Рис.1 Концептуальна ER-модель проходження практики студентами

Сутність може унікально ідентифікуватися комбінацією атрибутів і/або  зв'язків. При  використанні в ідентифікаторі сутності зв'язку до складу первинного ключа включається зовнішній ключ, який посилається на ту таблицю, з якою пов’язаний той або інший зв’язок.

• Крок 4. Перетворення зв'язків багато-до-одного й один-до-одного в зовнішні ключі. Зв'язки типу багато-до-одного й один-до-одного породжують зовнішні ключі. Інакше кажучи, необхідно взяти унікальні ідентифікатори  кожної сутності, розташованої в закінчення зв'язку зі ступенем один, і ввести його у відношення, розташоване з боку зв'язку "багато" як стовпці. Факультативним зв'язкам відповідають NULL-стовпці. Обов'язковим зв'язкам відповідають NOT NULL-стовпці.
• Крок 5. Введення спеціальних первинних ключів. Для більш адекватного відображення логічного проекту бази даних у фізичний, вводимо у всі таблиці один спеціальний стовпець з обмеженням цілісності первинного ключа. Всі ті стовпці, які мають властивість первинного ключа згідно з концептуальною моделлю, набувають обмеження цілісності UNIQUE та NOT NULL.

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

Таблиця 1. Відношення сутності КЛІЄНТ

client

Ім’я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
Cli_ID	ціле число	10	Унікальний ID	Первинний ключ
First_name	строка	50	Ім’я клієнта	Обов’язковий
Last_name	строка	50	Прізвище клієнта
Middle_name	строка	50	По батькові клієнта
Birthday	дата		Дата народження клієнта
Telephone	строка	15	Телефон клієнта	Обов’язковий
Address	текст		Адреса доставки	Обов’язковий
man_ID	ціле число	10	Менеджер який веде клієнта	Зовнішній ключ, що посилається на первинний ключ відношення Менеджер. Обов’язковий
Date_reg	дата		Дата реєстрації клієнта в БД	Обов’язковий

 

Таблиця 2. Відношення сутності ЗАМОВЛЕННЯ

order

Ім’я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
Ord_ID	ціле число	10	Унікальний ID замовлення	Первинний ключ
Date_order	дата		Дата замовлення	Обов’язковий
Curr_goods	ціле число	10	Ідентифікатор товару	Зовнішній ключ, що посилається на первинний ключ відношення ТОВАР.
Count_goods	ціле число	15	Кількість товару	Обов’язковий
Reserve	ціле число	1	Признак резервування замовлення
Manager_ID	ціле число	10	Ідентифікатор менеджера, який буде виконувати замовлення клієнта	Зовнішній ключ, що посилається на первинний ключ відношення МЕНЕДЖЕР.
Cli_ID	ціле число	10	Ідентифікатор клієнта	Зовнішній ключ, що посилається на первинний ключ відношення КЛІЄНТ. Обов’язковий
Price	ціле число	15	Ціна товару	Обов’язковий

 

Таблиця 3. Відношення сутності ПРОДАЖА

Sale

Ім’я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
Sale_ID	ціле число	10	Унікальний ID продажи	Первинний ключ
Ord_ID	ціле число	10	Ідентифікатор замовлення	Зовнішній ключ, що посилається на первинний ключ відношення ЗАМОВЛЕННЯ. Обов’язковий
Date_Sale	дата		Дата продажи	Обов’язковий
Curr_goods	ціле число	10	Ідентифікатор товару	Зовнішній ключ, що посилається на первинний ключ відношення ТОВАР.
Count_goods	ціле число	15	Кількість товару	Обов’язковий
Manager_ID	ціле число	10	Ідентифікатор менеджера, який ФАКТИЧНО виконав  замовлення клієнта	Зовнішній ключ, що посилається на первинний ключ відношення МЕНЕДЖЕР.
Price	ціле число	15	Ціна товару	Обов’язковий

 

Таблиця 4. Відношення сутності ТОВАР

good

Ім’я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
Good_ID	ціле число	10	Унікальний ID товару	Первинний ключ
Good_name	строка	255	Назва товару	Обов’язковий
Count_of_good	ціле число	15	Кількість товарів на складі	Обов’язковий
article	текст		Артикул
Country_of_origin	текст		Країна-виробник
Quality	строка	30	Якість товару

 

 

Таблиця 5. Відношення сутності МЕНЕДЖЕР

Manager

Ім’я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
man_ID	ціле число	10	Унікальний ID МЕНЕДЖЕРА	Первинний ключ
First_name	строка	50	Ім’я менеджера	Обов’язковий
Last_name	строка	50	Прізвище менеджера	Обов’язковий
Middle_name	строка	50	По батькові менеджера	Обов’язковий
Birthday	дата		Дата народження менеджера	Обов’язковий
Sex	ціле число	1	Стать менеджера	Обов’язковий
Address	текст		Адреса менеджера	Обов’язковий
Family	текст		Склад родини менеджера	Обов’язковий
Date_start	дата		Дата прийому на роботу	Обов’язковий
Date_end	дата		Дата завершення роботи

 

2. Фізичне проектування

База даних спроектована для її збереження у СУБД MySQL, яка підтримує реляційну модель даних. Ця СУБД має дуже розвинені можливості по створенню та супроводу баз даних, володіє можливостями індексування полів, що дозволяє одержувати доступ до даних за мінімальний час, а також функціями по забезпеченню підтримки цілісності даних між реляційними  таблицями, що дозволяє розробнику мінімізувати тимчасові витрати на створення бази даних, а кінцевому користувачеві витрати на підтримку цілісності збережених даних і одержання даних з бази даних.  Робота з базою даних підтримується за допомогою реляційної мови запитів SQL.

Логічна модель бази даних легко відображається в реляційну фізичну модель, оскільки логічна модель була побудована з використанням реляційної структури даних. Крім того, логічна модель була приведена у третю нормальну форму, тому усі відношення представляються у фізичній моделі окремими таблицями. Ніякі злиття відношень в одну таблицю для підвищення ефективності виконання окремих класів запитів не виконуються у зв’язку  з тим, що такі класи запитів не були знайдені. У результаті отримано сімнадцять таблиць реляційної бази даних, де кожне відношення прямо відповідає окремій таблиці, атрибути кожного відношення стають полями цієї таблиці, а первинні ключі відношень стають первинними ключами таблиць.

1. Скрипти створення бази даних

-- Створення таблиці client

CREATE TABLE IF NOT EXISTS `client` (

  `cliID` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `First_name` varchar(50) NOT NULL,

  `Last_name` varchar(50) NOT NULL,

  `Middle_name` varchar(50) NOT NULL,

  `Birthday` date NOT NULL,

  `Telephone` varchar (25) NOT NULL,

  `Address` text NOT NULL,

  `menID` int(10) unsigned NOT NULL,

  `Date_reg` date NOT NULL,

  PRIMARY KEY (`cliID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

 

-- Створення таблиці order

CREATE TABLE IF NOT EXISTS `order` (

  `Ord_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `Date_order` date DEFAULT NULL,

  `Curr_goods` int(10) unsigned NOT NULL,

  `Count_goods` int(15) unsigned NOT NULL,

  ` Reserve` tinyint(1) unsigned NOT NULL,

  `Manager_ID` int(10) unsigned NOT NULL,

  `Cli_ID` int(10) unsigned NOT NULL,

  `Price` int(25) unsigned NOT NULL,

  PRIMARY KEY (`Ord_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

 

-- Створення таблиці sale

CREATE TABLE IF NOT EXISTS `sale` (

  `Sale_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `Ord_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `Date_sale` date DEFAULT NULL,

  `Curr_goods` int(10) unsigned NOT NULL,

  `Count_goods` int(15) unsigned NOT NULL,

  `Manager_ID` int(10) unsigned NOT NULL,

  `Cli_ID` int(10) unsigned NOT NULL,

  `Price` int(25) unsigned NOT NULL,

  PRIMARY KEY (`Sale_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

 

 

-- Створення таблиці good

CREATE TABLE IF NOT EXISTS `good` (

  `Good_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `Good_name` varchar(255) NOT NULL,

  `Count_of_good` int(15) unsigned NOT NULL,

  `article` varchar (15) NOT NULL,

  `Country_of_origin` varchar (30) NOT NULL,

  `Quality` varchar (30) NOT NULL,

  PRIMARY KEY (`measID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

 

-- Створення таблиці manager

CREATE TABLE IF NOT EXISTS ` manager ` (

  `man_ID` int(10) unsigned NOT NULL,

  `First_name` varchar(50) NOT NULL,

  `Last_name` varchar(50) NOT NULL,

  `Middle_name` varchar(50) NOT NULL,

  `Birthday` date NOT NULL,

  `Sex` tinyint(1) unsigned NOT NULL,

  `Address` text NOT NULL,

  `Date_start` date NOT NULL,

  `Date_end` date NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

 

Обмеження цілісності таблиць

 

-- Додавання зовнішнього  ключа з поля man_ID сутності КЛІЄНТ на поле man_ID -- сутності МЕНЕДЖЕР

ALTER TABLE `client` ADD FOREIGN KEY(`man_ID`) REFERENCES `manager`(`man_ID`) ON DELETE CASCADE;

 

-- Додавання зовнішнього  ключа з поля cliID сутності КЛІЄНТ на поле Cli_ID  -- сутності ЗАМОВЛЕННЯ

ALTER TABLE `client` ADD FOREIGN KEY(`man_ID`) REFERENCES `order` (`man_ID`) ON DELETE CASCADE;

 

-- Додавання зовнішнього  ключа з поля Good_ID сутності ТОВАР на поле 

-- Curr_goods сутності ЗАМОВЛЕННЯ

ALTER TABLE `good` ADD FOREIGN KEY(`Good_ID`) REFERENCES `order` (`Curr_goods`) ON DELETE CASCADE;

 

-- Додавання зовнішнього ключа з поля man_ID сутності МЕНЕДЖЕР на поле 

-- Manager_ID сутності ЗАМОВЛЕННЯ

ALTER TABLE `manager` ADD FOREIGN KEY(`man_ID`) REFERENCES `order` (`Manager_ID`) ON DELETE CASCADE;

 

-- Додавання зовнішнього  ключа з поля cliID сутності КЛІЄНТ на поле Cli_ID  -- сутності ПРОДАЖИ

ALTER TABLE `client` ADD FOREIGN KEY(`man_ID`) REFERENCES `sale` (`man_ID`) ON DELETE CASCADE;

 

-- Додавання зовнішнього  ключа з поля Ord_ID сутності КЛІЄНТ на поле Ord_ID  -- сутності ПРОДАЖИ

ALTER TABLE `order` ADD FOREIGN KEY(`Ord_ID`) REFERENCES `sale` (`Ord_ID `) ON DELETE CASCADE;

 

 

-- Додавання зовнішнього  ключа з поля cliID сутності КЛІЄНТ на поле Cli_ID  -- сутності ПРОДАЖИ

ALTER TABLE `client` ADD FOREIGN KEY(`man_ID`) REFERENCES `sale` (`man_ID`) ON DELETE CASCADE;

 

 

-- Додавання зовнішнього  ключа з поля Good_ID сутності ТОВАР на поле 

-- Curr_goods сутності ПРОДАЖИ

ALTER TABLE `good` ADD FOREIGN KEY(`Good_ID`) REFERENCES `sale` (`Curr_goods`) ON DELETE CASCADE;

 

-- Додавання зовнішнього  ключа з поля man_ID сутності МЕНЕДЖЕР на поле 

-- Manager_ID сутності ПРОДАЖИ

ALTER TABLE `manager` ADD FOREIGN KEY(`man_ID`) REFERENCES `sale` (`Manager_ID`) ON DELETE CASCADE;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5. Висновки

Проектування баз даних — це складний, багатокроковий процес перетворення інформаційного середовища ПО у інформаційну модель у вигляді бази даних. Цей процес складається з різних етапів, а саме: розробка стратегії автоматизації, аналіз ПО, побудова концептуальної моделі ПО, логічне та фізичне проектування БД. На сучасному етапі розвитку інформатики проектування баз даних перетворилося на цілком сформовану наукову дисципліну, яка має у своєму складі формально-теоретичну та технологічну складові. Теоретичної основою проектування баз даних є теорія нормалізації, яка дозволяє чітко і строго відповісти на таке запитання: як слід проводити перетворення початкової схеми ПО таким чином, щоб результуюча схема бази даних була еквівалентна початковій і була краща за неї. Методологія проектування детально описує усі етапи життєвого циклу створення бази даних з використанням сучасних мов опису ПО.