6В06 Информационно-коммуникационные технологии

CREATE TABLE POTOK(

);
Пример 3. (Создание сервера-БД Nagruzka.gdb и таблиц Dep, Pers

Create database "d:\database\Ibase\Nagruzka.gdb" USER "SYSDBA" PASSWORD "masterkey";

CONNECT "d:\database\Ibase\Nagruzka.gdb" USER "SYSDBA" PASSWORD "masterkey";

Create Table Dep(

Dep Char(15) Not Null Primary Key, Proisv Char(1)

);

/*Создание таблицы Pers*/

Create Table Pers(

Num Smallint Not Null Primary Key, Dep Char(15) Default "Неизвестный", Fam Char(20) Not Null,

Year_b SmallInt Default 1950

CHECK ((Year_b>1917) And (Year_b<1980)), Sex Char(1) Default 'м',

Age COMPUTED BY (2003-Year_b),

Charact blob sub_type 1, Photo blob,

{внешний ключ по полю Dp(в скобке) таблицы Dep} Foreign key (Dep) References Dep

ON Delete Set Default ON UPDATE CASCADE, CONSTRAINT Dep CHECK (EXISTS(

SELECT Dep From Dep Where Pers.Dep =Dep.Dep))

);

/*Заполнение таблицы Dep*/

Insert Into Dep (Dep,Proisv) Values ("Бухгалтерия","у");

Insert Into Dep (Dep,Proisv) Values ("Цех1","п");

Insert Into Dep (Dep,Proisv) Values ("Цех2","п");

Insert Into Dep (Dep)

Values ("Неизвестный");

Commit;

/*Заполнение таблицы Pers*/

Insert Into Pers (Num,Dep,Fam,Nam,Par,Year_b,Sex)

Values (1,"Бухгалтерия","Иванов","Иван","Иванович",1950,"м");

Insert Into Pers (Num,Dep,Fam,Nam,Par,Year_b,Sex)

Values (2,"Цех1","Петров","Петр","Петрович",1960,"м");

Insert Into Pers (Num,Dep,Fam,Nam,Par,Year_b,Sex)

Values (3,"Цех2","Сидоров","Сидор","Сидорович",1955,"м");

Insert Into Pers (Num,Dep,Fam,Nam,Par,Year_b,Sex)

Values (4,"Цех1","Иванова","Ирина","Ивановна",1961,"ж");

Insert Into Pers (Num,Dep,Fam,Nam,Par,Year_b,Sex)

Values (5,"Бухгалтерия","Николаев","Николай","Николаевич",1930,"м"); Insert Into Pers (Num,Dep,Fam,Nam,Par,Year_b,Sex)

Values (6,"Цех2","Андреев","Андрей","Андреевич",1930,"м");

Insert Into Pers (Num,Dep,Fam,Nam,Par,Year_b,Sex)

Values (7,"Цех1","Борисов","Борис","Борисович",1937,"м");

Insert Into Pers (Num,Dep,Fam,Nam,Par,Year_b,Sex)

Values (8,"Цех1","Павлов","Павел","Павлович",1975,"м");

Insert Into Pers (Num,Dep,Fam,Nam,Par,Year_b,Sex)

Values (9,"Бухгалтерия","Антонова","Антонина","Антоновна",1955,"ж");

Insert Into Pers (Num,Dep,Fam,Nam,Par,Year_b,Sex)

Values (10,"Цех2","Харитонов","Харитон","Харитонович",1962,"м");

Insert Into Pers (Num,Dep,Fam,Nam,Par,Year_b,Sex)

Values (11,"Цех2","Иванников","Иван","Иванович",1975,"м");

Create index Fio On Pers Fam,Nam,Par; Create index dFio On Pers Dep,Fam,Nam,Par; Create index year On Pers Year_b;

/*Создание View по подразелениям*/ Create VIEW Dep_1 As

Select Fam,Nam,Par,Year_b,Sex From Pers Where Dep="Бухгалтерия";

Create VIEW Dep_2 As

Select Fam,Nam,Par,Year_b,Sex From Pers Where Dep="Цех1";

Create VIEW Dep_3 As

Select Fam,Nam,Par,Year_b,Sex From Pers Where Dep="Цех2";

/*Процедура SELECT - процедура выбора, возвращает сотрудников подразделений параметрическим запросом*/