Скрипты и их описание

Скрипт модификации БД

Цель модификации — улучшение структуры БД, оптимизация ее использования.

Для того, чтобы модифицировать исходную базу данных без потери имеющейся в ней информации, необходимо выполнить следующие действия:

/*домен для денег*/.

CREATE DOMAIN money AS NUMERIC (15,2);

/*для перечисляемых типов*/.

CREATE DOMAIN transfer AS INTEGER CHECK (VALUE >= 0);

/**********************/.

/* 1. Заказ — товар M к N */.

/**********************/.

/* Создаем таблицу row_order (строка заказа)*/.

CREATE TABLE Row_order (.

id_goods INTEGER NOT NULL,.

id_order INTEGER NOT NULL,.

quantity transfer NOT NULL);

ALTER TABLE row_order ADD CONSTRAINT XPKRow_order primary key (id_goods, id_order);

ALTER TABLE row_order ADD FOREIGN KEY (id_goods) REFERENCES goods (id_goods), ADD FOREIGN KEY (id_order) REFERENCES orders (id_order);

/*переносим кол-во и данные из таблиц заказ и товар в таблицу строка заказа*/.

INSERT INTO row_order SELECT g. id_goods, o. id_order, o. quantity FROM orders o JOIN goods g USING (id_goods);

/*удаляем столбец кол-во из таблицы заказ*/.

ALTER TABLE orders DROP quantity;

/*разрываем связь*/.

ALTER TABLE orders DROP id_goods;

/**************************************************************/.

/*2. Ввести сущность Поставка которая обьединяет позиции заказа в одну доставку товара*/.

/**************************************************************/.

/*создадим сущность поставка Delivery*/.

CREATE TABLE delivery (id_delivery INTEGER NOT NULL PRIMARY KEY,.

forwarding_agent INTEGER NOT NULL,.

driver INTEGER NOT NULL.

);

ALTER TABLE delivery ADD FOREIGN KEY (forwarding_agent) REFERENCES employee (id_employee),.

ADD FOREIGN KEY (driver) REFERENCES employee (id_employee);

/*создадим таблицу строка поставки (row_delivery)*/.

CREATE TABLE row_delivery (.

id_delivery INTEGER NOT NULL,.

id_order INTEGER NOT NULL);

ALTER TABLE row_delivery ADD CONSTRAINT XPKrow_delivery primary key (id_delivery, id_order);

ALTER TABLE row_delivery ADD FOREIGN KEY (id_delivery) REFERENCES delivery (id_delivery), ADD FOREIGN KEY (id_order) REFERENCES orders (id_order);

/* добавим новую строку в таблицу поставка, специализацию экспедитор и назначим нового экспедитора на поставку*/.

INSERT INTO SPECIALIZATION (SPECIALIZATION, ID_SPECIALIZATION) VALUES ('Экспедитор', 4);

INSERT INTO EMPLOYEE (FIO, ID_EMPLOYEE, HOUSE_NUMBER, ID_STREET, ID_SPECIALIZATION) VALUES ('Кузнецов А.', 4, 10, 3, 4);

INSERT INTO DELIVERY (ID_DELIVERY, FORWARDING_AGENT, DRIVER) VALUES (1, 4, 1);

/*занесем в таблицу строка поставки все данные которые были до реструктуризации бд*/.

INSERT INTO row_delivery (id_delivery, id_order) SELECT 1, o. id_order FROM orders o;

/*****************************************************************/.

/*3. убрать рабочую группу и в соответствии с ролями проставить менеджера и продавца в таблице заказ*/.

/*****************************************************************/.

/*добавим столбцы в таблицу заказ*/.

ALTER TABLE orders.

ADD manager INTEGER,.

ADD seller INTEGER;

ALTER TABLE orders.

ADD FOREIGN KEY (seller) REFERENCES employee (id_employee),.

ADD FOREIGN KEY (manager) REFERENCES employee (id_employee);

/*Заполняем данными новые столбцы*/.

UPDATE orders o.

SET o. manager =.

(SELECT FIRST 1 e. id_employee.

FROM composition_group c, employee e, specialization s.

WHERE c. id_employee=e.id_employee AND e. id_specialization=s.id_specialization AND.

o.id_group=c.id_group AND s. specialization='Менеджер');

