Функции в PostgreSQL. Проектирование структуры БД жилищной управляющей компании

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

Федеральное агентство по образованию

Государственное образовательное учреждение

высшего профессионального образования

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

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

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

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

Студенты:                                                                                   Преподаватель: Менжулин С.А.

Абалов Н.

Антонов А.                                       

Факультет:   АВТ

Группа:         АМ-610

Вариант:       8

Новосибирск

2009

Цель работы

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

Задание

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

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

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

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

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

Ход работы

Создание таблиц:

CREATE TABLE bill

(

  id serial NOT NULL,

  person_id integer NOT NULL,

  bill_date date NOT NULL,

  job_id integer NOT NULL,

  CONSTRAINT bill_id PRIMARY KEY (id),

  CONSTRAINT bill1 FOREIGN KEY (person_id)

      REFERENCES person (id) MATCH FULL

      ON UPDATE NO ACTION ON DELETE NO ACTION,

  CONSTRAINT bill2 FOREIGN KEY (job_id)

      REFERENCES job (id) MATCH FULL

      ON UPDATE NO ACTION ON DELETE NO ACTION

)

CREATE TABLE job

(

  id serial NOT NULL,

  task_id integer NOT NULL,

  worker_id integer NOT NULL,

  job_date date NOT NULL,

  completed boolean NOT NULL DEFAULT false,

  CONSTRAINT job_id PRIMARY KEY (id),

  CONSTRAINT job1 FOREIGN KEY (task_id)

      REFERENCES task (id) MATCH FULL

      ON UPDATE NO ACTION ON DELETE NO ACTION,

  CONSTRAINT job2 FOREIGN KEY (worker_id)

      REFERENCES workers (id) MATCH FULL

      ON UPDATE NO ACTION ON DELETE NO ACTION

)

CREATE TABLE loghist

(

  id serial NOT NULL,

  user_name character varying(128) NOT NULL,

  hist_date timestamp with time zone NOT NULL,

  "action" character varying(32) NOT NULL,

  CONSTRAINT log_id PRIMARY KEY (id)

)

CREATE TABLE payment

(

  id serial NOT NULL,

  bill_id integer NOT NULL,

  pay_date date NOT NULL,

  CONSTRAINT pay_id PRIMARY KEY (id),

  CONSTRAINT job2 FOREIGN KEY (bill_id)

      REFERENCES bill (id) MATCH FULL

      ON UPDATE NO ACTION ON DELETE NO ACTION

)

CREATE TABLE person

(

  id serial NOT NULL,

  firstname character varying(32),

  lastname character varying(32) NOT NULL,

  CONSTRAINT id PRIMARY KEY (id)

)

CREATE TABLE task

(

  id serial NOT NULL,

  "name" character varying(128) NOT NULL,

  price numeric(10,2) NOT NULL DEFAULT 0,

  CONSTRAINT task_id PRIMARY KEY (id),

  CONSTRAINT task_u UNIQUE (name)

)

CREATE TABLE workers

(

  id serial NOT NULL,

  "name" character varying(128) NOT NULL,

  CONSTRAINT workers_id PRIMARY KEY (id),

  CONSTRAINT workers_u UNIQUE (name)

)

Создание ролей:

CREATE ROLE "Administrator"

  SUPERUSER INHERIT CREATEDB CREATEROLE;

 CREATE ROLE "Analitik"

  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

CREATE ROLE "Operator"

  NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;

CREATE ROLE "user"

  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;

Создание функций:

CREATE OR REPLACE FUNCTION bill_delete(integer)

  RETURNS character AS

$BODY$