Створення бази даних

Зв'язки:

ВХОДИТЬ до сутності «ДТП».

3. Концептуальне моделювання предметної області

3.1 Теоретичні положення концептуального моделювання

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

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

Властивостями концептуальної моделі є наступні.

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

Вона включає тільки концептуально релевантні аспекти ПО, крім, таким чином, БУДЬ-ЯКИХ аспектів зовнішнього або внутрішнього представлення даних. Це означає, по перше, що концептуальна модель жодним чином не повинна фіксувати конкретні потреби окремих груп користувачів або додатків. Вона повинна фіксувати, що собою представляє ПО в цілому, а не з погляду інтересів або потреб користувачів. Вона повинна інтегрувати думки, погляди й інтереси окремих користувачів, але саме інтегрувати, для одержання цілісної картини, а не виражати їхні конкретні погляди, побажання думки. По-друге, у концептуальній моделі ПО ні яким чином не повинні відбиватися які-небудь аспекти майбутньої реалізації БД у комп'ютерному середовищі. Усе, що пов'язане з такими поняттями, як способи зберігання, методи доступу, ефективність виконання, оптимізація й т.д. перебувають за межами концептуальної моделі.

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

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

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

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

Ключовими результатами етапу концептуального моделювання э наступні:

формальний опис інформаційного забезпечення предметної області.

докладний і строгий опис сховищ даних.

детальний опис потоків даних.

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

детальний опис діючих у предметній області правил і обмежень.

Існує безліч мов, які претендують на роль мов концептуального моделювання ПО. У цей час найбільш популярними й повсюдно використовуваними є мови, що ставляться до класу, так званих, графічних мов, що оперують з такими поняттями, як сутність-атрибут-зв'язок. У наступному розділі ми коротко опишемо одну з таких мов яка отримала назву мови ER-моделювання предметних областей.

3.2 Мова ER - моделювання ПО

Мова ER-моделювання (Entity Relationship Modeling) -- це мова визначення інформаційних потреб організації. Мова базується на концепції, відповідно до якої інформаційне забезпечення будь-якої предметної області представляється як сукупність взаємозалежних об'єктів. Процес моделювання полягає у виділенні сутностей ПО, установлення властивостей виділених сутностей і виявлення існуючих між ними зв'язків.

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

Розглянемо коротко основні властивості, формальні позначення й визначення сутностей, зв'язків, атрибутів.

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

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

ім'я;

ступінь/потужність;

факультативність -- обов'язкова або факультативна.

Ці властивості використовуються для опису асоціації з кожної зі сторін, для завдання зв'язку повинні бути визначені обидва її кінця.

На діаграмах зв'язки представляються лініями, що з'єднують два прямокутники сутностей. Одним з видів зв'язку представлений на малюнку 1. Це зв'язок зі ступенем багато-до-одному, обов'язковий в закінченні зі ступенем "багато", і факультативний на протилежному кінці.

Малюнок 1. Приклад зв'язку

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

При читанні зв'язку з обов'язкової сторони перед її ім'ям використовуються слова "у всіх випадках" або "завжди"; для факультативної сторони використовуються слова "у загальному випадку" або "іноді". Ступінь "багато" читається як "один або декілька", а ступінь "один" -- "один і тільки один".

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

Для подання атрибута пишеться його ім'я малими літерами в однині, можливо, із прикладами значень. Атрибути необов'язково показувати на діаграмі сутностей і зв'язків, однак додавання до сутності одного-двох атрибутів у період формування моделі, як правило, виявляється досить корисним.

Атрибут описує одну сутність. Атрибут повинен описувати ту сутність, до якої він віднесений. У кожний момент часу сутність може володіти лише одним значенням атрибута.

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

Кожна сутність повинна однозначно ідентифікуватися за допомогою деякої комбінації атрибутів і/або зв'язків. Тому серед можливих атрибутів сутності завжди повинні бути знайдені такі атрибути, які дозволяють неї ідентифікувати. Унікальний ідентифікатор представляється на ER-Діаграмі вказівкою символу "#" перед ім'ям кожного атрибута, що входить у даний ідентифікатор. Значення усіх інших атрибутів повинні залежати від усього унікального ідентифікатора.

