Лабораторна робота №2.
Тема. Використання транзакцій та DML.
Мета. Навчитися використовувати транзації, виконувати вставку, оновлення та видалення записів в таблицях бази даних.
Теоретичні матеріали. Інструкції Begin Tran, Rollback, Commit, Insert, Update, Delete, Select, Union, Case.
Завдання для лабораторної роботи
Написати скрипт, який використовує транзакцію та виконує завдання за варіантом.
Провести верифікацію скрипта у відповідній базі даних.
Варіанти завдання до лабораторної роботи №2.
Варіант 1. Додати до таблиці PC наступну модель: code: 20, model: 2111, speed: 950, ram: 512, hd: 60, cd: 52x, price: 1100.

Варіант 2. Додати до таблиці Product наступні продукти виробника Z: принтер моделі 4003, ПК моделі 4001 і блокнот моделі 4002.
Варіант 3. Додати до таблиці PC модель 4444 з кодом 22, що має швидкість процесора 1200 і ціну 1350. Відсутні характеристики повинні бути заповнені значеннями за замовчуванням, прийнятими для відповідних стовпців.
Варіант 4. Для кожної групи блокнотів з однаковим номером моделі додати запис в таблицю PC з наступними характеристиками:
код: мінімальний код блокнота в групі 20;
модель: номер моделі блокнота 1000;
швидкість: максимальна швидкість блокнота в групі;
ram: максимальний обсяг ram блокнота в групі * 2;
hd: максимальний обсяг hd блокнота в групі * 2;
cd: значення за замовчуванням;
ціна: максимальна ціна блокнота в групі, зменшена в 1,5 рази.

Варіант 5. Вилучити з таблиці PC комп’ютери, що мають мінімальний обсяг диска або пам’яті.
Варіант 6. Видалити всі блокноти, що випускаються виробниками, які не випускають принтери.
Варіант 7. Виробництво принтерів виробник A передав виробнику Z. Виконати відповідну зміну.
Варіант 8. Видаліть з таблиці Ships всі кораблі, потоплені в битвах.
Варіант 9. Внесіть зміни в таблиці Classes так, щоб калібри гармат вимірювалися в сантиметрах (1 дюйм = 2,5 см), а водотоннажність в метричних тоннах (1 метрична тонна = 1,1 тонни). Водотоннажність обчислити з точністю до цілих.
Варіант 10. Додати до таблиці PC ті моделі ПК з Product, які відсутні в таблиці PC. При цьому моделі повинні мати такі характеристики:
1. Код дорівнює номеру моделі плюс максимальний код, який був до вставки.
2. Швидкість, об’єм пам’яті і диска, а також швидкість CD повинні мати максимальні характеристики серед всіх наявних в таблиці PC.
3. Ціна повинна бути середньої серед всіх ПК, що були в таблиці PC до вставки.
Варіант 11. Для кожної групи блокнотів з однаковим номером моделі додати запис в таблицю PC з наступними характеристиками:
код: мінімальний код блокнота в групі 20;
модель: номер моделі блокнота 1000;
швидкість: максимальна швидкість блокнота в групі;
ram: максимальний обсяг ram блокнота в групі * 2;
hd: максимальний обсяг hd блокнота в групі * 2;
cd: cd c максимальною швидкістю серед всіх ПК;
ціна: максимальна ціна блокнота в групі, зменшена в 1,5 рази
Варіант 12. Додайте один дюйм до розміру екрана кожного блокнота, випущеного виробниками E і B, і зменшіть його ціну на $ 100.
Варіант 13. Ввести в базу даних інформацію про те, що корабель Rodney був потоплений в битві, що сталася 25/10/1944, а корабель Nelson пошкоджений ? 28/01/1945. Зауваження: вважати, що дата битви унікальна в таблиці Battles.
Варіант 14. Видаліть класи, що мають у базі даних менше трьох кораблів (врахувати кораблі з Outcomes).
Варіант 15. Для кожного корабля в таблиці Ships змінити назву, додавши префікс у вигляді типу класу і закінчення у вигляді року спуску на воду, наприклад, bc_Kongo_1913. Якщо рік спуску на воду корабля невідомий, закінчення додавати не потрібно (як і символ "_"). У разі перевищення розміру поля (20 символів) прибрати стільки символів справа в імені корабля, щоб нова назва складалося з 20 символів.

