· 4 years ago · Mar 12, 2021, 12:12 PM
1-- MySQL Script generated by MySQL Workbench
2-- Fri Mar 12 13:04:54 2021
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 stocks
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `stocks` ;
14
15-- -----------------------------------------------------
16-- Schema stocks
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `stocks` DEFAULT CHARACTER SET utf8 ;
19USE `stocks` ;
20
21-- -----------------------------------------------------
22-- Table `stocks`.`Stock`
23-- -----------------------------------------------------
24CREATE TABLE IF NOT EXISTS `stocks`.`Stock` (
25 `StockID` INT NOT NULL,
26 `StockName` VARCHAR(45) NOT NULL,
27 `StockTIcker` VARCHAR(45) NOT NULL,
28 PRIMARY KEY (`StockID`))
29ENGINE = InnoDB;
30
31
32-- -----------------------------------------------------
33-- Table `stocks`.`Value`
34-- -----------------------------------------------------
35CREATE TABLE IF NOT EXISTS `stocks`.`Value` (
36 `StockID` INT NOT NULL,
37 `ValueDate` DATETIME NOT NULL,
38 `PriceInCents` INT NOT NULL,
39 PRIMARY KEY (`StockID`, `ValueDate`),
40 CONSTRAINT `ValueStockID`
41 FOREIGN KEY (`StockID`)
42 REFERENCES `stocks`.`Stock` (`StockID`)
43 ON DELETE NO ACTION
44 ON UPDATE NO ACTION)
45ENGINE = InnoDB;
46
47
48-- -----------------------------------------------------
49-- Table `stocks`.`Client`
50-- -----------------------------------------------------
51CREATE TABLE IF NOT EXISTS `stocks`.`Client` (
52 `ClientID` INT NOT NULL,
53 `ClientFullName` VARCHAR(45) NOT NULL,
54 PRIMARY KEY (`ClientID`))
55ENGINE = InnoDB;
56
57
58-- -----------------------------------------------------
59-- Table `stocks`.`Portfolio`
60-- -----------------------------------------------------
61CREATE TABLE IF NOT EXISTS `stocks`.`Portfolio` (
62 `PortofolioID` INT NOT NULL,
63 `ClientID` INT NOT NULL,
64 PRIMARY KEY (`PortofolioID`),
65 INDEX `PortfolioClientID_idx` (`ClientID` ASC) VISIBLE,
66 CONSTRAINT `PortfolioClientID`
67 FOREIGN KEY (`ClientID`)
68 REFERENCES `stocks`.`Client` (`ClientID`)
69 ON DELETE NO ACTION
70 ON UPDATE NO ACTION)
71ENGINE = InnoDB;
72
73
74-- -----------------------------------------------------
75-- Table `stocks`.`PortfolioStock`
76-- -----------------------------------------------------
77CREATE TABLE IF NOT EXISTS `stocks`.`PortfolioStock` (
78 `PortfolioID` INT NOT NULL,
79 `StockID` INT NOT NULL,
80 `TotalAmount` VARCHAR(45) NOT NULL,
81 PRIMARY KEY (`PortfolioID`, `StockID`),
82 INDEX `PSStockID_idx` (`StockID` ASC) VISIBLE,
83 CONSTRAINT `PSPortfolioID`
84 FOREIGN KEY (`PortfolioID`)
85 REFERENCES `stocks`.`Portfolio` (`PortofolioID`)
86 ON DELETE NO ACTION
87 ON UPDATE NO ACTION,
88 CONSTRAINT `PSStockID`
89 FOREIGN KEY (`StockID`)
90 REFERENCES `stocks`.`Stock` (`StockID`)
91 ON DELETE NO ACTION
92 ON UPDATE NO ACTION)
93ENGINE = InnoDB;
94
95
96-- -----------------------------------------------------
97-- Table `stocks`.`Transaction`
98-- -----------------------------------------------------
99CREATE TABLE IF NOT EXISTS `stocks`.`Transaction` (
100 `TransactionID` INT NOT NULL,
101 `PortfolioID` INT NOT NULL,
102 `StockID` INT NOT NULL,
103 `Date` DATETIME NOT NULL,
104 `StockAmount` INT NOT NULL,
105 PRIMARY KEY (`TransactionID`),
106 INDEX `TransactionPortfolioID_idx` (`PortfolioID` ASC) VISIBLE,
107 INDEX `TransactionStockId_idx` (`StockID` ASC) VISIBLE,
108 INDEX `TransactionDate_idx` (`Date` ASC) VISIBLE,
109 CONSTRAINT `TransactionPortfolioID`
110 FOREIGN KEY (`PortfolioID`)
111 REFERENCES `stocks`.`Portfolio` (`PortofolioID`)
112 ON DELETE NO ACTION
113 ON UPDATE NO ACTION,
114 CONSTRAINT `TransactionStockId`
115 FOREIGN KEY (`StockID`)
116 REFERENCES `stocks`.`Stock` (`StockID`)
117 ON DELETE NO ACTION
118 ON UPDATE NO ACTION,
119 CONSTRAINT `TransactionDate`
120 FOREIGN KEY (`Date`)
121 REFERENCES `stocks`.`Value` (`ValueDate`)
122 ON DELETE NO ACTION
123 ON UPDATE NO ACTION)
124ENGINE = InnoDB;
125
126
127-- -----------------------------------------------------
128-- Table `stocks`.`Country`
129-- -----------------------------------------------------
130CREATE TABLE IF NOT EXISTS `stocks`.`Country` (
131 `CountryID` INT NOT NULL,
132 `CountryName` VARCHAR(45) NOT NULL,
133 PRIMARY KEY (`CountryID`))
134ENGINE = InnoDB;
135
136
137-- -----------------------------------------------------
138-- Table `stocks`.`Postalcode`
139-- -----------------------------------------------------
140CREATE TABLE IF NOT EXISTS `stocks`.`Postalcode` (
141 `PostalcodeID` INT NOT NULL,
142 `PostalCode` VARCHAR(45) NOT NULL,
143 `CountryID` INT NOT NULL,
144 PRIMARY KEY (`PostalcodeID`),
145 INDEX `PostalCodeCountry_idx` (`CountryID` ASC) VISIBLE,
146 CONSTRAINT `PostalCodeCountry`
147 FOREIGN KEY (`CountryID`)
148 REFERENCES `stocks`.`Country` (`CountryID`)
149 ON DELETE NO ACTION
150 ON UPDATE NO ACTION)
151ENGINE = InnoDB;
152
153
154-- -----------------------------------------------------
155-- Table `stocks`.`Address`
156-- -----------------------------------------------------
157CREATE TABLE IF NOT EXISTS `stocks`.`Address` (
158 `AddressID` INT NOT NULL,
159 `CountryID` INT NOT NULL,
160 `PostalcodeID` INT NOT NULL,
161 `Street Number` VARCHAR(45) NOT NULL,
162 PRIMARY KEY (`AddressID`),
163 INDEX `AddressCountry_idx` (`CountryID` ASC) VISIBLE,
164 INDEX `AddressPostalCode_idx` (`PostalcodeID` ASC) VISIBLE,
165 CONSTRAINT `AddressCountry`
166 FOREIGN KEY (`CountryID`)
167 REFERENCES `stocks`.`Country` (`CountryID`)
168 ON DELETE NO ACTION
169 ON UPDATE NO ACTION,
170 CONSTRAINT `AddressPostalCode`
171 FOREIGN KEY (`PostalcodeID`)
172 REFERENCES `stocks`.`Postalcode` (`PostalcodeID`)
173 ON DELETE NO ACTION
174 ON UPDATE NO ACTION)
175ENGINE = InnoDB;
176
177
178-- -----------------------------------------------------
179-- Table `stocks`.`ClientAddress`
180-- -----------------------------------------------------
181CREATE TABLE IF NOT EXISTS `stocks`.`ClientAddress` (
182 `ClientID` INT NOT NULL,
183 `AddressID` INT NOT NULL,
184 PRIMARY KEY (`ClientID`, `AddressID`),
185 INDEX `ClientAddressAddress_idx` (`AddressID` ASC) VISIBLE,
186 CONSTRAINT `ClientAddressesClient`
187 FOREIGN KEY (`ClientID`)
188 REFERENCES `stocks`.`Client` (`ClientID`)
189 ON DELETE NO ACTION
190 ON UPDATE NO ACTION,
191 CONSTRAINT `ClientAddressAddress`
192 FOREIGN KEY (`AddressID`)
193 REFERENCES `stocks`.`Address` (`AddressID`)
194 ON DELETE NO ACTION
195 ON UPDATE NO ACTION)
196ENGINE = InnoDB;
197
198
199-- -----------------------------------------------------
200-- Table `stocks`.`PhoneNumber`
201-- -----------------------------------------------------
202CREATE TABLE IF NOT EXISTS `stocks`.`PhoneNumber` (
203 `PhoneNumberID` INT NOT NULL,
204 `PhoneNumber` VARCHAR(45) NOT NULL,
205 PRIMARY KEY (`PhoneNumberID`))
206ENGINE = InnoDB;
207
208
209-- -----------------------------------------------------
210-- Table `stocks`.`ClientPhoneNumber`
211-- -----------------------------------------------------
212CREATE TABLE IF NOT EXISTS `stocks`.`ClientPhoneNumber` (
213 `ClientID` INT NOT NULL,
214 `PhoneNumberID` INT NOT NULL,
215 PRIMARY KEY (`ClientID`, `PhoneNumberID`),
216 INDEX `ClientPhonennumberPhoneNumberID_idx` (`PhoneNumberID` ASC) VISIBLE,
217 CONSTRAINT `ClientPhonenumberClientID`
218 FOREIGN KEY (`ClientID`)
219 REFERENCES `stocks`.`Client` (`ClientID`)
220 ON DELETE NO ACTION
221 ON UPDATE NO ACTION,
222 CONSTRAINT `ClientPhonennumberPhoneNumberID`
223 FOREIGN KEY (`PhoneNumberID`)
224 REFERENCES `stocks`.`PhoneNumber` (`PhoneNumberID`)
225 ON DELETE NO ACTION
226 ON UPDATE NO ACTION)
227ENGINE = InnoDB;
228
229
230SET SQL_MODE=@OLD_SQL_MODE;
231SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
232SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
233