· 4 years ago · Jun 10, 2021, 07:10 PM
1
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 CONTROL
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema CONTROL
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `CONTROL` DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci ;
15USE `CONTROL` ;
16
17-- -----------------------------------------------------
18-- Table `CONTROL`.`SLOTS`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `CONTROL`.`SLOTS` (
21 `Slot_id` INT NOT NULL,
22 `name` VARCHAR(45) NULL,
23 PRIMARY KEY (`Slot_id`))
24ENGINE = InnoDB;
25
26
27-- -----------------------------------------------------
28-- Table `CONTROL`.`SEMESTERS`
29-- -----------------------------------------------------
30CREATE TABLE IF NOT EXISTS `CONTROL`.`SEMESTERS` (
31 `Semester_id` INT NOT NULL,
32 `semester` VARCHAR(45) NOT NULL,
33 `Slot_id` INT NOT NULL,
34 PRIMARY KEY (`Semester_id`),
35 INDEX `Slot_id_idx` (`Slot_id` ASC) VISIBLE,
36 CONSTRAINT `Slot_id`
37 FOREIGN KEY (`Slot_id`)
38 REFERENCES `CONTROL`.`SLOTS` (`Slot_id`)
39 ON DELETE NO ACTION
40 ON UPDATE NO ACTION)
41ENGINE = InnoDB;
42
43
44-- -----------------------------------------------------
45-- Table `CONTROL`.`STUDENTS`
46-- -----------------------------------------------------
47CREATE TABLE IF NOT EXISTS `CONTROL`.`STUDENTS` (
48 `Student_id` INT NOT NULL,
49 `identification` VARCHAR(45) NOT NULL,
50 `name` VARCHAR(80) NOT NULL,
51 `lastName` VARCHAR(80) NOT NULL,
52 `phone` VARCHAR(15) NOT NULL,
53 `mail` VARCHAR(256) NOT NULL,
54 `Semester_id` INT NOT NULL,
55 PRIMARY KEY (`Student_id`),
56 INDEX `Semester_id_idx` (`Semester_id` ASC) VISIBLE,
57 CONSTRAINT `Semester_id`
58 FOREIGN KEY (`Semester_id`)
59 REFERENCES `CONTROL`.`SEMESTERS` (`Semester_id`)
60 ON DELETE NO ACTION
61 ON UPDATE NO ACTION)
62ENGINE = InnoDB;
63
64
65-- -----------------------------------------------------
66-- Table `CONTROL`.`SESSIONS`
67-- -----------------------------------------------------
68CREATE TABLE IF NOT EXISTS `CONTROL`.`SESSIONS` (
69 `Session_id` INT NOT NULL,
70 `sessionName` VARCHAR(70) NOT NULL,
71 `Slot_id` INT NOT NULL,
72 PRIMARY KEY (`Session_id`),
73 INDEX `Slot2_id_idx` (`Slot_id` ASC) VISIBLE,
74 CONSTRAINT `Slot2_id`
75 FOREIGN KEY (`Slot_id`)
76 REFERENCES `CONTROL`.`SEMESTERS` (`Slot_id`)
77 ON DELETE NO ACTION
78 ON UPDATE NO ACTION)
79ENGINE = InnoDB;
80
81
82-- -----------------------------------------------------
83-- Table `CONTROL`.`ASSISTANCES`
84-- -----------------------------------------------------
85CREATE TABLE IF NOT EXISTS `CONTROL`.`ASSISTANCES` (
86 `Assistance_id` INT NOT NULL,
87 `dateStart` DATE NOT NULL,
88 `dateEnd` DATE NOT NULL,
89 `assistanceCheck` VARCHAR(45) NOT NULL,
90 `Session_id` INT NOT NULL,
91 `Student_id` INT NOT NULL,
92 PRIMARY KEY (`Assistance_id`),
93 INDEX `Student_id_idx` (`Student_id` ASC) VISIBLE,
94 INDEX `Session_id_idx` (`Session_id` ASC) VISIBLE,
95 CONSTRAINT `Student_id`
96 FOREIGN KEY (`Student_id`)
97 REFERENCES `CONTROL`.`STUDENTS` (`Student_id`)
98 ON DELETE NO ACTION
99 ON UPDATE NO ACTION,
100 CONSTRAINT `Session_id`
101 FOREIGN KEY (`Session_id`)
102 REFERENCES `CONTROL`.`SESSIONS` (`Session_id`)
103 ON DELETE NO ACTION
104 ON UPDATE NO ACTION)
105ENGINE = InnoDB;
106
107
108SET SQL_MODE=@OLD_SQL_MODE;
109SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
110SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
111