МИНИСТЕРСТВО НАУКИ И ВЫСШЕГО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ
ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ОБРАЗОВАНИЯ
«НОВОСИБИРСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ
УНИВЕРСИТЕТ»
Кафедра вычислительной техники
Отчет
По Курсовой работе
По дисциплине: «Базы данных»
Тема: «Фотоаппараты»
Факультет: АВТФ
Группа: АВТ-043
Студент: Логутов Р.Ю.
Преподаватель: Трошина Г.В.
Новосибирск 2022.
Оглавление
1.Введение 3
2.Задание 4
6
2 Ход работы 6
Руководство пользователя (описание функций) 32
Вывод 35
Список использованных источников 36
Введение
Актуальность:
Любая современная организация не может обойтись без базы данных. Это учебные заведения, банки, магазины, заводы, любые предприятия и государственные учреждения. Они используют их для перевода данных в электронный вид и объединения данных, а также оперативного доступа к ним. Это позволяет экономить время и средства на затраты.
Конечно, снижение времени является лишь побочным эффектом автоматизации. Самая главная задача развития информационных технологий в совсем другом - в приобретении той или иной организацией исключительно новых качеств, придающих ей существенную конкурентоспособность. А это дорогого стоит.
К тому же, сейчас установка и управление базы данных не является таким уж и трудным процессом, как это было десятилетие назад. Когда проектирование и управление базами данных были не автоматизированы. Система управления базой данных позволяет создавать базу данных, обновляя в ней хранимую информацию, обеспечивая оперативный доступ к ней для просмотра и поиска информации.
Цель:
Реализовать базу данных «Фотоаппараты» с использованием СУБД PostgreSQL и процедурного языка pgplsql, и сделать различные выборки по этой базе.
Задание
Основная база данных «Фотоаппараты». Предусмотреть: ввод данных, редактирование, просмотр данных. Реализовать следующие типы запросов с использованием нескольких таблиц.
Для каждого фотоаппарата указать сведения о нем (наименование, дату выпуска, поставщик, цена и другие функции).
Для каждого фотоаппарата выдать список, отсортированный:
По дате выпуска
В алфавитном порядке
По стоимости
По дате продажи
Найти самый дорогой фотоаппарат, самый дешевый, среднюю стоимость.
Найти фотоаппараты с ценой в заданных пределах (предусмотреть ввод цены с клавиатуры).
Найти все фотоаппараты заданного производителя (выбор).
Найти все фотоаппараты с заданной датой выпуска (ввод даты).
Найти все фотоаппараты, чья стоимость находится в заданных пределах (ввод интервала) для заданного производителя (выбор).
Найти долю фотоаппаратов, проданных за определенный период (ввод периода) от общего времени продажи.
Найти самую популярную модель фотоаппарата (продано наибольшее количеств).
Найти все фотоаппараты, поступившие от заданного поставщика (выбор поставщика), чья стоимость больше, чем средняя стоимость заданного фотоаппарата (ввод марки фотоаппарата), поступившего из заданной страны (ввод наименования страны).
Найти долю дешевых фотоаппаратов (чья стоимость меньше заданной, ввод стоимости), поступивших от заданного поставщика (выбор поставщика) из заданной страны (выбор страны) и в целом.
Найти количество бракованных фотоаппаратов, поступивших из заданной страны (выбор страны) для заданного поставщика (выбор поставщика) за определенный промежуток времени (ввод интервала).
Найти среднюю стоимость фотоаппаратов, проданных за определенный промежуток времени (ввод интервала).
Найти все фотоаппараты, чья стоимость выше, чем средняя стоимость фотоаппаратов заданного производителя (выбор производителя).
Найти виды запасных частей, поступающих для ремонта фотоаппаратов, отсортировать по наименованиям, по стоимости, по поставщику.
Найти поставщиков заданного вида запасных частей (выбор) для заданного вида фотоаппаратов (выбор модели).
Рис.1. UML диаграмма БД.
2 Ход работы
Создание базы данных в среде postgresql:
create database photo;
Создание необходимых таблиц и их заполнение:
create table countries(id serial primary key, name char(50));
Рис.2. Таблица стран.
create table firm(id serial primary key, name char(50));
Рис.3. Таблица фирм.
create table supp( id serial primary key, name char(50));
Рис.4. Таблица поставщиков.
create table cameras(id serial primary key, model_id int references model(id), sup_id int references supp(id), country int references countries(id), date_out date, date_sell date, cost int, damaged boolean);
Рис.5. Таблица камер.
create table parts(id serial primary key, model_id int references model(id), sup_id int references supp(id), type part_type, cost int);
Рис.5. Таблица запасных частей.
Выполнение заданий:
Для каждого фотоаппарата выдать список, отсортированный:
По дате выпуска
CREATE OR REPLACE FUNCTION by_out() RETURNS TABLE (id integer, firm char(50), name char(50), cost int, country char(50), date_out date, date_sell date, supplyer char(50))
AS $$
BEGIN
RETURN QUERY SELECT cameras.id, firm.name, model.name, cameras.cost, countries.name, cameras.date_out, cameras.date_sell, supp.name
FROM cameras, firm, model, countries, supp
WHERE cameras.model_id = model.id AND cameras.sup_id = supp.id AND cameras.country = countries.id AND model.firm_id = firm.id
ORDER BY cameras.date_out;
END;
$$ LANGUAGE plpgsql;
Рис.6. Результат выполнения функции by_out().
В алфавитном порядке
CREATE OR REPLACE FUNCTION by_supname() RETURNS TABLE (id integer, firm char(50), name char(50), cost int, country char(50), date_out date, date_sell date, supplyer char(50))
AS $$
BEGIN
RETURN QUERY SELECT cameras.id, firm.name, model.name, cameras.cost, countries.name, cameras.date_out, cameras.date_sell, supp.name
FROM cameras, firm, model, countries, supp
WHERE cameras.model_id = model.id AND cameras.sup_id = supp.id AND cameras.country = countries.id AND model.firm_id = firm.id
ORDER BY supp.name;
END;
$$ LANGUAGE plpgsql;
Рис.7. Результат выполнения функции by_supname().
По стоимости
CREATE OR REPLACE FUNCTION by_cost() RETURNS TABLE (id integer, firm char(50), name char(50), cost int, country char(50), date_out date, date_sell date, supplyer char(50))
AS $$
BEGIN
RETURN QUERY SELECT cameras.id, firm.name, model.name, cameras.cost, countries.name, cameras.date_out, cameras.date_sell, supp.name
FROM cameras, firm, model, countries, supp
WHERE cameras.model_id = model.id AND cameras.sup_id = supp.id AND cameras.country = countries.id AND model.firm_id = firm.id
ORDER BY cameras.cost;
END;
$$ LANGUAGE plpgsql;
Рис.8. Результат выполнения функции by_cost().
По дате продажи
CREATE OR REPLACE FUNCTION by_sell() RETURNS TABLE (id integer, firm char(50), name char(50), cost int, country char(50), date_out date, date_sell date, supplyer char(50))
AS $$
BEGIN
RETURN QUERY SELECT cameras.id, firm.name, model.name, cameras.cost, countries.name, cameras.date_out, cameras.date_sell, supp.name
FROM cameras, firm, model, countries, supp
WHERE cameras.model_id = model.id AND cameras.sup_id = supp.id AND cameras.country = countries.id AND model.firm_id = firm.id
ORDER BY cameras.date_sell;
END;
$$ LANGUAGE plpgsql;
Рис.9. Результат выполнения функции by_sell().
Найти самый дорогой фотоаппарат, самый дешевый, среднюю стоимость
CREATE FUNCTION max_price() RETURNS TABLE (id integer, firm char(50), model char(50), cost integer)
AS $$
BEGIN
RETURN QUERY
SELECT cameras.id, firm.name, model.name, cameras.cost
FROM cameras, firm, model
WHERE cameras.model_id = model.id AND model.firm_id = firm.id AND cameras.cost = (SELECT MAX(cameras.cost) from cameras);
END;
$$ LANGUAGE plpgsql;
Рис.10. Результат выполнения функции max_pricel().
CREATE FUNCTION min_price() RETURNS TABLE (id integer, firm char(50), model char(50), cost integer)
AS $$
BEGIN
RETURN QUERY
SELECT camera.id, firm.name, model.name, cameras.cost
FROM cameras, firm, model
WHERE cameras.model_id = model.id AND model.firm_id = firm.id AND cameras.cost = (SELECT MIN(cameras.cost) from cameras);
END;
$$ LANGUAGE plpgsql;
Рис.11. Результат выполнения функции min_pricel().
CREATE OR REPLACE FUNCTION avg_price() RETURNS TABLE (average_cost numeric)
AS $$
BEGIN
RETURN QUERY
SELECT AVG(cameras.cost) from cameras;
END;
$$ LANGUAGE plpgsql;
Рис.12. Результат выполнения функции avg_pricel().
Найти фотоаппараты с ценой в заданных пределах (предусмотреть ввод цены с клавиатуры)
CREATE FUNCTION task3(integer, integer) RETURNS TABLE (id integer, firm char(50), name char(50), cost int, country char(50),
date_out date, date_sell date, supplyer char(50))
AS $$
BEGIN
RETURN QUERY
SELECT cameras.id, firm.name, model.name, cameras.cost, countries.name, cameras.date_out, cameras.date_sell, supp.name
FROM cameras, firm, model, countries, supp
WHERE cameras.model_id = model.id AND cameras.sup_id = supp.id AND cameras.country = countries.id AND model.firm_id =
firm.id AND cameras.cost BETWEEN $1 AND $2;
END;
$$ LANGUAGE plpgsql;
Рис.12. Результат выполнения функции task3.
Найти все фотоаппараты заданного производителя (выбор).
CREATE FUNCTION task4(char) RETURNS TABLE (id integer, firm char(50), name char(50), cost int, country char(50),
date_out date, date_sell date, supplyer char(50))
AS $$
BEGIN
RETURN QUERY
SELECT cameras.id, firm.name, model.name, cameras.cost, countries.name, cameras.date_out, cameras.date_sell, supp.name
FROM cameras, firm, model, countries, supp
WHERE cameras.model_id = model.id AND cameras.sup_id = supp.id AND cameras.country = countries.id AND model.firm_id =
firm.id AND firm.name = $1;
END;
$$ LANGUAGE plpgsql;
Рис.13. Результат выполнения функции task4.
Найти все фотоаппараты, с заданной датой выпуска (ввод даты).
CREATE FUNCTION task5(date) RETURNS TABLE (id integer, firm char(50), name char(50), cost int, country char(50),
date_out date, date_sell date, supplyer char(50))
AS $$
BEGIN
RETURN QUERY
SELECT cameras.id, firm.name, model.name, cameras.cost, countries.name, cameras.date_out, cameras.date_sell, supp.name
FROM cameras, firm, model, countries, supp
WHERE cameras.model_id = model.id AND cameras.sup_id = supp.id AND cameras.country = countries.id AND model.firm_id =
firm.id AND cameras.date_out = $1;
END;
$$ LANGUAGE plpgsql;
Рис.14. Результат выполнения функции task5.
Найти все фотоаппараты, чья стоимость находится в заданных пределах (ввод интервала) для заданного производителя (выбор).
CREATE FUNCTION task6(integer, integer, char) RETURNS TABLE (id integer, firm char(50), name char(50), cost int, country char(50),
date_out date, date_sell date, supplyer char(50))
AS $$
BEGIN
RETURN QUERY
SELECT cameras.id, firm.name, model.name, cameras.cost, countries.name, cameras.date_out, cameras.date_sell, supp.name
FROM cameras, firm, model, countries, supp
WHERE cameras.model_id = model.id AND cameras.sup_id = supp.id AND cameras.country = countries.id AND model.firm_id =
firm.id AND cameras.cost BETWEEN $1 AND $2 AND firm.name = $3;
END;
$$ LANGUAGE plpgsql;
Рис.15. Результат выполнения функции task6.
Найти долю фотоаппаратов, проданных за определенный период (ввод периода) от общего времени продажи
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;
Рис.16. Результат выполнения функции task7.
Найти самую популярную модель фотоаппарата (продано наибольшее количество)
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.
Найти все фотоаппараты, поступившие от заданного поставщика (выбор поставщика), чья стоимость больше, чем средняя стоимость заданного фотоаппарата (ввод марки фотоаппарата), поступившего из заданной страны (ввод наименования страны).
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.
Найти долю дешевых фотоаппаратов (чья стоимость меньше заданной, ввод стоимости), поступивших от заданного поставщика (выбор поставщика) из заданной страны (выбор страны) и в целом.
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.
Найти количество бракованных фотоаппаратов, поступивших из заданной страны (выбор страны) для заданного поставщика (выбор поставщика) за определенный промежуток времени (ввод интервала).
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.
Найти среднюю стоимость фотоаппаратов, проданных за определенный промежуток времени (ввод интервала).
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.
Найти все фотоаппараты, чья стоимость выше, чем средняя стоимость фотоаппаратов заданного производителя (выбор производителя).
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.
Найти виды запасных частей, поступающих для ремонта фотоаппаратов, отсортировать по наименованиям, по стоимости, по поставщику.
По имени
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.
Найти поставщиков заданного вида запасных частей (выбор) для заданного вида фотоаппаратов (выбор модели).
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)
|
Скачать 1.3 Mb.