Розробка бази даних обліку автомобільного транспорту у МРЕВ ДАІ

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Вступ

Метою створення курсової роботи є створення бази даних обліку автомобільного транспорту у МРЕВ ДАІ. В базі даних обліку автомобілів використовується ієрархічні сутності, які в повному обсязі описують поставлену задачу, а саме такі сутності як автомобілі, власники, водії, співробітники ДАІ і штрафи.

Головною ціллю курсової роботі є проектування бази даних реєстрації та ведення обліку автомобілів в ДАІ на прикладі київського МРЕВ ДАІ. Для про проектування бази даних в курсовій описані наступні фази проектування:

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

2) Системний аналіз предметної області;

3) Моделювання предметної області;

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

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

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

1.1 Мета та цілі створення бази даних

Головна мета створення бази даних полягає у структуруванні та зберіганні інформації обліку автомобільного транспорту в ДАІ.

Розробка бази даних передбачає можливість взаємодії БД з системою управління, яка використовується в відділах дорожньої авто інспекції міста Києва та інших міст країни. За допомогою цієї системи працівники МРЕВ ДАІ мають доступ до перегляду та можливість маніпулювати даними обліку автомобільних засобів.

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

Цілі створення бази даних:

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

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

- Прискорення доступу до інформації. Дає змогу працівнику ДАІ отримати доступ до інформації не виходячи з кабінету;

- Звільнення від паперової документації, яка потребує більше фізичного місця для збереження і затрудняє доступ до неї;

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

На основі встановлених цілей, створення бази даних, система повинна виконувати наступні задачі:

- Автоматизація обліку окремого автомобільного засобу;

- Ведення повної інформації стосовно автомобільного засобу;

- Швидке надання доступу до інформації автомобільного засобу;

- Можливість маніпулювання інформацією;

- Безпека доступу до інформації.

1.2 Вимоги до інформаційного забезпечення

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

- Кожен працівник повинен мати доступ до бази даних обмежений його посадовими обов'язками;

- Система повинна мати можливість повного дублювання бази даних;

- Система повинна дублювати всю інформацію, яка змінюється. Це робіться для того, якщо при зміні даних система дасть збій і дані в БД пошкодяться, то система могла відновити дані;

- Жорстке контролювання запитів до бази даних із зовнішніх пристроїв (пристрої, які знаходяться за межами офісу МРЕВ ДАІ).

2. Системний аналіз предметної області

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

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

