· 6 years ago · May 05, 2019, 05:42 PM
1USE server_db;
2
3CREATE TABLE IF NOT EXISTS `Machines` (
4 `MachineId` Integer NOT NULL AUTO_INCREMENT,
5 PRIMARY KEY (`MachineId`)
6);
7
8CREATE TABLE IF NOT EXISTS `DenominationReserve` (
9 `DenominationReserveId` Integer NOT NULL AUTO_INCREMENT,
10 `MachineId` Integer NOT NULL,
11 `Denomination` Integer,
12 `Reserve` Integer,
13 PRIMARY KEY (`DenominationReserveId`),
14 FOREIGN KEY (`MachineId`)
15 REFERENCES Machines(MachineId)
16);
17
18CREATE TABLE IF NOT EXISTS `MachinesProperties` (
19 `MachinePropertiesId` Integer NOT NULL AUTO_INCREMENT,
20 `MachineId` Integer,
21 `MilkTemperature` Double,
22 `MinMilkTemperature` Double,
23 `MaxMilkTemperature` Double,
24 `MinElectronicsTemperature` Double,
25 `MaxElectronicsTemperature` Double,
26 `MilkTemperatureHysteresis` Double,
27 `ElectronicsTemperatureHysteresis` Double,
28 `MixingTime` Time,
29 `RestTime` Time,
30 `AccelerationAlertLevel` Double,
31 `PropertiesStartTime` Datetime,
32 PRIMARY KEY (`MachinePropertiesId`),
33 FOREIGN KEY (`MachineId`)
34 REFERENCES Machines(MachineId)
35);
36
37CREATE TABLE `ArtificialMilkAmount` (
38 `ArtificialMilkAmountId` Integer NOT NULL AUTO_INCREMENT,
39 `MachineId` Integer,
40 `AmountToDecrease` Double,
41 `MinRealAmount` Double,
42 `MaxRealAmount` Double,
43 `StartTime` Time,
44 PRIMARY KEY (`ArtificialMilkAmountId`),
45 FOREIGN KEY (`MachineId`)
46 REFERENCES Machines(MachineId)
47);
48
49
50
51CREATE TABLE IF NOT EXISTS `MilkPrices` (
52 `MilkPriceId` Integer NOT NULL AUTO_INCREMENT,
53 `MachineId` Integer NOT NULL,
54 `MilkPrice` Integer,
55 `PriceStartTime` Datetime,
56 PRIMARY KEY (`MilkPriceId`),
57 FOREIGN KEY (`MachineId`)
58 REFERENCES Machines(MachineId)
59);
60
61
62
63CREATE TABLE IF NOT EXISTS `WorkHours` (
64 `WorkHoursId` Integer NOT NULL AUTO_INCREMENT,
65 `MachineId` Integer NOT NULL,
66 `Day` Integer,
67 `OpeningTime` Time,
68 `ClosingTime` Time,
69 PRIMARY KEY (`WorkHoursId`),
70 FOREIGN KEY (`MachineId`)
71 REFERENCES Machines(MachineId)
72);
73
74
75
76CREATE TABLE IF NOT EXISTS `Addresses` (
77 `AddressId` Integer NOT NULL AUTO_INCREMENT,
78 `DeviceId` Integer NOT NULL,
79 `Country` Varchar(63),
80 `City` Varchar(63),
81 `ZipCode` Mediumint,
82 `Street` Varchar(255),
83 `Latitude` Double,
84 `Longitude` Double,
85 PRIMARY KEY (`AddressId`),
86 FOREIGN KEY (`DeviceId`)
87 REFERENCES Machines(MachineId)
88);
89
90
91CREATE TABLE IF NOT EXISTS `DiscountDegrees` (
92 `DiscountDegreeId` Integer NOT NULL AUTO_INCREMENT,
93 `MachineId` Integer,
94 `StartTime` Time,
95 `MinAmount` Integer,
96 `DiscountPercentage` Integer,
97 PRIMARY KEY (`DiscountDegreeId`),
98 FOREIGN KEY (`MachineId`)
99 REFERENCES Machines(MachineId)
100);
101
102
103
104CREATE TABLE IF NOT EXISTS `Measurements` (
105 `MeasurementId` Integer NOT NULL AUTO_INCREMENT,
106 `MachineId` Integer NOT NULL,
107 `MeasurementTime` Datetime,
108 `MilkTemperature` Double,
109 `ElectronicsTemperature` Double,
110 `Humidity` Double,
111 `Acceleration` Double,
112 PRIMARY KEY (`MeasurementId`),
113 FOREIGN KEY (`MachineId`)
114 REFERENCES Machines(MachineId)
115);
116
117
118
119CREATE TABLE IF NOT EXISTS `MinDenominationAmount` (
120 `MinDenominationAmountId` Integer NOT NULL AUTO_INCREMENT,
121 `MachineId` Integer,
122 `Denomination` Integer,
123 `MinAmount` Integer,
124 PRIMARY KEY (`MinDenominationAmountId`),
125 FOREIGN KEY (`MachineId`)
126 REFERENCES Machines(MachineId)
127);
128
129
130
131CREATE TABLE IF NOT EXISTS `MilkContainers` (
132 `MilkContainerId` Integer NOT NULL AUTO_INCREMENT,
133 `MachineId` Integer,
134 `MilkReserve` Double,
135 `MinMilkAmount` Double,
136 `MaxMilkAmount` Double,
137 PRIMARY KEY (`MilkContainerId`),
138 FOREIGN KEY (`MachineId`)
139 REFERENCES Machines(MachineId)
140);
141
142
143
144CREATE TABLE IF NOT EXISTS `MilkOperations` (
145 `MilkOperationId` Integer NOT NULL AUTO_INCREMENT,
146 `MilkContainerId` Integer,
147 `SuppliedMilk` Double,
148 `RemovedMilk` Double,
149 `CurrentMilk` Double,
150 `OperationTime` Datetime,
151 PRIMARY KEY (`MilkOperationId`),
152 FOREIGN KEY (`MilkContainerId`)
153 REFERENCES MilkContainers(MilkContainerId)
154);
155
156CREATE TABLE IF NOT EXISTS `Roles` (
157 `RoleId` Integer NOT NULL AUTO_INCREMENT,
158 `RoleName` Varchar(127),
159 PRIMARY KEY (`RoleId`)
160);
161
162CREATE TABLE IF NOT EXISTS `Privileges` (
163 `PrivilegeId` Integer NOT NULL AUTO_INCREMENT,
164 `PrivilegeName` Varchar(255),
165 PRIMARY KEY (`PrivilegeId`)
166);
167
168CREATE TABLE IF NOT EXISTS `RolesPrivileges` (
169 `RoleId` Integer NOT NULL,
170 `PrivilegeId` Integer NOT NULL,
171 PRIMARY KEY (`RoleId`, `PrivilegeId`)
172);
173
174CREATE TABLE IF NOT EXISTS `Users` (
175 `UserId` Integer NOT NULL AUTO_INCREMENT,
176 `RoleId` Integer,
177 `UserName` Varchar(32),
178 `PhoneNumber` Integer,
179 `Email` Varchar(64),
180 `Password` Varchar(64),
181 PRIMARY KEY (`UserId`),
182 FOREIGN KEY (`RoleId`)
183 REFERENCES Roles(RoleId)
184);
185
186CREATE TABLE IF NOT EXISTS `LogTypes` (
187 `LogTypeId` Integer NOT NULL AUTO_INCREMENT,
188 `LogTypeName` Varchar(63),
189 PRIMARY KEY (`LogTypeId`)
190);
191
192CREATE TABLE IF NOT EXISTS `ServerLogs` (
193 `ServerLogId` Integer NOT NULL AUTO_INCREMENT,
194 `DeviceId` Integer,
195 `UserId` Integer,
196 `LogTypeId` Integer,
197 `Time` Datetime,
198 `LogContent` Text,
199 PRIMARY KEY (`ServerLogId`),
200 FOREIGN KEY (`DeviceId`)
201 REFERENCES Machines(MachineId),
202 FOREIGN KEY (`UserId`)
203 REFERENCES Users(UserId),
204 FOREIGN KEY (`LogTypeId`)
205 REFERENCES LogTypes(LogTypeId)
206);
207
208CREATE TABLE IF NOT EXISTS `Orders` (
209 `OrderId` Integer NOT NULL AUTO_INCREMENT,
210 `MachineId` Integer,
211 `UserId` Integer,
212 `ReservationTime` Datetime,
213 `MilkAmount` Integer,
214 `Pin` Integer,
215 `Price` Double,
216 `OrderStatus` Varchar(255),
217 `SubmitTime` Datetime,
218 `ReceiveTime` Datetime,
219 PRIMARY KEY (`OrderId`),
220 FOREIGN KEY (`MachineId`)
221 REFERENCES Machines(MachineId),
222 FOREIGN KEY (`UserId`)
223 REFERENCES Users(UserId)
224);
225
226
227
228CREATE TABLE IF NOT EXISTS `Subscriptions` (
229 `SubscriptionId` Integer NOT NULL AUTO_INCREMENT,
230 `MachineId` Integer NOT NULL,
231 `UserId` Integer NOT NULL,
232 `StartTime` Datetime,
233 `ExpiredTime` Datetime,
234 PRIMARY KEY (`SubscriptionId`),
235 FOREIGN KEY (`MachineId`)
236 REFERENCES Machines(MachineId),
237 FOREIGN KEY (`UserId`)
238 REFERENCES Users(UserId)
239);
240
241
242
243CREATE TABLE IF NOT EXISTS `NotificationTypes` (
244 `NotificationTypeId` Integer NOT NULL AUTO_INCREMENT,
245 `NotificationTypeName` Varchar(127),
246 PRIMARY KEY (`NotificationTypeId`)
247);
248
249d
250
251CREATE TABLE IF NOT EXISTS `SubscriptionNotification` (
252 `SubscriptionId` Integer NOT NULL,
253 `NotificationTypeId` Integer NOT NULL,
254 PRIMARY KEY (`SubscriptionId`, `NotificationTypeId`)
255);