5.1 Завдання 1
Спроектувати та утворити базу даних Turyst для автоматизації послуг фірми “Сьорфінг” на основі таблиці А20 додатка А, яка містить такі дані про фірму “Сьорфінг”:
найменування маршруту;
кількість днів;
дата початку маршруту;
поточна кількість проданих путівок, шт;
умови проживання;
умови харчування;
ціна за одну путівку, у. о.;
початкова кількість путівок, шт.
Розв’язок
--Створення таблиці маршрут
CREATE TABLE marshrut (nom INTEGER, name CHARACTER(40), kil_d FLOAT, kil_p INTEGER, k_u_p INTEGER, k_u_h INTEGER, cina FLOAT);
--Створення таблиці облік реалізації
CREATE TABLE oblik_realizaciy (nom INTEGER, data DATE, kil_p_p INTEGER);
--Створення таблиці проживання
CREATE TABLE prozgyvannya (k_u_p INTEGER, u_p CHARACTER (80));
--Створення таблиці харчування
CREATE TABLE harchuvannya (k_u_h INTEGER, u_h CHARACTER(80));
-- Додаємо обмеження до таблиць
ALTER TABLE marshrut ADD PRIMARY KEY (nom) ;
ALTER TABLE oblik_realizaciy ADD FOREIGN KEY (nom) REFERENCES marshrut(nom);
ALTER TABLE marshrut ADD PRIMARY KEY (k_u_p);
ALTER TABLE marshrut ADD PRIMARY KEY (k_u_h);
ALTER TABLE prozgyvannya ADD CONSTRAINT cs_marshrut FOREIGN KEY (k_u_p) REFERENCES marshrut(k_u_p);
ALTER TABLE harchuvannya ADD CONSTRAINT cs_marshrut FOREIGN KEY (k_u_h) REFERENCES marshrut(k_u_h);
ALTER TABLE marshrut ADD CONSTRAINT cs_marshrut CHECK (kil_p>0);
ALTER TABLE marshrut ADD CONSTRAINT cs_marshrut CHECK (kil_d>0);
ALTER TABLE marshrut ADD CONSTRAINT cs_marshrut CHECK (cina>0);
ALTER TABLE oblik_realizaciy ADD CONSTRAINT cs_oblik_realizaciy CHECK (kil_p_p>5);
-- Створення послідовності setnom і додавання обмеження DEFAULT до поля nom таблиці marshrut, використовуючи дану послідовність:
CREATE SEQUENCE setnom minvalue 0;
ALTER TABLE marshrut ALTER nom SET DEFAULT nextval('setnom');
Результати виконання:
5.2 Завдання 2
Підготувати і занести в таблиці контрольні дані, відредагувати, якщо потрібно
Розв'язок задачі
-- Вставляємо контрольні дані в таблицю ‘Маршрут’
INSERT INTO marshrut VALUES(1, 'Івано-Франківськ-Яремче', 2.5, 20, 1, 2, 230);
INSERT INTO marshrut VALUES(2, 'Київ-Прага', 10, 10, 1, 2, 850.5);
INSERT INTO marshrut VALUES(3, 'Львів-Одеса', 7, 15, 1, 2, 400);
INSERT INTO marshrut VALUES(4, 'Київ-Лондон', 25, 10, 1, 2, 4000);
INSERT INTO marshrut VALUES(5, 'Івано-Франківськ-Крим', 7, 40, 1, 2, 680);
-- Вставляємо контрольні дані в таблицю ‘Облік реалізації’
INSERT INTO oblik_realizaciy VALUES(1, '20.10.2012', 20);
INSERT INTO oblik_realizaciy VALUES(2, '16.12.2012', 9);
INSERT INTO oblik_realizaciy VALUES(3, '5.11.2012', 12);
INSERT INTO oblik_realizaciy VALUES(4, '5.05.2012', 8);
INSERT INTO oblik_realizaciy VALUES(5, '18.12.2011', 37);
INSERT INTO oblik_realizaciy VALUES(1, '10.10.2012', 14);
INSERT INTO oblik_realizaciy VALUES(1, '22.06.2011', 19);
INSERT INTO oblik_realizaciy VALUES(3, '05.10.2009', 14);
INSERT INTO oblik_realizaciy VALUES(2, '02.09.2012', 10);
INSERT INTO oblik_realizaciy VALUES(3, '06.01.2013', 18);
INSERT INTO oblik_realizaciy VALUES(1, '11.02.2012', 10);
INSERT INTO oblik_realizaciy VALUES(4, '10.12.2012', 8);
INSERT INTO oblik_realizaciy VALUES(5, '10.12.2012', 37);
-- Вставляємо контрольні дані в таблицю ‘Проживання’
INSERT INTO prozgyvannya VALUES(1, '3* готелі');
INSERT INTO prozgyvannya VALUES(1, '3* і 5* готелі');
INSERT INTO prozgyvannya VALUES(1, '2* і 3* готелі');
-- Вставляємо контрольні дані в таблицю ‘Харчування’
INSERT INTO harchuvannya VALUES(2, '3-разове харчування');
INSERT INTO harchuvannya VALUES(2, 'сніданок');
INSERT INTO harchuvannya VALUES(2, 'без харчування');
-- Редагуємо контрольні дані в таблиці ‘Облік реалізації’
UPDATE oblik_realizaciy SET kil_p_p = 10 WHERE nom = 4;
UPDATE oblik_realizaciy SET kil_p_p = 39 WHERE nom = 5;
-- Редагуємо контрольні дані в таблиці‘Проживання’
UPDATE prozgyvannya SET k_u_p = 2 WHERE u_p = '3* готелі';
UPDATE prozgyvannya SET k_u_p = 3 WHERE u_p = '3* і 5* готелі';
-- Редагуємо контрольні дані в таблиці‘Харчування’
UPDATE harchuvannya SET k_u_h = 4 WHERE u_h = '3-разове харчування';
UPDATE harchuvannya SET k_u_h = 5 WHERE u_h = 'сніданок';
UPDATE harchuvannya SET k_u_h = 6 WHERE u_h = 'без харчування';
-- Редагуємо контрольні дані в таблиці‘Маршрут’
UPDATE marshrut SET k_u_p = 1 WHERE nom = 1;
UPDATE marshrut SET k_u_p = 2 WHERE nom = 2;
UPDATE marshrut SET k_u_p = 3 WHERE nom = 3;
UPDATE marshrut SET k_u_p = 1 WHERE nom = 4;
UPDATE marshrut SET k_u_p = 3 WHERE nom = 5;
UPDATE marshrut SET k_u_h = 4 WHERE nom = 1;
UPDATE marshrut SET k_u_h = 5 WHERE nom = 2;
UPDATE marshrut SET k_u_h = 6 WHERE nom = 3;
UPDATE marshrut SET k_u_h = 5 WHERE nom = 4;
UPDATE marshrut SET k_u_h = 4 WHERE nom = 5;
UPDATE marshrut SET cina = 980 WHERE nom = 4;
Результати виконання завдання
5.3 Завдання 3
Виготовити запити, де відібрати дані для звітів.
--При виготовленні запиту, використовуємо JOIN для об’єднання таблиць
SELECT * FROM oblik_realizaciy
INNER JOIN marshrut ON marshrut.nom=oblik_realizaciy.nom
INNER JOIN prozgyvannya ON marshrut.k_u_p=prozgyvannya.k_u_p
INNER JOIN harchuvannya ON marshrut.k_u_h=harchuvannya.k_u_h;
Результати запиту
Задача 5.
Утворити запит про помісячну сумарну виручку за путівки в поточному році. Виручка за путівки дорівнює її ціні, помноженій на кількість проданих.
Розв'язок задачі
SELECT name, sum(marshrut.cina*oblik_realizaciy.kil_p_p),
extract (month FROM data) AS mis, extract (year FROM data) AS rik FROM oblik_realizaciy
INNER JOIN marshrut ON (marshrut.nom=oblik_realizaciy.nom)
WHERE extract (year FROM data)=extract (year FROM current_date)
GROUP BY name, mis, rik
ORDER BY name;
Результати запиту
Задача 7.
Виготовити запити для перегляду вмісту таблиць з даними та доповнення БД відомостями про нові маршрути і редагування кількості проданих путівок.
Розв'язок задачі
SELECT *FROM marshrut;
SELECT *FROM oblik_realizaciy;
SELECT *FROM prozgyvannya;
SELECT *FROM harchuvannya;
SELECT name, kil_p_p
FROM marshrut, oblik_realizaciy
WHERE oblik_realizaciy.nom=marshrut.nom AND oblik_realizaciy.nom=4;
SELECT name, kil_p_p
FROM marshrut, oblik_realizaciy
WHERE oblik_realizaciy.nom=marshrut.nom AND oblik_realizaciy.nom=5;
