· 4 years ago · Apr 22, 2021, 08:48 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='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 mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
15USE `mydb` ;
16
17-- -----------------------------------------------------
18-- Table `mydb`.`Role`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`Role` (
21 `id` INT NOT NULL,
22 `role` VARCHAR(45) NOT NULL,
23 PRIMARY KEY (`id`))
24ENGINE = InnoDB;
25
26
27-- -----------------------------------------------------
28-- Table `mydb`.`User`
29-- -----------------------------------------------------
30CREATE TABLE IF NOT EXISTS `mydb`.`User` (
31 `id` INT NOT NULL,
32 `email` VARCHAR(45) NOT NULL,
33 `password` VARCHAR(45) NOT NULL,
34 `fname` VARCHAR(45) NOT NULL,
35 `lname` VARCHAR(45) NOT NULL,
36 `phoneNumber` VARCHAR(45) NOT NULL,
37 `Role_id` INT NOT NULL,
38 PRIMARY KEY (`id`),
39 INDEX `fk_User_Role1_idx` (`Role_id` ASC) VISIBLE,
40 CONSTRAINT `fk_User_Role1`
41 FOREIGN KEY (`Role_id`)
42 REFERENCES `mydb`.`Role` (`id`)
43 ON DELETE NO ACTION
44 ON UPDATE NO ACTION)
45ENGINE = InnoDB;
46
47
48-- -----------------------------------------------------
49-- Table `mydb`.`Coutries`
50-- -----------------------------------------------------
51CREATE TABLE IF NOT EXISTS `mydb`.`Coutries` (
52 `idCoutries` INT NOT NULL,
53 `countryCode` VARCHAR(45) NOT NULL,
54 `name` VARCHAR(45) NOT NULL,
55 PRIMARY KEY (`idCoutries`))
56ENGINE = InnoDB;
57
58
59-- -----------------------------------------------------
60-- Table `mydb`.`Room`
61-- -----------------------------------------------------
62CREATE TABLE IF NOT EXISTS `mydb`.`Room` (
63 `idRoom` INT NOT NULL,
64 `numberOfRoom` VARCHAR(45) NOT NULL,
65 `availability` VARCHAR(45) NOT NULL,
66 PRIMARY KEY (`idRoom`))
67ENGINE = InnoDB;
68
69
70-- -----------------------------------------------------
71-- Table `mydb`.`Hotel`
72-- -----------------------------------------------------
73CREATE TABLE IF NOT EXISTS `mydb`.`Hotel` (
74 `idHotel` INT NOT NULL,
75 `hotelName` VARCHAR(45) NOT NULL,
76 `address` VARCHAR(45) NOT NULL,
77 `phoneNumber` VARCHAR(45) NOT NULL,
78 `Coutries_idCoutries` INT NOT NULL,
79 `Room_idRoom` INT NOT NULL,
80 PRIMARY KEY (`idHotel`),
81 INDEX `fk_Hotel_Coutries1_idx` (`Coutries_idCoutries` ASC) VISIBLE,
82 INDEX `fk_Hotel_Room1_idx` (`Room_idRoom` ASC) VISIBLE,
83 CONSTRAINT `fk_Hotel_Coutries1`
84 FOREIGN KEY (`Coutries_idCoutries`)
85 REFERENCES `mydb`.`Coutries` (`idCoutries`)
86 ON DELETE NO ACTION
87 ON UPDATE NO ACTION,
88 CONSTRAINT `fk_Hotel_Room1`
89 FOREIGN KEY (`Room_idRoom`)
90 REFERENCES `mydb`.`Room` (`idRoom`)
91 ON DELETE NO ACTION
92 ON UPDATE NO ACTION)
93ENGINE = InnoDB;
94
95
96-- -----------------------------------------------------
97-- Table `mydb`.`Order`
98-- -----------------------------------------------------
99CREATE TABLE IF NOT EXISTS `mydb`.`Order` (
100 `idOrder` INT NOT NULL,
101 `dateOfPlaceOrder` DATE NOT NULL,
102 `checkIn` DATE NOT NULL,
103 `checkOut` DATE NOT NULL,
104 `price` DECIMAL NOT NULL,
105 `User_id` INT NOT NULL,
106 `Hotel_idHotel` INT NOT NULL,
107 PRIMARY KEY (`idOrder`),
108 INDEX `fk_Order_User1_idx` (`User_id` ASC) VISIBLE,
109 INDEX `fk_Order_Hotel1_idx` (`Hotel_idHotel` ASC) VISIBLE,
110 CONSTRAINT `fk_Order_User1`
111 FOREIGN KEY (`User_id`)
112 REFERENCES `mydb`.`User` (`id`)
113 ON DELETE NO ACTION
114 ON UPDATE NO ACTION,
115 CONSTRAINT `fk_Order_Hotel1`
116 FOREIGN KEY (`Hotel_idHotel`)
117 REFERENCES `mydb`.`Hotel` (`idHotel`)
118 ON DELETE NO ACTION
119 ON UPDATE NO ACTION)
120ENGINE = InnoDB;
121
122
123SET SQL_MODE=@OLD_SQL_MODE;
124SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
125SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
126