Реалізація бази даних в СУБД

ВСТУП

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

Теорія розробки баз даних порівняно молода область комп’ютерної науки, однак бази являються сьогодні основою таких напрямків в розробці автоматизованих систем обробки інформації як системи штучного інтелекту, експертні системи, системи автоматизованого конструкторського і технологічного проектування [1].

Застосування комп’ютера для накопичення, зберігання та використання інформації почалося ще в 60-ті роки. Тоді ж створювалися перші бази даних засновані на мережевій моделі та перші СУБД – програмні системи управління базами даних, що забезпечують користувачеві оперативне отримання потрібної інформації. СУБД має всі інструменти і можливості для роботи з базою даних, тому для ефективної роботи із СУБД користувач повинен мати деякі знання та навики роботи. Але часто користувачі працюють тільки з певними часинами баз даних в чітко сформованих межах. Для організації такої роботи можуть створюватися окремі програмні продукти, що забезпечують зручний і зрозумілий інтерфейс користувача для бази даних [2].

Подальший розвиток СУБД пов'язаний зі стандартизацією в області програмного забезпечення на основі структурної мови запитів SQL (Structured Query Language). Така мова стандартизована, тому її можна використовувати щоб створювати, змінювати, шукати та передавати інформацію не залежно від того, чи робота проходить на персональному комп’ютері, робочий станції чи ін. Незалежність від специфіки комп’ютерних технологій, а також його підтримка лідерами промисловості в області технологій реляційних баз даних, зробило SQL основною стандартною мовою запитів [3].

ПРОЕКТУВАННЯ БАЗИ ДИНИХ

Аналіз предметної області

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

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

Отже, центральний ринок, як організація, поділяється на структурні підрозділи – відділи. Кожен відділ виконує своє чітке завдання. Наприклад, до відділу «Склад» відносяться працівники, які надають послугу по збереженню товару орендаторів, обладнання яке використовується відділом, а також обладнання, яке може здаватися в арену.

Орендатор використовує місце на ринковій площі та комірку на складі у відповідності з договором, що укладається між ним на ринком, в особі директора.

Таким чином визначимо сутності для побудови бази даних:

– Відділ;

– Працівник;

– Обладнання;

– Орендоване обладнання;

– Місця.

Кожен відділ має свого керівника – завідуючого та персонал, що користується обладнанням у межах відділу.

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

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

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

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

Також орендатору центральний ринок надає місце на складі. Місця на складі називаються комірками.

Центральний ринок нараховує плату за послуги оренди відповідно до витрат, які він поніс на організацію цих послуг.

Розробка ER-моделі

В реляційних базах даних можуть використовуватися різні моделі даних:

– «сутність зв’язок» – ER- модель;

– «відношення – властивість» – PR- модель;

У проектування бази даних будемо використовувати ER-модель. Для неї є характерні поняттях [2]:

– Сутність – деяке абстрактне поняття про реально існуючий об’єкт;

– Атрибут – поіменована характеристика сутності, яка може приймати значення з деякої множини;

– Зв'язок – представлення відношення між сутностями;

Отже, визначимо властиві атрибути для кожної сутності.

Відділ: назва відділу, завідуючий, номер телефону відділу, кількість працівників.

Працівник: П.І.П., ідентифікаційний номер працівника, паспорт працівника, дата народження, адреса, стаж, ставка, посада, сімейний стан, відпускні, відділ.

Обладнання: інвентарний номер обладнання, назва, кінець експлуатації, відповідальний за стан, назва відділу.

Орендоване обладнання: інвентарний номер орендованого обладнання, назва, кінець експлуатації, дата повернення, орендатор.

Орендатор: П.І.П., договір, паспорт, торговий патент.

Місця: місце, сектор, склад, комірка, вид товару, договір, орендатор, будова, плата за місце, плата за стан, водопостачання та електрифікація, кількість товару на складі.

Сутності властиві центральному ринку були визначені в попередньому розділі. Далі визначимо зв’язки між відповідними сутностями [3]. Зв’язки можуть бути таких типів:

– Один-до-одного;

– Один-до-багатьох;

– Багато-до-одного;

– Багато-до-багатьох.

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

Сутність Орендатор пов’язана з трьома сутностями: Орендоване обладнання, Місце, Склад. Так як орендатор може використовувати більше одного місця на торговій площі, комірки складу і одиниць обладнання, тому відношення між всіма парами сутностей буде один-до багатьох.

Відповідно до визначених сутностей та зв’язків між ними на рисунку 1 сформована ER-модель.

Працівники

працює

