· 9 years ago · Nov 17, 2016, 07:34 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='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema sekretariat
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema sekretariat
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `sekretariat` DEFAULT CHARACTER SET utf8 ;
15USE `sekretariat` ;
16
17-- -----------------------------------------------------
18-- Table `sekretariat`.`school`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `sekretariat`.`school` (
21 `idschool` INT NOT NULL AUTO_INCREMENT,
22 `name` VARCHAR(45) NOT NULL,
23 `type` VARCHAR(45) NOT NULL,
24 `number` INT NOT NULL,
25 `street` VARCHAR(45) NOT NULL,
26 `address` VARCHAR(45) NOT NULL,
27 `postalcode` VARCHAR(6) NOT NULL,
28 `city` VARCHAR(45) NOT NULL,
29 `isActive` INT NOT NULL,
30 `info` VARCHAR(255) NOT NULL,
31 PRIMARY KEY (`idschool`))
32ENGINE = InnoDB;
33
34
35-- -----------------------------------------------------
36-- Table `sekretariat`.`employee`
37-- -----------------------------------------------------
38CREATE TABLE IF NOT EXISTS `sekretariat`.`employee` (
39 `idemployee` INT NOT NULL AUTO_INCREMENT,
40 `name` VARCHAR(45) NOT NULL,
41 `surname` VARCHAR(45) NOT NULL,
42 `title` VARCHAR(45) NOT NULL,
43 `role` VARCHAR(45) NOT NULL,
44 `street` VARCHAR(45) NOT NULL,
45 `address` VARCHAR(45) NOT NULL,
46 `postalcode` VARCHAR(6) NOT NULL,
47 `city` VARCHAR(45) NOT NULL,
48 `pesel` VARCHAR(11) NOT NULL,
49 `isActive` VARCHAR(45) NOT NULL,
50 `note` VARCHAR(45) NOT NULL,
51 `salary` FLOAT NOT NULL,
52 PRIMARY KEY (`idemployee`))
53ENGINE = InnoDB;
54
55
56-- -----------------------------------------------------
57-- Table `sekretariat`.`schoolclass`
58-- -----------------------------------------------------
59CREATE TABLE IF NOT EXISTS `sekretariat`.`schoolclass` (
60 `idschoolclass` INT NOT NULL AUTO_INCREMENT,
61 `startYear` INT NOT NULL,
62 `number` INT NOT NULL,
63 `letter` VARCHAR(5) NOT NULL,
64 `school_idschool` INT NOT NULL,
65 PRIMARY KEY (`idschoolclass`),
66 INDEX `fk_schoolclass_school1_idx` (`school_idschool` ASC),
67 CONSTRAINT `fk_schoolclass_school1`
68 FOREIGN KEY (`school_idschool`)
69 REFERENCES `sekretariat`.`school` (`idschool`)
70 ON DELETE NO ACTION
71 ON UPDATE NO ACTION)
72ENGINE = InnoDB;
73
74
75-- -----------------------------------------------------
76-- Table `sekretariat`.`teacher`
77-- -----------------------------------------------------
78CREATE TABLE IF NOT EXISTS `sekretariat`.`teacher` (
79 `idteacher` INT NOT NULL AUTO_INCREMENT,
80 `isActive` INT NOT NULL,
81 `employee_idemployee` INT NOT NULL,
82 `schoolclass_idschoolclass` INT NOT NULL,
83 PRIMARY KEY (`idteacher`),
84 INDEX `fk_teacher_employee1_idx` (`employee_idemployee` ASC),
85 INDEX `fk_teacher_schoolclass1_idx` (`schoolclass_idschoolclass` ASC),
86 CONSTRAINT `fk_teacher_employee1`
87 FOREIGN KEY (`employee_idemployee`)
88 REFERENCES `sekretariat`.`employee` (`idemployee`)
89 ON DELETE NO ACTION
90 ON UPDATE NO ACTION,
91 CONSTRAINT `fk_teacher_schoolclass1`
92 FOREIGN KEY (`schoolclass_idschoolclass`)
93 REFERENCES `sekretariat`.`schoolclass` (`idschoolclass`)
94 ON DELETE NO ACTION
95 ON UPDATE NO ACTION)
96ENGINE = InnoDB;
97
98
99-- -----------------------------------------------------
100-- Table `sekretariat`.`student`
101-- -----------------------------------------------------
102CREATE TABLE IF NOT EXISTS `sekretariat`.`student` (
103 `idstudent` INT NOT NULL AUTO_INCREMENT,
104 `name` VARCHAR(45) NOT NULL,
105 `surname` VARCHAR(45) NOT NULL,
106 `street` VARCHAR(45) NOT NULL,
107 `address` VARCHAR(45) NOT NULL,
108 `postalcode` VARCHAR(6) NOT NULL,
109 `city` VARCHAR(45) NOT NULL,
110 `pesel` VARCHAR(45) NOT NULL,
111 `beginDate` DATE NOT NULL,
112 `endDate` DATE NOT NULL,
113 `phone` VARCHAR(45) NOT NULL,
114 `email` VARCHAR(45) NULL,
115 `isActive` INT NULL,
116 `schoolclass_idschoolclass` INT NOT NULL,
117 PRIMARY KEY (`idstudent`),
118 INDEX `fk_student_schoolclass1_idx` (`schoolclass_idschoolclass` ASC),
119 CONSTRAINT `fk_student_schoolclass1`
120 FOREIGN KEY (`schoolclass_idschoolclass`)
121 REFERENCES `sekretariat`.`schoolclass` (`idschoolclass`)
122 ON DELETE NO ACTION
123 ON UPDATE NO ACTION)
124ENGINE = InnoDB;
125
126
127-- -----------------------------------------------------
128-- Table `sekretariat`.`subject`
129-- -----------------------------------------------------
130CREATE TABLE IF NOT EXISTS `sekretariat`.`subject` (
131 `idsubject` INT NOT NULL,
132 `name` VARCHAR(45) NOT NULL,
133 `obligatory` INT NOT NULL,
134 PRIMARY KEY (`idsubject`))
135ENGINE = InnoDB;
136
137
138-- -----------------------------------------------------
139-- Table `sekretariat`.`grade`
140-- -----------------------------------------------------
141CREATE TABLE IF NOT EXISTS `sekretariat`.`grade` (
142 `idnote` INT NOT NULL,
143 `student_idstudent` INT NOT NULL,
144 `subject_idsubject` INT NOT NULL,
145 PRIMARY KEY (`idnote`),
146 INDEX `fk_grade_student1_idx` (`student_idstudent` ASC),
147 INDEX `fk_grade_subject1_idx` (`subject_idsubject` ASC),
148 CONSTRAINT `fk_grade_student1`
149 FOREIGN KEY (`student_idstudent`)
150 REFERENCES `sekretariat`.`student` (`idstudent`)
151 ON DELETE NO ACTION
152 ON UPDATE NO ACTION,
153 CONSTRAINT `fk_grade_subject1`
154 FOREIGN KEY (`subject_idsubject`)
155 REFERENCES `sekretariat`.`subject` (`idsubject`)
156 ON DELETE NO ACTION
157 ON UPDATE NO ACTION)
158ENGINE = InnoDB;
159
160
161-- -----------------------------------------------------
162-- Table `sekretariat`.`studentparent`
163-- -----------------------------------------------------
164CREATE TABLE IF NOT EXISTS `sekretariat`.`studentparent` (
165 `idstudentparent` INT NOT NULL AUTO_INCREMENT,
166 `parentName` VARCHAR(45) NOT NULL,
167 `parentSurname` VARCHAR(45) NOT NULL,
168 `parentAddress` VARCHAR(45) NOT NULL,
169 `parentPhone` VARCHAR(45) NOT NULL,
170 `parentEmail` VARCHAR(45) NOT NULL,
171 `student_idstudent` INT NOT NULL,
172 PRIMARY KEY (`idstudentparent`),
173 INDEX `fk_studentparent_student1_idx` (`student_idstudent` ASC),
174 CONSTRAINT `fk_studentparent_student1`
175 FOREIGN KEY (`student_idstudent`)
176 REFERENCES `sekretariat`.`student` (`idstudent`)
177 ON DELETE NO ACTION
178 ON UPDATE NO ACTION)
179ENGINE = InnoDB;
180
181
182-- -----------------------------------------------------
183-- Table `sekretariat`.`user`
184-- -----------------------------------------------------
185CREATE TABLE IF NOT EXISTS `sekretariat`.`user` (
186 `iduser` INT NOT NULL AUTO_INCREMENT,
187 `login` VARCHAR(45) NOT NULL,
188 `password` VARCHAR(45) NOT NULL,
189 `type` VARCHAR(45) NOT NULL,
190 `recoveryMail` VARCHAR(45) NOT NULL,
191 `creationDate` DATETIME NOT NULL,
192 `creationUser` VARCHAR(45) NOT NULL,
193 `editDate` DATETIME NOT NULL,
194 `editUser` VARCHAR(45) NOT NULL,
195 PRIMARY KEY (`iduser`))
196ENGINE = InnoDB;
197
198
199SET SQL_MODE=@OLD_SQL_MODE;
200SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
201SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;