Разработка схемы базы данных

БД представляет собой ряд таблиц связанных между собой.

Самой верхней таблицей является список городов (City), т.к. город не включается ни в один из списков.

Структура этой таблицы:

Id (integer NOT NULL DEFAULT nextval('city_id_seq'::regclass)) – уникальный код записи;

Name (character (15) NOT NULL DEFAULT '') – название города.

Таблица имеет первичный индекс по уникальному полю id

CONSTRAINT pk_city PRIMARY KEY (id).

 

Следующей таблицей является список зоопарков (Zoo). Каждый зоопарк не может существовать просто так и должен принадлежать какому-то только одному городу.

Структура:

Id (integer NOT NULL DEFAULT nextval('zoo_id_seq'::regclass)) – уникальный код записи;

Name (character (20) NOT NULL DEFAULT '') – название зоопарка;

Id_city (integer NOT NULL DEFAULT 0) – код города, которому принадлежит зоопарк. Соответствует полю City.id.

Таблица имеет первичный индекс по уникальному полю id

CONSTRAINT pk_zoo PRIMARY KEY (id).

А также внешний ключ для связи с таблицей City и для обеспечения целостности БД

CONSTRAINT fk_city FOREIGN KEY (id_city)

REFERENCES public."City" (id) MATCH SIMPLE

ON UPDATE CASCADE ON DELETE CASCADE

 

Следующей таблицей является список работников (Worker). Каждый работник (теоретически) может работать только в одном зоопарке и оказывать только одни услуги.

Структура:

Id (integer NOT NULL DEFAULT nextval('worker_id_seq'::regclass)) – уникальный код записи;

Name (character (30) NOT NULL DEFAULT '') – фамилия, имя и отчество работника;

Id_zoo (integer NOT NULL DEFAULT 0) – код зоопарка, в котором работает работник. Соответствует полю Zoo.id;

Id_servis (integer DEFAULT 0) – код услуги, которую оказывает работник. Соответствует полю Servis.id. В этом поле я оставил возможность принимать полем значение NULL, чтобы при удалении услуги не возникало ошибок в целостности БД.

Таблица имеет первичный индекс по уникальному полю id

CONSTRAINT pk_worker PRIMARY KEY (id).

А также внешние ключи для связи с таблицами Servis и Zoo и для обеспечения целостности БД

CONSTRAINT fk_servis FOREIGN KEY (id_servis)

REFERENCES public."Servis" (id) MATCH SIMPLE

ON UPDATE CASCADE ON DELETE SET NULL,

CONSTRAINT fk_zoo FOREIGN KEY (id_zoo)

REFERENCES public."Zoo" (id) MATCH SIMPLE

ON UPDATE CASCADE ON DELETE CASCADE

 

Каждый работник может обслуживать несколько животных и каждое животное (имеется в виду не особь животного, а его название. Например, «жираф». Их в зоопарке может быть несколько и они могут обслуживаться разными работниками) может принадлежать нескольким работникам. Поэтому для установления соответствий между работниками и животными существует отдельная таблица AnimToWork.

Ее структура:

Id (integer NOT NULL DEFAULT nextval('"AnimToWork_id_seq"'::regclass)) – уникальный код записи;

Id_worker (integer NOT NULL DEFAULT 0) – код работника, который обслуживает животное. Соответствует полю Worker.id;

Id_animal (integer NOT NULL DEFAULT 0) – код животного. Соответствует полю Animals.id.

Таблица имеет первичный индекс по уникальному полю id

CONSTRAINT pk_AnimToWork PRIMARY KEY (id).

А также внешние ключи для связи с таблицами Animals и Worker и для обеспечения целостности БД

CONSTRAINT fk_anim FOREIGN KEY (id_animal)

REFERENCES public."Animals" (id) MATCH SIMPLE

ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT fk_work FOREIGN KEY (id_worker)

REFERENCES public."Worker" (id) MATCH SIMPLE

