create table Store (
storeID INT,
name varchar(100),
location varchar(50),
contactInfo varchar(50),
primary key (storeID, name)
);
INSERT INTO Store VALUES (356, 'FashionHub', 'Moscow, Russia', '84951234567');
INSERT INTO Store VALUES (234, 'StyleSpot', 'Saint Petersburg, Russia', '88122345678');
INSERT INTO Store VALUES (321, 'TrendSetter', 'Novosibirsk, Russia', '83831234567');
INSERT INTO Store VALUES (245, 'ChicBoutique', 'Yekaterinburg, Russia', '83439876543');
INSERT INTO Store VALUES (267, 'EcoFashion', 'Kazan, Russia', '88431234567');
INSERT INTO Store VALUES (123, 'SportStyle', 'Nizhny Novgorod, Russia', '88312345678');
INSERT INTO Store VALUES (256, 'ModaCasa', 'Chelyabinsk, Russia', '83519876543');
INSERT INTO Store VALUES (743, 'GlamourSpot', 'Samara, Russia', '88461234567');
INSERT INTO Store VALUES (512, 'UrbanChic', 'Krasnodar, Russia', '84951237654');
INSERT INTO Store VALUES (678, 'VintageVogue', 'Sochi, Russia', '88641238765');
CREATE TABLE Product (
article INT,
name VARCHAR(50),
category VARCHAR(20),
sizes VARCHAR(5),
color VARCHAR(20),
price INT,
stockQuantity INT,
primary key (article, name)
);
INSERT INTO Product VALUES (82635, 'Classic T-Shirt', 'Tops', 'M', 'Black', 1500, 120);
INSERT INTO Product VALUES (92147, 'Summer Dress', 'Dresses', 'S', 'Red', 3500, 45);
INSERT INTO Product VALUES (34829, 'Hoodie', 'Outerwear', 'M', 'Gray', 3000, 60);
INSERT INTO Product VALUES (65943, 'Leather Jacket', 'Outerwear', 'L', 'Black', 7000, 35);
INSERT INTO Product VALUES (71582, 'Cargo Pants', 'Bottoms', 'M', 'Green', 2700, 100);
INSERT INTO Product VALUES (23491, 'Maxi Skirt', 'Bottoms', 'S', 'Pink', 3200, 50);
INSERT INTO Product VALUES (48236, 'Blazer', 'Outerwear', 'XL', 'Navy', 5000, 40);
INSERT INTO Product VALUES (50984, 'Denim Jacket', 'Outerwear', 'M', 'Blue', 4500, 55);
INSERT INTO Product VALUES (61473, 'Polo Shirt', 'Tops', 'L', 'White', 1800, 75);
INSERT INTO Product VALUES (82635, 'Skinny Jeans', 'Bottoms', 'M', 'Black', 1500, 120);
INSERT INTO Product VALUES (57382, 'Skinny Jeans', 'Bottoms', 'S', 'Blue', 1500, 120);
INSERT INTO Product VALUES (57382, 'Summer Dress', 'Dresses', 'S', 'White', 3500, 45);
INSERT INTO Product VALUES (92147, 'Hoodie', 'Outerwear', 'S', 'Red', 3500, 45);
INSERT INTO Product VALUES (82635, 'Leather Jacket', 'Outerwear', 'M', 'Black', 1500, 120);
INSERT INTO Product VALUES (61473, 'Classic T-Shirt', 'Tops', 'L', 'Blue', 1500, 120);
CREATE TABLE Customer (
name VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
address VARCHAR(100),
primary key (name, phone)
);
INSERT INTO Customer VALUES ('Alexei Petrov', 'alice.johnson@email.com', '+7 495 1111111', 'Moscow, Tverskaya St, 15');
INSERT INTO Customer VALUES ('Dmitry Ivanov', 'bob.smith@email.com', '+7 812 2222222', 'Saint Petersburg, Nevsky Ave, 45');
INSERT INTO Customer VALUES ('Natalia Sokolova', 'charlie.brown@email.com', '+7 383 3333333', 'Novosibirsk, Lenina St, 23');
INSERT INTO Customer VALUES ('Maria Novikova', 'diana.prince@email.com', '+7 343 4444444', 'Yekaterinburg, Malysheva St, 10');
INSERT INTO Customer VALUES ('Sergey Kuznetsov', 'edward.green@email.com', '+7 843 5555555', 'Kazan, Baumana St, 50');
INSERT INTO Customer VALUES ('Elena Smirnova', 'fiona.davis@email.com', '+7 831 6666666', 'Nizhny Novgorod, Gorkogo St, 12');
INSERT INTO Customer VALUES ('Viktor Orlov', 'george.white@email.com', '+7 351 7777777', 'Chelyabinsk, Kirova St, 7');
INSERT INTO Customer VALUES ('Anna Volkova', 'hannah.black@email.com', '+7 846 8888888', 'Samara, Kuybysheva St, 20');
INSERT INTO Customer VALUES ('Igor Fedorov', 'igor.fedorov@email.com', '+7 916 9999999', 'Moscow, Arbat St, 9');
INSERT INTO Customer VALUES ('Tatiana Morozova', 'tatiana.morozova@email.com', '+7 812 1234567', 'Saint Petersburg, Ligovsky Ave, 33');
INSERT INTO Customer VALUES ('Global Textiles', 'glob@email.com', '8 (903) 567-8900', 'Saint Petersburg, Ligovsky Ave, 33');
INSERT INTO Customer VALUES ('Igor Fedorov', 'igor.fedorov@email.com', '+7 381 9999999', 'Moscow, Arbat St, 9');
INSERT INTO Customer VALUES ('Tatiana Morozova', 'tatiana.morozova@email.com', '+7 863 1010101', 'Saint Petersburg, Ligovsky Ave, 33');
CREATE TABLE Orders (
orderID INT,
orderDate VARCHAR(10), -- ΠΠΎΠΆΠ½ΠΎ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡ ΡΡΡΠΎΠΊΡ ΡΠΎΡΠΌΠ°ΡΠ° 'MM/DD/YYYY'
totalAmount DECIMAL(10, 2),
placedIn VARCHAR(100),
submittedBy VARCHAR(100),
paidBy INT,
primary key (orderID)
);
INSERT INTO Orders VALUES (51234, '09/01/2024', 8500, 'Moscow, Tverskaya St', 'Alexandra Gromova', 78342);
INSERT INTO Orders VALUES (62345, '09/02/2024', 5200, 'Saint Petersburg, Nevsky Ave', 'Denis Lapin', 94321);
INSERT INTO Orders VALUES (23154, '09/03/2024', 12000, 'Novosibirsk, Lenina St', 'Svetlana Berezina', 12365);
INSERT INTO Orders VALUES (86453, '09/04/2024', 4600, 'Yekaterinburg, Malysheva St', 'Oleg Tikhonov', 67431);
INSERT INTO Orders VALUES (13542, '09/05/2024', 7800, 'Kazan, Baumana St', 'Marina Zaitseva', 19843);
INSERT INTO Orders VALUES (76321, '09/06/2024', 3900, 'Nizhny Novgorod, Gorkogo St', 'Maxim Solovyov', 32154);
INSERT INTO Orders VALUES (90876, '09/07/2024', 6500, 'Chelyabinsk, Kirova St', 'Anastasia Kravtsova', 87534);
INSERT INTO Orders VALUES (31425, '09/08/2024', 5200, 'Samara, Kuybysheva St', 'Vladimir Egorov', 54312);
INSERT INTO Orders VALUES (78912, '09/09/2024', 7100, 'Moscow, Tverskaya St', 'Natalia Romanova', 93847);
INSERT INTO Orders VALUES (45678, '09/10/2024', 9000, 'Saint Petersburg, Nevsky Ave', 'Igor Petrov', 74231);
INSERT INTO Orders VALUES (13256, '09/09/2023', 7100, 'Moscow, Tverskaya St', 'Natalia Romanova', 93847);
INSERT INTO Orders VALUES (23678, '09/09/2024', 2300, 'Moscow, Tverskaya St', 'Natalia Romanova', 93847);
INSERT INTO Orders VALUES (54312, '09/04/2024', 4600, 'Yekaterinburg, Malysheva St', 'Oleg Tikhonov', 67431);
INSERT INTO Orders VALUES (93847, '09/01/2024', 8500, 'Moscow, Tverskaya St', 'Alexandra Gromova', 78342);
INSERT INTO Orders VALUES (74231, '09/02/2024', 5200, 'Saint Petersburg, Nevsky Ave', 'Denis Lapin', 94321);
INSERT INTO Orders VALUES (45679, '09/09/2024', 2300, 'Moscow, Tverskaya St', 'Natalia Romanova', 93847);
INSERT INTO Orders VALUES (62346, '09/07/2024', 6500, 'Chelyabinsk, Kirova St', 'Anastasia Kravtsova', 87534);
INSERT INTO Orders VALUES (86454, '09/08/2024', 5200, 'Samara, Kuybysheva St', 'Vladimir Egorov', 54312);
INSERT INTO Orders VALUES (23155, '09/08/2024', 5200, 'Samara, Kuybysheva St', 'Vladimir Egorov', 54312);
INSERT INTO Orders VALUES (78913, '09/02/2024', 5200, 'Saint Petersburg, Nevsky Ave', 'Denis Lapin', 94321);
CREATE TABLE Payment (
paymentID INT,
method VARCHAR(50),
paymentDate VARCHAR(10), -- Ρ
ΡΠ°Π½Π΅Π½ΠΈΠ΅ Π΄Π°ΡΡ Π² Π²ΠΈΠ΄Π΅ ΡΡΡΠΎΠΊΠΈ
amountPaid DECIMAL(10, 2),
primary key (paymentID, method)
);
INSERT INTO Payment VALUES (78342, 'Credit Card', '09/01/2024', 8500);
INSERT INTO Payment VALUES (94321, 'PayPal', '09/02/2024', 5200);
INSERT INTO Payment VALUES (12365, 'Credit Card', '09/03/2024', 12000);
INSERT INTO Payment VALUES (67431, 'Cash', '09/04/2024', 4600);
INSERT INTO Payment VALUES (19843, 'Bank Transfer', '09/05/2024', 7800);
INSERT INTO Payment VALUES (32154, 'PayPal', '09/06/2024', 3900);
INSERT INTO Payment VALUES (87534, 'Credit Card', '09/07/2024', 6500);
INSERT INTO Payment VALUES (54312, 'Cash', '09/08/2024', 5200);
INSERT INTO Payment VALUES (93847, 'Credit Card', '09/09/2024', 3400);
INSERT INTO Payment VALUES (74231, 'Bank Transfer', '09/10/2024', 9000);
CREATE TABLE Supplier (
name VARCHAR(100),
phone VARCHAR(20),
email VARCHAR(100),
primary key (name, phone)
);
INSERT INTO Supplier VALUES ('Global Textiles', '8 (903) 567-8900', 'info@globaltextiles.com');
INSERT INTO Supplier VALUES ('Fashion Supplies Ltd', '8 (905) 1234-5678', 'contact@fashionsupplies.co.uk');
INSERT INTO Supplier VALUES ('Premium Clothing Co', '8 (914) 123-4567', 'sales@premiumclothing.de');
INSERT INTO Supplier VALUES ('Urban Wear Suppliers', '8 (912) 9876-5432', 'urban@wearsuppliers.fr');
INSERT INTO Supplier VALUES ('Classic Fabrics', '8 (920) 1234-5678', 'classic@fabrics.in');
INSERT INTO Supplier VALUES ('Elegant Apparel', '8 (916) 555-1234', 'service@elegantapparel.com');
INSERT INTO Supplier VALUES ('Modern Style Ltd', '8 (915) 1234-5678', 'support@modernstyle.jp');
INSERT INTO Supplier VALUES ('EuroFashion Imports', '8 (903) 9876-5432', 'sales@eurofashion.it');
INSERT INTO Supplier VALUES ('Smart Textile Co', '8 (917) 123-7654', 'info@smarttextile.com');
INSERT INTO Supplier VALUES ('Luxe Fabrics Inc', '8 (918) 432-9876', 'contact@luxefabricsinc.com');
CREATE TABLE Employee (
name VARCHAR(100),
position VARCHAR(50),
salary DECIMAL(10, 2),
hireDate VARCHAR(10), -- ΠΠ°ΡΠ° ΠΊΠ°ΠΊ ΡΡΡΠΎΠΊΠ° Π² ΡΠΎΡΠΌΠ°ΡΠ΅ 'MM/DD/YYYY'
store VARCHAR(100),
primary key (name, position)
);
INSERT INTO Employee VALUES ('Alice Ivanova', 'Sales Manager', 60000, '03/15/2022', 'Global Textiles');
INSERT INTO Employee VALUES ('Dmitry Petrov', 'Store Manager', 75000, '06/10/2021', 'Fashion Supplies Ltd');
INSERT INTO Employee VALUES ('Maria Sidorova', 'Cashier', 40000, '01/20/2023', 'Premium Clothing Co');
INSERT INTO Employee VALUES ('Igor Vasiliev', 'Stock Manager', 50000, '02/25/2022', 'Urban Wear Suppliers');
INSERT INTO Employee VALUES ('Elena Kuznetsova', 'Sales Associate', 45000, '05/12/2023', 'Classic Fabrics');
INSERT INTO Employee VALUES ('Sergey Mikhailov', 'Customer Service', 48000, '11/30/2021', 'Elegant Apparel');
INSERT INTO Employee VALUES ('Tatiana Romanova', 'Marketing Specialist', 55000, '09/15/2020', 'Modern Style Ltd');
INSERT INTO Employee VALUES ('Vladimir Nikolaev', 'Sales Associate', 42000, '04/05/2023', 'EuroFashion Imports');
INSERT INTO Employee VALUES ('Anna Volkova', 'Assistant Manager', 62000, '07/01/2022', 'Urban Wear Suppliers');
INSERT INTO Employee VALUES ('Alexey Smirnov', 'HR Specialist', 58000, '08/18/2021', 'Fashion Supplies Ltd');
INSERT INTO Employee VALUES ('Ivan Petrov', 'Cashier', 60000, '03/15/2022', 'Global Textiles');
INSERT INTO Employee VALUES ('Maria Sidorova', 'Stock Manager', 48000, '11/30/2021', 'Elegant Apparel');
INSERT INTO Employee VALUES ('Dmitry Petrov', 'Sales Associate', 55000, '09/15/2020', 'Modern Style Ltd');
--ΡΠ²ΡΠ·Ρ ΠΌΠ΅ΠΆΠ΄Ρ Store ΠΈ Order
CREATE TABLE Processes (
storeID INT,
name VARCHAR(100),
orderID INT,
PRIMARY KEY (storeID, name, orderID),
FOREIGN KEY (storeID, name) REFERENCES Store (storeID, name),
FOREIGN KEY (orderID) REFERENCES Orders (orderID)
);
INSERT INTO Processes VALUES (356, 'FashionHub', 51234);
INSERT INTO Processes VALUES (234, 'StyleSpot', 13256);
INSERT INTO Processes VALUES (234, 'StyleSpot', 45678);
INSERT INTO Processes VALUES (234, 'StyleSpot', 23678);
INSERT INTO Processes VALUES (234, 'StyleSpot', 62345);
INSERT INTO Processes VALUES (321, 'TrendSetter', 23154);
INSERT INTO Processes VALUES (245, 'ChicBoutique', 86453);
INSERT INTO Processes VALUES (267, 'EcoFashion', 13542);
INSERT INTO Processes VALUES (123, 'SportStyle', 76321);
INSERT INTO Processes VALUES (256, 'ModaCasa', 90876);
INSERT INTO Processes VALUES (743, 'GlamourSpot', 54312);
INSERT INTO Processes VALUES (512, 'UrbanChic', 93847);
INSERT INTO Processes VALUES (678, 'VintageVogue', 74231);
CREATE TABLE Provides (
storeID INT,
storeName VARCHAR(100),
article INT,
productName VARCHAR(50),
PRIMARY KEY (storeID, storeName, article, productName),
FOREIGN KEY (storeID, storeName) REFERENCES Store (storeID, name),
FOREIGN KEY (article, productName) REFERENCES Product (article, name)
);
INSERT INTO Provides VALUES (356, 'FashionHub', 82635, 'Classic T-Shirt');
INSERT INTO Provides VALUES (356, 'FashionHub', 82635, 'Skinny Jeans');
INSERT INTO Provides VALUES (234, 'StyleSpot', 57382, 'Skinny Jeans');
INSERT INTO Provides VALUES (234, 'StyleSpot', 57382, 'Summer Dress');
INSERT INTO Provides VALUES (321, 'TrendSetter', 92147, 'Summer Dress');
INSERT INTO Provides VALUES (321, 'TrendSetter', 92147, 'Hoodie');
INSERT INTO Provides VALUES (245, 'ChicBoutique', 34829, 'Hoodie');
INSERT INTO Provides VALUES (267, 'EcoFashion', 65943, 'Leather Jacket');
INSERT INTO Provides VALUES (123, 'SportStyle', 71582, 'Cargo Pants');
INSERT INTO Provides VALUES (256, 'ModaCasa', 23491, 'Maxi Skirt');
INSERT INTO Provides VALUES (743, 'GlamourSpot', 48236, 'Blazer');
INSERT INTO Provides VALUES (512, 'UrbanChic', 50984, 'Denim Jacket');
INSERT INTO Provides VALUES (678, 'VintageVogue', 61473, 'Polo Shirt');
INSERT INTO Provides VALUES (356, 'FashionHub', 82635, 'Leather Jacket');
INSERT INTO Provides VALUES (678, 'VintageVogue', 61473, 'Classic T-Shirt');
INSERT INTO Provides VALUES (256, 'ModaCasa', 50984, 'Denim Jacket');
--ΡΠ²ΡΠ·Ρ Order ΠΈ Product
CREATE TABLE Contain (
orderID INT,
article INT,
name VARCHAR(50),
PRIMARY KEY (orderID, article, name),
FOREIGN KEY (orderID) REFERENCES Orders (OrderID),
FOREIGN KEY (article, name) REFERENCES Product (article, name)
);
INSERT INTO Contain VALUES (51234, 82635, 'Classic T-Shirt');
INSERT INTO Contain VALUES (62345, 57382, 'Skinny Jeans');
INSERT INTO Contain VALUES (23154, 92147, 'Summer Dress');
INSERT INTO Contain VALUES (86453, 34829, 'Hoodie');
INSERT INTO Contain VALUES (13542, 65943, 'Leather Jacket');
INSERT INTO Contain VALUES (76321, 71582, 'Cargo Pants');
INSERT INTO Contain VALUES (90876, 23491, 'Maxi Skirt');
INSERT INTO Contain VALUES (31425, 48236, 'Blazer');
INSERT INTO Contain VALUES (78912, 50984, 'Denim Jacket');
INSERT INTO Contain VALUES (45678, 61473, 'Polo Shirt');
INSERT INTO Contain VALUES (51234, 57382, 'Skinny Jeans');
INSERT INTO Contain VALUES (23154, 34829, 'Hoodie');
INSERT INTO Contain VALUES (86453, 82635, 'Classic T-Shirt');
INSERT INTO Contain VALUES (13542, 92147, 'Summer Dress');
--ΡΠ²ΡΠ·Ρ Store ΠΈ Employee
CREATE TABLE Work (
storeID INT,
name VARCHAR(100),
employee_name VARCHAR(100),
position VARCHAR(100),
PRIMARY KEY (storeID, name, employee_name, position),
FOREIGN KEY (storeID, name) REFERENCES Store (storeID, name),
FOREIGN KEY (employee_name, position) REFERENCES Employee (name, position)
);
INSERT INTO Work VALUES (356, 'FashionHub', 'Alice Ivanova', 'Sales Manager');
INSERT INTO Work VALUES (356, 'FashionHub', 'Ivan Petrov', 'Cashier');
INSERT INTO Work VALUES (356, 'FashionHub', 'Maria Sidorova', 'Stock Manager');
INSERT INTO Work VALUES (234, 'StyleSpot', 'Dmitry Petrov', 'Store Manager');
INSERT INTO Work VALUES (234, 'StyleSpot', 'Elena Kuznetsova', 'Sales Associate');
INSERT INTO Work VALUES (321, 'TrendSetter', 'Maria Sidorova', 'Cashier');
INSERT INTO Work VALUES (321, 'TrendSetter', 'Igor Vasiliev', 'Stock Manager');
INSERT INTO Work VALUES (245, 'ChicBoutique', 'Igor Vasiliev', 'Stock Manager');
INSERT INTO Work VALUES (267, 'EcoFashion', 'Elena Kuznetsova', 'Sales Associate');
INSERT INTO Work VALUES (123, 'SportStyle', 'Sergey Mikhailov', 'Customer Service');
INSERT INTO Work VALUES (256, 'ModaCasa', 'Tatiana Romanova', 'Marketing Specialist');
INSERT INTO Work VALUES (743, 'GlamourSpot', 'Vladimir Nikolaev', 'Sales Associate');
INSERT INTO Work VALUES (512, 'UrbanChic', 'Anna Volkova', 'Assistant Manager');
INSERT INTO Work VALUES (678, 'VintageVogue', 'Alexey Smirnov', 'HR Specialist');
INSERT INTO Work VALUES (678, 'VintageVogue', 'Dmitry Petrov', 'Sales Associate');
--ΡΠ²ΡΠ·Ρ Supplier ΠΈ Product
CREATE TABLE Supplied (
name VARCHAR(100),
phone VARCHAR(20),
article INT,
productName VARCHAR(50),
PRIMARY KEY (name, phone, article, productName),
FOREIGN KEY (name, phone) REFERENCES Supplier (name, phone),
FOREIGN KEY (article, productName) REFERENCES Product (article, name)
);
INSERT INTO Supplied VALUES ('Global Textiles', '8 (903) 567-8900', 82635, 'Classic T-Shirt');
INSERT INTO Supplied VALUES ('Fashion Supplies Ltd', '8 (905) 1234-5678', 57382, 'Skinny Jeans');
INSERT INTO Supplied VALUES ('Premium Clothing Co', '8 (914) 123-4567', 92147, 'Summer Dress');
INSERT INTO Supplied VALUES ('Urban Wear Suppliers', '8 (912) 9876-5432', 34829, 'Hoodie');
INSERT INTO Supplied VALUES ('Classic Fabrics', '8 (920) 1234-5678', 65943, 'Leather Jacket');
INSERT INTO Supplied VALUES ('Elegant Apparel', '8 (916) 555-1234', 71582, 'Cargo Pants');
INSERT INTO Supplied VALUES ('Modern Style Ltd', '8 (915) 1234-5678', 23491, 'Maxi Skirt');
INSERT INTO Supplied VALUES ('EuroFashion Imports', '8 (903) 9876-5432', 48236, 'Blazer');
INSERT INTO Supplied VALUES ('Smart Textile Co', '8 (917) 123-7654', 50984, 'Denim Jacket');
INSERT INTO Supplied VALUES ('Luxe Fabrics Inc', '8 (918) 432-9876', 61473, 'Polo Shirt');
--Payment Customer
CREATE TABLE Paid (
name VARCHAR(100),
phone VARCHAR(20),
paymentID INT,
method VARCHAR(50),
PRIMARY KEY (name, phone, paymentID, method),
FOREIGN KEY (name, phone) REFERENCES Customer (name, phone),
FOREIGN KEY (paymentID, method) REFERENCES Payment (paymentID, method)
);
-- ΠΠ°ΠΏΠΎΠ»Π½Π΅Π½ΠΈΠ΅ ΡΠ°Π±Π»ΠΈΡΡ paid
INSERT INTO Paid VALUES ('Alexei Petrov', '+7 495 1111111', 78342, 'Credit Card');
INSERT INTO Paid VALUES ('Dmitry Ivanov', '+7 812 2222222', 94321, 'PayPal');
INSERT INTO Paid VALUES ('Natalia Sokolova', '+7 383 3333333', 12365, 'Credit Card');
INSERT INTO Paid VALUES ('Maria Novikova', '+7 343 4444444', 67431, 'Cash');
INSERT INTO Paid VALUES ('Sergey Kuznetsov', '+7 843 5555555', 19843, 'Bank Transfer');
INSERT INTO Paid VALUES ('Elena Smirnova', '+7 831 6666666', 32154, 'PayPal');
INSERT INTO Paid VALUES ('Viktor Orlov', '+7 351 7777777', 87534, 'Credit Card');
INSERT INTO Paid VALUES ('Anna Volkova', '+7 846 8888888', 54312, 'Cash');
INSERT INTO Paid VALUES ('Igor Fedorov', '+7 916 9999999', 93847, 'Credit Card');
INSERT INTO Paid VALUES ('Tatiana Morozova', '+7 812 1234567', 74231, 'Bank Transfer');
--customer order
CREATE TABLE Submit (
name VARCHAR(100),
phone VARCHAR(20),
orderID INT,
PRIMARY KEY (name, phone, orderID),
FOREIGN KEY (name, phone) REFERENCES Customer (name, phone),
FOREIGN KEY (orderID) REFERENCES Orders (orderID)
);
INSERT INTO Submit VALUES ('Alexei Petrov', '+7 495 1111111', 51234);
INSERT INTO Submit VALUES ('Alexei Petrov', '+7 495 1111111', 62345);
INSERT INTO Submit VALUES ('Dmitry Ivanov', '+7 812 2222222', 62346);
INSERT INTO Submit VALUES ('Natalia Sokolova', '+7 383 3333333', 23154);
INSERT INTO Submit VALUES ('Natalia Sokolova', '+7 383 3333333', 86453);
INSERT INTO Submit VALUES ('Maria Novikova', '+7 343 4444444', 86454);
INSERT INTO Submit VALUES ('Sergey Kuznetsov', '+7 843 5555555', 13542);
INSERT INTO Submit VALUES ('Elena Smirnova', '+7 831 6666666', 76321);
INSERT INTO Submit VALUES ('Viktor Orlov', '+7 351 7777777', 90876);
INSERT INTO Submit VALUES ('Anna Volkova', '+7 846 8888888', 31425);
INSERT INTO Submit VALUES ('Igor Fedorov', '+7 381 9999999', 78912);
INSERT INTO Submit VALUES ('Tatiana Morozova', '+7 863 1010101', 45678);
INSERT INTO Submit VALUES ('Alexei Petrov', '+7 495 1111111', 23155);
INSERT INTO Submit VALUES ('Dmitry Ivanov', '+7 812 2222222', 78913);
INSERT INTO Submit VALUES ('Maria Novikova', '+7 343 4444444', 45679);
SELECT * FROM Store;
SELECT * FROM Product;
SELECT * FROM Customer;
SELECT * FROM Orders;
SELECT * FROM Payment;
SELECT * FROM Supplier;
SELECT * FROM Employee;
SELECT * FROM Processes;
SELECT * FROM Provides;
SELECT * FROM Contain;
SELECT * FROM Work;
SELECT * FROM Supplied;
SELECT * FROM Paid;
SELECT * FROM Submit;
CREATE TABLE AuditLog (
auditID SERIAL PRIMARY KEY, -- ΠΠ΄Π΅Π½ΡΠΈΡΠΈΠΊΠ°ΡΠΎΡ Π·Π°ΠΏΠΈΡΠΈ Π°ΡΠ΄ΠΈΡΠ°
action_type VARCHAR(50), -- Π’ΠΈΠΏ Π΄Π΅ΠΉΡΡΠ²ΠΈΡ (INSERT, UPDATE, DELETE)
table_name VARCHAR(50), -- ΠΠΌΡ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½Π½ΠΎΠΉ ΡΠ°Π±Π»ΠΈΡΡ
old_data TEXT, -- Π‘ΡΠ°ΡΡΠ΅ Π΄Π°Π½Π½ΡΠ΅ (Π΄ΠΎ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΠΉ)
new_data TEXT, -- ΠΠΎΠ²ΡΠ΅ Π΄Π°Π½Π½ΡΠ΅ (ΠΏΠΎΡΠ»Π΅ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΠΉ)
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- ΠΡΠ΅ΠΌΡ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΡ
);
-- Π€ΡΠ½ΠΊΡΠΈΡ Π΄Π»Ρ ΠΎΠ±ΡΠ°Π±ΠΎΡΠΊΠΈ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΠΉ Π² ΡΠ°Π±Π»ΠΈΡΠ΅ Orders
CREATE OR REPLACE FUNCTION audit_orders_changes()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO AuditLog (action_type, table_name, old_data, new_data)
VALUES
('UPDATE', 'Orders', row_to_json(OLD)::TEXT, row_to_json(NEW)::TEXT);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Π’ΡΠΈΠ³Π³Π΅Ρ Π΄Π»Ρ ΠΎΠ±Π½ΠΎΠ²Π»Π΅Π½ΠΈΡ Ρ ΡΡΠ»ΠΎΠ²ΠΈΠ΅ΠΌ WHEN
CREATE TRIGGER orders_audit_trigger
AFTER UPDATE
ON Orders
FOR EACH ROW
WHEN (NEW.totalAmount IS DISTINCT FROM OLD.totalAmount)
EXECUTE FUNCTION audit_orders_changes();
-- ΠΡΡΠ°Π²ΠΈΠΌ Π½ΠΎΠ²ΡΡ Π·Π°ΠΏΠΈΡΡ Π² ΡΠ°Π±Π»ΠΈΡΡ Orders
INSERT INTO Orders (orderID, orderDate, totalAmount, placedIn, submittedBy, paidBy)
VALUES (11223, '09/15/2024', 4500, 'Moscow, Arbat St', 'Tatiana Morozova', 87534);
-- ΠΠ±Π½ΠΎΠ²ΠΈΠΌ ΡΡΡΠ΅ΡΡΠ²ΡΡΡΡΡ Π·Π°ΠΏΠΈΡΡ Π² ΡΠ°Π±Π»ΠΈΡΠ΅ Orders
UPDATE Orders
SET totalAmount = 5200
WHERE orderID = 11223;
-- ΠΡΠ²Π΅Π΄Π΅ΠΌ Π΄Π°Π½Π½ΡΠ΅ ΠΈΠ· ΡΠ°Π±Π»ΠΈΡΡ Π°ΡΠ΄ΠΈΡΠ°
SELECT * FROM AuditLog;
Telegram: DB developers channel