Файл: Разработка бд для асу Московская доставка Курсовая работа Студента 4 курса дневного отделения группа Студент (подпись).docx

3.2 Физическое проектирование БД “Московская доставка”

На основе реляционной модели произведена программная реализация. База данных содержит 9 таблиц:

• 
  Товары
  
• 
  Клиенты
  
• 
  Склады
  
• 
  Курьеры
  
• 
  Точки самовывоза
  
• 
  Менеджеры
  
• 
  Заказы
  
• 
  Количество по позиции
  
• 
  Промежуточная таблица “товары к сладам”, реализующая связь многие ко многим между данными таблицами
  

Р исунок 10 – Таблица клиентов

Рисунок 11 – Таблица курьеров

Рисунок 12 – Таблица менеджеров

Рисунок 13 – Таблица товаров



Рисунок 14– Таблица точек самовывоза

Рисунок 15 – Таблица складов



Р исунок 16 – Таблица товары к складам

Рисунок 17 – Таблица заказов



Рисунок 18 – Таблица количества по позиции

3.3 Реализация ограничений

С базой могут работать курьеры, менеджеры и клиенты.

Курьеры и менеджеры могут просматривать заказы, распределенные на них. Клиенты могут оформлять заказы и видеть информацию о них.

Для того, чтобы удалять товары, если они не хранятся ни на одном из складов, создадим триггер:

CREATE OR REPLACE TRIGGER noProduct

---

BEFORE DELETE

ON Warehouses

FOR EACH ROW

DECLARE

CURSOR productOnDelete IS

SELECT Products.Product_ID FROM Products

MINUS (SELECT Product_to_warehouse.Product_ID FROM Product_to_warehouse GROUP BY Product_to_warehouse.Product_ID);

BEGIN

DELETE FROM Product_to_warehouse WHERE Warehouse_ID = :old.Warehouse_ID;

FOR prod IN productOnDelete

LOOP

DELETE FROM Products WHERE Product_ID = prod.Product_ID;

END LOOP;

END;

/

Для того, чтобы назначать заказам менеджера, создадим триггер:

CREATE OR REPLACE TRIGGER giveManager

BEFORE INSERT

ON Orders

FOR EACH ROW

DECLARE

managerID int(5) := 10000 ;

managerOrders int(5) := 10000 ;

CURSOR managerChoose IS

SELECT Manager_ID, COUNT(*) AS MOrders FROM Orders GROUP BY Manager_ID;

BEGIN

FOR man IN managerChoose

LOOP

IF man.MOrders < managerOrders THEN

BEGIN

managerID := man.Manager_ID;

managerOrders := man.MOrders;

END;

END IF;

END LOOP;

:new.Manager_ID := managerID;

END;

/

Для того, чтобы назначать заказам курьера, создадим триггер:

CREATE OR REPLACE TRIGGER giveCourier

BEFORE INSERT

ON Orders

FOR EACH ROW

DECLARE

courierID int(5) := 10000 ;

courierOrders int(5) := 10000 ;

CURSOR courierChoose IS

SELECT Courier_ID, COUNT(*) AS MOrders FROM Orders GROUP BY Courier_ID;

BEGIN

FOR cur IN courierChoose

LOOP

IF cur.MOrders < courierOrders THEN

BEGIN

courierID := cur.Courier_ID;

courierOrders := cur.MOrders;

END;

END IF;

END LOOP;

:new.Courier_ID := courierID;

END;

/

3.4 Безопасность и контроль

Средства безопасности в Oracle можно разделить на две категории:

• 
  Безопасность доступа.
  
• 
  Безопасность данных.
  

Безопасность доступа.

В Oracle имеется целый ряд механизмов для идентификации и верификации пользователей. Самый простой из них – обязательное указание пользователем своих имени и пароля при каждом подключении. Эта верификация должна выполняться независимо от того, какое внешнее интерфейсное средство используется для доступа к базе данных. Идея состоит в том, чтобы допустить пользователей к работе со средствами базы данных только после того, как он установит санкционированное соединение с ней. Имя пользователя и пароль сверяются с указанными в таблице SYS.USERS, куда пароль заносится в зашифрованной форме.

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

---

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

Безопасность данных.

Если подключаться к базе данных могут лишь уполномоченные пользователи, и они могут запускать только те модули, на выполнение которых им явно предоставлено право, то нужно подумать о следующем уровне безопасности – ограничении доступа этих пользователей к данным.

Для добавления пользователя в базу данных администратор базы данных создает учетную запись с именем пользователя и паролем. Каждому пользователю присваивается профиль — характеристика предельных объемов системных ресурсов, которые могут быть выделены данному пользователю. Сюда входит лимит совокупного процессорного времени, предоставляемого в течение одного сеанса или за один вызов Oracle, и другие подобные ограничения.

