· 9 years ago · Jul 16, 2017, 11:10 AM
1-- phpMyAdmin SQL Dump
2-- version 4.7.1
3-- https://www.phpmyadmin.net/
4--
5-- Host: localhost
6-- Generation Time: Jul 16, 2017 at 01:07 PM
7-- Server version: 5.6.35
8-- PHP Version: 7.1.1
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11SET time_zone = "+00:00";
12
13--
14-- Database: `Project`
15--
16CREATE DATABASE IF NOT EXISTS `Project` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
17USE `Project`;
18
19-- --------------------------------------------------------
20
21--
22-- Table structure for table `Administrator`
23--
24
25CREATE TABLE `Administrator` (
26 `AdministratorID` int(5) NOT NULL,
27 `Password` varchar(255) DEFAULT NULL,
28 `FirstName` varchar(255) DEFAULT NULL,
29 `LastName` varchar(255) DEFAULT NULL
30) ENGINE=InnoDB DEFAULT CHARSET=utf8;
31
32-- --------------------------------------------------------
33
34--
35-- Table structure for table `Charity`
36--
37
38CREATE TABLE `Charity` (
39 `CharityID` int(5) NOT NULL,
40 `Name` varchar(255) DEFAULT NULL,
41 `Description` varchar(255) DEFAULT NULL,
42 `WebsiteUrl` varchar(255) DEFAULT NULL,
43 `Logo` varchar(255) DEFAULT NULL
44) ENGINE=InnoDB DEFAULT CHARSET=utf8;
45
46-- --------------------------------------------------------
47
48--
49-- Table structure for table `Event`
50--
51
52CREATE TABLE `Event` (
53 `EventID` int(5) NOT NULL,
54 `Name` varchar(255) DEFAULT NULL,
55 `Distance` float DEFAULT NULL,
56 `DateOfEvent` date DEFAULT NULL,
57 `TimeStart` time DEFAULT NULL,
58 `Price` decimal(10,2) DEFAULT NULL
59) ENGINE=InnoDB DEFAULT CHARSET=utf8;
60
61--
62-- Dumping data for table `Event`
63--
64
65INSERT INTO `Event` (`EventID`, `Name`, `Distance`, `DateOfEvent`, `TimeStart`, `Price`) VALUES
66(1, 'Marathon Skills 2017', 42.5, '2017-10-10', '12:30:00', '10.52'),
67(2, 'Marathon Skills ~_~', 21.34, '2017-12-03', '20:20:00', '400.00'),
68(3, 'Marathon ABC', 5.22, '2016-10-20', '12:30:00', '200.50'),
69(4, 'WhatMara', 42.52, '2018-10-10', '12:30:00', '500.50');
70
71-- --------------------------------------------------------
72
73--
74-- Table structure for table `EventRegister`
75--
76
77CREATE TABLE `EventRegister` (
78 `RegID` int(5) NOT NULL,
79 `RunnerID` int(5) NOT NULL,
80 `EventID` int(5) NOT NULL,
81 `CheckInTime` time DEFAULT NULL,
82 `FinishTime` time DEFAULT NULL,
83 `TopSpeed` float DEFAULT NULL,
84 `PaymentConfirmed` tinyint(1) DEFAULT '0',
85 `PaymentTotal` int(11) DEFAULT NULL,
86 `RaceKitID` int(5) NOT NULL,
87 `RaceKitSent` tinyint(1) DEFAULT '0'
88) ENGINE=InnoDB DEFAULT CHARSET=utf8;
89
90--
91-- Dumping data for table `EventRegister`
92--
93
94INSERT INTO `EventRegister` (`RegID`, `RunnerID`, `EventID`, `CheckInTime`, `FinishTime`, `TopSpeed`, `PaymentConfirmed`, `PaymentTotal`, `RaceKitID`, `RaceKitSent`) VALUES
95(3, 1, 3, '10:52:00', '12:24:02', 10.5, 1, 123123, 4, 1),
96(8, 1, 2, '10:24:00', '12:01:24', 12.4, 1, 450, 2, 1),
97(9, 1, 1, NULL, NULL, NULL, 0, 51, 1, 0);
98
99-- --------------------------------------------------------
100
101--
102-- Table structure for table `RaceKitChoice`
103--
104
105CREATE TABLE `RaceKitChoice` (
106 `RaceKitID` int(5) NOT NULL,
107 `Name` varchar(255) DEFAULT NULL,
108 `Description` varchar(255) DEFAULT NULL,
109 `Price` decimal(10,2) DEFAULT NULL,
110 `Photo` varchar(255) DEFAULT NULL,
111 `EventID` int(5) NOT NULL
112) ENGINE=InnoDB DEFAULT CHARSET=utf8;
113
114--
115-- Dumping data for table `RaceKitChoice`
116--
117
118INSERT INTO `RaceKitChoice` (`RaceKitID`, `Name`, `Description`, `Price`, `Photo`, `EventID`) VALUES
119(1, 'RaceKit1', 'Water, OPOP', '40.00', NULL, 1),
120(2, 'RaceKit1', 'GGSI, HAHA', '50.00', NULL, 2),
121(3, 'RaceKit2', 'Food, GG', '60.00', NULL, 1),
122(4, 'RaceKitABC', 'abc R', '20.00', NULL, 3);
123
124-- --------------------------------------------------------
125
126--
127-- Table structure for table `Runner`
128--
129
130CREATE TABLE `Runner` (
131 `RunnerID` int(5) NOT NULL,
132 `VolunteerID` int(5) DEFAULT NULL,
133 `Password` varchar(255) DEFAULT NULL,
134 `FirstName` varchar(255) DEFAULT NULL,
135 `LastName` varchar(255) DEFAULT NULL,
136 `Gender` varchar(10) DEFAULT NULL,
137 `DateOfBirth` date DEFAULT NULL,
138 `Email` varchar(255) DEFAULT NULL,
139 `Country` varchar(255) DEFAULT NULL,
140 `ProfilePicture` varchar(255) DEFAULT NULL
141) ENGINE=InnoDB DEFAULT CHARSET=utf8;
142
143--
144-- Dumping data for table `Runner`
145--
146
147INSERT INTO `Runner` (`RunnerID`, `VolunteerID`, `Password`, `FirstName`, `LastName`, `Gender`, `DateOfBirth`, `Email`, `Country`, `ProfilePicture`) VALUES
148(1, NULL, 'b123y321', 'Bobby', 'Wan', 'M', '1998-01-04', 'bobbywan1414@gmail.com', 'Hong Kong', NULL),
149(2, NULL, 'qwerqwer', 'Kenneth', 'Wan', 'M', '1990-11-08', 'Kenneth@gmail.com', 'Hong Kong', NULL),
150(3, NULL, 'passpass', 'pass0101', 'pass0101', 'M', '2017-06-12', 'pass01@gmail.com', 'Hong Kong', NULL),
151(4, NULL, 'poiuqwer', 'poipoi', 'poipoi', 'F', '1999-12-11', 'Tai@gmail.com', 'Taiwan', NULL),
152(5, NULL, 'qwerqwer', 'qwer', 'qwer', 'F', '1999-05-04', 'Tai@gmail.com', 'AZ', NULL),
153(6, NULL, 'b123y321', 'Bobby', 'Wan', 'M', '1998-01-04', 'bobbywan1414@gmail.com', 'Hong Kong', NULL),
154(7, NULL, 'b123y321', 'Bobby', 'Wan', 'M', '1998-01-04', 'bobbywan1414@gmail.com', 'Hong Kong', NULL),
155(8, NULL, 'b123y321', 'Bobby', 'Wan', 'M', '1998-01-04', 'bobbywan1414@gmail.com', 'Hong Kong', NULL);
156
157-- --------------------------------------------------------
158
159--
160-- Table structure for table `Sponsor`
161--
162
163CREATE TABLE `Sponsor` (
164 `SponsorID` int(5) NOT NULL,
165 `Password` varchar(255) DEFAULT NULL,
166 `FirstName` varchar(255) DEFAULT NULL,
167 `LastName` varchar(255) DEFAULT NULL,
168 `Company` varchar(255) DEFAULT NULL,
169 `Email` varchar(255) DEFAULT NULL
170) ENGINE=InnoDB DEFAULT CHARSET=utf8;
171
172--
173-- Dumping data for table `Sponsor`
174--
175
176INSERT INTO `Sponsor` (`SponsorID`, `Password`, `FirstName`, `LastName`, `Company`, `Email`) VALUES
177(1, 'sponsorsponsor', 'SponsorFirst', 'NewSLas', 'NewSCom', 'sponsor@gmail.com');
178
179-- --------------------------------------------------------
180
181--
182-- Table structure for table `SponsorRecord`
183--
184
185CREATE TABLE `SponsorRecord` (
186 `SponsorID` int(5) NOT NULL,
187 `CharityID` int(5) NOT NULL,
188 `RegID` int(5) NOT NULL,
189 `Amount` decimal(10,2) DEFAULT NULL,
190 `PaymentConfirmed` tinyint(1) DEFAULT '0'
191) ENGINE=InnoDB DEFAULT CHARSET=utf8;
192
193-- --------------------------------------------------------
194
195--
196-- Table structure for table `Volunteer`
197--
198
199CREATE TABLE `Volunteer` (
200 `VolunteerID` int(5) NOT NULL,
201 `Password` varchar(255) DEFAULT NULL,
202 `FirstName` varchar(255) DEFAULT NULL,
203 `LastName` varchar(255) DEFAULT NULL,
204 `Gender` varchar(10) DEFAULT NULL,
205 `Email` varchar(255) DEFAULT NULL
206) ENGINE=InnoDB DEFAULT CHARSET=utf8;
207
208--
209-- Dumping data for table `Volunteer`
210--
211
212INSERT INTO `Volunteer` (`VolunteerID`, `Password`, `FirstName`, `LastName`, `Gender`, `Email`) VALUES
213(1, 'volunteer', 'volunteerFirst', 'Last', 'F', 'volunteer@gmail.com');
214
215--
216-- Indexes for dumped tables
217--
218
219--
220-- Indexes for table `Administrator`
221--
222ALTER TABLE `Administrator`
223 ADD PRIMARY KEY (`AdministratorID`);
224
225--
226-- Indexes for table `Charity`
227--
228ALTER TABLE `Charity`
229 ADD PRIMARY KEY (`CharityID`);
230
231--
232-- Indexes for table `Event`
233--
234ALTER TABLE `Event`
235 ADD PRIMARY KEY (`EventID`);
236
237--
238-- Indexes for table `EventRegister`
239--
240ALTER TABLE `EventRegister`
241 ADD PRIMARY KEY (`RegID`),
242 ADD KEY `FKEventRegis439203` (`EventID`),
243 ADD KEY `FKEventRegis634186` (`RaceKitID`),
244 ADD KEY `FKEventRegis640020` (`RunnerID`);
245
246--
247-- Indexes for table `RaceKitChoice`
248--
249ALTER TABLE `RaceKitChoice`
250 ADD PRIMARY KEY (`RaceKitID`),
251 ADD KEY `FKRaceKitCho896145` (`EventID`);
252
253--
254-- Indexes for table `Runner`
255--
256ALTER TABLE `Runner`
257 ADD PRIMARY KEY (`RunnerID`),
258 ADD KEY `FKRunner505187` (`VolunteerID`);
259
260--
261-- Indexes for table `Sponsor`
262--
263ALTER TABLE `Sponsor`
264 ADD PRIMARY KEY (`SponsorID`);
265
266--
267-- Indexes for table `SponsorRecord`
268--
269ALTER TABLE `SponsorRecord`
270 ADD PRIMARY KEY (`SponsorID`,`CharityID`,`RegID`),
271 ADD KEY `FKSponsorRec723837` (`RegID`),
272 ADD KEY `FKSponsorRec587573` (`CharityID`),
273 ADD KEY `FKSponsorRec216661` (`SponsorID`);
274
275--
276-- Indexes for table `Volunteer`
277--
278ALTER TABLE `Volunteer`
279 ADD PRIMARY KEY (`VolunteerID`);
280
281--
282-- AUTO_INCREMENT for dumped tables
283--
284
285--
286-- AUTO_INCREMENT for table `Administrator`
287--
288ALTER TABLE `Administrator`
289 MODIFY `AdministratorID` int(5) NOT NULL AUTO_INCREMENT;
290--
291-- AUTO_INCREMENT for table `Charity`
292--
293ALTER TABLE `Charity`
294 MODIFY `CharityID` int(5) NOT NULL AUTO_INCREMENT;
295--
296-- AUTO_INCREMENT for table `Event`
297--
298ALTER TABLE `Event`
299 MODIFY `EventID` int(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
300--
301-- AUTO_INCREMENT for table `EventRegister`
302--
303ALTER TABLE `EventRegister`
304 MODIFY `RegID` int(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
305--
306-- AUTO_INCREMENT for table `RaceKitChoice`
307--
308ALTER TABLE `RaceKitChoice`
309 MODIFY `RaceKitID` int(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
310--
311-- AUTO_INCREMENT for table `Runner`
312--
313ALTER TABLE `Runner`
314 MODIFY `RunnerID` int(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
315--
316-- AUTO_INCREMENT for table `Sponsor`
317--
318ALTER TABLE `Sponsor`
319 MODIFY `SponsorID` int(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
320--
321-- AUTO_INCREMENT for table `Volunteer`
322--
323ALTER TABLE `Volunteer`
324 MODIFY `VolunteerID` int(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
325--
326-- Constraints for dumped tables
327--
328
329--
330-- Constraints for table `EventRegister`
331--
332ALTER TABLE `EventRegister`
333 ADD CONSTRAINT `FKEventRegis439203` FOREIGN KEY (`EventID`) REFERENCES `Event` (`EventID`) ON DELETE CASCADE ON UPDATE CASCADE,
334 ADD CONSTRAINT `FKEventRegis634186` FOREIGN KEY (`RaceKitID`) REFERENCES `RaceKitChoice` (`RaceKitID`) ON DELETE CASCADE ON UPDATE CASCADE,
335 ADD CONSTRAINT `FKEventRegis640020` FOREIGN KEY (`RunnerID`) REFERENCES `Runner` (`RunnerID`) ON DELETE CASCADE ON UPDATE CASCADE;
336
337--
338-- Constraints for table `RaceKitChoice`
339--
340ALTER TABLE `RaceKitChoice`
341 ADD CONSTRAINT `FKRaceKitCho896145` FOREIGN KEY (`EventID`) REFERENCES `Event` (`EventID`) ON DELETE CASCADE ON UPDATE CASCADE;
342
343--
344-- Constraints for table `Runner`
345--
346ALTER TABLE `Runner`
347 ADD CONSTRAINT `FKRunner505187` FOREIGN KEY (`VolunteerID`) REFERENCES `Volunteer` (`VolunteerID`) ON DELETE CASCADE ON UPDATE CASCADE;
348
349--
350-- Constraints for table `SponsorRecord`
351--
352ALTER TABLE `SponsorRecord`
353 ADD CONSTRAINT `FKSponsorRec216661` FOREIGN KEY (`SponsorID`) REFERENCES `Sponsor` (`SponsorID`) ON DELETE CASCADE ON UPDATE CASCADE,
354 ADD CONSTRAINT `FKSponsorRec587573` FOREIGN KEY (`CharityID`) REFERENCES `Charity` (`CharityID`) ON DELETE CASCADE ON UPDATE CASCADE,
355 ADD CONSTRAINT `FKSponsorRec723837` FOREIGN KEY (`RegID`) REFERENCES `EventRegister` (`RegID`) ON DELETE CASCADE ON UPDATE CASCADE;
356--
357-- Database: `petdb`
358--
359CREATE DATABASE IF NOT EXISTS `petdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
360USE `petdb`;
361
362-- --------------------------------------------------------
363
364--
365-- Table structure for table `Appointment`
366--
367
368CREATE TABLE `Appointment` (
369 `bDate` date NOT NULL,
370 `bTime` varchar(5) NOT NULL,
371 `cPhone` varchar(15) NOT NULL,
372 `pName` varchar(30) NOT NULL,
373 `pService` varchar(50) NOT NULL
374) ENGINE=InnoDB DEFAULT CHARSET=utf8;
375
376--
377-- Dumping data for table `Appointment`
378--
379
380INSERT INTO `Appointment` (`bDate`, `bTime`, `cPhone`, `pName`, `pService`) VALUES
381('2312-03-12', '12:21', '12341234', 'Ro', 'Parasite prevention'),
382('2312-03-12', '12:21', '12341234', 'Ro', 'Parasite prevention'),
383('2312-03-12', '12:21', '12341234', 'Ro', 'Parasite prevention'),
384('2312-03-12', '12:21', '12341234', 'Ro', 'Parasite prevention'),
385('2312-03-12', '12:21', '12341234', 'Ro', 'Parasite prevention'),
386('2312-03-12', '12:21', '12341234', 'Ro', 'Parasite prevention');