ON UPDATE CASCADE ON DELETE CASCADE

 

Есть еще две таблицы, которые выполняют только справочную функцию, т.е. содержат списки, на которые ссылаются другие таблицы.

 

Servis – таблица содержащая список услуг.

Структура:

Id (integer NOT NULL DEFAULT nextval('usluga_id_seq'::regclass)) – уникальный код записи;

Name (character (15) NOT NULL DEFAULT '') – название услуги.

Таблица имеет первичный индекс по уникальному полю id

CONSTRAINT pk_servis PRIMARY KEY (id).

 

Animals – таблица содержащая список названий животных.

Структура:

Id (integer NOT NULL DEFAULT nextval('animals_id_seq'::regclass)) – уникальный код записи;

Name (character (15) NOT NULL DEFAULT '') – название животного.

Таблица имеет первичный индекс по уникальному полю id

CONSTRAINT pk_animals PRIMARY KEY (id).

Разработка базовых запросов

Для работы с таблицами базы данных разработано ряд функций, которые делятся на те, которые:

- добавляют записи (add_). Возвращает id новой записи;

- читают таблицы (read_). Возвращает результат запроса в виде таблицы;

- изменяют запись в таблице (upd_). Возвращает id изменной записи;

- удаляет запись из таблицы (del_record). Возвращает id удаленной записи.

 

Функция add_animals – добавляет новую запись в таблицу Animals. Входящий параметр – название животного. Возвращает id новой записи или 0, если добавление не получилось. Функция проверяет, есть ли в таблице такое название и если нету, то только тогда выполняется добавление новой записи.

 

CREATE OR REPLACE FUNCTION add_animals(new_name varchar)

RETURNS integer AS

$BODY$

DECLARE

ret_id integer;

BEGIN

ret_id=0;

IF (SELECT COUNT(*)

FROM "Animals" WHERE UPPER(TRIM(name))=UPPER(TRIM(new_name)))=0

THEN

INSERT INTO "Animals" (name) VALUES (new_name);

ret_id = (SELECT MAX(id) FROM "Animals");

END IF;

RETURN ret_id;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE COST 100;

 

Функция add_AnimToWork – добавляет новую запись в таблицу AnimToWork. Входящие параметры – id зоопарка, id животного. Возвращает id рабочего, которому было добавлено новое животное. Функция ищет рабочего, у которого животных наименьшее количество и тогда ему (рабочему) добавляется данное животное.

 

CREATE OR REPLACE FUNCTION public.add_AnimToWork(idZoo integer, idAnimal integer)

RETURNS integer AS

$BODY$

DECLARE

idWorker integer;

BEGIN

idWorker = (SELECT id FROM (SELECT "Worker".id, COUNT("AnimToWork".*) AS kol

FROM "Worker" LEFT JOIN "AnimToWork" ON "AnimToWork".id_worker="Worker".id

WHERE "Worker".id_zoo=idZoo

GROUP BY "Worker".id

ORDER BY kol

LIMIT 1) AS aaa);

INSERT INTO "AnimToWork" (id_worker, id_animal) VALUES (idWorker, idAnimal);

RETURN idWorker;

END;

$BODY$

LANGUAGE plpgsql VOLATILE COST 100;

 

Функция add_city – добавляет новую запись в таблицу City. Входящий параметр – название города. Возвращает id новой записи или 0, если добавление не получилось. Функция проверяет, есть ли в таблице такое название и если нету, то только тогда выполняется добавление новой записи.

 

CREATE OR REPLACE FUNCTION add_city(new_name varchar)

RETURNS integer AS

$BODY$

DECLARE

ret_id integer;

BEGIN

ret_id=0;

IF (SELECT COUNT(*) FROM "City"

WHERE UPPER(TRIM(name))=UPPER(TRIM(new_name)))=0

THEN

INSERT INTO "City" (name) VALUES (new_name);

ret_id = (SELECT MAX(id) FROM "City");

END IF;

