/*
A character set is a set of symbols and encodings. 
A collation is a set of rules for comparing characters in a character set.

Suppose that we have an alphabet with four letters: "A", "B", "a", "b". 
We give each letter a number: "A" = 0, "B" = 1, "a" = 2, "b" = 3. 
The letter "A" is a symbol, the number 0 is the encoding for "A", 
and the combination of all four letters and their encodings is a character set. 

Suppose that we want to compare two string values, "A" and "B". 
The simplest way to do this is to look at the encodings: 0 for "A" and 1 for "B". 
Because 0 is less than 1, we say "A" is less than "B". What we've just done is apply a collation to our character set. 
The collation is a set of rules (only one rule in this case): "compare the encodings." 
We call this simplest of all possible collations a binary collation.

http://dev.mysql.com/doc/refman/5.5/en/charset.html
*/

set foreign_key_checks=0;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table region
-- -----------------------------------------------------
-- following line will show warning (no region table)
DROP TABLE IF EXISTS region ;

CREATE  TABLE IF NOT EXISTS region 
(
  region_code INT NOT NULL AUTO_INCREMENT ,
  region_descript VARCHAR(45) NULL ,
  PRIMARY KEY (region_code) )
ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table store
-- -----------------------------------------------------
-- following line will show warning (no store table)
DROP TABLE IF EXISTS store ;

CREATE  TABLE IF NOT EXISTS store (
  store_code INT NOT NULL AUTO_INCREMENT ,
  store_name VARCHAR(45) NULL COMMENT '		' ,
  store_ytd_sales DECIMAL(10,2) NULL ,
  region_code INT NULL ,
  PRIMARY KEY (store_code) ,
  INDEX idx_store_region (region_code ASC) ,
  CONSTRAINT fk_store_region
    FOREIGN KEY (region_code )
    REFERENCES region (region_code )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Data for table region
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO region (region_code, region_descript) VALUES (NULL, 'East');
INSERT INTO region (region_code, region_descript) VALUES (NULL, 'West');

COMMIT;

-- -----------------------------------------------------
-- Data for table store
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO store (store_code, store_name, store_ytd_sales, region_code) VALUES (NULL, 'Access Junction', 1003455.76, 2);
INSERT INTO store (store_code, store_name, store_ytd_sales, region_code) VALUES (NULL, 'Database Corner', 1421987.39, 2);
INSERT INTO store (store_code, store_name, store_ytd_sales, region_code) VALUES (NULL, 'Tuple Charge', 986783.22, 1);
INSERT INTO store (store_code, store_name, store_ytd_sales, region_code) VALUES (NULL, 'Attribute Alley', 944568.56, 2);
INSERT INTO store (store_code, store_name, store_ytd_sales, region_code) VALUES (NULL, 'Primary Key Point', 2930098.45, 1);

COMMIT;

set foreign_key_checks=1;