· 8 years ago · Jan 11, 2018, 06:44 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('Maintenance'))
69drop table Maintenance
70go
71
72if exists(select * from sysobjects where id = object_id('CreditCard'))
73drop table CreditCard
74go
75
76
77--Creating tables--
78
79Create table Bike
80(
81 BikeID char(4) NOT NULL,
82 Rate smallint NOT NULL,
83 Status varchar(2) NOT NULL CHECK(Status IN ('W', 'NR', 'R'))
84 CONSTRAINT Pk_Bike PRIMARY KEY(BikeID)
85)
86
87Create table Member
88(
89 MemberID char(6) NOT NULL,
90 Name varchar(30) NOT NULL,
91 Email varchar(40) NULL,
92 Phone char(9) NOT NULL,
93 RewardPts smallint NOT NULL DEFAULT(0),
94 Deposit money NOT NULL DEFAULT(50),
95 CreditBalance money NOT NULL DEFAULT(0),
96
97 CONSTRAINT Pk_Member PRIMARY KEY(MemberID),
98 --CONSTRAINT Chk_Phone CHECK(Phone like '^[689]\d{8}$'),
99 --CONSTRAINT Chk_Email CHECK(Email like '^[\w\d]+@\D+(\.\D+)?(.com)(\.\D+)?$')
100)
101
102
103Create table BikeReservation
104(
105 ReservationID char(4) NOT NULL,
106 MemberID char(6) NOT NULL,
107 BikeID char(4) NOT NULL,
108 ResStatus char(1) NOT NULL,
109 DateTimeRes datetime NOT NULL DEFAULT(GETDATE()),
110 DateTimeReservedFor datetime NOT NULL,
111
112 CONSTRAINT Pk_ReservationID PRIMARY KEY(ReservationID),
113 CONSTRAINT Fk_BikeReservation_MemberID FOREIGN KEY(MemberID) REFERENCES Member(MemberID),
114 CONSTRAINT Fk_BikeReservation_BikeID FOREIGN KEY(BikeID) REFERENCES Bike(BikeID),
115 CONSTRAINT Chk_ResStatus CHECK(ResStatus IN ('R', 'A')),
116 CONSTRAINT Chk_DateTimeReservedFor CHECK(DateTimeReservedFor >= DateTimeRes)
117
118)
119
120Create table BikeStop
121(
122 BikeStopID char(2) NOT NULL,
123 StopLat decimal(7,6) NOT NULL,
124 StopLong decimal(9,6) NOT NULL,
125 StopRadius smallint NOT NULL,
126
127 CONSTRAINT Pk_BikeStop PRIMARY KEY(BikeStopID),
128 CONSTRAINT Chk_StopRadius CHECK(StopRadius between 5 and 25)
129)
130
131Create table Promotion
132(
133 PromoCode varchar(10) NOT NULL,
134 PromoDesc varchar(50) NULL,
135 PromoRate decimal(3,2) NOT NULL,
136 StartDate date NOT NULL,
137 EndDate date NOT NULL,
138
139 CONSTRAINT Pk_Promotion PRIMARY KEY(PromoCode),
140 CONSTRAINT Chk_EndDate CHECK(EndDate >= StartDate),
141 CONSTRAINT Chk_StartDate CHECK(StartDate >= GETDATE())
142)
143Create table Ride
144(
145 RideID char(6) NOT NULL,
146 DateTimeStart datetime NOT NULL,
147 DateTimeEnd datetime NULL,
148 RidePts Int NOT NULL DEFAULT(0),
149 Distance Decimal(5,3) NULL,
150 PtsRedeemed Int NOT NULL DEFAULT(0),
151 Cost smallmoney NULL,
152 MemberID char(6) NULL,
153 BikeID char(4) NULL,
154 BikeStopID char(2) NULL,
155 PromoCode varchar(10) NULL,
156
157 CONSTRAINT Pk_Ride PRIMARY KEY(RideID),
158 CONSTRAINT Fk_Ride_MemberID FOREIGN KEY(MemberID) REFERENCES Member(MemberID),
159 CONSTRAINT Fk_Ride_BikeID FOREIGN KEY(BikeID) REFERENCES Bike(BikeID),
160 CONSTRAINT Fk_Ride_BikeStopID FOREIGN KEY(BikeStopID) REFERENCES BikeStop(BikeStopID),
161 CONSTRAINT Fk_Ride_PromoCode FOREIGN KEY(PromoCode) REFERENCES Promotion(PromoCode),
162
163 CONSTRAINT Chk_PtsRedeemed CHECK(PtsRedeemed % 50 = 0)
164)
165
166Create table Feedback
167(
168 FBID char(5) NOT NULL,
169 FBDateTime datetime NOT NULL default(GETDATE()),
170 MemberID char(6) NOT NULL,
171
172 CONSTRAINT Pk_Feedback PRIMARY KEY(FBID),
173 CONSTRAINT Fk_Feedback_MemberID FOREIGN KEY(MemberID) REFERENCES Member(MemberID)
174)
175
176
177Create table FaultType
178(
179 FaultCode char(4) NOT NULL,
180 FaultDesc varchar(500) NOT NULL,
181
182
183 CONSTRAINT Pk_FaultType PRIMARY KEY(FaultCode),
184)
185
186Create table Maintenance
187(
188 MaintID char(5) NOT NULL,
189 MaintDate date NOT NULL,
190
191 CONSTRAINT Pk_Maintenance PRIMARY KEY(MaintID),
192 CONSTRAINT Chk_MaintID CHECK(MaintID <= GETDATE())
193)
194
195Create table MaintType
196(
197 TypeCode char(5) NOT NULL,
198 TypeDesc varchar(50) NOT NULL,
199
200 CONSTRAINT Pk_MaintType PRIMARY KEY (TypeCode)
201
202
203)
204
205Create table MaintItem
206(
207 SNo char(6) NOT NULL,
208 MaintID char(5) NOT NULL,
209 MaintDesc varchar(100) NOT NULL,
210 TypeCode char(5) NULL,
211
212 CONSTRAINT Pk_MaintItem PRIMARY KEY (SNo, MaintID),
213 CONSTRAINT Fk_MaintID FOREIGN KEY (MaintID) REFERENCES Maintenance (MaintID) ON DELETE CASCADE,
214 CONSTRAINT Fk_TypeCode FOREIGN KEY (TypeCode) REFERENCES MaintType (TypeCode)
215)
216
217Create table Fault
218(
219 FBID char(5) NOT NULL,
220 FBDateTime varchar(40) NOT NULL,
221 FaultCat char(6) NULL,
222 MemberID char(6) NOT NULL,
223 FaultCode char(4) NULL,
224 MaintID char(5) NOT NULL,
225
226 CONSTRAINT Pk_Fault PRIMARY KEY (FBID),
227 CONSTRAINT Fk_Fault_FBID FOREIGN KEY (FBID) REFERENCES Feedback(FBID),
228 CONSTRAINT Fk_Fault_MemberID FOREIGN KEY (MemberID) REFERENCES Member (MemberID),
229 CONSTRAINT Fk_Fault_FaultCode FOREIGN KEY(FaultCode) REFERENCES FaultType(FaultCode),
230 CONSTRAINT Fk_Fault_MaintID FOREIGN KEY(MaintID) REFERENCES Maintenance(MaintID)
231)
232
233Create table Comment
234(
235 FBID char(5) NOT NULL,
236 FBDateTime datetime NOT NULL,
237 FBMessage varchar(1000) NULL,
238 MemberID char(6) NOT NULL,
239
240 CONSTRAINT Pk_Comment PRIMARY KEY(FBID),
241 CONSTRAINT Fk_Comment_FBID FOREIGN KEY(FBID) REFERENCES Feedback(FBID),
242 CONSTRAINT Fk_Comment_MemberID FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
243 CONSTRAINT Chk_FBDateTime CHECK(FBDateTime <= GETDATE())
244)
245
246Create table CreditCard
247(
248 CardNo char(5) NOT NULL,
249 CardType varchar(30) NOT NULL,
250 ExpiryDate date NOT NULL,
251 CVV char(3) NOT NULL,
252 MemberID char(6) NOT NULL
253 CONSTRAINT Pk_CreditCard PRIMARY KEY(CardNo),
254 CONSTRAINT Chk_ExpiryDate CHECK(ExpiryDate >= GETDATE()),
255 CONSTRAINT Fk_MemberID FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
256)
257
258Create table CreditDebitTrans
259(
260 TransID char(6) NOT NULL,
261 TransAmt smallmoney NOT NULL,
262 TransType varchar(6) NOT NULL,
263 TransDateTime datetime NOT NULL default(GETDATE()),
264 CardNo char(5) NOT NULL,
265 RideID char(6) NOT NULL,
266
267 CONSTRAINT Pk_CreditDebitTrans PRIMARY KEY(TransID),
268 CONSTRAINT Fk_CardNo FOREIGN KEY(CardNo) REFERENCES CreditCard(CardNo),
269 CONSTRAINT Fk_RideID FOREIGN KEY(RideID) REFERENCES Ride(RideID),
270
271 CONSTRAINT Chk_TransType CHECK(TransType in ('Credit','Debit'))
272)
273
274
275
276--Inserting values--
277
278insert into Bike(BikeID, Rate, Status) VALUES ('1AAA', 0.5, 'W')
279insert into Bike(BikeID, Rate, Status) VALUES ('2BBB', 0.5, 'W')
280insert into Bike(BikeID, Rate, Status) VALUES ('3CCC', 0.5, 'W')
281insert into Bike(BikeID, Rate, Status) VALUES ('4DDD', 0.5, 'R')
282insert into Bike(BikeID, Rate, Status) VALUES ('5EEE', 0.5, 'W')
283insert into Bike(BikeID, Rate, Status) VALUES ('6FFF', 0.5, 'W')
284insert into Bike(BikeID, Rate, Status) VALUES ('7GGG', 0.5, 'NR')
285insert into Bike(BikeID, Rate, Status) VALUES ('8HHH', 0.5, 'W')
286insert into Bike(BikeID, Rate, Status) VALUES ('9III', 0.5, 'W')
287insert into Bike(BikeID, Rate, Status) VALUES ('JJJ1', 0.5, 'W')
288insert into Bike(BikeID, Rate, Status) VALUES ('KKK2', 0.5, 'W')
289insert into Bike(BikeID, Rate, Status) VALUES ('LLL3', 0.5, 'NR')
290insert into Bike(BikeID, Rate, Status) VALUES ('MMM4', 0.5, 'W')
291insert into Bike(BikeID, Rate, Status) VALUES ('NNN5', 0.5, 'W')
292insert into Bike(BikeID, Rate, Status) VALUES ('OOO6', 0.5, 'W')
293
294
295insert into Member(MemberID, Name, Email, Phone) values ('00000A','Robert Bruce', 'robertbruce@gmail.com', '90000000')
296insert into Member(MemberID, Name, Email, Phone) values ('11111B','John Bruce', 'jhonbruce@gmail.com', '90000001')
297insert into Member(MemberID, Name, Email, Phone) values ('22222C','Tom Harrison', 'tomharrison@gmail.com', '90000002')
298insert into Member(MemberID, Name, Email, Phone) values ('33333D','Zachary Tan', 'zacharytan@gmail.com', '900000003')
299insert into Member(MemberID, Name, Email, Phone) values ('44444E','Tammy Lee', 'tammylee@gmail.com', '90000004')
300insert into Member(MemberID, Name, Email, Phone) values ('55555F','Monica Cheng', 'monicacheng@gmail.com', '90000005')
301insert into Member(MemberID, Name, Email, Phone) values ('66666G','Javier Tang', 'javiertang@gmail.com', '90000006')
302insert into Member(MemberID, Name, Email, Phone) values ('77777H','Shaun Jacob', 'shaunjacob@gmail.com', '90000007')
303insert into Member(MemberID, Name, Email, Phone) values ('88888I','William Johnson', 'willianjohnson@gmail.com', '90000008')
304insert into Member(MemberID, Name, Email, Phone) values ('99999J','James Bruce', 'jamesbruce@gmail.com', '90000009')
305insert into Member(MemberID, Name, Email, Phone) values ('AAAAA1','Wayne', 'xxxwayne999@gmail.com', '80000000')
306insert into Member(MemberID, Name, Email, Phone) values ('BBBBB2','Jennifer Pang', 'jenniferpang@gmail.com', '80000001')
307insert into Member(MemberID, Name, Email, Phone) values ('CCCCC3','Ethan Bradberry', 'ethanbradberry@gmail.com', '80000002')
308insert into Member(MemberID, Name, Email, Phone) values ('DDDDD4','Daniel Lim', 'daniellim@gmail.com', '80000003')
309insert into Member(MemberID, Name, Email, Phone) values ('EEEEE5','Lucas paul', 'lucaspaul@gmail.com', '80000004')
310
311insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345A', '1AAA', 'A', '2008-01-01 01:01:01', '2008-01-01 01:11:01')
312insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345B', '2BBB', 'A', '2009-02-02 02:02:02', '2009-02-02 02:12:02')
313insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345C', '3CCC', 'A', '2010-03-03 03:03:03', '2010-03-03 03:23:03')
314insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345D', '4DDD', 'A', '2011-04-04 04:04:04', '2011-04-04 04:34:04')
315insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345E', '5EEE', 'A', '2011-05-05 05:05:05', '2012-05-05 05:15:05')
316insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345F', '6FFF', 'A', '2012-06-06 06:06:06', '2012-06-06 06:16:06')
317insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345G', '7GGG', 'A', '2013-07-07 07:07:07', '2013-07-07 07:27:07')
318insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345H', '8HHH', 'A', '2013-08-08 08:08:08', '2013-08-08 08:28:08')
319insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345I', '9III', 'A', '2013-09-09 09:09:09', '2013-09-09 09:10:09')
320insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345J', 'JJJ1', 'A', '2014-10-10 09:09:10', '2014-10-10 09:09:20')
321insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345K', 'KKK2', 'A', '2014-11-11 10:10:10', '2014-11-11 10:20:10')
322insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345L', 'LLL3', 'A', '2016-12-12 11:11:11', '2016-12-12 11:15:11')
323insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345M', 'MMM4', 'A', '2017-01-02 12:12:12', '2017-01-02 12:15:12')
324insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345N', 'NNN5', 'A', '2017-02-03 13:13:13', '2017-02-03 13:23:13')
325insert into BikeReservation(MemberID, BikeID, ResStatus, DateTimeRes, DateTimeReservedFor) values ('12345O', 'OOO6', 'A', '2017-03-04 14:14:14', '2017-03-04 14:54:14')
326
327insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AA',1.335512, 103.962087, 25)
328insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AB',1.340790, 103.950886, 20)
329insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AC',1.345273, 103.939234, 15)
330insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AD',1.334727, 103.764290, 10)
331insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AE',1.359096, 103.893723, 5)
332insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AF',1.366304, 103.947624, 25)
333insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AG',1.446274, 103.823685, 10)
334insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AH',1.394791, 103.906769, 15)
335insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AI',1.312760, 103.767380, 25)
336insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AJ',1.327176, 103.688416, 25)
337insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AK',1.444214, 103.788666, 20)
338insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AL',1.392389, 103.904709, 10)
339insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AM',1.381405, 103.890633, 5)
340insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AN',1.377630, 103.879646, 5)
341insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AO',1.328892, 103.744720, 25)
342
343insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo01', 'Get 50% off of total price of the ride', 0.50, '2008-01-01', '2008-02-01')
344insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo02', 'Get 25% off of total price of the ride', 0.25, '2008-04-01', '2008-06-01')
345insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo03', 'Get 30% off of total price of the ride', 0.30, '2008-08-01', '2018-12-01')
346insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo04', 'Get 20% off of total price of the ride', 0.20, '2009-02-29', '2009-04-01')
347insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo05', 'Get 10% off of total price of the ride', 0.10, '2009-06-01', '2009-08-01')
348insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo06', 'Get 30% off of total price of the ride', 0.30, '2010-01-01', '2010-02-01')
349insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo07', 'Get 60% off of total price of the ride', 0.60, '2010-02-29', '2010-06-01')
350insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo08', 'Get 80% off of total price of the ride', 0.80, '2010-08-01', '2010-09-01')
351insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo09', 'Get 90% off of total price of the ride', 0.90, '2010-11-01', '2010-12-01')
352insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo10', 'Get 10% off of total price of the ride', 0.10, '2011-02-01', '2011-10-01')
353insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo11', 'Get 15% off of total price of the ride', 0.15, '2012-06-01', '2012-12-31')
354insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo12', 'Get 33% off of total price of the ride', 0.33, '2013-02-01', '2013-04-01')
355insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo13', 'Get 25% off of total price of the ride', 0.25, '2014-01-01', '2014-08-01')
356insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo14', 'Get 55% off of total price of the ride', 0.55, '2015-11-01', '2016-03-01')
357insert into Promotion(PromoCode, PromoDesc, PromoRate, StartDate, EndDate) values ('Promo15', 'Get 75% off of total price of the ride', 0.75, '2017-06-01', '2018-01-01')
358
359insert into Ride(RideID, DateTimeStart, DateTimeEnd, RidePts, Distance, PtsRedeemed, Cost, MemberID, BikeID, BikeShopID, PromoCode) values('1AAAAA', '2008-01-01 01:01:01', '2008-01-01 01:21:01', 4