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

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

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

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

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

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

Факультет:                АВТ
Группа:                      АМ-809
Студент:                    Барсуков Д.О.
Преподаватель:         Трошина Г.В

Вариант:                    1

Новосибирск 2011

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

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

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

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

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

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

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

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

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

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

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

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

Выполнение работы

--Определение используемых типов данных

--Перечисление, представляющее позицию игрока на поле

CREATE TYPE pos AS ENUM

(

            'goalkeeper',

            'defender',

            'forward'

);

--Перечисление, представляющее опыт игрока

CREATE TYPE exp AS ENUM

(

            'beginner',

            'candidate',

            'master'

);

--Определениетаблиц

--Таблица-журнал событий. Каждая запись содержит название события и дату его

--возникновения

CREATE TABLE log

(

            id SERIAL PRIMARY KEY,

            event VARCHAR(30) NOT NULL,

            created DATE NOT NULL

);

--Таблица-справочник городов. Названия городов уникальны и должны быть набраны

--прописными символами, для названий автоматически создан индекс

CREATE TABLE city

(

            city_id SERIAL PRIMARY KEY,

            name VARCHAR(20) NOT NULL UNIQUE,

            CONSTRAINT name CHECK(upper(name) = name)

);

--Таблица-справочник команд. Пара «город – название» должны быть уникальными,

--название должно быть набрано прописными буквами, город должен быть записан в

--таблице city. При удалении города из таблицы city возникает ошибка, если ссылка на

--удаляемый город есть в таблице teams

CREATE TABLE teams

(

            team_id SERIAL PRIMARY KEY,

            name VARCHAR(20) NOT NULL,

            city_id INT NOT NULL REFERENCES city ON DELETE RESTRICT,

            CONSTRAINT name CHECK(upper(name) = name),

            CONSTRAINT name_city_id UNIQUE(name, city_id)

);

--Таблица игроков. Родной город игрока должен быть записан в таблице city; команда, в

--которой состоит игрок, должна быть в таблице teams; пара «команда – игровой

--номер» должна быть уникальной. При удалении команды из таблицы teams, ссылки на

--эту команду в записях таблицы players заменяются NULL

CREATE TABLE players

(

            player_id SERIAL PRIMARY KEY,

            name VARCHAR(20) NOT NULL,

            surname VARCHAR(20) NOT NULL,

            bday DATE NOT NULL,

            city_id INT NOT NULL REFERENCES city ON DELETE RESTRICT,

            height INT CHECK(height BETWEEN 140 AND 240),

            weight INT CHECK(weight BETWEEN 50 AND 150),

            experience exp,

            team_id INT REFERENCES teams ON DELETE SET NULL,

            position pos,

            num INT CHECK(num >= 0),