nstxW6TZ

1CREATE SCHEMA IF NOT EXISTS `online_shop`;
2
3CREATE TABLE IF NOT EXISTS `online_shop`.`items`
4(
5`item_id` INT(11) PRIMARY KEY,
6`name` VARCHAR(50),
7`item_type_id` INT(11)
8);
9
10CREATE TABLE IF NOT EXISTS `online_shop`.`item_types`
11(
12`item_type_id` INT(11) PRIMARY KEY,
13`name` VARCHAR(50)
14);
15
16CREATE TABLE IF NOT EXISTS `online_shop`.`customers`
17(
18`customer_id` INT(11) PRIMARY KEY,
19`name` VARCHAR(50),
20`birthday` DATE,
21`city_id` INT(11)
22);
23
24CREATE TABLE IF NOT EXISTS `online_shop`.`orders`
25(
26`order_id` INT(11) PRIMARY KEY,
27`customer_id` INT(11)
28);
29
30CREATE TABLE IF NOT EXISTS `online_shop`.`cities`
31(
32`city_id` INT(11) PRIMARY KEY,
33`name` VARCHAR(50)
34);
35
36CREATE TABLE IF NOT EXISTS `online_shop`.`order_items`
37(
38`order_id` INT(11) ,
39`item_id` INT(11),
40PRIMARY KEY(`order_id`,`item_id`)
41);
42ALTER TABLE `online_shop`.`items`
43ADD CONSTRAINT `fk_item_type_id`
44	FOREIGN KEY (`item_type_id`)
45    REFERENCES `online_shop`.`item_types` (`item_type_id`)
46    ON DELETE NO ACTION
47    ON UPDATE NO ACTION;
48    
49    ALTER TABLE `online_shop`.`customers`
50ADD CONSTRAINT `fk_city_id`
51	FOREIGN KEY (`city_id`)
52    REFERENCES `online_shop`.`cities` (`city_id`)
53    ON DELETE NO ACTION
54    ON UPDATE NO ACTION;
55    
56    ALTER TABLE `online_shop`.`orders`
57ADD CONSTRAINT `fk_customer_id`
58	FOREIGN KEY (`customer_id`)
59    REFERENCES `online_shop`.`customers` (`customer_id`)
60    ON DELETE NO ACTION
61    ON UPDATE NO ACTION;
62    
63     ALTER TABLE `online_shop`.`order_items`
64ADD CONSTRAINT `fk_order_id`
65	FOREIGN KEY (`order_id`)
66    REFERENCES `online_shop`.`orders` (`order_id`)
67    ON DELETE NO ACTION
68    ON UPDATE NO ACTION;
69    
70    ALTER TABLE `online_shop`.`order_items`
71ADD CONSTRAINT `fk_item_id`
72	FOREIGN KEY (`item_id`)
73    REFERENCES `online_shop`.`items` (`item_id`)
74    ON DELETE NO ACTION
75    ON UPDATE NO ACTION;