Создайте уникальный кластерный индекс для таблицы книги. Обоснуйте выбор полей для индексирования.

 

Т.к. кластеризация дает преимущество в том случае, если большую часть данных, которые выбираются из таблицы, составляют группы индексированных данных, а не случайно выбранные отдельные данные, т.к. такие группы располагаются на одной и той же физической странице (или последовательных страницах).

Для таблицы Book естественной представляется выборка групп записей по полю Author, поэтому и создадим кластерный индекс по этому полю.

 

CREATE INDEX idx_author ON "Book" (author);

CLUSTER "Book" USING idx_author

 

Создайте представление для вывода трех заказчиков, имеющих наибольшее количество заказов.

 

CREATE VIEW max_zakaz_3 AS

SELECT COUNT(zakaz.*) AS kolvo, client.name

FROM client, zakaz

WHERE zakaz.num_client=client.num_client

GROUP BY client.num_client

ORDER BY kolvo DESC

LIMIT 3

 

Создайте представление, которое выводило бы данные о поставках, упорядоченные по наименованию издательства и количеству книг.

 

CREATE VIEW postavka_cnt AS

SELECT postavchik.name, "Book".name, postavka.cnt

FROM postavchik, postavka, "Book"

WHERE postavka.num_postavchik=postavchik.num_suppl

AND postavka.num_book="Book".num_book

ORDER BY postavchik.name, postavka.cnt

 

Создайте представление, которое выводило бы информацию о неоплаченных заказах.

 

CREATE VIEW neoplata_zakaz AS

SELECT client.name AS zakazchik, "Book".name AS Kniga, zakaz.data AS data_zakaz

FROM "Book", client, zakaz

WHERE zakaz.num_book="Book".num_book

AND zakaz.num_client=client.num_client

AND zakaz.pay='Нет'

ORDER BY zakazchik

 

Напишите инструкцию, результатом выполнения которой была бы вставка нескольких строк в таблицу Сотрудники, созданную ранее.

 

INSERT INTO "Sotrudnik" (fam_im) VALUES ('Кротов Игорь');

INSERT INTO "Sotrudnik" (fam_im, doljnost) VALUES ('Силко Валерий', 'консультант');

INSERT INTO "Sotrudnik" (fam_im, doljnost, komission) VALUES ('Стельмах Алексей', 'младший продавец', 4);

INSERT INTO "Sotrudnik" (fam_im, doljnost, data_priem) VALUES ('Стельмах Алексей', 'младший продавец', '2014-03-21');

 

Напишите инструкцию, результатом выполнения которой было бы удаление всех неоплаченных на текущую дату заказов из таблицы Заказы.

 

DELETE FROM zakaz WHERE pay='Нет'

 

17. Создайте таблицу такой же структуры, что и таблица Сотрудники, и напишите инструкцию для вставки в нее данных обо всех сотрудниках из таблицы Сотрудники, оформивших более трех заказов и имеющих стаж не менее 5 лет.

 

CREATE TABLE sotrudniki2 AS

SELECT "Sotrudnik".*

FROM "Sotrudnik", zakaz

WHERE zakaz.lic_sotrudnik ="Sotrudnik".lic_nomer

AND "Sotrudnik".data_priem < (now()-interval '5 year')

GROUP BY "Sotrudnik".lic_nomer

HAVING COUNT("Sotrudnik".*)>3

 

Напишите инструкцию, результатом выполнения которой было бы удаление из таблицы Заказчики тех заказчиков, которые сделали последний заказ более 1 года тому назад. Удалите соответствующие заказы из таблицы Заказы.

 

CREATE TEMPORARY TABLE ttt AS (SELECT client.num_client

FROM client, zakaz

WHERE client.num_client=zakaz.num_client

GROUP BY client.num_client

HAVING MAX(zakaz.data)<(now()-interval '1 year'));

 

DELETE FROM client WHERE num_client IN (SELECT num_client FROM temp);

 

DELETE FROM zakaz WHERE num_client IN (SELECT num_client FROM temp);

 

DROP TABLE IF EXISTS ttt;

 

19. Создайте представления для ролей «Директор», «Закупщик», «Продавец», исходя из того, что им необходимо создавать отчеты:

