Файл: Отчет По Курсовой работе По дисциплине Базы данных Тема Фотоаппараты.docx

CREATE OR REPLACE FUNCTION task7(date, date) RETURNS TABLE(part float)

AS $$

BEGIN

RETURN QUERY

SELECT (SELECT COUNT(*) from cameras

WHERE cameras.date_sell BETWEEN $1 AND $2)::real

/

(SELECT COUNT(*) from cameras);

END;

$$ LANGUAGE plpgsql;



Рис.17. Результат выполнения функции task8.

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

CREATE OR REPLACE FUNCTION task77(char, char, char) RETURNS TABLE(id integer, firm text, model text, avgc numeric)

AS $$

BEGIN

RETURN QUERY

SELECT cameras.id, trim(firm.name), trim(model.name), (SELECT avg(cameras.cost) FROM cameras

WHERE cameras.model_id IN (SELECT model.id FROM model WHERE trim(model.name) = $2)) from cameras, firm, model

WHERE cameras.sup_id = (SELECT supp.id FROM supp WHERE supp.name = $1)

AND model.id = cameras.model_id

AND firm.id = model.firm_id

AND cameras.cost

>

(SELECT avg(cameras.cost) FROM cameras

WHERE cameras.model_id IN (SELECT model.id FROM model WHERE trim(model.name) = $2)

AND cameras.country = (SELECT countries.id FROM countries WHERE trim(countries.name) = $3)) GROUP BY cameras.id, firm.name, model.name;

END;

$$ LANGUAGE plpgsql;


Рис.18. Результат выполнения функции task9.

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

CREATE OR REPLACE FUNCTION task10(integer, char, char) RETURNS TABLE(part float)

AS $$

BEGIN

RETURN QUERY

SELECT (SELECT COUNT(*) FROM cameras

WHERE cameras.cost < $1

AND cameras.sup_id = (SELECT id from supp WHERE supp.name = $2)

AND cameras.country = (SELECT countries.id FROM countries WHERE trim(countries.name) = $3))::real

/

(SELECT COUNT(*) FROM cameras);

END;

$$ LANGUAGE plpgsql;



Рис.19. Результат выполнения функции tas10.

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

CREATE OR REPLACE FUNCTION task11(char, char, date, date) RETURNS TABLE(cnt bigint)

AS $$

BEGIN

RETURN QUERY

SELECT COUNT(*) from cameras

WHERE cameras.damaged = true

AND cameras.country = (SELECT countries.id FROM countries WHERE trim(countries.name) = $1 limit 1)

AND cameras.sup_id = (SELECT id from supp WHERE supp.name = $2)

AND cameras.date_out BETWEEN $3 AND $4;

END;

$$ LANGUAGE plpgsql;


Рис.20. Результат выполнения функции tas11.

12. 
    Найти среднюю стоимость фотоаппаратов, проданных за определенный промежуток времени (ввод интервала).
    

---

CREATE OR REPLACE FUNCTION task12(date, date) RETURNS TABLE(price numeric)

AS $$

BEGIN

RETURN QUERY

SELECT AVG(cameras.cost) FROM cameras

WHERE cameras.date_sell BETWEEN $1 AND $2;

END;

$$ LANGUAGE plpgsql;



Рис.21. Результат выполнения функции tas12.

13. 
    Найти все фотоаппараты, чья стоимость выше, чем средняя стоимость фотоаппаратов заданного производителя (выбор производителя).
    

CREATE OR REPLACE FUNCTION task13(char) RETURNS TABLE(id integer, firm text, model text, price integer, avgc numeric)

AS $$

BEGIN

RETURN QUERY

SELECT cameras.id, trim(firm.name), trim(model.name), cameras.cost, (SELECT AVG(cost) FROM cameras WHERE cameras.model_id IN (SELECT model.id FROM model

WHERE firm_id = (SELECT firm.id FROM firm WHERE firm.name = $1)))

FROM cameras, firm, model

WHERE cameras.model_id = model.id AND model.firm_id = firm.id

AND cameras.cost

>

(SELECT AVG(cost) FROM cameras WHERE cameras.model_id IN (SELECT model.id FROM model

WHERE firm_id = (SELECT firm.id FROM firm WHERE firm.name = $1))) group by cameras.id,firm.name,model.name;

END;

$$ LANGUAGE plpgsql;




Рис.22. Результат выполнения функции tas13.

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

По имени
CREATE OR REPLACE FUNCTION task1_1() RETURNS TABLE(id integer, type part_type, price integer, sup text, mod text)

AS $$

BEGIN

RETURN QUERY

SELECT parts.id, parts.type, parts.cost, trim(supp.name), trim(model.name)

FROM parts, supp, model

WHERE parts.sup_id = supp.id AND parts.model_id = model.id

ORDER BY parts.type;

END;

$$ LANGUAGE plpgsql;

-

по стоимости
CREATE OR REPLACE FUNCTION task1_2() RETURNS TABLE(id integer, type part_type, price integer, sup text, mod text)

AS $$

BEGIN

RETURN QUERY

SELECT parts.id, parts.type, parts.cost, trim(supp.name), trim(model.name)

FROM parts, supp, model

WHERE parts.sup_id = supp.id AND parts.model_id = model.id

