Utilizei uma base de dados obtida no Kaggle, disponibilizada em arquivos CSV.
Para importar os dados no MySQL Workbench, optei pela forma mais prática: usei o Assistente de Importação de Dados da Tabela (Table Data Import Wizard) para carregar cada arquivo individualmente.
Criei um banco de dados denominado loja_kaggle, dentro do qual configurei as tabelas correspondentes e realizei a importação dos arquivos CSV um a um
Criei as tabelas
-- Tabela de Marcas
CREATE TABLE brands (
brand_id INT PRIMARY KEY,
brand_name VARCHAR(100)
);
-- Tabela de Categorias
CREATE TABLE categories (
category_id INT PRIMARY KEY,
category_name VARCHAR(100)
);
-- Tabela de Produtos
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
brand_id INT,
category_id INT,
model_year INT,
list_price DECIMAL(10,2),
FOREIGN KEY (brand_id) REFERENCES brands(brand_id),
FOREIGN KEY (category_id) REFERENCES categories(category_id)
);
-- Tabela de Lojas
CREATE TABLE stores (
store_id INT PRIMARY KEY,
store_name VARCHAR(100),
phone VARCHAR(20),
email VARCHAR(100),
street VARCHAR(100),
city VARCHAR(50),
state VARCHAR(50),
zip_code VARCHAR(10)
);
-- Tabela de Funcionários
CREATE TABLE staffs (
staff_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
active TINYINT,
store_id INT,
manager_id INT,
FOREIGN KEY (store_id) REFERENCES stores(store_id)
);
-- Tabela de Clientes
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
phone VARCHAR(20),
email VARCHAR(100),
street VARCHAR(100),
city VARCHAR(50),
state VARCHAR(50),
zip_code VARCHAR(10)
);
-- Tabela de Pedidos
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_status INT,
order_date DATE,
required_date DATE,
shipped_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)
);
-- Itens dos Pedidos
CREATE TABLE order_items (
order_id INT,
item_id INT,
product_id INT,
quantity INT,
list_price DECIMAL(10,2),
discount DECIMAL(4,2),
PRIMARY KEY (order_id, item_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- Estoques
CREATE TABLE stocks (
store_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (store_id, product_id),
FOREIGN KEY (store_id) REFERENCES stores(store_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
