· 8 years ago · Mar 28, 2018, 03:10 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 mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema toiletdb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema toiletdb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `toiletdb` DEFAULT CHARACTER SET utf8 ;
18USE `toiletdb` ;
19
20-- -----------------------------------------------------
21-- Table `toiletdb`.`adminlogin`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `toiletdb`.`adminlogin` (
24 `admin_id` INT(11) NOT NULL AUTO_INCREMENT,
25 `bruger` VARCHAR(45) NOT NULL,
26 `password` VARCHAR(45) NOT NULL,
27 PRIMARY KEY (`admin_id`))
28ENGINE = InnoDB
29AUTO_INCREMENT = 4
30DEFAULT CHARACTER SET = utf8;
31
32
33-- -----------------------------------------------------
34-- Table `toiletdb`.`toiletinfo`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `toiletdb`.`toiletinfo` (
37 `catagory_id` INT(11) NOT NULL AUTO_INCREMENT,
38 `catagory` VARCHAR(45) NOT NULL,
39 PRIMARY KEY (`catagory_id`))
40ENGINE = InnoDB
41AUTO_INCREMENT = 6
42DEFAULT CHARACTER SET = utf8;
43
44
45-- -----------------------------------------------------
46-- Table `toiletdb`.`toiletdata`
47-- -----------------------------------------------------
48CREATE TABLE IF NOT EXISTS `toiletdb`.`toiletdata` (
49 `toilet_id` INT(11) NOT NULL AUTO_INCREMENT,
50 `toilet_name` VARCHAR(45) NULL DEFAULT NULL,
51 `adress` VARCHAR(45) NULL DEFAULT NULL,
52 `zipcode` VARCHAR(45) NULL DEFAULT NULL,
53 `status` VARCHAR(45) NULL DEFAULT NULL,
54 `catagory_id` INT(11) NOT NULL,
55 PRIMARY KEY (`toilet_id`),
56 INDEX `fk_Toiletdata_zipcodedb_idx` (`zipcode` ASC),
57 INDEX `fk_Toiletdata_Toiletinfo` (`catagory_id` ASC),
58 CONSTRAINT `fk_Toiletdata_Toiletinfo`
59 FOREIGN KEY (`catagory_id`)
60 REFERENCES `toiletdb`.`toiletinfo` (`catagory_id`)
61 ON DELETE NO ACTION
62 ON UPDATE NO ACTION)
63ENGINE = InnoDB
64AUTO_INCREMENT = 50
65DEFAULT CHARACTER SET = utf8;
66
67
68-- -----------------------------------------------------
69-- Table `toiletdb`.`descriptiondb`
70-- -----------------------------------------------------
71CREATE TABLE IF NOT EXISTS `toiletdb`.`descriptiondb` (
72 `toilet_id` INT(11) NOT NULL,
73 `description` VARCHAR(200) NULL DEFAULT NULL,
74 PRIMARY KEY (`toilet_id`),
75 CONSTRAINT `fk_descriptiondb_toiletdata1`
76 FOREIGN KEY (`toilet_id`)
77 REFERENCES `toiletdb`.`toiletdata` (`toilet_id`)
78 ON DELETE NO ACTION
79 ON UPDATE NO ACTION)
80ENGINE = InnoDB
81DEFAULT CHARACTER SET = utf8;
82
83
84-- -----------------------------------------------------
85-- Table `toiletdb`.`descriptionsg`
86-- -----------------------------------------------------
87CREATE TABLE IF NOT EXISTS `toiletdb`.`descriptionsg` (
88 `toilet_id` INT(11) NOT NULL,
89 `suggest_description` VARCHAR(200) NULL DEFAULT NULL,
90 INDEX `fk_descriptiondb_has_toiletdata_descriptiondb1_idx` (`toilet_id` ASC),
91 CONSTRAINT `fk_descriptiondb_has_toiletdata_descriptiondb1`
92 FOREIGN KEY (`toilet_id`)
93 REFERENCES `toiletdb`.`descriptiondb` (`toilet_id`)
94 ON DELETE NO ACTION
95 ON UPDATE NO ACTION)
96ENGINE = InnoDB
97DEFAULT CHARACTER SET = utf8;
98
99
100-- -----------------------------------------------------
101-- Table `toiletdb`.`ratingdb`
102-- -----------------------------------------------------
103CREATE TABLE IF NOT EXISTS `toiletdb`.`ratingdb` (
104 `toilet_id` INT(11) NOT NULL,
105 `rating` INT(2) NULL DEFAULT NULL,
106 `times_rated` INT(6) NULL DEFAULT NULL,
107 PRIMARY KEY (`toilet_id`),
108 CONSTRAINT `fk_ratingdb_toiletdata`
109 FOREIGN KEY (`toilet_id`)
110 REFERENCES `toiletdb`.`toiletdata` (`toilet_id`)
111 ON DELETE NO ACTION
112 ON UPDATE NO ACTION)
113ENGINE = InnoDB
114DEFAULT CHARACTER SET = utf8;
115
116
117-- -----------------------------------------------------
118-- Table `toiletdb`.`zipcodedb`
119-- -----------------------------------------------------
120CREATE TABLE IF NOT EXISTS `toiletdb`.`zipcodedb` (
121 `zipcode` VARCHAR(5) NOT NULL,
122 `cityname` VARCHAR(45) NOT NULL,
123 PRIMARY KEY (`zipcode`))
124ENGINE = InnoDB
125DEFAULT CHARACTER SET = utf8;
126
127
128-- -----------------------------------------------------
129-- Table `toiletdb`.`ratingsg`
130-- -----------------------------------------------------
131CREATE TABLE IF NOT EXISTS `toiletdb`.`ratingsg` (
132 `toilet_id` INT(11) NOT NULL,
133 `rating` INT NOT NULL,
134 INDEX `fk_ratingsg_ratingdb1_idx` (`toilet_id` ASC),
135 CONSTRAINT `fk_ratingsg_ratingdb1`
136 FOREIGN KEY (`toilet_id`)
137 REFERENCES `toiletdb`.`ratingdb` (`toilet_id`)
138 ON DELETE NO ACTION
139 ON UPDATE NO ACTION)
140ENGINE = InnoDB;
141
142
143SET SQL_MODE=@OLD_SQL_MODE;
144SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
145SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;