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

НАЦІОНАЛЬНИЙ АВІАЦІЙНИЙ УНІВЕРСИТЕТ

Факультет кібербезпеки, комп'ютерної та програмної інженерії

Кафедра комп'ютеризованих систем захисту інформації

Курсова робота

з дисципліни «Технології програмування»

Програма «Полювання і рибалка»

Київ 2020



Зміст

Завдання

Теоретичні відомості

Схема зв'язків БД

Створення та заповнення таблиць

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

Висновок

Список використаної літератури



Завдання

ІІ. «Полюваня та рибалка»

Написать програму «Полюваня та рибалка», яка буде складатися з таких таблиць:

· Gun - містить інформацію про рушниці.

· Tackle - містить інформацію про снасті.

· Price - містить інформацію про ціни на продукцію.

· Client - містить інформацію про покупців.

· Seller - містить інформацію про продавців.

У програмі повинні бути реалізовані такі пункти:

· Створення таблиць, повинні програмно.

· Створити первинні і зовнішні ключі таблиць.

· Таблиця, «Сlient», повинна містити не менше 20-ти записів.

· Таблиця, «Tackle», повинна містити не менше 5-ти записів

· Таблиця, «Gun», повинна містити не менше 10-ти записів.

· Повинна бути реалізована можливість:

o Додавання даних про нових покупців (Insert);

o Зміна даних про нових покупців (Update);

o Видалення даних про нових покупців (Delete);

o Покупки не більше 3 товарів в один день.

Програма повинна:

1. Виводити інформацію про продавців відділів. Приклад:

· Рушниці

· Іван Іванов

· Стаж 3 роки

· Снасті

· Андрей Андреев

· Стаж 5 років

· Снасті

· Сергій Сергеев

· Стаж 6 місяців

2. Виводити інформацію про конкретного покупця. Приклад:

· Ідентифікатор у таблиці (id)

· Ім'я

· Призвіще

· По-батькові

· Тел. номер

· Країна

· Наявність дозволу

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

id_route	Продавец	Покупатель	Отдел	Название товара	Производство	Дата покупки
249	Андрей Андреев	Ксюша Собчак	Ружья	Зауер	Германия	23.02.2010
250	Сергей Сергеев	Дима Билан	Снасти	Спиннинг1	Россия	1.03.2010
251	Иван Иванов	Олег Котов	Снасти	Спиннинг2	Китай	12.01.2010

4. Вміти сортувати таблицю з пункту «3», по: Покупцям, Країні, Товару, Даті.

5. Створити уявлення по запиту з пункту «1».

6. Накласти обмеження на вміст всієї бази даних за допомогою затвердження.

7. Створити можливість додавання покупців з блокуванням доступу до таблиці інших користувачів до внесення даних.

8. Організувати можливість резервного копіювання та відновлення даних

9. Організувати виконання послідовності операторів SQL, яка не переривається іншими користувачами при створенні таблиці продовцов відділів на підставі стажу роботи. У разі однакової стажу роботи (передбачити таку можливість). Скасувати створення таблиці продовцов до отримання інформації про стаж роботи.



Теоретичні відомості

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

Створення бази даних засобами SQL здійснюється за допомогою оператора CREATE DATABASE

CREATE DATABASE test; - Створення бази даних test

Максимальна довжина імені бази даних складає 64 знаки та може вміщувати літери, цифри та символи _ та $. Ім'я може починатись з цифри, але не повинне повністю складатись з цифр.

В каталозі бази даних (з версії 4.1.1) створюється файл db.opt, в якому вказується кодування, яке використовується в базі даних, за замовчуванням - default-character-set та порядок сортування символів default-collation.

Для контролю створення бази даних та для перевірки вже існуючих баз даних можна використовувати оператор SHOW DATABASES.

Бази даних information_shema, performance_shema і mysql - службові та необхідні для підтримки сервера MySQL у робочому стані - в них зберігається інформація про облікові записи, регіональні налаштування, тощо.

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

DROP DATABASE test1; - Видалення бази даних test1

Якщо реалізовується спроба створити базу даних, яка вже існує, то СУБД поверне помилку.Для попередження даного виду помилок використовується конструкція IF NOT EXISTS, при наявності якої база даних створюється лише у тому випадку, якщо з такою назвою не існує бази даних.

