Робота з реляційними базами даних на прикладі PostgreSQL
1. Вступ
PostgreSQL - це
об'єктно-реляційна система управління базами даних (ORDBMS) заснована на
POSTGRES версії 4.2
<#"656775.files/image001.gif">
5.2 Завдання
Підготувати і занести в таблиці
контрольні дані
Розв'язок задачі
- Вставляємо
контрольні дані в таблицю ‘Регіони’into region values(1, 'Передкарпатська
нафтогазоносна область');into region values(2, 'Днiпровсько-Донецька
нафтогазоносна область');into region values(3, 'Причорноморсько-Кримська
газонафтоносна впадина');
-- Вставляємо
контрольні дані в таблицю ‘області’into
oblast values(1,1,'Івано-Франківська');into oblast
values(2,2,'Дніпропетровська');into oblast values(3,3,'Одесська');into oblast
values(1,4,'Львівська');into oblast values(2,5,'Донецька');into oblast
values(3,6,'Миколаївська');into oblast values(1,7,'Закарпатська');into oblast
values(2,8,'Харківська');into oblast values(3,9,'Херсонська');into oblast
values(1,10,'Чернівецька');into oblast values(2,11,'Запорізька');
-- Вставляємо
контрольні дані в таблицю ‘бази’into baza values(1,1,'Майдан НБ');into baza
values(2,7,'Чернiїв НБ');into baza values(3,12,'Яворів НБ');into baza
values(4,11,'Есхар НБ');into baza values(5,13,'Берізка НБ');into baza
values(6,5,'Проспект НБ');into baza values(7,3,'Паливо НБ');into baza
values(8,4,' Хотин НБ');into baza values(8,10,'Міжгір’я НБ');into baza
values(9,6,'Запоріжжя НБ');into baza values(10,2,'Вільно НБ');into baza
values(11,8,'Микантил НБ');into baza values(11,9,'Бурманськ НБ');
-- Вставляємо
контрольні дані в таблицю ‘паливо’into paluvo values(1,'01-10-2012',145,250,333);into
paluvo values(2,'02-10-2010',123,321,531);into paluvo
values(3,'04-10-2005',455,562,145);into paluvo
values(4,'07-11-2007',754,125,645);into paluvo
values(5,'18-11-2003',874,985,121);into paluvo
values(6,'17-10-2008',158,247,833);into paluvo values(7,'13-10-2009',844,347,175);into
paluvo values(8,'17-10-2002',559,577,48);into paluvo
values(9,'09-11-2002',891,147,614);into paluvo
values(10,'28-11-2001',198,549,178);into paluvo
values(11,'04-10-2000',189,277,400);into paluvo
values(12,'02-10-2008',151,578,844);into paluvo values(13,'03-10-2007',478,562,237);
Результати виконання завдання
Завдання
Виготовити запити, де відібрати дані
для звітів.
Задача 3.1
Виготовити запити для перегляду
вмісту таблиць зі всіма даними. Розв'язок
задачі
SELECT
region.n_reg
, oblast.n_obl,
baza.n_b,
paluvo.data,
paluvo.kilk_m,
paluvo.kilk_b,
paluvo.kilk_d
Результати
запиту
Задача
Побудувати запит про запаси ресурсів
на базах протягом останніх 11 років. Додати назву цього родовища.
Розв'язок задачі
SELECT
baza.n_b,
paluvo.kilk_b,
paluvo.kilk_d,.databaza INNER JOIN
paluvobaza.kod_b = paluvo.kod_bextract(YEAR FROM current_date) <
(extract(YEAR FROM paluvo.data) + 11)BY n_b, kilk_b, kilk_d ,data ;
Результатизадачі 3.2
Задача
Виготовити запит з відомостями про
родовища: назва регіону, назва області, назва нафтобази, кількість бензину,
масла, дизпалива . Вказати, що база бідна, якщо її запаси бензину і масла менше
350 тонн, а дизпалива менше 400 тонн.
Розв'язок задачі
SELECT
region.n_reg,
oblast.n_obl,
baza.n_b,paluvo.kilk_m,
paluvo.kilk_b, paluvo.kilk_d,WHEN
paluvo.kilk_m<550and paluvo.kilk_b<570 and paluvo.kilk_d<580 THEN
'бідна база' END AS text,baza,oblast,region.kod_reg = oblast.kod_reg
AND.kod_obl=baza.kod_obl AND.kod_b=paluvo.kod_b
Результати запиту:
.3 Завдання
Виготовити запит
про розподіл кількості нафтових областей по категоріях. Навпроти назв регіонів
і областей установити
значення кількості нафтових баз для кожної категорії.
Розв’язок
задачі
SELECT.n_obl, region.n_reg,
count(baza.kod_b)INNER JOIN oblast INNER JOIN regionregion.kod_reg =
oblast.kod_regoblast.kod_obl= baza.kod_obl,WHERE paluvo.kod_b = baza.kod_bBY
region.n_reg, oblast.n_obl;
Результати запиту
Завдання
Задача 8
Виготовити запити з відомостями про
всі нафтобази. Забезпечити відповідний напис для баз про ввивіз палива в
листопаді. Використовуючи курсори.
Розв’язок
задачі;CURSOR FOR*, CASE WHEN OR extract(month from paluvo.data)=11'Вивіз
палива!' END AS vuviz FROM paluvoJOIN baza ON baza.kod_b=paluvo.kod_bJOIN
oblast ON oblast.kod_obl = baza.kod_oblJOIN region ON
region.kod_reg=oblast.kod_reg;
FETCH 13 FROM curs;
5.4 Завдання
Виготовити звіт про розподіл
кількості нафтобаз, областей по категоріях. Навпроти назв областей установити
назви нафтобаз, розділених пробілом, установити значення кількості нафтобаз у
даній області.
Розв'язок задачі
CREATE or REPLACE function zvit4() returns
text
$$_o oblast %rowtype;_b baza
%rowtype;_r region %rowtype ;
_text text = '';_region region.n_reg
%type := '';_b_name baza.n_b %type := '';
_count integer := 0;rec_r IN SELECT
* FROM region ORDER BY region.n_regrec_o IN SELECT * FROM oblast ORDER BY
oblast.n_oblrec_r.kod_reg = rec_o.kod_regrec_b IN SELECT * FROM
bazarec_o.kod_obl = rec_b.kod_oblrec_r.n_reg != prev_regionrec_b.n_b <
prev_b_name
_text := _text || rpad(rec_o.n_obl,
70, '_') || rec_b.n_b|| E'\n';IF;
_count := _count + 1;_count > 0
_text := _text||E'\n'||repeat(' ',
30)||' '||rec_r.n_reg||E'\n'||(rec_o.n_obl, 70, '_')||rec_b.n_b||E'\n';
_count:=1;IF;_region :=
rec_r.n_reg;_b_name := rec_b.n_b;IF;LOOP;IF;LOOP;LOOP;
_text := _text ||repeat(' ',
30)||'Кількість родовищ : '||TO_CHAR(_count, '9')||E'\n';_text;;
$$plpgsql;zvit4();
Результат задачі
Задача. Утворити
запит про вартість
палива яке є на нафтобазі. Використовуючи курсори.;
DECLARECURSOR FORoblast.n_obl,
baza.n_b,
sum(paluvo.kilk_b*10.65+paluvo.kilk_d*6.45+paluvo.kilk_m*2.45),text('грн'),(month
FROM data) AS mis, extract (year FROM data) AS rik FROMINNER JOIN baza INNER
JOIN oblast(oblast.kod_obl=baza.kod_obl)(baza.kod_b=paluvo.kod_b)BY
oblast.n_obl,baza.n_b, mis, rikBY oblast.n_obl;13 FROM curs;
Результат:
Задача. Скласти
програму для видачі звіту за умовою задачі 6, використати курсор.
Розв’язок
задачіor replace function gg() returns TEXT as
$$
- локальні змінні_name
text;oblast_name text;baza_name text;alltxt text; kilk_b integer; kilk_d
integer; kilk_m integer;
- оголошення курсораCURSOR
FOR.n_reg, oblast.n_obl, baza.n_b, paluvo.kilk_b, paluvo.kilk_d,
paluvo.kilk_mINNER JOIN baza ON paluvo.kod_b=baza.kod_bJOIN oblast ON
baza.kod_obl=oblast.kod_oblJOIN region ON oblast.kod_reg=region.kod_reg;:='
Область'||' Нафтобаза,'||' Бензину(тонн),'||' Дизпалива(тонн),'||' Машинного
масла(тонн),'||'Регіон'||'\n\n';cur;cur INTO reg_name, oblast_name, baza_name,
kilk_b, kilk_d, kilk_m;when NOT FOUND;:= alltxt|| oblast_name||baza_name
||kilk_b ||' '||kilk_d ||' '||kilk_m ||' '||reg_name || '\n';loop;alltxt;;
$$plpgsql;gg() AS "ZVIT9";
Результат:
6. Висновки
реляційний база даних postgresql
Вона дозволяє модифікувати дані,
складати і виконувати запити, виводити результати у вигляді звітів.
Система управління базами даних
PostgreSQL, щоє однією з найрозвиненіших в своїй категорії, дозволяє повноцінну
реалізацію баз даних на основі SQL, забезпечує всі стандарти SQL, і крім того
дозволяє використання власних додаткових можливостей.
В даному курсовому проекті було
розглянуто роботу з реляційними базами даних на прикладі PostgreSQL. Було
реалізовано завдання по створенню бази даних для обробки інформації в
нафтогазовій області, зокрема отримання відомостей про родовища і поклади нафти
на території України, для чого використовувалась як мова запитів SQL, так і
мова програмування pl/pgSQL.
7. Список
використаної літератури
1. http://www.postgresql.org/docs/
. Клим
Б.В. Конспект лекцій по предмету “Бази даних”
. В.В.
Кириллов Основы проектирования реляционных баз данных. Учебное пособие. - СПб.:
ИТМО, 1994. - 90 с.
. М.
Мейер Теория реляционных баз данных. - М.: Мир, 1987. - 608 с.
. PostgreSQL
Reference Manual - Volume 1: SQL Language Reference ―
The PostgreSQL Global Development Group, 2007.
. Уорсли
Дж., Дрейк Дж. PostgreSQL. Для профессионалов. - СПб.: Питер, 2003. - 496с.