[데이터베이스 과제]

CREATE SCHEMA `pokemon_store`;

CREATE TABLE `pokemon_store`.`MERCHANDISE`(
`product_no` CHAR(10) NOT NULL,
`name` VARCHAR(20),
`registration_date` DATE,
`price` DOUBLE,
`quantity` INT,
`type` ENUM('F','D','C','P','E'),
`supplier_no` CHAR(10) NULL,
PRIMARY KEY(`product_no`),
CONSTRAINT `product_no_format`
CHECK (`product_no` REGEXP '^[0-9]{10}$'),
CONSTRAINT `quantity_format`
CHECK (`quantity` >=0),
CONSTRAINT `fk_MERCHANDISE_SUPPLIER`
FOREIGN KEY (`supplier_no`)
REFERENCES `pokemon_store`.`SUPPLIER` (`business_number`)
ON DELETE SET NULL
ON UPDATE CASCADE
);

CREATE TABLE `pokemon_store`.`SUPPLIER`(
`business_number` CHAR(10) NOT NULL,
`location` VARCHAR(100),
PRIMARY KEY(`business_number`),
CONSTRAINT `business_number_format`
 CHECK (`business_number` LIKE '___-__-___')
);

CREATE TABLE `pokemon_store`.`CUSTOMER`(
`email` VARCHAR(20) NOT NULL,
`password` VARCHAR(20),
PRIMARY KEY(`email`),
CONSTRAINT `email_format`
CHECK(`email` LIKE '%@%' ),
CONSTRAINT `password_format`
CHECK(char_length(`password`)>=10 and char_length(`password`)<=20)

);

CREATE TABLE `pokemon_store`.`POKEMON`(
`number` INT NOT NULL,
`name` VARCHAR(20),
`description` VARCHAR(300),
PRIMARY KEY(`number`),
CONSTRAINT `number`
CHECK (`number` >=0)
);

CREATE TABLE `pokemon_store`.`POKEMON_STATS`(
`name` VARCHAR(20) NOT NULL,
`value` INT NOT NULL,
`pokemon_dict_no` INT NOT NULL,
PRIMARY KEY(`name`,`value`,`pokemon_dict_no`),
CONSTRAINT `value` 
CHECK(`value`>=0 and `value`<=999),
CONSTRAINT `fk_pokemon_dict_no`
FOREIGN KEY(`pokemon_dict_no`)
REFERENCES `pokemon_store`.`POKEMON`(`number`)
ON DELETE CASCADE
ON UPDATE CASCADE


);

CREATE TABLE `pokemon_store`.`ADDRESS`(
`name` VARCHAR(20) NOT NULL, 
`state` CHAR(3),
`city` VARCHAR(20),
`street` VARCHAR(20),
`room` VARCHAR(20),
`zip` CHAR(5),
`customer_email` VARCHAR(20) NOT NULL,
PRIMARY KEY (`name`, `customer_email`),
CONSTRAINT `state_format`
CHECK (`state` REGEXP '^[A-Z]{3}$'),
CONSTRAINT `zip_format1`
CHECK (`zip` REGEXP '^[0-9]{5}$'),
CONSTRAINT `fk_ADDRESS_CUSTOMER`
FOREIGN KEY (`customer_email`)
REFERENCES `pokemon_store`.`CUSTOMER`(`email`)
ON DELETE CASCADE
ON UPDATE CASCADE
);

SET FOREIGN_KEY_CHECKS=0;
#RELATION
CREATE TABLE `pokemon_store`.`MOTIVE`(
`merchandise_productno` CHAR(10) NOT NULL,
`pokemon_number` INT NOT NULL,
PRIMARY KEY (`merchandise_productno`, `pokemon_number`),
CONSTRAINT `fk_MOTIVE_merchandise_productno`
FOREIGN KEY(`merchandise_productno`)
REFERENCES `pokemon_store`.`MERCHANDISE`(`product_no`)
ON DELETE CASCADE
ON UPDATE CASCADE,

CONSTRAINT `fk_POKEMON_pokemon_number`
FOREIGN KEY(`pokemon_number`)
REFERENCES `pokemon_store`.`POKEMON`(`number`)
ON DELETE CASCADE
ON UPDATE CASCADE
);

CREATE TABLE `pokemon`.`PURCHASE_LIST`(
`customer_buy_email` VARCHAR(20) NULL,
`buy_merchandise_product_no` CHAR(10) NULL,
 
CONSTRAINT `fk_customer_buy_email`
FOREIGN KEY(`customer_buy_email`)
REFERENCES `pokemon_store`.`CUSTOMER`(`email`)
ON DELETE SET NULL
ON UPDATE CASCADE,

CONSTRAINT `fk_buy_merchandise_product_no`
FOREIGN KEY(`buy_merchandise_product_no`)
REFERENCES `pokemon_store`.`MERCHANDISE`(`product_no`)
ON DELETE SET NULL
ON UPDATE CASCADE


);