|
|
ПРИЛОЖЕНИЕ 1. Скрипты Базы данныхФайлcreate_db: CREATE DATABASE uch_db WITH OWNER = postgres ENCODING = 'WIN1251'; Файл create_schema: CREATE SCHEMA zakazy AUTHORIZATION users2009; Файл create_domain: CREATE DOMAIN zakazy.d_age AS smallint NOT NULL CONSTRAINT d_age_check CHECK (((VALUE >= 22) OR (VALUE <= 65))); CREATE DOMAIN zakazy.d_count AS integer NOT NULL CONSTRAINT d_count_check CHECK ((VALUE >= 0)); ALTER DOMAIN zakazy.d_count OWNER TO postgres; CREATE DOMAIN zakazy.d_fio AS character varying(70) NOT NULL CONSTRAINT d_fio_check CHECK (((VALUE)::text ~ '[A-Za-zА-Яа-я-]+'::text)); CREATE DOMAIN zakazy.d_price AS real CONSTRAINT d_price_check CHECK ((VALUE >= (0)::double precision)); Файл create_table: CREATE TABLE zakazy.clienty ( id_cln integer NOT NULL, company character varying(50) NOT NULL, id_slzh integer NOT NULL, limit_credit zakazy.d_price NOT NULL ); CREATE TABLE zakazy.offisy ( id_ofc integer NOT NULL, city character varying(70) NOT NULL, region character varying(70) NOT NULL, mngr integer NOT NULL, target zakazy.d_price NOT NULL, sales zakazy.d_price NOT NULL ); CREATE TABLE zakazy.sluzhaschie ( id_slzh integer NOT NULL, "family" zakazy.d_fio NOT NULL, "name" zakazy.d_fio NOT NULL, age zakazy.d_age NOT NULL, id_ofc integer, dlzh character varying(50) NOT NULL, mngr integer, quota zakazy.d_price, sales zakazy.d_price NOT NULL ); CREATE TABLE zakazy.tovary ( id_mvr character varying(20) NOT NULL, id_prd character varying(10) NOT NULL, description character varying(100) NOT NULL, price zakazy.d_price NOT NULL, count_tvr zakazy.d_count NOT NULL ); CREATE TABLE zakazy.zakazy ( id_order integer NOT NULL, date_order date NOT NULL, id_cln integer NOT NULL, id_slzh integer NOT NULL, id_mvr character varying(20) NOT NULL, id_prd character varying(10) NOT NULL, count_ord zakazy.d_count NOT NULL, price_all zakazy.d_price NOT NULL ); Файлadd_PK: /************ Добавление первичных ключей в базу данных ***************/ /* Table Items: zakazy.clienty */ ALTER TABLE zakazy.clienty ADD CONSTRAINT pkclienty PRIMARY KEY (id_cln); /* Table Items: zakazy.offisy */ ALTER TABLE zakazy.offisy ADD CONSTRAINT pkoffisy PRIMARY KEY (id_ofc); /* Table Items: zakazy.sluzhaschie */ ALTER TABLE zakazy.sluzhaschie ADD CONSTRAINT pksluzhaschie PRIMARY KEY (id_slzh); /* Table Items: zakazy.tovary */ ALTER TABLE zakazy.tovary ADD CONSTRAINT pktovary PRIMARY KEY (id_mvr, id_prd); /* Table Items: zakazy.zakazy */ ALTER TABLE zakazy.zakazy ADD CONSTRAINT pkzakazy PRIMARY KEY (id_order); Файлadd_FK: /************ Добавление вторичных ключей в базу данных ***************/ /************ Foreign Key: fk_clienty_sluzhaschie ***************/ ALTER TABLE zakazy.clienty ADD CONSTRAINT fk_clienty_sluzhaschie FOREIGN KEY (id_slzh) REFERENCES zakazy.sluzhaschie (id_slzh) ON UPDATE CASCADE ON DELETE RESTRICT; /************ Foreign Key: fk_sluzhaschie_offisy ***************/ ALTER TABLE zakazy.sluzhaschie ADD CONSTRAINT fk_sluzhaschie_offisy FOREIGN KEY (id_ofc) REFERENCES zakazy.offisy (id_ofc) ON UPDATE CASCADE ON DELETE CASCADE; /************ Foreign Key: fk_zakazy_clienty ***************/ ALTER TABLE zakazy.zakazy ADD CONSTRAINT fk_zakazy_clienty FOREIGN KEY (id_cln) REFERENCES zakazy.clienty (id_cln) ON UPDATE CASCADE ON DELETE CASCADE; /************ Foreign Key: fk_zakazy_sluzhaschie ***************/ ALTER TABLE zakazy.zakazy ADD CONSTRAINT fk_zakazy_sluzhaschie FOREIGN KEY (id_slzh) REFERENCES zakazy.sluzhaschie (id_slzh) ON UPDATE CASCADE ON DELETE RESTRICT; /************ Foreign Key: fk_zakazy_tovary ***************/ ALTER TABLE zakazy.zakazy ADD CONSTRAINT fk_zakazy_tovary FOREIGN KEY (id_mvr, id_prd) REFERENCES zakazy.tovary (id_mvr, id_prd) ON UPDATE CASCADE ON DELETE RESTRICT; Файл create_seq: /* Table Items: zakazy.clienty */ /* Alter Columns */ CREATE SEQUENCE zakazy.clienty_id_cln_seq; ALTER TABLE zakazy.clienty ALTER COLUMN id_cln SET DEFAULT nextval('zakazy.clienty_id_cln_seq'); /* Table Items: zakazy.offisy */ /* Alter Columns */ CREATE SEQUENCE zakazy.offisy_id_ofc_seq; ALTER TABLE zakazy.offisy ALTER COLUMN id_ofc SET DEFAULT nextval('zakazy.offisy_id_ofc_seq'); /* Table Items: zakazy.sluzhaschie */ /* Alter Columns */ CREATE SEQUENCE zakazy.sluzhaschie_id_slzh_seq; ALTER TABLE zakazy.sluzhaschie ALTER COLUMN id_slzh SET DEFAULT nextval('zakazy.sluzhaschie_id_slzh_seq'); /* Table Items: zakazy.zakazy */ /* Alter Columns */ CREATE SEQUENCE zakazy.zakazy_id_order_seq; ALTER TABLE zakazy.zakazy ALTER COLUMN id_order SET DEFAULT nextval('zakazy.zakazy_id_order_seq'); Файл create_index: CREATE INDEX index_descript ON zakazy.tovary (description) Файл insert_data: /*** Ввод данных в таблицу offisy ***/ INSERT INTO zakazy.offisy(id_ofc, city, region, mngr, target, sales) VALUES (322, 'Инза', 'Ульяновская', 2108, 300000.00, 186042.00 ); INSERT INTO zakazy.offisy(id_ofc, city, region, mngr, target, sales) VALUES (311, 'Буинск', 'Татарстан', 2106, 575000.00, 692637.00 ); INSERT INTO zakazy.offisy(id_ofc, city, region, mngr, target, sales) VALUES (312, 'Тверь', 'Московская', 2104, 800000.00, 735042.00 ); INSERT INTO zakazy.offisy(id_ofc, city, region, mngr, target, sales) VALUES ( 313, 'Орел', 'Орловская', 2105, 350000.00, 367911.00); INSERT INTO zakazy.offisy(id_ofc, city, region, mngr, target, sales) VALUES ( 321, 'Киров', 'Кировская', 2108, 725000.00, 835915.00); /*** Ввод данных в таблицу sluzhaschie ***/ INSERT INTO zakazy.sluzhaschie(id_slzh, "family", "name", age, id_ofc, dlzh, mngr, quota, sales) VALUES (2105,'Филатов', 'Петр', 37, 313, 'Брокер', 2104, 350000.00, 367911.00 ); INSERT INTO zakazy.sluzhaschie(id_slzh, "family", "name", age, id_ofc, dlzh, mngr, quota, sales) VALUES (2109, 'Полев', 'Андрей', 31, 311, 'Брокер', 2106, 300000.00, 392725.00 ); INSERT INTO zakazy.sluzhaschie(id_slzh, "family", "name", age, id_ofc, dlzh, mngr, quota, sales) VALUES (2102, 'Пронин', 'Игорь', 48, 321, 'Брокер', 2108, 350000.00, 474050.00 ); INSERT INTO zakazy.sluzhaschie(id_slzh, "family", "name", age, id_ofc, dlzh, mngr, quota, sales) VALUES (2106 ,'Петров', 'Петр', 52, 311, 'Гл.Брокер', NULL, 275000.00, 299912.00 ); INSERT INTO zakazy.sluzhaschie(id_slzh, "family", "name", age, id_ofc, dlzh, mngr, quota, sales) VALUES (2104, 'Иванов', 'Иван', 33, 312, 'Ст.Брокер', 2106, 200000.00 ,142594.00 ); INSERT INTO zakazy.sluzhaschie(id_slzh, "family", "name", age, id_ofc, dlzh, mngr, quota, sales) VALUES (2101, 'Федоров', 'Федор', 45, 312, 'Брокер', 2104, 300000.00, 305673.00 ); INSERT INTO zakazy.sluzhaschie(id_slzh, "family", "name", age, id_ofc, dlzh, mngr, quota, sales) VALUES (2110, 'Уткин', 'Денис', 41, NULL, 'Брокер', 2101, NULL, 75985.00 ); INSERT INTO zakazy.sluzhaschie(id_slzh, "family", "name", age, id_ofc, dlzh, mngr, quota, sales) VALUES (2108, 'Нилов', 'Лев', 62, 321, 'Ст.Брокер', 2106, 350000.00, 361865.00 ); INSERT INTO zakazy.sluzhaschie(id_slzh, "family", "name", age, id_ofc, dlzh, mngr, quota, sales) VALUES (2103, 'Филин', 'Федор', 29, 312, 'Брокер', 2104, 275000.00, 286775.00 ); INSERT INTO zakazy.sluzhaschie(id_slzh, "family", "name", age, id_ofc, dlzh, mngr, quota, sales) VALUES (2107, 'Ганин', 'Сергей', 49, 322, 'Брокер', 2108, 300000.00, 186042.00 ); /*** Ввод данных в таблицу clienty ***/ INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12111, '«Заря»', 2103, 50000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12102, '«Гранит»', 2101, 65000.00); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12103, '«Базальт»', 2105, 50000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12123, '«Марс»', 2102, 40000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES ( 12107, '«Комета»', 2110, 35000.00); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES ( 12115, '«Сатурн»', 2101, 20000.00); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES ( 12101, '«Метеор»', 2106, 65000.00); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES ( 12112, '«Юпитер»', 2108, 50000.00); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12121, '«Агат»', 2103, 45000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12114, '«Аметист»', 2102, 20000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12124, '«Меркурий»', 2107, 40000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12108, '«Алмаз»', 2109, 55000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12117, '«Цезарь»', 2106, 35000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12122, '«Центурион»', 2105, 30000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12120, '«Взлет»', 2102, 50000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12106, '«Атлант»', 2102, 65000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12119, '«Аргонавт»', 2109, 25000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12118, '«Стрела»', 2108, 60000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12113, '«Кристалл»', 2104, 20000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12109, '«Самолет»', 2103, 25000.00 ); INSERT INTO zakazy.clienty(id_cln, company, id_slzh, limit_credit) VALUES (12105, '«Энергия»', 2101, 45000.00 ); /*** Ввод данных в таблицу tovary ***/ INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('УАЗ', '2A45C', 'Деталь кузова', 79.00, 210 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('ВАЗ', '4100Y', 'Деталь двигателя', 2750.00, 25 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('ПМЗ', 'XK47', 'Сопло', 355.00, 38 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('УПЗ', '41672' , 'Плата', 180.00, 0 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('Утес', '779C' , 'Пылесос А300', 1875.00, 9 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('ВАЗ', '41003' , 'Дверь 1', 107.00, 207 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('ВАЗ', '41004', 'Дверь 2', 117.00, 139 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('УПЗ', '41003', 'Корпус', 652.00, 3 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('Утес', '887P', 'Пылесос ручной', 250.00, 24 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('ПМЗ', 'XK48', 'Ось', 134.00, 203 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('УАЗ', '2A44L', 'Левая дверь', 4500.00, 12 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('УМЗ', '112', 'Цепь', 148.00, 115 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('Утес', '887H', 'Шланг пылесоса', 54.00, 223 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('УПЗ', '41089', 'Крышка', 225.00, 78 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('ВАЗ', '41001', 'Ручка', 55.00, 277 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('Утес', '775C', 'Пылесос В200', 1425.00, 5 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('ВАЗ', '4100Z', 'Карбюратор', 2500.00, 28 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('ПМЗ', 'XK48A', 'Редуктор', 177.00, 37 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('ВАЗ', '41002', 'Педаль', 76.00, 167 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('УАЗ', '2A44R', 'Правая дверь', 4500.00, 12 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('Утес', '773C', 'Пылесос Д100', 975.00, 28 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('ВАЗ', '4100X', 'Дворник', 25.00, 37 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('УМЗ', '114', 'Поршень', 243.00, 15 ); INSERT INTO zakazy.tovary(id_mvr, id_prd, description, price, count_tvr) VALUES ('Утес', '887X', 'Brace Retainer', 475.00, 32 ); /*** Ввод данных в таблицу zakazy ***/ INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (312961, '1989-12-17', 12117, 2106, 'УАЗ', '2A44L', 7, 31500.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313012, '1990-01-17', 12111, 2105, 'ВАЗ', '41003', 35, 3745.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (312989, '1990-03-01', 12101, 2106, 'УМЗ', '114', 6, 1458.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313051, '1990-10-02', 12118, 2108, 'ПМЗ', 'XK47', 4, 1420.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (312968, '1989-12-10', 12102, 2101, 'ВАЗ', '41004', 34, 3978.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313036, '1990-01-30', 12107, 2110, 'ВАЗ', '4100Z', 9, 22500.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313045, '1990-02-02', 12112, 2108, 'УАЗ', '2A44R', 10, 45000.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (312963, '1989-12-17', 12103, 2105, 'ВАЗ', '41004', 28, 3276.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313013, '1990-01-14', 12118, 2108, 'УПЗ', '41003', 1, 652.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313058, '1990-02-23', 12108, 2109, 'УМЗ', '112', 10, 1480.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (312997, '1990-08-01', 12124, 2107, 'УПЗ', '41003', 1, 652.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (312983, '1989-12-27', 12103, 2105, 'ВАЗ', '41004', 6, 702.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313024, '1990-01-20', 12114, 2108, 'ПМЗ', 'XK47', 20, 7100.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313062, '1990-02-24', 12124, 2107, 'УМЗ', '114', 10, 2430.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313027, '1990-01-22', 12103, 2105, 'ВАЗ', '41002', 54, 4104.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313034, '1990-01-29', 12107, 2110, 'УАЗ', '2A45C', 8, 632.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313055, '1990-02-15', 12108, 2101, 'ВАЗ', '4100X', 6, 150.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (312993, '1989-01-04', 12106, 2102, 'УАЗ', '2A45C', 24, 1896.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313065, '1990-02-27', 12106, 2102, 'ПМЗ', 'XK47', 6, 2130.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313049, '1990-02-10', 12118, 2108, 'ПМЗ', 'XK47', 2, 776.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (312987, '1989-12-31', 12103, 2105, 'ВАЗ', '4100Y', 11, 27500.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313057, '1990-02-18', 12111, 2103, 'ВАЗ', '4100X', 24, 600.00 ); INSERT INTO zakazy.zakazy(id_order, date_order, id_cln, id_slzh, id_mvr, id_prd, count_ord, price_all) VALUES (313042, '1990-02-02', 12113, 2101, 'УАЗ', '2A44R', 5, 22500.00 );
Не нашли, что искали? Воспользуйтесь поиском по сайту: ©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.
|