# Note: The "if exists" which only works with MySQL 3.22 or later
drop database if exists winestore;
create database winestore;
use winestore;

CREATE TABLE users (
cust_id int(5) NOT NULL,
user_name varchar(50) NOT NULL,
password varchar(32) NOT NULL,
PRIMARY KEY (user_name),
KEY password (password),
KEY cust_id (cust_id)
) type=MyISAM;

CREATE TABLE customer (
cust_id int(5) NOT NULL,
surname varchar(50),
firstname varchar(50),
initial char(1),
title_id int(3),
address varchar(50),
city varchar(50),
state varchar(20),
zipcode varchar(10),
country_id int(4),
phone varchar(15),
birth_date char(10),
PRIMARY KEY (cust_id)
) type=MyISAM;

CREATE TABLE grape_variety (
variety_id int(3) NOT NULL,
variety varchar(50) DEFAULT '' NOT NULL,
PRIMARY KEY (variety_id),
KEY var (variety)
) type=MyISAM;

CREATE TABLE inventory (
wine_id int(5) NOT NULL,
inventory_id int(3) NOT NULL,
on_hand int(5) NOT NULL,
cost decimal(5,2) NOT NULL,
date_added date,
PRIMARY KEY (wine_id,inventory_id)
) type=MyISAM;

CREATE TABLE items (
cust_id int(5) NOT NULL,
order_id int(5) NOT NULL,
item_id int(3) NOT NULL,
wine_id int(4) NOT NULL,
qty int(3),
price decimal(5,2),
PRIMARY KEY (cust_id,order_id,item_id)
) type=MyISAM;

CREATE TABLE orders (
cust_id int(5) NOT NULL,
order_id int(5) NOT NULL,
date timestamp(12),
instructions varchar(128),
creditcard char(16),
expirydate char(5),
PRIMARY KEY (cust_id,order_id)
) type=MyISAM;

CREATE TABLE region (
region_id int(4) NOT NULL,
region_name varchar(100) NOT NULL,
PRIMARY KEY (region_id),
KEY region (region_name)
) type=MyISAM;

CREATE TABLE wine_type(
wine_type_id int(2) NOT NULL,
wine_type varchar(32) NOT NULL,
PRIMARY KEY (wine_type_id)
) type=MyISAM;

CREATE TABLE wine (
wine_id int(5) NOT NULL,
wine_name varchar(50) NOT NULL,
wine_type int(2) NOT NULL,
year int(4) NOT NULL,
winery_id int(4) NOT NULL,
description blob,
PRIMARY KEY (wine_id),
KEY name (wine_name),
KEY winery (winery_id)
) type=MyISAM;

CREATE TABLE wine_variety (
wine_id int(5) DEFAULT '0' NOT NULL,
variety_id int(3) DEFAULT '0' NOT NULL,
id int(1) DEFAULT '0' NOT NULL,
PRIMARY KEY (wine_id,variety_id),
KEY wine (wine_id,variety_id)
) type=MyISAM;

CREATE TABLE winery (
winery_id int(4) NOT NULL,
winery_name varchar(100) NOT NULL,
region_id int(4) NOT NULL,
PRIMARY KEY (winery_id),
KEY name (winery_name),
KEY region (region_id)
) type=MyISAM;

CREATE TABLE titles (
title_id int(2) NOT NULL,
title char(10),
PRIMARY KEY (title_id)
) type=MyISAM;

CREATE TABLE countries (
country_id int(4) NOT NULL,
country char(30) NOT NULL,
PRIMARY KEY (country_id),
KEY (country)
) type=MyISAM;