Задача 1. Спроектувати та утворити базу даних Zapas для зберігання та обробки інформації, яка містить такі дані про запаси нафти на родовищах України:

1. назва регіону; 2 - назва області; 3 - назва родовища; 4 - категорія родовища; 5 - геологічні запаси, тис. т; 6 - видобувні запаси, тис. т;

7 - дата представлення даних.

1	2	3	4	5	6	7
Передкарпатська нафтогазоносна область	Iвано-Франкiвська	Коломийське-Н	B	1593	129	19.04.94
Передкарпатська нафтогазоносна область	Iвано-Франкiвська	Слобідське-Н	А	775	3871	21.09.93
Передкарпатська нафтогазоносна область	Iвано-Франкiвська	Снятинське-ГН	AВ	726	13245	11.09.93
Передкарпатська нафтогазоносна область	Чернiвецька	Лужанське-Н	C2	6169	1814	18.03.92
Передкарпатська нафтогазоносна область	Чернiвецька	Хотинське-Н	ABC1	16844	4897	19.06.02
Передкарпатська нафтогазоносна область	Львiвська	Жидачівське-Н	C1	962	9935	10.03.03
Днiпровсько-Донецька нафтогазоносна область	Херсонська	Бехтерське-Н	АВС2	117	462	01.09.93
Днiпровсько-Донецька нафтогазоносна область	Херсонська	Пнiвське-НГК	АВ	903	313	21.07.02
Днiпровсько-Донецька нафтогазоносна область	Херсонська	Пнiвське-НГК	АВ	903	36	09.06.04
Днiпровсько-Донецька нафтогазоносна область	Сумська	Схiднiвське-НГ	В	1483	23	07.10.04
Днiпровсько-Донецька нафтогазоносна область	Сумська	Схiднiвське-НГ	B	1483	184	04.04.04
Причорноморсько-Кримська газонафтоносна впадина	Одеська	Жовтярське-Н	C1	2292	500	03.03.02
Причорноморсько-Кримська газонафтоносна впадина	Одеська	Жовтярське-Н	С1	2292	340	02.02.03
Причорноморсько-Кримська газонафтоносна впадина	Кримська АР	Сiвське-Н	А	254	14	11.19.94
Причорноморсько-Кримська газонафтоносна впадина	Кримська АР	Сiвське-Н	А	254	241	15.11.02
Причорноморсько-Кримська газонафтоносна впадина	Кримська АР	Сiвське-Н	А	254	14	21.12.92

Утворити таблиці БД. Рекомендації до побудови таблиць:

Регіон: код регіону, назва регіону;

Область: код регіону, код області, назва області;

Родовище: код області, код родовища, назва родовища, геологічні запаси, категорія родовища;

Запаси: код родовища, видобувні запаси, дата представлення даних.

Задача 2. Підготувати і занести в таблиці контрольні дані.

Задача 3. Виготовити запити, де відібрати дані для звітів.

Задача 4. Виготовити звіт про розподіл кількості родовищ областей по категоріях. Навпроти назв регіонів і областей, розділених пробілом, установити значення кількості родовищ для кожної категорії. Передбачити підсумки по вертикалі і по горизонталі.

Задача 5. Побудувати запит про динаміку зміни видобувних запасів нафти протягом останніх 15 років заданого родовища. Додати назву цього родовища.

Задача 6. Виготовити звіт про різницю геологічних і видобувних запасів нафти в родовищах на поточну дату. Оскільки зміна видобувних запасів відбувається нерегулярно (рідко), врахувати останні дані. У звіт внести скорочені назви регіонів та повні назви областей і родовищ, розділені комами. До заголовка звіту додати слова “станом на” і встановити поточну дату.

Задача 7. Виготовити запити для перегляду вмісту таблиць зі всіма даними та доповнення БД відомостями про зміну видобувних запасів нафти.

Задача 8. Виготовити запит з відомостями про родовища: назва регіону, назва області, назва родовища, категорія родовища, геологічні та видобувні запаси нафти. Вказати, що родовище невелике, якщо геологічні запаси нафти менші за 1 млн т.

Завдання 1

Спроектувати та утворити базу даних Zapas для зберігання та обробки інформації, що містить такі дані про запаси нафти на родовищах України:

1. назва регіону;

2. назва області;

3. назва родовища;

4. категорія родовища;

5. геологічні запаси, тис. т;

6. видобувні запаси, тис. т;

7. дата представлення даних;

Розв’язок

Схема даних виглядатиме наступним чином:

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

CREATE TABLE region (id INTEGER, name VARCHAR(80));

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

CREATE TABLE zone (id INTEGER, region_id INTEGER, name VARCHAR(80));

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

