Tabelite loomine
Tabeli loomine brands
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE Database ShopBase; USE ShopBase --Tabeli loomine brands Create table brands( brand_id int primary key identity(1,1), brand_name varchar (20) ); INSERT INTO brands (brand_name) VALUES ( 'Zara' ), ( 'Nike' ), ( 'Adidas' ), ( 'Gucci' ), ( 'Chanel' ); SELECT * FROM brands |

Tabeli loomine categories
1 2 3 4 5 6 7 8 9 10 11 12 | --Tabeli loomine categories Create table categories( category_id int primary key identity(1,1), category_name varchar (20) ); INSERT INTO categories (category_name) VALUES ( 'Ülerõivad' ), ( 'Talje riided' ), ( 'Aluspesu' ), ( 'Mütsid' ), ( 'Kingad' ); SELECT * FROM categories |

Tabeli loomine customers
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | --Tabeli loomine customers CREATE TABLE customers( customer_id int primary key identity(1,1), first_name varchar (30), last_name varchar (30), phone varchar (30), email varchar (30), street varchar (30), city varchar (30), state varchar (15), zip_code varchar (10) ); INSERT INTO customers (first_name, last_name, phone, email, street, city, state, zip_code) VALUES ( 'Sofiia' , 'Halchenko' , '54346644' , 'sofiagalcenko3@gmail.com' , 'Viljandi mnt 16' , 'Tallinn' , 'Eesti' , '25544' ), ( 'Zenja' , 'Svirilin' , '58877544' , 'zenja323@gmail.com' , 'Majaka 9' , 'Tallinn' , 'Eesti' , '25334' ), ( 'Sasha' , 'Semjonova' , '58756454' , 'semjonova2@gmail.com' , 'Meistri 5' , 'Tallinn' , 'Eesti' , '33774' ), ( 'Bogdan' , 'Sergatsov' , '58334563' , 'bogdan.sergatsov233@gmail.com' , 'Väike-Õismäe 15' , 'Tallinn' , 'Eesti' , '58761' ), ( 'Martin' , 'Sild' , '58996845' , 'martinsild@gmail.com' , 'Peterburi tee 2' , 'Tallinn' , 'Eesti' , '58364' ); SELECT * FROM customers |

Tabeli loomine products
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | --Tabeli loomine products CREATE TABLE products( product_id int not null primary key identity(1,1), product_name varchar (20), brand_id int , category_id int , model_year int , list_price decimal (7, 2), FOREIGN KEY (brand_id) REFERENCES brands(brand_id), FOREIGN KEY (category_id) REFERENCES categories(category_id) ); INSERT INTO products (product_name, brand_id, category_id,model_year,list_price) VALUES ( 'särk' , 1, 2, 2020, 25.20), ( 'jope' , 2, 2, 2021, 25.15), ( 'seelik' , 3, 2, 2022, 26.10), ( 'tossud' , 5, 5, 2023, 22.25), ( 'ujumistrikoo' , 4, 3, 2024, 20.5); select * from products |

Tabeli loomine stores
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | --Tabeli loomine stores Create table stores( store_id int primary key identity(1,1), store_name varchar (20), phone varchar (20), email varchar (20), street varchar (20), city varchar (20), state varchar (20), zip_code varchar (10) ); INSERT INTO stores (store_name, phone, email, street, city, state, zip_code) VALUES ( 'Zara' , '55565545' , 'zara323@gmail.com' , 'Narva mnt 22' , 'Tallinn' , 'Eesti' , '25644' ), ( 'Skyler' , '58755469' , 'skylerl42@gmail.com' , 'Majaka 16' , 'Tallinn' , 'Eesti' , '33234' ), ( 'AOIFE' , '58732463' , 'aoife233@gmail.com' , 'Väike-Õismäe 15' , 'Tallinn' , 'Eesti' , '58871' ), ( 'Fashions' , '58334445' , 'fashions23@gmail.com' , 'Männiku tee 109a' , 'Tallinn' , 'Eesti' , '53324' ), ( 'Butik' , '5822345' , 'butik23@gmail.com' , 'Männiku tee 135a' , 'Tallinn' , 'Eesti' , '51234' ); SELECT * FROM stores |