надає

Орендоване обладнання

Відділ

використовує

Обладнання

N 1 1 N

орендує

Орендатор

орендує

Місця

N 1 1

Рисунок 1 – ER-модель

Таким чином, визначено структуру ER- моделі, яка забезпечуватиме роботу бази даних.

Нормалізація

На основі визначеної ER-моделі та сформованих атрибутів сутностей можна розробляти базу даних. Але для реляційну базу даних потрібно привести до нормальної форми – набору вимог, якому база даних повинна відповідати.

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

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

Існують шість нормальних форм. Розглянемо тільки три з них, що використовуються найчастіше.

Таблиця знаходиться в першій нормальній формі (1НФ), якщо кожен її атрибут атомарний. Під атомарним атрибутом розуміється, що атрибут може містити лише одне значення. Таким чином, не існує 1НФ таблиці, в полях яких можуть зберігатися списки значень. Для приведення таблиці до 1НФ зазвичай потрібно розбити таблицю на декілька окремих таблиць. Тобто само визначення поняття відношення свідомо має на увазі наявність 1НФ.

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

Таблиця знаходиться в третій нормальній формі (3НФ), якщо вона знаходиться в другій нормальній формі 2НФ і при цьому будь-який її неключевий атрибут залежить лише від первинного ключа.

Таким чином, відношення знаходиться в 3НФ тоді і лише тоді, коли воно знаходиться в 3НФ і відсутні транзитивні залежності неключевих атрибутів від ключових. Транзитивною залежністю неключевих атрибутів від ключових називається наступна: A B і B C, де A – набір ключових атрибутів (ключ), B і С – різна безліч неключевих атрибутів.

При вирішенні практичних завдань в більшості випадків друга або третя нормальні форми є достатніми. Процес проектування реляційної бази даних, як правило, закінчується приведенням до цих форм [4].

Проведемо нормалізацію кожного з відношень, атрибути яких визначені вище (див. п. 1.2).

Для відношення Працівник-Відділ визначимо атрибути та приведемо таблицю до НФ1. Ключовим атрибутом визначено атрибут прізвища ім’я та по-батькові працівника (П.І.П.). Неатомарний атрибутом є «паспорт», тому розпишемо його на «№ паспорта», «серія паспорта», «Коли виданий». 1НФ показана на рисунку 2.

П.І.П.

ідентифікаційний номер

№ паспорта

серія паспорта

Коли виданий

Дата народження

Адреса

№ тел.

Ставка

Стаж

Сімейний стан

К-сть дітей

посада

Відпускні

Назва відділу

Завідуючий

№ тел. відділу

Кількість працюючих

Корисна площа

Рисунок 2 – 1НФ для відношення Працівник-Відділ

Приведемо таблицю в НФ2, тобто позбудемося часткових функціональних залежностей.

П.І.П. → інде. номер

П.І.П. → № паспорта

П.І.П. → серія паспорта

П.І.П. → дата народження

П.І.П. → адреса

П.І.П. → № телефону

П.І.П. → ставка

П.І.П. → стаж

П.І.П. → посада

П.І.П. → назва відділу

Назва відділу → завідуючий

Назва відділу → № телефону

Назва відділу → кількість працівників

Отже, 2НФ для відношення Працівник-відділ показана на рисунку 3.

П.І.П

ідент. номер

серія паспорта

дата народження

адреса

№ тел.

ставка

стаж

посада

назва відділу

№ телефону

кількість працівників

Рисунок 3 – 2 НФ для відношення Працівник-Відділ

Після приведення таблиці в 2НФ транзитивних залежностей не виникло, тому приведення до 3НФ не потрібне.

Проведемо нормалізації для відношення Відділ – Обладнання. Атрибути сутностей не мають неатомарності, тому для приведення таблиці в 1 НФ перетворень не потрібно. 1 НФ показано не рисунку 4.

Назва відділу

завідуючий

№ телефону

Інвентарний номер

Назва обладнання

Кінець експлуатації

Відповідальний за стан

Назві відділу

Далі визначено функціональні залежності. 2 НФ показана на рисунку 5.

Назва відділу → завідуючий

Назва відділу → № телефону

Назва відділу → кількість працівників

Інвентарний номер обладнання → назва відділу

Інвентарний номер обладнання → обладнання

Інвентарний номер обладнання → кінець експлуатації

Інвентарний номер обладнання → відповідальний за стан

назва відділу

№ телефону

кількість працівників

назва відділу

інв. ном. обладнання

назва обладнання

кінець експлуатації

відповідальний за стан