CREATE DATABASE IF NOT EXISTS test;

Такий самий механізм розроблений для оператора DROP DATABASE: використання конструкції IF EXISTS видаляє базу даних, якщо вона існує, і не здійснює операцію видалення, якщо база даних не існує.

DROP DATABASE IF EXISTS test;

При створенні бази даних можна вказувати кодування, яка буде використовуватись у таблицях та стовпцях за замовчуванням. Для цього після імені бази даних варто вказати ключове слово DEFAULT CHARACTER SET charset_name, де charset_name - назва кодування, наприклад, cp1251, яка позначає українське Windows-кодування.

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET cp1251;

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

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

CREATE TABLE tb1 (number INT, name TEXT);

В дужках вказуються назви стовпців та їх тип через кому. Створена таблиця tb1 має два поля: number, куди можна записувати цілі числа (INT), та поле name, в якому розміщуються рядки (TEXT). Максимальна довжина назви таблиць та стовпців складає 64 знаки та може вміщувати літери, цифри та символи _ та $. Ім'я може починатись з цифри, але не повинне повністю складатись з цифр.

Видалення таблиці здійснюється за допомогою оператора DROP TABLE

DROP TABLE tb1;

Звертатись до таблиці можна по назві, наприклад, tb1, а також за допомогою розширеного синтаксису, який включає назву бази даних та назву таблиці, наприклад test. tb1. Будь-який SQLоператор може звертатись до бази даних по цій назві. Відповідно до цього, оператор DROP TABLE може мати наступний вигляд: DROP TABLE test.tb1. При використанні розширеного синтаксису допускається не здійснювати вибір бази даних оператором USE, так як база явно вказується в запиті.

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

Заповнення таблиці даними

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

1. Однорядковий оператор INSERT вносить в таблицю даних новий запис;

2. Багаторядковий оператор INSERT вносить в таблицю даних декілька нових записів;

3. Пакетне завантаження даних використовується для внесення даних в таблицю з зовнішнього файлу

Однорядковий оператор INSERT

Однорядковий оператор INSERT може використовуватись в декількох формах. Спрощений синтаксис першої форми виглядає наступним чином:

INSERT [IGNORE] [INTO] tb1 [(col_name,…)] VALUES (expression,…)

При написанні синтаксису операторів, необов'язкові ключові слова, які можуть не використовуватись, записуються в квадратні дужки. Даний оператор добавляє новий запис в таблицю tb1, значення запису перераховуються в переліку expression, порядок слідування стовпців може задаватись списком col_name. Значення передаються в списку після ключового слова VALUES.

Багаторядковий оператор INSERT

Багаторядковий оператор INSERT співпадає по формі з однорядковим оператором. В ньому використовується ключове слово VALUES, після якого вноситься не один запис, а декілька записів expression

Видалення даних

Для видалення записів з таблиць передбачено два оператора:

1. DELETE

2. TRUNCATE TABLE.

Оператор DELETE

Оператор DELETE має наступний синтаксис:

DELETE FROM tb1 WHERE where_definition ORDER BY … LIMIT rows

Оператор видаляє з таблиці tb1 записи, які задовольняють умову where_definition.

Оператор TRUNCATE TABLE

Оператор TRUNCATE TABLE, на відміну від оператора DELETE, повністю очищує таблицю та не використовує умовне видалення. Тобто оператор TRUNCATE TABLE аналогічний оператору DELETE без умови WHERE та LIMIT. На відміну від оператора DELETE, видалення відбувається значно швидше, так як не реалізовується перебір кожного запису, а повне очищення таблиці. Оптимізатор запитів MySQL автоматично використовує оператор TRUNCATE TABLE, якщо оператор DELETE не містить WHERE-умову або конструкцію LIMIT.

Оновлення даних

Оновлення даних дозволяє змінювати значення полів в існуючих записах. Для оновлення даних використовуються оператори UPDATE та REPLACE. Перший оператор дозволяє оновлювати окремі поля в існуючих записах, тоді як оператор REPLACE більш схожий на оператор INSERT, за винятком того, що якщо старий запис в таблиці має таке ж значення індексу UNIQUE або PRIMARY KEY, що й новий запис, то існуючий запис перед заміною на новий буде видалений.