RETURN ret_id;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE COST 100;

 

Функция add_servis – добавляет новую запись в таблицу Servis. Входящий параметр – название услуги. Возвращает id новой записи или 0, если добавление не получилось. Функция проверяет, есть ли в таблице такое название и если нету, то только тогда выполняется добавление новой записи.

 

CREATE OR REPLACE FUNCTION add_servis(new_name varchar)

RETURNS integer AS

$BODY$

DECLARE

ret_id integer;

BEGIN

ret_id=0;

IF (SELECT COUNT(*) FROM "Servis"

WHERE UPPER(TRIM(name))=UPPER(TRIM(new_name)))=0

THEN

INSERT INTO "Servis" (name) VALUES (new_name);

ret_id = (SELECT MAX(id) FROM "Servis");

END IF;

RETURN ret_id;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE COST 100;

 

Функция add_worker – добавляет новую запись в таблицу Worker. Входящие параметры – фамилия, имя и отчество нового рабочего, id зоопарка, в который добавляется рабочий, id услуги, которую выполняет рабочий. Возвращает id новой записи или 0, если добавление не получилось. Функция проверяет, есть ли в таблице такие фамилия, имя и отчество и если нету, то только тогда выполняется добавление новой записи.

 

CREATE OR REPLACE FUNCTION add_worker(new_name varchar, idZoo integer, idServis integer)

RETURNS integer AS

$BODY$

DECLARE

ret_id integer;

BEGIN

ret_id=0;

IF (SELECT COUNT(*) FROM "Worker"

WHERE UPPER(TRIM(name))=UPPER(TRIM(new_name))

AND id_zoo=idZoo)=0

THEN

INSERT INTO "Worker" (name, id_zoo, id_servis)

VALUES (new_name, idZoo, idServis);

ret_id = (SELECT MAX(id) FROM "Worker");

END IF;

RETURN ret_id;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE COST 100;

 

Функция add_zoo – добавляет новую запись в таблицу Zoo. Входящие параметры – название зоопарка, id города, в который добавляется зоопарк. Возвращает id новой записи или 0, если добавление не получилось. Функция проверяет, есть ли в таблице такое название и если нету, то только тогда выполняется добавление новой записи.

 

CREATE OR REPLACE FUNCTION add_zoo(new_name varchar, idCity integer)

RETURNS integer AS

$BODY$

DECLARE

ret_id integer;

BEGIN

ret_id=0;

IF (SELECT COUNT(*) FROM "Zoo"

WHERE UPPER(TRIM(name))=UPPER(TRIM(new_name))

AND id_city=idCity)=0

THEN

INSERT INTO "Zoo" (name, id_city) VALUES (new_name, idCity);

ret_id = (SELECT MAX(id) FROM "Zoo");

END IF;

RETURN ret_id;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE COST 100;

 

Функция del_record – удаляет запись из таблицы. Входящие параметры – название таблицы, из которой нужно удалить запись, id записи, которую необходимо удалить. Возвращает id удаленной записи или 0, если удаление не получилось. Функция проверяет, есть ли в таблице такая запись и если есть, то только тогда выполняется удаление.

 

CREATE OR REPLACE FUNCTION del_record(tab_name varchar, old_id integer)

RETURNS integer AS

$BODY$

DECLARE

ret_id integer;

BEGIN

ret_id=0;

IF (tab_name='Animals') AND (SELECT COUNT(*) FROM "Animals" WHERE id=old_id)>0

THEN

DELETE FROM "Animals" WHERE id=old_id;

ret_id = old_id;

END IF;

 

IF tab_name='City' AND (SELECT COUNT(*) FROM "City" WHERE id=old_id)>0

THEN

DELETE FROM "City" WHERE id=old_id;

ret_id = old_id;

END IF;

 

IF tab_name='Servis' AND (SELECT COUNT(*) FROM "Servis" WHERE id=old_id)>0

THEN

DELETE FROM "Servis" WHERE id=old_id;

