л.р.5 - Разработка баз данных средствами SQL-сервера Interbase. Команды языка SQL для описания данных.
========= Отчет лаб5 ===============
В этой работе нет Делфи-проекта. Создается база данных Interbase.
Описан процесс создания базы данных, доменов, таблиц, генераторов,
триггеров, хранимых процедур, заполнения таблиц.
( БД из лаб2-4 создана другими средствами). Делфи-проект, работающий
с этой БД рассмотрен в лаб6.
-----------------------------
Задание:
1. Выполнить анализ схемы данных разработанной ранее БД для исключения
возможности возникновения аномалий модификации данных.
******* это не выполнялось
2. С целью доказательства нахождения разработанной схемы в 3НФ
(или НФБК), для каждого отношения (таблицы) определить функциональные
зависимости. При выявлении аномалий модели данных выполнить ее
нормализацию, следуя алгоритму нормализации.
******* это не выполнялось
3. Создать БД на сервере InterBase и зарегистрировать ее с использованием
приложения IBConsole.
\Program Files\Borland\InterBase\bin\IBConsole.exe
- Зарегистрирован админ:
Регистрация сервера осуществляется в диалоговом окне, которое
открывается командой Server – Register, в котором необходимо указать имя
администратора БД (User Name) - SYSDBA и его пароль (Password) – masterkey.
При регистрации локального сервера в открывшемся окне должна быть включена
радиокнопка Local Server.
( Не описано в методичке:
Как открыть сервер: можно дв щелчком по ярлыку "Local Server",
ввести пароль. Красный крест заменится зеленой птичкой. Ингда открывается
долго)
- Регистрируем фирменную демо-БД: Меню\DataBase\Register
Здесь в окне DataBase настроить БД на адрес
Program Files\Common Files\Borland Shared\Data\EMPLOYEE.GDB
- как сказано в методичке.
При регистрации БД
можно ввести Login Information.
Ввел (и для своей БД то же)
UserName: WINNI
Password: krolik
Role:
Default Character Set: WIN1251
--- Однако эти UserName, Password не используются. Нужно вводить
SYSDBA masterkey -------------
После этого в окне появляется эта БД и можно смотреть ее детали:
таблицы, индексы и т д.
- Полезно поизучать эту демо-бд (см scr1.gif-scr3.gif)
- Все это работает и ДО регистрации сервера InterBase с использованием
приложения SQLExplorer. SQLExplorer удобен для просмотра структуры БД,
ввода SQL запросов (см scr5,6,7)
4. Создать алиас БД сервера InterBase с использованием приложения
SQLExplorer.
- открываю SQLExplorer (из группы Делфи или \Program Files\Borland\Delphi7\Bin\dbexplor.exe)
- Создаю алиас БД сервера InterBase:
- Меню | Object | New
- в окошке New DataBase alias выбираю INTRBASE, щелкаю ОК
- в правом окошке, закладка Definition устанавливаю Server Name
т е путь к БД:
\Program Files\Borland\InterBase\examples\Database\EMPLOYEE.GDB
(например. Там есть еще одна БД) или
\Program Files\Common Files\Borland Shared\Data\EMPLOYEE.GDB
Можно тут же установить
LANGDRIVER = Pdox ANSI Cirillic
USER NAME = SYSDBA
(см scr4.gif)
- Сделать правый щелчок на значке создаваемого алиаса INTRBASE1
и выбрать Apply (применть), щелк ОК.
(перед этим можно INTRBASE1 переименовать во что угодно)
- после этого созданный алиас (псевдоним) будет появляться в окошках
доступных алиасов в тех программах, где есть такое окошко
(список создается компонентом TSession, например:
ses1.GetAliasNames(cbAliases.Items); )
Однако в этих лаб. работах этот алас не используется, т е
как бы непонятно, зачем его создавать.
5. Познакомиться с возможностями приложений IBConsole и SQLExplorer при
создании и выполнении команд SQL в интерактивном режиме.
- Открываю IBConsole
- дв щелчок на Local Server, ввожу пароль. Выделяю EMPLOYEE.GDB
- DataBase\Connect (или дв щелч на значке этой БД)
- Для работы с SQL: Меню\Tools\Interactive SQL
---------------------
Примеры SQL:
- в списке таблиц вижу EMPLOYEE
для ее просмотра ввожу SQL: SELECT * FROM EMPLOYEE
(в верхнее окно Interactive SQL). Щелкаю кнопку Execute Query
(молния со знаком ?)
В нижнем окне появляется таблица-результат.
- усложняю запрос сортировкой:
SELECT * FROM EMPLOYEE
ORDER BY FIRST_NAME
** выполнено нормально
- еще:
SELECT * FROM EMPLOYEE
WHERE JOB_COUNTRY = 'Italy' OR
JOB_COUNTRY = 'USA'
ORDER BY FIRST_NAME
** выполнено нормально
т е выполняются любые обычные SQL-запросы.
--------------------------
6. С использованием команд SQL определения данных создать следующие
объекты БД: ....
Создание новой БД обеспечивается командой Database – Create Database.
После ее выполнения открывается диалоговое окно, в котором в панели File(s)
надо записать имя файла создаваемой БД с полным путем доступа к нему.
- создаю пустую папку, например:
E:\XRAN_RW_MyProg\Apteka\IB_kassa6_2
- Открываю IBConsole, ввожу пароль masterkey
- IBConsole | Database | Create Database
- вписываю E:\XRAN_RW_MyProg\Apteka\IB_kassa6_2\kas6_2.gdb
(т е создаю новую, другую БД). Size = 250
Alias= IB_kassa6_2.
- получается пустая БД (однако файл KAS6_2.GDB имеет размер 590 Кб)
- теперь в верхнее окно Interactive SQL можно вводить запросы
по созданию объектов: доменов, таблиц и т д. После вписания запроса
Щелкаю кнопку Execute Query (молния со знаком ?)
Интересно, что можно в окно поместить несколько запросов (в конце
запроса ;) и выполнить сразу все.
• домены для тех типов данных и ограничений, которые повторяются при
определении нескольких полей (определяются на основании анализа типов
полей всех таблиц БД).
- вначале создаются домены (хотя можно без них)
тексты запросов можно видеть (и копировать) в БД kas6.gdb и
EMPLOYEE.GDB. (правый щелчок по домену, выбрать extract)
Получим, например:
/* Domain definitions */
CREATE DOMAIN "CHAR10" AS VARCHAR(10) CHARACTER SET WIN1251;
/* Domain definitions */
CREATE DOMAIN "CHAR30" AS VARCHAR(30) CHARACTER SET WIN1251;
Введем сразу эти 2 заапроса, как было описано выше, в БД
kas6_2.gdb. (Выделим ее в окошке, Tools | Interactive SQL)
видим (scr8.gif)
Вводим остальные домены
/* Domain definitions */
CREATE DOMAIN "CHAR100" AS VARCHAR(100) CHARACTER SET WIN1251;
/* Domain definitions */
CREATE DOMAIN "CHAR12" AS VARCHAR(12) CHARACTER SET WIN1251;
/* Domain definitions */
CREATE DOMAIN "CHAR20" AS VARCHAR(20) CHARACTER SET WIN1251;
/* Domain definitions */
CREATE DOMAIN "CHAR50" AS VARCHAR(50) CHARACTER SET WIN1251;
/* Domain definitions */
CREATE DOMAIN "COUNT1" AS SMALLINT
CHECK (VALUE >= 0);
/* Domain definitions */
CREATE DOMAIN "ID1" AS SMALLINT;
/* Domain definitions */
CREATE DOMAIN "MONEY" AS NUMERIC(8, 2)
DEFAULT 0
CHECK (VALUE > 0);
----------------------------
• таблицы (с обязательным определением первичных и внешних ключей,
ссылочной целостности связанных таблиц, с вычисляемыми полями и
ограничениями на ввод допустимых значений в отдельные поля);
- таблицы создаются запросами вида:
CREATE TABLE AP_USERS
(
ID_USERS ID1 NOT NULL,
NAME CHAR100 NOT NULL,
EMAIL CHAR30 NOT NULL UNIQUE,
PASS CHAR20 NOT NULL,
CONTACTFIO CHAR30 NOT NULL,
ID_COUNTRY ID1 NOT NULL,
PINDEX CHAR10 NOT NULL,
CITY CHAR20 NOT NULL,
PADDRESS CHAR50 NOT NULL,
PHONE CHAR12,
FAX CHAR12,
ETC CHAR50,
PRIMARY KEY (ID_USERS)
);
ALTER TABLE AP_USERS ADD FOREIGN KEY (ID_COUNTRY) REFERENCES COUNTRIES
(ID_COUNTRY);
Однако,
ALTER TABLE AP_USERS ADD FOREIGN KEY (ID_COUNTRY) REFERENCES COUNTRIES
(ID_COUNTRY); не вводится, т к нет еще табл COUNTRIES, т е лучше начать
с COUNTRIES:
CREATE TABLE "COUNTRIES"
(
"ID_COUNTRY" "ID1" NOT NULL,
"COUNTRY" "CHAR20" NOT NULL,
CONSTRAINT "CONS2" UNIQUE ("COUNTRY"),
PRIMARY KEY ("ID_COUNTRY")
);
а теперь повторно ввести
ALTER TABLE AP_USERS ADD FOREIGN KEY (ID_COUNTRY) REFERENCES COUNTRIES
(ID_COUNTRY); - нормально. Т е начинать лучше с независимых таблиц.
--------
CREATE TABLE KATEGORII
(
ID_KATEGOR ID1 NOT NULL,
KATEGOR CHAR20 NOT NULL,
PRIMARY KEY (ID_KATEGOR)
);
/* Table: LEK_LIST, Owner: SYSDBA (Winni - krolik) */
CREATE TABLE LEK_LIST
(
LEK_ID ID1 NOT NULL,
ID_KATEGOR ID1 NOT NULL,
NAZVZAN1 CHAR30,
NAZVZAN2 CHAR30,
NAZVZAN3 CHAR50,
CENA MONEY NOT NULL,
PRIMARY KEY (LEK_ID)
);
ALTER TABLE LEK_LIST ADD FOREIGN KEY (ID_KATEGOR) REFERENCES KATEGORII
(ID_KATEGOR);
- эти 2 табл созданы за один щелчок по Execute Query(молния со знаком ?)
-------------
/* Table: SKLAD, Owner: SYSDBA (Winni - krolik) */
CREATE TABLE SKLAD
(
LEK_ID ID1 NOT NULL UNIQUE,
OSTATOK COUNT1,
STELLAJ CHAR20
);
ALTER TABLE SKLAD ADD FOREIGN KEY (LEK_ID) REFERENCES LEK_LIST (LEK_ID);
-----------------
• генераторы для ключевых полей таблиц, имеющих автоинкрементный
тип данных;
- CREATE GENERATOR "AP_USERS_ID";
CREATE GENERATOR "COUNTRIES_ID";
CREATE GENERATOR "KATEGORII_ID";
вводится в верхнее окно Interactive SQL.
Генераторы (объекты, похожие на переменные в программировании)
созданы.
• триггеры для работы генераторов;
-------
SET TERM ^ ;
/* Triggers only will work for SQL triggers */
CREATE TRIGGER "SET_ID_CNTR" FOR "COUNTRIES"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.ID_COUNTRY = gen_id(COUNTRIES_ID, 1);
END
^
SET TERM ;^
--------------
SET TERM ^ ;
CREATE TRIGGER "SET_ID_KATEGOR" FOR "KATEGORII"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.ID_KATEGOR = gen_id(KATEGORII_ID, 1);
END
^
SET TERM ;^
--------------
Эти запросы вводятся в то же окошко.
Тексты для образца и копирования находятся:
- правый щелчок по БД (KAS6.gdb), выбрать View MetaData.
• просмотры:
---------
SQL-запросы на создание просмотров.
CREATE VIEW SPISOK1 AS
SELECT NAZVZAN1, OSTATOK, CENA
FROM LEK_LIST, SKLAD
WHERE SKLAD.LEK_ID = LEK_LIST.LEK_ID;
CREATE VIEW SPISOK2 AS
SELECT KATEGOR, NAZVZAN1, OSTATOK, CENA
FROM KATEGORII, LEK_LIST, SKLAD
WHERE (LEK_LIST.ID_KATEGOR = KATEGORII.ID_KATEGOR) AND
(SKLAD.LEK_ID = LEK_LIST.LEK_ID);
------- Использование просмотра ---------
SELECT * FROM SPISOK2
ORDER BY KATEGOR, CENA;
---------
Чтобы это работало в лаб6, нужно или
- заменить файл БД: KAS6.gdb (прилагается)
- в приложении лаб6 (Apteka6.exe) щелкнуть кнопку
Ввод запросов и выполнить запрос:
SELECT * FROM SPISOK2
ORDER BY KATEGOR, CENA;
------
или выполнить запросы CREATE на создание
в IBConsole.exe => изменится KAS6.gdb а потом
- в приложении лаб6 (Apteka6.exe) щелкнуть кнопку
Ввод запросов и выполнить запрос:
SELECT * FROM SPISOK2
ORDER BY KATEGOR, CENA;
(см scr9.gif)
---------
• индексы;
- индексы создавались в запросах на создание таблиц,
например:
CREATE TABLE "AP_USERS"
(
"ID_USERS" "ID1" NOT NULL,
......,
UNIQUE ("EMAIL"),
PRIMARY KEY ("ID_USERS")
);
-------------
CREATE TABLE "COUNTRIES"
( ....
CONSTRAINT "CONS2" UNIQUE ("COUNTRY"),
PRIMARY KEY ("ID_COUNTRY")
);
----------
• хранимые процедуры. Например: ;
SET TERM ^ ;
CREATE PROCEDURE "DELETE_LEKAR"
(
"LEKID" INTEGER
)
AS
BEGIN
DELETE FROM LEK_LIST
WHERE (LEK_ID = :LEKID);
END
^
SET TERM ; ^
----------
Автоматика разбивает это на 2 запроса: CREATE и ALTER
(см в БД)
--------
SET TERM ^ ;
CREATE PROCEDURE "INSERT_LEKAR"
(
"LEKID" INTEGER,
"IDKATEGOR" INTEGER,
"N1" VARCHAR(30) CHARACTER SET WIN1251,
"N2" VARCHAR(30) CHARACTER SET WIN1251,
"N3" VARCHAR(40) CHARACTER SET WIN1251,
"CENA" NUMERIC(8, 2)
)
AS
BEGIN
INSERT INTO LEK_LIST
VALUES (:LEKID, :IDKATEGOR, :N1, :N2, :N3, :CENA);
END
^
SET TERM ; ^
----
В начале и в конце меняется ограничитель запроса, так как ;
используется внутри текста запроса.
--------
SET TERM !!;
CREATE PROCEDURE UPDATE_LEKAR
(LEKID INTEGER, IDKATEGOR INTEGER, N1 VARCHAR(30), N2 VARCHAR(30),
N3 VARCHAR(40), CENA NUMERIC (8,2))
AS
BEGIN
UPDATE LEK_LIST
SET
LEK_ID = :LEKID,
ID_KATEGOR = :IDKATEGOR,
NAZVZAN1 = :N1,
NAZVZAN2 = :N2,
NAZVZAN3 = :N3,
CENA = :CENA
WHERE LEK_ID = :LEKID;
END !!
SET TERM ; !!
• триггеры для реализации правил бизнес – логики.
-----------------------------
====== Бизнес-правила для связи lek_list <---> kategorii =====
A)lek_list:
+Before_Insert - поле Id_kategor вводить выбором из списка категорий.
Или проверять, что вводимое значение Id_kategor
есть в табл kategorii
After_Insert ---
------------
Before_Delete --
After_Delete --
------------
+Before_Update - поле Id_kategor вводить выбором из списка категорий.
Или проверять, что вводимое значение Id_kategor есть в табл kategorii
After_Update --
==============
B)kategorii:
Before_Insert ---
After_Insert ---
------------
Before_Delete
Проверить: если есть связанные записи в lek_list, выдать сообщение
и не удалять или
предложить удалить все лекарства этой категории (каскадно).
After_Delete ---
------------
Before_Update
Проверить: если есть связанные записи в lek_list, запретить
редактирование ключа Id_kategor. Или проверить - есть ли
новое значение в табл kategorii
After_Update ---
------------- триггеры, реализующие эти бизнес-правила, см далее ----
------- Добавлены исключения для триггеров: ---------
CREATE EXCEPTION no_del_kat 'Нельзя удалять - есть лекарства этой категории';
CREATE EXCEPTION no_ins_lek 'Нельзя добавить - нет такой категории';
CREATE EXCEPTION no_upd_kat 'Нельзя изменить - есть лекарства этой категории';
CREATE EXCEPTION no_upd_lek 'Нельзя изменить - нет такой категории';
========= ТРИГГЕРЫ: ==========
SET TERM !! ;
CREATE TRIGGER BEFORE_DELETE_KAT FOR KATEGORII
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE lek_count INTEGER ;
BEGIN
SELECT count(lek_id) from LEK_LIST
WHERE LEK_LIST.ID_KATEGOR = KATEGORII.ID_KATEGOR
INTO :lek_count ;
IF (:lek_count > 0) THEN EXCEPTION no_del_kat ;
END !!
SET TERM ; !!
------- эта версия работает -------
================
SET TERM !! ;
CREATE TRIGGER BEFORE_INSERT_LEK FOR LEK_LIST
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE kat_count INTEGER;
BEGIN
SELECT count(ID_KATEGOR) from KATEGORII
WHERE KATEGORII.ID_KATEGOR = new.ID_KATEGOR
INTO :kat_count;
IF (:kat_count < 1) THEN EXCEPTION no_ins_lek;
END !!
SET TERM ; !!
------- это ^^ работает -------
=================================
SET TERM !! ;
CREATE TRIGGER BEFORE_UPDATE_KAT FOR KATEGORII
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE lek_count INTEGER;
BEGIN
SELECT count(lek_id) from LEK_LIST
WHERE KATEGORII.ID_KATEGOR = LEK_LIST.ID_KATEGOR
INTO :lek_count;
IF (:lek_count > 0) THEN EXCEPTION no_upd_kat;
END !!
SET TERM ; !!
------- это ^^ не работает, т к ?? раньше срабатывает
ограничение FOREIGN KEY ("ID_KATEGOR") в табл LEK_LIST,
то есть это дублирование, поэтому убираю этот триггер.
----------------------------
SET TERM !! ;
CREATE TRIGGER BEFORE_UPDATE_LEK FOR LEK_LIST
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE kat_count INTEGER;
BEGIN
SELECT count(ID_KATEGOR) from KATEGORII
WHERE KATEGORII.ID_KATEGOR = new.ID_KATEGOR
INTO :kat_count;
IF (:kat_count < 1) THEN EXCEPTION no_upd_lek;
END !!
SET TERM ; !!
- по русски: нельзя при редактировании менять поле ID_KATEGOR
устанавливая новое значение, которого нет в табл KATEGORII
Это работает, но в IBConsole раньше срабатывает ошибка
перекодировки. Если же новые значения писать латин, тогда видна
работа этого триггера. В лаб6 тоже раньше срабатывает блок
try ... except и выдает свой текст, после которого прога
выводит текст системный от EXCEPTION no_upd_lek, т е работает
нормально. Но это в версии лаб 6 от 31.12.08
Версия лаб6 от 31.12.08 показывает сообщения исключений
от InterBase, описанные вначале.
------------------------------
7. Познакомиться с синтаксисом операторов модификации объектов базы
данных с помощью справочной системы интерактивного SQL.+
- познакомился
-------
8. Получить навыки работы при работе с операторами SQL модификации
объектов БД. +
9. Отработать в режиме интерактивного SQL операторы манипулирования
данными. +
При выполнении лабораторной работы в качестве примера для создания
всех объектов БД использовать контрольный пример базы данных, входящий
в поставку InterBase, хранящийся в файле:
Program Files\Common Files\Borland Shared\Data\EMPLOYEE.GDB
---
10. Заполняю таблицы данными (снова SQL запросы):
INSERT INTO KATEGORII (ID_KATEGOR, KATEGOR)
VALUES ( 4,'аллергия');
INSERT INTO KATEGORII (ID_KATEGOR, KATEGOR)
VALUES ( 5,'мать и дитя');
INSERT INTO KATEGORII (ID_KATEGOR, KATEGOR)
VALUES ( 6,'слабительные');
INSERT INTO KATEGORII (ID_KATEGOR, KATEGOR)
VALUES ( 7,'кандидоз');
INSERT INTO KATEGORII (ID_KATEGOR, KATEGOR)
VALUES ( 8,'лечебное питание');
INSERT INTO KATEGORII (ID_KATEGOR, KATEGOR)
VALUES ( 9,'антибиотики');
INSERT INTO KATEGORII (ID_KATEGOR, KATEGOR)
VALUES ( 10,'анальгетики');
INSERT INTO KATEGORII (ID_KATEGOR, KATEGOR)
VALUES ( 11,'тошнотворные (kas6)');
-----------
INSERT INTO COUNTRIES (ID_COUNTRY,COUNTRY)
VALUES ( 1,'Россия');
INSERT INTO COUNTRIES (ID_COUNTRY,COUNTRY)
VALUES ( 2,'Республика Беларусь');
INSERT INTO COUNTRIES (ID_COUNTRY,COUNTRY)
VALUES ( 3,'Франция');
INSERT INTO COUNTRIES (ID_COUNTRY,COUNTRY)
VALUES ( 4,'Германия');
INSERT INTO COUNTRIES (ID_COUNTRY,COUNTRY)
VALUES ( 5,'Казахстан');
INSERT INTO COUNTRIES (ID_COUNTRY,COUNTRY)
VALUES ( 6,'Италия');
INSERT INTO COUNTRIES (ID_COUNTRY,COUNTRY)
VALUES ( 7,'Швеция');
INSERT INTO COUNTRIES (ID_COUNTRY,COUNTRY)
VALUES ( 8,'Норвегия');
INSERT INTO COUNTRIES (ID_COUNTRY,COUNTRY)
VALUES ( 9,'Украина');
INSERT INTO COUNTRIES (ID_COUNTRY,COUNTRY)
VALUES ( 10,'Бельгия');
--------------- Не вводите это, прочтите дальше, чтобы избежать мороки
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 111,6,'ДУЛЬКОЛАКС','таблетки 5мг','BOEHRINGER INGELHEIM FRANCE',56);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 112,6,'ФОРТРАНС','порошок 64г','BEAUFOUR IPSEN INDUSTRIE',32);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 113,6,'ДЮФАЛАК','сироп 100мл','SOLVAY B.V.',113);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 114,4,'СУПРАСТИН','таблетки 25мг','EGIS PHARMACEUTICALS Ltd.',276);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 115,4,'АЛЛЕРТЕК','таблетки 10 мг','WARSAW PHARMACEUTICAL WORK POLSA',87);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 116,4,'АЛЛЕРТЕК','раствор-капли 1мл','NOVARTIS CONSUMER HEALTH S.A.',55);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 117,4,'ФЕНИСТИЛ','гель 1г','NOVARTIS CONSUMER HEALTH S.A.',107);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 118,4,'ФЕНИСТИЛ 24','капсулы 4 мг','NOVARTIS CONSUMER HEALTH S.A.',218);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 119,5,'СИМИЛАК С ЖЕЛЕЗОМ','смесь молочная сухая (на 1 л)','ALFA FOODS ARINCO',123);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 120,5,'СИМИЛАК ФОРМУЛА ПЛЮС 1','смесь молочная сухая (на 1 л)','ABBOT LABORATORIES S.A.',43);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 121,5,'СИМИЛАК ФОРМУЛА ПЛЮС 2','смесь молочная сухая (на 1 л)','ABBOT LABORATORIES S.A.',51);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 122,7,'АМФОТЕРИЦИН В','флаконы 10 мл 50 000 ед','СИНТЕЗ ОАО',231);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 123,7,'АМФОТЕРИЦИН В','мазь 1 г 30 000 ед','СИНТЕЗ ОАО',175);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 124,7,'НИСТАТИН','мазь 1 г 100 000 ед','НИЖФАРМ ОАО',45);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 125,7,'НИСТАТИН','таблетки 250 000 ед','БИОСИНТЕЗ ОАО',23);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 126,8,'ПРОТЕИН МОДУЛЬ','питательная смесь сухая 100 г','BERLIN-CHEMIE AG',76);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 127,8,'КАРНИТИН МОДУЛЬ','питательная смесь сухая 1 г','BERLAMIN MODULAR',27);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 128,8,'МСТ МОДУЛЬ','питательная смесь сух 1 пак','BERLAMIN MODULAR',231);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 129,9,'БИЦИЛЛИН-1','порошок 1 фл 1.2 млн ед','СИНТЕЗ ОАО',5.12);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 130,9,'БИЦИЛЛИН-3','порошок 1 фл 1.2 млн ед','СИНТЕЗ ОАО',7);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 131,9,'БИЦИЛЛИН-5','порошок 1 фл 1.2 млн ед','СИНТЕЗ ОАО',9);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 132,10,'М-ЭСЛОН','капсулы ретард 10 мг','GRUNENHAL GmbH',12);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 133,10,'М-ЭСЛОН','капсулы ретард 30 мг','GRUNENHAL GmbH',27);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 134,10,'М-ЭСЛОН','капсулы ретард 100 мг','GRUNENHAL GmbH',74);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 135,11,'РЕЛАДОРМ','таблетки','TARCHOMIN WORKS',48);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 136,11,'ЭСТАЗОЛАМ','таблетки 2 мг','TARCHOMIN WORKS',38);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 137,11,'СОМНОЛ','таблетки 7.5 мг','GRINDEX',61);
INSERT INTO LEK_LIST (LEK_ID, ID_KATEGOR, NAZVZAN1, NAZVZAN2, NAZVZAN3, CENA)
VALUES ( 144,6,'ДУЛЬКОЛАКС','таблетки 5мг','BOEHRINGER INGELHEIM FRANCE',56);
---------------
- это нарушает ограничение целостности. ID_KATEGOR>7 не существуют
в таб KATEGORII. Нужно триггер поставить после ввода таблицы
и начальное значение генератора установить. Т е:
- DROP TRIGGER SET_ID_KATEGOR;
- DELETE FROM LEK_LIST;
- DELETE FROM KATEGORII;
- Снова заполнить KATEGORII
- INSERT (тот же но только записи ID_KATEGOR < 8 - 4 записи).
Теперь ID_KATEGOR от 4 до 7, как и значение генератора=7.
- возвращаю триггер
- ввожу остальные записи (ID_KATEGOR > 7). Теперь значение генератора=11
и последнее значение ID_KATEGOR=11, так что дальше триггер будет
вырабатывать годные номера ID_KATEGOR=12,13,...
- Повторяем список INSERT (для LEK_LIST). На этот раз нормально.
- Вся эта морока потому, что не хочется менять старые номера ID_KATEGOR
(они связаны с табл LEK_LIST)
============== Структура БД ===========
----------- MetaData for KAS6.gdb ---------------
SET SQL DIALECT 3;
/* CREATE DATABASE 'E:\XRAN_RW_MyProg\Apteka\BD_IB_kassa6\KAS6.gdb'
PAGE_SIZE 4096
DEFAULT CHARACTER SET WIN1251 */
/* Domain definitions */
CREATE DOMAIN "CHAR10" AS VARCHAR(10) CHARACTER SET WIN1251;
CREATE DOMAIN "CHAR100" AS VARCHAR(100) CHARACTER SET WIN1251;
CREATE DOMAIN "CHAR12" AS VARCHAR(12) CHARACTER SET WIN1251;
CREATE DOMAIN "CHAR20" AS VARCHAR(20) CHARACTER SET WIN1251;
CREATE DOMAIN "CHAR30" AS VARCHAR(30) CHARACTER SET WIN1251;
CREATE DOMAIN "CHAR50" AS VARCHAR(50) CHARACTER SET WIN1251;
CREATE DOMAIN "COUNT1" AS SMALLINT
CHECK (VALUE >= 0);
CREATE DOMAIN "ID1" AS SMALLINT;
CREATE DOMAIN "MONEY" AS NUMERIC(8, 2)
DEFAULT 0
CHECK (VALUE > 0);
/* Table: AP_USERS, Owner: SYSDBA */
CREATE TABLE "AP_USERS"
(
"ID_USERS" "ID1" NOT NULL,
"NAME" "CHAR100" NOT NULL,
"EMAIL" "CHAR30" NOT NULL,
"PASS" "CHAR20" NOT NULL,
"CONTACTFIO" "CHAR30" NOT NULL,
"ID_COUNTRY" "ID1" NOT NULL,
"PINDEX" "CHAR10" NOT NULL,
"CITY" "CHAR20" NOT NULL,
"PADDRESS" "CHAR50" NOT NULL,
"PHONE" "CHAR12",
"FAX" "CHAR12",
"ETC" "CHAR50",
UNIQUE ("EMAIL"),
PRIMARY KEY ("ID_USERS")
);
/* Table: COUNTRIES, Owner: SYSDBA */
CREATE TABLE "COUNTRIES"
(
"ID_COUNTRY" "ID1" NOT NULL,
"COUNTRY" "CHAR20" NOT NULL,
CONSTRAINT "CONS2" UNIQUE ("COUNTRY"),
PRIMARY KEY ("ID_COUNTRY")
);
/* Table: KATEGORII, Owner: SYSDBA */
CREATE TABLE "KATEGORII"
(
"ID_KATEGOR" "ID1" NOT NULL,
"KATEGOR" "CHAR20" NOT NULL,
PRIMARY KEY ("ID_KATEGOR")
);
/* Table: LEK_LIST, Owner: SYSDBA */
CREATE TABLE "LEK_LIST"
(
"LEK_ID" "ID1" NOT NULL,
"ID_KATEGOR" "ID1" NOT NULL,
"NAZVZAN1" "CHAR30",
"NAZVZAN2" "CHAR30",
"NAZVZAN3" "CHAR50",
"CENA" "MONEY" NOT NULL,
PRIMARY KEY ("LEK_ID")
);
/* Table: SKLAD, Owner: SYSDBA */
CREATE TABLE "SKLAD"
(
"LEK_ID" "ID1" NOT NULL,
"OSTATOK" "COUNT1",
"STELLAJ" "CHAR20",
UNIQUE ("LEK_ID")
);
ALTER TABLE "AP_USERS" ADD FOREIGN KEY ("ID_COUNTRY") REFERENCES
COUNTRIES ("ID_COUNTRY");
ALTER TABLE "LEK_LIST" ADD FOREIGN KEY ("ID_KATEGOR") REFERENCES
KATEGORII ("ID_KATEGOR");
ALTER TABLE "SKLAD" ADD FOREIGN KEY ("LEK_ID") REFERENCES LEK_LIST
("LEK_ID");
CREATE GENERATOR "AP_USERS_ID";
CREATE GENERATOR "COUNTRIES_ID";
CREATE GENERATOR "KATEGORII_ID";
/* View: SPISOK1, Owner: SYSDBA */
CREATE VIEW "SPISOK1" (
"NAZVZAN1",
"OSTATOK",
"CENA"
) AS
SELECT NAZVZAN1, OSTATOK, CENA
FROM LEK_LIST, SKLAD
WHERE SKLAD.LEK_ID = LEK_LIST.LEK_ID
;
/* View: SPISOK2, Owner: SYSDBA */
CREATE VIEW "SPISOK2" (
"KATEGOR",
"NAZVZAN1",
"OSTATOK",
"CENA"
) AS
SELECT KATEGOR, NAZVZAN1, OSTATOK, CENA
FROM KATEGORII, LEK_LIST, SKLAD
WHERE (LEK_LIST.ID_KATEGOR = KATEGORII.ID_KATEGOR) AND
(SKLAD.LEK_ID = LEK_LIST.LEK_ID)
;
/* Exceptions */
CREATE EXCEPTION "NO_DEL_KAT" 'Нельзя удалять - есть лекарства этой категории';
CREATE EXCEPTION "NO_INS_LEK" 'Нельзя добавить - нет такой категории';
CREATE EXCEPTION "NO_UPD_KAT" 'Нельзя изменить - есть лекарства этой категории';
CREATE EXCEPTION "NO_UPD_LEK" 'Нельзя изменить - нет такой категории';
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;
/* Stored procedures */
CREATE PROCEDURE "DELETE_LEKAR"
(
"LEKID" INTEGER
)
AS
BEGIN EXIT; END ^
CREATE PROCEDURE "INSERT_LEKAR"
(
"LEKID" INTEGER,
"IDKATEGOR" INTEGER,
"N1" VARCHAR(30) CHARACTER SET WIN1251,
"N2" VARCHAR(30) CHARACTER SET WIN1251,
"N3" VARCHAR(40) CHARACTER SET WIN1251,
"CENA" NUMERIC(8, 2)
)
AS
BEGIN EXIT; END ^
CREATE PROCEDURE "UPDATE_LEKAR"
(
"LEKID" INTEGER,
"IDKATEGOR" INTEGER,
"N1" VARCHAR(30) CHARACTER SET WIN1251,
"N2" VARCHAR(30) CHARACTER SET WIN1251,
"N3" VARCHAR(40) CHARACTER SET WIN1251,
"CENA" NUMERIC(8, 2)
)
AS
BEGIN EXIT; END ^
ALTER PROCEDURE "DELETE_LEKAR"
(
"LEKID" INTEGER
)
AS
BEGIN
DELETE FROM LEK_LIST
WHERE (LEK_ID = :LEKID);
END
^
ALTER PROCEDURE "INSERT_LEKAR"
(
"LEKID" INTEGER,
"IDKATEGOR" INTEGER,
"N1" VARCHAR(30) CHARACTER SET WIN1251,
"N2" VARCHAR(30) CHARACTER SET WIN1251,
"N3" VARCHAR(40) CHARACTER SET WIN1251,
"CENA" NUMERIC(8, 2)
)
AS
BEGIN
INSERT INTO LEK_LIST
VALUES (:LEKID, :IDKATEGOR, :N1, :N2, :N3, :CENA);
END
^
ALTER PROCEDURE "UPDATE_LEKAR"
(
"LEKID" INTEGER,
"IDKATEGOR" INTEGER,
"N1" VARCHAR(30) CHARACTER SET WIN1251,
"N2" VARCHAR(30) CHARACTER SET WIN1251,
"N3" VARCHAR(40) CHARACTER SET WIN1251,
"CENA" NUMERIC(8, 2)
)
AS
BEGIN
UPDATE LEK_LIST
SET
LEK_ID = :LEKID,
ID_KATEGOR = :IDKATEGOR,
NAZVZAN1 = :N1,
NAZVZAN2 = :N2,
NAZVZAN3 = :N3,
CENA = :CENA
WHERE LEK_ID = :LEKID;
END
^
SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;
SET TERM ^ ;
/* Triggers only will work for SQL triggers */
CREATE TRIGGER "SET_ID_CNTR" FOR "COUNTRIES"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.ID_COUNTRY = gen_id(COUNTRIES_ID, 1);
END
^
CREATE TRIGGER "SET_ID_KATEGOR" FOR "KATEGORII"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.ID_KATEGOR = gen_id(KATEGORII_ID, 1);
END
^
CREATE TRIGGER "BEFORE_DELETE_KAT" FOR "KATEGORII"
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE lek_count INTEGER ;
BEGIN
SELECT count(lek_id) from LEK_LIST
WHERE LEK_LIST.ID_KATEGOR = KATEGORII.ID_KATEGOR
INTO :lek_count ;
IF (:lek_count > 0) THEN EXCEPTION no_del_kat ;
END
^
CREATE TRIGGER "BEFORE_INSERT_LEK" FOR "LEK_LIST"
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE kat_count INTEGER;
BEGIN
SELECT count(ID_KATEGOR) from KATEGORII
WHERE KATEGORII.ID_KATEGOR = new.ID_KATEGOR
INTO :kat_count;
IF (:kat_count < 1) THEN EXCEPTION no_ins_lek;
END
^
CREATE TRIGGER "BEFORE_UPDATE_LEK" FOR "LEK_LIST"
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE kat_count INTEGER;
BEGIN
SELECT count(ID_KATEGOR) from KATEGORII
WHERE KATEGORII.ID_KATEGOR = new.ID_KATEGOR
INTO :kat_count;
IF (:kat_count < 1) THEN EXCEPTION no_upd_lek;
END
^
COMMIT WORK ^
SET TERM ;^
/* Grant Roles for this database */
/* Grant permissions for this database */