Оператор UPDATE

Оператор UPDATE має наступний синтаксис:

UPDATE [IGNORE] tb1 SET caol1=expr1 [, col2=expr2…] [WHERE where_definition] [ORDER BY …] [LIMIT rows]

В інструкції, одразу після ключового слова UPDATE вказується таблиця tb1, в яку вносяться зміни. В конструкції SET перераховуються стовпці, які необхідно оновити, та встановлюються нові значення. Необов'язкова умова WHERE дозволяє задати критерій відбову рядків - оновленню будуть підлягати тільки ті рядки, які задовольняють умову where_definition.

Оператор REPLACE

Оператор REPLACE працює аналогічно INSERT, за винятком того, що у випадку дублювання значень UNIQUE та PRIMARY KEY нового запису, то спочатку буде видалений старий запис і тільки після цього в таблицю буде доданий новий запис. Варто зауважити, що у випадку відсутності індексів UNIQUE та PRIMARY KEY, то застосування REPLACE не матиме змісту, так як працюватиме даний оператор як оператор INSERT. Синтаксис оператора REPLACE аналогічний оператору INSERT:

REPLACE [INTO] tb1 [(col_name, …)] VALUES (expression, …), (…),

Редагування структури таблиці

Найбільш розповсюдженими задачами зміни структури таблиці - зміна порядку слідування стовпців, їх назв, створення нових індексів і т.д. Всі ці операції реалізовуються за допомогою SQL-оператора ALTER TABLE.

Оператор ALTER TABLE має наступний синтаксис:

ALTER TABLE tb1 alter_specification[, alter_specification] …

Одразу після оператора ALTER TABLE вказується назва таблиці tb1, яка підлягає зміні, і специфікація alter_specification, яка визначає зміну, що вноситься. В одному операторі ALTER TABLE дозволяється вказувати одразу декілька специфікацій через кому.

Механізм роботи оператора наступний: під час роботи оператор ALTER TABLE створює копію вихідної таблиці. Необхідна зміна здійснюється у копії, після цього вихідна таблиця видаляється, а нова таблиця змінює назву. Це здійснюється для того, щоб в нову таблицю автоматично додавалися всі оновлення, окрім невдалих. Під час виконання оператора ALTER TABLE вихідна таблиця доступна для читання іншим клієнтам.

Для додання стовпця вказується специфікатор оператора ALTER TABLE - ADD [COLUMN]

Видалення стовпця відбувається за допомогою специфікатора DROP [COLUMN] оператора ALTER TABLE

Зміна стовпців у створеній таблиці

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

При необхідності зміни порядку слідування стовпців використовуються ключові слова FIRST та AFTER.

Специфікація MODIFY в операторі ALTER TABLE не дозволяє змінити ім'я стовпця, для цього призначена специфікація CHANGE. Після даного ключового слова вказується ім'я стовпця, після цього - нове ім'я та новий тип. Навіть якщо тип залишається попереднім, його необхідно вказати.

Облікові записи СУБД MySQL

Обліковий запис складається з декількох частин та приймає форму 'username'@'host', де username - ім'я, а host - назва хосту, з якого користувачу username надано дозвіл звертатися до серверу MySQL.

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

Оператор CREATE USER

Оператор CREATE USER дозволяє створювати новий обліковий запис та має наступний синтаксис:

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']

Оператор створює обліковий запис user з необов'язковим паролем.

Якщо пароль не вказується, за замовчуванням в якості паролю слугує порожній рядок.

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

Для підвищення рівня захищеності, в MySQL дозволено використовувати плагіни автентифікації, наприклад mysql_native_password (реалізує локальну автентифікацію, тобто автентифікацію, яка ґрунтується на методі хешування паролю, який використовується перед введенням автентифікації підключення), sha256_password (впроваджує базову автентифікацію SHA-256), caching_sha2_password (впроваджує автентифікацію SHA-256 (як і sha256_password), але використовує кешування для підвищення продуктивності та має додаткові функції для більш широкого застосування).

Використання плагіну вказується у властивостях автентифікації, синтаксис створення нового облікового запису:

CREATE USER user IDENTIFIED WITH auth_plugin

