ΠŸΠΎΠΌΠΎΡ‰ΡŒ Π² написании студСнчСских Ρ€Π°Π±ΠΎΡ‚
АнтистрСссовый сСрвис

Π‘ΠΊΡ€ΠΈΠΏΡ‚Ρ‹ ΠΈ ΠΈΡ… описаниС

Π Π΅Ρ„Π΅Ρ€Π°Ρ‚ΠŸΠΎΠΌΠΎΡ‰ΡŒ Π² Π½Π°ΠΏΠΈΡΠ°Π½ΠΈΠΈΠ£Π·Π½Π°Ρ‚ΡŒ ΡΡ‚ΠΎΠΈΠΌΠΎΡΡ‚ΡŒΠΌΠΎΠ΅ΠΉ Ρ€Π°Π±ΠΎΡ‚Ρ‹

ДабавляСм Π½ΠΎΠ²Ρ‹Π΅ поля Π² Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ ΠΈ ΡΠ²ΡΠ·Ρ‹Π²Π°Π΅ΠΌ ΠΈΡ… Ρ Ρ‚Π°Π±Π»ΠΈΡ†Π΅ΠΉ адрСс*/. INSERT INTO row_order SELECT g. id_goods, o. id_order, o. quantity FROM orders o JOIN goods g USING (id_goods); 6. ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ Ρ†Π΅Π½Ρƒ Π½Π° Π΄ΠΎΠΌΠ΅Π½Π΅ дСньги ΠΈ ΡΡ‚ΠΎΠΈΠΌΠΎΡΡ‚ΡŒ Π² Π·Π°ΠΊΠ°Π·Π΅*/. УдаляСм столбцы Π³ΠΎΡ€ΠΎΠ΄Π° ΠΈ ΡƒΠ»ΠΈΡ†Ρ‹ ΠΈΠ· Ρ‚Π°Π±Π»ΠΈΡ† client ΠΈ employee*/. INSERT INTO SPECIALIZATION (SPECIALIZATION, ID_SPECIALIZATION) VALUES ('ЭкспСдитор', 4); WHERE a… Π§ΠΈΡ‚Π°Ρ‚ΡŒ Π΅Ρ‰Ρ‘ >

Π‘ΠΊΡ€ΠΈΠΏΡ‚Ρ‹ ΠΈ ΠΈΡ… описаниС (Ρ€Π΅Ρ„Π΅Ρ€Π°Ρ‚, курсовая, Π΄ΠΈΠΏΠ»ΠΎΠΌ, ΠΊΠΎΠ½Ρ‚Ρ€ΠΎΠ»ΡŒΠ½Π°Ρ)

Π‘ΠΊΡ€ΠΈΠΏΡ‚ ΠΌΠΎΠ΄ΠΈΡ„ΠΈΠΊΠ°Ρ†ΠΈΠΈ Π‘Π”

Π‘ΠΊΡ€ΠΈΠΏΡ‚Ρ‹ ΠΈ ΠΈΡ… описаниС.

ЦСль ΠΌΠΎΠ΄ΠΈΡ„ΠΈΠΊΠ°Ρ†ΠΈΠΈ — ΡƒΠ»ΡƒΡ‡ΡˆΠ΅Π½ΠΈΠ΅ структуры Π‘Π”, оптимизация Π΅Π΅ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Π½ΠΈΡ.

Для Ρ‚ΠΎΠ³ΠΎ, Ρ‡Ρ‚ΠΎΠ±Ρ‹ ΠΌΠΎΠ΄ΠΈΡ„ΠΈΡ†ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ ΠΈΡΡ…ΠΎΠ΄Π½ΡƒΡŽ Π±Π°Π·Ρƒ Π΄Π°Π½Π½Ρ‹Ρ… Π±Π΅Π· ΠΏΠΎΡ‚Π΅Ρ€ΠΈ ΠΈΠΌΠ΅ΡŽΡ‰Π΅ΠΉΡΡ Π² Π½Π΅ΠΉ ΠΈΠ½Ρ„ΠΎΡ€ΠΌΠ°Ρ†ΠΈΠΈ, Π½Π΅ΠΎΠ±Ρ…ΠΎΠ΄ΠΈΠΌΠΎ Π²Ρ‹ΠΏΠΎΠ»Π½ΠΈΡ‚ΡŒ ΡΠ»Π΅Π΄ΡƒΡŽΡ‰ΠΈΠ΅ дСйствия:

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

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 integ24,.

DROP id_group;

DROP TABLE composition_group;

DROP TABLE working_group;

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

/*4. ΡΠΏΠ΅Ρ†ΠΈΠ°Π»ΠΈΠ·Π°Ρ†ΠΈΡŽ ΠΏΠ΅Ρ€Π΅ΠΈΠΌΠ΅Π½ΠΎΠ²Π°Ρ‚ΡŒ Π² Π΄ΠΎΠ»ΠΆΠ½ΠΎΡΡ‚ΡŒ*/.

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

ALTER TABLE employee DROP CONSTRAINT integ22;

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 integ23,.

DROP id_street,.

DROP house_number;

ALTER TABLE client DROP CONSTRAINT integ18,.

DROP CONSTRAINT integ19,.

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;

ΠŸΠΎΠΊΠ°Π·Π°Ρ‚ΡŒ вСсь тСкст
Π—Π°ΠΏΠΎΠ»Π½ΠΈΡ‚ΡŒ Ρ„ΠΎΡ€ΠΌΡƒ Ρ‚Π΅ΠΊΡƒΡ‰Π΅ΠΉ Ρ€Π°Π±ΠΎΡ‚ΠΎΠΉ