Дуже важливо чітко розуміти, що всі визначення сутності, зв'язку, атрибута й унікального ідентифікатора, які ми тільки що розглянули, суть визначення типу, або класу, поняття, а не екземпляра. Екземпляри сутностей і зв'язків будуть представлені в самій базі даних.



3.3 Побудова концептуальної моделі обліку автомобілів в ДАІ

На основі проведеного аналізу предметної області була побудована концептуальна модель з використанням мови ER-моделювання. Концептуальна модель наведена на малюнку 2. Дамо декілька зауважень:

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

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

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



Малюнок 2. Концептуальна ER-модель обліку автомобілів в ДАІ.



4. Логічне та фізичне проектування бази даних

4.1 Логічне проектування

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

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

Крок 1. Перетворення сутностей у таблиці. Кожна сутність перетворюється у таблицю. Ім'я сутності представляється у вигляді семантично осмисленого імені у латинському алфавіті.

Крок 2. Перетворення атрибутів у стовпці. Кожний атрибут перетвориться в стовпець. Ім'я атрибуту представляється у вигляді семантично осмисленого імені у латинському алфавіті. У цей момент уточнюється формат представлення значень стовпця. Факультативні атрибути стають NULL-стовпцями. Обов'язкові атрибути стають NOT NULL-стовпцями.

Крок 3. Подання унікальних ідентифікаторів ключами таблиць. Складові унікального ідентифікатора сутності стають первинним ключем таблиці. Нагадаємо, що сутність може мати більш ніж один унікальний ідентифікатор Тому вибирається той, котрий використовується найбільше часто. Всі інші унікальні ідентифікатори приймають обмеження цілісності UNIQUE NOT та NOT NULL.

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

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

Крок 5. Введення спеціальних первинних ключів. Для більш адекватного відображення логічного проекту бази даних у фізичний, вводимо у всі таблиці один спеціальний стовпець з обмеженням цілісності первинного ключа. Всі ті стовпці, які мають властивість первинного ключа згідно з концептуальною моделлю, набувають обмеження цілісності UNIQUE та NOTNULL.

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

Таблиця 1 - Опис зв'язків сутності "Автомобіль" - (car)

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
id	ціле число	11	Унікальний ID	Первинний ключ
manufacturer_id	ціле число	11	Зв'язок з виробником	Зовнішній ключ, що посилається на первинний ключ
manufacturer_serial	строка	175	Серійний номер, виданий виробником	Обов'язковий
fabrication_date	дата		Дата виробництва	Обов'язковий
color	строка	175	Колір	Обов'язковий
engine_capacity	число з крапкою		Об'єм двигуна	Обов'язковий

Таблиця 2 - Опис зв'язків сутності "Техогляд" - (car_checkup)

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
id	ціле число	11	Унікальний ID	Первинний ключ
car_id	ціле число	11	Зв'язок з автомобілем	Зовнішній ключ, що посилається на первинний ключ
checkup_station_id	ціле число	11	Зв'язок зі станцією тех. обслуговування	Зовнішній ключ, що посилається на первинний ключ
conclushion	строка	175	Висновок огляду	Обов'язковий
since	date		Дата видачі талону тех. огляду	Обов'язковий
till	date		Дата закінчення дії талону тех. огляду	Обов'язковий

Таблиця 3 - Опис зв'язків сутності "Виробник автомобілів" - (car_manufacturer)

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
id	ціле число	11	Унікальний ID	Первинний ключ
name	строка	175	Назва виробника	Обов'язковий
address	адреса	175	Адреса виробника	Обов'язковий
contact_phone	ціле число	10	Контактний номер телефону виробника	Обов'язковий

Таблиця 4 - Опис зв'язків сутності "Автовласник" - (car_owner)

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
id	ціле число	11	Унікальний ID	Первинний ключ
citizen_id	ціле число	11	Зв'язок з громадянином	Зовнішній ключ, що посилається на первинний ключ.
car_id	ціле число	11	Зв'язок з автомобілем	Зовнішній ключ, що посилається на первинний ключ.