ret_id = old_id;

END IF;

 

IF tab_name='Worker' AND (SELECT COUNT(*) FROM "Worker" WHERE id=old_id)>0

THEN

DELETE FROM "Worker" WHERE id=old_id;

ret_id = old_id;

END IF;

 

IF tab_name='Zoo' AND (SELECT COUNT(*) FROM "Zoo" WHERE id=old_id)>0

THEN

DELETE FROM "Zoo" WHERE id=old_id;

ret_id = old_id;

END IF;

 

IF tab_name='AnimToWork' AND (SELECT COUNT(*) FROM "AnimToWork" WHERE id=old_id)>0

THEN

DELETE FROM "AnimToWork" WHERE id=old_id;

ret_id = old_id;

END IF;

 

RETURN ret_id;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE COST 100;

 

Функция read_animals – возвращает таблицу Animals. Входящие параметры – нету.

 

CREATE OR REPLACE FUNCTION public.read_animals()

RETURNS TABLE(id integer, name character) AS

$BODY$

DECLARE

rec RECORD;

BEGIN

FOR rec IN EXECUTE 'select * from "Animals"'

LOOP

id = rec.id;

name = rec.name;

RETURN next;

END LOOP;

END;

$BODY$

LANGUAGE plpgsql VOLATILE COST 100

 

Функция read_animtowork – возвращает таблицу животных, которые обслуживаются конкретным рабочим. Входящий параметр – id рабочего, чьи животные нужно получить.

 

CREATE OR REPLACE FUNCTION public.read_animtowork(IN idWorker integer)

RETURNS TABLE(id integer, name character) AS

$BODY$

DECLARE

rec RECORD;

cur_work CURSOR FOR

SELECT "AnimToWork".id,"Animals".name

FROM "AnimToWork", "Animals"

WHERE "AnimToWork".id_worker=idWorker

AND "AnimToWork".id_animal="Animals".id;

BEGIN

FOR rec IN cur_work

LOOP

id = rec.id;

name = rec.name;

RETURN next;

END LOOP;

END;

$BODY$

LANGUAGE plpgsql VOLATILE COST 100;

 

Функция read_city – возвращает таблицу City. Входящие параметры – нету.

 

CREATE OR REPLACE FUNCTION read_city()

RETURNS table(id integer, name character(15)) AS

$BODY$

DECLARE

rec RECORD;

BEGIN

FOR rec IN EXECUTE 'select * from "City"'

LOOP

id = rec.id;

name = rec.name;

RETURN next;

END LOOP;

END;

$BODY$

LANGUAGE plpgsql VOLATILE COST 100;

 

Функция read_servis – возвращает таблицу Servis. Входящие параметры – нету.

 

CREATE OR REPLACE FUNCTION read_servis()

RETURNS table(id integer, name character(15)) AS

$BODY$

DECLARE

rec RECORD;

BEGIN

FOR rec IN EXECUTE 'select * from "Servis"'

LOOP

id = rec.id;

name = rec.name;

RETURN next;

END LOOP;

END;

$BODY$

LANGUAGE plpgsql VOLATILE COST 100;

 

Функция read_worker – возвращает таблицу рабочих, которые работают в конкретном зоопарке. Входящий параметр – id зоопарка, чьи рабочие нужно получить.

 

CREATE OR REPLACE FUNCTION read_worker(idZoo integer)

RETURNS table(id integer, name character(30), id_servis integer) AS

$BODY$

DECLARE

rec RECORD;

cur_work CURSOR FOR select * from "Worker" where id_zoo=idZoo;

BEGIN

FOR rec IN cur_work

LOOP

id = rec.id;

name = rec.name;

id_servis = rec.id_servis;

RETURN next;

END LOOP;

END;

$BODY$

LANGUAGE plpgsql VOLATILE COST 100;

 

Функция read_zoo – возвращает таблицу зоопарков, которые находятся в конкретном городе. Входящий параметр – id города, чьи зоопарки нужно получить.

 

