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

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

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

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

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

«Пользователи, ограничения и транзакции в PostgreSQL»

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

Вариант:                    1

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

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

Задание. Ознакомиться с теоретическими сведениями о возможностях создания пользователей баз данных, использования транзакций в PostgreSQL. Создать нового пользователя и зайти под его именем. Создать и заполнить базу данных своего варианта. Таблицы (минимум по 5 записей в каждой) связать между собой полями идентификаторов. С помощью команд интерактивного терминала psql просмотреть структуру базы данных, структуру таблиц, просмотреть данные в них. Ознакомиться с теоретическими сведениями о возможностях создания ограничений в PostgreSQL. Наложить ограничения согласно своему варианту. Проверить работоспособность ограничений путем добавления в таблицы данных, удовлетворяющих и не удовлетворяющих условиям ограничений. Создать транзакционный блок, в котором производится добавление в таблицы произвольных полей, создать несколько производных таблиц, просмотреть структуру измененных таблиц. Не завершая транзакции параллельно запустить еще одно окно терминала, подключиться к базе и попробовать добавить и удалить записи в таблицы. Сделать откат транзакций, просмотреть структуру таблиц. Сохранить базу данных в файл. Удалить базу данных и восстановить ее из файла. Просмотреть и проанализировать полученную в результате выполнения операций информацию. Сохранить базу в файл для использования в следующих работах.

Вариант 1. Создать и заполнить базу данных игроков хоккейной команды, состоящую из четырех таблиц. На основании созданных таблиц создать таблицу, содержащую поля: имя, фамилия, игровая позиция, игровой номер, оклад, игровое время. Запретить ввод роста хоккеиста более 220 см и менее 150 см. Поле «хват клюшки» должно содержать только значения правый и левый. «Игровая позиция» может быть только вратарь, нападающий или защитник.

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

//Создание нового пользователя:

postgres=# create user dimon with password '0000' createdb valid until 'infinity';

CREATE ROLE

postgres=# \q

C:\PostgreSQL\bin>psql --file "D:\Desktop\var1_lab1.txt" --username "dimon" "template1"

C:\PostgreSQL\bin>psql --username "dimon" "var1"

var1=# \d+

//Добавление ограничений и их проверка:

var1=> alter table people add constraint hc check (height > 150 and height < 220);

ALTER TABLE

var1=> \d+ people

var1=# select * from people;

var1=> insert into people values(6,'petya','pupkin','01/01/2001',140,100);

ERROR:  new row for relation "people" violates check constraint "hc"

var1=> update people set height=200 where id=4;

UPDATE 1

var1=> select * from people;

//В транзакционном блоке изменяем одну из записей:

var1=> begin;

BEGIN

var1=> update people set weight=100 where id=4;

UPDATE 1

var1=> commit;

COMMIT

//Тем временем другой пользователь не видит никаких изменений в базе, и замечает //их лишь после команды COMMIT:

//Пользователь postgres создает новую запись в таблице people

var1=# insert into people values(6,'petya','kolbasin','12/12/2012',170,70);

INSERT 0 1

//Тем временем пользователь dimon в транзакционном блоке изменяет только что //добавленную запись

var1=> begin;

BEGIN

var1=> update people set weight=150 where id=6;

UPDATE 1

//Ничего не подозревающий postgres решает удалить несчастную запись, но запись //заблокирована незавершенной транзакцией пользователя dimon и поэтому //postgres вынужден ждать завершения транзакции

var1=# delete from people where id=6

//Пользователь dimon завершает транзакцию и запись удаляется

var1=> commit;

COMMIT

//В транзакционном блоке изменяем запись и видим изменения в таблице

var1=> begin;

BEGIN

var1=> update people set height=190 where id=4;

UPDATE 1

var1=> select * from people where id=4;

//Делаем откат и видим первоначальное состояние таблицы

var1=> rollback;

ROLLBACK

var1=> select * from people where id=4;

//Создание производных таблиц

var1=> create table people2(iq int) inherits(people);

CREATE TABLE

var1=> insert into people2 values(6,'petya','pukin','01/01/2010',180,90,150);

INSERT 0 1

var1=> select * from only people;

var1=> select * from people2;

var1=> select * from people;

var1=> create table people3(klichka char(6)) inherits (people2);

CREATE TABLE

var1=> \d+ people3

var1=> insert into people3 values(7,'seva','mokin','02/02/2002',151,50,100,'sevka');

INSERT 0 1

//Сохранениебазыданныхвфайл

C:\PostgreSQL\bin>pg_dump --file "D:\Desktop\var1_lab2.txt" --no-owner --inserts --create --username "dimon" var1

//Удалениебазы var1

C:\PostgreSQL\bin>psql --username "dimon" "template1"

template1=> drop database var1;

DROP DATABASE

template1=> \q

//Восстановление базы из текстового файла

C:\PostgreSQL\bin>psql --file "D:\Desktop\var1_lab2.txt" --username "dimon" "template1"

C:\PostgreSQL\bin>psql --username "dimon" "var1"

var1=> \d+

//Добавление таблицы стран и опыта игроков

var1=# create table country(id int, name varchar(10));

CREATE TABLE

var1=# insert into country values(1,'russia');

INSERT 0 1

var1=# insert into country values(2,'usa');

INSERT 0 1

var1=# insert into country values(3,'georgia');

INSERT 0 1

var1=# insert into country values(4,'francia');

INSERT 0 1

var1=# insert into country values(5,'germany');

INSERT 0 1

var1=# update people set countryid=1 where id=1;

UPDATE 1

var1=# update people set countryid=2 where id=2;

UPDATE 1

var1=# update people set countryid=3 where id=3;

UPDATE 1

var1=# update people set countryid=4 where id=4;

UPDATE 1

var1=# update people set countryid=5 where id=5;

UPDATE 1

var1=# update people set countryid=1 where id=6;

UPDATE 1

var1=# update people set countryid=2 where id=7;

UPDATE 1

var1=# create table exp(id int, name char(10));

CREATE TABLE

var1=# insert into exp values(1,'master');

INSERT 0 1

var1=# insert into exp values(2,'candidate');

INSERT 0 1

var1=# insert into exp values(3,'beginer');

INSERT 0 1

Выводы

В результате выполнения лабораторной работы я изучил некоторые возможности СУБД PostgreSQL 9.0, в частности: освоил команды создания пользователей, введения ограничений в таблицы и поля таблиц; изучил модель наследования, транзакции; изучил работу с утилитой архивации и восстановления базы данных. На практике ощутил ценность и мощь современных СУБД.