В Oracle имеются системные и объектные привилегии. Системные привилегии — это права на выполнение общих задач, таких как SELECT ANY TABLE и UPDATE ANY TABLE. Объектные привилегии относятся к действиям с определенными элементами базы данных — таблицами, представлениями и последовательностями. Для предоставления привилегий другому пользователю можно использовать оператор GRANT.

Выводы по главе 3

В данной главе произведено физическое проектирование базы данных системы “Московская доставка”. Создано три триггера. Определены ограничения. Произведен анализ безопасности в СУБД Oracle.

Заключение

Таким образом, курсовая работа посвящена разработке базы данных системы “Московская доставка”.

Решены следующие задачи:

• 
  Произведен системный анализ предметной области;
  
• 
  Построена инфологическая модель;
  
• 
  Построена реляционная модель;
  
• 
  Реализация базы данных в СУБД Oracle.
  

Разработана реляционная база данных, содержащая элементы автоматизации и обработки данных. В ее составе: 9 таблиц, 3 триггера.

Список литературы

1. 
   Воронова Л.И. «Лабораторный практикум по дисциплине “базы данных”» - Москва 2010г.
   
2. 
   ГОСТ 2.105-95 Единая система конструкторской документации. Общие требования к текстовым документам
   
3. 
   Флоренция Нами [Электронный ресурс] – Статья “Применение баз данных в современном мире” – Режим доступа - https://idaten.ru/technology/primenenie-baz-dannih-v-sovremennom-mire
   
4. 
   Википедия. Свободная энциклопедия [Электронный ресурс] – Базы данных- Режим доступа - https://ru.wikipedia.org/wiki/%D0%91%D0%B0%D0%B7%D0%B0_%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85
   
5. 
   Портал StudFiles [Электронный ресурс] – Обзор информационных технологий для БД –Режим доступа - https://studfiles.net/preview/5157336/page:3/
   
6. 
   Википедия. Свободная энциклопедия [Электронный ресурс] – SQLite – Режим доступа - https://ru.wikipedia.org/wiki/SQLite
   
7. 
   Форум w3ii [Электронный ресурс] - Обзор SQLite – Режим доступа - http://www.w3ii.com/ru/sqlite/sqlite_overview.html
   
8. 
   Сообщество Oracle-patches [Электронный ресурс] – СУБД Oracle database 11g – Режим доступа - https://oracle-patches.ru/%D0%B1%D0%BB%D0%BE%D0%B3%D0%B8/70-%D0%B1%D0%B0%D0%B7%D1%8B-%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85-oracle/3139-%D1%81%D1%83%D0%B1%D0%B4-oracle-database-11g
   
9. 
   Портал Студопедия [Электронный ресурс] – Модели данных – Режим доступа - https://studopedia.ru/2_57929_infologicheskie-i-datalogicheskie-modeli-dannih.html
   
10. 
    Портал Office-menu[Электронный ресурс] – Нормализация базы данных – Режим доступа - http://office-menu.ru/uroki-sql/51-normalizatsiya-bazy-dannykh
    

---

Приложения

Программный код:

CREATE TABLE Products (

Product_ID INT PRIMARY KEY,

Name VARCHAR(45) NOT NULL,

Price INT NOT NULL,

Description VARCHAR(45) NULL

);

CREATE SEQUENCE ProductID START WITH 1 INCREMENT BY 1;

CREATE TABLE Clients (

Client_ID INT PRIMARY KEY,

Username VARCHAR(20) NOT NULL,

Password_hash VARCHAR(50) NOT NULL,

Name VARCHAR(45) NOT NULL,

Client_Type VARCHAR(20) NOT NULL,

Address VARCHAR(45) NOT NULL,

Contact_number VARCHAR(15) NULL,

Email VARCHAR(45) NULL

);

CREATE SEQUENCE ClientID START WITH 1 INCREMENT BY 1;

CREATE TABLE Warehouses (

Warehouse_ID INT PRIMARY KEY,

Address VARCHAR(45) NOT NULL

);

CREATE SEQUENCE WarehouseID START WITH 1 INCREMENT BY 1;

CREATE TABLE Couriers (

Courier_ID INT PRIMARY KEY,

Username VARCHAR(20) NOT NULL,

Password_hash VARCHAR(50) NOT NULL,

FIO VARCHAR(45) NOT NULL,

Birth_date DATE NOT NULL,

Passport VARCHAR(100) NOT NULL,

Hire_date DATE NOT NULL,

Contact_number VARCHAR(15) NOT NULL

);

CREATE SEQUENCE CourierID START WITH 1 INCREMENT BY 1;

CREATE TABLE Self_pickup_points (

Point_ID INT PRIMARY KEY,

Point_address VARCHAR(45) NOT NULL

);

CREATE SEQUENCE PointID START WITH 1 INCREMENT BY 1;

