yaV8BwQp

1DROP DATABASE `mick_sample`;
2CREATE DATABASE IF NOT EXISTS `mick_sample` DEFAULT CHARACTER SET utf8mb4;
3USE mick_sample;
4
5CREATE TABLE IF NOT EXISTS `sisya` (
6  sisya_code VARCHAR(191),
7  sisya_name VARCHAR(191),
8  PRIMARY KEY (sisya_code)
9);
10
11CREATE TABLE IF NOT EXISTS `siten` (
12  sisya_code VARCHAR(191),
13  siten_code VARCHAR(191),
14  siten_name VARCHAR(191),
15  PRIMARY KEY (sisya_code, siten_code),
16  FOREIGN KEY (sisya_code)
17    REFERENCES sisya(sisya_code)
18);
19
20CREATE TABLE IF NOT EXISTS `syohin_group` (
21  syohin_group_code VARCHAR(191),
22  group_name VARCHAR(191),
23  PRIMARY KEY (syohin_group_code)
24);
25
26CREATE TABLE IF NOT EXISTS `syohin` (
27  syohin_code VARCHAR(191),
28  syohin_name VARCHAR(191),
29  syohin_group_code VARCHAR(191),
30  PRIMARY KEY (syohin_code),
31  FOREIGN KEY (syohin_group_code)
32    REFERENCES syohin_group(syohin_group_code)
33);
34
35CREATE TABLE IF NOT EXISTS `siten_syohin` (
36  sisya_code VARCHAR(191),
37  siten_code VARCHAR(191),
38  syohin_code VARCHAR(191),
39  PRIMARY KEY (sisya_code, siten_code, syohin_code),
40  FOREIGN KEY (sisya_code, siten_code)
41    REFERENCES siten(sisya_code, siten_code),
42  FOREIGN KEY (syohin_code)
43    REFERENCES syohin(syohin_code)
44);