· 8 years ago · Jan 14, 2018, 04:24 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
201Create table Repair
202(
203 MaintID char(5) NOT NULL,
204 MaintDate date NOT NULL,
205
206 CONSTRAINT Pk_Repair PRIMARY KEY(MaintID),
207 CONSTRAINT Fk_Repair_MaintID FOREIGN KEY(MaintID) REFERENCES Maintenance(MaintID)
208)
209
210Create table MaintType
211(
212 TypeCode char(5) NOT NULL,
213 TypeDesc varchar(50) NOT NULL,
214
215 CONSTRAINT Pk_MaintType PRIMARY KEY (TypeCode)
216)
217
218Create table MaintItem
219(
220 SNo char(2) NOT NULL,
221 MaintID char(5) NOT NULL,
222 MaintDesc varchar(100) NOT NULL,
223 TypeCode char(5) NULL,
224
225 CONSTRAINT Pk_MaintItem PRIMARY KEY (SNo, MaintID),
226 CONSTRAINT Fk_MaintItem_MaintID FOREIGN KEY (MaintID) REFERENCES Maintenance (MaintID) ON DELETE CASCADE,
227 CONSTRAINT Fk_MaintItem_TypeCode FOREIGN KEY (TypeCode) REFERENCES MaintType (TypeCode)
228)
229
230Create table Fault
231(
232 FBID char(5) NOT NULL,
233 FBDateTime varchar(40) NOT NULL,
234 FaultCat varchar(15) NOT NULL,
235 MemberID char(6) NOT NULL,
236 FaultCode char(4) NULL,
237 BikeID char(4) NOT NULL,
238 MaintID char(5) NOT NULL,
239
240 CONSTRAINT Pk_Fault PRIMARY KEY (FBID),
241 CONSTRAINT FK_Fault_FBID FOREIGN KEY(FBID) REFERENCES Feedback(FBID),
242 CONSTRAINT Fk_Fault_MemberID FOREIGN KEY (MemberID) REFERENCES Member (MemberID),
243 CONSTRAINT Fk_Fault_FaultCode FOREIGN KEY(FaultCode) REFERENCES FaultType(FaultCode),
244 CONSTRAINT Fk_Fault_BikeID FOREIGN KEY(BikeID) REFERENCES Bike(BikeID),
245 CONSTRAINT Fk_Fault_MaintID FOREIGN KEY(MaintID) REFERENCES Maintenance(MaintID),
246 CONSTRAINT Chk_Fault_FaultCat CHECK(FaultCat IN ('Tire', 'Pedal', 'Seat', 'Basket', 'Lock', 'Frame', 'Others'))
247)
248
249Create table Comment
250(
251 FBID char(5) NOT NULL,
252 FBDateTime datetime NOT NULL,
253 FBMessage varchar(1000) NULL,
254 MemberID char(6) NOT NULL,
255
256 CONSTRAINT Pk_Comment PRIMARY KEY(FBID),
257 CONSTRAINT Fk_Comment_FBID FOREIGN KEY(FBID) REFERENCES Feedback(FBID),
258 CONSTRAINT Fk_Comment_MemberID FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
259 CONSTRAINT Chk_FBDateTime CHECK(FBDateTime <= GETDATE())
260)
261
262Create table CreditCard
263(
264 CardNo char(6) NOT NULL,
265 CardType varchar(30) NOT NULL,
266 ExpiryDate date NOT NULL,
267 CVV char(3) NOT NULL,
268 MemberID char(6) NOT NULL
269 CONSTRAINT Pk_CreditCard PRIMARY KEY(CardNo),
270 CONSTRAINT Chk_ExpiryDate CHECK(ExpiryDate >= GETDATE()),
271 CONSTRAINT Fk_MemberID FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
272)
273
274Create table CreditDebitTrans
275(
276 TransID char(6) NOT NULL,
277 TransAmt smallmoney NOT NULL,
278 TransType varchar(6) NOT NULL,
279 TransDateTime datetime NOT NULL default(GETDATE()),
280 CardNo char(6) NOT NULL,
281 RideID char(6) NOT NULL,
282
283 CONSTRAINT Pk_CreditDebitTrans PRIMARY KEY(TransID),
284 CONSTRAINT Fk_CreditDebitTrans_CardNo FOREIGN KEY(CardNo) REFERENCES CreditCard(CardNo),
285 CONSTRAINT Fk_CreditDebitTrans_RideID FOREIGN KEY(RideID) REFERENCES Ride(RideID),
286 CONSTRAINT Chk_TransType CHECK(TransType in ('Credit','Debit'))
287)
288
289
290
291--Inserting values--
292
293insert into Bike(BikeID, Rate, Status) VALUES ('1AAA', 0.5, 'W')
294insert into Bike(BikeID, Rate, Status) VALUES ('2BBB', 0.5, 'W')
295insert into Bike(BikeID, Rate, Status) VALUES ('3CCC', 0.5, 'W')
296insert into Bike(BikeID, Rate, Status) VALUES ('4DDD', 0.5, 'R')
297insert into Bike(BikeID, Rate, Status) VALUES ('5EEE', 0.5, 'W')
298insert into Bike(BikeID, Rate, Status) VALUES ('6FFF', 0.5, 'W')
299insert into Bike(BikeID, Rate, Status) VALUES ('7GGG', 0.5, 'NR')
300insert into Bike(BikeID, Rate, Status) VALUES ('8HHH', 0.5, 'W')
301insert into Bike(BikeID, Rate, Status) VALUES ('9III', 0.5, 'W')
302insert into Bike(BikeID, Rate, Status) VALUES ('JJJ1', 0.5, 'W')
303insert into Bike(BikeID, Rate, Status) VALUES ('KKK2', 0.5, 'W')
304insert into Bike(BikeID, Rate, Status) VALUES ('LLL3', 0.5, 'NR')
305insert into Bike(BikeID, Rate, Status) VALUES ('MMM4', 0.5, 'W')
306insert into Bike(BikeID, Rate, Status) VALUES ('NNN5', 0.5, 'W')
307insert into Bike(BikeID, Rate, Status) VALUES ('OOO6', 0.5, 'W')
308
309insert into Member (MemberID, Name, Email, Phone, Deposit, CreditBalance, DateJoin) values ('11111B','John Bruce', 'jhonbruce@gmail.com', '90000001', 100, 200, '2017-08-01')
310insert into Member values ('22222C','Tom Harrison', 'tomharrison@gmail.com', '90000002', 15, 60, 50, '2017-01-03')
311insert into Member (MemberID, Name, Email, Phone, Deposit, CreditBalance, DateJoin) values ('33333D','Zachary Tan', 'zacharytan@gmail.com', '90000003', 80, 200, '2016-12-01')
312insert into Member (MemberID, Name, Email, Phone, RewardPts, CreditBalance, DateJoin) values ('44444E','Tammy Lee', 'tammylee@gmail.com', '90000004', 24, 200, '2016-04-01')
313insert into Member values ('55555F','Monica Cheng', 'monicacheng@gmail.com', '90000005', 29, 140, 350, '2015-10-15')
314insert into Member values ('66666G','Javier Tang', 'javiertang@gmail.com', '90000006', 33, 120, 400, '2015-06-01')
315insert into Member (MemberID, Name, Email, Phone, RewardPts) values ('77777H','Shaun Jacob', 'shaunjacob@gmail.com', '90000007', 38)
316insert into Member values ('88888I','William Johnson', 'willianjohnson@gmail.com', '90000008', 42, 150, 25, '2014-09-09')
317insert into Member (MemberID, Name, Email, Phone, RewardPts, Deposit, DateJoin) values ('99999J','James Bruce', 'jamesbruce@gmail.com', '90000009', 45, 170, '2013-06-09')
318insert into Member (MemberID, Name, Email, Phone, Deposit, DateJoin) values ('AAAAA1','Wayne', 'xxxwayne999@gmail.com', '80000000', 200, '2012-07-22')
319insert into Member (MemberID, Name, Email, Phone, RewardPts) values ('BBBBB2','Jennifer Pang', 'jenniferpang@gmail.com', '80000001', 51)
320insert into Member (MemberID, Name, Email, Phone, RewardPts, DateJoin) values ('CCCCC3','Ethan Bradberry', 'ethanbradberry@gmail.com', '80000002', 55, '2011-11-11')
321insert into Member values ('DDDDD4','Daniel Lim', 'daniellim@gmail.com', '80000003', 69, 170, 46, '2010-10-10')
322insert into Member (MemberID, Name, Email, Phone, CreditBalance, DateJoin) values ('EEEEE5','Lucas Paul', 'lucaspaul@gmail.com', '80000004', 100, '2009-09-09')
323insert into Member values ('FFFFF6', 'Jack Lee', 'jacklee99@yahoo.com', '80000005', 1, 55, 31, '2008-08-08')
324insert into Member values ('GGGGG7', 'Mary Tan', 'marytan@yahoo.com', '80000006', 10, 60, 12, '2008-10-18')
325insert into Member values ('HHHHH8', 'Tom Everson', 'tomeve@yahoo.com', '80000007', 12, 56, 15, '2008-05-28')
326insert into Member values ('IIIII9', 'Claudia Koh', 'claudia13@yahoo.com', '80000008', 21, 53, 35, '2008-09-17')
327insert into Member (MemberID, Name, Email, Phone, RewardPts, Deposit, DateJoin) values ('JJJJJ1', 'Nicholas Paris', 'parisnicalo@yahoo.com', '80000009', 34, 55, '2016-02-25')
328insert into Member (MemberID, Name, Email, Phone, RewardPts, CreditBalance, DateJoin) values ('KKKKK2', 'Jackson Yi', 'yi08@yahoo.com', '80000010', 56, 60, '2016-11-26')
329
330insert into BikeReservation values ('0001','55555F', '4DDD', 'F', '2011-04-04 04:04:04', '2011-04-04 04:34:04')
331insert into BikeReservation values ('0002','44444E', '5EEE', 'F', '2011-05-05 05:05:05', '2011-05-05 05:15:05')
332insert into BikeReservation values ('0003','77777H', '3CCC', 'F', '2010-03-03 03:03:03', '2010-03-03 03:23:03')
333insert into BikeReservation values ('0004','88888I', '9III', 'O', '2018-01-11 09:09:09', '2018-01-11 17:10:09')
334insert into BikeReservation values ('0005','CCCCC3', 'LLL3', 'O', '2018-01-10 11:11:11', '2018-01-10 11:15:20')
335insert into BikeReservation values ('0006','FFFFF6', '1AAA', 'F', '2008-01-01 01:01:01', '2008-01-01 01:11:01')
336insert into BikeReservation values ('0007','AAAAA1', 'NNN5', 'F', '2017-02-03 13:13:13', '2017-02-03 13:23:13')
337insert into BikeReservation values ('0008','EEEEE5', 'KKK2', 'F', '2014-11-11 10:10:10', '2014-11-11 10:20:10')
338insert into BikeReservation values ('0009','DDDDD4', 'OOO6', 'F', '2017-03-04 14:14:14', '2017-03-04 14:54:14')
339insert into BikeReservation values ('0010','55555F', '6FFF', 'O', '2018-01-10 06:06:06', '2018-01-11 18:16:06')
340insert into BikeReservation values ('0011','99999J', 'JJJ1', 'F', '2014-10-10 09:09:10', '2014-10-10 09:09:20')
341insert into BikeReservation values ('0012','66666G', '7GGG', 'F', '2013-07-07 07:07:07', '2013-07-07 07:27:07')
342insert into BikeReservation values ('0013','77777H', '8HHH', 'F', '2013-08-08 08:08:08', '2013-08-08 08:28:08')
343insert into BikeReservation values ('0014','BBBBB2', 'MMM4', 'F', '2017-01-02 12:12:12', '2017-01-02 12:15:12')
344insert into BikeReservation values ('0015','11111B', '2BBB', 'F', '2009-02-02 02:02:02', '2009-02-02 02:12:02')
345
346insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AA',1.335512, 103.962087, 25)
347insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AB',1.340790, 103.950886, 20)
348insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AC',1.345273, 103.939234, 15)
349insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AD',1.334727, 103.764290, 10)
350insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AE',1.359096, 103.893723, 5)
351insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AF',1.366304, 103.947624, 25)
352insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AG',1.446274, 103.823685, 10)
353insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AH',1.394791, 103.906769, 15)
354insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AI',1.312760, 103.767380, 25)
355insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AJ',1.327176, 103.688416, 25)
356insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AK',1.444214, 103.788666, 20)
357insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AL',1.392389, 103.904709, 10)
358insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AM',1.381405, 103.890633, 5)
359insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AN',1.377630, 103.879646, 5)
360insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AO',1.328892, 103.744720, 25)
361
362insert into Promotion values ('Promo01', 'Get 5% off of total price of the ride', 0.05, '2008-01-01', '2008-03-01')
363insert into Promotion values ('Promo02', 'Get 25% off of total price of the ride', 0.25, '2008-04-01', '2008-06-01')
364insert into Promotion values ('Promo03', 'Get 30% off of total price of the ride', 0.30, '2008-08-01', '2018-12-01')
365insert into Promotion values ('Promo04', 'Get 20% off of total price of the ride', 0.20, '2009-02-13', '2009-04-01')
366insert into Promotion values ('Promo05', 'Get 10% off of total price of the ride', 0.10, '2009-06-01', '2009-08-01')
367insert into Promotion values ('Promo06', 'Get 30% off of total price of the ride', 0.30, '2010-01-01', '2010-02-01')
368insert into Promotion values ('Promo07', 'Get 60% off of total price of the ride', 0.60, '2010-02-28', '2010-06-01')
369insert into Promotion values ('Promo08', 'Get 80% off of total price of the ride', 0.80, '2010-08-01', '2010-09-01')
370insert into Promotion values ('Promo09', 'Get 90% off of total price of the ride', 0.90, '2010-11-01', '2010-12-01')
371insert into Promotion values ('Promo10', 'Get 10% off of total price of the ride', 0.10, '2011-02-01', '2011-10-01')
372insert into Promotion values ('Promo11', 'Get 15% off of total price of the ride', 0.15, '2012-06-01', '2012-12-31')
373insert into Promotion values ('Promo12', 'Get 33% off of total price of the ride', 0.33, '2013-02-01', '2013-04-01')
374insert into Promotion values ('Promo13', 'Get 25% off of total price of the ride', 0.25, '2014-01-01', '2014-08-01')
375insert into Promotion values ('Promo14', 'Get 55% off of total price of the ride', 0.55, '2015-11-01', '2016-03-01')
376insert into Promotion values ('Promo15', 'Get 75% off of total price of the ride', 0.75, '2017-06-01', '2018-01-01')
377
378insert 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')
379insert 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')
380insert into Ride values('1CCCCC', '2009-04-05 13:30:00', '2009-04-05 14:10:00', 40, 7.00, 0, 0.50, '22222C', '3CCC', 'AD', Null)
381insert 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')
382insert 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')
383insert 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')
384insert into Ride values('1GGGGG', '2013-08-08 08:28:08', '2013-08-08 08:43:08', 15, 4.50, 0, 0.50, '66666G', '7GGG', 'AJ', Null)
385insert 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', 'Promo10')
386insert 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')
387insert 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', Null)
388insert 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', Null)
389insert 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')
390insert into Ride values('1MMMMM', '2015-01-01 01:01:01', '2015-01-01 03:01:01', 120, 21.10, 100, 1.40, 'CCCCC3', '8HHH', 'AA', Null)
391insert 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)
392insert 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)
393
394INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00001', '2017-03-04 00:14:14', 'FFFFF6')
395INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00002', '2010-01-07 12:59:12', '22222C')
396INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00003', '2016-12-14 02:49:45', '11111B')
397INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00004', '2014-02-24 18:17:10', '33333D')
398INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00005', '2015-02-15 19:37:20', 'FFFFF6')
399INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00006', '2013-11-25 20:18:30', 'AAAAA1')
400INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00007', '2017-08-05 11:58:40', '44444E')
401INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00008', '2012-07-03 22:28:50', '66666G')
402INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00009', '2012-04-30 19:54:12', '77777H')
403INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00010', '2011-03-29 10:34:14', 'FFFFF6')
404INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00011', '2017-11-19 03:45:17', '55555F')
405INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00012', '2016-12-01 04:12:56', '55555F')
406INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00013', '2011-01-03 11:27:45', '11111B')
407INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00014', '2015-01-14 07:17:34', 'EEEEE5')
408INSERT INTO Feedback(FBID, FBDateTime, MemberID) VALUES ('00015', '2012-04-02 09:09:21', 'BBBBB2')
409INSERT INTO Feedback VALUES('10001','2017-02-10','HHHHH8')
410INSERT INTO Feedback VALUES('20002','2017-05-13','KKKKK2')
411INSERT INTO Feedback VALUES('30003','2017-03-26','GGGGG7')
412INSERT INTO Feedback VALUES('40004','2017-07-17','66666G')
413INSERT INTO Feedback VALUES('50005','2017-07-06','AAAAA1')
414INSERT INTO Feedback VALUES('60006','2017-10-09','HHHHH8')
415INSERT INTO Feedback VALUES('70007','2017-01-19','CCCCC3')
416INSERT INTO Feedback VALUES('80008','2017-04-03','99999J')
417INSERT INTO Feedback VALUES('90009','2017-02-20','JJJJJ1')
418INSERT INTO Feedback VALUES('11011','2017-07-21','33333D')
419INSERT INTO Feedback VALUES('22022','2017-08-26','BBBBB2')
420INSERT INTO Feedback VALUES('33033','2017-02-14','IIIII9')
421INSERT INTO Feedback VALUES('44044','2017-03-18','44444E')
422INSERT INTO Feedback VALUES('55055','2017-06-22','IIIII9')
423INSERT INTO Feedback VALUES('66066','2017-04-22','GGGGG7')
424
425INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00001', '2017-03-04 00:14:14', 'Message01', 'FFFFF6')
426INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00002', '2010-01-07 12:59:12', 'Message02', '22222C')
427INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00003', '2016-12-14 02:49:45', 'Message03', '11111B')
428INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00004', '2014-02-24 18:17:10', 'Message04', '33333D')
429INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00005', '2015-02-15 19:37:20', 'Message05', 'FFFFF6')
430INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00006', '2013-11-25 20:18:30', 'Message06', 'AAAAA1')
431INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00007', '2017-08-05 11:58:40', 'Message07', '44444E')
432INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00008', '2012-07-03 22:28:50', 'Message08', '66666G')
433INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00009', '2012-04-30 19:54:12', 'Message09', '77777H')
434INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00010', '2011-03-29 10:34:14', 'Message10', 'FFFFF6')
435INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00011', '2017-11-19 03:45:17', 'Message11', '55555F')
436INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00012', '2016-12-01 04:12:56', 'Message12', '55555F')
437INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00013', '2011-01-03 11:27:45', 'Message13', '11111B')
438INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00014', '2015-01-14 07:17:34', 'Message14', 'EEEEE5')
439INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00015', '2012-04-02 09:09:21', 'Message15', 'BBBBB2')
440
441INSERT INTO FaultType VALUES('TI-1','Tires are punctured')
442INSERT INTO FaultType VALUES('TI-2','Tires are worn out')
443INSERT INTO FaultType VALUES('TI-3','Tires misshaped')
444INSERT INTO FaultType VALUES('BA-1','A part of the basket is broken off')
445INSERT INTO FaultType VALUES('BA-2','Basket has been misshaped by force')
446INSERT INTO FaultType VALUES('BA-3','Basket is almost / already detached from the rest of the bike' )
447INSERT INTO FaultType VALUES('SE-1','Seat covering is torn off (or a portion of it)')
448INSERT INTO FaultType VALUES('SE-2','Seat is physically damaged')
449INSERT INTO FaultType VALUES('LO-1','Lock is broken, completely insecure')
450INSERT INTO FaultType VALUES('LO-2','Lock does not work')
451INSERT INTO FaultType VALUES('PE-1','Pedal(s) is / are loose')
452INSERT INTO FaultType VALUES('PE-2','Pedal(s) is / are missing')
453INSERT INTO FaultType VALUES('FR-1','Frame is dented in')
454INSERT INTO FaultType VALUES('FR-2','Paint on frame has been scraped off')
455INSERT INTO FaultType VALUES('OO-1','Bicycle is unlocked')
456
457INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0001', '2008-01-01', '1AAA')
458INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0002', '2009-02-02', '2BBB')
459INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0003', '2010-03-03', '3CCC')
460INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0004', '2010-04-04', '4DDD')
461INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0005', '2011-05-05', '5EEE')
462INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0006', '2012-06-06', '6FFF')
463INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0007', '2013-07-07', '1AAA')
464INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0008', '2014-08-08', '9III')
465INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0009', '2015-09-09', 'KKK2')
466INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0010', '2016-10-10', '1AAA')
467INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0011', '2017-11-11', 'OOO6')
468INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0012', '2010-12-12', 'KKK2')
469INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0013', '2008-01-01', 'NNN5')
470INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0014', '2011-02-02', 'MMM4')
471INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0015', '2012-03-03', '8HHH')
472
473INSERT INTO Fault VALUES('10001','2017-02-10','tire', 'HHHHH8','TI-1','OOO6', 'A0015')
474INSERT INTO Fault VALUES('20002','2017-05-13','basket', 'KKKKK2','BA-1','NNN5', 'A0014')
475INSERT INTO Fault VALUES('30003','2017-03-26', 'lock', 'GGGGG7','TI-2','MMM4', 'A0013')
476INSERT INTO Fault VALUES('40004','2017-07-17', 'others', '66666G','SE-1','LLL3', 'A0012')
477INSERT INTO Fault VALUES('50005','2017-07-06', 'pedal', 'AAAAA1','LO-1','KKK2', 'A0011')
478INSERT INTO Fault VALUES('60006','2017-10-09', 'frame', 'HHHHH8','LO-2','JJJ1', 'A0010')
479INSERT INTO Fault VALUES('70007','2017-01-19', 'basket', 'CCCCC3','BA-2','9III', 'A0009')
480INSERT INTO Fault VALUES('80008','2017-04-03', 'tire', '99999J','PE-1','8HHH', 'A0008')
481INSERT INTO Fault VALUES('90009','2017-02-20', 'frame', 'JJJJJ1','OO-1','7GGG', 'A0007')
482INSERT INTO Fault VALUES('11011','2017-07-21', 'basket', '33333D','SE-2','6FFF', 'A0006')
483INSERT INTO Fault VALUES('22022','2017-08-26', 'pedal', 'BBBBB2','TI-3','5EEE', 'A0005')
484INSERT INTO Fault VALUES('33033','2017-02-14', 'tire', 'IIIII9','FR-2','4DDD', 'A0004')
485INSERT INTO Fault VALUES('44044','2017-03-18', 'others', '44444E','PE-2','3CCC', 'A0003')
486INSERT INTO Fault VALUES('55055','2017-06-22', 'lock', 'IIIII9','BA-3','2BBB', 'A0002')
487INSERT INTO Fault VALUES('66066','2017-04-22', 'basket', 'GGGGG7','FR-1','1AAA', 'A0001')
488
489INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA01', 'TDesc01')
490INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA02', 'TDesc02')
491INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA03', 'TDesc03')
492INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA04', 'TDesc04')
493INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA05', 'TDesc05')
494INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA06', 'TDesc06')
495INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA07', 'TDesc07')
496INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA08', 'TDesc08')
497INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA09', 'TDesc09')
498INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA10', 'TDesc10')
499INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA11', 'TDesc11')
500INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA12', 'TDesc12')
501INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA13', 'TDesc13')
502INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA14', 'TDesc14')
503INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA15', 'TDesc15')
504
505INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('01', 'A0015', 'Mdesc01', 'AAA15')
506INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('02', 'A0014', 'Mdesc04', 'AAA14')
507INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('03', 'A0013', 'Mdesc05', 'AAA13')
508INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('04', 'A0012', 'Mdesc06', 'AAA12')
509INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('05', 'A0011', 'Mdesc07', 'AAA11')
510INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('06', 'A0010', 'Mdesc02', 'AAA10')
511INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('07', 'A0009', 'Mdesc03', 'AAA09')
512INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('08', 'A0008', 'Mdesc02', 'AAA08')
513INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('09', 'A0007', 'Mdesc01', 'AAA07')
514INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('10', 'A0006', 'Mdesc05', 'AAA06')
515INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('11', 'A0005', 'Mdesc08', 'AAA05')
516INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('12', 'A0004', 'Mdesc10', 'AAA04')
517INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('13', 'A0003', 'Mdesc06', 'AAA03')
518INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('14', 'A0002', 'Mdesc04', 'AAA02')
519INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('15', 'A0001', 'Mdesc07', 'AAA01')
520
521INSERT INTO CreditCard VALUES('Card01', 'Visa', '2019-01-01', '000', 'FFFFF6')
522INSERT INTO CreditCard VALUES('Card02', 'Master', '2020-01-01', '001', '66666G')
523INSERT INTO CreditCard VALUES('Card03', 'Diners', '2021-01-01', '002', 'FFFFF6')
524INSERT INTO CreditCard VALUES('Card04', 'Master', '2021-01-01', '003', 'BBBBB2')
525INSERT INTO CreditCard VALUES('Card05', 'Express', '2022-01-01', '004', '22222C')
526INSERT INTO CreditCard VALUES('Card06', 'Chase', '2022-01-01', '005', '77777H')
527INSERT INTO CreditCard VALUES('Card07', 'Visa', '2022-01-01', '006', 'EEEEE5')
528INSERT INTO CreditCard VALUES('Card08', 'Visa', '2022-01-01', '007', '11111B')
529INSERT INTO CreditCard VALUES('Card0', 'Diners', '2022-01-01', '008', '55555F')
530INSERT INTO CreditCard VALUES('Card10', 'Express', '2022-01-01', '009', '11111B')
531INSERT INTO CreditCard VALUES('Card11', 'Visa', '2022-01-01', '010', '55555F')
532INSERT INTO CreditCard VALUES('Card12', 'Visa', '2022-01-01', '011', '44444E')
533INSERT INTO CreditCard VALUES('Card13', 'Visa', '2019-01-01', '012', 'FFFFF6')
534INSERT INTO CreditCard VALUES('Card14', 'Master', '2019-01-01', '013', '33333D')
535INSERT INTO CreditCard VALUES('Card15', 'Chase', '2019-01-01', '014', 'AAAAA1')