create schema if not exists game_store;

use game_store;

create table if not exists game (

game_id int(11) not null auto_increment primary key,

title varchar(50) not null,

ersb_rating varchar(50) not null,

description varchar(255) not null,

price decimal(5, 2) not null,

studio varchar(50) not null,

quantity int(11)

);

create table if not exists console (

game_id int(11) not null auto_increment primary key,

model varchar(50) not null,

manufacturer varchar(50) not null,

memory_amount varchar(20),

processor varchar(20),

price decimal(5, 2) not null,

quantity int(11) not null

);

create table if not exists t_shirt (

t_shirt_id int(11) not null auto_increment primary key,

size varchar(20) not null,

color varchar(20) not null,

description varchar(255) not null,

price decimal(5,2) not null,

quantity int(11) not null

);

create table if not exists sales_tax_rate (

state char(2) not null,

rate decimal(3,2) not null

);

create unique index ix_state_rate on sales_tax_rate(state, rate);

create table if not exists processing_fee (

product_type varchar(20) not null,

fee decimal (4,2)

);

create unique index ix_product_type_fee on processing_fee(product_type, fee);

create table if not exists invoice (

invoice_id int(11) not null auto_increment primary key,

name varchar(80) not null,

street varchar(30) not null,

city varchar(30) not null,

state varchar(30) not null,

zipcode varchar(5) not null,

item_type varchar(20) not null,

item_id int(11) not null,

unit_price decimal(5,2) not null,

quantity int(11) not null,

subtotal decimal(5,2) not null,

tax decimal(5,2) not null,

processing_fee decimal (5,2) not null,

total decimal(5,2) not null

);