L3J8414A

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`.`toiletinfo`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `toiletdb`.`toiletinfo` (
24  `catagory_id` INT(11) NOT NULL AUTO_INCREMENT,
25  `catagory` VARCHAR(45) NOT NULL,
26  PRIMARY KEY (`catagory_id`))
27ENGINE = InnoDB
28AUTO_INCREMENT = 5
29DEFAULT CHARACTER SET = utf8;
30
31
32-- -----------------------------------------------------
33-- Table `toiletdb`.`zipcodedb`
34-- -----------------------------------------------------
35CREATE TABLE IF NOT EXISTS `toiletdb`.`zipcodedb` (
36  `zipcode` VARCHAR(5) NOT NULL,
37  `cityname` VARCHAR(45) NOT NULL,
38  PRIMARY KEY (`zipcode`))
39ENGINE = InnoDB
40DEFAULT CHARACTER SET = utf8;
41
42
43-- -----------------------------------------------------
44-- Table `toiletdb`.`toiletdata`
45-- -----------------------------------------------------
46CREATE TABLE IF NOT EXISTS `toiletdb`.`toiletdata` (
47  `toilet_id` INT(11) NOT NULL AUTO_INCREMENT,
48  `toilet_name` VARCHAR(45) NULL DEFAULT NULL,
49  `adress` VARCHAR(45) NULL DEFAULT NULL,
50  `zipcode` VARCHAR(5) NULL,
51  `status` VARCHAR(45) NULL DEFAULT NULL,
52  `catagory_id` INT(11) NOT NULL,
53  PRIMARY KEY (`toilet_id`),
54  INDEX `fk_Toiletdata_Toiletinfo` (`catagory_id` ASC),
55  INDEX `fk_toiletdata_zipcodedb1_idx` (`zipcode` ASC),
56  CONSTRAINT `fk_Toiletdata_Toiletinfo`
57    FOREIGN KEY (`catagory_id`)
58    REFERENCES `toiletdb`.`toiletinfo` (`catagory_id`)
59    ON DELETE NO ACTION
60    ON UPDATE NO ACTION,
61  CONSTRAINT `fk_toiletdata_zipcodedb1`
62    FOREIGN KEY (`zipcode`)
63    REFERENCES `toiletdb`.`zipcodedb` (`zipcode`)
64    ON DELETE NO ACTION
65    ON UPDATE NO ACTION)
66ENGINE = InnoDB
67AUTO_INCREMENT = 50
68DEFAULT CHARACTER SET = utf8;
69
70
71-- -----------------------------------------------------
72-- Table `toiletdb`.`descriptiondb`
73-- -----------------------------------------------------
74CREATE TABLE IF NOT EXISTS `toiletdb`.`descriptiondb` (
75  `toilet_id` INT(11) NOT NULL,
76  `description` VARCHAR(200) NULL DEFAULT NULL,
77  `suggest_description` VARCHAR(200) NULL DEFAULT NULL,
78  PRIMARY KEY (`toilet_id`),
79  CONSTRAINT `fk_descriptiondb_toiletdata1`
80    FOREIGN KEY (`toilet_id`)
81    REFERENCES `toiletdb`.`toiletdata` (`toilet_id`)
82    ON DELETE NO ACTION
83    ON UPDATE NO ACTION)
84ENGINE = InnoDB
85DEFAULT CHARACTER SET = utf8;
86
87
88-- -----------------------------------------------------
89-- Table `toiletdb`.`ratingdb`
90-- -----------------------------------------------------
91CREATE TABLE IF NOT EXISTS `toiletdb`.`ratingdb` (
92  `toilet_id` INT(11) NOT NULL,
93  `rating` INT(2) NULL DEFAULT NULL,
94  `sum_ratings` INT(6) NULL DEFAULT NULL,
95  `times_rated` INT(6) NULL DEFAULT NULL,
96  PRIMARY KEY (`toilet_id`),
97  CONSTRAINT `fk_ratingdb_toiletdata`
98    FOREIGN KEY (`toilet_id`)
99    REFERENCES `toiletdb`.`toiletdata` (`toilet_id`)
100    ON DELETE NO ACTION
101    ON UPDATE NO ACTION)
102ENGINE = InnoDB
103DEFAULT CHARACTER SET = utf8;
104
105
106SET SQL_MODE=@OLD_SQL_MODE;
107SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
108SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;