0t6M4uuD

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