Бізнес-правила - це правила й обмеження, що діють у ПО відносно основних понять інформаційної структури (сутностей, атрибутів і зв'язків). Виділяються бізнес правила, що мають відносини до атрибутів однієї сутності (унікальність атрибутів, ідентифікація сутності, спеціальні правила, наприклад, тривалість практики вказується в годинниках і не повинна перевищувати 500 годин), до зв'язків між сутностями (факультативність закінчення зв'язку, потужність закінчень зв'язку (1:1, 1:n, m:n), ступінь зв'язку, наприклад, на факультеті повинне бути не більше 10 кафедр).

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

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

У результаті аналізу ПО були визначені наступні сутності, їх атрибути та зв'язки:

Сутність АВТОМОБІЛЬ

Короткий опис сутності. Транспортний засіб (автомобіль), якого ведеться облік в системі.

Атрибути. Сутність характеризується наступними атрибутами:

· Тип транспортного засобу;

· Номера транспорту;

· Регіон, в якому зареєстроване авто;

· Дата реєстрації авто;

· Власник транспортного засобу;

· Заводський номер двигуна;

· Заводський номер кузова;

· Колір автомобіля.

Зв'язки. Сутність АВТОМОБІЛЬ має наступні зв'язки з іншими сутностями:

· АВТОМОБІЛЬ обов'язково має лише одному НОМЕРУ;

· АВТОМОБІЛЬ обов'язково має лише одному ТИПУ АВТОМОБІЛЯ;

· АВТОМОБІЛЬ обов'язково має лише одного власника ВОДІЯ

· АВТОМОБІЛЬ може відповідати лише одному ТЕХНІЧНИЙ ПАСПОРТ;

· АВТОМОБІЛЬ може відповідати одному чи більше водіїв, які мають право на КУРЕВАННЯ;

· АВТОМОБІЛЬ може відповідати лише за одним видом РОЗШУКУ;

· АВТОМОБІЛЬ може відповідати арештованим лише на одне місце ШТРАФ-МАЙДАНЧИКУ;

· АВТОМОБІЛЬ може відповідати одному чи більше ШТРАФІВ;

· АВТОМОБІЛЬ може відповідати тільки одній СТРАХОВЦІ.

Бізнес-правила. Автомобіль обов'язково повинен мати лише один тип, ключ якого записується в атрибут типу. Також автомобіль повинен мати автомобільний номер, але цей атрибут не обов'язковий так як автомобіль може стояти на обліку в ДАІ і не мати номерів, атрибут приймає первинний ключ з сутності «Номера». Автомобіль повинен обов'язково мати власника, атрибут приймає ключ з сутності «Водії». Кожен автомобіль обов'язково повинен мати первинний ключ, який є унікальним і дає змогу ідентифікувати автомобіль в інший сутностях бази даних.

Сутність ВОДІЙ

Короткий опис сутності. Повна інформація про всіх водіїв, які мають право керувати будь-яким видом транспорту.

Атрибути. Сутність характеризується наступними атрибутами:

· Прізвище водія;

· Ім'я водія;

· По-батькові водія;

· Пол;

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

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

· Прописка;

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

· Дата отримання прав на керування автотранспортом;

· Номер прав.

Зв'язки. Сутність ВОДІЙ має наступні зв'язки з іншими сутностями:

· ВОДІЙ може бути власником одного чи більше АВТОМОБІЛІВ;

· ВОДІЙ може мати один чи більше ШТРАФІВ;

· ВОДІЙ може мати один чи більше дозволів на КЕРУВАННЯ.

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

Сутність ТИП АВТОМОБІЛЯ

Короткий опис сутності. Містить інформацію про характеристики всіх видів транспортних засобів.

Атрибути. Сутність характеризується наступними атрибутами:

· Тип кузова;

· Тип двигуна;

· Вага автомобільного засобу;

· Вантажопідйомність;

· Марка палива.

Зв'язки. Сутність ТИП АВТОМОБІЛЯ має наступні зв'язки з іншими сутностями:

· ТИП АВТОМОБІЛЯ може відповідати одному чи більше АВТОМОБІЛЯМ.

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

Сутність ТЕХНІЧНИЙ ПАСПОРТ

Короткий опис сутності. Містить інформацію про всі технічні паспорти всіх автомобільних засобів.

Атрибути. Сутність характеризується наступними атрибутами:

· Автомобільний засіб, до якого відноситься паспорт;

· Співробітник ГАІ, який підтвердив проходження технічного огляду авто;

· Дата видачі;

· Термін придатності.

Зв'язки. Сутність ТЕХНІЧНИЙ ПАСПОРТ має наступні зв'язки з іншими сутностями:

· ТЕХНІЧНИЙ ПАСПОРТ обов'язково має лише один АВТОМОБІЛЬ;

· ТЕХНІЧНИЙ ПАСПОРТ обов'язково має лише одного СПІВРОБІТНИКА.

Бізнес-правила. Технічний паспорт не має первинного ключа. Атрибут «автомобільний засіб» приймає первинний ключ з сутності «Автомобіль». Атрибут «Співробітник ГАІ» приймає первинний ключ з сутності «Співробітник». Атрибути «Дата видачі» і «Термін придатності» мають тип даних дати. Всі атрибути обов'язкові.

Сутність КЕРУВАННЯ

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

Атрибути. Сутність характеризується наступними атрибутами:

· Автомобіль, на який видається право керування;

· Водій, якому видається право керування на авто.

Зв'язки. Сутність КЕРУВАННЯ має наступні зв'язки з іншими сутностями:

· КЕРУВАННЯ обов'язково має один чи більше АВТОМОБІЛІВ;

· КЕРУВАННЯ обов'язково має одного чи декілька ВОДІЇ.

Бізнес-правила. Сутність «Керування» не має первинного ключа, так як вона не посилається на жодну з інших сутностей і використовується як проміжна сутність для визначення зв'язків багато до багатьох с сутностей «Автомобіль» і «Водій». Атрибут «Автомобіль» обов'язково приймає первинний ключ з сутності «Автомобіль». Атрибут «Водій» обов'язково приймає первинний ключ з сутності «Водій». Всі атрибути є обов'язковими і не можуть мати пусті або невизначені значення.

Сутність ШТРАФ

Короткий опис сутності. Містить інформацію про всі штрафи всіх автомобільних транспортних засобів та водіїв.

Атрибути. Сутність характеризується наступними атрибутами:

· Автомобіль, який отримав штраф;

· Водій, який отримав штраф;

· Співробітник, який виписав штраф;

· Вид порушення;

· Дата порушення;

· Вид покарання.

Зв'язки. Сутність ШТРАФ має наступні зв'язки з іншими сутностями:

· ШТРАФ може відповідати тільки одному арешту і відправлення на ШТРАФ-МАЙДАНЧИК;

· ШТРАФ обов'язково має тільки один АВТОМОБІЛЬ;

· ШТРАФ обов'язково має тільки одного ВОДІЯ;

· ШТРАФ обов'язково має тільки одного СПІВРОБІТНИКА;

· ШТРАФ обов'язково має тільки одну СТАТТЮ.

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

Сутність РОЗШУК

Короткий опис сутності. Містить інформацію про всі автомобілі, які знаходяться в розшуку.

Атрибути. Сутність характеризується наступними атрибутами:

· Автомобіль, який розшукується;

· Дата зникнення;

· Тип розшуку.

Зв'язки. Сутність РОЗШУК має наступні зв'язки з іншими сутностями:

· РОЗШУК обов'язково має лише один АВТОМОБІЛЬ.

Бізнес-правила. Сутність не має первинного ключа, так як виступає в ролі інформаційної сутності. Атрибут «Автомобіль» приймає первинний ключ з сутності «Автомобіль». Атрибут «Дата зникнення» повинен мати тип даних дата. Атрибут «Тип розшуку» повинен мати символьний тип даних, так як він приймає значення, які описують причину розшуку автомобіля. Всі атрибути обов'язкові.

Сутність ШТРАФ-МАЙДАНЧИК

Короткий опис сутності. Містить інформацію про всі автомобілі, які знаходяться штраф-майданчику.

Атрибути. Сутність характеризується наступними атрибутами:

· Автомобіль, який був арештований;

· Номер місця на майданчику, на яке було розміщено авто;

· Дата арешту;

· Причина арештую.

Зв'язки. Сутність ШТРАФ-МАЙДАНЧИК має наступні зв'язки з іншими сутностями:

· ШТРАФ-МАЙДАНЧИК обов'язково має лише один АВТОМОБІЛЬ

· ШТРАФ-МАЙДАНЧИК обов'язково має лише один ШТРАФ

Бізнес-правила. Сутність не має первинного ключа. Атрибут «Автомобіль» приймає первинний ключ з сутності «Автомобіль». Атрибут «Причина арешту» приймає первинний ключ з сутності «Штраф». Атрибут «Номер місця» повинен бути символьного типу. Атрибут «Дата арешту» повинен приймати дані типу дата. Всі атрибути обов'язкові.

Сутність СПІВРОБІТНИК

Короткий опис сутності. Містить інформацію про всіх співробітників ГАІ.

Атрибути. Сутність характеризується наступними атрибутами:

· Прізвище співробітника;

· Ім'я співробітника;

· По-батькові співробітника;

· Посада;

· Звання;

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

· Батальйон.

Зв'язки. Сутність СПІВРОБІТНИК має наступні зв'язки з іншими сутностями:

· СПІВРОБІТНИК може відповідати одному чи більше ТЕХНІЧНИМ ПАСПОРТАМ;

· СПІВРОБІТНИК може відповідати одному чи більше ШТРАФАМ.

Бізнес-правила. Сутність обов'язково має первинний ключ, який є унікальним. Наступні атрибути мають символьний тип даних: «Прізвище», «Ім'я», «По-батькові», «Посада». Атрибут «Звання» має вибірковий тип даних в яких записані всі військові звання. Атрибут «Дата народження» приймає тип даних дата. Атрибут «Батальйон» приймає символьний тип і записує в себе номер батальйону, в якому несе службу співробітник. Всі атрибути обов'язкові.

Сутність АВТОМОБІЛЬНИЙ НОМЕР

Короткий опис сутності. Містить інформацію про всіх всі номера всіх автомобілів.

Атрибути. Сутність характеризується наступними атрибутами:

· Вид номера (транзитні або постійні);

· Номер;

· Дата реєстрації.

Зв'язки. Сутність АВТОМОБІЛЬНИЙ НОМЕР має наступні зв'язки з іншими сутностями:

· АВТОМОБІЛЬНИЙ НОМЕР обов'язково відповідає лише одному АВТОМОБІЛЮ

Бізнес-правила. Сутність обов'язково має первинний ключ, який є унікальним. Атрибут «Вид номера» має вибірковий тип даних (один з двох варіантів: «Транзитні» та «Постійні»). Атрибут «Номер» є символьним типом не більше 8 символів. Атрибут «Дата реєстрації» приймає дані типу дата. Всі атрибути обов'язкові.

Сутність СТАТТЯ

Короткий опис сутності. Містить інформацію про всіх всі можливі статті порушення правил дорожнього руху.

Атрибути. Сутність характеризується наступними атрибутами:

· Номер статті;

· Опис порушення;

· Тип покарання.

Зв'язки. Сутність СТАТТЯ має наступні зв'язки з іншими сутностями:

· СТАТТЯ обов'язково відповідає одному чи більше ШТРАФІВ.

Бізнес-правила. Сутність обов'язково має первинний ключ, який є унікальним. Всі атрибути в сутності мають символьний тип даних і є обов'язковими.

Сутність СТРАХОВКА

Короткий опис сутності. Містить інформацію про всі страхування всіх автомобільних засобів.

Атрибути. Сутність характеризується наступними атрибутами:

· Автомобіль, який був застрахований;

· Назва страхової фірми;

· Тип страхування;

· Сума страхування;

· Термін придатності.

Зв'язки. Сутність СТРАХОВКА має наступні зв'язки з іншими сутностями:

· СТРАХОВКА обов'язково включає лише один АВТОМОБІЛЬ.

Бізнес-правила. Сутність не має первинного ключа, так як використовується в інформаційних цілях. Атрибут «Автомобіль» приймає первинний ключ з сутності «Автомобіль». Атрибут «Назва страхової компанії» приймає дані символьного типу і не перевищують 100 символів. Атрибут «Тип страхування» має вибірковий тип даних. Атрибут «Сума страхування» приймає числове значення даних з плаваючою точкою, який зберігає в собі суму страховки в гривнях. Атрибут «Термін придатності» приймає значення типу дата. Всі атрибути обов'язкові.

інформаційний база дані облік

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

Завдання цього етапу полягає у проведенні логічного та фізичного проектування бази даних.

Логічне проектування -- це розробка логічної структури системи баз даних без прив'язки до конкретної СУБД, структур збереження, методам доступу і т.д..

Фізичне проектування - це проект системи бази даних для конкретної СУБД. Під час виконання даного етапу модель сутностей і зв'язків перетворюється в схему бази даних і специфікації позамашинного збереження.

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

Рисунок 1. Концептуальна ER-модель обліку автомобілів в ДАІ

Таблиця 1. Відношення сутності АВТОМОБІЛЬ

CAR

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
CAR_PK	ціле число	10	Унікальний ID	Первинний ключ. Обов'язковий
TYPE_FK	ціле число	10	Зв'язок з типом автомобіля	Зовнішній ключ, що посилається на первинний ключ відношення TYPE. Обов'язковий
NUMBER_FK	ціле число	10	Зв'язок з номером автомобіля	Зовнішній ключ, що посилається на первинний ключ відношення NUMBER. Обов'язковий
DRIVER_FK	ціле число	10	Зв'язок з власником	Зовнішній ключ, що посилається на первинний ключ відношення DRIVER. Обов'язковий
COUNTRY	строка	100	Регіон в якому зареєстрований транспортній засіб	Обов'язковий
DATE	дата		Дата реєстрації авто	Обов'язковий
NUM_ENGINE	строка	20	Номер двигуна	Обов'язковий
NUM_BODY	строка	20	Номер кузова	Обов'язковий
COLOR	строка	50	Колір транспортного засобу	Обов'язковий

Таблиця 2. Відношення сутності ВОДІЙ

DRIVER

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
DRIVER_PK	ціле число	10	Унікальний ID	Первинний ключ. Обов'язковий
F_NAME	строка	100	Прізвище водія	Обов'язковий
S_NAME	строка	100	Ім'я водія	Обов'язковий
T_NAME	строка	100	По-батькові водія	Обов'язковий
SEX	строка	1	Вказує пол водія	Може приймати одне із значень: «f» або «m». Обов'язковий
BIRTH	дата		Дата народження водія	Обов'язковий
NUM_PASSPORT	строка	10	Номер паспорта водія	Обов'язковий
REGISTRATION	строка	100	Прописка водія	Обов'язковий
NUM_IDENTIFICATION	ціле число	10	Ідентифікаційний номер водія	Обов'язковий
DATE	дата		Дата отримання посвідчення водія	Обов'язковий
NUMBER	строка	10	Номер посвідчення водія	Обов'язковий

Таблиця 3. Відношення сутності ТИП АВТОМОБІЛЯ

TYPE

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
TYPE_PK	ціле число	10	Унікальний ID	Первинний ключ. Обов'язковий
BODY	строка	20	Тип кузова	Може приймати одне із значень: «Седан», «Уніврсал», «Хетчбет», «Купе», «Лімузин», «Мінівен», «Таун-кар», «Комби», «Лифтбек», «Кабріолет», «Родстер», «Фаетрон», «Ландо», «Брогам», «Тарга», «Спайдер», «Пікап» або «Фургон». Обов'язковий
ENGINE	строка	20	Тип двигуна	Може приймати одне із значень: «Двохтактні», «Чотирьотактні», «Двохциліндрові», «Чотирьохциліндрові», «Шестициліндрові», «Восьмициліндрові». Обов'язковий
WEIGHT	ціле число	10	Вага автомобіля в кілограмах	Обов'язковий
CAPACITY	ціле число	10	Вантажопідйомність автомобіля	Обов'язковий
FUEL	строка	5	Вказує марку палива	Може приймати одне із значень: «А-92», «А-95», «А-98», «Дизель» або «Газ». Обов'язковий

Таблиця 4. Відношення сутності ТЕХНІЧНИЙ ПАСПОРТ

TECHNICAL_CERTIFICATE

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
CAR_FK	ціле число	10	Автомобіль, до якого відноситься тех. паспорт	Зовнішній ключ, що посилається на первинний ключ відношення CAR. Обов'язковий
EMPLOEE_FK	ціле число	10	Співробітник ДАІ, який видав тех.. паспорт	Зовнішній ключ, що посилається на первинний ключ відношення EMPLOEE. Обов'язковий
DATE	дата		Дата видачі тех. паспорту	Обов'язковий
NUMBER	ціле число	10	Номер технічного паспорту.	Обов'язковий
EXPIRATION_DATE	дата		Термін дії паспорту	Не може бути менший за дату видачі. Обов'язковий

Таблиця 5. Відношення сутності КЕРУВАННЯ

DRIVE_CAR

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
CAR_FK	ціле число	10	Автомобіль, яким має право керувати водій	Зовнішній ключ, що посилається на первинний ключ відношення CAR. Обов'язковий
DRIVER_FK	ціле число	10	Водій, якому надається право на керування	Зовнішній ключ, що посилається на первинний ключ відношення DRIVER. Обов'язковий

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

FINE

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
FINE_PK	ціле число	10	Унікальний ID	Первинний ключ. Обов'язковий
CAR_FK	ціле число	10	Автомобіль, до якого відноситься штраф.	Зовнішній ключ, що посилається на первинний ключ відношення CAR. Обов'язковий
DRIVER_FK	ціле число	10	Водій, до якого відноситься штраф.	Зовнішній ключ, що посилається на первинний ключ відношення DRIVER. Обов'язковий
EMPLOEE_FK	ціле число	10	Співробітник, який оформив штраф.	Зовнішній ключ, що посилається на первинний ключ відношення EMPLOEE. Обов'язковий
FINE	строка	100	Вид покарання	Обов'язковий
DATE	дата		Дата порушення	Обов'язковий
ARTICLE_FK	ціле число	10	Стаття, за якою було скоєно порушення	Зовнішній ключ, що посилається на первинний ключ відношення ARTICLE. Обов'язковий

Таблиця 7. Відношення сутності РОЗШУК

SEARCH

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
CAR_FK	ціле число	10	Автомобіль, який розшукується.	Зовнішній ключ, що посилається на первинний ключ відношення CAR. Обов'язковий
DATE	дата		Дата зникнення транспортного засобу.	Обов'язковий
TYPE	строка	300	З якої причини транспортний засіб в розшуку	Обов'язковий

Таблиця 8. Відношення сутності ШТРАФ-МАЙДАНЧИК

AREA

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
CAR_FK	ціле число	10	Автомобіль, який знаходиться на майданчику	Зовнішній ключ, що посилається на первинний ключ відношення CAR. Обов'язковий
PLACE	ціле число	5	Номер місця, на якому розміщений транспортний засіб	Не може бути від'ємним. Обов'язковий
DATE	дата		Дата парковки	Обов'язковий
FINE_FK	ціле число	10	Причина з якої авто було вилучено	Зовнішній ключ, що посилається на первинний ключ відношення FINE. Обов'язковий

Таблиця 9. Відношення сутності СПІВРОБІТНИК

EMPLOEE

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
EMPLOEE_PK	ціле число	10	Унікальний ID	Первинний ключ. Обов'язковий
F_NAME	строка	100	Прізвище співробітника	Обов'язковий
S_NAME	строка	100	Ім'я співробітника	Обов'язковий
T_NAME	строка	100	По-батькові співробітника	Обов'язковий
POST	строка	20	Посада, яку займає співробітник	Обов'язковий
RANK	строка	20	Звання співробітника.	Може приймати одне із значень: «Мл. Сержант», «Сержант», «Ст. Сержант», «Старшина», «Прапорщик», «Ст. Прапорщик», «Мл. Лейтенан», «Лейтенант», «Ст. Лейтенант», «Капітан», «Майор», «Підполковник», «Полковник». Обов'язковий
BIRTH	дата		Дата народження	Обов'язковий
BATTALION	ціле число	10	Номер батальйону	Обов'язковий

Таблиця 10. Відношення сутності АВТОМОБІЛЬНИЙ НОМЕР

NUMBER

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
NUMBER_PK	ціле число	10	Унікальний ID	Первинний ключ. Обов'язковий
TYPE	строка	10	До якого типу відносяться номера.	Може приймати одне із значень: «Транзитні» або «Постійні». Обов'язковий
NUMBER	строка	8	Номер	Обов'язковий
DATE	дата		Дата реєстрації	Обов'язковий

Таблиця 11. Відношення сутності СТАТТЯ

ARTICLE

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
ARTICLE_PK	ціле число	10	Унікальний ID	Первинний ключ. Обов'язковий
NUMBER	ціле число	5	Номер статті	Не може бути від'ємним Обов'язковий
DESC	строка	300	Формулювання статті.	Обов'язковий
PUNISHMENT	строка	100	Інформація щодо покарання за порушення	Обов'язковий

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

INSURANCE

Ім'я стовпця	Тип	Довжина	Призначення	Обмеження цілісності стовпців
CAR_FK	ціле число	10	Автомобіль, до якого відноситься страховка	Зовнішній ключ, що посилається на первинний ключ відношення CAR. Обов'язковий
FIRM	строка	50	Назва страхової фірми	Обов'язковий
TYPE	строка	50	Тип страховки	Може приймати одне із значень: «ОСАГО» або «КАСКО». Обов'язковий
PRICE	число з плаваючою точкою	10, 2	Сума страховки	Обов'язковий
DATE	дата		Дата страхування	Обов'язковий

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

Після того, як база даних повністю спроектована її можна зберегти у СУБД Oracle. Кожна сутність відповідає таблиці, атрибути сутності відповідають полям таблиці.

Створення таблиці ВОДІЙ

CREATE TABLE `DRIVER`(

`DRIVER_PK` NUMBER(10) PRIMARY KEY UNIQUE NOT NULL,

`F_NAME` VARCHAR2(100) NOT NULL,

`S_NAME` VARCHAR2(100) NOT NULL,

`T_NEME` VARCHAR2(100) NOT NULL,

`SEX` CHAR(1) NOT NULL CHECK(`SEX` IN ('f', 'm')),

`BIRTH` DATE NOT NULL,

`NUM_PASSPORT` VARCHAR2(10) NOT NULL,

`REGISTRATION` VARCHAR2(100) NOT NULL,

`NUM_IDENTIFICATION` NUMBER(10) NOT NULL,

`DATE` DATE NOT NULL,

`NUMBER` VARCHAR2(10) NOT NULL

);

Створення таблиці ТИП АВТОМОБІЛЯ

CREATE TABLE `TYPE`(

`TYPE_PK` NUMBER(10) PRIMARY KEY UNIQUE NOT NULL,

`BODY` VARCHAR2(20) NOT NULL CHECH(`BODY` IN('Седан','Уніврсал','Хетчбет','Купе','Лімузин','Мінівен','Таун-кар','Комби','Лифтбек','Кабріолет','Родстер','Фаетрон','Ландо','Брогам','Тарга','Спайдер','Пікап','Фургон')),

`ENGINE` VARCHAR2(20) NOT NULL CHECH(`ENGINE` IN('Двохтактні','Чотирьотактні','Двохциліндрові','Чотирьохциліндрові','Шестициліндрові','Восьмициліндрові')),

`WEIGHT` NUMBER(10) NOT NULL,

`CAPACITY` NUMBER(10) NOT NULL,

`FUEL` VARCHAR2(5) NOT NULL CHECK(`FUEL` IN('А-92','А-95','А-98','Дизель','Газ'))

);

Створення таблиці АВТОМОБІЛЬНИЙ НОМЕР

CREATE TABLE `NUMBER`(

`NUMBER_PK` NUMBER(10) PRIMARY KEY UNIQUE NOT NULL,

`TYPE` VARCHAR2(10) NOT NULL CHECK(`TYPE` IN('Транзитні','Постійні')),

`NUMBER` NUMBER(8) NOT NULL,

`DATE` DATE NOT NULL

);

Створення таблиці АВТОМОБІЛЬ

CREATE TABLE `CAR` (

`CAR_PK` NUMBER(10) PRIMARY KEY UNIQUE NOT NULL,

`TYPE_FK` NUMBER(10) NOT NULL REFERENCES TYPE(TYPE_PK) ON DELETE SET NULL,

`NUMBER_FK` NUMBER(10) NOT NULL REFERENCES NUMBER(NUMBER_PK) ON DELETE SET NULL,

`DRIVER_FK` NUMBER(10) NOT NULL REFERENCES DRIVER(DRIVER_PK) ON DELETE SET NULL,

`COUNTRY` VARCHAR2(100) NOT NULL,

`DATE` DATE NOT NULL,

`NUM_ENGINE` VARCHAR2(20) NOT NULL,

`NUM_BODY` VARCHAR2(20) NOT NULL,

`COLOR` VARCHAR2(50) NOT NULL

);

Створення таблиці КЕРУВАННЯ

CREATE TABLE `DRIVE_CAR`(

`CAR_FK` NUMBER(10) NOT NULL REFERENCES CAR(CAR_PK) ON DELETE CASCADE,

`DRIVER_FK` NUMBER(10) NOT NULL REFERENCES DRIVER(DRIVER_PK) ON DELETE CASCADE

);

Створення таблиці СПІВРОБІТНИКИ

CREATE TABLE `EMPLOEE`(

`EMPLOEE_PK` NUMBER(10) PRIMARY KEY UNIQUE NOT NULL,

`F_NAME` VARCHAR2(100) NOT NULL,

`S_NAME` VARCHAR2(100) NOT NULL,

`T_NEME` VARCHAR2(100) NOT NULL,

`POST` VARCHAR2(20) NOT NULL,

`RANK` VARCHAR2(20) NOT NULL CHECK(`RANK` IN('Мл. Сержант','Сержант','Ст. Сержант','Старшина','Прапорщик','Ст. Прапорщик','Мл. Лейтенан','Лейтенант','Ст. Лейтенант','Капітан','Майор','Підполковник','Полковник'))

`BIRTH` DATE NOT NULL,

`BATTALION` NUMBER(10) NOT NULL

);

Створення таблиці РОЗШУК

CREATE TABLE `SEARCH`(

`CAR_FK` NUMBER(10) NOT NULL REFERENCES CAR(CAR_PK) ON DELETE CASCADE,

`DATE` DATE NOT NULL,

`TYPE` VARCHAR2(300) NOT NULL

);

Створення таблиці ТЕХНІЧНИЙ ПАСПОРТ

CREATE TABLE `TECHNICAL_CERTIFICATE`(

`CAR_FK` NUMBER(10) NOT NULL REFERENCES CAR(CAR_PK) ON DELETE CASCADE,

`EMPLOEE_FK` NUMBER(10) NOT NULL REFERENCES EMPLOEE(EMPLOEE_PK) ON DELETE CASCADE,

`DATE` DATE NOT NULL,

`NUMBER` NUMBER(10) NOT NULL,

`EXPIRATION_DATE` DATE NOT NULL CHECK(`EXPIRATION_DATE`>`DATE`)

);

Створення таблиці СТАТТЯ

CREATE TABLE `ARTICLE`(

`ARTICLE_PK` NUMBER(10) PRIMARY KEY UNIQUE NOT NULL,

`NUMBER` NUMBER(5) NOT NULL CHECK(`NUMBER`>0),

`DESC` VARCHAR2(300) NOT NULL,

`PUNISHMENT` VARCHAR2(100) NOT NULL

);

Створення таблиці ШТРАФ

CREATE TABLE `FINE`(

`FINE_PK` NUMBER(10) PRIMARY KEY UNIQUE NOT NULL,

`CAR_FK` NUMBER(10) NOT NULL REFERENCES CAR(CAR_PK) ON DELETE CASCADE,

`DRIVER_FK` NUMBER(10) NOT NULL REFERENCES DRIVER(DRIVER_PK) ON DELETE CASCADE,

`EMPLOEE_FK` NUMBER(10) NOT NULL REFERENCES EMPLOEE(EMPLOEE_PK) ON DELETE SET NULL,

`FINE` VARCHAR2(100) NOT NULL,

`DATE` DATE NOT NULL,

`ARTICLE_FK` NUMBER(10) NOT NULL REFERENCES ARTICLE(ARTICLE_PK) ON DELETE CASCADE

);

Створення таблиці ШТРАФ-МАЙДАНЧИК

CREATE TABLE `AREA`(

`CAR_FK` NUMBER(10) NOT NULL REFERENCES CAR(CAR_PK) ON DELETE CASCADE,

`PLACE` NUMBER(5) NOT NULL CHECK(`PLACE`>0),

`DATE` DATE NOT NULL,

`FINE_FK` NUMBER(10) REFERENCES FINE(FINE_PK) ON DELETE SET NULL

);

Створення таблиці СТРАХОВКА

CREATE TABLE `INSURANCE`(

`CAR_FK` NUMBER(10) NOT NULL REFERENCES CAR(CAR_PK) ON DELETE CASCADE,

`FIRM` VARCHAR2(50) NOT NULL,

`TYPE` VARCHAR2(50) NOT NULL CHECK(`TYPE` IN('ОСАГО','КАСКО')),

`PRICE` NUMBER(10, 2) NOT NULL,

`DATE` DATE NOT NULL

);

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

Завдання 1. Вивести всіх водіїв автомобіля, який має номер АА0125ВІ.

SELECT d.F_NAME || d.S_NAME || d.T_NAME AS `Ім'я водіїв' FROM DRIVER d, DRIVE_CAR dc, NUMBER n, CAR c WHERE n.NUMBER_PK=c.NUMBER_FK AND c.CAR_PK=dc.CAR_FK AND dc.DRIVER_FK=dc.DRIVER_PK AND n.NUMBER='АА0125ВІ';

Завдання 2. Вивести номера всіх автомобілів, які мають штрафи за статтею номер 318.

SELECT n.NUMBER FROM NUMBER n, FINE f, ARTICLE a, CAR c WHERE n.NUMBER_PK=c.NUMBER_FK AND c.CAR_PK=f.CAR_FK AND f.ARTICLE_FK=a.ARTICLE_PK AND a.NUMBER='318';

Завдання 3. Вивести прізвище та дату народження власників автомобілів, які знаходяться на штраф-майданчику з 13-04-2010. Імена відсортувати по алфавіту.

SELECT d.F_NAME, d.BIRTH FROM DRIVER d, CAR c, AREA a WHERE a.CAR_FK=c.CAR_PK AND c.DRIVER_FK=d.DRIVER_PK AND a.DATE=TO_DATE('13-04-2010', `dd-mm-yyyy') ORDER BY d.E_NAME;

Завдання 4. Вивести імена співробітників, які зафіксували порушення за статтею номер 411 автомобілів з кузовом типу «Седан».

SELECT e.F_NAME || e.S_NAME || e.T_NAME FROM ARTICLE a, FINE f, EMPLOEE e, CAR c WHERE e.EMPLOEE_PK=f.EMPLOEE_FK AND a.ARTICLE_PK=f.ARTICLE_FK AND f.CAR_FK=c.CAR_PK AND c.TYPE_FK=(SELECT TYPE_PK FROM TYPE WHERE BODY='Седан') AND a.NUMBER=411;

Завдання 5. Вивести номера технічних паспортів, які оформив співробітник на прізвище «Довгодько» автомобілів у яких тип двигуна відповідає «Двохциліндрові».

SELECT tc.NUMBER FROM TECHNICAL_CERTICATE tc WHERE tc.EMPLOEE_FK=(SELECT EMPLOEE_PK FROM EMPLOEE WHERE F_NAME='Довгодько') AND tc.CAR_FK=(SELECT CAR_PK FROM CAR WHERE TYPE_FK=(SELECT TYPE_PK FROM TYPE WHERE ENGINE=` Двохциліндрові`));

Висновок

В цій курсовій роботі була спроектована база даних обліку автомобілів в ДАІ на прикладі київського МРЕВ ДАІ. Основними задачами проектування бази даних були:

· забезпечення зберігання в БД всієї необхідної інформації.

· забезпечення можливості отримання даних по всім необхідним запитам.

· скорочення надмірності та дублювання даних.

· забезпечення цілісності даних

База даних була спроектована на декількох рівнях:

· концептуальному

· логічному

· фізичному

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

Після цього остаточно сформувавши сутності, атрибути та зв'язки за допомогою SQL Oracle була побудована база даних та написані перевірочні запити.

За час виконання курсової роботи я в повному обсязі ознайомився з принципами проектування бази даних, вивчив предметну область системи обліку автомобілів в ДАІ та отримав практичний досвід проектування бази даних.

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