· 6 years ago · Nov 18, 2019, 05:36 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 ESS_Trading
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema ESS_Trading
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `ESS_Trading` ;
15USE `ESS_Trading` ;
16
17-- -----------------------------------------------------
18-- Table `ESS_Trading`.`Portfolio`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `ESS_Trading`.`Portfolio` (
21 `idPortfolio` INT NOT NULL,
22 PRIMARY KEY (`idPortfolio`))
23ENGINE = InnoDB;
24
25
26-- -----------------------------------------------------
27-- Table `ESS_Trading`.`User`
28-- -----------------------------------------------------
29CREATE TABLE IF NOT EXISTS `ESS_Trading`.`User` (
30 `idUser` INT NOT NULL,
31 `Username` VARCHAR(45) NOT NULL,
32 `Email` VARCHAR(255) NOT NULL,
33 `Password` VARCHAR(255) NOT NULL,
34 `isAdmin` INT NOT NULL,
35 `Credit` DOUBLE NOT NULL,
36 `Portfolio_idPortfolio` INT NOT NULL,
37 PRIMARY KEY (`idUser`, `Portfolio_idPortfolio`),
38 INDEX `fk_User_Portfolio1_idx` (`Portfolio_idPortfolio` ASC) VISIBLE,
39 CONSTRAINT `fk_User_Portfolio1`
40 FOREIGN KEY (`Portfolio_idPortfolio`)
41 REFERENCES `ESS_Trading`.`Portfolio` (`idPortfolio`)
42 ON DELETE NO ACTION
43 ON UPDATE NO ACTION)
44ENGINE = InnoDB;
45
46
47-- -----------------------------------------------------
48-- Table `ESS_Trading`.`Asset`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `ESS_Trading`.`Asset` (
51 `idAsset` INT NOT NULL,
52 `Company` VARCHAR(45) NOT NULL,
53 `Type` ENUM('coin', 'stock', 'commodity') NOT NULL,
54 `Value` DOUBLE NOT NULL,
55 PRIMARY KEY (`idAsset`))
56ENGINE = InnoDB;
57
58
59-- -----------------------------------------------------
60-- Table `ESS_Trading`.`CFD`
61-- -----------------------------------------------------
62CREATE TABLE IF NOT EXISTS `ESS_Trading`.`CFD` (
63 `idCFD` INT NOT NULL,
64 `AquisitionPrice` DOUBLE NOT NULL,
65 `Position` ENUM('short', 'long') NOT NULL,
66 `TakeProfit` DOUBLE NULL,
67 `StopLoss` DOUBLE NULL,
68 `Date` VARCHAR(45) NOT NULL,
69 `Quantity` DOUBLE NOT NULL,
70 `Portfolio_idPortfolio` INT NOT NULL,
71 `Asset_idAsset` INT NOT NULL,
72 PRIMARY KEY (`idCFD`, `Portfolio_idPortfolio`, `Asset_idAsset`),
73 INDEX `fk_CFD_Portfolio1_idx` (`Portfolio_idPortfolio` ASC) VISIBLE,
74 INDEX `fk_CFD_Asset1_idx` (`Asset_idAsset` ASC) VISIBLE,
75 CONSTRAINT `fk_CFD_Portfolio1`
76 FOREIGN KEY (`Portfolio_idPortfolio`)
77 REFERENCES `ESS_Trading`.`Portfolio` (`idPortfolio`)
78 ON DELETE NO ACTION
79 ON UPDATE NO ACTION,
80 CONSTRAINT `fk_CFD_Asset1`
81 FOREIGN KEY (`Asset_idAsset`)
82 REFERENCES `ESS_Trading`.`Asset` (`idAsset`)
83 ON DELETE NO ACTION
84 ON UPDATE NO ACTION)
85ENGINE = InnoDB;
86
87
88-- -----------------------------------------------------
89-- Table `ESS_Trading`.`Bug`
90-- -----------------------------------------------------
91CREATE TABLE IF NOT EXISTS `ESS_Trading`.`Bug` (
92 `idBug` INT NOT NULL,
93 `Error` VARCHAR(255) NOT NULL,
94 `Date` VARCHAR(45) NOT NULL,
95 `User_idUser` INT NOT NULL,
96 PRIMARY KEY (`idBug`, `User_idUser`),
97 INDEX `fk_Bug_User1_idx` (`User_idUser` ASC) VISIBLE,
98 CONSTRAINT `fk_Bug_User1`
99 FOREIGN KEY (`User_idUser`)
100 REFERENCES `ESS_Trading`.`User` (`idUser`)
101 ON DELETE NO ACTION
102 ON UPDATE NO ACTION)
103ENGINE = InnoDB;
104
105
106-- -----------------------------------------------------
107-- Table `ESS_Trading`.`Portfolio_has_Asset`
108-- -----------------------------------------------------
109CREATE TABLE IF NOT EXISTS `ESS_Trading`.`Portfolio_has_Asset` (
110 `Portfolio_idPortfolio` INT NOT NULL,
111 `Asset_idAsset` INT NOT NULL,
112 PRIMARY KEY (`Portfolio_idPortfolio`, `Asset_idAsset`),
113 INDEX `fk_Portfolio_has_Asset_Asset1_idx` (`Asset_idAsset` ASC) VISIBLE,
114 INDEX `fk_Portfolio_has_Asset_Portfolio1_idx` (`Portfolio_idPortfolio` ASC) VISIBLE,
115 CONSTRAINT `fk_Portfolio_has_Asset_Portfolio1`
116 FOREIGN KEY (`Portfolio_idPortfolio`)
117 REFERENCES `ESS_Trading`.`Portfolio` (`idPortfolio`)
118 ON DELETE NO ACTION
119 ON UPDATE NO ACTION,
120 CONSTRAINT `fk_Portfolio_has_Asset_Asset1`
121 FOREIGN KEY (`Asset_idAsset`)
122 REFERENCES `ESS_Trading`.`Asset` (`idAsset`)
123 ON DELETE NO ACTION
124 ON UPDATE NO ACTION)
125ENGINE = InnoDB;
126
127
128SET SQL_MODE=@OLD_SQL_MODE;
129SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
130SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;