;CREATE SCHEMA IF NOT EXISTS `affablebean` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;USE `affablebean` ;-- ------------------------------------------------------- Table `affablebean`.`customer`-- -----------------------------------------------------DROP TABLE IF EXISTS `affablebean`.`customer` ;CREATE TABLE IF NOT EXISTS `affablebean`.`customer` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NOT NULL , `email` VARCHAR(45) NOT NULL , `phone` VARCHAR(45) NOT NULL , `address` VARCHAR(45) NOT NULL , `city_region` VARCHAR(2) NOT NULL , `cc_number` VARCHAR(19) NOT NULL , PRIMARY KEY (`id`) )ENGINE = InnoDBCOMMENT = 'maintains customer details';-- ------------------------------------------------------- Table `affablebean`.`customer_order`-- -----------------------------------------------------DROP TABLE IF EXISTS `affablebean`.`customer_order` ;CREATE TABLE IF NOT EXISTS `affablebean`.`customer_order` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `amount` DECIMAL(6,2) NOT NULL , `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `confirmation_number` INT UNSIGNED NOT NULL , `customer_id` INT UNSIGNED NOT NULL , PRIMARY KEY (`id`) , INDEX `fk_customer_order_customer` (`customer_id` ASC) , CONSTRAINT `fk_customer_order_customer` FOREIGN KEY (`customer_id` ) REFERENCES `affablebean`.`customer` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION)ENGINE = InnoDBCOMMENT = 'maintains customer order details';-- ------------------------------------------------------- Table `affablebean`.`category`-- -----------------------------------------------------DROP TABLE IF EXISTS `affablebean`.`category` ;CREATE TABLE IF NOT EXISTS `affablebean`.`category` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NOT NULL , PRIMARY KEY (`id`) )ENGINE = InnoDBCOMMENT = 'contains product categories, e.g., dairy, meats, etc.';-- ------------------------------------------------------- Table `affablebean`.`product`-- -----------------------------------------------------DROP TABLE IF EXISTS `affablebean`.`product` ;CREATE TABLE IF NOT EXISTS `affablebean`.`product` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NOT NULL , `price` DECIMAL(5,2) NOT NULL , -- Delete after description is moved to resource bundle `description` TINYTEXT NULL , `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , `category_id` TINYINT UNSIGNED NOT NULL , PRIMARY KEY (`id`) , INDEX `fk_product_category` (`category_id` ASC) , CONSTRAINT `fk_product_category` FOREIGN KEY (`category_id` ) REFERENCES `affablebean`.`category` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION)ENGINE = InnoDBCOMMENT = 'contains product details';-- ------------------------------------------------------- Table `affablebean`.`ordered_product`-- -----------------------------------------------------DROP TABLE IF EXISTS `affablebean`.`ordered_product` ;CREATE TABLE IF NOT EXISTS `affablebean`.`ordered_product` ( `customer_order_id` INT UNSIGNED NOT NULL , `product_id` INT UNSIGNED NOT NULL , `quantity` SMALLINT UNSIGNED NOT NULL DEFAULT 1 , PRIMARY KEY (`customer_order_id`, `product_id`) , INDEX `fk_ordered_product_customer_order` (`customer_order_id` ASC) , INDEX `fk_ordered_product_product` (`product_id` ASC) , CONSTRAINT `fk_ordered_product_customer_order` FOREIGN KEY (`customer_order_id` ) REFERENCES `affablebean`.`customer_order` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_ordered_product_product` FOREIGN KEY (`product_id` ) REFERENCES `affablebean`.`product` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION)ENGINE = InnoDBCOMMENT = 'matches products with customer orders and records their quantity';SET SQL_MODE=@OLD_SQL_MODE;SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;*************Use o codigo abaixo para insercao de dados*****************USE affablebean;INSERT INTO `category` (name) VALUES ('dairy'),('meats'),('bakery'),('fruit & veg');USE affablebean;INSERT INTO `product` (`name`, price, description, category_id) VALUES ('milk', 1.70, 'semi skimmed (1L)', 1);INSERT INTO `product` (`name`, price, description, category_id) VALUES ('cheese', 2.39, 'mild cheddar (330g)', 1);INSERT INTO `product` (`name`, price, description, category_id) VALUES ('butter', 1.09, 'unsalted (250g)', 1);INSERT INTO `product` (`name`, price, description, category_id) VALUES ('free range eggs', 1.76, 'medium-sized (6 eggs)', 1);INSERT INTO `product` (`name`, price, description, category_id) VALUES ('organic meat patties', 2.29, 'rolled in fresh herbs<br>2 patties (250g)', 2);INSERT INTO `product` (`name`, price, description, category_id) VALUES ('parma ham', 3.49, 'matured, organic (70g)', 2);INSERT INTO `product` (`name`, price, description, category_id) VALUES ('chicken leg', 2.59, 'free range (250g)', 2);INSERT INTO `product` (`name`, price,