Приклад виконання роботи

Особливості діалекту SQL у СУБД MySQL розглянемо на прикладі навчальної бази даних book Інтернет-магазина, що торгує комп'ютерною літературою. У базі даних повинна підтримуватися наступна інформація:

· тематичні каталоги, по яких згруповані книги;

· пропоновані книги (назва, автор, рік видання, ціна, наявне на складі кількість);

· зареєстровані покупці (ім'я, по батькові, прізвище, телефон, адреса електронної пошти, статус - авторизований, неавторизований, заблокований, активний з гарною кредитною історією);

· покупки, зроблені в магазині (час здійснення покупки, число придбаних екземплярів книги).

Логічна модель даних предметної області в стандарті IDEF1X представлена на мал. 1. Виділено сутності КАТАЛОГ, КНИГА, КЛІЄНТ, ЗАМОВЛЕННЯ, між якими встановлені неідентифікуючі зв'язки потужністю oдин-ко-многим, певною специфікою предметної області.

Рис. 1. Логічна модель даних предметної області

Фізична модель даних предметної області в стандарті IDEF1X для цільовий СУБД MySQL представлена на мал. 2.

Рис. 2. Фізична модель предметної області

База даних book складається із чотирьох таблиць:

· catalogs – список торговельних каталогів;

· books – список пропонованих книг;

· users – список зареєстрованих користувачів магазина;

· orders – список замовлень (здійснених угод).

Таблиця catalogs складається із двох полів:

· cat_ID – унікальний код каталогу;

· cat_name – ім'я каталогу.

Обоє поля повинні бути постачені атрибутом not null, оскільки невизначене значення для них неприпустимо.

Таблиця books складається із семи полів:

· book_ID – унікальний код книги;

· b_name – назва книги;

· b_author – автор книги;

· b_year – рік видання;

· b_price – ціна книги;

· b_count – кількість книг на складі;

· b_cat_ID – код каталогу з таблиці catalogs.

Ціна книги b_price і кількість екземплярів на складі b_count можуть мати атрибут null. На момент доставки часто невідома кількість товару і його ціна, але відбити факт наявності товару в прайс-листе необхідно.

Поле b_cat_ID встановлює зв'язок між таблицями catalogs й books. Це поле повинне бути оголошене як зовнішній ключ (FK)із правилом каскадного видалення й відновлення. Відновлення таблиці catalogs викличе автоматичне відновлення таблиці books. Видалення каталогу в таблиці catalogs приведе до автоматичного видалення всіх записів у таблиці books, що відповідають каталогу.

Таблиця users складається із семи полів:

· user_ID – унікальний код покупця;

· u_name – ім'я покупця;

· u_patronymic – по батькові покупця;

· u_surname – прізвище покупця;

· u_phone – телефон покупця (якщо є);

· u_email – e-mail покупця (якщо є);

· u_status – статус покупця.

Статус покупця представлений полем типу enum, що може приймати одне із чотирьох значень:

· active – авторизований покупець, що може здійснювати покупки через Інтернет;

· passive – неавторизований покупець (значення за замовчуванням), що здійснив процедуру реєстрації, але не підтвердив її й поки не може здійснювати покупки через Інтернет, однак йому доступні каталоги для перегляду;

· lock – заблокований покупець, не може здійснювати покупки й переглядати каталоги магазина;

· gold – активний покупець із гарною кредитною історією, якому надається знижка при наступних покупках у магазині.

Поля u_phone й u_email можуть бути постачені атрибутом null. Інші поля повинні одержати атрибут not null.

Таблиця orders включає п'ять полів:

· order_ID – унікальний номер угоди;

· o_user_ID – номер користувача з таблиці users;

· o_book_ID – номер товарної позиції з таблиці books;

· o_time – час здійснення угоди;

· o_number – число придбаних товарів.

Поля таблиці orders повинні бути постачені атрибутом not null, тому що при здійсненні покупки вся інформація повинна бути занесена в таблицю.

В таблиці orders установлюється зв'язок з таблицями users (за рахунок поля o_user_id) і books (за рахунок поля o_book_id). Ці поля оголошені як зовнішні ключі (FK)із правилом каскадного видалення й відновлення. Відновлення таблиць users й books приведе до автоматичного відновлення таблиці orders. Видалення будь-якого користувача в таблиці users приведе до автоматичного видалення всіх записів у таблиці orders, що відповідають цьому користувачеві.