Tabeli loomine staffs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | --Tabeli loomine staffs CREATE TABLE staffs( staff_id int primary key identity(1,1), first_name varchar (25), last_name varchar (25), email varchar (30), phone varchar (20), active bit , store_id int , manager_id int , FOREIGN KEY (manager_id) REFERENCES staffs(staff_id), FOREIGN KEY (store_id) REFERENCES stores(store_id) ); INSERT INTO staffs (first_name, last_name, email, phone, active, store_id, manager_id) VALUES ( 'Daria' , 'Halchenko' , 'daragalcenko3@gmail.com' , '58933227' , 1, 2, 1), ( 'Kirill' , 'Saats' , 'kirill123@gmail.com' , '58764027' , 0, 1, 2), ( 'Valeria' , 'Allik' , 'valeria.allik@gmail.com' , '58746852' , 1, 4, 3), ( 'Lev' , 'Jegorov' , 'lev@gmail.com' , '58568797' , 0, 3, 4), ( 'Maksim' , 'Svirilin' , 'maksim134@gmail.com' , '5833852' , 1, 5, 5); select * from staffs |

Tabeli loomine orders
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | --Tabeli loomine orders CREATE TABLE orders( order_id int not null primary key identity(1,1), customer_id int , order_status varchar (20), order_date date , required_date date , sgipped_date date , store_id int , staff_id int , FOREIGN KEY (customer_id) REFERENCES customers(customer_id), FOREIGN KEY (store_id) REFERENCES stores(store_id), FOREIGN KEY (staff_id) REFERENCES staffs(staff_id) ); INSERT INTO orders (customer_id, order_status, order_date, required_date, sgipped_date, store_id, staff_id) VALUES (1, 'Saadetud' , '2024-08-15' , '2024-04-26' , '2024-08-05' , 1, 2), (2, 'ravi' , '2024-05-13' , '2024-03-21' , '2024-06-27' , 3, 5), (3, 'ootus' , '2024-10-25' , '2024-01-22' , '2024-03-24' , 4, 1), (1, 'Saadetud' , '2024-03-07' , '2024-09-23' , '2024-10-24' , 5, 4), (1, 'ravi' , '2024-11-27' , '2024-05-19' , '2024-08-24' , 2, 3); select * from orders |

Tabeli loomine stocks
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | --Tabeli loomine stocks CREATE TABLE stocks( store_id int not null , product_id int , quantity int , PRIMARY KEY (store_id, product_id), FOREIGN KEY (product_id) REFERENCES products(product_id), FOREIGN KEY (store_id) REFERENCES stores(store_id) ); INSERT INTO stocks (store_id, product_id, quantity) VALUES (1, 9, 20), (2, 10, 50), (3, 11, 45), (4, 12, 25), (5, 13, 55); select * from stocks |

Tabeli loomine order_items
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | --Tabeli loomine order_items CREATE TABLE order_items( order_id int not null , item_id int not null , product_id int , quantity int , list_price decimal (7, 2), discount decimal (7, 2), PRIMARY KEY (order_id, item_id), FOREIGN KEY (product_id) REFERENCES products(product_id) ); INSERT INTO order_items (order_id, item_id, product_id, quantity, list_price, discount) VALUES (1, 1, 9, 25, 23.33, 10), (2, 2, 10, 15, 25.45, 5), (3, 3, 11, 25, 215.45, 15), (4, 4, 12, 45, 533.45, 20), (5, 5, 13, 5, 25.45, 5); select * from order_items |

Andmeskeem

Kasutaja staff loomine

Public andmebaasi tasemel