CREATE TABLE Managers (

Manager_ID INT PRIMARY KEY,

Username VARCHAR(20) NOT NULL,

Password_hash VARCHAR(50) NOT NULL,

FIO VARCHAR(45) NOT NULL,

Birth_date DATE NOT NULL,

Passport VARCHAR(100) NOT NULL,

Hire_date DATE NOT NULL,

Contact_number VARCHAR(15) NOT NULL

);

CREATE SEQUENCE ManagerID START WITH 1 INCREMENT BY 1;

CREATE TABLE Orders (

Order_ID INT PRIMARY KEY,

Client_ID INT NOT NULL,

Courier_ID INT NOT NULL,

Delivery_date DATE NULL,

Payment_method VARCHAR(45) NOT NULL,

Self_pickup_ID INT NULL,

Manager_ID INT NOT NULL

);

ALTER TABLE Orders ADD CONSTRAINT fk_clientId FOREIGN KEY (Client_ID) REFERENCES Clients(Client_ID);

ALTER TABLE Orders ADD CONSTRAINT fk_courierId FOREIGN KEY (Courier_ID) REFERENCES Couriers(Courier_ID);

ALTER TABLE Orders ADD CONSTRAINT fk_selfpickupId FOREIGN KEY (Self_pickup_ID) REFERENCES Self_pickup_points(Point_ID);

ALTER TABLE Orders ADD CONSTRAINT fk_managerId FOREIGN KEY (Manager_ID) REFERENCES Managers(Manager_ID);

CREATE SEQUENCE OrderID START WITH 1 INCREMENT BY 1;

CREATE TABLE Position_count (

Position_ID INT PRIMARY KEY,

Product_ID INT NOT NULL,

Order_ID INT NOT NULL,

Amount INT NOT NULL

)

ALTER TABLE Position_count ADD CONSTRAINT fk_producId FOREIGN KEY (Product_ID) REFERENCES Products(Product_ID);

ALTER TABLE Position_count ADD CONSTRAINT fk_orderId FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID);

CREATE SEQUENCE PositionID START WITH 1 INCREMENT BY 1;

CREATE TABLE Product_to_warehouse (

Warehouse_ID INT ,

Product_ID INT

)

ALTER TABLE Product_to_warehouse ADD CONSTRAINT pk_Id PRIMARY KEY (Warehouse_ID, Product_ID);

ALTER TABLE Product_to_warehouse ADD CONSTRAINT fk_warehouseId FOREIGN KEY (Warehouse_ID) REFERENCES Warehouses(Warehouse_ID);

ALTER TABLE Product_to_warehouse ADD CONSTRAINT fk_productId FOREIGN KEY (Product_ID) REFERENCES Products(Product_ID);

INSERT INTO Products VALUES (ProductID.nextval, 'Шиферные гвозди', 3, '90мм/5мм/18мм/');

INSERT INTO Products VALUES (ProductID.nextval, 'Молоток слесарный', 58, 'Молоток слесарный 100гр с деревянной ручкой');

INSERT INTO Clients VALUES (ClientID.nextval, 'Client1', '098f6bcd4621d373cade4e832627b4f6', 'Виталий Иванов', 'Физ. лицо', 'Москва', '88005553535', 'vitalya@ya.ru');

INSERT INTO Clients VALUES (ClientID.nextval, 'Client2', 'ad0234829205b9033196ba818f7a872b', 'ЗАО Строй', 'Юр. лицо', 'Москва', '89034457812', 'stroy@ya.ru');

INSERT INTO Warehouses VALUES (WarehouseID.nextval, 'Москва, ул. Пушкина д.3');

INSERT INTO Warehouses VALUES (WarehouseID.nextval, 'Москва, ул. Академика Королева д.12');

---

INSERT INTO Couriers VALUES (CourierID.nextval, 'Courier1', 'c79d74a1e537c13ccb4ec1c6266d5a8d', 'Петрова С.А.', '03/12/1990', '123456/12', '13/03/2017', '89346570173');

INSERT INTO Couriers VALUES (CourierID.nextval, 'Courier2', '61b49956a9f21a7268ea9d175aa0fc91', 'Иванов К.С.', '04/03/1995', '145656/03', '10/05/2017', '89346567183');

INSERT INTO Self_pickup_points VALUES(PointID.nextval, 'ул.Якиманка д.74');

INSERT INTO Self_pickup_points VALUES(PointID.nextval, 'Мирской проезд д.16');

INSERT INTO Managers VALUES (ManagerID.nextval, 'Manager1', 'c240642ddef994358c96da82c0361a58', 'Васильева К.А.', '03/11/1980', '199956/12', '13/03/2017', '8934445173');

INSERT INTO Managers VALUES (ManagerID.nextval, 'Manager2', '8df5127cd164b5bc2d2b78410a7eea0c', 'Пилан И.И.', '04/04/1987', '124566/12', '13/03/2017', '89346668173');

INSERT INTO Product_to_warehouse VALUES(3,4);

INSERT INTO Product_to_warehouse VALUES(3,6);

INSERT INTO Product_to_warehouse VALUES(6,6);

---