CREATE OR REPLACE FUNCTION read_zoo(idCity integer)

RETURNS table(id integer, name character(20)) AS

$BODY$

DECLARE

rec RECORD;

cur_zoo CURSOR FOR select * from "Zoo" where id_city=idCity;

BEGIN

FOR rec IN cur_zoo

LOOP

id = rec.id;

name = rec.name;

RETURN next;

END LOOP;

END;

$BODY$

LANGUAGE plpgsql VOLATILE COST 100;

 

Функция upd_animals – изменяет название животного. Входящие параметры – id животного, новое название животного. Возвращает id животного или 0, если замена не удалась.

 

CREATE OR REPLACE FUNCTION upd_animals(old_id integer, new_name varchar)

RETURNS integer AS

$BODY$

DECLARE

ret_id integer;

BEGIN

ret_id=0;

IF (SELECT COUNT(*) FROM "Animals"

WHERE UPPER(TRIM(name))=UPPER(TRIM(new_name)))=0

THEN

UPDATE "Animals" SET name=new_name WHERE id=old_id;

ret_id = old_id;

END IF;

RETURN ret_id;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE COST 100;

 

Функция upd_AnimToWork – изменяет животного, которое обслуживается у конкретного рабочего. Входящие параметры – id записи, новое id животного. Возвращает id измененной записи.

 

CREATE OR REPLACE FUNCTION public.upd_AnimToWork(old_id integer, idAnimal integer)

RETURNS integer AS

$BODY$

BEGIN

UPDATE "AnimToWork" SET id_animal=idAnimal WHERE id=old_id;

RETURN old_id;

END;

$BODY$

LANGUAGE plpgsql VOLATILE COST 100;

 

Функция upd_city – изменяет название города. Входящие параметры – id города, новое название города. Возвращает id города или 0, если замена не удалась.

 

CREATE OR REPLACE FUNCTION upd_city(old_id integer, new_name varchar)

RETURNS integer AS

$BODY$

DECLARE

ret_id integer;

BEGIN

ret_id=0;

IF (SELECT COUNT(*) FROM "City" WHERE UPPER(TRIM(name))=UPPER(TRIM(new_name)))=0

THEN

UPDATE "City" SET name=new_name WHERE id=old_id;

ret_id = old_id;

END IF;

RETURN ret_id;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE COST 100;

 

Функция upd_servis – изменяет название услуги. Входящие параметры – id услуги, новое название услуги. Возвращает id услуги или 0, если замена не удалась.

 

CREATE OR REPLACE FUNCTION upd_servis(old_id integer, new_name varchar)

RETURNS integer AS

$BODY$

DECLARE

ret_id integer;

BEGIN

ret_id=0;

IF (SELECT COUNT(*) FROM "Servis"

WHERE UPPER(TRIM(name))=UPPER(TRIM(new_name)))=0

THEN

UPDATE "Servis" SET name=new_name WHERE id=old_id;

ret_id = old_id;

END IF;

RETURN ret_id;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE COST 100;

 

Функция upd_worker – изменяет фамилию, имя и отчество рабочего, а также услугу, которую этот рабочий предоставляет. Входящие параметры – id рабочего, новые фамилия, имя и отчество, id услуги. Возвращает id рабочего или 0, если замена не удалась.

 

CREATE OR REPLACE FUNCTION public.upd_worker(old_id integer, new_name varchar, idZoo integer, idServis integer)

RETURNS integer AS

$BODY$

DECLARE

ret_id integer;

BEGIN

ret_id=0;

IF (SELECT COUNT(*) FROM "Worker"

WHERE UPPER(TRIM(name))=UPPER(TRIM(new_name))

AND id_zoo=idZoo AND id_servis=idServis)=0

THEN

UPDATE "Worker" SET name=new_name, id_servis=idServis WHERE id=old_id;

ret_id = old_id;

END IF;

