USER
По этой бд сделай всё что укажу ниже. Также пропиши в конце вывод процедур и функций: -- 1. Создание таблиц
CREATE TABLE RoleT (
RoleIdentifier NUMBER PRIMARY KEY,
RoleName NVARCHAR2(50) NOT NULL
);
CREATE TABLE UsersT (
UserID NUMBER PRIMARY KEY,
RoleIdentifier NUMBER,
UserName NVARCHAR2(50) NOT NULL,
Password NVARCHAR2(255) NOT NULL,
CONSTRAINT fk_role FOREIGN KEY (RoleIdentifier) REFERENCES RoleT(RoleIdentifier)
);
CREATE TABLE PrivilegesT (
PrivilegesID NUMBER PRIMARY KEY,
RoleIdentifier NUMBER,
PrivilegeName NVARCHAR2(200) NOT NULL,
CONSTRAINT fk_privilege_role FOREIGN KEY (RoleIdentifier) REFERENCES RoleT(RoleIdentifier)
);
CREATE TABLE Products (
ProductID NUMBER PRIMARY KEY,
ProductName NVARCHAR2(50) NOT NULL,
Quantity NUMBER NOT NULL,
Location NVARCHAR2(50) NOT NULL
);
CREATE TABLE Orders (
OrderID NUMBER PRIMARY KEY,
UserID NUMBER,
OrderDate DATE NOT NULL,
Status NVARCHAR2(20) NOT NULL,
CONSTRAINT fk_order_user FOREIGN KEY (UserID) REFERENCES UsersT(UserID)
);
CREATE TABLE OrderItems (
OrderItemsID NUMBER PRIMARY KEY,
OrderID NUMBER,
ProductID NUMBER,
Quantity NUMBER NOT NULL,
CONSTRAINT fk_order_item_order FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
CONSTRAINT fk_order_item_product FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
-- 2. Вставка данных
INSERT INTO RoleT (RoleIdentifier, RoleName) VALUES (1, 'Admin');
INSERT INTO RoleT (RoleIdentifier, RoleName) VALUES (2, 'User');
INSERT INTO RoleT (RoleIdentifier, RoleName) VALUES (3, 'Manager');
INSERT INTO RoleT (RoleIdentifier, RoleName) VALUES (4, 'Guest');
INSERT INTO RoleT (RoleIdentifier, RoleName) VALUES (5, 'Support');
INSERT INTO UsersT (UserID, RoleIdentifier, UserName, Password) VALUES (1, 1, 'admin', 'adminpass');
INSERT INTO UsersT (UserID, RoleIdentifier, UserName, Password) VALUES (2, 2, 'john_doe', 'password123');
INSERT INTO UsersT (UserID, RoleIdentifier, UserName, Password) VALUES (3, 3, 'alice', 'alicepass');
INSERT INTO UsersT (UserID, RoleIdentifier, UserName, Password) VALUES (4, 4, 'guest_user', 'guestpass');
INSERT INTO UsersT (UserID, RoleIdentifier, UserName, Password) VALUES (5, 5, 'support_guy', 'supportpass');
INSERT INTO PrivilegesT (PrivilegesID, RoleIdentifier, PrivilegeName) VALUES (1, 1, 'Full Control');
INSERT INTO PrivilegesT (PrivilegesID, RoleIdentifier, PrivilegeName) VALUES (2, 2, 'Read Only');
INSERT INTO PrivilegesT (PrivilegesID, RoleIdentifier, PrivilegeName) VALUES (3, 3, 'Edit Content');
INSERT INTO PrivilegesT (PrivilegesID, RoleIdentifier, PrivilegeName) VALUES (4, 4, 'View Content');
INSERT INTO PrivilegesT (PrivilegesID, RoleIdentifier, PrivilegeName) VALUES (5, 5, 'Technical Support');
INSERT INTO Products (ProductID, ProductName, Quantity, Location) VALUES (1, 'Laptop', 50, 'Warehouse A');
INSERT INTO Products (ProductID, ProductName, Quantity, Location) VALUES (2, 'Mouse', 150, 'Warehouse B');
INSERT INTO Products (ProductID, ProductName, Quantity, Location) VALUES (3, 'Keyboard', 100, 'Warehouse A');
INSERT INTO Products (ProductID, ProductName, Quantity, Location) VALUES (4, 'Monitor', 75, 'Warehouse C');
INSERT INTO Products (ProductID, ProductName, Quantity, Location) VALUES (5, 'Printer', 60, 'Warehouse B');
INSERT INTO Orders (OrderID, UserID, OrderDate, Status) VALUES (1, 1, TO_DATE('2023-01-10', 'YYYY-MM-DD'), 'Completed');
INSERT INTO Orders (OrderID, UserID, OrderDate, Status) VALUES (2, 2, TO_DATE('2023-02-15', 'YYYY-MM-DD'), 'Pending');
INSERT INTO Orders (OrderID, UserID, OrderDate, Status) VALUES (3, 3, TO_DATE('2023-03-20', 'YYYY-MM-DD'), 'Shipped');
INSERT INTO Orders (OrderID, UserID, OrderDate, Status) VALUES (4, 4, TO_DATE('2023-04-25', 'YYYY-MM-DD'), 'Delivered');
INSERT INTO Orders (OrderID, UserID, OrderDate, Status) VALUES (5, 5, TO_DATE('2023-05-30', 'YYYY-MM-DD'), 'Canceled');
INSERT INTO OrderItems (OrderItemsID, OrderID, ProductID, Quantity) VALUES (1, 1, 1, 2);
INSERT INTO OrderItems (OrderItemsID, OrderID, ProductID, Quantity) VALUES (2, 2, 2, 5);
INSERT INTO OrderItems (OrderItemsID, OrderID, ProductID, Quantity) VALUES (3, 3, 3, 1);
INSERT INTO OrderItems (OrderItemsID, OrderID, ProductID, Quantity) VALUES (4, 4, 4, 3);
INSERT INTO OrderItems (OrderItemsID, OrderID, ProductID, Quantity) VALUES (5, 5, 5, 4);
-- Вывод всех объектов
SELECT * FROM RoleT;
SELECT * FROM UsersT;
SELECT * FROM PrivilegesT;
SELECT * FROM Products;
SELECT * FROM Orders;
SELECT * FROM OrderItems; Нужно сделать: 2.1 Представления
- V_OrdersWithProductDetails: Представление для отображения заказов с деталями продуктов, включая имя пользователя, дату заказа и статус.
- V_UserRolesPrivileges: Представление для отображения пользователей с их ролями и привилегиями.
- V_ProductInventoryStatus: Представление для отображения статуса инвентаря продуктов, основанного на количестве.
2.2 Индексы
- IX_Orders_UserID: Индекс на поле UserID в таблице Orders, чтобы ускорить поиск заказов по пользователю.
2.3 Последовательности
- seq_OrderID: Последовательность для автоматического инкремента идентификаторов заказов, начиная с 6(Для учитывая предзаписанных данных).
2.4 Функции
- fn_TotalQuantityOrderedByUser: Возвращает общее количество товаров, заказанных пользователем по его идентификатору.
- fn_OrderCountByUser: Возвращает количество заказов, сделанных пользователем.
- fn_GetOrderStatus: Возвращает статус заказа по его идентификатору.
2.5 Хранимые процедуры
- sp_InsertNewUser: Процедура для добавления нового пользователя с заданным именем, паролем и ролью.
- sp_UpdateOrderStatus: Процедура для обновления статуса заказа по его идентификатору.
- sp_AddNewProduct: Процедура для добавления нового продукта с заданным именем, количеством и местоположением.
2.6 Триггеры
- trg_UpdateOrderStatusOnDelivery: Триггер, который обновляет статус заказа на "Завершен", если все товары в заказе были доставлены.
- trg_LogNewUser: Триггер для записи информации о новом пользователе в таблицу логов после его добавления.
ASSISTANT
Вот код для выполнения всех указанных действий на конкретной базе данных:
### 2.1 Представления
```sql
-- Представление для отображения заказов с деталями продуктов
CREATE OR REPLACE VIEW V_OrdersWithProductDetails AS
SELECT o.OrderID, u.UserName, o.OrderDate, o.Status, p.ProductName, oi.Quantity
FROM Orders o
JOIN UsersT u ON o.UserID = u.UserID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
JOIN Products p ON oi.ProductID = p.ProductID;
-- Представление для отображения пользователей с их ролями и привилегиями
CREATE OR REPLACE VIEW V_UserRolesPrivileges AS
SELECT u.UserID, u.UserName, r.RoleName, p.PrivilegeName
FROM UsersT u
JOIN RoleT r ON u.RoleIdentifier = r.RoleIdentifier
JOIN PrivilegesT p ON r.RoleIdentifier = p.RoleIdentifier;
-- Представление для отображения статуса инвентаря продуктов
CREATE OR REPLACE VIEW V_ProductInventoryStatus AS
SELECT ProductID, ProductName, Quantity,
CASE
WHEN Quantity = 0 THEN 'Out of Stock'
WHEN Quantity < 10 THEN 'Low Stock'
ELSE 'In Stock'
END AS InventoryStatus
FROM Products;
```
### 2.2 Индексы
```sql
-- Индекс на поле UserID в таблице Orders
CREATE INDEX IX_Orders_UserID ON Orders(UserID);
```
### 2.3 Последовательности
```sql
-- Последовательность для идентификаторов заказов
CREATE SEQUENCE seq_OrderID
START WITH 6
INCREMENT BY 1
NOCACHE NOCYCLE;
```
### 2.4 Функции
```sql
-- Функция для подсчета общего количества товаров, заказанных пользователем
CREATE OR REPLACE FUNCTION fn_TotalQuantityOrderedByUser(userId NUMBER) RETURN NUMBER IS
totalQuantity NUMBER := 0;
BEGIN
SELECT SUM(oi.Quantity) INTO totalQuantity
FROM Orders o
JOIN OrderItems oi ON o.OrderID = oi.OrderID
WHERE o.UserID = userId;
RETURN NVL(totalQuantity, 0);
END;
-- Функция для подсчета количества заказов, сделанных пользователем
CREATE OR REPLACE FUNCTION fn_OrderCountByUser(userId NUMBER) RETURN NUMBER IS
orderCount NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO orderCount FROM Orders WHERE UserID = userId;
RETURN orderCount;
END;
-- Функция для получения статуса заказа по его идентификатору
CREATE OR REPLACE FUNCTION fn_GetOrderStatus(orderId NUMBER) RETURN NVARCHAR2 IS
orderStatus NVARCHAR2(20);
BEGIN
SELECT Status INTO orderStatus FROM Orders WHERE OrderID = orderId;
RETURN orderStatus;
END;
```
### 2.5 Хранимые процедуры
```sql
-- Процедура для добавления нового пользователя
CREATE OR REPLACE PROCEDURE sp_InsertNewUser(
p_UserName IN NVARCHAR2,
p_Password IN NVARCHAR2,
p_RoleIdentifier IN NUMBER
) IS
BEGIN
INSERT INTO UsersT (UserID, UserName, Password, RoleIdentifier)
VALUES (UsersT_SEQ.NEXTVAL, p_UserName, p_Password, p_RoleIdentifier);
END;
-- Процедура для обновления статуса заказа
CREATE OR REPLACE PROCEDURE sp_UpdateOrderStatus(
p_OrderID IN NUMBER,
p_Status IN NVARCHAR2
) IS
BEGIN
UPDATE Orders SET Status = p_Status WHERE OrderID = p_OrderID;
END;
-- Процедура для добавления нового продукта
CREATE OR REPLACE PROCEDURE sp_AddNewProduct(
p_ProductName IN NVARCHAR2,
p_Quantity IN NUMBER,
p_Location IN NVARCHAR2
) IS
BEGIN
INSERT INTO Products (ProductID, ProductName, Quantity, Location)
VALUES (Products_SEQ.NEXTVAL, p_ProductName, p_Quantity, p_Location);
END;
```
### 2.6 Триггеры
```sql
-- Триггер, обновляющий статус заказа на "Завершен", если все товары доставлены
CREATE OR REPLACE TRIGGER trg_UpdateOrderStatusOnDelivery
AFTER UPDATE OF Quantity ON OrderItems
FOR EACH ROW
BEGIN
IF :NEW.Quantity = 0 THEN
UPDATE Orders SET Status = 'Completed' WHERE OrderID = :NEW.OrderID
AND NOT EXISTS (
SELECT 1 FROM OrderItems WHERE OrderID = :NEW.OrderID AND Quantity > 0
);
END IF;
END;
-- Триггер для записи информации о новом пользователе в лог
CREATE OR REPLACE TRIGGER trg_LogNewUser
AFTER INSERT ON UsersT
FOR EACH ROW
BEGIN
INSERT INTO UserLogs (LogID, UserID, LogDate, Action)
VALUES (UserLogs_SEQ.NEXTVAL, :NEW.UserID, SYSDATE, 'New User Added');
END;
```
### Вывод процедур и функций
```sql
-- Примеры вызова представлений
SELECT * FROM V_OrdersWithProductDetails;
SELECT * FROM V_UserRolesPrivileges;
SELECT * FROM V_ProductInventoryStatus;
-- Примеры вызова функций
SELECT fn_TotalQuantityOrderedByUser(2) FROM dual;
SELECT fn_OrderCountByUser(3) FROM dual;
SELECT fn_GetOrderStatus(1) FROM dual;
-- Примеры вызова процедур
BEGIN
sp_InsertNewUser('new_user', 'new_password', 2);
sp_UpdateOrderStatus(2, 'Shipped');
sp_AddNewProduct('Tablet', 30, 'Warehouse D');
END;
```
Эти команды помогут создать все необходимые объекты и проиллюстрируют их использование в вашей базе данных. Не забудьте создать последовательности для `UserID` и `ProductID`, а также таблицу lога пользователей `UserLogs`, если таковая еще не существует.