Лабораторна робота з університету по Microsoft SQL

1. Створення бази даних у СУБД MS SQL Server 2019 Увійдіть до середовища Microsoft SQL Server Management Studio з групи програм Microsoft SQL Server Tools. Оберіть з'єднання із сервером, що пропонується за замовчуванням. У вікні, що відображається зліва, клацніть правою кнопкою миші на папці Databases і виберіть опцію зі створення нової БД – New Database (рис. 4.1). Рис. 4.1. Створення нової БД у середовищі Microsoft SQL Server Management Studio У вікні, що з'явилося (рис. 4.2) надайте нову БД назву латинськими літерами, бажано без пробілів. Щоб зберегти, натисніть ОК. Рис. 4.2. Вікно для визначення параметрів нової БД Організація баз даних. - Танасюк Ю.В., Одайська Х.С. 2 2. Підготовка логічної моделі до перевантаження У веб-браузері перейдіть до онлайн-середовища DB Designer за посиланням https://www.dbdesigner.net/. Увійдіть до свого акаунта через пункт меню Login. З переліку збережених проектів оберіть потрібну схему БД. Середовище проектування схеми БД – це не просто засіб графічного відображення схеми БД. Завдяки його можливостям нам не доведеться вручну створювати таблиці, визначати їх склад та формувати з'єднання у СУБД. Натомість, на основі розробленої логічної моделі буде згенеровано SQL-скрипт зі створення відповідних компонентів, який можна зберегти та запустити на виконання у конкретній середовищі. Оскільки навіть найменші невідповідності можуть викликати помилки при створенні реальних таблиць, перш ніж братися за фізичну реалізацію розробленої моделі, слід переконатися у правильності налаштування таких параметрів: - назви сутностей (таблиць) та атрибутів (полів); - типи даних для атрибутів; - первинні ключі та їх властивості; - зовнішні ключі та зв'язки між таблицями. Під час підготовки до перевантаження логічної моделі бази даних до СУБД слід дотримуватися таких рекомендацій:  Упевніться, що ER-діаграма розроблена саме для тієї СУБД, до якої її буде перенесено. Необхідна інформація міститься на панелі Properties (Властивості) у верхньому правому куті середовища DB Designer. Для зміни СУБД створіть нову схему БД, вказавши відповідну систему, скористайтесь пунктом меню Schema>Load і оберіть з переліку модель БД.  Назви таблиць та атрибутів потрібно надавати латинськими літерами.  Імена повинні бути змістовними, містити лише букви, цифри та позначку підкреслення, мати завдовжки не більше 10 символів (для зручності читання). Більше рекомендацій щодо іменування об'єктів у SQL можна знайти за посиланням: https://www.sqlstyle. guide/ru/  Назви об'єктів не можуть бути зарезервованими словами мови SQL, а саме group, order, year, table, user. Повний список можна переглянути за посиланням: https://docs.actian.com/psql/psqlv13/index.html#page/sqlref/sqlkword.htm.  Для уникнення небажаних збігів та уточнення змісту атрибуту краще при іменуванні поєднувати кілька слів, наприклад student_group, order_id, release_year, reserved_table, book_user тощо.  Упевніться, що для атрибутів зазначені типи, що підтримуються СУБД, в якій буде створюватись база даних. Зокрема, перелік типів, визначених для MS SQL Server, можна переглянути за посиланням: https://docs.microsoft.com/ru-ru/sql/t-sql/data-types/data-typestransact-sql?view=sql-server -ver15  Довжину слід вказувати лише для символьних атрибутів типу char, nchar, varchar, nvarchar.  Первинні ключі повинні бути цілочисельними.  Властивість Auto Increment краще обирати лише для первинних ключів.  При утворенні зв'язків типи первинних та зовнішніх ключів повинні збігатися.  Для полів первинного та зовнішнього ключів має бути встановлена властивість Not Null, що унеможливлює пропуски значень. Тому при визначенні характеристик цих атрибутів не потрібно позначати опцію Allow nulls. Будь-які виявлені невідповідності потрібно усунути на самій ER-діаграмі, адже не всі з них вдасться виправити після перенесення до СУБД. 3. Генерування та збереження SQL-коду Щоб розпочати перенесення готової схеми БД у меню середовища DB Designer оберіть пункт Export >Sql (рис.4.3). Організація баз даних. - Танасюк Ю.В., Одайська Х.С. 3 Мал. 4.3. Опція Export в онлайн-середовище проектування бази даних DB Designer У вікні, що з'явилося, виберіть СУБД у якої на першому кроці була створена база даних для перенесення логічної моделі. Як бачимо на рис. 4.4, це MS SQL Server. Щоб згенерувати SQL-код для створення відповідної схеми БД, позначаємо опцію Create script і натискаємо кнопку Generate SQL. Рис. 4.4. Налаштування параметрів для генерування SQL-скрипту з створення вмісту БД Згенерований SQL-код, що відповідає розробленій логічній моделі БД, з'являється в окремому вікні (рис. 4.5). Його можна зберегти у SQL-файлі (Download SQL file), виділити і скопіювати вручну до текстового документа або повернутися до попереднього кроку (Back). Рис. 4.5. Вікно з SQL-скриптом, згенерованим на основі розробленої логічної моделі БД Організація баз даних. - Танасюк Ю.В., Одайська Х.С. 4 Перегляньте згенерований SQL-скрипт, зверніть увагу на синтаксис команд зі створення таблиць, визначення їх складу, формування зв'язків та інших обмежень. Весь згенерований SQL-код виділіть, скопіюйте у буфер і спочатку додайте до звіту з лабораторної роботи (рис. 4.6). Рис. 4.6. Копіювання згенерованого SQL-коду 4. Запуск на виконання у СУБД згенерованого SQL-коду Перейдіть до середовища Microsoft SQL Server Management Studio, оберіть створену базу даних. Як показано на рис. 4.7, у меню виберіть опцію New Query (Новий запит). Рис. 4.7. Створення в базі даних нового запиту для запуску згенерованого SQL-коду Додайте попередньо скопійований SQL-скрипт до вікна створення нового запиту до бази даних (рис. 4.8). Упевніться, що додано весь код без втрат. Як бачите, команди можна редагувати, проте не варто вдаватися до цього без нагальної потреби. Виконання доданого набору SQL-команд дозволить створити в базі спроектовані на ERдіаграми таблиці з визначеними властивостями та зв'язками. Запит можна запустити за допомогою кнопки Execute (Виконати), позначеної на рис. 4.8. Відгук про результат запиту буде відображатись у нижній частині вікна (рис. 4.9). У нашому випадку повідомлення свідчить про успішне виконання. Організація баз даних. - Танасюк Ю.В., Одайська Х.С. 5 Мал. 4.8. Додавання згенерованого SQL-коду до нового запиту у базі даних Рис. 4.9. Результат виконання запиту зі створення таблиць у базі даних 5. Перевірка правильності створення таблиць у БД Перелік створених таблиць можна переглянути у відповідній базі даних, відкривши папку Tables. У нашому випадку в MovieTheatre утворено вісім таблиць (рис. 4.9), що узгоджується із розробленою схемою бази даних. Поява після запуску SQL-коду повідомлень, виділених червоним кольором, свідчить про наявність помилок під час виконання запиту. Перегляньте зміст повідомлень, зазвичай вони вказують на невідповідності, які виникли при створенні таблиць, полів або обмежень, визначених на схемі. Ошибка у визначенні параметрів одного атрибуту може унеможливити створення цілої таблиці, а також її дочірніх таблиць. Поверніться до розробки ER-діаграми. Перевірте та виправіть параметри, що спричинили конфлікт. Повторно згенеруйте код та запустіть його на виконання у СУБД. 6. Створення діаграми бази даних, перенесеної до СУБД Для зручності перевірки результатів перевантаження логічної моделі бази даних до СУБД створимо її діаграму, яка дозволить графічно відобразити наявний набір користувацьких таблиць із повним набором полів та зв'язків. Щоб створити діаграму бази даних, виберіть відповідну папку Database Diagram, клацніть на ній правою кнопкою миші та в контекстному меню виберіть пункт New Database Diagram (рис. 4.10). Організація баз даних. - Танасюк Ю.В., Одайська Х.С. 6 Мал. 4.10. Створення нової діаграми бази даних у Microsoft SQL Server Management Studio Рис. 4.11. Додавання таблиць, що необхідно зобразити на діаграмі бази даних Далі з'являється вікно Add Table, в якому можна обрати таблиці для приєднання до діаграми (рис. 4.11). Додана таблиця автоматично вилучається з переліку. В результаті одержується діаграма, приблизний вигляд якої наведений на рис. 4.12. На ній відразу можна перевірити наявність усіх таблиць, їх склад, а також переконатися у правильності утворення зв'язків. Організація баз даних. - Танасюк Ю.В., Одайська Х.С. 7 Мал. 4.12. Результуюча діаграма створеної бази даних Утворена діаграма входитиме до складу бази даних. Для її збереження при закритті діаграми вкажіть назву, як показано на рис. 4.13, та натисніть ОК. Рис. 4.12. Збереження нової діаграми у базі даних Якщо при перевантаженні моделі деякі таблиці були втрачені, їх можна створити вручну безпосередньо на базі даних. Також можна відновити зв'язки між таблицями. Припустимо, що було втрачено зв'язки між таблицями Film, Actor та Role (рис. 4.13). Рис. 4.13. Нагадаємо, що згідно з розробленою діаграмою, сутність Role походить від батьківських сутностей Actor та Film і утворює з ними зв'язок типу «один-до-багатьох». Для цього у дочірньої сутності Role є два зовнішні ключі: actor_id та film_id, пов'язані з первинними ключами відповідних базових сутностей. Щоб відновити з'єднання, клацніть правою кнопкою миші на похідній таблиці Role і виберіть пункт меню Relationships (Зв'язки), як показано на рис. 4.13. Організація баз даних. - Танасюк Ю.В., Одайська Х.С. 8 Мал. 4.14. Створення зв'язку в дочірній таблиці У вікні Foreign Key Relationships (Зв'язки зовнішнього ключа) (рис. 4.14) для утворення нового зв'язку натискаємо кнопку Add (Додати), У лівій частині вікна з'являється умовне позначення нового зв'язку . Для визначення пов'язаних полів та таблиць для нього вибираємо у правій частині вікна пункт Table And Column Specification (Специфікація таблиць та полів) та натискаємо кнопку з трьома крапками (рис. 4.15, а). а б Мал. 4.15. Визначення властивостей зв'язку між таблицями Організація баз даних. - Танасюк Ю.В., Одайська Х.С. 9 При відновленні зв'язку із таблицею Actor у вікні визначення таблиць і полів (Tables and Columns), як видно на рис. 4.15, б, справа відображається дочірня таблиця Role (Foreign key table). Обираємо серед її полів зовнішній ключ actor_id. Ліворуч обираємо з переліку батьківську таблицю Actor (Primary key table) та з переліку полів обираємо поле первичного ключа – actor_id, до якого веде посилання. Зверніть увагу, що зв'язок змінивши свою назву на FK_Role_Actor. Для збереження змін натискаємо ОК. Аналогічним чином у вікні, зображеному на рис. 4.15, а, можна додати ще одне з'єднання із таблицею Film, і вибравши його в розділі Selected Relationships, визначити відповідні параметри (рис. 4.16). Рис. 4.16. Параметри зв'язку між таблицями Film та Role У результаті виконаних налаштувань до таблиці Role відновлено дві зв'язки, які можна побачити як у переліку Selected Relationships (рис. 4.17, а), так і графічно на діаграмі бази даних (рис. 4.17, б). а б Мал. 4.17. Відновлені зв'язки між дочірньою та батьківськими таблицями у Microsoft SQL Server Management Studio ПОРЯДОК ВИКОНАННЯ РОБОТИ 1. Створіть у відповідній СУБД базу даних, до якої буде перевантажено спроектовану в DB Designer схему БД. 2. Перевірте логічну модель БД та підготуйте її для перевантаження відповідно до наданих рекомендацій. 3. Згенеруйте та збережіть у звіті SQL-скрипт із створення спроектованої моделі БД. 4. Запустіть на виконання згенерований SQL-код у СУБД, перевірте правильність створення таблиць, у разі потреби, внесіть корективи до розробленої моделі. 5. Створіть та збережіть діаграму перевантаженої бази даних у СУБД так і графічно на діаграмі бази даних (рис. 4.17, б). а б Мал. 4.17. Відновлені зв'язки між дочірньою та батьківськими таблицями у Microsoft SQL Server Management Studio ПОРЯДОК ВИКОНАННЯ РОБОТИ 1. Створіть у відповідній СУБД базу даних, до якої буде перевантажено спроектовану в DB Designer схему БД. 2. Перевірте логічну модель БД та підготуйте її для перевантаження відповідно до наданих рекомендацій. 3. Згенеруйте та збережіть у звіті SQL-скрипт із створення спроектованої моделі БД. 4. Запустіть на виконання згенерований SQL-код у СУБД, перевірте правильність створення таблиць, у разі потреби, внесіть корективи до розробленої моделі. 5. Створіть та збережіть діаграму перевантаженої бази даних у СУБД так і графічно на діаграмі бази даних (рис. 4.17, б). а б Мал. 4.17. Відновлені зв'язки між дочірньою та батьківськими таблицями у Microsoft SQL Server Management Studio ПОРЯДОК ВИКОНАННЯ РОБОТИ 1. Створіть у відповідній СУБД базу даних, до якої буде перевантажено спроектовану в DB Designer схему БД. 2. Перевірте логічну модель БД та підготуйте її для перевантаження відповідно до наданих рекомендацій. 3. Згенеруйте та збережіть у звіті SQL-скрипт із створення спроектованої моделі БД. 4. Запустіть на виконання згенерований SQL-код у СУБД, перевірте правильність створення таблиць, у разі потреби, внесіть корективи до розробленої моделі. 5. Створіть та збережіть діаграму перевантаженої бази даних у СУБД Відновлені зв'язки між дочірньою та батьківськими таблицями у Microsoft SQL Server Management Studio ПОРЯДОК ВИКОНАННЯ РОБОТИ 1. Створіть у відповідній СУБД базу даних, до якої буде перевантажено спроектовану в DB Designer схему БД. 2. Перевірте логічну модель БД та підготуйте її для перевантаження відповідно до наданих рекомендацій. 3. Згенеруйте та збережіть у звіті SQL-скрипт із створення спроектованої моделі БД. 4. Запустіть на виконання згенерований SQL-код у СУБД, перевірте правильність створення таблиць, у разі потреби, внесіть корективи до розробленої моделі. 5. Створіть та збережіть діаграму перевантаженої бази даних у СУБД Відновлені зв'язки між дочірньою та батьківськими таблицями у Microsoft SQL Server Management Studio ПОРЯДОК ВИКОНАННЯ РОБОТИ 1. Створіть у відповідній СУБД базу даних, до якої буде перевантажено спроектовану в DB Designer схему БД. 2. Перевірте логічну модель БД та підготуйте її для перевантаження відповідно до наданих рекомендацій. 3. Згенеруйте та збережіть у звіті SQL-скрипт із створення спроектованої моделі БД. 4. Запустіть на виконання згенерований SQL-код у СУБД, перевірте правильність створення таблиць, у разі потреби, внесіть корективи до розробленої моделі. 5. Створіть та збережіть діаграму перевантаженої бази даних у СУБД Згенеруйте та збережіть у звіті SQL-скрипт із створення спроектованої моделі БД. 4. Запустіть на виконання згенерований SQL-код у СУБД, перевірте правильність створення таблиць, у разі потреби, внесіть корективи до розробленої моделі. 5. Створіть та збережіть діаграму перевантаженої бази даних у СУБД Згенеруйте та збережіть у звіті SQL-скрипт із створення спроектованої моделі БД. 4. Запустіть на виконання згенерований SQL-код у СУБД, перевірте правильність створення таблиць, у разі потреби, внесіть корективи до розробленої моделі. 5. Створіть та збережіть діаграму перевантаженої бази даних у СУБД