· 7 years ago · Dec 16, 2018, 04:22 AM
1-- MySQL Script generated by MySQL Workbench
2-- Sat Dec 15 19:31:56 2018
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 mydb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`Professor`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`Professor` (
24 `SSN` INT NOT NULL,
25 `Name` VARCHAR(45) NOT NULL,
26 `Address_Street` VARCHAR(45) NULL,
27 `Address_City` VARCHAR(45) NULL,
28 `Address_State` VARCHAR(45) NULL,
29 `Address_Zip` INT NULL,
30 `Phone_AreaCode` INT NULL,
31 `Phone_Number` INT NULL,
32 `Title` VARCHAR(45) NULL,
33 `Sex` VARCHAR(45) NULL,
34 `Salary` INT NULL,
35 `Degrees` VARCHAR(45) NULL,
36 PRIMARY KEY (`SSN`))
37ENGINE = InnoDB;
38
39
40-- -----------------------------------------------------
41-- Table `mydb`.`Department`
42-- -----------------------------------------------------
43CREATE TABLE IF NOT EXISTS `mydb`.`Department` (
44 `DNum` INT NOT NULL AUTO_INCREMENT,
45 `Name` VARCHAR(45) NOT NULL,
46 `Phone` INT NULL,
47 `Location` VARCHAR(45) NULL,
48 `Chair_SSN` INT NOT NULL,
49 PRIMARY KEY (`DNum`),
50 INDEX `fk_Department_Professor2_idx` (`Chair_SSN` ASC) VISIBLE,
51 CONSTRAINT `fk_Department_Professor2`
52 FOREIGN KEY (`Chair_SSN`)
53 REFERENCES `mydb`.`Professor` (`SSN`)
54 ON DELETE NO ACTION
55 ON UPDATE NO ACTION)
56ENGINE = InnoDB;
57
58
59-- -----------------------------------------------------
60-- Table `mydb`.`Student`
61-- -----------------------------------------------------
62CREATE TABLE IF NOT EXISTS `mydb`.`Student` (
63 `CWID` INT NOT NULL AUTO_INCREMENT,
64 `FName` VARCHAR(45) NOT NULL,
65 `LName` VARCHAR(45) NOT NULL,
66 `Adress` VARCHAR(45) NULL,
67 `Phone` INT NULL,
68 `Major_DNum` INT NOT NULL,
69 PRIMARY KEY (`CWID`),
70 INDEX `fk_Student_Department1_idx` (`Major_DNum` ASC) VISIBLE,
71 CONSTRAINT `fk_Student_Department1`
72 FOREIGN KEY (`Major_DNum`)
73 REFERENCES `mydb`.`Department` (`DNum`)
74 ON DELETE NO ACTION
75 ON UPDATE NO ACTION)
76ENGINE = InnoDB;
77
78
79-- -----------------------------------------------------
80-- Table `mydb`.`Course`
81-- -----------------------------------------------------
82CREATE TABLE IF NOT EXISTS `mydb`.`Course` (
83 `CNum` INT NOT NULL AUTO_INCREMENT,
84 `Title` VARCHAR(45) NOT NULL,
85 `Textbook` VARCHAR(45) NULL,
86 `Units` INT NULL,
87 `Department_DNum` INT NOT NULL,
88 PRIMARY KEY (`CNum`),
89 INDEX `fk_Course_Department1_idx` (`Department_DNum` ASC) VISIBLE,
90 CONSTRAINT `fk_Course_Department1`
91 FOREIGN KEY (`Department_DNum`)
92 REFERENCES `mydb`.`Department` (`DNum`)
93 ON DELETE NO ACTION
94 ON UPDATE NO ACTION)
95ENGINE = InnoDB;
96
97
98-- -----------------------------------------------------
99-- Table `mydb`.`Prof_Degrees`
100-- -----------------------------------------------------
101CREATE TABLE IF NOT EXISTS `mydb`.`Prof_Degrees` (
102 `Degrees` VARCHAR(45) NOT NULL,
103 `Professor_SSN` INT NOT NULL,
104 PRIMARY KEY (`Professor_SSN`, `Degrees`),
105 CONSTRAINT `fk_Prof_Degrees_Professor2`
106 FOREIGN KEY (`Professor_SSN`)
107 REFERENCES `mydb`.`Professor` (`SSN`)
108 ON DELETE NO ACTION
109 ON UPDATE NO ACTION)
110ENGINE = InnoDB;
111
112
113-- -----------------------------------------------------
114-- Table `mydb`.`Section`
115-- -----------------------------------------------------
116CREATE TABLE IF NOT EXISTS `mydb`.`Section` (
117 `SNum` INT NOT NULL AUTO_INCREMENT,
118 `Course_CNum` INT NOT NULL,
119 `Room` VARCHAR(45) NULL,
120 `Meeting_Days` VARCHAR(45) NULL,
121 `Start_time` VARCHAR(45) NULL,
122 `End_time` VARCHAR(45) NULL,
123 `Num_Seats` VARCHAR(45) NULL,
124 `Professor_SSN` INT NOT NULL,
125 PRIMARY KEY (`SNum`, `Course_CNum`),
126 INDEX `fk_Section_Professor2_idx` (`Professor_SSN` ASC) VISIBLE,
127 CONSTRAINT `fk_Section_Course1`
128 FOREIGN KEY (`Course_CNum`)
129 REFERENCES `mydb`.`Course` (`CNum`)
130 ON DELETE NO ACTION
131 ON UPDATE NO ACTION,
132 CONSTRAINT `fk_Section_Professor2`
133 FOREIGN KEY (`Professor_SSN`)
134 REFERENCES `mydb`.`Professor` (`SSN`)
135 ON DELETE NO ACTION
136 ON UPDATE NO ACTION)
137ENGINE = InnoDB;
138
139
140-- -----------------------------------------------------
141-- Table `mydb`.`Minor`
142-- -----------------------------------------------------
143CREATE TABLE IF NOT EXISTS `mydb`.`Minor` (
144 `Student_CWID` INT NOT NULL,
145 `Department_DNum` INT NOT NULL,
146 PRIMARY KEY (`Student_CWID`, `Department_DNum`),
147 INDEX `fk_Minor_Student1_idx` (`Student_CWID` ASC) VISIBLE,
148 INDEX `fk_Minor_Department1_idx` (`Department_DNum` ASC) VISIBLE,
149 CONSTRAINT `fk_Minor_Student1`
150 FOREIGN KEY (`Student_CWID`)
151 REFERENCES `mydb`.`Student` (`CWID`)
152 ON DELETE NO ACTION
153 ON UPDATE NO ACTION,
154 CONSTRAINT `fk_Minor_Department1`
155 FOREIGN KEY (`Department_DNum`)
156 REFERENCES `mydb`.`Department` (`DNum`)
157 ON DELETE NO ACTION
158 ON UPDATE NO ACTION)
159ENGINE = InnoDB;
160
161
162-- -----------------------------------------------------
163-- Table `mydb`.`Prerequisites`
164-- -----------------------------------------------------
165CREATE TABLE IF NOT EXISTS `mydb`.`Prerequisites` (
166 `Course_CNum` INT NOT NULL,
167 `Prereq_Num` INT NOT NULL,
168 PRIMARY KEY (`Course_CNum`, `Prereq_Num`),
169 CONSTRAINT `fk_Prerequisites_Course1`
170 FOREIGN KEY (`Course_CNum`)
171 REFERENCES `mydb`.`Course` (`CNum`)
172 ON DELETE NO ACTION
173 ON UPDATE NO ACTION)
174ENGINE = InnoDB;
175
176
177-- -----------------------------------------------------
178-- Table `mydb`.`Record`
179-- -----------------------------------------------------
180CREATE TABLE IF NOT EXISTS `mydb`.`Record` (
181 `Student_CWID` INT NOT NULL,
182 `Course_CNum` INT NOT NULL,
183 `Grade` DOUBLE NULL,
184 PRIMARY KEY (`Student_CWID`, `Course_CNum`),
185 INDEX `fk_Record_Course1_idx` (`Course_CNum` ASC) VISIBLE,
186 CONSTRAINT `fk_Record_Student1`
187 FOREIGN KEY (`Student_CWID`)
188 REFERENCES `mydb`.`Student` (`CWID`)
189 ON DELETE NO ACTION
190 ON UPDATE NO ACTION,
191 CONSTRAINT `fk_Record_Course1`
192 FOREIGN KEY (`Course_CNum`)
193 REFERENCES `mydb`.`Course` (`CNum`)
194 ON DELETE NO ACTION
195 ON UPDATE NO ACTION)
196ENGINE = InnoDB;
197
198
199SET SQL_MODE=@OLD_SQL_MODE;
200SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
201SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;