Варіант 16. Вилучити з таблиці Product ті моделі, які відсутні в інших таблицях.
Варіант 17. Для кожного корабля з невідомим роком спуску на воду записати в поле launched округлений до цілого числа середній рік спуску на воду кораблів цього класу. Якщо цей середній по класу рік невідомий, записати округлений до цілого числа середній рік спуску на воду кораблів країни даного корабля.
Варіант 18. Додати відсутні в таблиці Ships головні кораблі з Outcomes. Роком спуску на воду вважати середній округлений до цілого числа рік по кораблях країни додається корабля. Якщо середній рік невідомий, запис не вносити.
Варіант 19. Потопити в наступному бою судна, які у першій своїй битві були пошкоджені і більше не брали участь ні в яких битвах. Якщо наступного бою для такого судна не існує в базі даних, не вносити його в таблицю Outcomes.
Зауваження: в базі даних немає двох битв, які відбулися б в один день.
Варіант 20. Для кораблів, які брали участь всього в двох боях, поміняти результати (result) цих битв.
Наприклад, якщо в битві 1 результат був "ok", а в битві 2 ? "sunk", то має стати "ok" для битви 2 і "sunk" ? для битви 1.
Варіант 21. Замінити будь-яку кількість повторюваних прогалин у назвах кораблів з таблиці Ships на один пробіл.
Варіант 22. З кожної групи ПК з однаковим номером моделі в таблиці PC видалити всі рядки крім рядки з найбільшим для цієї групи кодом (стовпчик code).