Таблиця 5 - Опис зв'язків сутності "Реєстраційний номер" - (car_registration)

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
id	ціле число	11	Унікальний ID	Первинний ключ
reg_date	дата		Дата видачі реєстраційного номеру	Обов'язковий
car_owner	ціле число	11	Зв'язок з автовласником	Зовнішній ключ, що посилається на первинний ключ
reg_location	строка	2	Код області, де видано номер	Обов'язковий
reg_number	ціле число	4	Реєстраційний номер	Обов'язковий
reg_series	строка	2	Серія номеру	Обов'язковий

Таблиця 6 - Опис зв'язків сутності "СТО" - (checkup_station)

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
id	ціле число	11	Унікальний ID	Первинний ключ
title	строка	200	Назва СТО	Обов'язковий
address	строка	200	Адреса	Обов'язковий
manager_f_name	строка	125	Имя менеджеру	Обов'язковий
manager_l_name	строка	125	Прізвище менеджеру	Обов'язковий
manager_phone	ціле число	10	Контактний телефон менеджеру	Обов'язковий

Таблиця 7 - Опис зв'язків сутності "Громадянин" - (citizen)

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
id	ціле число	11	Унікальний ID	Первинний ключ
first_name	строка	125	Ім'я	Обов'язковий
last_name	строка	125	Прізвище	Обов'язковий
birth_date	datetime		Дата народження	Обов'язковий
sex	цілечисло		Код статі (1 - чоловіча, 2 - жіноча)	Обов'язковий
actual_address	строка	175	Адреса проживання	Обов'язковий
juridical_address	строка	175	Адреса прописки	Обов'язковий
registration_date	дата		Дата реєстрації	Обов'язковий
passport_series	строка	2	Серія паспорту	Обов'язковий
passport_code	цілечисло	9	Номер паспорту	Обов'язковий

Таблиця 8 - Опис зв'язків сутності "Водійське посвідчення" - (driving_license)

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
id	ціле число	11	Унікальний ID	Первинний ключ
citizen_id	ціле число	11	Зв'язок з громадянином	Зовнішній ключ, що посилається на первинний ключ.
driving_school_id	ціле число	11	Зв'язок з автошколою	Зовнішній ключ, що посилається на первинний ключ.
licensing_date	Дата		Дата видачі посвідчення	Обов'язковий

Таблиця 9 - Опис зв'язків сутності "Автошкола" - (driving_school)

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
id	ціле число	11	Унікальний ID	Первинний ключ
title	строка	200	Назва	Обов'язковий
address	строка	200	Адреса	Обов'язковий
manager_f_name	строка	125	Ім'я менеджеру	Обов'язковий
manager_l_name	строка	125	Прізвище менеджеру	Обов'язковий
manager_phone	ціле число	10	Контактний телефон менеджеру	Обов'язковий

Таблиця 10 - Опис зв'язків сутності "ДТП" - (exident)

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
id	ціле число	11	Унікальний ID	Первинний ключ
reporter	ціле число	11	Зв'язок з громадянином	Зовнішній ключ, що посилається на первинний ключ.
location	строка	250	Місце пригоди	Обов'язковий
exident_date	дата		Дата пригоди	Обов'язковий
officer	строка	11	Зв'язок з громадянином	Зовнішній ключ, що посилається на первинний ключ.
type	ціле число	4	Код типу пригоди	Обов'язковий

Таблиця 11 - Опис зв'язків сутності "Учасник ДТП" - (exident_participant)

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
id	ціле число	11	Унікальний ID	Первинний ключ.
exident_id	ціле число	11	Зв'язок з ДТП	Зовнішній ключ, що посилається на первинний ключ.
role	ціле число	4	Код ролі (1 - постраждалий; 2- ініціатор; 3 - свідок;)	Обов'язковий
citizen_id	ціле число	11	Зв'язок з громадянином	Зовнішній ключ, що посилається на первинний ключ.
license_id	ціле число	11	Зв'язок з водійським посвідченням (за наявності; не обов'язкове поле)	Зовнішній ключ, що посилається на первинний ключ.

