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