Määratleme õigused ja loobume õigustes
1 2 3 4 5 6 7 8 9 10 11 12 | --просматривать (STAFF) GRANT SELECT ON customers TO staff; GRANT SELECT ON orders TO staff; GRANT SELECT ON staffs TO staff; GRANT SELECT ON stores TO staff; GRANT SELECT ON order_items TO staff; --Staff имеет право добавлять заказы (orders, order_item) GRANT INSERT ON order_items TO staff; GRANT INSERT ON orders TO staff; --keelduda DELETE ja UPDATE (STAFF) DENY DELETE to staff; DENY UPDATE to staff; |
Kasutaja manager loomine


Määratleme õigused ja loobume õigustes
1 2 3 4 5 6 7 8 9 10 11 12 | --Manager не может добавлять новые магазины (stores), ему доступно полное управление товарами, покупателями и заказами. GRANT INSERT , SELECT , UPDATE , DELETE ON order_items TO manager; GRANT SELECT , UPDATE ON stores TO manager; GRANT INSERT , SELECT , UPDATE , DELETE ON staffs TO manager; GRANT INSERT , SELECT , UPDATE , DELETE ON orders TO manager; GRANT INSERT , SELECT , UPDATE , DELETE ON customers TO manager; GRANT INSERT , SELECT , UPDATE , DELETE ON categories TO manager; GRANT INSERT , SELECT , UPDATE , DELETE ON products TO manager; GRANT INSERT , SELECT , UPDATE , DELETE ON stocks TO manager; GRANT INSERT , SELECT , UPDATE , DELETE ON brands TO manager; GRANT SELECT to manager; |
Õiguste kontroll staff

Tabelite vaatamine

1 2 3 4 5 | SELECT * FROM customers; SELECT * FROM orders; SELECT * FROM staffs; SELECT * FROM stores; SELECT * FROM order_items; |
Staffil on õigus lisada tellimusi (orders)


1 2 3 4 | INSERT INTO orders (customer_id, order_status, order_date, required_date, sgipped_date, store_id, staff_id) VALUES (2, 'Uuendatud' , '2024-10-25' , '2024-07-22' , '2024-07-05' , 1, 3); SELECT * FROM orders; |
Staffil on õigus lisada tellimusi ( order_item)


1 2 3 4 | INSERT INTO order_items (order_id, item_id, product_id, quantity, list_price, discount) VALUES (1, 3, 15, 25, 143.33, 15); SELECT * FROM order_items; |
Kontrollitakse, mida kasutaja ei saa teha
Kontrollime UPDATE

1 2 3 | --kontrollime UPDATE UPDATE order_items set quantity=45 where order_id=1; |
Kontrollime DELETE

1 2 3 | --kontrollime DELETE DELETE from orders |
Õiguste kontroll manager

Tabelite vaatamine

1 2 3 4 5 6 7 8 9 | SELECT * FROM customers; SELECT * FROM orders; SELECT * FROM staffs; SELECT * FROM stores; SELECT * FROM order_items; SELECT * FROM categories; SELECT * FROM products; SELECT * FROM stocks; SELECT * FROM brands; |
Kontrollime UPDATE

1 2 3 | UPDATE order_items set quantity=45 where order_id=1; SELECT * FROM order_items; |
Kontrollitakse, mida kasutaja ei saa teha
Kontrollime INSERT

1 2 3 | INSERT INTO stores (store_name, phone, email, street, city, state, zip_code) VALUES ( 'H&M' , '58885545' , 'hm35675323@gmail.com' , 'Narva mnt 22' , 'Tallinn' , 'Eesti' , '25644' ); |
Protseduurid

1 2 3 4 5 6 7 8 9 10 11 | create procedure insertToProducts( @prod_name varchar (50), @brand int , @category int ) as INSERT INTO products( product_name, brand_id, category_id) Values (@prod_name, @brand, @category); EXEC insertToProducts @prod_name=automasin,@brand=1, @category=2; SELECT * from products |
Protseduuri kustutamiseks selle id

1 2 3 4 5 6 7 8 9 10 11 | CREATE PROCEDURE categories_kustutamine @deleteID int AS BEGIN SELECT * FROM categories; DELETE FROM categories WHERE category_id = @deleteID; SELECT * FROM categories; END ; -- kontroll EXEC categories_kustutamine 1; |