########################################

# MySQL Crash Course

# http://www.forta.com/books/0672327120/

# Example table creation scripts

########################################

########################

# Create customers table

########################

CREATE TABLE customers

(

  cust_id      int       NOT NULL AUTO_INCREMENT,

  cust_name    char()  NOT NULL ,

  cust_address char()  NULL ,

  cust_city    char()  NULL ,

  cust_state   char()   NULL ,

  cust_zip     char()  NULL ,

  cust_country char()  NULL ,

  cust_contact char()  NULL ,

  cust_email   char() NULL ,

  PRIMARY KEY (cust_id)

) ENGINE=InnoDB;

#########################

# Create orderitems table

#########################

CREATE TABLE orderitems

(

  order_num  int          NOT NULL ,

  order_item int          NOT NULL ,

  prod_id    char()     NOT NULL ,

  quantity   int          NOT NULL ,

  item_price decimal(,) NOT NULL ,

  PRIMARY KEY (order_num, order_item)

) ENGINE=InnoDB;

#####################

# Create orders table

#####################

CREATE TABLE orders

(

  order_num  int      NOT NULL AUTO_INCREMENT,

  order_date datetime NOT NULL ,

  cust_id    int      NOT NULL ,

  PRIMARY KEY (order_num)

) ENGINE=InnoDB;

#######################

# Create products table

#######################

CREATE TABLE products

(

  prod_id    char()      NOT NULL,

  vend_id    int           NOT NULL ,

  prod_name  char()     NOT NULL ,

  prod_price decimal(,)  NOT NULL ,

  prod_desc  text          NULL ,

  PRIMARY KEY(prod_id)

) ENGINE=InnoDB;

######################

# Create vendors table

######################

CREATE TABLE vendors

(

  vend_id      int      NOT NULL AUTO_INCREMENT,

  vend_name    char() NOT NULL ,

  vend_address char() NULL ,

  vend_city    char() NULL ,

  vend_state   char()  NULL ,

  vend_zip     char() NULL ,

  vend_country char() NULL ,

  PRIMARY KEY (vend_id)

) ENGINE=InnoDB;

###########################

# Create productnotes table

###########################

CREATE TABLE productnotes

(

  note_id    int           NOT NULL AUTO_INCREMENT,

  prod_id    char()      NOT NULL,

  note_date datetime       NOT NULL,

  note_text  text          NULL ,

  PRIMARY KEY(note_id),

  FULLTEXT(note_text)

) ENGINE=MyISAM;

#####################

# Define foreign keys

#####################

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);

ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);

ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

########################################

# MySQL Crash Course

# http://www.forta.com/books/0672327120/

# Example table population scripts

########################################

##########################

# Populate customers table

##########################

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)

VALUES(, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '', 'USA', 'Y Lee', 'ylee@coyote.com');

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)

VALUES(, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '', 'USA', 'Jerry Mouse');

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)

VALUES(, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '', 'USA', 'Jim Jones', 'rabbit@wascally.com');

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)

VALUES(, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '', 'USA', 'Y Sam', 'sam@yosemite.com');

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)

VALUES(, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '', 'USA', 'E Fudd');

########################

# Populate vendors table

########################

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)

VALUES(,'Anvils R Us','123 Main Street','Southfield','MI','', 'USA');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)

VALUES(,'LT Supplies','500 Park Street','Anytown','OH','', 'USA');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)

VALUES(,'ACME','555 High Street','Los Angeles','CA','', 'USA');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)

VALUES(,'Furball Inc.','1000 5th Avenue','New York','NY','', 'USA');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)

VALUES(,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)

VALUES(,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'', 'France');

#########################

# Populate products table

#########################

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES('ANV01', , '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES('ANV02', , '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES('ANV03', , '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES('OL1', , 'Oil can', 8.99, 'Oil can, red');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES('FU1', , 'Fuses', 3.42, '1 dozen, extra long');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES('SLING', , 'Sling', 4.49, 'Sling, one size fits all');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES('TNT1', , 'TNT (1 stick)', 2.50, 'TNT, red, single stick');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES('TNT2', , 'TNT (5 sticks)', , 'TNT, red, pack of 10 sticks');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES('FB', , 'Bird seed', , 'Large bag (suitable for road runners)');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES('FC', , 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES('SAFE', , 'Safe', , 'Safe with combination lock');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES('DTNTR', , 'Detonator', , 'Detonator (plunger powered), fuses not included');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES('JP1000', , 'JetPack 1000', , 'JetPack 1000, intended for single use');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES('JP2000', , 'JetPack 2000', , 'JetPack 2000, multi-use');

#######################

# Populate orders table

#######################

INSERT INTO orders(order_num, order_date, cust_id)

VALUES(, '2005-09-01', );

INSERT INTO orders(order_num, order_date, cust_id)

VALUES(, '2005-09-12', );

INSERT INTO orders(order_num, order_date, cust_id)

VALUES(, '2005-09-30', );

INSERT INTO orders(order_num, order_date, cust_id)

VALUES(, '2005-10-03', );

INSERT INTO orders(order_num, order_date, cust_id)

VALUES(, '2005-10-08', );

###########################

# Populate orderitems table

###########################

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(, , 'ANV01', , 5.99);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(, , 'ANV02', , 9.99);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(, , 'TNT2', , );

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(, , 'FB', , );

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(, , 'JP2000', , );

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(, , 'TNT2', , );

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(, , 'FC', , 2.50);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(, , 'FB', , );

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(, , 'OL1', , 8.99);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(, , 'SLING', , 4.49);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(, , 'ANV03', , 14.99);

#############################

# Populate productnotes table

#############################

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(, 'TNT2', '2005-08-17',

'Customer complaint:

Sticks not individually wrapped, too easy to mistakenly detonate all at once.

Recommend individual wrapping.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(, 'OL1', '2005-08-18',

'Can shipped full, refills not available.

Need to order new can if refill needed.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(, 'SAFE', '2005-08-18',

'Safe is combination locked, combination not provided with safe.

This is rarely a problem as safes are typically blown up or dropped by customers.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(, 'FC', '2005-08-19',

'Quantity varies, sold by the sack load.

All guaranteed to be bright and orange, and suitable for use as rabbit bait.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(, 'TNT2', '2005-08-20',

'Included fuses are short and have been known to detonate too quickly for some customers.

Longer fuses are available (item FU1) and should be recommended.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(, 'TNT2', '2005-08-22',

'Matches not included, recommend purchase of matches or detonator (item DTNTR).'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(, 'SAFE', '2005-08-23',

'Please note that no returns will be accepted if safe opened using explosives.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(, 'ANV01', '2005-08-25',

'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(, 'ANV03', '2005-09-01',

'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(, 'FC', '2005-09-01',

'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(, 'SLING', '2005-09-02',

'Shipped unassembled, requires common tools (including oversized hammer).'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(, 'SAFE', '2005-09-02',

'Customer complaint:

Circular hole in safe floor can apparently be easily cut with handsaw.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(, 'ANV01', '2005-09-05',

'Customer complaint:

Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(, 'SAFE', '2005-09-07',

'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.

Comment forwarded to vendor.'

);