· 8 years ago · Jan 14, 2018, 09:06 AM
1DROP DATABASE IF EXISTS MokeBike;
2GO
3
4create DATABASE MokeBike;
5GO
6USE MokeBike;
7GO
8
9
10create table FaultType (
11 FaultCode INT,
12 FaultDesc VARCHAR(100) NULL,
13 CONSTRAINT PK_FAULTTYPE PRIMARY KEY(FaultCode)
14);
15GO
16
17INSERT INTO FaultType VALUES(1001,'Low Tyre Pressure');
18INSERT INTO FaultType VALUES(1002,'Broken Pedal');
19INSERT INTO FaultType VALUES(1003,'Broken Handle');
20INSERT INTO FaultType VALUES(1004,'Faulty Headlight');
21INSERT INTO FaultType VALUES(1005,'Faulty Brake');
22INSERT INTO FaultType VALUES(1006,'Broken Bike Chain');
23INSERT INTO FaultType VALUES(1007,'Dislodged Chain');
24INSERT INTO FaultType VALUES(1008,'Missing/Damaged Seat');
25INSERT INTO FaultType VALUES(1009,'Unreadable QR Code');
26INSERT INTO FaultType VALUES(1010,'Faulty Bell');
27INSERT INTO FaultType VALUES(1011,'Faulty Backlight');
28INSERT INTO FaultType VALUES(1012,'Faulty Bikestand');
29INSERT INTO FaultType VALUES(1013,'Burst Tyre');
30GO
31
32create table Member (
33 MemberID BIGINT,
34 Name VARCHAR(50) NOT NULL,
35 Email VARCHAR(50) NOT NULL,
36 Phone CHAR(8) NOT NULL,
37 RewardPts INT NOT NULL,
38 CreditBalance SMALLMONEY NOT NULL,
39 Deposit INT NOT NULL DEFAULT 50,
40 CONSTRAINT PK_MEMBER PRIMARY KEY (MemberID)
41);
42GO
43
44INSERT INTO Member VALUES(1,'Johnny' ,'johnny@gmail.com', '88888888', 2, 10,50);
45INSERT INTO Member VALUES(2,'Jack' ,'jack@gmail.com', '87777777', 0, 2.5, 50);
46INSERT INTO Member VALUES(3,'Ashok' ,'ashok@gmail.com', '86666666', 0, 3, 50);
47INSERT INTO Member VALUES(4,'Bala' ,'bala@gmail.com', '85555555', 0, 6, 50);
48INSERT INTO Member VALUES(5,'Jibala','jilaba@gmail.com', '84444444', 0, 4, 50);
49INSERT INTO Member VALUES(6,'Marcus' ,'marcus@gmail.com', '84444444', 9, 1.2, 50);
50INSERT INTO Member VALUES(7,'Ethan' ,'ethan@gmail.com', '83333333', 0, 7, 50);
51INSERT INTO Member VALUES(8,'Dominic' ,'dom@gmail.com', '82222222', 0, 9.5, 50);
52INSERT INTO Member VALUES(9,'Alloysius' ,'alloy@gmail.com', '81111111', 7, 3.6, 50);
53INSERT INTO Member VALUES(10,'Jason' ,'jason@gmail.com', '91716258', 0, 0, 50);
54INSERT INTO Member VALUES(11,'Tan Hong Geng' ,'thg@gmail.com', '94818525', 0, 0, 50);
55INSERT INTO Member VALUES(12,'Khoo Chan Ang' ,'kca@gmail.com', '91761833', 0, 12, 50);
56INSERT INTO Member VALUES(13,'Chan Chee How' ,'cch@gmail.com', '93175624', 15, 0, 50);
57INSERT INTO Member VALUES(14,'Sim Qui Ling' ,'sql@gmail.com', '97864412', 10, 34.5, 50);
58INSERT INTO Member VALUES(15,'Pearlynn' ,'pear@gmail.com', '96325874', 0, 0, 50);
59GO
60
61
62
63create table BikeStop (
64 BikeStopID BIGINT,
65 StopLat FLOAT NOT NULL,
66 StopLong FLOAT NOT NULL,
67 StopRadius FLOAT NOT NULL,
68 CONSTRAINT PK_BIKESTOP PRIMARY KEY(BikeStopID)
69);
70GO
71
72/*
73INSERT INTO BikeStop VALUES(1,43.5042,101.7224,5);
74INSERT INTO BikeStop VALUES(2,6.8984,-22.7858,11);
75INSERT INTO BikeStop VALUES(3,-54.1907,-22.6828,6);
76INSERT INTO BikeStop VALUES(4,-2.9456,-80.8041,15);
77INSERT INTO BikeStop VALUES(5,-84.9534,-177.2016,19);
78INSERT INTO BikeStop VALUES(6,5.3499,179.1429,19);
79INSERT INTO BikeStop VALUES(7,-68.964,-47.9981,13);
80INSERT INTO BikeStop VALUES(8,4.3991,-11.72,5);
81INSERT INTO BikeStop VALUES(9,50.1012,49.7975,17);
82INSERT INTO BikeStop VALUES(10,8.7519,76.5216,13);
83INSERT INTO BikeStop VALUES(11,10.633,100.7970,6);
84INSERT INTO BikeStop VALUES(12,51.2142,-34.4475,13);
85INSERT INTO BikeStop VALUES(13,-65.2268,-113.7503,19);
86INSERT INTO BikeStop VALUES(14,86.8529,-27.52,11);
87INSERT INTO BikeStop VALUES(15,-23.9473,179.5459,17);
88
89
90INSERT INTO BikeStop VALUES(1,-43.2507,108.8365,17);
91INSERT INTO BikeStop VALUES(2,76.3089,77.4343,16);
92INSERT INTO BikeStop VALUES(3,-30.8631,-164.2771,12);
93INSERT INTO BikeStop VALUES(4,-64.2218,62.5458,13);
94INSERT INTO BikeStop VALUES(5,72.907,-114.7961,20);
95INSERT INTO BikeStop VALUES(6,-32.5963,-96.6938,15);
96INSERT INTO BikeStop VALUES(7,-63.5707,74.5102,13);
97INSERT INTO BikeStop VALUES(8,18.2401,108.2409,10);
98INSERT INTO BikeStop VALUES(9,-50.7613,-123.2256,17);
99INSERT INTO BikeStop VALUES(10,-63.7323,-24.6738,18);
100INSERT INTO BikeStop VALUES(11,7.6349,-88.2881,25);
101INSERT INTO BikeStop VALUES(12,78.7137,94.238,24);
102INSERT INTO BikeStop VALUES(13,-19.5523,78.9278,24);
103INSERT INTO BikeStop VALUES(14,-24.347,176.2456,23);
104INSERT INTO BikeStop VALUES(15,-50.8928,-106.1940,12);
105*/
106INSERT INTO BikeStop VALUES(1,44.5686,-32.2106,24);
107INSERT INTO BikeStop VALUES(2,11.1960,20.5757,24);
108INSERT INTO BikeStop VALUES(3,-25.8368,-97.9830,21);
109INSERT INTO BikeStop VALUES(4,49.1972,73.3687,22);
110INSERT INTO BikeStop VALUES(5,-39.6337,-167.7832,14);
111INSERT INTO BikeStop VALUES(6,57.2666,-84.3147,8);
112INSERT INTO BikeStop VALUES(7,-41.1852,20.9549,17);
113INSERT INTO BikeStop VALUES(8,17.6020,166.4798,25);
114INSERT INTO BikeStop VALUES(9,75.8180,157.574,5);
115INSERT INTO BikeStop VALUES(10,-79.3275,-57.9349,6);
116INSERT INTO BikeStop VALUES(11,-45.2015,46.6072,20);
117INSERT INTO BikeStop VALUES(12,-24.7553,-26.5969,21);
118INSERT INTO BikeStop VALUES(13,7.1617,52.4206,15);
119INSERT INTO BikeStop VALUES(14,-76.3142,-123.5048,15);
120INSERT INTO BikeStop VALUES(15,-84.1758,-14.8369,12);
121GO
122
123
124create table Bike (
125 BikeID BIGINT,
126 Rate SMALLMONEY NOT NULL,
127 Status VARCHAR(20) NOT NULL CHECK (Status IN ('Available', 'Damaged','Under Repair')),
128 BikeStopID BIGINT NOT NULL,
129 CONSTRAINT PK_BIKE PRIMARY KEY(BikeID),
130 CONSTRAINT FK_BIKE_BIKESTOP FOREIGN KEY(BikeStopID) REFERENCES BikeStop(BikeStopID)
131)
132GO
133
134INSERT INTO Bike VALUES(1,0.90,'Available',1);
135INSERT INTO Bike VALUES(2,0.90,'Under Repair',3);
136INSERT INTO Bike VALUES(3,0.90,'Damaged',6);
137INSERT INTO Bike VALUES(4,0.90,'Under Repair',8);
138INSERT INTO Bike VALUES(5,0.90,'Damaged',5);
139INSERT INTO Bike VALUES(6,0.90,'Available',4);
140INSERT INTO Bike VALUES(7,0.90,'Under Repair',12);
141INSERT INTO Bike VALUES(8,0.90,'Damaged',15);
142INSERT INTO Bike VALUES(9,0.90,'Available',2);
143INSERT INTO Bike VALUES(10,0.90,'Under Repair',3);
144INSERT INTO Bike VALUES(11,0.90,'Damaged',7);
145INSERT INTO Bike VALUES(12,0.90,'Available',9);
146INSERT INTO Bike VALUES(13,0.90,'Damaged',1);
147INSERT INTO Bike VALUES(14,0.90,'Available',10);
148INSERT INTO Bike VALUES(15,0.90,'Under Repair',14);
149GO
150
151
152create table Promotion (
153 PromoCode CHAR(6),
154 PromoRate SMALLMONEY NOT NULL,
155 StartDate DATE NOT NULL,
156 EndDate DATE NOT NULL,
157 PromoDescription VARCHAR(70) NULL,
158 CONSTRAINT PK_PROMOTION PRIMARY KEY (Promocode)
159);
160GO
161
162INSERT INTO Promotion VALUES('200001',0.8,'2018-01-01','2018-12-31','Opening Discount');
163INSERT INTO Promotion VALUES('200002',0.7,'2018-02-16','2018-03-05','Chinese New Year');
164INSERT INTO Promotion VALUES('200003',0.85,'2018-10-31','2018-10-31','Halloween');
165INSERT INTO Promotion VALUES('200004',0.74,'2018-08-09','2018-08-09','National Day');
166INSERT INTO Promotion VALUES('200005',0.66,'2018-06-01','2018-07-01','Student June Holiday');
167INSERT INTO Promotion VALUES('200006',0.66,'2018-11-21','2018-12-31','Student December Holiday');
168INSERT INTO Promotion VALUES('200007',0.7,'2018-11-06','2018-11-06','Diwali');
169INSERT INTO Promotion VALUES('200008',0.7,'2018-08-22','2018-08-22','Hari Raya Haji');
170INSERT INTO Promotion VALUES('200009',0.0,'2018-07-07','2018-07-07','MokeBike Founder Birthday');
171INSERT INTO Promotion VALUES('200010',0.7,'2018-05-29','2018-05-29','Vesak Day');
172INSERT INTO Promotion VALUES('200011',0.7,'2018-03-30','2018-03-30','Good Friday');
173INSERT INTO Promotion VALUES('200012',0.2,'2018-01-05','2018-01-05','MokeBike CEO Birthday');
174INSERT INTO Promotion VALUES('200013',0.1,'2018-12-24','2019-01-07','Christmas');
175INSERT INTO Promotion VALUES('200014',0.0,'2018-11-22','2018-11-22','Thanksgiving');
176INSERT INTO Promotion VALUES('200015',0.0,'2018-03-11','2018-03-18','Free Week');
177GO
178
179
180
181create table CreditCard (
182 CardNo VARCHAR(16),
183 CVV CHAR(4) NOT NULL,
184 CardType VARCHAR(20) NOT NULL,
185 ExpiryDate DATE NOT NULL,
186 MemberID BIGINT NOT NULL,
187 CONSTRAINT PK_CREDITCARD PRIMARY KEY(CardNo),
188 CONSTRAINT FK_CREDITCARD_MEMBER FOREIGN KEY(MemberID) REFERENCES Member(MemberID)
189);
190GO
191/*
192INSERT INTO CreditCard VALUES('8880608937225050','852','Visa','2023-12-31',1);
193INSERT INTO CreditCard VALUES('6218746476325216','3448','American Express','2019-07-31',2);
194INSERT INTO CreditCard VALUES('2899456568720215','8534','American Express','2023-08-31',3);
195INSERT INTO CreditCard VALUES('4431316272631179','763','Diners','2021-11-30',4);
196INSERT INTO CreditCard VALUES('5337633403197111','2249','Discover','2023-10-31',5);
197INSERT INTO CreditCard VALUES('6044876956822460','5002','Citibank','2021-04-30',6);
198INSERT INTO CreditCard VALUES('5111290774194846','3478','American Express','2022-08-31',7);
199INSERT INTO CreditCard VALUES('7794998005757402','4444','Discover','2023-08-31',8);
200INSERT INTO CreditCard VALUES('3572759370155954','1196','American Express','2019-02-28',9);
201INSERT INTO CreditCard VALUES('7341973676894160','2685','Discover','2022-11-30',10);
202INSERT INTO CreditCard VALUES('1654873210619853','8254','American Express','2021-05-31',11);
203INSERT INTO CreditCard VALUES('8256166663904591','1588','Discover','2021-08-31',12);
204INSERT INTO CreditCard VALUES('7588446596431283','7597','Visa','2020-03-31',13);
205INSERT INTO CreditCard VALUES('3249263200178344','5275','MasterCard','2021-08-31',14);
206INSERT INTO CreditCard VALUES('7066723463818103','9140','Visa','2023-10-31',15);
207
208INSERT INTO CreditCard VALUES('9050260343067428','4334','American Express','2023-09-30',1);
209INSERT INTO CreditCard VALUES('9988928808837826','2066','MasterCard','2022-01-31',2);
210INSERT INTO CreditCard VALUES('6866750296195129','410','Visa','2019-02-28',3);
211INSERT INTO CreditCard VALUES('8433234695712901','7144','American Express','2023-03-31',4);
212INSERT INTO CreditCard VALUES('4307074330713896','8487','Capital One','2023-05-31',5);
213INSERT INTO CreditCard VALUES('7501269562023275','3202','Capital One','2021-02-28',6);
214INSERT INTO CreditCard VALUES('7148614631813306','7108','Citibank','2023-04-30',7);
215INSERT INTO CreditCard VALUES('8918693653819535','7320','Citibank','2020-05-31',8);
216INSERT INTO CreditCard VALUES('8853762869816894','1419','Diners','2023-04-30',9);
217INSERT INTO CreditCard VALUES('9102769033826449','1873','American Express','2019-02-28',10);
218INSERT INTO CreditCard VALUES('2614658709642602','1657','Diners','2019-11-30',11);
219INSERT INTO CreditCard VALUES('2611733976264853','6795','MasterCard','2023-03-31',12);
220INSERT INTO CreditCard VALUES('2410435961704171','1983','Citibank','2021-08-31',13);
221INSERT INTO CreditCard VALUES('6593095350607913','2546','Citibank','2021-03-31',14);
222INSERT INTO CreditCard VALUES('2209687163105684','3770','Citibank','2021-08-31',15);
223INSERT INTO CreditCard VALUES('5869751114901469','5224','Visa','2021-08-31',13);
224INSERT INTO CreditCard VALUES('4207375484267524','4006','Diners','2021-02-28',13);
225INSERT INTO CreditCard VALUES('9158613353021138','6052','American Express','2022-10-31',5);
226INSERT INTO CreditCard VALUES('5738785636188285','6993','Visa','2020-10-31',14);
227*/
228INSERT INTO CreditCard VALUES('8745293703114267','3797','Citibank','2020-11-30',1);
229INSERT INTO CreditCard VALUES('2679384785588009','8720','Citibank','2018-03-31',2);
230INSERT INTO CreditCard VALUES('6436119583326253','5097','Diners','2021-08-31',3);
231INSERT INTO CreditCard VALUES('3116626043756071','8092','Visa','2021-11-30',4);
232INSERT INTO CreditCard VALUES('4345050888490444','4134','MasterCard','2022-07-31',5);
233INSERT INTO CreditCard VALUES('9354311844645448','5351','MasterCard','2022-03-31',6);
234INSERT INTO CreditCard VALUES('3602850644847330','2106','Citibank','2018-05-31',7);
235INSERT INTO CreditCard VALUES('9180820911790110','1400','Capital One','2019-06-30',8);
236INSERT INTO CreditCard VALUES('1583406448451580','6974','Citibank','2023-09-30',9);
237INSERT INTO CreditCard VALUES('9269960865610390','4989','Discover','2019-01-31',10);
238INSERT INTO CreditCard VALUES('4306323393279742','4196','MasterCard','2020-06-30',11);
239INSERT INTO CreditCard VALUES('4573384248599067','3670','Capital One','2020-08-31',12);
240INSERT INTO CreditCard VALUES('3094874180668958','2673','MasterCard','2022-10-31',13);
241INSERT INTO CreditCard VALUES('5331017677288009','8966','Discover','2023-02-28',14);
242INSERT INTO CreditCard VALUES('7005854403531840','1097','Diners','2022-02-28',15);
243INSERT INTO CreditCard VALUES('2686541588934054','5757','American Express','2022-11-30',8);
244INSERT INTO CreditCard VALUES('6886742199461103','2789','Diners','2023-08-31',4);
245INSERT INTO CreditCard VALUES('6874365590225482','9113','Diners','2020-07-31',3);
246INSERT INTO CreditCard VALUES('5218314559070124','7792','Discover','2022-09-30',5);
247GO
248
249create table CreditDebitTrans (
250 TransID BIGINT,
251 TransType VARCHAR(20) NOT NULL,
252 TransAmt SMALLMONEY NOT NULL,
253 TransDateTime DATETIME NOT NULL,
254 CardNo VARCHAR(16) NOT NULL,
255 CONSTRAINT PK_CREDITDEBITTRANS PRIMARY KEY(TransID),
256 CONSTRAINT FK_CREDITDEBITTRANS_CREDITCARD FOREIGN KEY(CardNo) REFERENCES CreditCard(CardNo)
257);
258GO
259/*
260INSERT INTO CreditDebitTrans VALUES(1,'Debit',6.96,'2019-08-31 09:19:47','2899456568720215');
261INSERT INTO CreditDebitTrans VALUES(2,'Top-up',9.52,'2019-08-31 22:52:19','6218746476325216');
262INSERT INTO CreditDebitTrans VALUES(3,'Top-up',7.72,'2019-10-31 19:04:39','6218746476325216');
263INSERT INTO CreditDebitTrans VALUES(4,'Debit',5.76,'2019-06-30 08:29:18','8256166663904591');
264INSERT INTO CreditDebitTrans VALUES(5,'Top-up',5.11,'2019-07-31 08:59:45','3249263200178344');
265INSERT INTO CreditDebitTrans VALUES(6,'Top-up',6.78,'2019-11-30 17:37:15','6044876956822460');
266INSERT INTO CreditDebitTrans VALUES(7,'Debit',4.72,'2019-05-31 20:49:39','6044876956822460');
267INSERT INTO CreditDebitTrans VALUES(8,'Top-up',9.07,'2019-10-31 08:48:41','7066723463818103');
268INSERT INTO CreditDebitTrans VALUES(9,'Top-up',9.02,'2019-10-31 01:28:34','8880608937225050');
269INSERT INTO CreditDebitTrans VALUES(10,'Debit',6.25,'2019-11-30 19:13:03','3249263200178344');
270INSERT INTO CreditDebitTrans VALUES(11,'Top-up',8.72,'2019-05-31 21:29:59','3249263200178344');
271INSERT INTO CreditDebitTrans VALUES(12,'Top-up',8.00,'2019-09-30 04:55:51','7341973676894160');
272INSERT INTO CreditDebitTrans VALUES(13,'Top-up',8.47,'2019-02-28 03:15:49','5337633403197111');
273INSERT INTO CreditDebitTrans VALUES(14,'Debit',5.81,'2019-03-31 00:19:39','2899456568720215');
274INSERT INTO CreditDebitTrans VALUES(15,'Top-up',4.15,'2019-09-30 00:58:55','7794998005757402');
275
276INSERT INTO CreditDebitTrans VALUES(2,'Debit',-6.35,'2018-06-13 20:26:40','9988928808837826');
277INSERT INTO CreditDebitTrans VALUES(11,'Debit',-9.23,'2018-06-15 6:03:26','2614658709642602');
278INSERT INTO CreditDebitTrans VALUES(1,'Debit',-5.22,'2018-06-18 11:05:32','9050260343067428');
279INSERT INTO CreditDebitTrans VALUES(9,'Debit',-14.88,'2018-04-02 26:07:08','8853762869816894');
280INSERT INTO CreditDebitTrans VALUES(5,'Debit',-6.57,'2018-11-18 25:39:54','4307074330713896');
281INSERT INTO CreditDebitTrans VALUES(13,'Debit',-15.50,'2018-11-12 14:51:25','2410435961704171');
282INSERT INTO CreditDebitTrans VALUES(10,'Debit',-7.12,'2018-11-13 15:04:38','9102769033826449');
283INSERT INTO CreditDebitTrans VALUES(3,'Debit',-0.77,'2018-12-08 11:02:50','6866750296195129');
284INSERT INTO CreditDebitTrans VALUES(4,'Debit',-13.81,'2018-05-04 24:15:18','8433234695712901');
285INSERT INTO CreditDebitTrans VALUES(8,'Debit',-0.00,'2018-07-07 11:56:26','8918693653819535');
286INSERT INTO CreditDebitTrans VALUES(12,'Debit',-5.90,'2018-03-19 2:25:49','2611733976264853');
287INSERT INTO CreditDebitTrans VALUES(6,'Debit',-5.32,'2018-01-20 9:03:03','7501269562023275');
288INSERT INTO CreditDebitTrans VALUES(15,'Debit',-3.21,'2018-06-01 4:25:04','2209687163105684');
289INSERT INTO CreditDebitTrans VALUES(7,'Debit',-9.37,'2018-06-16 22:33:19','7148614631813306');
290INSERT INTO CreditDebitTrans VALUES(1,'Top-up',6.02,'2018-10-31 10:24:59','6593095350607913');
291INSERT INTO CreditDebitTrans VALUES(2,'Top-up',9.96,'2018-06-30 14:49:15','9050260343067428');
292INSERT INTO CreditDebitTrans VALUES(3,'Top-up',8.32,'2018-11-30 21:48:16','4307074330713896');
293INSERT INTO CreditDebitTrans VALUES(4,'Top-up',4.34,'2018-01-31 22:38:21','8433234695712901');
294INSERT INTO CreditDebitTrans VALUES(5,'Top-up',6.11,'2018-10-31 14:21:46','8853762869816894');
295INSERT INTO CreditDebitTrans VALUES(6,'Top-up',5.74,'2018-09-30 18:15:28','9158613353021138');
296*/
297INSERT INTO CreditDebitTrans VALUES(13,'Debit',-7.64,'2018-03-02 8:18:09','3094874180668958');
298INSERT INTO CreditDebitTrans VALUES(9,'Debit',-1.27,'2018-12-13 9:00:35','1583406448451580');
299INSERT INTO CreditDebitTrans VALUES(3,'Debit',-8.99,'2018-08-06 20:02:36','6436119583326253');
300INSERT INTO CreditDebitTrans VALUES(7,'Debit',-12.86,'2018-01-01 9:49:42','3602850644847330');
301INSERT INTO CreditDebitTrans VALUES(10,'Debit',-13.76,'2018-08-25 17:23:29','9269960865610390');
302INSERT INTO CreditDebitTrans VALUES(1,'Debit',-3.68,'2018-01-28 17:15:39','8745293703114267');
303INSERT INTO CreditDebitTrans VALUES(8,'Debit',-1.47,'2018-12-31 5:15:31','9180820911790110');
304INSERT INTO CreditDebitTrans VALUES(14,'Debit',-2.91,'2018-11-28 21:07:15','5331017677288009');
305INSERT INTO CreditDebitTrans VALUES(11,'Debit',-9.10,'2018-04-27 7:22:14','4306323393279742');
306INSERT INTO CreditDebitTrans VALUES(2,'Debit',-12.96,'2018-01-12 4:34:14','2679384785588009');
307INSERT INTO CreditDebitTrans VALUES(12,'Debit',-10.20,'2018-03-25 12:03:59','4573384248599067');
308INSERT INTO CreditDebitTrans VALUES(4,'Debit',-1.88,'2018-12-31 12:45:20','3116626043756071');
309INSERT INTO CreditDebitTrans VALUES(6,'Debit',-9.96,'2018-09-30 15:31:26','9354311844645448');
310INSERT INTO CreditDebitTrans VALUES(5,'Debit',-11.44,'2018-04-02 22:50:09','4345050888490444');
311INSERT INTO CreditDebitTrans VALUES(15,'Debit',-13.81,'2018-01-10 22:12:58','7005854403531840');
312INSERT INTO CreditDebitTrans VALUES(19,'Top-up',5.68,'2018-07-31 01:51:19','5331017677288009');
313INSERT INTO CreditDebitTrans VALUES(20,'Top-up',6.16,'2018-06-30 15:10:26','2686541588934054');
314INSERT INTO CreditDebitTrans VALUES(17,'Top-up',7.64,'2018-07-31 12:16:29','5218314559070124');
315INSERT INTO CreditDebitTrans VALUES(16,'Top-up',7.88,'2018-10-31 10:53:32','5331017677288009');
316INSERT INTO CreditDebitTrans VALUES(18,'Top-up',8.10,'2018-07-31 20:05:24','2686541588934054');
317Go
318
319create table Ride (
320 RideID BIGINT,
321 PtsRedeemed INT NULL,
322 RidePts INT NOT NULL,
323 Cost SMALLMONEY NOT NULL,
324 Distance FLOAT NOT NULL,
325 DateTimeStart DATETIME NOT NULL,
326 DateTimeEnd DATETIME NOT NULL,
327 MemberID BIGINT NOT NULL,
328 BikeID BIGINT NOT NULL,
329 PromoCode CHAR(6) NULL,
330 Start BIGINT NOT NULL,
331 Finish BIGINT NOT NULL,
332 TransID BIGINT NOT NULL,
333 CONSTRAINT PK_RIDE PRIMARY KEY(RideID),
334 CONSTRAINT FK_RIDE_MEMBER FOREIGN KEY(MemberID) REFERENCES Member(MemberID),
335 CONSTRAINT FK_RIDE_BIKE FOREIGN KEY(BikeID) REFERENCES Bike(BikeID),
336 CONSTRAINT FK_RIDE_PROMOTION FOREIGN KEY(PromoCode) REFERENCES Promotion(PromoCode),
337 CONSTRAINT FK_RIDE_BIKESTOP_START FOREIGN KEY(Start) REFERENCES BikeStop(BikeStopID),
338 CONSTRAINT FK_RIDE_BIKESTOP_FINISH FOREIGN KEY(Finish) REFERENCES BikeStop(BikeStopID),
339 CONSTRAINT FK_RIDE_CREDITDEBITTRANS FOREIGN KEY(TransID) REFERENCES CreditDebitTrans(TransID)
340);
341GO
342
343INSERT INTO Ride VALUES(1,150,57,7.64,11.57,'2018-03-02 04:18:37','2018-03-02 8:18:09',12,3,200002,9,5,13);
344INSERT INTO Ride VALUES(2,50,61,1.27,12.40,'2018-12-13 08:01:23','2018-12-13 9:00:35',2,15,200006,13,11,9);
345INSERT INTO Ride VALUES(3,200,62,8.99,12.49,'2018-08-06 16:01:24','2018-08-06 20:02:36',2,13,null,3,5,3);
346INSERT INTO Ride VALUES(4,100,102,12.86,20.53,'2018-06-29 03:21:08','2018-06-29 9:49:42',10,10,200005,14,4,7);
347INSERT INTO Ride VALUES(5,0,84,13.76,16.90,'2018-08-25 15:34:15','2018-08-25 17:23:29',14,10,200001,1,15,10);
348INSERT INTO Ride VALUES(6,150,22,3.68,4.56,'2018-01-28 12:28:28','2018-01-28 17:15:39',10,12,null,14,8,1);
349INSERT INTO Ride VALUES(7,200,81,1.47,16.35,'2018-12-31 01:05:28','2018-12-31 5:15:31',11,8,200006,15,4,8);
350INSERT INTO Ride VALUES(8,250,27,2.91,5.45,'2018-11-28 19:45:40','2018-11-28 21:07:15',13,13,200006,11,3,14);
351INSERT INTO Ride VALUES(9,100,57,9.10,11.53,'2018-04-27 06:52:32','2018-04-27 7:22:14',5,7,null,15,7,11);
352INSERT INTO Ride VALUES(10,0,98,12.96,19.60,'2018-06-27 00:29:21','2018-06-27 4:34:14',3,11,200005,1,7,2);
353INSERT INTO Ride VALUES(11,0,59,10.20,11.94,'2018-03-25 08:55:59','2018-03-25 12:03:59',9,15,200001,9,14,12);
354INSERT INTO Ride VALUES(12,250,107,1.88,21.43,'2018-12-31 08:49:39','2018-12-31 12:45:20',7,2,null,9,2,4);
355INSERT INTO Ride VALUES(13,300,74,9.96,14.95,'2018-09-30 12:42:26','2018-09-30 15:31:26',4,4,200001,6,9,6);
356INSERT INTO Ride VALUES(14,50,71,11.44,14.22,'2018-04-02 21:36:08','2018-04-02 22:50:09',14,6,200001,13,14,5);
357INSERT INTO Ride VALUES(15,300,101,13.81,20.29,'2018-01-10 21:57:42','2018-01-10 22:12:58',14,8,null,6,9,15);
358Go
359
360
361create table Maintenance (
362 MaintID BIGINT,
363 MaintDate DATETIME NOT NULL,
364 BikeID BIGINT NOT NULL,
365 CONSTRAINT PK_MAINTENANCE PRIMARY KEY(MaintID),
366 CONSTRAINT FK_MAINTENANCE_BIKE FOREIGN KEY(BikeID) REFERENCES Bike(BikeID)
367);
368GO
369INSERT INTO Maintenance VALUES(1,'2018-01-02',5);
370INSERT INTO Maintenance VALUES(2,'2018-03-09',8);
371INSERT INTO Maintenance VALUES(3,'2018-06-01',1);
372INSERT INTO Maintenance VALUES(4,'2018-09-12',9);
373INSERT INTO Maintenance VALUES(5,'2018-12-24',12);
374INSERT INTO Maintenance VALUES(6,'2018-11-01',15);
375INSERT INTO Maintenance VALUES(7,'2018-03-10',8);
376INSERT INTO Maintenance VALUES(8,'2018-07-31',7);
377INSERT INTO Maintenance VALUES(9,'2018-11-30',4);
378INSERT INTO Maintenance VALUES(10,'2018-11-22',10);
379INSERT INTO Maintenance VALUES(11,'2018-05-25',12);
380INSERT INTO Maintenance VALUES(12,'2018-08-04',15);
381INSERT INTO Maintenance VALUES(13,'2018-02-28',3);
382INSERT INTO Maintenance VALUES(14,'2018-09-13',13);
383INSERT INTO Maintenance VALUES(15,'2018-02-02',8);
384GO
385
386create table Repair (
387 MaintID BIGINT,
388 CONSTRAINT PK_REPAIR PRIMARY KEY(MaintID),
389 CONSTRAINT FK_REPAIR_MAINTENANCE FOREIGN KEY(MaintID) REFERENCES Maintenance(MaintID)
390);
391GO
392
393INSERT INTO Repair VALUES(1);
394INSERT INTO Repair VALUES(2);
395INSERT INTO Repair VALUES(3);
396INSERT INTO Repair VALUES(4);
397INSERT INTO Repair VALUES(5);
398INSERT INTO Repair VALUES(6);
399INSERT INTO Repair VALUES(7);
400INSERT INTO Repair VALUES(8);
401INSERT INTO Repair VALUES(9);
402INSERT INTO Repair VALUES(10);
403INSERT INTO Repair VALUES(11);
404INSERT INTO Repair VALUES(12);
405INSERT INTO Repair VALUES(13);
406INSERT INTO Repair VALUES(14);
407INSERT INTO Repair VALUES(15);
408
409GO
410
411create table MaintType (
412 TypeCode INT,
413 TypeDesc TEXT NULL,
414 CONSTRAINT PK_MAINTTYPE PRIMARY KEY(TypeCode)
415);
416GO
417INSERT INTO MaintType VALUES(1,'Tyre');
418INSERT INTO MaintType VALUES(2,'Pedal');
419INSERT INTO MaintType VALUES(3,'Handle');
420INSERT INTO MaintType VALUES(4,'Lights');
421INSERT INTO MaintType VALUES(5,'Brake');
422INSERT INTO MaintType VALUES(6,'Bike Chain');
423INSERT INTO MaintType VALUES(7,'Bike Seat');
424INSERT INTO MaintType VALUES(8,'QR Code');
425INSERT INTO MaintType VALUES(9,'Bell');
426INSERT INTO MaintType VALUES(10,'Bikestand');
427GO
428
429create table MaintItem (
430 MaintID BIGINT NOT NULL,
431 SNo BIGINT NOT NULL,
432 Descr TEXT NULL,
433 TypeCode INT NULL,
434 CONSTRAINT PK_MaintItem PRIMARY KEY (MaintID, SNo),
435 CONSTRAINT FK_MAINTITEM_MAINTENANCE FOREIGN KEY(MaintID) REFERENCES Maintenance(MaintID),
436 CONSTRAINT FK_MAINTIEM_MAINTTYPE FOREIGN KEY(TypeCode) REFERENCES MaintType(TypeCode)
437);
438GO
439INSERT INTO MaintItem VALUES(13,1,'Fixed Faulty BikeStand',10);
440INSERT INTO MaintItem VALUES(3,2,'Fixed Faulty Pedal',2);
441INSERT INTO MaintItem VALUES(9,3,'Fixed Faulty Handle',3);
442INSERT INTO MaintItem VALUES(1,5,'Fixed Faulty Headlight',4);
443INSERT INTO MaintItem VALUES(8,6,'Fixed Faulty Backlight',4);
444INSERT INTO MaintItem VALUES(7,7,'Fixed Faulty Brake',5);
445INSERT INTO MaintItem VALUES(2,8,'Fixed Broken Bike Chain',6);
446INSERT INTO MaintItem VALUES(15,9,'Fixed Dislodged Bike Chain',6);
447INSERT INTO MaintItem VALUES(4,10,'Fixed Faulty Bike Seat',7);
448INSERT INTO MaintItem VALUES(10,11,'Fixed Unreadable QR Code',8);
449INSERT INTO MaintItem VALUES(11,12,'Fixed Faulty Bell',9);
450INSERT INTO MaintItem VALUES(5,13,'Fixed Burst Tyre',1);
451INSERT INTO MaintItem VALUES(14,14,'Fixed Flat Tyre',1);
452GO
453
454create table Feedback (
455 FBID BIGINT,
456 FBDateTime DATETIME NOT NULL,
457 MemberID BIGINT NOT NULL,
458 CONSTRAINT PK_FEEDBACK PRIMARY KEY(FBID),
459 CONSTRAINT FK_FEEDBACK_MEMBER FOREIGN KEY(MemberID) REFERENCES Member(MemberID)
460);
461GO
462INSERT INTO Feedback VALUES(1,'2018-01-01',2);
463INSERT INTO Feedback VALUES(2,'2018-03-12',4);
464INSERT INTO Feedback VALUES(3,'2018-01-28',6);
465INSERT INTO Feedback VALUES(4,'2018-09-30',8);
466INSERT INTO Feedback VALUES(5,'2018-11-11',2);
467INSERT INTO Feedback VALUES(6,'2018-12-24',13);
468INSERT INTO Feedback VALUES(7,'2018-04-01',14);
469INSERT INTO Feedback VALUES(8,'2018-08-04',15);
470INSERT INTO Feedback VALUES(9,'2018-05-25',1);
471INSERT INTO Feedback VALUES(10,'2018-04-02',9);
472INSERT INTO Feedback VALUES(11,'2018-12-03',14);
473INSERT INTO Feedback VALUES(12,'2018-06-06',12);
474INSERT INTO Feedback VALUES(13,'2018-07-07',15);
475INSERT INTO Feedback VALUES(14,'2018-04-17',11);
476INSERT INTO Feedback VALUES(15,'2018-07-12',1);
477INSERT INTO Feedback VALUES(16,'2018-09-27',2);
478INSERT INTO Feedback VALUES(17,'2018-08-09',14);
479INSERT INTO Feedback VALUES(18,'2018-01-07',12);
480INSERT INTO Feedback VALUES(19,'2018-01-07',9);
481INSERT INTO Feedback VALUES(20,'2018-04-19',1);
482INSERT INTO Feedback VALUES(21,'2018-07-20',13);
483INSERT INTO Feedback VALUES(22,'2018-10-22',7);
484INSERT INTO Feedback VALUES(23,'2018-12-12',9);
485INSERT INTO Feedback VALUES(24,'2018-02-18',2);
486INSERT INTO Feedback VALUES(25,'2019-06-19',11)
487
488
489GO
490
491create table Comment (
492 FBID BIGINT,
493 Message TEXT NULL,
494 CONSTRAINT PK_COMMENT PRIMARY KEY(FBID),
495 CONSTRAINT FK_COMMENT_FEEDBACK FOREIGN KEY(FBID) REFERENCES Feedback(FBID)
496);
497GO
498INSERT INTO Comment VALUES(1,'Bikes was in good condition');
499INSERT INTO Comment VALUES(3,'Prefer to have more bikes in my area');
500INSERT INTO Comment VALUES(10,'Bike Stops too little');
501INSERT INTO Comment VALUES(7,'Bike is too heavy');
502INSERT INTO Comment VALUES(9,'Good amount of promo codes');
503INSERT INTO Comment VALUES(2,'Too little bikes in Choa Chu Kang Area');
504INSERT INTO Comment VALUES(5,'Wish bikes had different gears');
505INSERT INTO Comment VALUES(15,'Pedal feels too weak');
506INSERT INTO Comment VALUES(18,'Seat feels unstable');
507INSERT INTO Comment VALUES(11,'Bikes feels premium. Good Job!');
508GO
509
510create table Fault (
511 FBID BIGINT,
512 FaultCode INT NOT NULL,
513 MaintID BIGINT NULL,
514 BikeID BIGINT NOT NULL,
515 CONSTRAINT PK_FAULT PRIMARY KEY(FBID),
516 CONSTRAINT FK_FAULT_FEEDBACK FOREIGN KEY(FBID) REFERENCES Feedback(FBID),
517 CONSTRAINT FK_FAULT_FAULTTYPE FOREIGN KEY(FaultCode) REFERENCES FaultType(FaultCode),
518 CONSTRAINT FK_FAULT_MAINTENANCE FOREIGN KEY(MaintID) REFERENCES Repair(MaintID),
519 CONSTRAINT FK_FAULT_BIKE FOREIGN KEY(BikeID) REFERENCES Bike(BikeID)
520);
521GO
522
523INSERT INTO Fault VALUES(4,1001,1,5);
524INSERT INTO Fault VALUES(6,1011,2,8);
525INSERT INTO Fault VALUES(8,1004,3,1);
526INSERT INTO Fault VALUES(12,1008,4,9);
527INSERT INTO Fault VALUES(13,1009,5,12);
528INSERT INTO Fault VALUES(14,1001,6,15);
529INSERT INTO Fault VALUES(16,1010,7,8);
530INSERT INTO Fault VALUES(17,1013,8,7);
531INSERT INTO Fault VALUES(19,1007,9,4);
532INSERT INTO Fault VALUES(20,1003,10,10);
533INSERT INTO Fault VALUES(21,1011,11,12);
534INSERT INTO Fault VALUES(22,1010,12,15);
535INSERT INTO Fault VALUES(23,1001,13,3);
536INSERT INTO Fault VALUES(24,1006,14,13);
537INSERT INTO Fault VALUES(25,1004,15,8);
538GO
539
540create table Reservation (
541 MemberID BIGINT,
542 BikeID BIGINT,
543 ResStatus VARCHAR(15) NOT NULL CHECK (ResStatus IN ('Confirmed', 'Cancelled', 'NoShow')),
544 DateTimeRes DATETIME NOT NULL,
545 DateTimeReservedFor DATETIME NOT NULL,
546 CONSTRAINT PK_Reservation PRIMARY KEY (MemberID, BikeID),
547 CONSTRAINT CHK_DateTime CHECK (DateTimeRes < DateTimeReservedFor),
548 CONSTRAINT FK_RESERVATION_MEMBER FOREIGN KEY(MemberID) REFERENCES Member(MemberID),
549 CONSTRAINT FK_RESERVATION_BIKE FOREIGN KEY(BikeID) REFERENCES Bike(BikeID),
550);
551GO
552INSERT INTO Reservation VALUES(1,8,'Confirmed','2018-03-11 10:56:10','2018-03-11 12:00:00');
553INSERT INTO Reservation VALUES(12,9,'Cancelled','2019-05-11 10:56:10','2019-05-13 8:00:00');
554INSERT INTO Reservation VALUES(7,11,'Confirmed','2018-07-09 22:20:10','2018-07-12 7:15:00');
555INSERT INTO Reservation VALUES(8,10,'Confirmed','2018-08-04 8:00:10','2018-08-04 8:10:00');
556INSERT INTO Reservation VALUES(2,2,'NoShow','2018-11-10 10:00:00','2018-11-15 12:00:00');
557INSERT INTO Reservation VALUES(10,8,'Confirmed','2019-12-05 21:46:24','2019-12-15 15:13:00');
558INSERT INTO Reservation VALUES(1,11,'Confirmed','2018-03-09 09:50:18','2018-03-09 13:15:00');
559INSERT INTO Reservation VALUES(8,15,'NoShow','2018-03-14 22:10:10','2018-03-14 22:30:00');
560INSERT INTO Reservation VALUES(3,3,'Cancelled','2018-08-12 07:09:10','2018-08-14 03:15:00');
561INSERT INTO Reservation VALUES(12,14,'Confirmed','2018-04-01 11:45:00','2018-04-11 12:00:00');
562INSERT INTO Reservation VALUES(15,9,'Confirmed','2019-11-02 23:31:45','2019-11-03 12:00:00');
563INSERT INTO Reservation VALUES(12,3,'Cancelled','2018-12-15 08:28:39','2018-12-17 15:10:00');
564INSERT INTO Reservation VALUES(13,6,'Confirmed','2018-07-24 11:50:00','2018-07-26 13:25:00');
565INSERT INTO Reservation VALUES(14,2,'Confirmed','2019-10-29 17:00:10','2019-10-30 07:14:00');
566INSERT INTO Reservation VALUES(7,1,'Confirmed','2018-12-24 21:30:10','2018-12-24 22:00:00');
567GO
568
569USE NP40Book;
570GO