Додатки.
А. Схеми баз даних
Комп’ютерна фірма
Схема БД складається з чотирьох таблиць:
Product (maker, model, type)PC (code, model, speed, ram, hd, cd, price)Laptop (code, model, speed, ram, hd, screen, price)Printer (code, model, color, type, price)
Таблиця Product представляє виробника (maker), номер моделі (model) і тип ('PC' – ПК, 'Laptop' – ПК-блокнот або 'Printer' – принтер). Припускається, що номера моделей в таблиці Product унікальні для всіх виробників та типів продуктів.
В таблиці PC для кожного ПК, який однозначно визначається унікальним кодом – code, вказані модель – model (зовнішній ключ до таблиці Product), швидкість – speed (процесора в мегагерцах), об’єм памяті – ram (в мегабайтах), розмір диска – hd (в гігабайтах), швидкість зчитуючого пристрою – cd (наприклад, '4x') та ціна – price.
Таблица Laptop аналогічна таблиці РС за винятком того, що замість швидкості CD містить розмір екрана – screen (в дюймах).
В таблиці Printer для кожної моделі принтера вказується, чи є він кольоровим – color ('y', якщо кольоровий), тип принтера – type (лазерний – 'Laser', струменевий – 'Jet' або матричний – 'Matrix') і ціна – price.
INCLUDEPICTURE "http://www.sql-ex.ru/images/computers.gif" \* MERGEFORMATINET
Фірма прийому сировини
Фірма має декілька пунктів прийому вторсировини. Кожний пункт отримує гроші для видачі здавачам вторсировити. Відомості про отримання грошей на пунктах прийому записуються в таблицю:
Income_o(point, date, inc)
Первинним ключем є (point, date). При цьому в стовпець date записується тільки дата (без часу), тобто прийом грошей (inc) на кожному пункті проводиться не частіше одного разу на день. Відомості про видачу грошей здавачам вторсировини записуються в таблицю:
Outcome_o(point, date, out)
У цій таблиці також первинний ключ (point, date) гарантує звітність кожного пункту про видані грошах (out) не частіше одного разу на день.
У випадку, коли прихід і витрата грошей може фіксуватися кілька разів на день, використовується інша схема з таблицями, що мають первинний ключ code:
Income(code, point, date, inc)
Outcome(code, point, date, out)
Тут також значення стовпця date не містять часу.
INCLUDEPICTURE "http://www.sql-ex.ru/images/income.gif" \* MERGEFORMATINET
Кораблі
Розглядається БД кораблів, що брали участь у другій світовій війні. Є наступні відношення:
Classes (class, type, country, numGuns, bore, displacement)
Ships (name, class, launched)
Battles (name, date)
Outcomes (ship, battle, result)
Кораблі в «класах» побудовані за одним і тим же проектом, і класу привласнюється або ім’я першого корабля, побудованого за даним проектом, або назві класу дається ім’я проекту, яке не збігається ні з одним з кораблів. Корабель, що дав назву класу, називається головним.
Відношення Classes містить ім’я класу, тип (bb для бойового (лінійного) корабля або bc для бойового крейсера), країну, в якій побудований корабель, число головних знарядь, калібр гармат (діаметр стовбура знаряддя в дюймах) і водотоннажність (вага в тоннах). У відношенні Ships записані назва корабля, ім’я його класу і рік спуску на воду. У відношенні Battles включені назва і дата битви, в якій брали участь кораблі, а у відношенні Outcomes ? результат участі даного корабля в битві (потоплений ? sunk, пошкоджений ? damaged або неушкоджений ? OK).
Зауваження. У відношення Outcomes можуть входити кораблі, які відсутні у відношенні Ships.
INCLUDEPICTURE "http://www.sql-ex.ru/images/ships.gif" \* MERGEFORMATINET
Аерофлот
Схема БД складається з чотирьох відношень:
Company (ID_comp, name)
Trip (trip_no, ID_comp, plane, town_from, town_to, time_out, time_in) Passenger (ID_psg, name)
Pass_in_trip (trip_no, date, ID_psg, place)
Таблиця Company містить ідентифікатор і назву компанії, яка здійснює перевезення пасажирів. Таблиця Trip містить інформацію про рейси: номер рейсу, ідентифікатор компанії, тип літака, місто відправлення, місто прибуття, час відправлення та час прибуття. Таблиця Passenger містить ідентифікатор та ім’я пасажира. Таблиця Pass_in_trip містить інформацію про польоти: номер рейсу, дата вильоту (день), ідентифікатор пасажира і місце, на якому він сидів під час польоту. При цьому слід мати на увазі, що
рейси виконуються щодня, а тривалість польоту будь-якого рейсу менше доби;
час і дата враховується щодо одного часового поясу;
час відправлення та прибуття вказується з точністю до хвилини;
серед пасажирів можуть бути однофамільці (однакові значення поля name, наприклад, Bruce Willis);
номер місця в салоні ? це число з буквою; число визначає номер ряду, літера (a ? d) ? місце в ряду зліва направо в алфавітному порядку;
зв’язки і обмеження показані на схемі даних.
INCLUDEPICTURE "http://www.sql-ex.ru/images/aero.gif" \* MERGEFORMATINET
Фарбування
Схема бази даних складається з трьох відношень:
utQ (Q_ID int,Q_NAME varchar(35)) utV (V_ID int,V_NAME varchar(35),V_COLOR char(1)) utB (B_Q_ID int,B_V_ID int,B_VOL tinyint, B_DATETIME datetime)
Таблиця utQ містить ідентифікатор і назву квадрата, колір якого спочатку чорний.
Таблиця utV містить ідентифікатор, назву та колір балончика з фарбою.
Таблиця utB містить інформацію про зафарбування квадрата балончиком: ідентифікатор квадрата, ідентифікатор балончика, кількість фарби і час фарбування.
При цьому слід мати на увазі, що:
Балончики з фарбою можуть бути трьох кольорів: червоний V_COLOR = 'R', зелений V_COLOR = 'G', блакитний V_COLOR = 'B' (латинські літери).
Обсяг балончика дорівнює 255 і спочатку він повний;
Колір квадрата визначається за правилом RGB, тобто R = 0, G = 0, B = 0 ? чорний, R = 255, G = 255, B = 255 ? білий;
Запис у таблиці зафарбувань utB зменшує кількість фарби в балончику на величину B_VOL і відповідно збільшує кількість фарби в квадраті на цю ж величину;
Значення 0 < B_VOL <= 255
Кількість фарби одного кольору в квадраті не перевищує 255, а кількість фарби в балончику не може бути менше нуля.
INCLUDEPICTURE "http://www.sql-ex.ru/images/painting.gif" \* MERGEFORMATINET