· 8 years ago · Jan 11, 2018, 04:08 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','87285936',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','88741167',53,1,1)
48INSERT INTO Member VALUES('7','hesus@gmail.com','86993358',92,19,1)
49INSERT INTO Member VALUES('8','jchrist@hotmail','80868883',36,38,1)
50INSERT INTO Member VALUES('9','hallah@outlook.com','80223786',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
122CREATE TABLE CreditCard (
123 CardNo VARCHAR(19) PRIMARY KEY,
124 CVV CHAR(4) NOT NULL,
125 CardType VARCHAR(20) NOT NULL,
126 ExpiryDate DATETIME NOT NULL,
127 MemberID BIGINT NOT NULL FOREIGN KEY REFERENCES Member,
128);
129GO
130
131INSERT INTO CreditCard VALUES('5935687892060702','5159','Network','2020-09-30',1);
132INSERT INTO CreditCard VALUES('8224503908962211','3742','American Express','2023-11-30',2);
133INSERT INTO CreditCard VALUES('1267066767278728','8360','MasterCard','2022-06-30',3);
134INSERT INTO CreditCard VALUES('8518818624861005','2004','Capital One','2019-03-31',4);
135INSERT INTO CreditCard VALUES('8334188285737418','7355','Network','2020-11-30',5);
136INSERT INTO CreditCard VALUES('8269070784041522','3672','MasterCard','2023-08-31',6);
137INSERT INTO CreditCard VALUES('7546628190228343','4758','Visa','2023-03-31',7);
138INSERT INTO CreditCard VALUES('8698548266606433','1796','Diners','2022-08-31',8);
139INSERT INTO CreditCard VALUES('3202744613765058','5960','Discover','2025-02-28',9);
140INSERT INTO CreditCard VALUES('7883726759101047','1532','American Express','2025-06-30',10);
141INSERT INTO CreditCard VALUES('8028876524515000','6937','Capital One','2024-09-30',11);
142INSERT INTO CreditCard VALUES('2163899734310162','8085','Diners','2023-11-30',12);
143INSERT INTO CreditCard VALUES('9177259930362024','2644','Visa','2022-01-31',13);
144INSERT INTO CreditCard VALUES('9050315501627512','2568','Visa','2025-02-28',14);
145INSERT INTO CreditCard VALUES('2519518809191926','4997','Capital One','2020-04-30',15);
146GO
147
148CREATE TABLE CreditDebitTrans (
149 TransID BIGINT PRIMARY KEY,
150 TransType VARCHAR(20) NOT NULL,
151 TransAmt SMALLMONEY NOT NULL,
152 TransDateTime DATETIME NOT NULL,
153 CardNo VARCHAR(19) NOT NULL FOREIGN KEY REFERENCES CreditCard,
154);
155GO
156
157
158CREATE TABLE Ride (
159 RideID BIGINT PRIMARY KEY,
160 PtsRedeemed INT NULL,
161 RidePts INT NOT NULL,
162 Cost SMALLMONEY NOT NULL,
163 Distance FLOAT NOT NULL,
164 DateTimeStart DATETIME NOT NULL,
165 DateTimeEnd DATETIME NOT NULL,
166 MemberID BIGINT NOT NULL FOREIGN KEY REFERENCES Member,
167 BikeID BIGINT NOT NULL FOREIGN KEY REFERENCES Bike,
168 PromoCode CHAR(6) NULL FOREIGN KEY REFERENCES Promotion,
169 Start BIGINT NOT NULL FOREIGN KEY REFERENCES BikeStop(BikeStopID),
170 Finish BIGINT NOT NULL FOREIGN KEY REFERENCES BikeStop(BikeStopID),
171 TransID BIGINT NULL FOREIGN KEY REFERENCES CreditDebitTrans,
172);
173GO
174
175
176CREATE TABLE Maintenance (
177 MaintID BIGINT PRIMARY KEY,
178 MaintDate DATETIME NOT NULL,
179 BikeID BIGINT NOT NULL FOREIGN KEY REFERENCES Bike,
180);
181GO
182
183
184CREATE TABLE Repair (
185 MaintID BIGINT PRIMARY KEY FOREIGN KEY REFERENCES Maintenance,
186);
187GO
188
189
190CREATE TABLE MaintItem (
191 MaintID BIGINT NOT NULL FOREIGN KEY REFERENCES Maintenance,
192 SNo BIGINT NOT NULL,
193 Descr TEXT NULL,
194 CONSTRAINT PK_MaintItem PRIMARY KEY (MaintID, SNo),
195);
196GO
197
198
199CREATE TABLE MaintType (
200 TypeCode INT PRIMARY KEY,
201 TypeDesc TEXT NULL,
202);
203GO
204
205
206CREATE TABLE Feedback (
207 FBID BIGINT PRIMARY KEY,
208 FBDateTime DATETIME NOT NULL,
209 MemberID BIGINT NOT NULL FOREIGN KEY REFERENCES Member,
210);
211GO
212
213
214CREATE TABLE Comment (
215 FBID BIGINT PRIMARY KEY FOREIGN KEY REFERENCES Feedback,
216 Message TEXT NULL,
217);
218GO
219
220
221CREATE TABLE Fault (
222 FBID BIGINT PRIMARY KEY FOREIGN KEY REFERENCES Feedback,
223 FaultCode SMALLINT NOT NULL FOREIGN KEY REFERENCES FaultType,
224 MaintID BIGINT NULL FOREIGN KEY REFERENCES Repair,
225 BikeID BIGINT NOT NULL FOREIGN KEY REFERENCES Bike,
226);
227GO
228
229CREATE TABLE Reservation (
230 MemberID BIGINT FOREIGN KEY REFERENCES Member,
231 BikeID BIGINT FOREIGN KEY REFERENCES Bike,
232 ResStatus VARCHAR(15) NOT NULL CHECK (ResStatus IN ('Confirmed', 'Cancelled', 'NoShow')),
233 DateTimeRes DATETIME NOT NULL,
234 DateTimeReservedFor DATETIME NOT NULL,
235 CONSTRAINT PK_Reservation PRIMARY KEY (MemberID, BikeID),
236 CONSTRAINT CHK_DateTime CHECK (DateTimeRes < DateTimeReservedFor),
237)
238GO
239
240
241SELECT * FROM FaultType;
242SELECT * FROM Member;
243SELECT * FROM BikeStop;
244SELECT * FROM Bike;
245GO