Code
/*TIMEZONE Временные зоны*/
CREATE TABLE TIMEZONE (
timezone_id INTEGER PRIMARY KEY,
time_offset TEXT
);
INSERT INTO TIMEZONE VALUES (1, 'UTC -5');
INSERT INTO TIMEZONE VALUES (2, 'UTC +9');
INSERT INTO TIMEZONE VALUES (3, 'UTC +1');
/*CITY Города*/
CREATE TABLE CITY (
city_id INTEGER PRIMARY KEY,
name TEXT,
timezone_id INTEGER
);
INSERT INTO CITY VALUES (1, 'New York', 1);
INSERT INTO CITY VALUES (2, 'Tokyo', 2);
INSERT INTO CITY VALUES (3, 'Berlin', 3);
INSERT INTO CITY VALUES (4, 'Paris', 3);
INSERT INTO CITY VALUES (5, 'Detroit', 1);
/*STORE_ADDRESS Адреса магазинов*/
CREATE TABLE STORE_ADDRESS (
store_address_id INTEGER,
store_id INTEGER,
city_id INTEGER,
address TEXT,
opening_hours TEXT,
phone TEXT
);
INSERT INTO STORE_ADDRESS VALUES (1, 1, 1, 'Park Ave. 1', 'Mo-Fr 10-18, Sa,Su 11-22','0211545454');
INSERT INTO STORE_ADDRESS VALUES (2, 2, 4, 'Mon Rue. 13, 64738', 'Mo-Sun 11-18','120-54527887');
/*STORE Магазины*/
CREATE TABLE STORE (
store_id INTEGER PRIMARY KEY,
name TEXT,
site_url TEXT
);
INSERT INTO STORE VALUES (1, 'Mall of New York', 'mallofny.com');
INSERT INTO STORE VALUES (2, 'Paris Shopping', 'parisshop.fr');
/*EMPLOYEE Сотрудники магазина*/
CREATE TABLE EMPLOYEE (
employee_id INTEGER PRIMARY KEY,
store_id INTEGER,
rank_id INTEGER,
first_name TEXT,
last_name TEXT,
middle_name TEXT,
manager_id INTEGER
);
INSERT INTO EMPLOYEE VALUES (1, 1, 1, 'John', 'Powers', 'J', 3);
INSERT INTO EMPLOYEE VALUES (2, 2, 1, 'Antonio', 'Bergeuo', NULL, 1);
INSERT INTO EMPLOYEE VALUES (3, 2, 2, 'Paolo', 'Fuera', 'Sonio', 2);
INSERT INTO EMPLOYEE VALUES (4, 1, 2, 'Mark', 'Borrows', NULL, 4);
/*RANK Должности*/
CREATE TABLE RANKS (
store_id INTEGER,
rank_id INTEGER,
name TEXT
);
INSERT INTO RANKS VALUES (1, 1, 'Manager');
INSERT INTO RANKS VALUES (1, 2, 'Top Manager');
INSERT INTO RANKS VALUES (2, 1, 'Seller');
INSERT INTO RANKS VALUES (2, 2, 'Manager');
/*PRODUCT_PRICE Цены на товар в магазинах*/
CREATE TABLE PRODUCT_PRICE (
product_id INTEGER PRIMARY KEY,
store_id INTEGER,
price FLOAT
);
INSERT INTO PRODUCT_PRICE VALUES (1, 1, 2.2);
INSERT INTO PRODUCT_PRICE VALUES (2, 1, 6.5);
INSERT INTO PRODUCT_PRICE VALUES (3, 2, 5);
/*PURCHASE Заказ*/
CREATE TABLE PURCHASE (
purchase_id INTEGER PRIMARY KEY,
purchase_date DATE,
store_id INTEGER,
employee_id INTEGER
);
INSERT INTO PURCHASE VALUES (1, '2021-12-01', 1, 1);
INSERT INTO PURCHASE VALUES (2, '2021-04-03', 1, 4);
INSERT INTO PURCHASE VALUES (3, '2021-11-28', 2, 3);
INSERT INTO PURCHASE VALUES (4, '2021-05-16', 2, 2);
/*CATEGORY Категории товаров*/
CREATE TABLE CATEGORY (
category_id INTEGER PRIMARY KEY,
parent_category_id INTEGER,
name TEXT
);
INSERT INTO CATEGORY VALUES (1, 2, 'Fruits');
INSERT INTO CATEGORY VALUES (2, NULL, 'Fresh');
INSERT INTO CATEGORY VALUES (3, NULL, 'Household');
/*PRODUCT Товары*/
CREATE TABLE PRODUCT (
product_id INTEGER PRIMARY KEY,
category_id INTEGER,
name TEXT,
description TEXT
);
INSERT INTO PRODUCT VALUES (1, 1, 'Banana', 'From Spain');
INSERT INTO PRODUCT VALUES (2, 3, 'Tempo', 'Paper napkin');
INSERT INTO PRODUCT VALUES (3, 3, 'Pantene Pro V', 'Shampoo');
/*PURCHASE_ITEM Товары заказа*/
CREATE TABLE PURCHASE_ITEM (
purchase_item_id INTEGER PRIMARY KEY,
purchase_id INTEGER,
product_id INTEGER,
price FLOAT,
count FLOAT
);
INSERT INTO PURCHASE_ITEM VALUES (1, 1, 1, 4.4, 2);
INSERT INTO PURCHASE_ITEM VALUES (2, 1, 2, 19.5, 3);
INSERT INTO PURCHASE_ITEM VALUES (3, 2, 1, 5, 1);
SELECT * FROM TIMEZONE;