Примеры использования БД

Запрос, с помощью которого выводится информация о книгах, в том числе, и из родительских таблиц (Улица, Город, Адрес, Клиент):

SELECT cl. id_client, cl. fio, c. city_name, s. street_name, a. house_number, a. apartament_number, cl. phone, cl.discount.

FROM address a, street s, city c, client cl.

WHERE a. id_city=c.id_city AND a. id_street = s. id_street AND cl. id_address=a.id_address;

Рисунок 5 — Результат выполнения первого запроса.

Запрос, с помощью которого выводится информация о заказах:

SELECT o. id_order, c. fio, g.name, r. quantity, o. cost, o.executed.

FROM orders o, client c, row_order r, goods g.

WHERE r. id_goods=g.id_goods AND r. id_order=o.id_order AND o. id_client=c.id_client;

Рисунок 6 — Результат выполнения второго запроса.

Запрос, с помощью которого выводится информация о поставках:

SELECT d. id_delivery,.

• (SELECT e. fio FROM employee e WHERE e. id_employee=d.forwarding_agent) AS forwarding_agent,
• (SELECT e. fio FROM employee e WHERE e. id_employee=d.driver) AS driver,

o.id_order, o.executed.

FROM delivery d JOIN row_delivery r USING (id_delivery) JOIN orders o USING (id_order);

Рисунок 7 — Результат выполнения третьего запроса.

Проанализируем работу триггеров.

Триггер вычисления стоимости заказа и триггер запрета добавления товара при отсутствии его на складе, проверим добавив запись в таблицу «строка заказа»:

Рисунок 9 — Таблица «Заказ» до внесения новой записи.

Рисунок 10 — Добавление новой записи в таблицу «Строка заказа».

Рисунок 11 — Автоматическое изменение стоимости в таблице «Заказ».

Как видно из рисунков, при попытке добавления товара которого нет на складе будет выведено исключение. При повторной попытке (с изменением количества товара), сообщения выведено не будет и в таблице «Заказ» изменится стоимость заказа.

Теперь проверим работу триггеров на ссылочную целостность для таблиц «Адрес», «Город», «Улица».

Рисунок 12 — Попытка добавить новый адрес с несуществующим городом в таблицу «Адрес».

Рисунок 13 — Попытка добавить новый адрес с несуществующей улицей в таблицу «Адрес».

Рисунок 14 — Попытка удаления города на который есть ссылка в таблице «Адрес».

Рисунок 15 — Попытка удаления улицы на которую есть ссылка в таблице «Адрес».

Проверим правильность срабатывания триггера на запрет добавления «Заказа» от клиента, если есть 5 и более непогашенных заявок:

Проанализируем работу хранимых процедур.

Выполнение процедуры вывода всех непогашенных заказов вместе с товаром, их количеством и сотрудников которые их выполняют.

SELECT * FROM not_executed;

Рисунок 17 — Результат выполнения процедуры вывода всех непогашенных заказов.

Выполнение процедуры вывода города, улицы, дома, квартиры, и человека который проживает по данному адресу: SELECT * FROM view_address;

Рисунок 18 — Результат выполнения процедуры вывода всех адресов клиентов.

Выполнение процедуры расчета и занесения скидки для клиента в зависимости от суммы потраченной им на товары:

EXECUTE PROCEDURE calculate_discount;

Рисунок 19 — Результат выполнения процедуры просчета скидки для клиентов.

Рисунок 20 — Изменения в таблице «Клиент».

Проверка ролей пользователей.

1. Пользователи группы «Гость», имеющие права гостя, а именно на просмотр данных о товарах.

Пользователь Guest, подключившийся к БД с указанием роли MANAGER_ROLE, не имеет прав доступа к таблицам:

Рисунок 21 — Отказ в доступе пользователю Guest.

2. Пользователи группы «Менеджеры», имеющие права на работу с данными во всех таблицах, кроме «Адрес», «Сотрудники».

Результат выполнения запроса пользователем «maneger» с указанием роли «MANAGER_ROLE»:

SELECT * FROM client;

Рисунок 22 — Результат выполнения запроса пользователем «maneger».

Результат выполнения процедуры вывода адресов клиентов пользователем «maneger»:

SELECT * FROM view_address;

Рисунок 23 — Результат выполнения процедуры вывода адресов клиентов пользователем «maneger».

3. Пользователи группы «Экспедиторы», имеющие права на работу с данными в таблицах «Заказ», «Строка заказа», «Поставка».

Проверим доступность пользователю FORWARDING_AGENT, подключившемуся к БД с указанием роли FORWARDING_AGENT_role, выполнения процедуры вывод всех непогашенных заявок:

SELECT * FROM not_executed;

Рисунок 24 — Результат выполнения процедуры вывода всех непогашенных заказов.