ORDER BY parts.cost;

end;

$$ LANGUAGE plpgsql;

-

по поставщику
CREATE OR REPLACE FUNCTION task14_3() RETURNS TABLE(id integer, type part_type, price integer, sup text, mod text)

AS $$

BEGIN

RETURN QUERY

SELECT parts.id, parts.type, parts.cost, trim(supp.name), trim(model.name)

FROM parts, supp, model

WHERE parts.sup_id = supp.id AND parts.model_id = model.id

ORDER BY supp.name;

END;

$$ LANGUAGE plpgsql;


Рис.22. Результат выполнения функции task1_1.


Рис.23. Результат выполнения функции task1_2.



Рис.24. Результат выполнения функции task14_3.

15. 
    Найти поставщиков заданного вида запасных частей (выбор) для заданного вида фотоаппаратов (выбор модели).
    

CREATE OR REPLACE FUNCTION task15(part_type, char) RETURNS TABLE(name text, price integer)

---

AS $$

BEGIN

RETURN QUERY

SELECT distinct trim(supp.name), parts.cost

FROM supp, parts, model

WHERE supp.id = parts.sup_id

AND parts.type = $1

AND parts.model_id = (SELECT id FROM model WHERE

trim(model.name) = $2);

END;

$$ LANGUAGE plpgsql;


Рис.25. Результат выполнения функции task15.

Создание ролей для БД:

create role operatorBD login;

grant select, insert on countries, firm to operatorBd;

create role userBD login;

grant select, insert, update on all tables in schema public to userBd;

create role adminBD login;

grant all privileges on all tables in schema public to adminBd;

Демонстрация работы ролей:



Рис.26. Демонстрация работы роли operatorBD.



Рис.27. Демонстрация работы роли userrBD.



Рис.28. Демонстрация работы роли adminBD.

Руководство пользователя (описание функций)

Функция	Аргументы	Описаний
by_out()		список, отсортированный: по дате выпуска
by_supname()		список, отсортированный: по алфавиту
by_cost()		список, отсортированный: по стоимости
by_sell()		список, отсортированный: по продажам
max_price()		самый дорогой фотоаппарат.
min_price()		самый дешевый
avg_price()		Средняя стоимость
task3	integer, integer	фотоаппараты с ценой в заданных пределах (предусмотреть ввод цены с клавитары)
task4	char	все фотоаппараты заданного производителя (выбор).
task5	date	все фотоаппараты, с заданной датой выпуска (ввод даты).
task6	integer, integer, char	все фотоаппараты, чья стоимость находится в заданных пределах (ввод интервала) для заданного производителя (выбор).
task7	date, date	доля фотоаппаратов, проданных за определенный период (ввод периода) от общего времени продажи
task8()		самая популярная модель фотоаппарата (продано наибольшее количество)
task9	char, char, char	все фотоаппараты, поступившие от заданного поставщика (выбор поставщика), чья стоимость больше, чем средняя стоимость заданного фотоаппарата (ввод марки фотоаппарата), поступившего из заданной страны (ввод наименования страны).
task10	integer, char, char	доля дешевых фотоаппаратов (чья стоимость меньше заданной, ввод стоимости), поступивших от заданного поставщика (выбор поставщика) из заданной страны (выбор страны) и в целом.
task11	char, char, date, date	количество бракованных фотоаппаратов, поступивших из заданной страны (выбор страны) для заданного поставщика (выбор поставщика) за определенный промежуток времени (ввод интервала).
task12	date, date	средняя стоимость фотоаппаратов, проданных за определенный промежуток времени (ввод интервала).
task13	char	все фотоаппараты, чья стоимость выше, чем средняя стоимость фотоаппаратов заданного производителя (выбор производителя).
task1_1()		виды запасных частей, поступающих для ремонта фотоаппаратов, отсортировать по наименованиям, по стоимости, по поставщику. По имени
task1_2()		виды запасных частей, поступающих для ремонта фотоаппаратов, отсортировать по наименованиям, по стоимости, по поставщику. по стоимости
task14_3()		виды запасных частей, поступающих для ремонта фотоаппаратов, отсортировать по наименованиям, по стоимости, по поставщику. по поставщику
task15	part_type, char	поставщики заданного вида запасных частей (выбор) для заданного вида фотоаппаратов (выбор модели).

---

Вывод

В ходе выполнения курсовой работы была реализована база данных «Фотоаппараты» с использованием СУБД PostgreSQL и процедурного языка pgplsql, и сделаны различные выборки по этой базе. Для реализации этой работы были использованы знания, навыки и приемы разработки архитектуры баз данных, написания запросов и функций. Была изучена часть технической документации, предоставленной на официальном сайте СУБД PostgreSQL, опыта разработки баз данных стало больше.

Список использованных источников

1. Б. А. Новиков, Е. А. Горшкова Основы технологий баз данных. - 2-е изд. - М.: ДМК Пресс, 2020. - 582 с.
2. Моргунов, Е. П. PostgreSQL. Основы языка SQL. - СПБ.: БХВ-Петербург, 2018. - 336 с.
3. postgresql // postgresql URL: https://www.postgresql.org/ (Дата обращения: 25.12.2022)

---