· 6 years ago · Apr 14, 2019, 10:14 AM
1-- MySQL Script generated by MySQL Workbench
2-- Sun Apr 14 00:33:46 2019
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @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';
9
10-- -----------------------------------------------------
11-- Schema regol
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema regol
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `regol` DEFAULT CHARACTER SET utf8 ;
18USE `regol` ;
19
20-- -----------------------------------------------------
21-- Table `regol`.`person`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `regol`.`person` (
24 `person_id` INT NOT NULL,
25 PRIMARY KEY (`person_id`))
26ENGINE = InnoDB;
27
28
29-- -----------------------------------------------------
30-- Table `regol`.`department`
31-- -----------------------------------------------------
32CREATE TABLE IF NOT EXISTS `regol`.`department` (
33 `dept_name` VARCHAR(20) NOT NULL,
34 `dept_address` VARCHAR(200) NULL,
35 `dept_phone_number` VARCHAR(10) NULL,
36 PRIMARY KEY (`dept_name`))
37ENGINE = InnoDB;
38
39
40-- -----------------------------------------------------
41-- Table `regol`.`batch`
42-- -----------------------------------------------------
43CREATE TABLE IF NOT EXISTS `regol`.`batch` (
44 `batch_id` INT NOT NULL,
45 `batch_year_of_graduation` INT NULL,
46 PRIMARY KEY (`batch_id`))
47ENGINE = InnoDB;
48
49
50-- -----------------------------------------------------
51-- Table `regol`.`course`
52-- -----------------------------------------------------
53CREATE TABLE IF NOT EXISTS `regol`.`course` (
54 `course_id` INT NOT NULL,
55 `dept_name` VARCHAR(20) NULL,
56 `degree_name` VARCHAR(45) NULL,
57 `course_name` VARCHAR(45) NULL,
58 `years` INT NULL,
59 `batch_batch_id` INT NOT NULL,
60 PRIMARY KEY (`course_id`, `batch_batch_id`),
61 INDEX `fk_course_batch1_idx` (`batch_batch_id` ASC) VISIBLE,
62 CONSTRAINT `dept_name`
63 FOREIGN KEY ()
64 REFERENCES `regol`.`department` ()
65 ON DELETE NO ACTION
66 ON UPDATE NO ACTION,
67 CONSTRAINT `fk_course_batch1`
68 FOREIGN KEY (`batch_batch_id`)
69 REFERENCES `regol`.`batch` (`batch_id`)
70 ON DELETE NO ACTION
71 ON UPDATE NO ACTION)
72ENGINE = InnoDB;
73
74
75-- -----------------------------------------------------
76-- Table `regol`.`student`
77-- -----------------------------------------------------
78CREATE TABLE IF NOT EXISTS `regol`.`student` (
79 `person_id` INT NOT NULL,
80 `enrollment_no` INT NULL,
81 `course_id` INT NULL,
82 `batch_id` INT NULL,
83 `bhawan_name` VARCHAR(15) NULL,
84 `room_number` VARCHAR(10) NULL,
85 PRIMARY KEY (`person_id`),
86 CONSTRAINT `person_id`
87 FOREIGN KEY ()
88 REFERENCES `regol`.`person` ()
89 ON DELETE NO ACTION
90 ON UPDATE NO ACTION,
91 CONSTRAINT `course_id`
92 FOREIGN KEY ()
93 REFERENCES `regol`.`course` ()
94 ON DELETE NO ACTION
95 ON UPDATE NO ACTION)
96ENGINE = InnoDB;
97
98
99-- -----------------------------------------------------
100-- Table `regol`.`parent_child`
101-- -----------------------------------------------------
102CREATE TABLE IF NOT EXISTS `regol`.`parent_child` (
103 `parent_person_id` INT NULL,
104 `child_person_id` INT NOT NULL,
105 PRIMARY KEY (`parent_person_id`, `child_person_id`),
106 INDEX `parent_person_id_idx` (`parent_person_id` ASC, `child_person_id` ASC) VISIBLE,
107 CONSTRAINT `parent_person_id`
108 FOREIGN KEY (`parent_person_id` , `child_person_id`)
109 REFERENCES `regol`.`person` (`person_id` , `person_id`)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION,
112 CONSTRAINT `child_person_id`
113 FOREIGN KEY ()
114 REFERENCES `regol`.`person` ()
115 ON DELETE NO ACTION
116 ON UPDATE NO ACTION)
117ENGINE = InnoDB;
118
119
120-- -----------------------------------------------------
121-- Table `regol`.`personal_info`
122-- -----------------------------------------------------
123CREATE TABLE IF NOT EXISTS `regol`.`personal_info` (
124 `person_id` INT NOT NULL,
125 `name` VARCHAR(45) NULL,
126 `date_of_birth` VARCHAR(45) NULL,
127 `phone_number` VARCHAR(10) NULL,
128 `permanent_address` VARCHAR(200) NULL,
129 `category` VARCHAR(10) NULL,
130 `blood_group` VARCHAR(3) NULL,
131 PRIMARY KEY (`person_id`),
132 CONSTRAINT `person_id`
133 FOREIGN KEY ()
134 REFERENCES `regol`.`person` ()
135 ON DELETE NO ACTION
136 ON UPDATE NO ACTION)
137ENGINE = InnoDB;
138
139
140-- -----------------------------------------------------
141-- Table `regol`.`verified`
142-- -----------------------------------------------------
143CREATE TABLE IF NOT EXISTS `regol`.`verified` (
144 `enrollment_no` INT NOT NULL,
145 `personal_info_verified` TINYINT NULL DEFAULT 0,
146 `student_info_verified` TINYINT NULL DEFAULT 0,
147 PRIMARY KEY (`enrollment_no`),
148 UNIQUE INDEX `enrollment_no_UNIQUE` (`enrollment_no` ASC) VISIBLE,
149 CONSTRAINT `enrollment_no`
150 FOREIGN KEY ()
151 REFERENCES `regol`.`student` ()
152 ON DELETE NO ACTION
153 ON UPDATE NO ACTION)
154ENGINE = InnoDB;
155
156
157-- -----------------------------------------------------
158-- Table `regol`.`secret_keys`
159-- -----------------------------------------------------
160CREATE TABLE IF NOT EXISTS `regol`.`secret_keys` (
161 `enrollment_no` INT NOT NULL,
162 `secret_key` VARCHAR(8) NULL,
163 PRIMARY KEY (`enrollment_no`),
164 CONSTRAINT `enrollment_no`
165 FOREIGN KEY (`enrollment_no`)
166 REFERENCES `regol`.`student` (`person_id`)
167 ON DELETE NO ACTION
168 ON UPDATE NO ACTION)
169ENGINE = InnoDB;
170
171
172SET SQL_MODE=@OLD_SQL_MODE;
173SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
174SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;