Таблиця 12 - Опис зв'язків сутності "Страховий поліс" - (insurance)

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
id	ціле число	11	Унікальний ID	Первинний ключ.
issued	дата		Дата видачі полісу	Обов'язковий
valid_untill	дата		Дата закінчення терміну дії зобов'язань(полісу)	Обов'язковий
car_registration	ціле число	11	Зв'язок з Реєстраційним номером	Зовнішній ключ, що посилається на первинний ключ.
insurance_company_id	ціле число	11	Зв'язок з Страховою компанією	Зовнішній ключ, що посилається на первинний ключ.

Таблиця 13 - Опис зв'язків сутності "Страхова компанія" - (insurance_company)

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
id	ціле число	10	Унікальний ID	Первинний ключ.
name	строка	200	Назва	Обов'язковий
address	строка	200	Адреса	Обов'язковий
manager_f_name	строка	125	Ім'я контактної особи	Обов'язковий
manager_l_name	строка	125	Прізвище контактної особи	Обов'язковий
contact_phone	ціле число	10	Контактний телефон	Обов'язковий

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

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

Наведемо скрипти мови SQL, що використовувалися в середовищі СКБД MySQL, для створення відповідних таблиць даних:

CREATE TABLE `car` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`manufacturer_id` int(11) NOT NULL,

`manufacturer_serial` varchar(175) NOT NULL,

`fabrication_date` date NOT NULL,

`color` varchar(175) NOT NULL,

`engine_capacity` float NOT NULL,

PRIMARY KEY (`id`),