CREATE USER user IDENTIFIED WITH auth_plugin BY 'auth_string'

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

Другий варіант створення облікового запису встановлює плагін автентифікації облікового запису в auth_plugin, передає відкрите (не хешоване) значення 'auth_string' до плагіну для хешування та зберігає результат у рядку облікового запису mysql.user.

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

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

CREATE USER дозволяє наступні значення параметрів ресурсу:

· MAX_QUERIES_PER_HOUR count,

· MAX_UPDATES_PER_HOUR count,

· MAX_CONNECTIONS_PER_HOUR count

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

MAX_USER_CONNECTIONS count

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

Наприклад, необхідно створити обліковий запис 'user6'@'localhost' з паролем 'password' з обмеженою кількістю запитів - 500 в годину, та з кількістю оновлень - 100 в годину

Оператор GRANT

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



Схема зв'язків БД

Первинні ключі таблиць виділені сірим кольором.

Створення та заповнення таблиць

Створюємо БД з таблицями:

· Gun - містить інформацію про рушниці.

· Tackle містить інформацію про снасті.

· Price - містить інформацію про ціни на продукцію.

· Client - містить інформацію про покупців.

· Seller - містить інформацію про продавців.

· Orders - містить інформацію про замовлення.



1. Створюємо БД

2. Створення таблиці Gun, яка містить інформацію про рушниці:

3. Створення таблиці Tackle, яка містить інформацію про снасті:

4. Створення таблиці Seller, яка містить інформацію про продавців:

5. Створення таблиці Client, яка містить інформацію про покупців:

6. Створення таблиці Price, яка містить інформацію про ціни на продукцію:

7. Створення таблиці Orders, яка містить інформацію про замовлення:

8. Відображення створених таблиць:



9. Заповнення таблиці Client даними:



10. Заповнення таблиці Seller даними:



11. Заповнення таблиці Trackle даними:

12. Заповнення таблиці Gun даними:



13. Заповнення таблиці Price даними:

14. Заповнення таблиці Orders даними:



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

1. Повинна бути здійснена можливість:

Можливість додавання даних про нового покупця

Можливість оновлення даних про покупця

Можливість видалення даних про покупця



Можливість перевірки покупок до 3 за день

2. Виведення інформації про продавців відділів:

3. Виведення інформації про конкретного покупця:

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



5. Сортування таблиці із пункта 4 по:

· Покупцям

· Країні

· Товару

· Даті

6. Створення представлення по запросу з пункта 2:

7. Накладення обмеження на вміст всієї бази даних за допомогою твердження.

8. Створення можливості додавання покупців з блокуванням доступу до таблиці інших користувачів до внесення даних.

Для початку створюється новий користувача та надаються йому всі привілегії:

Потім блокується доступ до таблиці інших користувачів до внесення даних:

Виконується вхід під користувачем userkyrs2 і він намагається внести дані в таблицю:

9. Організовується можливість резервного копіювання та відновлення даних:

Створення резервної копії бази даних:

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

Відновлення даних за допомогою копії бази даних:

програма покупець таблиця користувач

Або ж через MySQL Command Line запустити:

10. Організовується виконання послідовності операторів SQL, яка не переривається іншими користувачами при створенні таблиці продавців відділів на підставі стажу роботи. У разі однакового стажу роботи (передбачити таку можливість). Скасувати створення таблиці продавців до отримання інформації про стаж роботи.

Початок транзакції та створення таблиці продавців з відділу Рушниці:

Виявлення, де однаковий стаж роботи:



Висновок

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

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



Список використаної літератури

1. Курс лекцій з дисципліни «Технології програмування».

2. Алан Бьюли - Изучаем SQL. Москва. 2007 - 315 с.

3. Грофф Дж. Р., Вайнберг П.Н., Оппель Э. Дж. SQL. Полное руководство. Москва. 2015 - 952с.

4. Редмонд Э., Уилсон Д. Семь баз данных за семь недель. Москва. 2013. 383с.

5. https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/index.html

3. https://habr.com/ru/post/47031/

4. https://webistore.ru/sql/tranzakcii-v-mysql/

5. https://hetmanrecovery.com/ru/recovery_news/backup-and-recovery-of-mysql-databases-using-mysqldump.htm

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