· 8 years ago · Jan 14, 2018, 06:34 AM
1USE master
2
3--Delete database if it exists--
4
5IF EXISTS(select * from sys.databases where name='BikeRentalSystem')
6DROP DATABASE BikeRentalSystem
7GO
8CREATE DATABASE BikeRentalSystem
9GO
10
11USE BikeRentalSystem
12
13
14--Delete tables if they exist--
15
16if exists(select * from sysobjects where id = object_id('CreditDebitTrans'))
17drop table CreditDebitTrans
18go
19
20if exists(select * from sysobjects where id = object_id('Ride'))
21drop table Ride
22go
23
24if exists(select * from sysobjects where id = object_id('BikeReservation'))
25drop table BikeReservation
26go
27
28if exists(select * from sysobjects where id = object_id('Fault'))
29drop table Fault
30go
31
32if exists(select * from sysobjects where id = object_id('Comment'))
33drop table Comment
34go
35
36if exists(select * from sysobjects where id = object_id('Feedback'))
37drop table Feedback
38go
39
40if exists(select * from sysobjects where id = object_id('Member'))
41drop table Member
42go
43
44if exists(select * from sysobjects where id = object_id('Bike'))
45drop table Bike
46go
47
48if exists(select * from sysobjects where id = object_id('BikeStop'))
49drop table BikeStop
50go
51
52if exists(select * from sysobjects where id = object_id('Promotion'))
53drop table Promotion
54go
55
56if exists(select * from sysobjects where id = object_id('FaultType'))
57drop table FaultType
58go
59
60if exists(select * from sysobjects where id = object_id('MaintItem'))
61drop table MaintItem
62go
63
64if exists(select * from sysobjects where id = object_id('MaintType'))
65drop table MaintType
66go
67
68if exists(select * from sysobjects where id = object_id('Repair'))
69drop table Repair
70go
71
72if exists(select * from sysobjects where id = object_id('Maintenance'))
73drop table Maintenance
74go
75
76if exists(select * from sysobjects where id = object_id('CreditCard'))
77drop table CreditCard
78go
79
80
81--Creating tables--
82
83Create table Bike
84(
85 BikeID char(4) NOT NULL,
86 Rate smallint NOT NULL,
87 Status varchar(2) NOT NULL CHECK(Status IN ('W', 'NR', 'R'))
88 CONSTRAINT Pk_Bike PRIMARY KEY(BikeID)
89)
90
91Create table Member
92(
93 MemberID char(6) NOT NULL,
94 Name varchar(30) NOT NULL,
95 Email varchar(40) NULL,
96 Phone char(9) NOT NULL,
97 RewardPts smallint NOT NULL DEFAULT(0),
98 Deposit money NOT NULL DEFAULT(50),
99 CreditBalance money NOT NULL DEFAULT(0),
100 DateJoin date NOT NULL DEFAULT(GETDATE()),
101
102 CONSTRAINT Pk_Member PRIMARY KEY(MemberID),
103 --CONSTRAINT Chk_Phone CHECK(Phone like '^[689]\d{8}$'),
104 --CONSTRAINT Chk_Email CHECK(Email like '^[\w\d]+@\D+(\.\D+)?(.com)(\.\D+)?$')
105)
106
107
108Create table BikeReservation
109(
110 ReservationID char(4) NOT NULL,
111 MemberID char(6) NOT NULL,
112 BikeID char(4) NOT NULL,
113 ResStatus char(1) NOT NULL,
114 DateTimeRes datetime NOT NULL DEFAULT(GETDATE()),
115 DateTimeReservedFor datetime NOT NULL,
116
117 CONSTRAINT Pk_ReservationID PRIMARY KEY(ReservationID),
118 CONSTRAINT Fk_BikeReservation_MemberID FOREIGN KEY(MemberID) REFERENCES Member(MemberID),
119 CONSTRAINT Fk_BikeReservation_BikeID FOREIGN KEY(BikeID) REFERENCES Bike(BikeID),
120 CONSTRAINT Chk_ResStatus CHECK(ResStatus IN ('O', 'F')),
121 CONSTRAINT Chk_DateTimeReservedFor CHECK(DateTimeReservedFor >= DateTimeRes)
122
123)
124
125Create table BikeStop
126(
127 BikeStopID char(2) NOT NULL,
128 StopLat decimal(7,6) NOT NULL,
129 StopLong decimal(9,6) NOT NULL,
130 StopRadius smallint NOT NULL,
131
132 CONSTRAINT Pk_BikeStop PRIMARY KEY(BikeStopID),
133 CONSTRAINT Chk_StopRadius CHECK(StopRadius between 5 and 25)
134)
135
136Create table Promotion
137(
138 PromoCode varchar(10) NOT NULL,
139 PromoDesc varchar(50) NULL,
140 PromoRate decimal(3,2) NOT NULL,
141 StartDate date NOT NULL,
142 EndDate date NOT NULL,
143
144 CONSTRAINT Pk_Promotion PRIMARY KEY(PromoCode),
145 CONSTRAINT Chk_StartDate CHECK(StartDate <= GETDATE()),
146 CONSTRAINT Chk_EndDate CHECK(EndDate >= StartDate)
147)
148Create table Ride
149(
150 RideID char(6) NOT NULL,
151 DateTimeStart datetime NOT NULL,
152 DateTimeEnd datetime NULL,
153 RidePts Int NOT NULL DEFAULT(0),
154 Distance Decimal(5,3) NULL,
155 PtsRedeemed Int NOT NULL DEFAULT(0),
156 Cost smallmoney NULL,
157 MemberID char(6) NULL,
158 BikeID char(4) NULL,
159 BikeStopID char(2) NULL,
160 PromoCode varchar(10) NULL,
161
162 CONSTRAINT Pk_Ride PRIMARY KEY(RideID),
163 CONSTRAINT Fk_Ride_MemberID FOREIGN KEY(MemberID) REFERENCES Member(MemberID),
164 CONSTRAINT Fk_Ride_BikeID FOREIGN KEY(BikeID) REFERENCES Bike(BikeID),
165 CONSTRAINT Fk_Ride_BikeStopID FOREIGN KEY(BikeStopID) REFERENCES BikeStop(BikeStopID),
166 CONSTRAINT Fk_Ride_PromoCode FOREIGN KEY(PromoCode) REFERENCES Promotion(PromoCode),
167
168 CONSTRAINT Chk_PtsRedeemed CHECK(PtsRedeemed % 50 = 0)
169)
170
171Create table Feedback
172(
173 FBID char(5) NOT NULL,
174 FBDateTime datetime NOT NULL DEFAULT(GETDATE()),
175 MemberID char(6) NOT NULL,
176
177 CONSTRAINT Pk_Feedback PRIMARY KEY(FBID),
178 CONSTRAINT Fk_Feedback_MemberID FOREIGN KEY(MemberID) REFERENCES Member(MemberID)
179)
180
181
182Create table FaultType
183(
184 FaultCode char(4) NOT NULL,
185 FaultDesc varchar(500) NOT NULL,
186
187 CONSTRAINT Pk_FaultType PRIMARY KEY(FaultCode),
188)
189
190Create table Maintenance
191(
192 MaintID char(5) NOT NULL,
193 MaintDate date NOT NULL,
194 BikeID char(4) NOT NULL,
195
196 CONSTRAINT Pk_Maintenance PRIMARY KEY(MaintID),
197 CONSTRAINT Chk_MaintDate CHECK(MaintDate <= GETDATE()),
198 CONSTRAINT Fk_Maintenance_BikeID FOREIGN KEY(BikeID) REFERENCES Bike(BikeID)
199)
200
201
202Create table Repair
203(
204 MaintID char(5) NOT NULL,
205 MaintDate date NOT NULL,
206 BikeID char(4) NOT NULL,
207
208 CONSTRAINT Pk_Repair PRIMARY KEY(MaintID),
209 CONSTRAINT Fk_Repair_MaintID FOREIGN KEY(MaintID) REFERENCES Maintenance(MaintID),
210 CONSTRAINT Chk_Repair_MaintDate CHECK(MaintDate <= GETDATE()),
211 CONSTRAINT Fk_Repair_BikeID FOREIGN KEY(BikeID) REFERENCES Bike(BikeID)
212)
213
214Create table MaintType
215(
216 TypeCode char(5) NOT NULL,
217 TypeDesc varchar(50) NOT NULL,
218
219 CONSTRAINT Pk_MaintType PRIMARY KEY (TypeCode)
220)
221
222Create table MaintItem
223(
224 SNo char(2) NOT NULL,
225 MaintID char(5) NOT NULL,
226 MaintDesc varchar(100) NOT NULL,
227 TypeCode char(5) NULL,
228
229 CONSTRAINT Pk_MaintItem PRIMARY KEY (SNo, MaintID),
230 CONSTRAINT Fk_MaintItem_MaintID FOREIGN KEY (MaintID) REFERENCES Maintenance (MaintID) ON DELETE CASCADE,
231 CONSTRAINT Fk_MaintItem_TypeCode FOREIGN KEY (TypeCode) REFERENCES MaintType (TypeCode)
232)
233
234Create table Fault
235(
236 FBID char(5) NOT NULL,
237 FBDateTime varchar(40) NOT NULL,
238 FaultCat varchar(15) NOT NULL,
239 MemberID char(6) NOT NULL,
240 FaultCode char(4) NULL,
241 BikeID char(4) NOT NULL,
242 MaintID char(5) NOT NULL,
243
244 CONSTRAINT Pk_Fault PRIMARY KEY (FBID),
245 CONSTRAINT FK_Fault_FBID FOREIGN KEY(FBID) REFERENCES Feedback(FBID),
246 CONSTRAINT Fk_Fault_MemberID FOREIGN KEY (MemberID) REFERENCES Member (MemberID),
247 CONSTRAINT Fk_Fault_FaultCode FOREIGN KEY(FaultCode) REFERENCES FaultType(FaultCode),
248 CONSTRAINT Fk_Fault_BikeID FOREIGN KEY(BikeID) REFERENCES Bike(BikeID),
249 CONSTRAINT Fk_Fault_MaintID FOREIGN KEY(MaintID) REFERENCES Maintenance(MaintID),
250 CONSTRAINT Chk_Fault_FaultCat CHECK(FaultCat IN ('Tire', 'Pedal', 'Seat', 'Basket', 'Lock', 'Frame', 'Others'))
251)
252
253Create table Comment
254(
255 FBID char(5) NOT NULL,
256 FBDateTime datetime NOT NULL,
257 FBMessage varchar(1000) NULL,
258 MemberID char(6) NOT NULL,
259
260 CONSTRAINT Pk_Comment PRIMARY KEY(FBID),
261 CONSTRAINT Fk_Comment_FBID FOREIGN KEY(FBID) REFERENCES Feedback(FBID),
262 CONSTRAINT Fk_Comment_MemberID FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
263 CONSTRAINT Chk_FBDateTime CHECK(FBDateTime <= GETDATE())
264)
265
266Create table CreditCard
267(
268 CardNo char(6) NOT NULL,
269 CardType varchar(30) NOT NULL,
270 ExpiryDate date NOT NULL,
271 CVV char(3) NOT NULL,
272 MemberID char(6) NOT NULL
273 CONSTRAINT Pk_CreditCard PRIMARY KEY(CardNo),
274 CONSTRAINT Chk_ExpiryDate CHECK(ExpiryDate >= GETDATE()),
275 CONSTRAINT Fk_MemberID FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
276)
277
278Create table CreditDebitTrans
279(
280 TransID char(6) NOT NULL,
281 TransAmt smallmoney NOT NULL,
282 TransType varchar(6) NOT NULL,
283 TransDateTime datetime NOT NULL default(GETDATE()),
284 CardNo char(6) NOT NULL,
285 RideID char(6) NOT NULL,
286
287 CONSTRAINT Pk_CreditDebitTrans PRIMARY KEY(TransID),
288 CONSTRAINT Fk_CreditDebitTrans_CardNo FOREIGN KEY(CardNo) REFERENCES CreditCard(CardNo),
289 CONSTRAINT Fk_CreditDebitTrans_RideID FOREIGN KEY(RideID) REFERENCES Ride(RideID),
290 CONSTRAINT Chk_TransType CHECK(TransType in ('Credit','Debit'))
291)
292
293
294
295--Inserting values--
296
297insert into Bike(BikeID, Rate, Status) VALUES ('1AAA', 0.5, 'W')
298insert into Bike(BikeID, Rate, Status) VALUES ('2BBB', 0.5, 'W')
299insert into Bike(BikeID, Rate, Status) VALUES ('3CCC', 0.5, 'W')
300insert into Bike(BikeID, Rate, Status) VALUES ('4DDD', 0.5, 'R')
301insert into Bike(BikeID, Rate, Status) VALUES ('5EEE', 0.5, 'W')
302insert into Bike(BikeID, Rate, Status) VALUES ('6FFF', 0.5, 'W')
303insert into Bike(BikeID, Rate, Status) VALUES ('7GGG', 0.5, 'NR')
304insert into Bike(BikeID, Rate, Status) VALUES ('8HHH', 0.5, 'W')
305insert into Bike(BikeID, Rate, Status) VALUES ('9III', 0.5, 'W')
306insert into Bike(BikeID, Rate, Status) VALUES ('JJJ1', 0.5, 'W')
307insert into Bike(BikeID, Rate, Status) VALUES ('KKK2', 0.5, 'W')
308insert into Bike(BikeID, Rate, Status) VALUES ('LLL3', 0.5, 'NR')
309insert into Bike(BikeID, Rate, Status) VALUES ('MMM4', 0.5, 'W')
310insert into Bike(BikeID, Rate, Status) VALUES ('NNN5', 0.5, 'W')
311insert into Bike(BikeID, Rate, Status) VALUES ('OOO6', 0.5, 'W')
312
313insert into Member (MemberID, Name, Email, Phone, Deposit, CreditBalance, DateJoin) values ('11111B','John Bruce', 'jhonbruce@gmail.com', '90000001', 100, 200, '2017-08-01')
314insert into Member values ('22222C','Tom Harrison', 'tomharrison@gmail.com', '90000002', 15, 60, 50, '2017-01-03')
315insert into Member (MemberID, Name, Email, Phone, Deposit, CreditBalance, DateJoin) values ('33333D','Zachary Tan', 'zacharytan@gmail.com', '90000003', 80, 200, '2016-12-01')
316insert into Member (MemberID, Name, Email, Phone, RewardPts, CreditBalance, DateJoin) values ('44444E','Tammy Lee', 'tammylee@gmail.com', '90000004', 24, 200, '2016-05-01')
317insert into Member values ('55555F','Monica Cheng', 'monicacheng@gmail.com', '90000005', 29, 140, 350, '2015-10-15')
318insert into Member values ('66666G','Javier Tang', 'javiertang@gmail.com', '90000006', 33, 120, 400, '2015-06-01')
319insert into Member (MemberID, Name, Email, Phone, RewardPts) values ('77777H','Shaun Jacob', 'shaunjacob@gmail.com', '90000007', 38)
320insert into Member values ('88888I','William Johnson', 'willianjohnson@gmail.com', '90000008', 42, 150, 25, '2014-09-09')
321insert into Member (MemberID, Name, Email, Phone, RewardPts, Deposit, DateJoin) values ('99999J','James Bruce', 'jamesbruce@gmail.com', '90000009', 45, 170, '2013-06-09')
322insert into Member (MemberID, Name, Email, Phone, Deposit, DateJoin) values ('AAAAA1','Wayne', 'xxxwayne999@gmail.com', '80000000', 200, '2012-07-22')
323insert into Member (MemberID, Name, Email, Phone, RewardPts) values ('BBBBB2','Jennifer Pang', 'jenniferpang@gmail.com', '80000001', 51)
324insert into Member (MemberID, Name, Email, Phone, RewardPts, DateJoin) values ('CCCCC3','Ethan Bradberry', 'ethanbradberry@gmail.com', '80000002', 55, '2011-11-11')
325insert into Member values ('DDDDD4','Daniel Lim', 'daniellim@gmail.com', '80000003', 69, 170, 46, '2010-10-10')
326insert into Member (MemberID, Name, Email, Phone, CreditBalance, DateJoin) values ('EEEEE5','Lucas Paul', 'lucaspaul@gmail.com', '80000004', 100, '2009-09-09')
327insert into Member values ('FFFFF6', 'Jack Lee', 'jacklee99@yahoo.com', '80000005', 1, 55, 31, '2008-08-08')
328insert into Member values ('GGGGG7', 'Mary Tan', 'marytan@yahoo.com', '80000006', 10, 60, 12, '2008-10-18')
329insert into Member values ('HHHHH8', 'Tom Everson', 'tomeve@yahoo.com', '80000007', 12, 56, 15, '2008-05-28')
330insert into Member values ('IIIII9', 'Claudia Koh', 'claudia13@yahoo.com', '80000008', 21, 53, 35, '2008-09-17')
331insert into Member (MemberID, Name, Email, Phone, RewardPts, Deposit, DateJoin) values ('JJJJJ1', 'Nicholas Paris', 'parisnicalo@yahoo.com', '80000009', 34, 55, '2016-02-25')
332insert into Member (MemberID, Name, Email, Phone, RewardPts, CreditBalance, DateJoin) values ('KKKKK2', 'Jackson Yi', 'yi08@yahoo.com', '80000010', 56, 2, '2016-03-26')
333
334insert into BikeReservation values ('0001','55555F', '4DDD', 'F', '2011-04-04 04:04:04', '2011-04-04 04:34:04')
335insert into BikeReservation values ('0002','44444E', '5EEE', 'F', '2011-05-05 05:05:05', '2011-05-05 05:15:05')
336insert into BikeReservation values ('0003','77777H', '3CCC', 'F', '2010-03-03 03:03:03', '2010-03-03 03:23:03')
337insert into BikeReservation values ('0004','88888I', '9III', 'O', '2018-01-11 09:09:09', '2018-01-11 17:10:09')
338insert into BikeReservation values ('0005','CCCCC3', 'LLL3', 'O', '2018-01-10 11:11:11', '2018-01-10 11:15:20')
339insert into BikeReservation values ('0006','FFFFF6', '1AAA', 'F', '2008-01-01 01:01:01', '2008-01-01 01:11:01')
340insert into BikeReservation values ('0007','AAAAA1', 'NNN5', 'F', '2017-02-03 13:13:13', '2017-02-03 13:23:13')
341insert into BikeReservation values ('0008','EEEEE5', 'KKK2', 'F', '2014-11-11 10:10:10', '2014-11-11 10:20:10')
342insert into BikeReservation values ('0009','DDDDD4', 'OOO6', 'F', '2017-03-04 14:14:14', '2017-03-04 14:54:14')
343insert into BikeReservation values ('0010','55555F', '6FFF', 'O', '2018-01-10 06:06:06', '2018-01-11 18:16:06')
344insert into BikeReservation values ('0011','99999J', 'JJJ1', 'F', '2014-10-10 09:09:10', '2014-10-10 09:09:20')
345insert into BikeReservation values ('0012','66666G', '7GGG', 'F', '2013-07-07 07:07:07', '2013-07-07 07:27:07')
346insert into BikeReservation values ('0013','77777H', '8HHH', 'F', '2013-08-08 08:08:08', '2013-08-08 08:28:08')
347insert into BikeReservation values ('0014','BBBBB2', 'MMM4', 'F', '2017-01-02 12:12:12', '2017-01-02 12:15:12')
348insert into BikeReservation values ('0015','11111B', '2BBB', 'F', '2009-02-02 02:02:02', '2009-02-02 02:12:02')
349
350insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AA',1.335512, 103.962087, 25)
351insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AB',1.340790, 103.950886, 20)
352insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AC',1.345273, 103.939234, 15)
353insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AD',1.334727, 103.764290, 10)
354insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AE',1.359096, 103.893723, 5)
355insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AF',1.366304, 103.947624, 25)
356insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AG',1.446274, 103.823685, 10)
357insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AH',1.394791, 103.906769, 15)
358insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AI',1.312760, 103.767380, 25)
359insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AJ',1.327176, 103.688416, 25)
360insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AK',1.444214, 103.788666, 20)
361insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AL',1.392389, 103.904709, 10)
362insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AM',1.381405, 103.890633, 5)
363insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AN',1.377630, 103.879646, 5)
364insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AO',1.328892, 103.744720, 25)
365
366insert into Promotion values ('Promo01', 'Get 5% off of total price of the ride', 0.05, '2008-01-01', '2008-03-04')
367insert into Promotion values ('Promo02', 'Get 25% off of total price of the ride', 0.25, '2008-04-11', '2008-06-19')
368insert into Promotion values ('Promo03', 'Get 30% off of total price of the ride', 0.30, '2008-08-01', '2018-12-09')
369insert into Promotion values ('Promo04', 'Get 20% off of total price of the ride', 0.20, '2009-02-13', '2009-04-01')
370insert into Promotion values ('Promo05', 'Get 10% off of total price of the ride', 0.10, '2009-06-01', '2009-08-20')
371insert into Promotion values ('Promo06', 'Get 30% off of total price of the ride', 0.30, '2010-01-01', '2010-02-01')
372insert into Promotion values ('Promo07', 'Get 60% off of total price of the ride', 0.60, '2010-02-28', '2010-06-30')
373insert into Promotion values ('Promo08', 'Get 80% off of total price of the ride', 0.80, '2011-08-21', '2011-09-01')
374insert into Promotion values ('Promo09', 'Get 90% off of total price of the ride', 0.90, '2011-08-10', '2011-12-14')
375insert into Promotion values ('Promo10', 'Get 10% off of total price of the ride', 0.10, '2011-10-15', '2011-12-16')
376insert into Promotion values ('Promo11', 'Get 15% off of total price of the ride', 0.15, '2012-06-23', '2012-12-31')
377insert into Promotion values ('Promo12', 'Get 33% off of total price of the ride', 0.33, '2013-02-11', '2013-04-01')
378insert into Promotion values ('Promo13', 'Get 25% off of total price of the ride', 0.25, '2014-01-05', '2014-08-07')
379insert into Promotion values ('Promo14', 'Get 55% off of total price of the ride', 0.55, '2015-11-03', '2016-03-18')
380insert into Promotion values ('Promo15', 'Get 75% off of total price of the ride', 0.75, '2017-06-20', '2018-01-02')
381
382INSERT INTO Ride VALUES('1AAAAA', '2008-01-01 01:11:01', '2008-01-01 01:31:01', 20, 5.00, 0, 0.50, 'FFFFF6', '1AAA', 'AA', 'Promo01')
383INSERT INTO Ride VALUES('1BBBBB', '2008-03-04 13:01:01', '2008-03-04 13:31:01', 30, 6.50, 0, 0.50, '11111B', '2BBB', 'AC', 'Promo02')
384INSERT INTO Ride VALUES('1CCCCC', '2009-04-05 13:30:00', '2009-04-05 14:10:00', 40, 7.00, 0, 0.50, 'IIIII9', '3CCC', 'AD', Null)
385INSERT INTO Ride VALUES('1DDDDD', '2010-05-06 14:20:00', '2010-05-06 14:10:00', 50, 8.50, 50, 0.50, '33333D', '4DDD', 'AE', 'Promo07')
386INSERT INTO Ride VALUES('1EEEEE', '2010-09-01 16:35:00', '2010-09-01 17:30:00', 55, 9.00, 50, 0.50, '44444E', '1AAA', 'AG', 'Promo08')
387INSERT INTO Ride VALUES('1FFFFF', '2011-04-04 04:34:04', '2011-04-04 05:34:04', 60, 10.00, 50, 0.50, '55555F', '5EEE', 'AH', 'Promo10')
388INSERT INTO Ride VALUES('1GGGGG', '2013-08-08 08:28:08', '2013-08-08 08:43:08', 15, 4.50, 0, 0.50, 'GGGGG7', '7GGG', 'AJ', Null)
389INSERT INTO Ride VALUES('1HHHHH', '2011-07-07 09:00:00', '2011-07-07 09:10:00', 10, 4.00, 0, 0.50, '77777H', '9III', 'AK', 'Promo08')
390INSERT INTO Ride VALUES('1IIIII', '2012-06-06 06:16:06', '2012-06-06 01:46:06', 30, 6.35, 0, 0.50, '88888I', 'LLL3', 'AM', 'Promo11')
391INSERT INTO Ride VALUES('1JJJJJ', '2014-10-10 09:09:20', '2014-10-10 09:49:46', 40, 7.10, 0, 0.50, '99999J', 'JJJ1', 'AO', 'Promo08')
392INSERT INTO Ride VALUES('1KKKKK', '2013-01-01 01:01:01', '2013-01-01 02:01:01', 60, 10.10, 50, 0.50, 'AAAAA1', 'OOO6', 'AN', 'Promo09')
393INSERT INTO Ride VALUES('1LLLLL', '2014-01-01 01:01:01', '2014-01-01 01:51:01', 50, 9.45, 50, 0.50, 'BBBBB2', 'NNN5', 'AM', 'Promo13')
394INSERT INTO Ride VALUES('1MMMMM', '2015-01-01 01:01:01', '2015-01-01 03:01:01', 120, 21.10, 100, 1.40, 'HHHHH8', '8HHH', 'AA', Null)
395INSERT INTO Ride VALUES('1NNNNN', '2017-03-04 14:54:14', '2017-03-04 15:19:14', 25, 6.00, 0, 0.50, 'DDDDD4', 'OOO6', 'AB', Null)
396INSERT INTO Ride VALUES('1OOOOO', '2014-11-11 10:20:10', '2014-11-11 10:30:10', 10, 4.25, 0, 0.50, 'EEEEE5', 'KKK2', 'AC', Null)
397INSERT INTO Ride VALUES('1PPPPP', '2013-01-01 01:01:01', '2013-01-01 02:01:01', 60, 10.10, 50, 0.50, 'AAAAA1', 'OOO6', 'AN', Null)
398INSERT INTO Ride VALUES('1QQQQQ', '2014-01-01 01:01:01', '2014-01-01 01:51:01', 50, 9.45, 50, 0.50, 'GGGGG7', 'NNN5', 'AM', 'Promo13')
399
400INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00001', '2017-03-04 00:14:14', 'FFFFF6')
401INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00002', '2010-01-07 12:59:12', '22222C')
402INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00003', '2016-12-14 02:49:45', '11111B')
403INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00004', '2014-02-24 18:17:10', '33333D')
404INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00005', '2015-02-15 19:37:20', 'FFFFF6')
405INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00006', '2013-11-25 20:18:30', 'AAAAA1')
406INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00007', '2017-05-05 11:58:40', '44444E')
407INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00008', '2012-07-03 22:28:50', '66666G')
408INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00009', '2012-04-30 19:54:12', '77777H')
409INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00010', '2011-03-29 10:34:14', 'FFFFF6')
410INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00011', '2017-11-19 03:45:17', '55555F')
411INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00012', '2016-05-01 04:12:56', '55555F')
412INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00013', '2011-01-03 11:27:45', '11111B')
413INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00014', '2015-01-14 07:17:34', 'EEEEE5')
414INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00015', '2012-04-02 09:09:21', 'BBBBB2')
415INSERT INTO Feedback VALUES('10001','2017-02-10','HHHHH8')
416INSERT INTO Feedback VALUES('20002','2017-05-13','KKKKK2')
417INSERT INTO Feedback VALUES('30003','2016-03-26','GGGGG7')
418INSERT INTO Feedback VALUES('40004','2016-07-17','66666G')
419INSERT INTO Feedback VALUES('50005','2015-07-06','AAAAA1')
420INSERT INTO Feedback VALUES('60006','2015-10-09','HHHHH8')
421INSERT INTO Feedback VALUES('70007','2014-01-19','CCCCC3')
422INSERT INTO Feedback VALUES('80008','2014-04-03','99999J')
423INSERT INTO Feedback VALUES('90009','2013-02-20','JJJJJ1')
424INSERT INTO Feedback VALUES('11011','2013-07-21','33333D')
425INSERT INTO Feedback VALUES('22022','2012-08-26','BBBBB2')
426INSERT INTO Feedback VALUES('33033','2012-02-14','IIIII9')
427INSERT INTO Feedback VALUES('44044','2011-03-18','44444E')
428INSERT INTO Feedback VALUES('55055','2011-06-22','IIIII9')
429INSERT INTO Feedback VALUES('66066','2010-04-22','GGGGG7')
430
431INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00001', '2017-03-04 00:14:14', 'Message01', 'FFFFF6')
432INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00002', '2010-01-07 12:59:12', 'Message02', '22222C')
433INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00003', '2016-12-14 02:49:45', 'Message03', '11111B')
434INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00004', '2014-02-24 18:17:10', 'Message04', '33333D')
435INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00005', '2015-02-15 19:37:20', 'Message05', 'FFFFF6')
436INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00006', '2013-11-25 20:18:30', 'Message06', 'AAAAA1')
437INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00007', '2017-08-05 11:58:40', 'Message07', '44444E')
438INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00008', '2012-07-03 22:28:50', 'Message08', '66666G')
439INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00009', '2012-04-30 19:54:12', 'Message09', '77777H')
440INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00010', '2011-03-29 10:34:14', 'Message10', 'FFFFF6')
441INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00011', '2017-11-19 03:45:17', 'Message11', '55555F')
442INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00012', '2016-12-01 04:12:56', 'Message12', '55555F')
443INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00013', '2011-01-03 11:27:45', 'Message13', '11111B')
444INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00014', '2015-01-14 07:17:34', 'Message14', 'EEEEE5')
445INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00015', '2012-04-02 09:09:21', 'Message15', 'BBBBB2')
446
447INSERT INTO FaultType VALUES('TI-1','Tires are punctured')
448INSERT INTO FaultType VALUES('TI-2','Tires are worn out')
449INSERT INTO FaultType VALUES('TI-3','Tires misshaped')
450INSERT INTO FaultType VALUES('BA-1','A part of the basket is broken off')
451INSERT INTO FaultType VALUES('BA-2','Basket has been misshaped by force')
452INSERT INTO FaultType VALUES('BA-3','Basket is almost / already detached from the rest of the bike' )
453INSERT INTO FaultType VALUES('SE-1','Seat covering is torn off (or a portion of it)')
454INSERT INTO FaultType VALUES('SE-2','Seat is physically damaged')
455INSERT INTO FaultType VALUES('LO-1','Lock is broken, completely insecure')
456INSERT INTO FaultType VALUES('LO-2','Lock does not work')
457INSERT INTO FaultType VALUES('PE-1','Pedal(s) is / are loose')
458INSERT INTO FaultType VALUES('PE-2','Pedal(s) is / are missing')
459INSERT INTO FaultType VALUES('FR-1','Frame is dented in')
460INSERT INTO FaultType VALUES('FR-2','Paint on frame has been scraped off')
461INSERT INTO FaultType VALUES('OO-1','Bicycle is unlocked')
462
463INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0001', '2008-01-01', '1AAA')
464INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0002', '2009-02-02', '2BBB')
465INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0003', '2010-03-03', '3CCC')
466INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0004', '2010-04-04', '4DDD')
467INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0005', '2011-05-05', '5EEE')
468INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0006', '2012-06-06', '6FFF')
469INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0007', '2013-07-07', '1AAA')
470INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0008', '2014-08-08', '9III')
471INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0009', '2015-09-09', 'KKK2')
472INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0010', '2016-10-10', '1AAA')
473INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0011', '2017-11-11', 'OOO6')
474INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0012', '2010-12-12', 'KKK2')
475INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0013', '2008-01-01', 'NNN5')
476INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0014', '2011-02-02', 'MMM4')
477INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0015', '2012-03-03', '8HHH')
478
479INSERT INTO Fault VALUES('10001','2017-02-10','tire', 'HHHHH8','TI-1','OOO6', 'A0015')
480INSERT INTO Fault VALUES('20002','2017-05-13','basket', 'KKKKK2','BA-1','NNN5', 'A0014')
481INSERT INTO Fault VALUES('30003','2016-03-26', 'lock', 'GGGGG7','TI-2','MMM4', 'A0013')
482INSERT INTO Fault VALUES('40004','2016-07-17', 'others', '66666G','SE-1','LLL3', 'A0012')
483INSERT INTO Fault VALUES('50005','2015-07-06', 'pedal', 'AAAAA1','LO-1','KKK2', 'A0011')
484INSERT INTO Fault VALUES('60006','2015-10-09', 'frame', 'HHHHH8','LO-2','JJJ1', 'A0010')
485INSERT INTO Fault VALUES('70007','2014-01-19', 'basket', 'CCCCC3','BA-2','9III', 'A0009')
486INSERT INTO Fault VALUES('80008','2014-04-03', 'tire', '99999J','PE-1','8HHH', 'A0008')
487INSERT INTO Fault VALUES('90009','2013-02-20', 'frame', 'JJJJJ1','OO-1','7GGG', 'A0007')
488INSERT INTO Fault VALUES('11011','2013-07-21', 'basket', '33333D','SE-2','6FFF', 'A0006')
489INSERT INTO Fault VALUES('22022','2012-08-26', 'pedal', 'BBBBB2','TI-3','5EEE', 'A0005')
490INSERT INTO Fault VALUES('33033','2012-02-14', 'tire', 'IIIII9','FR-2','4DDD', 'A0004')
491INSERT INTO Fault VALUES('44044','2011-03-18', 'others', '44444E','PE-2','3CCC', 'A0003')
492INSERT INTO Fault VALUES('55055','2011-06-22', 'lock', 'IIIII9','BA-3','2BBB', 'A0002')
493INSERT INTO Fault VALUES('66066','2010-04-22', 'basket', 'GGGGG7','FR-1','1AAA', 'A0001')
494
495INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA01', 'TDesc01')
496INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA02', 'TDesc02')
497INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA03', 'TDesc03')
498INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA04', 'TDesc04')
499INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA05', 'TDesc05')
500INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA06', 'TDesc06')
501INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA07', 'TDesc07')
502INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA08', 'TDesc08')
503INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA09', 'TDesc09')
504INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA10', 'TDesc10')
505INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA11', 'TDesc11')
506INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA12', 'TDesc12')
507INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA13', 'TDesc13')
508INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA14', 'TDesc14')
509INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA15', 'TDesc15')
510
511INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('01', 'A0015', 'Mdesc01', 'AAA15')
512INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('02', 'A0014', 'Mdesc04', 'AAA14')
513INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('03', 'A0013', 'Mdesc05', 'AAA13')
514INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('04', 'A0012', 'Mdesc06', 'AAA12')
515INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('05', 'A0011', 'Mdesc07', 'AAA11')
516INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('06', 'A0010', 'Mdesc02', 'AAA10')
517INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('07', 'A0009', 'Mdesc03', 'AAA09')
518INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('08', 'A0008', 'Mdesc02', 'AAA08')
519INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('09', 'A0007', 'Mdesc01', 'AAA07')
520INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('10', 'A0006', 'Mdesc05', 'AAA06')
521INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('11', 'A0005', 'Mdesc08', 'AAA05')
522INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('12', 'A0004', 'Mdesc10', 'AAA04')
523INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('13', 'A0003', 'Mdesc06', 'AAA03')
524INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('14', 'A0002', 'Mdesc04', 'AAA02')
525INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('15', 'A0001', 'Mdesc07', 'AAA01')
526
527INSERT INTO CreditCard VALUES('Card01', 'Visa', '2019-01-01', '000', 'FFFFF6')
528INSERT INTO CreditCard VALUES('Card02', 'Master', '2020-01-01', '001', '66666G')
529INSERT INTO CreditCard VALUES('Card03', 'Diners', '2021-01-01', '002', 'FFFFF6')
530INSERT INTO CreditCard VALUES('Card04', 'Master', '2021-01-01', '003', 'BBBBB2')
531INSERT INTO CreditCard VALUES('Card05', 'Express', '2022-01-01', '004', '22222C')
532INSERT INTO CreditCard VALUES('Card06', 'Chase', '2022-01-01', '005', '77777H')
533INSERT INTO CreditCard VALUES('Card07', 'Visa', '2022-01-01', '006', 'EEEEE5')
534INSERT INTO CreditCard VALUES('Card08', 'Visa', '2022-01-01', '007', '11111B')
535INSERT INTO CreditCard VALUES('Card09', 'Diners', '2022-01-01', '008', '55555F')
536INSERT INTO CreditCard VALUES('Card10', 'Express', '2022-01-01', '009', '11111B')
537INSERT INTO CreditCard VALUES('Card11', 'Visa', '2022-01-01', '010', '55555F')
538INSERT INTO CreditCard VALUES('Card12', 'Visa', '2022-01-01', '011', '44444E')
539INSERT INTO CreditCard VALUES('Card13', 'Visa', '2019-01-01', '012', 'FFFFF6')
540INSERT INTO CreditCard VALUES('Card14', 'Master', '2019-01-01', '013', '33333D')
541INSERT INTO CreditCard VALUES('Card15', 'Chase', '2019-01-01', '014', 'AAAAA1')
542
543INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0001', '2008-01-01', '1AAA')
544INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0002', '2009-02-02', '2BBB')
545INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0003', '2010-03-03', '3CCC')
546INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0004', '2010-04-04', '4DDD')
547INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0005', '2011-05-05', '5EEE')
548INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0006', '2012-06-06', '6FFF')
549INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0007', '2013-07-07', '1AAA')
550INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0008', '2014-08-08', '9III')
551INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0009', '2015-09-09', 'KKK2')
552INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0010', '2016-10-10', '1AAA')
553INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0011', '2017-11-11', 'OOO6')
554INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0012', '2010-12-12', 'KKK2')
555INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0013', '2008-01-01', 'NNN5')
556INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0014', '2011-02-02', 'MMM4')
557INSERT INTO Repair(MaintID, MaintDate, BikeID) VALUES ('A0015', '2012-03-03', '8HHH')
558
559INSERT INTO CreditDebitTrans VALUES ('A00001', '1.50', 'Debit', '2008-01-01 01:31:01', 'card01', '1AAAAA')
560INSERT INTO CreditDebitTrans VALUES ('A00002', '1.00', 'Credit', '2008-03-04 13:31:01', 'card11', '1BBBBB')
561INSERT INTO CreditDebitTrans VALUES ('A00003', '0.50', 'Debit', '2009-04-05 14:10:00', 'card03', '1CCCCC')
562INSERT INTO CreditDebitTrans VALUES ('A00004', '2.00', 'Credit', '2010-05-06 14:10:00', 'card08', '1DDDDD')
563INSERT INTO CreditDebitTrans VALUES ('A00005', '1.50', 'Debit', '2010-09-01 17:30:00', 'card02', '1EEEEE')
564INSERT INTO CreditDebitTrans VALUES ('A00006', '1.00', 'Credit', '2011-04-04 05:34:04', 'card10', '1FFFFF')
565INSERT INTO CreditDebitTrans VALUES ('A00007', '1.50', 'Debit', '2013-08-08 08:43:08', 'card01', '1GGGGG')
566INSERT INTO CreditDebitTrans VALUES ('A00008', '2.00', 'Credit', '2011-07-07 09:10:00', 'card05', '1HHHHH')
567INSERT INTO CreditDebitTrans VALUES ('A00009', '1.00', 'Debit', '2012-06-06 01:46:06', 'card06', '1IIIII')
568INSERT INTO CreditDebitTrans VALUES ('A00010', '0.50', 'Credit', '2014-10-10 09:49:46', 'card07', '1JJJJJ')
569INSERT INTO CreditDebitTrans VALUES ('A00011', '0.50', 'Debit', '2013-01-01 02:01:01', 'card08', '1KKKKK')
570INSERT INTO CreditDebitTrans VALUES ('A00012', '0.50', 'Credit', '2013-01-01 02:01:01', 'card09', '1LLLLL')
571INSERT INTO CreditDebitTrans VALUES ('A00013', '2.50', 'Debit', '2015-01-01 03:01:01', 'card10', '1MMMMM')
572INSERT INTO CreditDebitTrans VALUES ('A00014', '1.50', 'Credit', '2017-03-04 15:19:14', 'card14', '1NNNNN')
573INSERT INTO CreditDebitTrans VALUES ('A00015', '0.50', 'Debit', '2014-11-11 10:30:10', 'card13', '1OOOOO')
574INSERT INTO CreditDebitTrans VALUES ('A00016', '1.50', 'Debit', '2013-01-01 02:01:01', 'card15', '1PPPPP')
575INSERT INTO CreditDebitTrans VALUES ('A00017', '2.50', 'Credit', '2014-01-01 01:51:01', 'card07', '1QQQQQ')