UPDATE orders o.

SET o. seller =.

(SELECT FIRST 1 e. id_employee.

FROM composition_group c, employee e, specialization s.

WHERE c. id_employee=e.id_employee AND e. id_specialization=s.id_specialization AND.

o.id_group=c.id_group AND s. specialization='Продавец');

/*удаляем id_group и таблицы composition_grop и working_group*/.

ALTER TABLE orders DROP CONSTRAINT integ₂₄,.

DROP id_group;

DROP TABLE composition_group;

DROP TABLE working_group;

/******************************************/.

/*4. специализацию переименовать в должность*/.

/******************************************/.

ALTER TABLE employee DROP CONSTRAINT integ₂₂;

CREATE TABLE post.

(id_post INTEGER NOT NULL PRIMARY KEY,.

postnm VARCHAR (20).

);

ALTER TABLE employee ALTER id_specialization TO id_post;

INSERT INTO post SELECT s. id_specialization, s. specialization FROM specialization s;

ALTER TABLE employee ADD FOREIGN KEY (id_post) REFERENCES post (id_post);

DROP TABLE specialization;

/***********************/.

/*5. ввести таблицу адрес*/.

/***********************/.

CREATE TABLE address.

(id_address INTEGER NOT NULL PRIMARY KEY,.

id_city INTEGER,.

id_street INTEGER,.

house_number SMALLINT,.

apartament_number SMALLINT);

ALTER TABLE address ADD FOREIGN KEY (id_city) REFERENCES city (id_city),.

ADD FOREIGN KEY (id_street) REFERENCES street (id_street);

CREATE SEQUENCE GEN_ADDRESS_ID;

ALTER SEQUENCE GEN_ADDRESS_ID RESTART WITH 1;

INSERT INTO address (id_address, id_city, id_street, house_number).

SELECT GEN_ID (gen_address_id, 1), c. id_city, c. id_street, c. house_number FROM client c;

INSERT INTO address (id_address, id_city, id_street, house_number).

SELECT GEN_ID (gen_address_id, 1),(SELECT c. id_city FROM city c WHERE c. city_name = 'Рязань'), e. id_street, e. house_number FROM employee e;

/*дабавляем новые поля в таблицы и связываем их с таблицей адрес*/.

ALTER TABLE employee ADD id_address INTEGER,.

ADD FOREIGN KEY (id_address) REFERENCES address (id_address);

ALTER TABLE client ADD id_address INTEGER,.

ADD FOREIGN KEY (id_address) REFERENCES address (id_address);

UPDATE client c.

SET c. id_address =.

(SELECT a. id_address FROM address a.

WHERE a. id_city=c.id_city and a. id_street=c.id_street and a. house_number= c. house_number);

UPDATE employee e.

SET e. id_address =.

(SELECT a. id_address FROM address a.

WHERE a. id_city=(SELECT c. id_city FROM city c WHERE c. city_name = 'Рязань').

AND a. id_street=e.id_street AND a. house_number= e. house_number);

/*удаляем столбцы города и улицы из таблиц client и employee*/.

ALTER TABLE employee DROP CONSTRAINT integ₂₃,.

DROP id_street,.

DROP house_number;

ALTER TABLE client DROP CONSTRAINT integ₁₈,.

DROP CONSTRAINT integ₁₉,.

DROP id_city,.

DROP id_street,.

DROP house_number;

/*************************************************/.

/*6. сделать цену на домене деньги и стоимость в заказе*/.

/*************************************************/.

/*дабавим в таблицу оrders столбец cost (стоимость)*/.

ALTER TABLE orders ADD cost money ,.

ALTER cost SET DEFAULT 0;

ALTER TABLE goods ALTER price TYPE money;

/******************************************/.

/*7. количественные хар-ки на домене не отриц.*/.

/******************************************/.

/*поля наличие, кол-во, номер дома, номер квартиры определим на домене не отрицательно*/.

ALTER TABLE goods ALTER presence TYPE transfer;

ALTER TABLE row_order ALTER quantity TYPE transfer;

ALTER TABLE address ALTER house_number TYPE transfer,.

ALTER apartament_number TYPE transfer;