KEY `manufacturer_id` (`manufacturer_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `car_checkup` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`car_id` int(11) unsigned NOT NULL,

`checkup_station_id` int(11) unsigned NOT NULL,

`conclushion` varchar(175) NOT NULL,

`since` date NOT NULL,

`till` date NOT NULL,

PRIMARY KEY (`id`),

KEY `car_id` (`car_id`),

KEY `checkup_station_id` (`checkup_station_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `car_manufacturer` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(175) NOT NULL,

`address` varchar(175) NOT NULL,

`contact_phone` int(10) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `car_owner` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`citizen_id` int(11) unsigned NOT NULL,

`car_id` int(11) unsigned NOT NULL,

PRIMARY KEY (`id`),

KEY `citizen_id` (`citizen_id`),

KEY `car_id` (`car_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `car_registration` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`reg_date` date NOT NULL,

`car_owner` int(11) unsigned NOT NULL,

`reg_location` varchar(2) NOT NULL,

`reg_number` int(4) NOT NULL,

`reg_series` varchar(2) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `checkup_station` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`title` varchar(200) NOT NULL,

`address` varchar(200) NOT NULL,

`manager_f_name` varchar(125) NOT NULL,

`manager_l_name` varchar(125) NOT NULL,

`manager_phone` int(10) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `citizen` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`first_name` varchar(125) NOT NULL,

`last_name` varchar(125) NOT NULL,

`birth_date` datetime NOT NULL,

`sex` tinyint(3) unsigned NOT NULL,

`actual_address` varchar(175) NOT NULL,

`juridical_address` varchar(175) NOT NULL,

`registration_date` datetime NOT NULL,

`passport_series` varchar(2) NOT NULL,

`passport_code` int(9) unsigned NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `driving_license` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`citizen_id` int(11) unsigned NOT NULL,

`driving_school_id` int(11) unsigned NOT NULL,

`licensing_date` date NOT NULL,

PRIMARY KEY (`id`),

KEY `citizen_id` (`citizen_id`),

KEY `driving_school_id` (`driving_school_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `driving_school` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`title` varchar(200) NOT NULL,

`address` varchar(200) NOT NULL,

`manager_f_name` varchar(125) NOT NULL,

`manager_l_name` varchar(125) NOT NULL,

`manager_phone` int(10) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `exident` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`reporter` int(11) NOT NULL,

`location` int(250) NOT NULL,

`exident_date` datetime NOT NULL,

`officer` varchar(200) NOT NULL,

`type` tinyint(4) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `exident_participant` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`exident_id` int(11) unsigned NOT NULL,

`role` tinyint(4) NOT NULL,

`citizen_id` int(11) unsigned NOT NULL,

`license_id` int(11) unsigned DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `insurance` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`issued` date NOT NULL,

`valid_untill` date NOT NULL,

`car_registration` int(11) unsigned NOT NULL,

`insurance_company_id` int(11) unsigned NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `insurance_company` (

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

`name` varchar(200) NOT NULL,

`address` varchar(200) NOT NULL,

`manager_f_name` varchar(125) NOT NULL,

`manager_l_name` varchar(125) NOT NULL,

`contact_phone` int(10) unsigned NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `car`

ADD CONSTRAINT `car_ibfk_1` FOREIGN KEY (`manufacturer_id`) REFERENCES `car_manufacturer` (`id`);

ALTER TABLE `car_checkup`

ADD CONSTRAINT `car_checkup_ibfk_1` FOREIGN KEY (`car_id`) REFERENCES `car` (`id`),

ADD CONSTRAINT `car_checkup_ibfk_2` FOREIGN KEY (`checkup_station_id`) REFERENCES `checkup_station` (`id`);

ALTER TABLE `car_owner`

ADD CONSTRAINT `car_owner_ibfk_2` FOREIGN KEY (`car_id`) REFERENCES `car` (`id`),

ADD CONSTRAINT `car_owner_ibfk_1` FOREIGN KEY (`citizen_id`) REFERENCES `citizen` (`id`);

ALTER TABLE `driving_license`

ADD CONSTRAINT `driving_license_ibfk_2` FOREIGN KEY (`driving_school_id`) REFERENCES `driving_school` (`id`),

ADD CONSTRAINT `driving_license_ibfk_1` FOREIGN KEY (`citizen_id`) REFERENCES `citizen` (`id`);

4.2.2 Інформаційно-пошукові запити

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

Запит 1. Вилучення інформації про володіння автомобілями та їх страховими полісами за серією та номером паспорту.

SELECT `car`.*,`insurance`.* FROM `citizen`

LEFT JOIN `car_owner`

ON (`citizen`.`id` = `car_owner`.`citizen_id`)

LEFT JOIN `car_registration`

ON(`car_owner`.`id` = `car_registration`.`car_owner`)

LEFT JOIN `insurance`

ON (`car_registration`.`id` = `insurance`.`car_registration`)

LEFT JOIN `car`

ON(`car_owner`.`car_id` = `car`.`id`)

WHERE `citizen`.`passport_series` = XAND `citizen`.`passport_code` = Y

Де - X - серія паспорту,

Y - номер паспорту.

Запит 2. Кількість ДТП за періодом років народження водіїв.

SELECT

COUNT(DISTINCT `exident_id`)

FROM `exident_participant`

LEFT JOIN `driving_license` ON (`exident_paticipant`.`license_id` = `driving_license`.`id`)

LEFT JOIN `citizen` ON (`driving_license`.`citizen_id` = `citizen_id`.`id`) WHERE `citizen`.`bith_date` >=YAND `citizen`.`bith_date` <=X

Де - X - кінцевий рік народження водія,

Y - початковий рік народження водія.



Висновки

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

Ціллю курсової роботи було проектування бази даних обліку автомобілів в ДАІ.

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

Після цього був проведений аналіз ПО в результаті якого був отриманий змістовний опис ПО. Для аналізу ПО використовувалися наявні документи, а саме: прикази та розпорядження, що були видані в Міністерстві ВС України.

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

Логічне та фізичне проектування БД складалося з конвертації концептуальної моделі ПО у реляційну модель даних. При цьому був використаний алгоритм конвертування схеми ПО у мові ER в схему реляційної бази даних. Після цього реляційна база даних була представлена у вигляді команд створення таблиць бази даних у мові SQL для СКБД MySQL. Крім того, у мові SQL описані деякі інформаційно-пошукові запити.

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

Размещено на Allbest.ru

...