Функции в PostgreSQL. Проектирование структуры БД гарантийного ремонта

Министерство образования и науки Российской Федерации

Новосибирский государственный технический университет

Кафедра вычислительной техники

Лабораторная работа №6

по дисциплине «Базы данных»

Функции в PostgreSQL. Проектирование структуры БД.

Факультет: АВТ                                                                         Преподаватель:

Группа:       АМ-610                                                                   Менжулин С. А.

Вариант:     9

Студенты:  Королевская В.Е.

                    Таратухин И.И.

Новосибирск, 2009

1.  Цель работы

Изучить правила создания функций. Приобрести практические навыки создания функций в среде PostgreSQL. Научиться проектировать БД в целом на основе поставленного задания с использованием всех полученных ранее базовых знаний и навыков.

2.  Задание

Ознакомиться с теоретическими сведениями о возможностях создания пользовательских функций в PostgreSQL. Разработать БД в соответствии с индивидуальным заданием. Создать функции, реализующие интерфейс для работы с базой данных. Проверить работоспособность функций путем выполнения этих функций с параметрами, обеспечивающими как успешное выполнение функции, так и невыполнение функции.

Обязательные требования к БД:

1.  Наличие таблиц-справочников и таблиц, использующих справочники. Предусмотреть сохранение ссылочной целостности для таблиц, использующих таблицы-справочники.

2.  Предусмотреть следующие роли:

·  оператор БД (пополнение справочников)

·  пользователь БД (основная работа с БД, с ограничениями для некоторого вида операций)

·  аналитик (разрешено выполнение запросов и функций, не изменяющих данные в БД)

·  администратор БД (просмотр протокола операций, любые изменения БД)

3.  Действия, изменяющие БД пользователем с любой ролью протоколируются в таблице-журнале операций.

4.  Для всех запросов необходимо создать индексы (для гарантированного использования индексов можно использовать отключение параметра enable_seqscan в текущей сессии)

Создать функции, реализующие интерфейс для работы с базой данных. Проверить работоспособность функций путем выполнения этих функций с параметрами, обеспечивающими как успешное выполнение функции, так и невыполнение функции.

3.  Индивидуальная часть

База данных гарантийного ремонта. Должна содержать следующие данные: информацию о выпускаемых производителем товарах, гарантийных мастерских в разных городах и товарах, ремонт которых они могут производить, данные о выполненных ремонтах. Предусмотреть анализ следующих показателей: рейтинг убыточности товаров, обеспеченность каждого города мастерскими по разным группам товаров и всему ассортименту в целом.

4.  Ход работы

С помощью функции add_n(int) добавить в какую-либо из таблиц, созданных в лабораторной работе №1, 1000 записей

CREATE FUNCTION add_n(integer)

  RETURNS character AS

'declare t int;

begin select max(id) into t from predmet;

for k in (t+1)..($1+t+1) loop

insert into predmet values (round(random()*10^12)||' '|| round(random()*10^5), round(random()*10^5), k);

end loop;

return 'Done!';

end;'

  LANGUAGE 'plpgsql' VOLATILE

  COST 100;

ALTER FUNCTION add_n(integer) OWNER TO lab01;

Выборка без использования индекса

shop=# EXPLAIN ANALYSE select * from predmet where id=500;

"Seq Scan on predmet  (cost=0.00..21.79 rows=1 width=30) (actual time=0.481..0.893 rows=1 loops=1)"

"  Filter: (id = 500)"

"Total runtime: 0.990 ms"

shop=# EXPLAIN ANALYSE select * from predmet where id<500;

"Seq Scan on predmet  (cost=0.00..21.79 rows=516 width=30) (actual time=0.017..1.379 rows=516 loops=1)"

"  Filter: (id < 500)"

"Total runtime: 2.221 ms"

shop=# EXPLAIN ANALYSE select * from predmet where id>500;

"Seq Scan on predmet  (cost=0.00..21.79 rows=507 width=30) (actual time=0.392..1.765 rows=506 loops=1)"

"  Filter: (id > 500)"

"Total runtime: 2.787 ms"

shop=# EXPLAIN ANALYSE select * from predmet where id between 573 and 800;

"Seq Scan on predmet  (cost=0.00..24.34 rows=227 width=30) (actual time=0.532..1.626 rows=228 loops=1)"

"  Filter: ((id >= 573) AND (id <= 800))"

"Total runtime: 2.277 ms"

Индекс типа BTREE

shop=# EXPLAIN ANALYSE select * from predmet where id=500;

"Index Scan using predmet1 on predmet  (cost=0.00..8.27 rows=1 width=30) (actual time=0.042..0.047 rows=1 loops=1)"

"  Index Cond: (id = 500)"

"Total runtime: 0.127 ms"

shop=# EXPLAIN ANALYSE select * from predmet where id<500;

"Seq Scan on predmet  (cost=0.00..21.79 rows=516 width=30) (actual time=0.042..1.806 rows=516 loops=1)"

"  Filter: (id < 500)"

"Total runtime: 2.840 ms"

shop=# EXPLAIN ANALYSE select * from predmet where id>500;

"Seq Scan on predmet  (cost=0.00..21.79 rows=507 width=30) (actual time=0.166..1.068 rows=506 loops=1)"

"  Filter: (id > 500)"

"Total runtime: 1.837 ms"

shop=# EXPLAIN ANALYSE select * from predmet where id between 573 and 800;

"Index Scan using predmet1 on predmet  (cost=0.00..17.79 rows=227 width=30) (actual time=0.075..0.731 rows=228 loops=1)"

"  Index Cond: ((id >= 573) AND (id <= 800))"

"Total runtime: 1.241 ms"

Индекс типа HASH

shop=# EXPLAIN ANALYSE select * from predmet where id=500;

"Index Scan using predmet1 on predmet  (cost=0.00..8.27 rows=1 width=30) (actual time=0.055..0.059 rows=1 loops=1)"

"  Index Cond: (id = 500)"

"Total runtime: 0.135 ms"

shop=# EXPLAIN ANALYSE select * from predmet where id<500;

"Seq Scan on predmet  (cost=0.00..21.79 rows=516 width=30) (actual time=0.033..2.671 rows=516 loops=1)"

"  Filter: (id < 500)"

"Total runtime: 3.685 ms"

shop=# EXPLAIN ANALYSE select * from predmet where id>500;