CREATE TABLE depository (id INTEGER, zone_id INTEGER, name VARCHAR(80), reserves FLOAT, category VARCHAR(10));

--Створення таблиці запаси

CREATE TABLE reserves(depository_id INTEGER, recoverable_reserves FLOAT, date DATE );

-- Додаємо обмеження до таблиць

ALTER TABLE region ADD PRIMARY KEY (id);

ALTER TABLE zone ADD PRIMARY KEY (id);

ALTER TABLE zone ADD CONSTRAINT cs_zone FOREIGN KEY (region_id) REFERENCES region(id);

ALTER TABLE depository ADD PRIMARY KEY (id);

ALTER TABLE depository ADD CONSTRAINT cs_depository_check FOREIGN KEY (zone_id) REFERENCES zone(id);

ALTER TABLE depository ADD CONSTRAINT cs_depository CHECK (reserves>0);

ALTER TABLE reserves ADD CONSTRAINT cs_reserves_check CHECK(recoverable_reserves>0);

ALTER TABLE reserves ADD CONSTRAINT cs_reserves FOREIGN KEY (depository_id) REFERENCES depository(id);

--Створення послідовності setid і додавання обмеження DEFAULT до поля id таблиці region, використовуючи дану послідовність

create SEQUENCE setid minvalue 0;

ALTER TABLE region ALTER id SET DEFAULT nextval('setid');

Результати виконання:

Завдання 2

Підготувати і занести в таблиці контрольні дані

-- Вставляємо контрольні дані в таблицю ‘Регіони’

INSERT INTO region (id, name) VALUES(1, 'Передкарпатська нафтогазоносна область');

INSERT INTO region (id, name) VALUES(2, 'Днiпровсько-Донецька нафтогазоносна область');

INSERT INTO region (id, name) VALUES(3, 'Причорноморсько-Кримська газонафтоносна впадина');

-- Вставляємо контрольні дані в таблицю ‘Області’

INSERT INTO zone (id, region_id, name) VALUES(1, 1,'Iвано-Франкiвська');

INSERT INTO zone (id, region_id, name) VALUES(2, 1,'Чернівецька');

INSERT INTO zone (id, region_id, name) VALUES(3, 1, 'Львiвська');

INSERT INTO zone (id, region_id, name) VALUES(4, 2, 'Херсонська');

INSERT INTO zone (id, region_id, name) VALUES(5, 2, 'Сумська');

INSERT INTO zone (id, region_id, name) VALUES(6, 3, 'Одеська');

INSERT INTO zone (id, region_id, name) VALUES(7, 3,'Кримська АР');

-- Вставляємо контрольні дані в таблицю ‘Родовища’

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(1, 1, 'Коломийське-Н', 1593, 'B');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(2, 1, 'Слобідське-Н', 775, 'A');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(3, 1, 'Снятинське-ГН',726, 'AB');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(4, 2, 'Лужанське-Н', 6169, 'C2');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(5, 2, 'Хотинське-Н', 16844, 'ABC1');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(6, 3, 'Жидачівське-Н', 962, 'C1');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(7, 4, 'Бехтерське-Н', 117, 'ABC2');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(8, 4, 'Пнiвське-НГК', 903, 'AB');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(9, 4, 'Пнiвське-НГК',903, 'AB');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(10, 5, 'Схiднiвське-НГ',1483, 'B');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(11, 5, 'Схiднiвське-НГ',1483, 'B');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(12, 6, 'Жовтярське-Н', 2292, 'C1');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(13, 6, 'Жовтярське-Н', 2292, 'C1');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(14, 7, 'Сiвське-Н', 254, 'A');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(15, 7, 'Сiвське-Н', 254, 'A');

INSERT INTO depository(id, zone_id, name, reserves, category) VALUES(16, 7, 'Сiвське-Н', 254, 'A');

-- Вставляємо контрольні дані в таблицю ‘Запаси’

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (1, 129, '1994-04-19');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (2, 3871, '1993-09-21');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (2, 3501, '1998-09-04');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (2, 2995, '2001-04-11');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (2, 2552, '2009-11-13');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (3, 13245, '1993-09-11');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (4, 1814, '1992-03-18');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (5, 4897, '2002-06-19');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (6, 9935, '2003-03-10');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (7, 462, '1993-09-01');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (8, 313, '2002-07-21');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (9, 36, '2004-06-09');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (10, 23, '2004-10-07');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (11, 184, '2004-04-04');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (12, 500, '2002-03-03');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (13, 340, '2003-02-02');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (3, 944, '1993-09-11');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (4, 1314, '2005-03-28');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (5, 1897, '2009-06-04');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (6, 935, '2008-11-04');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (15, 241, '2002-11-15');

INSERT INTO reserves (depository_id, recoverable_reserves, date) VALUES (16, 14, '1992-12-21');