· 8 years ago · Jan 11, 2018, 04:38 AM
1USE NP40Book;
2GO
3
4DROP DATABASE IF EXISTS MokeBike;
5GO
6
7CREATE DATABASE MokeBike;
8GO
9USE MokeBike;
10GO
11
12
13CREATE TABLE FaultType (
14 FaultCode SMALLINT PRIMARY KEY,
15 FaultDesc TEXT NULL,
16);
17GO
18
19INSERT INTO FaultType VALUES (1, 'Tyre pressure low');
20INSERT INTO FaultType VALUES (2, 'Broken handlebars');
21INSERT INTO FaultType VALUES (3, 'Bell not working');
22INSERT INTO FaultType VALUES (4, 'Safety lights not working');
23INSERT INTO FaultType VALUES (5, 'Broken seat');
24INSERT INTO FaultType VALUES (6, 'Dented body');
25INSERT INTO FaultType VALUES (7, 'QR code scratched/faded');
26INSERT INTO FaultType VALUES (8, 'GPS tracker broken');
27INSERT INTO FaultType VALUES (9, 'Broken lock');
28INSERT INTO FaultType VALUES (10, 'Missing parts');
29GO
30
31
32CREATE TABLE Member (
33 MemberID BIGINT PRIMARY KEY,
34 Email VARCHAR(50) NOT NULL,
35 Phone CHAR(8) NOT NULL,
36 RewardPts INT NOT NULL,
37 CreditBalance INT NOT NULL,
38 Deposit BIT NOT NULL DEFAULT 0,
39);
40GO
41
42INSERT INTO Member VALUES('1','Ezykill6969@hotmail','82543873',72,14,1)
43INSERT INTO Member VALUES('2','Ashokcoolboy5@outlook.com','99663219',96,0,1)
44INSERT INTO Member VALUES('3','ravennn@outlook.com','81022436',5,30,1)
45INSERT INTO Member VALUES('4','weiwei@gmail.com','87356981',50,47,1)
46INSERT INTO Member VALUES('5','kingarthur@gmail.com','86154372',23,50,1)
47INSERT INTO Member VALUES('6','janet@gmail.com','95695848',53,1,1)
48INSERT INTO Member VALUES('7','hesus@gmail.com','96806298',92,19,1)
49INSERT INTO Member VALUES('8','jchrist@hotmail','80868883',36,38,1)
50INSERT INTO Member VALUES('9','hallah@outlook.com','97180005',88,10,1)
51INSERT INTO Member VALUES('10','joehoe@hotmail','87838482',24,39,1)
52INSERT INTO Member VALUES('11','annietan@yahoo.com','81144240',17,25,1)
53INSERT INTO Member VALUES('12','all69goh@outlook.com','89583856',66,14,1)
54INSERT INTO Member VALUES('13','monkeymarq@yahoo.com','83265188',95,12,1)
55INSERT INTO Member VALUES('14','hockeytan@hotmail','86260086',21,35,1)
56INSERT INTO Member VALUES('15','youmomlol@hotmail','89406150',9,24,1)
57GO
58
59
60CREATE TABLE BikeStop (
61 BikeStopID BIGINT PRIMARY KEY,
62 StopLat FLOAT NOT NULL,
63 StopLong FLOAT NOT NULL,
64 StopRadius FLOAT NOT NULL,
65);
66GO
67
68INSERT INTO BikeStop VALUES(1,-87.3779,-33.1976,8);
69INSERT INTO BikeStop VALUES(2,16.8407,138.697,21);
70INSERT INTO BikeStop VALUES(3,30.3839,99.9450,25);
71INSERT INTO BikeStop VALUES(4,26.1532,-23.7384,15);
72INSERT INTO BikeStop VALUES(5,-66.9704,-74.9351,17);
73INSERT INTO BikeStop VALUES(6,-22.5941,144.6293,14);
74INSERT INTO BikeStop VALUES(7,-60.2102,18.1223,11);
75INSERT INTO BikeStop VALUES(8,80.9589,21.7501,6);
76INSERT INTO BikeStop VALUES(9,-2.1070,-68.5870,5);
77INSERT INTO BikeStop VALUES(10,-22.1847,83.4133,24);
78INSERT INTO BikeStop VALUES(11,34.2516,77.4688,19);
79INSERT INTO BikeStop VALUES(12,-3.8181,-48.3831,8);
80INSERT INTO BikeStop VALUES(13,71.9827,-48.4927,23);
81INSERT INTO BikeStop VALUES(14,77.8298,-83.6732,12);
82INSERT INTO BikeStop VALUES(15,-24.954,-57.9471,19);
83GO
84
85
86CREATE TABLE Bike (
87 BikeID BIGINT PRIMARY KEY,
88 Rate SMALLMONEY NOT NULL,
89 Status VARCHAR(20) NOT NULL CHECK (Status IN ('Available', 'Damaged')),
90 BikeStopID BIGINT NOT NULL FOREIGN KEY REFERENCES BikeStop,
91)
92GO
93
94INSERT INTO Bike VALUES (1, 0.40, 'Available', 1);
95INSERT INTO Bike VALUES (2, 0.32, 'Damaged', 2);
96INSERT INTO Bike VALUES (3, 0.40, 'Available', 3);
97INSERT INTO Bike VALUES (4, 0.40, 'Available', 4);
98INSERT INTO Bike VALUES (5, 0.40, 'Damaged', 5);
99INSERT INTO Bike VALUES (6, 0.55, 'Available', 6);
100INSERT INTO Bike VALUES (7, 0.40, 'Available', 7);
101INSERT INTO Bike VALUES (8, 0.20, 'Available', 8);
102INSERT INTO Bike VALUES (9, 0.40, 'Damaged', 9);
103INSERT INTO Bike VALUES (10, 0.40, 'Available', 10);
104INSERT INTO Bike VALUES (11, 0.55, 'Available', 11);
105INSERT INTO Bike VALUES (12, 0.40, 'Damaged', 12);
106INSERT INTO Bike VALUES (13, 0.20, 'Available', 13);
107INSERT INTO Bike VALUES (14, 0.40, 'Damaged', 14);
108INSERT INTO Bike VALUES (15, 0.40, 'Available', 15);
109GO
110
111
112CREATE TABLE Promotion (
113 PromoCode CHAR(6) PRIMARY KEY,
114 PromoRate FLOAT NOT NULL,
115 StartDate DATETIME NOT NULL,
116 EndDate DATETIME NOT NULL,
117 PromoDescription TEXT NULL,
118);
119GO
120
121
122
123CREATE TABLE CreditCard (
124 CardNo VARCHAR(19) PRIMARY KEY,
125 CVV CHAR(4) NOT NULL,
126 CardType VARCHAR(20) NOT NULL,
127 ExpiryDate DATETIME NOT NULL,
128 MemberID BIGINT NOT NULL FOREIGN KEY REFERENCES Member,
129);
130GO
131
132INSERT INTO CreditCard VALUES('5935687892060702','5159','Network','2020-09-30',1);
133INSERT INTO CreditCard VALUES('8224503908962211','3742','American Express','2023-11-30',2);
134INSERT INTO CreditCard VALUES('1267066767278728','8360','MasterCard','2022-06-30',3);
135INSERT INTO CreditCard VALUES('8518818624861005','2004','Capital One','2019-03-31',4);
136INSERT INTO CreditCard VALUES('8334188285737418','7355','Network','2020-11-30',5);
137INSERT INTO CreditCard VALUES('8269070784041522','3672','MasterCard','2023-08-31',6);
138INSERT INTO CreditCard VALUES('7546628190228343','4758','Visa','2023-03-31',7);
139INSERT INTO CreditCard VALUES('8698548266606433','1796','Diners','2022-08-31',8);
140INSERT INTO CreditCard VALUES('3202744613765058','5960','Discover','2025-02-28',9);
141INSERT INTO CreditCard VALUES('7883726759101047','1532','American Express','2025-06-30',10);
142INSERT INTO CreditCard VALUES('8028876524515000','6937','Capital One','2024-09-30',11);
143INSERT INTO CreditCard VALUES('2163899734310162','8085','Diners','2023-11-30',12);
144INSERT INTO CreditCard VALUES('9177259930362024','2644','Visa','2022-01-31',13);
145INSERT INTO CreditCard VALUES('9050315501627512','2568','Visa','2025-02-28',14);
146INSERT INTO CreditCard VALUES('2519518809191926','4997','Capital One','2020-04-30',15);
147GO
148
149CREATE TABLE CreditDebitTrans (
150 TransID BIGINT PRIMARY KEY,
151 TransType VARCHAR(20) NOT NULL,
152 TransAmt SMALLMONEY NOT NULL,
153 TransDateTime DATETIME NOT NULL,
154 CardNo VARCHAR(19) NOT NULL FOREIGN KEY REFERENCES CreditCard,
155);
156GO
157
158
159CREATE TABLE Ride (
160 RideID BIGINT PRIMARY KEY,
161 PtsRedeemed INT NULL,
162 RidePts INT NOT NULL,
163 Cost SMALLMONEY NOT NULL,
164 Distance FLOAT NOT NULL,
165 DateTimeStart DATETIME NOT NULL,
166 DateTimeEnd DATETIME NOT NULL,
167 MemberID BIGINT NOT NULL FOREIGN KEY REFERENCES Member,
168 BikeID BIGINT NOT NULL FOREIGN KEY REFERENCES Bike,
169 PromoCode CHAR(6) NULL FOREIGN KEY REFERENCES Promotion,
170 Start BIGINT NOT NULL FOREIGN KEY REFERENCES BikeStop(BikeStopID),
171 Finish BIGINT NOT NULL FOREIGN KEY REFERENCES BikeStop(BikeStopID),
172 TransID BIGINT NULL FOREIGN KEY REFERENCES CreditDebitTrans,
173);
174GO
175
176
177CREATE TABLE Maintenance (
178 MaintID BIGINT PRIMARY KEY,
179 MaintDate DATETIME NOT NULL,
180 BikeID BIGINT NOT NULL FOREIGN KEY REFERENCES Bike,
181);
182GO
183
184
185CREATE TABLE Repair (
186 MaintID BIGINT PRIMARY KEY FOREIGN KEY REFERENCES Maintenance,
187);
188GO
189
190
191CREATE TABLE MaintItem (
192 MaintID BIGINT NOT NULL FOREIGN KEY REFERENCES Maintenance,
193 SNo BIGINT NOT NULL,
194 Descr TEXT NULL,
195 CONSTRAINT PK_MaintItem PRIMARY KEY (MaintID, SNo),
196);
197GO
198
199
200CREATE TABLE MaintType (
201 TypeCode INT PRIMARY KEY,
202 TypeDesc TEXT NULL,
203);
204GO
205
206
207CREATE TABLE Feedback (
208 FBID BIGINT PRIMARY KEY,
209 FBDateTime DATETIME NOT NULL,
210 MemberID BIGINT NOT NULL FOREIGN KEY REFERENCES Member,
211);
212GO
213
214
215CREATE TABLE Comment (
216 FBID BIGINT PRIMARY KEY FOREIGN KEY REFERENCES Feedback,
217 Message TEXT NOT NULL,
218);
219GO
220
221
222CREATE TABLE Fault (
223 FBID BIGINT PRIMARY KEY FOREIGN KEY REFERENCES Feedback,
224 FaultCode SMALLINT NOT NULL FOREIGN KEY REFERENCES FaultType,
225 MaintID BIGINT NULL FOREIGN KEY REFERENCES Repair,
226 BikeID BIGINT NOT NULL FOREIGN KEY REFERENCES Bike,
227);
228GO
229
230CREATE TABLE Reservation (
231 MemberID BIGINT FOREIGN KEY REFERENCES Member,
232 BikeID BIGINT FOREIGN KEY REFERENCES Bike,
233 ResStatus VARCHAR(15) NOT NULL CHECK (ResStatus IN ('Confirmed', 'Cancelled', 'NoShow')),
234 DateTimeRes DATETIME NOT NULL,
235 DateTimeReservedFor DATETIME NOT NULL,
236 CONSTRAINT PK_Reservation PRIMARY KEY (MemberID, BikeID),
237 CONSTRAINT CHK_DateTime CHECK (DateTimeRes < DateTimeReservedFor),
238)
239GO
240
241
242SELECT * FROM FaultType;
243SELECT * FROM Member;
244SELECT * FROM BikeStop;
245SELECT * FROM Bike;
246GO