Рисунок 4 – 2НФ для відношення Відділ-Обладнання

Як і в попередньому випадку, транзитивних залежностей нема.

Далі розглянемо відношення Відділ – Орендоване обладнання. Атрибути сутності Орендоване обладнання майже такі самі як сутності Обладнання, але доданий ще один атрибут Орендатор. 1НФ показана на рисунку 5.

1НФ.

Назва відділу

завідуючий

№ телефону

к-сть працівників

інвентарний номер

назва обладнання

дата кінця експлуатації

назва відділу

орендатор

Рисунок 5 – 2НФ для відношення Відділ-Орендоване обладнання

Функціональні залежності показані у списку, а 2НФ на рисунку 6.

Назва відділу → завідуючий

Назва відділу → № телефону

Назва відділу → кількість працівників

Інвентарний номер → назва відділу

Інвентарний номер → назва обладнання

Інвентарний номер → дата кінця експлуатації

Інвентарний номер → орендатор

назва відділу

№ телефону

кількість працівників

назва відділу

інв. ном. обладнання

назва обладнання

кінець експлуатації

відповідальний за стан

орендатор

Рисунок 6 – 2НФ для відношення Відділ-Орендоване обладнання

Транзитивних залежностей нема, тому 3НФ не потрібна.

Наступним розглянемо, відповідно до ER- моделі, відношення Орендоване обладнання – Орендатор, 1НФ якого показана на рисунку 7

Інвентарний номер

Назва обладнання

Дата кінця експл.

Назва відділу

Орендатор

Договір

Номер паспорта

Серія паспорта

Торговий патент

паркова

Місце

Сектор

Товар

Будова

Водопост.

Електропост.

Плата за місце

Склад

Комірка

товар

Плата за склад

Рисунок 7 – 1НФ для відношення Орендатор-орендоване обладнання

Розглянемо перетворення до 2НФ для даного відношення. На рисунку 8 показана 2НФ.

Інвентарний номер → назва відділу

Інвентарний номер → назва обладнання

Інвентарний номер → дата кінця експлуатації

Інвентарний номер → орендатор

Орендатор → договір

Орендатор → номер паспорта

Орендатор → торговий патент

Орендатор → місце

Орендатор → сектор

Орендатор → товару

Орендатор → будова

Орендатор → водопостачання

Орендатор → електропостачання

Орендатор → плата за місце

Орендатор → склад

Орендатор → комірка

Орендатор → товар

Орендатор → плата за склад

назва відділу

інв. ном. обладнання

назва обладнання

кінець експлуатації

відповідальний за стан

орендатор

орендатор	договір	номер паспорта	торговий патент	місце	сектор	товар	будова	водопост.	елекроп.
плата за місце	склад	комірка	товар	плата за склад

Рисунок 7 – 2НФ для відношення Орендатор-Місця

Після проведення аналізу таблиці в 2НФ виникли транзитивні залежності пов’язані з атрибутами Місце і Склад. Тому приведемо таблицю в 3НФ.

Інвентарний номер → назва відділу

Інвентарний номер → назва обладнання

Інвентарний номер → дата кінця експлуатації

Інвентарний номер → орендатор

Орендатор → договір

Орендатор → номер паспорта

Орендатор → торговий патент

Договір → місце

Договір → сектор

Договір → товару

Договір → будова

Договір → водопостачання

Договір → електропостачання

Договір → плата за місце

Договір → склад

Договір → комірку

Договір → товар

Договір → плата за склад

3НФ показана на рисунку 8.

назва відділу

інв. ном. обладнання

назва обладнання

кінець експлуатації

відповідальний за стан

орендатор

договір

номер паспорта

торговий патент

договір

місце

сектор

товар

будова

водо пост.

електропост.

плата за місце

договір

склад

комірка

товар

плата за склад

Рисунок 8 – 3НФ для відношення Орендатор-Місця

Таким чином було проведено нормалізацію до 3НФ, що буде достатньо для побудови бази даних.

Реалізація бази даних в СУБД

База даних проектуватиметься в СУБД Microsoft Access 2007, що входить в пакет прикладних офісних програм MS Office 2007.

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

Далі використовуючи інструмент Зв’язки через меню Знаряддя бази даних, та встановлюємо зв’язки між створеними таблицями (рис.2). СУБД автоматично встановлює типи зв’язків, але потрібно прослідкувати за правильністю їх встановлення. Кожен зв'язок потрібно відредагувати для забезпечення цілісності даних. Також потрібно встановити каскадне оновлення полів та каскадне видалення полів.

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