· 5 years ago · Mar 17, 2020, 10:50 PM
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-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
15USE `mydb` ;
16
17-- -----------------------------------------------------
18-- Table `mydb`.`assessment`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`assessment` (
21 `Exam number` VARCHAR(45) NOT NULL,
22 `Assignemt 1` VARCHAR(20) NULL DEFAULT NULL,
23 `Assignemt 2` VARCHAR(45) NULL DEFAULT NULL,
24 `Exam` VARCHAR(45) NULL DEFAULT NULL,
25 PRIMARY KEY (`Exam number`))
26ENGINE = InnoDB
27DEFAULT CHARACTER SET = utf8;
28
29
30-- -----------------------------------------------------
31-- Table `mydb`.`class group`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `mydb`.`class group` (
34 `Class_Name` VARCHAR(20) NOT NULL,
35 `Class_Course` VARCHAR(45) NOT NULL,
36 PRIMARY KEY (`Class_Name`))
37ENGINE = InnoDB
38DEFAULT CHARACTER SET = utf8;
39
40
41-- -----------------------------------------------------
42-- Table `mydb`.`course`
43-- -----------------------------------------------------
44CREATE TABLE IF NOT EXISTS `mydb`.`course` (
45 `Course_ID` VARCHAR(30) NOT NULL,
46 `Course_Name` VARCHAR(45) NOT NULL,
47 `Course_AmmountOfStudents` VARCHAR(45) NOT NULL,
48 `Class Group_Class_Name` VARCHAR(20) NOT NULL,
49 `Result_Result` VARCHAR(30) NOT NULL,
50 PRIMARY KEY (`Course_ID`),
51 INDEX `fk_Course_Class Group1_idx` (`Class Group_Class_Name` ASC) VISIBLE,
52 CONSTRAINT `fk_Course_Class Group1`
53 FOREIGN KEY (`Class Group_Class_Name`)
54 REFERENCES `mydb`.`class group` (`Class_Name`))
55ENGINE = InnoDB
56DEFAULT CHARACTER SET = utf8;
57
58
59-- -----------------------------------------------------
60-- Table `mydb`.`teacher`
61-- -----------------------------------------------------
62CREATE TABLE IF NOT EXISTS `mydb`.`teacher` (
63 `Teach_ID` INT NOT NULL,
64 `Fname` VARCHAR(45) NOT NULL,
65 `Surname` VARCHAR(45) NOT NULL,
66 `PhoneNumber` INT NOT NULL,
67 `Email` VARCHAR(45) NOT NULL,
68 `Address1` VARCHAR(45) NOT NULL,
69 `Address2` VARCHAR(45) NOT NULL,
70 `town` VARCHAR(45) NOT NULL,
71 `City` VARCHAR(45) NOT NULL,
72 `Course` VARCHAR(45) NOT NULL,
73 PRIMARY KEY (`Teach_ID`))
74ENGINE = InnoDB
75DEFAULT CHARACTER SET = utf8;
76
77
78-- -----------------------------------------------------
79-- Table `mydb`.`modules`
80-- -----------------------------------------------------
81CREATE TABLE IF NOT EXISTS `mydb`.`modules` (
82 `Module_ID` VARCHAR(10) NOT NULL,
83 `Module_name` VARCHAR(45) NULL DEFAULT NULL,
84 `Course_Course_ID` VARCHAR(30) NOT NULL,
85 `Teacher_Teach_ID` INT NOT NULL,
86 PRIMARY KEY (`Module_ID`),
87 INDEX `fk_Modules_Course1_idx` (`Course_Course_ID` ASC) VISIBLE,
88 INDEX `fk_Modules_Teacher1_idx` (`Teacher_Teach_ID` ASC) VISIBLE,
89 CONSTRAINT `fk_Modules_Course1`
90 FOREIGN KEY (`Course_Course_ID`)
91 REFERENCES `mydb`.`course` (`Course_ID`),
92 CONSTRAINT `fk_Modules_Teacher1`
93 FOREIGN KEY (`Teacher_Teach_ID`)
94 REFERENCES `mydb`.`teacher` (`Teach_ID`))
95ENGINE = InnoDB
96DEFAULT CHARACTER SET = utf8;
97
98
99-- -----------------------------------------------------
100-- Table `mydb`.`student`
101-- -----------------------------------------------------
102CREATE TABLE IF NOT EXISTS `mydb`.`student` (
103 `Stud_ID` INT NOT NULL,
104 `Fname` VARCHAR(45) NOT NULL,
105 `Surname` VARCHAR(45) NOT NULL,
106 `PPSN` VARCHAR(45) NOT NULL,
107 `DOB` VARCHAR(45) NOT NULL,
108 `PhoneNumber` INT NOT NULL,
109 `Emial` VARCHAR(45) NOT NULL,
110 `Address1` VARCHAR(45) NOT NULL,
111 `Address2` VARCHAR(45) NOT NULL,
112 `Adress3` VARCHAR(45) NOT NULL,
113 `Nationality` VARCHAR(45) NOT NULL,
114 `Course` VARCHAR(45) NOT NULL,
115 `Group_name` VARCHAR(45) NOT NULL,
116 `Class Group_Class_Name` VARCHAR(20) NULL,
117 PRIMARY KEY (`Stud_ID`),
118 INDEX `fk_Student_Class Group_idx` (`Class Group_Class_Name` ASC) VISIBLE,
119 CONSTRAINT `fk_Student_Class Group`
120 FOREIGN KEY (`Class Group_Class_Name`)
121 REFERENCES `mydb`.`class group` (`Class_Name`))
122ENGINE = InnoDB
123DEFAULT CHARACTER SET = utf8;
124
125
126-- -----------------------------------------------------
127-- Table `mydb`.`result`
128-- -----------------------------------------------------
129CREATE TABLE IF NOT EXISTS `mydb`.`result` (
130 `Modules_Module_ID` VARCHAR(10) NOT NULL,
131 `Student_Stud_ID` INT NOT NULL,
132 `Tasks_Exam number` VARCHAR(45) NOT NULL,
133 INDEX `fk_Result_Modules1_idx` (`Modules_Module_ID` ASC) VISIBLE,
134 INDEX `fk_Result_Student1_idx` (`Student_Stud_ID` ASC) VISIBLE,
135 INDEX `fk_Result_Tasks1_idx` (`Tasks_Exam number` ASC) VISIBLE,
136 CONSTRAINT `fk_Result_Modules1`
137 FOREIGN KEY (`Modules_Module_ID`)
138 REFERENCES `mydb`.`modules` (`Module_ID`),
139 CONSTRAINT `fk_Result_Student1`
140 FOREIGN KEY (`Student_Stud_ID`)
141 REFERENCES `mydb`.`student` (`Stud_ID`),
142 CONSTRAINT `fk_Result_Tasks1`
143 FOREIGN KEY (`Tasks_Exam number`)
144 REFERENCES `mydb`.`assessment` (`Exam number`))
145ENGINE = InnoDB
146DEFAULT CHARACTER SET = utf8;
147
148
149SET SQL_MODE=@OLD_SQL_MODE;
150SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
151SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;