RKqWYFHq

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema zwhodb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema zwhodb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `zwhodb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
18USE `zwhodb` ;
19
20-- -----------------------------------------------------
21-- Table `zwhodb`.`teacher-sub`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `zwhodb`.`teacher-sub` (
24  `taxfile-number` VARCHAR(5) NOT NULL,
25  `title` VARCHAR(5) NOT NULL,
26  `type` VARCHAR(2) NOT NULL,
27  `teaching-hours` VARCHAR(2) NOT NULL,
28  PRIMARY KEY (`taxfile-number`))
29ENGINE = InnoDB
30DEFAULT CHARACTER SET = utf8mb4
31COLLATE = utf8mb4_0900_ai_ci;
32
33
34-- -----------------------------------------------------
35-- Table `zwhodb`.`school`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `zwhodb`.`school` (
38  `school-code` VARCHAR(6) NOT NULL,
39  `phone` VARCHAR(8) NOT NULL,
40  `name` VARCHAR(20) NOT NULL,
41  `town` VARCHAR(40) NOT NULL,
42  `street` VARCHAR(50) NOT NULL,
43  `post-code` VARCHAR(10) NOT NULL,
44  `teacher-sub_taxfile-number` VARCHAR(5) NOT NULL,
45  PRIMARY KEY (`school-code`, `teacher-sub_taxfile-number`),
46  INDEX `fk_school_teacher-sub_idx` (`teacher-sub_taxfile-number` ASC) VISIBLE,
47  CONSTRAINT `fk_school_teacher-sub`
48    FOREIGN KEY (`teacher-sub_taxfile-number`)
49    REFERENCES `zwhodb`.`teacher-sub` (`taxfile-number`)
50    ON DELETE NO ACTION
51    ON UPDATE NO ACTION)
52ENGINE = InnoDB
53DEFAULT CHARACTER SET = utf8mb4
54COLLATE = utf8mb4_0900_ai_ci;
55
56
57-- -----------------------------------------------------
58-- Table `zwhodb`.`student`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `zwhodb`.`student` (
61  `student-number` VARCHAR(9) NOT NULL,
62  `firstname` VARCHAR(10) NOT NULL,
63  `lastname` VARCHAR(10) NOT NULL,
64  `gender` VARCHAR(6) NOT NULL,
65  `birth-date` DATETIME(6) NOT NULL,
66  PRIMARY KEY (`student-number`))
67ENGINE = InnoDB
68DEFAULT CHARACTER SET = utf8mb4
69COLLATE = utf8mb4_0900_ai_ci;
70
71
72-- -----------------------------------------------------
73-- Table `zwhodb`.`student-sub`
74-- -----------------------------------------------------
75CREATE TABLE IF NOT EXISTS `zwhodb`.`student-sub` (
76  `student-number` VARCHAR(9) NOT NULL,
77  `title` VARCHAR(5) NOT NULL,
78  `type` VARCHAR(2) NOT NULL,
79  PRIMARY KEY (`student-number`))
80ENGINE = InnoDB
81DEFAULT CHARACTER SET = utf8mb4
82COLLATE = utf8mb4_0900_ai_ci;
83
84
85-- -----------------------------------------------------
86-- Table `zwhodb`.`subject`
87-- -----------------------------------------------------
88CREATE TABLE IF NOT EXISTS `zwhodb`.`subject` (
89  `title` VARCHAR(5) NOT NULL,
90  `type` VARCHAR(2) NOT NULL,
91  PRIMARY KEY (`title`))
92ENGINE = InnoDB
93DEFAULT CHARACTER SET = utf8mb4
94COLLATE = utf8mb4_0900_ai_ci;
95
96
97-- -----------------------------------------------------
98-- Table `zwhodb`.`teacher`
99-- -----------------------------------------------------
100CREATE TABLE IF NOT EXISTS `zwhodb`.`teacher` (
101  `taxfile-number` VARCHAR(5) NOT NULL,
102  `qualification` VARCHAR(20) NOT NULL,
103  `firstname` VARCHAR(10) NOT NULL,
104  `lastname` VARCHAR(10) NOT NULL,
105  `gender` VARCHAR(6) NOT NULL,
106  PRIMARY KEY (`taxfile-number`))
107ENGINE = InnoDB
108DEFAULT CHARACTER SET = utf8mb4
109COLLATE = utf8mb4_0900_ai_ci;
110
111
112SET SQL_MODE=@OLD_SQL_MODE;
113SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
114SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;