Роль	Отчеты
Директор	- Отчет по продажам: показывает, какие книги пользуются спросом, т.е. формировать рейтинг книг. - Отчет по продавцам: показывает количество проданных книг в месяц и тоже самое в денежном эквиваленте.
Закупщик	- Отчет по поставщикам: показывает список поставщиков указанной книги, цены, условия поставки. - Отчет по книгам: показывает наличие книг с возможностью заносить информацию о поступивших книгах.
Продавец	- Отчет по книгам: показывает наличие книг с возможностью заносить информацию о проданных книгах. - Отчет по продажам: показывает итоговые показатели продаж по продавцам на указанную дату.

 

CREATE VIEW dir_reiting_book AS

SELECT "Book".name, COUNT(zakaz.*) AS kolvo

FROM "Book", zakaz

WHERE "Book".num_book=zakaz.num_book

GROUP BY "Book".num_book

ORDER BY kolvo DESC, name;

 

CREATE VIEW dir_reiting_sotr AS

SELECT "Sotrudnik".fam_im, COUNT(zakaz.*) AS kolvo, SUM("Book".price) AS cena

FROM "Sotrudnik", zakaz, "Book"

WHERE zakaz.lic_sotrudnik="Sotrudnik".lic_nomer

AND zakaz.num_book="Book".num_book

GROUP BY "Sotrudnik".lic_nomer

ORDER BY cena DESC;

 

CREATE VIEW zak_postavchiki AS

SELECT postavchik.name, postavka.cnt AS kolvo, postavka.data, "Book".price AS cena

FROM postavchik, postavka, "Book"

WHERE postavchik.num_suppl=postavka.num_postavchik

AND postavka.num_book="Book".num_book

AND "Book".name='Іван Богун'

 

CREATE VIEW zak_knigi AS

SELECT * FROM "Book" ORDER BY name;

 

Для проверки возможности добавлять информацию:

INSERT INTO zak_knigi (name, author, publish, price, balance)

VALUES ('Москва 2042', 'Войнович', 'UAPRINT', 98, 100);

 

CREATE VIEW prod_prodazha AS

SELECT "Sotrudnik".fam_im, COUNT(zakaz.*) as kolvo

FROM "Sotrudnik", zakaz

WHERE zakaz.lic_sotrudnik="Sotrudnik".lic_nomer

AND zakaz.data='2016-03-20'

GROUP BY "Sotrudnik".lic_nomer

ORDER BY "Sotrudnik".fam_im

 

CREATE ROLE direktor LOGIN PASSWORD '1111';

GRANT SELECT ON dir_reiting_book TO direktor;

GRANT SELECT ON dir_reiting_sotr TO direktor;

GRANT SELECT ON "Book" TO direktor;

GRANT SELECT ON zakaz TO direktor;

GRANT SELECT ON "Sotrudnik" TO direktor;

 

CREATE ROLE zakupchik LOGIN PASSWORD '1111';

GRANT SELECT, INSERT ON zak_knigi TO zakupchik;

GRANT SELECT ON zak_postavchiki TO zakupchik;

GRANT SELECT ON "Book" TO zakupchik;

GRANT SELECT ON postavka TO zakupchik;

GRANT SELECT ON postavchik TO zakupchik;

 

CREATE ROLE prodavec LOGIN PASSWORD '1111'

GRANT SELECT, INSERT ON zak_knigi TO prodavec;

GRANT SELECT ON prod_prodazha TO prodavec;

GRANT SELECT ON "Book" TO prodavec;

GRANT SELECT ON zakaz TO prodavec;

GRANT SELECT ON "Sotrudnik" TO prodavec;

 

Выводы

Выполняя эту лабораторную работу я углубил свои знания в командах SQL. Больше работая не с мастерами построения каких либо объектов, а непосредственно в окне выполнения пользовательских запросов. Тем самым выработал навыки программирования в среде PostgreSQL.

 

Раздел «Ход работы» должен содержать инфологическую модель БД (на языке таблица-связь) для своего варианта и БД BookShop; отчеты DDL для всех таблиц БД BookShop и БД своего варианта; скрипты и результаты выполнения запросов для БД BookShop и БД своего варианта.

 

 

**************************************************************************

 

 

5 Контрольные вопросы