138 lines
3.5 KiB
SQL
138 lines
3.5 KiB
SQL
CREATE TABLE roles (
|
||
role_id SERIAL PRIMARY KEY,
|
||
role_name VARCHAR(50) NOT NULL
|
||
);
|
||
|
||
CREATE TABLE users (
|
||
user_id SERIAL PRIMARY KEY,
|
||
login VARCHAR(50) UNIQUE NOT NULL,
|
||
password VARCHAR(100) NOT NULL,
|
||
full_name VARCHAR(150) NOT NULL,
|
||
role_id INTEGER REFERENCES roles(role_id)
|
||
);
|
||
|
||
CREATE TABLE categories (
|
||
category_id SERIAL PRIMARY KEY,
|
||
category_name VARCHAR(100) NOT NULL
|
||
);
|
||
|
||
CREATE TABLE manufacturers (
|
||
manufacturer_id SERIAL PRIMARY KEY,
|
||
manufacturer_name VARCHAR(100) NOT NULL
|
||
);
|
||
|
||
CREATE TABLE suppliers (
|
||
supplier_id SERIAL PRIMARY KEY,
|
||
supplier_name VARCHAR(100) NOT NULL
|
||
);
|
||
|
||
CREATE TABLE age_groups (
|
||
age_group_id SERIAL PRIMARY KEY,
|
||
age_label VARCHAR(20) NOT NULL
|
||
);
|
||
|
||
CREATE TABLE toys (
|
||
toy_id SERIAL PRIMARY KEY,
|
||
toy_name VARCHAR(150) NOT NULL,
|
||
category_id INTEGER REFERENCES categories(category_id),
|
||
manufacturer_id INTEGER REFERENCES manufacturers(manufacturer_id),
|
||
price NUMERIC(10,2) NOT NULL,
|
||
discount INTEGER DEFAULT 0,
|
||
image VARCHAR(255)
|
||
);
|
||
|
||
CREATE TABLE toy_age_groups (
|
||
toy_id INTEGER REFERENCES toys(toy_id),
|
||
age_group_id INTEGER REFERENCES age_groups(age_group_id),
|
||
PRIMARY KEY (toy_id, age_group_id)
|
||
);
|
||
|
||
CREATE TABLE toy_suppliers (
|
||
toy_id INTEGER REFERENCES toys(toy_id),
|
||
supplier_id INTEGER REFERENCES suppliers(supplier_id),
|
||
PRIMARY KEY (toy_id, supplier_id)
|
||
);
|
||
|
||
CREATE TABLE stock (
|
||
toy_id INTEGER PRIMARY KEY REFERENCES toys(toy_id),
|
||
quantity INTEGER NOT NULL
|
||
);
|
||
|
||
INSERT INTO roles (role_name) VALUES
|
||
('Гость'),
|
||
('Покупатель'),
|
||
('Сотрудник'),
|
||
('Администратор');
|
||
|
||
INSERT INTO users (login, password, full_name, role_id) VALUES
|
||
('guest','guest','Гость',1),
|
||
('anna','111','Иванова Анна Сергеевна',2),
|
||
('oleg','222','Кузнецов Олег Петрович',2),
|
||
('manager','333','Смирнова Мария Андреевна',3),
|
||
('admin','admin','Сидоров Максим Игоревич',4);
|
||
|
||
INSERT INTO categories (category_name) VALUES
|
||
('Мягкие игрушки'),
|
||
('Конструкторы'),
|
||
('Развивающие игрушки'),
|
||
('Настольные игры'),
|
||
('Роботы');
|
||
|
||
INSERT INTO manufacturers (manufacturer_name) VALUES
|
||
('Lego'),
|
||
('Hasbro'),
|
||
('Mattel'),
|
||
('PlaySmart'),
|
||
('Fisher Price');
|
||
|
||
INSERT INTO suppliers (supplier_name) VALUES
|
||
('ООО Радуга'),
|
||
('ИП Смайл'),('ToyImport'),
|
||
('KidsWorld');
|
||
|
||
INSERT INTO age_groups (age_label) VALUES
|
||
('0-1'),
|
||
('1-3'),
|
||
('3-5'),
|
||
('5-7'),
|
||
('7+');
|
||
|
||
INSERT INTO toys (toy_name, category_id, manufacturer_id, price, discount, image) VALUES
|
||
('Плюшевый мишка',1,4,1500,10,'bear.png'),
|
||
('Конструктор City',2,1,4200,25,'city.png'),
|
||
('Развивающий куб',3,5,2300,0,NULL),
|
||
('Робот трансформер',5,2,5200,30,'robot.png'),
|
||
('Настольная игра Лото',4,3,1800,5,NULL),
|
||
('Кукла классическая',1,3,2700,15,'doll.png'),
|
||
('Конструктор Junior',2,1,3100,0,NULL),
|
||
('Музыкальный телефон',3,5,2100,20,NULL),
|
||
('Робот на пульте',5,2,6400,35,'rc.png'),
|
||
('Игра Мемори',4,3,1600,0,NULL);
|
||
|
||
INSERT INTO toy_age_groups (toy_id, age_group_id) VALUES
|
||
(1,2),(1,3),
|
||
(2,4),(2,5),
|
||
(3,2),
|
||
(4,4),(4,5),
|
||
(5,3),(5,4),
|
||
(6,3),(6,4),
|
||
(7,2),(7,3),
|
||
(8,2),
|
||
(9,5),
|
||
(10,3),(10,4);
|
||
|
||
INSERT INTO toy_suppliers (toy_id, supplier_id) VALUES
|
||
(1,1),(1,2),
|
||
(2,3),
|
||
(3,1),(3,4),
|
||
(4,2),(4,3),
|
||
(5,1),
|
||
(6,4),
|
||
(7,3),
|
||
(8,2),(8,4),
|
||
(9,3),
|
||
(10,1),(10,2);
|
||
|
||
INSERT INTO stock (toy_id, quantity) VALUES
|
||
(1,12),(2,5),(3,0),(4,7),(5,10),
|
||
(6,4),(7,8),(8,0),(9,3),(10,15);
|