CREATE TABLE `cities` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`city_id`)
);

CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `city_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`customer_id`),
  KEY `FK_CITY_idx` (`city_id`),
  CONSTRAINT `FK_CITY` FOREIGN KEY (`city_id`) REFERENCES `cities` (`city_id`)
);

CREATE TABLE `item_types` (
  `item_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`item_type_id`)
);

CREATE TABLE `items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `item_type_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`item_id`),
  KEY `FK_ITEM_TYPE_idx` (`item_type_id`),
  CONSTRAINT `FK_ITEM_TYPE` FOREIGN KEY (`item_type_id`) REFERENCES `item_types` (`item_type_id`)
);

CREATE TABLE `order_items` (
  `order_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  PRIMARY KEY (`order_id`,`item_id`),
  KEY `FK_ITEM_idx` (`item_id`),
  CONSTRAINT `FK_ITEM` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`),
  CONSTRAINT `FK_ORDER_FK` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`)
);

CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `FK_ORDER_idx` (`customer_id`),
  CONSTRAINT `FK_ORDER` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`)
);