5p8B6Mzb

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
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	INT			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', 0, 0,50);
45INSERT INTO Member VALUES(2,'Jack' ,'jack@gmail.com', '87777777', 0, 0, 50);
46INSERT INTO Member VALUES(3,'Ashok' ,'ashok@gmail.com', '86666666', 0, 0, 50);
47INSERT INTO Member VALUES(4,'Bala' ,'bala@gmail.com', '85555555', 0, 0, 50);
48INSERT INTO Member VALUES(5,'Jibala','jilaba@gmail.com', '84444444', 0, 0, 50);
49INSERT INTO Member VALUES(6,'Marcus' ,'marcus@gmail.com', '84444444', 0, 0, 50);
50INSERT INTO Member VALUES(7,'Ethan' ,'ethan@gmail.com', '83333333', 0, 0, 50);
51INSERT INTO Member VALUES(8,'Dominic' ,'dom@gmail.com', '82222222', 0, 0, 50);
52INSERT INTO Member VALUES(9,'Alloysius' ,'alloy@gmail.com', '81111111', 0, 0, 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, 0, 50);
56INSERT INTO Member VALUES(13,'Chan Chee How' ,'cch@gmail.com', '93175624', 0, 0, 50);
57INSERT INTO Member VALUES(14,'Sim Qui Ling' ,'sql@gmail.com', '97864412', 0, 0, 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	TEXT			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','2020-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','2022-01-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
258);
259GO
260/*
261INSERT INTO CreditDebitTrans VALUES(1,'Debit',6.96,'2019-08-31 09:19:47','2899456568720215');
262INSERT INTO CreditDebitTrans VALUES(2,'Top-up',9.52,'2019-08-31 22:52:19','6218746476325216');
263INSERT INTO CreditDebitTrans VALUES(3,'Top-up',7.72,'2019-10-31 19:04:39','6218746476325216');
264INSERT INTO CreditDebitTrans VALUES(4,'Debit',5.76,'2019-06-30 08:29:18','8256166663904591');
265INSERT INTO CreditDebitTrans VALUES(5,'Top-up',5.11,'2019-07-31 08:59:45','3249263200178344');
266INSERT INTO CreditDebitTrans VALUES(6,'Top-up',6.78,'2019-11-30 17:37:15','6044876956822460');
267INSERT INTO CreditDebitTrans VALUES(7,'Debit',4.72,'2019-05-31 20:49:39','6044876956822460');
268INSERT INTO CreditDebitTrans VALUES(8,'Top-up',9.07,'2019-10-31 08:48:41','7066723463818103');
269INSERT INTO CreditDebitTrans VALUES(9,'Top-up',9.02,'2019-10-31 01:28:34','8880608937225050');
270INSERT INTO CreditDebitTrans VALUES(10,'Debit',6.25,'2019-11-30 19:13:03','3249263200178344');
271INSERT INTO CreditDebitTrans VALUES(11,'Top-up',8.72,'2019-05-31 21:29:59','3249263200178344');
272INSERT INTO CreditDebitTrans VALUES(12,'Top-up',8.00,'2019-09-30 04:55:51','7341973676894160');
273INSERT INTO CreditDebitTrans VALUES(13,'Top-up',8.47,'2019-02-28 03:15:49','5337633403197111');
274INSERT INTO CreditDebitTrans VALUES(14,'Debit',5.81,'2019-03-31 00:19:39','2899456568720215');
275INSERT INTO CreditDebitTrans VALUES(15,'Top-up',4.15,'2019-09-30 00:58:55','7794998005757402');
276
277INSERT INTO CreditDebitTrans VALUES(2,'Debit',-6.35,'2018-06-13 20:26:40','9988928808837826');
278INSERT INTO CreditDebitTrans VALUES(11,'Debit',-9.23,'2018-06-15 6:03:26','2614658709642602');
279INSERT INTO CreditDebitTrans VALUES(1,'Debit',-5.22,'2018-06-18 11:05:32','9050260343067428');
280INSERT INTO CreditDebitTrans VALUES(9,'Debit',-14.88,'2018-04-02 26:07:08','8853762869816894');
281INSERT INTO CreditDebitTrans VALUES(5,'Debit',-6.57,'2018-11-18 25:39:54','4307074330713896');
282INSERT INTO CreditDebitTrans VALUES(13,'Debit',-15.50,'2018-11-12 14:51:25','2410435961704171');
283INSERT INTO CreditDebitTrans VALUES(10,'Debit',-7.12,'2018-11-13 15:04:38','9102769033826449');
284INSERT INTO CreditDebitTrans VALUES(3,'Debit',-0.77,'2018-12-08 11:02:50','6866750296195129');
285INSERT INTO CreditDebitTrans VALUES(4,'Debit',-13.81,'2018-05-04 24:15:18','8433234695712901');
286INSERT INTO CreditDebitTrans VALUES(8,'Debit',-0.00,'2018-07-07 11:56:26','8918693653819535');
287INSERT INTO CreditDebitTrans VALUES(12,'Debit',-5.90,'2018-03-19 2:25:49','2611733976264853');
288INSERT INTO CreditDebitTrans VALUES(6,'Debit',-5.32,'2018-01-20 9:03:03','7501269562023275');
289INSERT INTO CreditDebitTrans VALUES(15,'Debit',-3.21,'2018-06-01 4:25:04','2209687163105684');
290INSERT INTO CreditDebitTrans VALUES(7,'Debit',-9.37,'2018-06-16 22:33:19','7148614631813306');
291INSERT INTO CreditDebitTrans VALUES(1,'Top-up',6.02,'2018-10-31 10:24:59','6593095350607913');
292INSERT INTO CreditDebitTrans VALUES(2,'Top-up',9.96,'2018-06-30 14:49:15','9050260343067428');
293INSERT INTO CreditDebitTrans VALUES(3,'Top-up',8.32,'2018-11-30 21:48:16','4307074330713896');
294INSERT INTO CreditDebitTrans VALUES(4,'Top-up',4.34,'2018-01-31 22:38:21','8433234695712901');
295INSERT INTO CreditDebitTrans VALUES(5,'Top-up',6.11,'2018-10-31 14:21:46','8853762869816894');
296INSERT INTO CreditDebitTrans VALUES(6,'Top-up',5.74,'2018-09-30 18:15:28','9158613353021138');
297*/
298INSERT INTO CreditDebitTrans VALUES(13,'Debit',-7.64,'2018-03-02 8:18:09','3094874180668958');
299INSERT INTO CreditDebitTrans VALUES(9,'Debit',-1.27,'2018-12-13 9:00:35','1583406448451580');
300INSERT INTO CreditDebitTrans VALUES(3,'Debit',-8.99,'2018-08-06 20:02:36','6436119583326253');
301INSERT INTO CreditDebitTrans VALUES(7,'Debit',-12.86,'2018-06-29 9:49:42','3602850644847330');
302INSERT INTO CreditDebitTrans VALUES(10,'Debit',-13.76,'2018-08-25 17:23:29','9269960865610390');
303INSERT INTO CreditDebitTrans VALUES(1,'Debit',-3.68,'2018-01-28 17:15:39','8745293703114267');
304INSERT INTO CreditDebitTrans VALUES(8,'Debit',-1.47,'2018-12-31 5:15:31','9180820911790110');
305INSERT INTO CreditDebitTrans VALUES(14,'Debit',-2.91,'2018-11-28 21:07:15','5331017677288009');
306INSERT INTO CreditDebitTrans VALUES(11,'Debit',-9.10,'2018-04-27 7:22:14','4306323393279742');
307INSERT INTO CreditDebitTrans VALUES(2,'Debit',-12.96,'2018-06-27 4:34:14','2679384785588009');
308INSERT INTO CreditDebitTrans VALUES(12,'Debit',-10.20,'2018-03-25 12:03:59','4573384248599067');
309INSERT INTO CreditDebitTrans VALUES(4,'Debit',-1.88,'2018-12-31 12:45:20','3116626043756071');
310INSERT INTO CreditDebitTrans VALUES(6,'Debit',-9.96,'2018-09-30 15:31:26','9354311844645448');
311INSERT INTO CreditDebitTrans VALUES(5,'Debit',-11.44,'2018-04-02 22:50:09','4345050888490444');
312INSERT INTO CreditDebitTrans VALUES(15,'Debit',-13.81,'2018-01-10 22:12:58','7005854403531840');
313INSERT INTO CreditDebitTrans VALUES(19,'Top-up',5.68,'2018-07-31 01:51:19','5331017677288009');
314INSERT INTO CreditDebitTrans VALUES(20,'Top-up',6.16,'2018-06-30 15:10:26','2686541588934054');
315INSERT INTO CreditDebitTrans VALUES(17,'Top-up',7.64,'2018-07-31 12:16:29','5218314559070124');
316INSERT INTO CreditDebitTrans VALUES(16,'Top-up',7.88,'2018-10-31 10:53:32','5331017677288009');
317INSERT INTO CreditDebitTrans VALUES(18,'Top-up',8.10,'2018-07-31 20:05:24','2686541588934054');
318Go
319
320create table Ride (
321	RideID			BIGINT,
322	PtsRedeemed		INT			NULL,
323	RidePts			INT			NOT NULL,
324	Cost			SMALLMONEY	NOT NULL,
325	Distance		FLOAT		NOT NULL,
326	DateTimeStart	DATETIME	NOT NULL,
327	DateTimeEnd		DATETIME	NOT NULL,
328	MemberID		BIGINT		NOT NULL,
329	BikeID			BIGINT		NOT NULL,
330	PromoCode		CHAR(6)		NULL,
331	Start			BIGINT 		NOT NULL,
332	Finish			BIGINT		NOT NULL,
333	TransID			BIGINT		NOT NULL,
334	CONSTRAINT PK_RIDE PRIMARY KEY(RideID),
335	CONSTRAINT FK_RIDE_MEMBER FOREIGN KEY(MemberID) REFERENCES Member(MemberID),
336	CONSTRAINT FK_RIDE_BIKE FOREIGN KEY(BikeID) REFERENCES Bike(BikeID),
337	CONSTRAINT FK_RIDE_PROMOTION FOREIGN KEY(PromoCode) REFERENCES Promotion(PromoCode),
338	CONSTRAINT FK_RIDE_BIKESTOP_START FOREIGN KEY(Start) REFERENCES BikeStop(BikeStopID),
339	CONSTRAINT FK_RIDE_BIKESTOP_FINISH FOREIGN KEY(Finish) REFERENCES BikeStop(BikeStopID),
340	CONSTRAINT FK_RIDE_CREDITDEBITTRANS FOREIGN KEY(TransID) REFERENCES CreditDebitTrans(TransID)
341);
342GO
343
344INSERT 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);
345INSERT 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);
346INSERT 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);
347INSERT 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);
348INSERT 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);
349INSERT 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);
350INSERT 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);
351INSERT 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);
352INSERT 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);
353INSERT 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);
354INSERT 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);
355INSERT 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);
356INSERT 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);
357INSERT 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);
358INSERT 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);
359Go
360
361
362create table Maintenance (
363	MaintID		BIGINT,
364	MaintDate	DATETIME	NOT NULL,
365	BikeID		BIGINT		NOT NULL,
366	CONSTRAINT PK_MAINTENANCE PRIMARY KEY(MaintID),
367	CONSTRAINT FK_MAINTENANCE_BIKE FOREIGN KEY(BikeID) REFERENCES Bike(BikeID) 
368);
369GO
370INSERT INTO Maintenance VALUES(1,'2018-01-02',5);
371INSERT INTO Maintenance VALUES(2,'2018-03-09',8);
372INSERT INTO Maintenance VALUES(3,'2018-06-01',1);
373INSERT INTO Maintenance VALUES(4,'2018-09-12',9);
374INSERT INTO Maintenance VALUES(5,'2018-12-24',12);
375INSERT INTO Maintenance VALUES(6,'2018-11-01',15);
376INSERT INTO Maintenance VALUES(7,'2018-03-10',8);
377INSERT INTO Maintenance VALUES(8,'2018-07-31',7);
378INSERT INTO Maintenance VALUES(9,'2018-11-30',4);
379INSERT INTO Maintenance VALUES(10,'2018-11-22',10);
380INSERT INTO Maintenance VALUES(11,'2018-05-25',12);
381INSERT INTO Maintenance VALUES(12,'2018-08-04',15);
382INSERT INTO Maintenance VALUES(13,'2018-02-28',3);
383INSERT INTO Maintenance VALUES(14,'2018-09-13',13);
384INSERT INTO Maintenance VALUES(15,'2018-02-02',8);
385GO
386
387create table Repair (
388	MaintID	BIGINT,
389	CONSTRAINT PK_REPAIR PRIMARY KEY(MaintID),
390	CONSTRAINT FK_REPAIR_MAINTENANCE FOREIGN KEY(MaintID) REFERENCES Maintenance(MaintID)
391);
392GO
393
394INSERT INTO Repair VALUES(1);
395INSERT INTO Repair VALUES(2);
396INSERT INTO Repair VALUES(3);
397INSERT INTO Repair VALUES(4);
398INSERT INTO Repair VALUES(5);
399INSERT INTO Repair VALUES(6);
400INSERT INTO Repair VALUES(7);
401INSERT INTO Repair VALUES(8);
402INSERT INTO Repair VALUES(9);
403INSERT INTO Repair VALUES(10);
404INSERT INTO Repair VALUES(11);
405INSERT INTO Repair VALUES(12);
406INSERT INTO Repair VALUES(13);
407INSERT INTO Repair VALUES(14);
408INSERT INTO Repair VALUES(15);
409
410GO
411
412create table MaintType (
413	TypeCode	INT,
414	TypeDesc	TEXT	NULL,
415	CONSTRAINT PK_MAINTTYPE PRIMARY KEY(TypeCode)
416);
417GO
418INSERT INTO MaintType VALUES(1,'Tyre');
419INSERT INTO MaintType VALUES(2,'Pedal');
420INSERT INTO MaintType VALUES(3,'Handle');
421INSERT INTO MaintType VALUES(4,'Lights');
422INSERT INTO MaintType VALUES(5,'Brake');
423INSERT INTO MaintType VALUES(6,'Bike Chain');
424INSERT INTO MaintType VALUES(7,'Bike Seat');
425INSERT INTO MaintType VALUES(8,'QR Code');
426INSERT INTO MaintType VALUES(9,'Bell');
427INSERT INTO MaintType VALUES(10,'Bikestand');
428GO
429
430create table MaintItem (
431	MaintID		BIGINT	NOT NULL,
432	SNo			BIGINT	NOT NULL,
433	Descr		TEXT	NULL,
434	TypeCode	INT		NULL,
435	CONSTRAINT PK_MaintItem	PRIMARY KEY (MaintID, SNo),
436	CONSTRAINT FK_MAINTITEM_MAINTENANCE FOREIGN KEY(MaintID) REFERENCES Maintenance(MaintID),
437	CONSTRAINT FK_MAINTIEM_MAINTTYPE FOREIGN KEY(TypeCode) REFERENCES MaintType(TypeCode)
438);
439GO
440INSERT INTO MaintItem VALUES(13,1,'Fixed Faulty BikeStand',10);
441INSERT INTO MaintItem VALUES(3,2,'Fixed Faulty Pedal',2);
442INSERT INTO MaintItem VALUES(9,3,'Fixed Faulty Handle',3);
443INSERT INTO MaintItem VALUES(1,5,'Fixed Faulty Headlight',4);
444INSERT INTO MaintItem VALUES(8,6,'Fixed Faulty Backlight',4);
445INSERT INTO MaintItem VALUES(7,7,'Fixed Faulty Brake',5);
446INSERT INTO MaintItem VALUES(2,8,'Fixed Broken Bike Chain',6);
447INSERT INTO MaintItem VALUES(15,9,'Fixed Dislodged Bike Chain',6);
448INSERT INTO MaintItem VALUES(4,10,'Fixed Faulty Bike Seat',7);
449INSERT INTO MaintItem VALUES(10,11,'Fixed Unreadable QR Code',8);
450INSERT INTO MaintItem VALUES(11,12,'Fixed Faulty Bell',9);
451INSERT INTO MaintItem VALUES(5,13,'Fixed Burst Tyre',1);
452INSERT INTO MaintItem VALUES(14,14,'Fixed Flat Tyre',1);
453GO
454
455create table Feedback (
456	FBID		BIGINT,
457	FBDateTime	DATETIME	NOT NULL,
458	MemberID	BIGINT		NOT NULL,
459	CONSTRAINT PK_FEEDBACK PRIMARY KEY(FBID),
460	CONSTRAINT FK_FEEDBACK_MEMBER FOREIGN KEY(MemberID) REFERENCES Member(MemberID)
461);
462GO
463INSERT INTO Feedback VALUES(1,'2018-01-01',2);
464INSERT INTO Feedback VALUES(2,'2018-03-12',4);
465INSERT INTO Feedback VALUES(3,'2018-01-28',6);
466INSERT INTO Feedback VALUES(4,'2018-09-30',8);
467INSERT INTO Feedback VALUES(5,'2018-11-11',2);
468INSERT INTO Feedback VALUES(6,'2018-12-24',13);
469INSERT INTO Feedback VALUES(7,'2018-04-01',14);
470INSERT INTO Feedback VALUES(8,'2018-08-04',15);
471INSERT INTO Feedback VALUES(9,'2018-05-25',1);
472INSERT INTO Feedback VALUES(10,'2018-04-02',9);
473INSERT INTO Feedback VALUES(11,'2018-12-03',14);
474INSERT INTO Feedback VALUES(12,'2018-06-06',12);
475INSERT INTO Feedback VALUES(13,'2018-07-07',15);
476INSERT INTO Feedback VALUES(14,'2018-04-17',11);
477INSERT INTO Feedback VALUES(15,'2018-07-12',1);
478INSERT INTO Feedback VALUES(16,'2018-09-27',2);
479INSERT INTO Feedback VALUES(17,'2018-08-09',14);
480INSERT INTO Feedback VALUES(18,'2018-01-07',12);
481INSERT INTO Feedback VALUES(19,'2018-01-07',9);
482INSERT INTO Feedback VALUES(20,'2018-04-19',1);
483INSERT INTO Feedback VALUES(21,'2018-07-20',13);
484INSERT INTO Feedback VALUES(22,'2018-10-22',7);
485INSERT INTO Feedback VALUES(23,'2018-12-12',9);
486INSERT INTO Feedback VALUES(24,'2018-02-18',2);
487INSERT INTO Feedback VALUES(25,'2019-06-19',11)
488
489
490GO
491
492create table Comment (
493	FBID	BIGINT,
494	Message	TEXT	NULL,
495	CONSTRAINT PK_COMMENT PRIMARY KEY(FBID),
496	CONSTRAINT FK_COMMENT_FEEDBACK FOREIGN KEY(FBID) REFERENCES Feedback(FBID)
497);
498GO
499INSERT INTO Comment VALUES(1,'Bikes was in good condition');
500INSERT INTO Comment VALUES(3,'Prefer to have more bikes in my area');
501INSERT INTO Comment VALUES(10,'Bike Stops too little');
502INSERT INTO Comment VALUES(7,'Bike is too heavy');
503INSERT INTO Comment VALUES(9,'Good amount of promo codes');
504INSERT INTO Comment VALUES(2,'Too little bikes in Choa Chu Kang Area');
505INSERT INTO Comment VALUES(5,'Wish bikes had different gears');
506INSERT INTO Comment VALUES(15,'Pedal feels too weak');
507INSERT INTO Comment VALUES(18,'Seat feels unstable');
508INSERT INTO Comment VALUES(11,'Bikes feels premium. Good Job!');
509GO
510
511create table Fault (
512	FBID		BIGINT,
513	FaultCode	INT			NOT NULL,
514	MaintID		BIGINT		NULL,
515	BikeID		BIGINT		NOT NULL,
516	CONSTRAINT PK_FAULT PRIMARY KEY(FBID),
517	CONSTRAINT FK_FAULT_FEEDBACK FOREIGN KEY(FBID) REFERENCES Feedback(FBID),
518	CONSTRAINT FK_FAULT_FAULTTYPE FOREIGN KEY(FaultCode) REFERENCES FaultType(FaultCode),
519	CONSTRAINT FK_FAULT_MAINTENANCE FOREIGN KEY(MaintID) REFERENCES Repair(MaintID),
520	CONSTRAINT FK_FAULT_BIKE FOREIGN KEY(BikeID) REFERENCES Bike(BikeID)
521);
522GO
523
524INSERT INTO Fault VALUES(4,1001,1,5);
525INSERT INTO Fault VALUES(6,1011,2,8);
526INSERT INTO Fault VALUES(8,1004,3,1);
527INSERT INTO Fault VALUES(12,1008,4,9);
528INSERT INTO Fault VALUES(13,1009,5,12);
529INSERT INTO Fault VALUES(14,1001,6,15);
530INSERT INTO Fault VALUES(16,1010,7,8);
531INSERT INTO Fault VALUES(17,1013,8,7);
532INSERT INTO Fault VALUES(19,1007,9,4);
533INSERT INTO Fault VALUES(20,1003,10,10);
534INSERT INTO Fault VALUES(21,1011,11,12);
535INSERT INTO Fault VALUES(22,1010,12,15);
536INSERT INTO Fault VALUES(23,1001,13,3);
537INSERT INTO Fault VALUES(24,1006,14,13);
538INSERT INTO Fault VALUES(25,1004,15,8);
539GO
540
541create table Reservation (
542	MemberID			BIGINT,
543	BikeID				BIGINT,
544	ResStatus			VARCHAR(15)	NOT NULL		CHECK (ResStatus IN ('Confirmed', 'Cancelled', 'NoShow')),
545	DateTimeRes			DATETIME	NOT NULL,
546	DateTimeReservedFor	DATETIME	NOT NULL,
547	CONSTRAINT PK_Reservation	PRIMARY KEY (MemberID, BikeID),
548	CONSTRAINT CHK_DateTime		CHECK (DateTimeRes < DateTimeReservedFor),
549	CONSTRAINT FK_RESERVATION_MEMBER FOREIGN KEY(MemberID) REFERENCES Member(MemberID),
550	CONSTRAINT FK_RESERVATION_BIKE FOREIGN KEY(BikeID) REFERENCES Bike(BikeID),
551)
552GO
553INSERT INTO Reservation VALUES(1,8,'Confirmed','2018-03-11 10:56:10','2018-03-11 12:00:00');
554INSERT INTO Reservation VALUES(12,9,'Cancelled','2019-05-11 10:56:10','2019-05-13 8:00:00');
555INSERT INTO Reservation VALUES(7,11,'Confirmed','2018-07-09 22:20:10','2018-07-12 7:15:00');
556INSERT INTO Reservation VALUES(8,10,'Confirmed','2018-08-04 8:00:10','2018-08-04 8:10:00');
557INSERT INTO Reservation VALUES(2,2,'NoShow','2018-11-10 10:00:00','2018-11-15 12:00:00');
558INSERT INTO Reservation VALUES(10,8,'Confirmed','2019-12-05 21:46:24','2019-12-15 15:13:00');
559INSERT INTO Reservation VALUES(1,11,'Confirmed','2018-03-09 09:50:18','2018-03-09 13:15:00');
560INSERT INTO Reservation VALUES(8,15,'NoShow','2018-03-14 22:10:10','2018-03-14 22:30:00');
561INSERT INTO Reservation VALUES(3,3,'Cancelled','2018-08-12 07:09:10','2018-08-14 03:15:00');
562INSERT INTO Reservation VALUES(12,14,'Confirmed','2018-04-01 11:45:00','2018-04-11 12:00:00');
563INSERT INTO Reservation VALUES(15,9,'Confirmed','2019-11-02 23:31:45','2019-11-03 12:00:00');
564INSERT INTO Reservation VALUES(12,3,'Cancelled','2018-12-15 08:28:39','2018-12-17 15:10:00');
565INSERT INTO Reservation VALUES(13,6,'Confirmed','2018-07-24 11:50:00','2018-07-26 13:25:00');
566INSERT INTO Reservation VALUES(14,2,'Confirmed','2019-10-29 17:00:10','2019-10-30 07:14:00');
567INSERT INTO Reservation VALUES(7,1,'Confirmed','2018-12-24 21:30:10','2018-12-24 22:00:00');
568GO
569
570USE NP40Book;
571GO