RETURN ret_id;

END;

$BODY$

LANGUAGE plpgsql VOLATILE COST 100;

 

Функция upd_zoo – изменяет название зоопарка. Входящие параметры – id зоопарка, новое название зоопарка. Возвращает id зоопарка или 0, если замена не удалась.

 

CREATE OR REPLACE FUNCTION upd_zoo(old_id integer, new_name varchar, idCity integer)

RETURNS integer AS

$BODY$

DECLARE

ret_id integer;

BEGIN

ret_id=0;

IF (SELECT COUNT(*) FROM "Zoo"

WHERE UPPER(TRIM(name))=UPPER(TRIM(new_name))

AND id_city=idCity)=0

THEN

UPDATE "Zoo" SET name=new_name WHERE id=old_id;

ret_id = old_id;

END IF;

RETURN ret_id;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE COST 100;

 

Функция Top10 выбирает 10 работников со всей Украины, у которых наибольшее количество животных. Входящие параметры – нет. Возвращает таблицу 10-ти работников, с указанием фамилии, имени и отчества рабочего, названия зоопарка, где он работает, названия города, где находится зоопарк и количеством животных на обслуживании рабочего. Таблица сортируется по количеству животных в порядке убывания.

 

CREATE OR REPLACE FUNCTION public.Top10()

RETURNS TABLE(id integer, workname character, zooname character,

cityname character, kol integer) AS

$BODY$

DECLARE

rec RECORD;

cur_work CURSOR FOR

SELECT "Worker".id, "Worker".name AS workname,

"Zoo".name AS zooname, "City".name AS cityname,

COUNT("AnimToWork".*) AS kol

FROM "Worker", "Zoo", "City", "AnimToWork"

WHERE "Worker".id_zoo="Zoo".id AND "Zoo".id_city="City".id

AND "AnimToWork".id_worker="Worker".id

GROUP BY "Worker".id, "Zoo".name, "City".name

ORDER BY kol DESC LIMIT 10;

BEGIN

FOR rec IN cur_work

LOOP

id = rec.id;

workname = rec.workname;

zooname = rec.zooname;

cityname = rec.cityname;

kol = rec.kol;

RETURN next;

END LOOP;

END;

$BODY$

LANGUAGE plpgsql VOLATILE COST 100

 

 

Разработка модулей системы

База данных организована таким образом, что все изменения – добавления, изменения, удаления выполняются функциями сервера. Эти функции возвращают приложению-клиенту только код выполненной операции. Если код равен нулю, то операция не удалась, если другое число, то оно означает id записи, над которой было выполнено действие. Отдельной группой на сервере стоят функции, которые читают таблицу или ее часть, или результирующую таблицу состоящую из полей разных таблиц, т.е. результат выборки (SELECT… FROM… WHERE…) и передают в приложение-клиент таблицу.

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

В данной реализации приложение-клиент выполнено в среде Microsoft Visual FoxPro 9.

Для того, чтобы VFP мог работать с сервером базы данных PostgreSQL необходимо установить драйвер ODBC для PostgreSQL.

 

Выводы

В результате выполнения работы я получил полностью функциональную систему. На сервере PostgreSQL с нуля была создана база данных – созданы таблицы, установлены связи между ними и обеспечена целостность. Были написаны, испытаны, опробованы и отлажены функции по отображению данных, а также по изменению (добавлению, исправлению и удалению) данных.

Из среды Visual FoxPro была установлена связь с базой данных PostgreSQL. Были отлажены классы для работы с данными и написан понятный и дружественный интерфейс для пользователя.

Когда программа уже написана, вижу, что ее можно было бы усовершенствовать несколько изменив всю структуру. Так, в данной реализации каждое животное имеет только одного рабочего, который его обслуживает. Логичнее было бы каждому животному назначить по нескольку рабочих с разными видами услуг – кто-то кормит, кто-то чистит, а кто-то расчесывает. Но это уже не важно.

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