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;