База данных "Компьютерный магазин"
Глобальная логическая и физическая ER - модели исходной и модифицированной базы данных. Описание структуры базы данных "Компьютерный магазин". SQL сценарии по созданию триггеров, хранимых процедур. Скрипты и их описание. Примеры использования БД.
| Рубрика | Программирование, компьютеры и кибернетика |
| Вид | курсовая работа |
| Язык | русский |
| Дата добавления | 16.01.2018 |
| Размер файла | 1,3 M |
Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже
Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.
Размещено на http://www.allbest.ru/
Аннотация
В курсовой работе проводится разработка программ по сопровождению базы данных «Компьютерный магазин».
В данной пояснительной записке излагается постановка задачи, приводятся описание предметной области, логическая и физическая модели базы данных. Приводятся SQL сценарии по созданию триггеров, хранимых процедур и SQL сценарии по модификации разработанной базы данных и наделению пользователей, работающих с базой данных, различными привилегиями и правами доступа.
Содержание
- Введение
- 1. Описание предметной области
- 2. Разработка ER-моделей базы данных
- 2.1 Глобальная логическая ER - модель исходной базы данных
- 2.2 Глобальная физическая ER - модель исходной базы данных
- 3. Глобальные логическая и физическая модели модифицированной базы данных
- 3.1 Логическая ER-модель модифицированной базы данных
- 3.2 Физическая ER-модель модифицированной базы данных
- 4. Описание структуры базы данных
- 5. Скрипты и их описание
- 5.1 Скрипт модификации БД
- 5.2 Скрипт по созданию триггеров
- 5.3 Скрипт по созданию хранимых процедур
- 5.4 Скрипт по созданию ролей и присвоению им прав
- 6. Примеры использования БД
- Заключение
- Список литературы
- Лист регистрации изменений
Введение
Неотъемлемой частью современной повседневной жизни стали базы данных, для поддержки которых требуется некоторый организационный метод, или механизм. Такой механизм называется системой управления базами данных (СУБД).
База данных (БД) - организованная в соответствии с определенными правилами совокупность данных, характеризующая состояние некоторой предметной области и используемая для удовлетворения информационных потребностей пользователей.
Система управления базами данных (СУБД) - программная система, предназначенная для создания и хранения базы данных, предоставления к ней санкционированного доступа, а также для поддержки функций администратора базы данных.
Ключевую роль при обеспечении эффективного хранения данных играют методы поддержания логических связей между данными. По способам организации связей выделяют различные модели данных. Используемая в данной курсовой работе СУБД Firebird, как и подавляющее большинство современных СУБД, относится к реляционным системам.
Реляционной называется база данных, в которой все данные, доступные пользователю, организованы в виде прямоугольных таблиц, а все операции над данными сводятся к операциям над этими таблицами.
1. Описание предметной области
В настоящее время очень много фирм по реализации компьютеров. Комплектующие заказываются у оптовых фирм, затем собираются в помещении фирмы или в специально отведенных помещениях. Так же часть комплектующих продаются отдельно (без сборки).
При покупке товара заказчик указывает в заявке: ФИО, артикул изделия, количество.
Товар характеризуется: артикул, наименование, цена.
Такая фирма располагает информацией об имеющихся у нее товарах, клиентах и заказах каждого из них. Эти данные могут храниться как в виде различных документов, так и на компьютере.
В результате получается, что каждому клиенту соответствуют заказы, сделанные им в этой фирме.
Сам заказ состоит из нескольких товаров в разном количестве, поэтому желательно иметь возможность автоматически рассчитывать суммарную стоимость заказа.
2. Разработка ER-моделей базы данных
база данные триггер скрипт
Для разработки модели базы данных использовался программный продукт ERWin 4.0.
В результате проектирования были созданы две модели - логическая и физическая.
В логической ER-модели все объекты системы именуются на языке, понятном для любого пользователя, работающего с данной моделью.
Физическая ER-модель содержит описание реальной базы данных в терминах конкретной СУБД. В физической модели создаются домены, типы данных, используемые в СУБД. В данной курсовой работе используется СУБД FireBird 2.5 и средство администрирования IBExpert v2009.02.03.
2.1 Глобальная логическая ER - модель исходной базы данных
Указанной предметной области соответствует ER-модель, представленная на рисунке 1:
Рисунок 1 - Глобальная логическая ER-модель исходной базы данных
2.2 Глобальная физическая ER - модель исходной базы данных
Анализируя предметную область и глобальную логическую ER-модель, а также используя знания о типах данных, поддерживаемых СУБД FireBird 2.5, получаем глобальную физическую ER-модель, представленную на рисунке 2:
Рисунок 2 - Глобальная физическая ER-модель исходной базы данных
3. Глобальные логическая и физическая модели модифицированной базы данных
3.1 Логическая ER-модель модифицированной базы данных
Выполнив модификации, описанные в задании, получим следующую логическую ER-модель модифицированной базы данных:
Рисунок 3 - Логическая ER-модель модифицированной базы данных
3.2 Физическая ER-модель модифицированной базы данных
Физическая ER-модель модифицированной базы приведена на рисунке 4:
Рисунок 4 - Физическая ER-модель модифицированной базы данных
4. Описание структуры базы данных
Домены:
Таблица 1 - Домены
|
Домен |
Тип |
По умолчанию |
Описание |
|
|
MONEY |
NUMERIC(15,2) |
Вещественный тип, имеющий 15 знаков до и 2 знака после запятой. |
||
|
Домен предназначен для определения в таблицах полей, хранящих денежные суммы. |
||||
|
TRANSFER |
INTEGER |
Домен предназначен для определения в таблицах полей, имеющих не отрицательные значения |
Назначение полей таблицы Address (Адрес)
Таблица 2 - Поля таблицы «Адрес»
Назначение полей таблицы City (Город)
Таблица 3 - Поля таблицы «Город»
Назначение полей таблицы Client (Клиент)
Таблица 4 - Поля таблицы «Клиент»
Назначение полей таблицы Delivery (Поставка)
Таблица 5 - Поля таблицы «Поставка»
Назначение полей таблицы Employee (Сотрудники)
Таблица 6 - Поля таблицы «Сотрудники»
Назначение полей таблицы Goods (Товар)
Таблица 7 - Поля таблицы «Товар»
Назначение полей таблицы Orders (Заказ)
Таблица 8 - Поля таблицы «Заказ»
Назначение полей таблицы Post (Должность)
Таблица 9 - Поля таблицы «Должность»
Назначение полей таблицы Row_delivery (Строка поставки)
Таблица 10 - Поля таблицы «Строка поставки»
Назначение полей таблицы Row_order (Строка заказа)
Таблица 11 - Поля таблицы «Строка заказа»
Назначение полей таблицы Street (Улица)
Таблица 12 - Поля таблицы «Улица»
5. Скрипты и их описание
5.1 Скрипт модификации БД
Цель модификации - улучшение структуры БД, оптимизация ее использования.
Для того, чтобы модифицировать исходную базу данных без потери имеющейся в ней информации, необходимо выполнить следующие действия:
/*домен для денег*/
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 integ_24,
DROP id_group;
DROP TABLE composition_group;
DROP TABLE working_group;
/******************************************/
/*4. специализацию переименовать в должность*/
/******************************************/
ALTER TABLE employee DROP CONSTRAINT integ_22;
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 integ_23,
DROP id_street,
DROP house_number;
ALTER TABLE client DROP CONSTRAINT integ_18,
DROP CONSTRAINT integ_19,
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;
5.2 Скрипт по созданию триггеров
Для обеспечения удобства работы и семантической целостности данных в БД необходимо создать следующие триггеры:
1. На таблицу Строка заказа при модификации строки таблицы - изменение стоимости в таблице Заказ.
2. Целостность данных на справочники.
3. Запрет добавления Товара в Строку заказа если Товара нет в наличии.
Запрет добавления Заказа от клиента, если есть 5 и более непогашенных заявок.
/************************************************************/
/*Тригер - изменение стоимости в зависимости от добавления, изменения, удаления */
/************************************************************/
SET TERM !! ;
CREATE OR ALTER TRIGGER CALCULATE_COST
FOR row_order
ACTIVE
AFTER INSERT OR DELETE OR UPDATE POSITION 1
AS
DECLARE newcost money;
DECLARE price money;
DECLARE id_goods INTEGER;
DECLARE quantity transfer;
DECLARE id_order INTEGER;
BEGIN
IF (INSERTING) THEN
BEGIN
SELECT g.price FROM goods g WHERE g.id_goods= NEW.id_goods
INTO :price;
newcost = :price* NEW.quantity;
UPDATE orders o
SET o.cost = o.cost + :newcost
WHERE o.id_order = NEW.id_order;
END
IF (DELETING) THEN
BEGIN
SELECT g.price FROM goods g WHERE g.id_goods= OLD.id_goods
INTO :price;
newcost = :price* OLD.quantity;
UPDATE orders o
SET o.cost = o.cost - :newcost
WHERE o.id_order = OLD.id_order;
END
IF (UPDATING) THEN
BEGIN
FOR
SELECT o.id_order,coalesce(SUM(r.quantity * g.price),0)
FROM (row_order r JOIN goods g USING(id_goods)) right join orders o using(id_order)
GROUP BY 1
INTO :id_order,:newcost
DO
BEGIN
UPDATE orders o
SET o.cost=:newcost
WHERE o.id_order=:id_order;
END
END
END !!
SET TERM ; !!
/************************************************************/
/* Запрет добавления Товара в Строку заказа если Товара нет в наличии */
/************************************************************/
CREATE EXCEPTION ri_in_row_order 'Tovar otsutstvuet na sklade';
SET TERM !! ;
CREATE OR ALTER TRIGGER ri_in_row_order
FOR row_order
ACTIVE
AFTER INSERT POSITION 0
AS
DECLARE VARIABLE presence INTEGER;
BEGIN
IF (INSERTING) THEN
BEGIN
SELECT g.presence FROM goods g WHERE g.id_goods= NEW.id_goods
INTO :presence;
IF ((:presence - NEW.quantity) >= '0') THEN
UPDATE goods g
SET g.presence = g.presence - NEW.quantity
WHERE g.id_goods = NEW.id_goods;
ELSE
EXCEPTION ri_in_row_order;
END
END!!
SET TERM ; !!
/************************************************************/
/*Запрет добавления Заказа от клиента, если есть 5 и более непогашенных заявок */
/************************************************************/
CREATE EXCEPTION ri_in_orders 'Uge est 5 ili bolee nevypolnenyx zakazov';
SET TERM !! ;
CREATE OR ALTER TRIGGER ri_in_orders
FOR orders
ACTIVE
BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE kol_vo INTEGER;
BEGIN
IF (INSERTING) THEN
BEGIN
SELECT COUNT(*) FROM orders o WHERE o.id_client = new.id_client AND o.executed = '0'
INTO :kol_vo;
IF (:kol_vo >= '5') THEN
EXCEPTION ri_in_orders;
END
END!!
SET TERM ; !!
/************************************************************/
/* Целостность данных на справочники(город,улица и адрес) */
/************************************************************/
CREATE EXCEPTION rig_in_adres 'Net goroda v BD';
CREATE EXCEPTION ris_in_adres 'Net ulicy v BD';
CREATE EXCEPTION rdg_in_city 'Gorod est v tablice ADRES';
CREATE EXCEPTION rds_in_street 'Ulica est v tablice ADRES';
SET TERM !! ;
CREATE OR ALTER TRIGGER ri_in_adres
FOR address
ACTIVE
BEFORE INSERT OR UPDATE POSITION 0
AS
DECLARE VARIABLE cit SMALLINT;
BEGIN
SELECT COUNT(*) FROM city c WHERE c.id_city = NEW.id_city
INTO :cit;
IF (:cit='0') THEN EXCEPTION rig_in_adres;
SELECT COUNT(*) FROM street s WHERE s.id_street = NEW.id_street
INTO :cit;
IF (:cit='0') THEN EXCEPTION ris_in_adres;
END!!
CREATE OR ALTER TRIGGER rd_in_city
FOR city
ACTIVE
BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE cit SMALLINT;
BEGIN
IF (DELETING) THEN
BEGIN
SELECT COUNT(*) FROM address a WHERE a.id_city = OLD.id_city
INTO :cit;
IF (:cit>'0') THEN EXCEPTION rdg_in_city;
END
END!!
CREATE OR ALTER TRIGGER rd_in_street
FOR street
ACTIVE
BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE cit SMALLINT;
BEGIN
IF (DELETING) THEN
BEGIN
SELECT COUNT(*) FROM address a WHERE a.id_street = OLD.id_street
INTO :cit;
IF (:cit>'0') THEN EXCEPTION rds_in_street;
END
END!!
SET TERM ; !!
5.3 Скрипт по созданию хранимых процедур
В базе данных необходимо создать следующие хранимые процедуры:
1. Вывести все непогашенные заказы вместе с товаром, их количеством и сотрудников которые их выполняют.
2. Расчет и занесение скидки для клиента в зависимости от суммы потраченной им на товары.
3. Вывести город, улицу, дом, квартиру, и человека который проживает по данному адресу.
/************************************************************/
/*Вывести все непогашенные заказы вместе с товаром, их количеством и сотрудников которые их выполняют */
/************************************************************/
/*Вводим поле "погашено" в таблицу заказ*/
ALTER TABLE orders ADD executed INTEGER CHECK ((executed IN (0,1)) and (executed is not null));
/*вводим для клиента поле скидка*/
ALTER TABLE client ADD discount INTEGER DEFAULT 0 CHECK (discount IS NOT NULL);
SET TERM !!;
CREATE PROCEDURE not_executed
RETURNS (id_order INTEGER,name VARCHAR(20),quantity INTEGER,
manager VARCHAR(20), seller VARCHAR(20),forwarding_agent VARCHAR(20),driver VARCHAR(20))
AS
BEGIN
FOR
SELECT o.id_order,r.quantity,g.name,
(SELECT e.fio FROM employee e WHERE e.id_employee=o.manager) AS manager,
(SELECT e.fio FROM employee e WHERE e.id_employee=o.seller) AS seller,
(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
FROM orders o,row_order r,goods g,row_delivery rd,delivery d
WHERE r.id_order=o.id_order AND r.id_goods=g.id_goods AND rd.id_order=o.id_order AND d.id_delivery=rd.id_delivery
AND o.executed = '0'
INTO :id_order,:quantity,:name,:manager,:seller,:forwarding_agent,:driver
DO
SUSPEND;
END!!
/************************************************************/
/*Расчет и занесение скидки для клиента в зависимости от суммы потраченной им на товары */
/************************************************************/
CREATE PROCEDURE calculate_discount
AS
DECLARE VARIABLE id_client INTEGER;
DECLARE VARIABLE summa money;
DECLARE VARIABLE discount INTEGER;
BEGIN
FOR
SELECT o.id_client,SUM(o.cost)
FROM orders o
GROUP BY o.id_client
INTO :id_client,:summa
DO
BEGIN
IF (:summa>'3000') THEN discount = '5'; ELSE
IF ((:summa<'10000')AND(:summa>'3000')) THEN discount = '7'; ELSE
IF (:summa>'10000') THEN discount = '10'; ELSE
discount = '0';
UPDATE client c
SET c.discount = :discount
WHERE c.id_client=:id_client;
END
END!!
/************************************************************/
/*Вывести город, улицу, дом, квартиру, и человека который проживает по данному адресу*/
/************************************************************/
CREATE PROCEDURE view_address
returns(fio varchar(20),city_name varchar(20),street_name varchar(20),house_number transfer,
apartament_number transfer)
AS
BEGIN
FOR
SELECT c.fio,ci.city_name,s.street_name,a.house_number,a.apartament_number
FROM client c,address a,city ci,street s
WHERE c.id_address=a.id_address AND a.id_city=ci.id_city AND a.id_street=s.id_street
INTO :fio,:city_name,:street_name,:house_number,:apartament_number
DO
suspend;
END!!
SET TERM ;!!
5.4 Скрипт по созданию ролей и присвоению им прав
Анализ предметной области выявил, что будет три пользователя:
· Гость - просмотр товаров.
· Менеджер не может просматривать адреса сотрудников и может добавлять новые заказы.
· Экспедитор может просматривать Заказ, Строку поставки и Поставку
Для каждого из пользователей создаем набор прав на доступ к объектам БД. Политику безопасности на доступ к объектам реализуем с использованием механизма SQL ролей:
/*создаем пользователей */
CREATE USER guest PASSWORD 'guest';
CREATE USER maneger PASSWORD 'manager';
CREATE USER FORWARDING_AGENT PASSWORD 'forwarding_agent';
/* создаем роли */
CREATE ROLE guest_role;
CREATE ROLE manager_role;
CREATE ROLE FORWARDING_AGENT_role;
/*назначаем права для роли-гостя*/
GRANT SELECT ON goods TO GUEST_ROLE;
/*назначаем права для роли-менеджера*/
/*создаем представление т.к. менеджер не может просматривать адреса сотрудников*/
CREATE VIEW address_manager AS
SELECT a.* FROM address a JOIN client c USING(id_address);
GRANT ALL ON CLIENT TO MANAGER_ROLE;
GRANT ALL ON DELIVERY TO MANAGER_ROLE;
GRANT ALL ON GOODS TO MANAGER_ROLE;
GRANT ALL ON ORDERS TO MANAGER_ROLE;
GRANT ALL ON ROW_DELIVERY TO MANAGER_ROLE;
GRANT ALL ON ROW_ORDER TO MANAGER_ROLE;
GRANT ALL ON ADDRESS_MANAGER TO MANAGER_ROLE;
GRANT ALL ON STREET TO MANAGER_ROLE;
GRANT ALL ON CITY TO MANAGER_ROLE;
GRANT SELECT ON employee TO PROCEDURE not_executed;
GRANT SELECT ON address TO PROCEDURE view_address;
GRANT EXECUTE ON PROCEDURE calculate_discount TO MANAGER_ROLE;
GRANT EXECUTE ON PROCEDURE not_executed TO MANAGER_ROLE;
GRANT EXECUTE ON PROCEDURE view_address TO MANAGER_ROLE;
/*назначаем права для роли-экспедитора*/
GRANT SELECT ON row_delivery TO FORWARDING_AGENT_role;
GRANT SELECT ON delivery TO FORWARDING_AGENT_role;
GRANT SELECT ON orders TO FORWARDING_AGENT_role;
GRANT SELECT ON address TO PROCEDURE view_address;
/*присваиваем польхователям их роли*/
GRANT guest_role TO guest;
GRANT MANAGER_ROLE TO maneger;
GRANT FORWARDING_AGENT_role TO FORWARDING_AGENT;
6. Примеры использования БД
Запрос, с помощью которого выводится информация о книгах, в том числе, и из родительских таблиц (Улица, Город, Адрес, Клиент):
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 - Результат выполнения третьего запроса
Проанализируем работу триггеров.
Триггер вычисления стоимости заказа и триггер запрета добавления товара при отсутствии его на складе, проверим добавив запись в таблицу «строка заказа»:
Рисунок 8 - Ошибка т.к товар отсутствует на складе
Рисунок 9 - Таблица «Заказ» до внесения новой записи
Рисунок 10 - Добавление новой записи в таблицу «Строка заказа»
Рисунок 11 - Автоматическое изменение стоимости в таблице «Заказ»
Как видно из рисунков, при попытке добавления товара которого нет на складе будет выведено исключение. При повторной попытке(с изменением количества товара), сообщения выведено не будет и в таблице «Заказ» изменится стоимость заказа.
Теперь проверим работу триггеров на ссылочную целостность для таблиц «Адрес», «Город», «Улица».
Рисунок 12 - Попытка добавить новый адрес с несуществующим городом в таблицу «Адрес»
Рисунок 13 - Попытка добавить новый адрес с несуществующей улицей в таблицу «Адрес»
Рисунок 14 - Попытка удаления города на который есть ссылка в таблице «Адрес»
Рисунок 15 - Попытка удаления улицы на которую есть ссылка в таблице «Адрес»
Проверим правильность срабатывания триггера на запрет добавления «Заказа» от клиента, если есть 5 и более непогашенных заявок:
Рисунок 16 - Результаты срабатывания триггера для таблицы «Заказ»
Проанализируем работу хранимых процедур.
Выполнение процедуры вывода всех непогашенных заказов вместе с товаром, их количеством и сотрудников которые их выполняют
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 - Результат выполнения процедуры вывода всех непогашенных заказов
Заключение
В результате выполнения курсовой работы был разработан комплекс программ по сопровождению базы данных ИС «Компьютерный магазин».
Были разработаны триггеры ссылочной целостности, поддерживающие бизнес правила фирмы. Созданы хранимые процедуры. Была произведена модификация, улучшающая хранение данных в БД. Были распределены права между пользователями.
Список литературы
1. Маркин А. В. Построение запросов и программирование на SQL. - М.: «Диалог-МИФИ», 2008, 320 с.;
2. Маркин А. В. Методические указания «IBEXPERT». - Рязань, РГРТА, 2007;
3. Борзых В. Е. Методические указания к теме «Создание баз данных». - Рязань, РГРТА, 2002;
Лист регистрации изменений
|
Лист регистрации изменений |
||||||||||
|
Номера листов (страниц) |
Всего листов (страниц) в документе |
№ документа |
Входящий номер сопроводительного документа |
Подпись |
Дата |
|||||
|
Изм |
Измененных |
Замененных |
Новых |
Аннулированных |
||||||
Размещено на Allbest.ru
...Подобные документы
Основные функции системы управления базами данных - описание структуры базы данных, обработка данных и управление данными. Компьютерный магазин как предметная область, ее технико-экономические характеристики. Построение логической и физической моделей.
курсовая работа [3,7 M], добавлен 02.07.2012Логическая и физическая структура базы данных. Аппаратное и программное обеспечение системы. Создание представлений, хранимых процедур, пользовательских функций, триггеров. Описание основной структуры ASP.NET документов. Пользовательский интерфейс.
курсовая работа [4,2 M], добавлен 21.05.2013Выбор средств разработки базы данных для информационного функционирования аэропорта. Выделение и нормализация сущностей. Логическая схема и физическая структура базы данных. Спецификация и тестирование функций, процедур, триггеров, представлений.
курсовая работа [1,5 M], добавлен 07.06.2013Разработка базы данных, позволяющей определять месторасположение на полке и код товаров в магазинных складах, количество и качество товаров. Концепция баз данных. Модели данных, описание данных проектирования. Разработка программного приложения.
курсовая работа [1,1 M], добавлен 13.06.2014Определение функциональных зависимостей. Разработка структуры базы данных. Организация запросов к базе данных. Использование триггеров для поддержки данных в актуальном состоянии. Разработка хранимых процедур и функций. Ограничения ведения базы данных.
курсовая работа [113,2 K], добавлен 17.06.2014Проектирование базы данных фирмы по предоставлению телекоммуникационных услуг с помощью СУБД MS SQL SERVER. Построение логической и физической модели данных. Описание информационных потребностей пользователя. Создание хранимых процедур и триггеров.
курсовая работа [2,3 M], добавлен 21.03.2015Реализация приложения "Книжный магазин" средствами систем управления базами данных. Проектирование структуры базы данных, определение сущности и атрибутов. Логическое проектирование базы данных и реализация базы данных в СУБД Microsoft Office Access.
курсовая работа [7,8 M], добавлен 13.02.2023Анализ предметной области и введение ограничений. Выделение базовых сущностей. Концептуальная модель данных. Построение схемы реляционной модели базы данных магазина одежды в третьей нормальной форме. Описание физической БД. Проектирование интерфейса.
курсовая работа [2,6 M], добавлен 20.11.2013Сущности и функциональные зависимости базы данных. Атрибуты и связи. Таблицы базы данных. Построение ER-диаграммы. Организация ввода и корректировки данных. Реляционная схема базы данных. Реализация запросов, получение отчетов. Защита базы данных.
курсовая работа [2,4 M], добавлен 06.02.2016Концептуальное и инфологическое проектирование базы данных в системе управления базами данных Microsoft Access. Физическое проектирование базы данных "Магазин спорттоваров". Тестирование и отладка базы данных, составление руководства пользователя.
курсовая работа [6,7 M], добавлен 22.11.2022Описание предметной области "Магазин по продаже компьютерных комплектующих". Построение ER и реляционной модели данных, сущности и связи. Создание ER и реляционной модели данных, запросов, представлений, хранимых процедур для предметной области.
курсовая работа [32,2 K], добавлен 15.06.2014Проектирование модели разрабатываемой базы данных гостиниц. Разработка триггеров, хранимых процедур, запросов. Создание пользовательского интерфейса. Автоматизация работы по регистрации, учету, поиску, а также по формированию отчетности о работодателях.
курсовая работа [4,7 M], добавлен 29.11.2015Создание базы данных в СУБД MS ACCESS 2007 для оптимизации продаж в музыкальном магазине. Описание предметной области, разработка инфологической модели данных. Пользовательский интерфейс: информация о товаре, производителях, продавцах, отчет по чекам.
курсовая работа [847,3 K], добавлен 10.02.2014Разработка базы данных в ErWIn. Сущности, логическая и физическая структуры базы данных "Работа деканата в вузе". Разработка и проверка хранимой процедуры. Создание пользовательской функции по формированию текстовой строки и триггеров к таблице "Студент".
контрольная работа [3,2 M], добавлен 30.11.2015Сущность базы данных - набора, совокупности файлов, в которых находится информация. Система управления базой данных - программная система (приложение), обеспечивающая работу с базой данных (файлами данных). Назначение и польза от применения триггеров.
курсовая работа [50,0 K], добавлен 22.02.2011Создание базы данных и СУБД. Структура простейшей базы данных. Особенности языка программирования Турбо Паскаль. Описание типов, констант, переменных, процедур и функций. Описание алгоритма базы данных (для сотрудников ГИБДД), листинг программы.
курсовая работа [26,3 K], добавлен 26.01.2012Физическая модель, определяющая размещение данных, методы доступа и технику индексирования. Описание и структура таблиц. Выбор среды программирования. Разработка интерфейса, результат работы запроса. Подбор комплектующих. Листинг основных процедур Unit1.
курсовая работа [1017,5 K], добавлен 12.09.2014Описание состава реляционной базы данных как системы связанной информации, сохраняемой в двумерных таблицах. Основные функции CMS и изучение структуры сервера MySQL. Разработка системы выборок данных по товарам для интернет-магазина, таблицы покупателей.
курсовая работа [2,0 M], добавлен 21.04.2015Освоение методов проектирования баз данных и работы с базами данных в среде СУБД. Ведение точного учета поступивших и реализованных товаров и определение их остатка с помощью БД "Оптовый магазин". Преимущества и недостатки спроектированной базы данных.
курсовая работа [4,8 M], добавлен 12.01.2015Автоматизация торговли, база данных. Модели представления данных, СУБД Microsoft Access. Инструменты для работы с данными в Access. Назначение проектируемой базы данных для компьютерного магазина. Основные функции, решаемые информационной системой.
курсовая работа [2,9 M], добавлен 15.11.2011
