· 9 years ago · Aug 29, 2017, 06:20 PM
1DROP schema IF EXISTS prototype;
2create schema prototype;
3USE prototype;
4
5DROP TABLES IF EXISTS eventsTimes, eventsImage, tagReferences, tag, likedBusinesses, recommendationContent, recommendations, relationBusinessANDReview, review, relationUserNotification, relationBusinessUserNotification, notifications, businessImageContent,eventBooking, dealBooking, events, package, deals, businessUser, categories, user;
6
7CREATE TABLE `user` (
8 `userName` VARCHAR(255) NOT NULL UNIQUE,
9 `firstName` VARCHAR(255) NOT NULL,
10 `lastName` VARCHAR(255) NOT NULL,
11 `phoneNumber` VARCHAR(255) NOT NULL,
12 `email` VARCHAR(255) NOT NULL,
13 `postCode` VARCHAR(100),
14 `passWord` VARCHAR(255) NOT NULL,
15 `profileImageURL` VARCHAR(255) NOT NULL,
16 `admin` boolean DEFAULT FALSE,
17 `userID` int(10) UNIQUE NOT NULL AUTO_INCREMENT,
18 `dateOfBirth` date,
19 CONSTRAINT `User_pk` PRIMARY KEY (`userID`)
20);
21
22CREATE TABLE `categories`(
23 `categoriesID` int(10) NOT NULL UNIQUE AUTO_INCREMENT,
24 `category` VARCHAR(255) NOT NULL,
25 CONSTRAINT `Categories_Pk` PRIMARY KEY (`categoriesID`)
26);
27
28CREATE TABLE `businessUser` (
29 `userName` VARCHAR(255) NOT NULL UNIQUE,
30 `firstName` VARCHAR(255) NOT NULL,
31 `lastName` VARCHAR(255) NOT NULL,
32 `phoneNumber` VARCHAR(255) NOT NULL,
33 `email` VARCHAR(255) NOT NULL UNIQUE,
34 `postCode` VARCHAR(100),
35 `password` VARCHAR(255) NOT NULL,
36 `profileImageURL` VARCHAR(255) NOT NULL,
37 `businessID` int(10) UNIQUE NOT NULL AUTO_INCREMENT,
38 `businessName` VARCHAR(255) NOT NULL,
39 `street` VARCHAR(255) NOT NULL,
40 `number` int(4) NOT NULL,
41 `unit` VARCHAR(255),
42 `suburb` VARCHAR(255) NOT NULL,
43 `categoryID` int(10) NOT NULL,
44 `businessDescription` VARCHAR(255) NOT NULL,
45 `businessOpen` TIME NOT NULL,
46 `businessClose` TIME NOT NULL,
47 `rating` int(3) default 0,
48 `businessViews` int default 0,
49 `latitude` VARCHAR(255),
50 `longitude` VARCHAR(255),
51 CONSTRAINT `Business_pk` PRIMARY KEY (`businessID`)
52);
53
54CREATE TABLE `deals` (
55 `dealID` int(10) NOT NULL AUTO_INCREMENT,
56 `businessID` int(10) NOT NULL,
57 `dealTitle` VARCHAR(255) NOT NULL,
58 `startDate` datetime NOT NULL,
59 `endDate` datetime NOT NULL,
60 `validDuration` int(3) NOT NULL,
61 `dealDescription` VARCHAR(255) NOT NULL,
62 `dealImageURL` VARCHAR(255) NOT NULL,
63 `activeDeal` boolean DEFAULT TRUE,
64 `price` double NOT NULL,
65 `oldPrice` double NOT NULL,
66 `totalPurchased` int DEFAULT 0,
67 `totalViews` int DEFAULT 0,
68 CONSTRAINT `Deal_pk` PRIMARY KEY (`dealID`),
69 CONSTRAINT `Business_fk` FOREIGN KEY (`businessID`) REFERENCES `businessUser` (`businessID`) ON DELETE NO ACTION ON UPDATE CASCADE
70);
71
72CREATE TABLE `package`(
73 `packageID` int(10) NOT NULL AUTO_INCREMENT,
74 `userID` int(10) NOT NULL,
75 CONSTRAINT `Package_pk` PRIMARY KEY (`packageID`),
76 CONSTRAINT `User_fk` FOREIGN KEY (`userID`) REFERENCES `user` (`userID`) ON DELETE NO ACTION ON UPDATE CASCADE
77);
78
79CREATE TABLE `events`(
80 `eventID` int(10) NOT NULL AUTO_INCREMENT,
81 `businessID` int(10) NOT NULL,
82 `title` VARCHAR(255) NOT NULL,
83 `categoriesID` int(10) NOT NULL,
84 `eventDescription` VARCHAR(255) NOT NULL,
85 `startDate` date NOT NULL,
86 `endDate` date NOT NULL,
87 `price` double DEFAULT 0,
88 `oldPrice` double DEFAULT 0,
89 `activeEvent` boolean DEFAULT TRUE,
90 `totalViews` int DEFAULT 0,
91 CONSTRAINT `Event_Pk` PRIMARY KEY (`eventID`),
92 CONSTRAINT `categories_fk` FOREIGN KEY (`categoriesID`) REFERENCES `categories` (`categoriesID`) ON DELETE NO ACTION ON UPDATE CASCADE,
93 CONSTRAINT `business_fk5` FOREIGN KEY (`businessID`) REFERENCES `businessUser` (`businessID`) ON DELETE NO ACTION ON UPDATE CASCADE
94);
95
96CREATE TABLE `eventsTimes`(
97 `eventID` int(10) NOT NULL,
98 `startTime` TIME NOT NULL,
99 `endTime` TIME NOT NULL,
100 `daysDate` DATE,
101 CONSTRAINT `eventTimes_pk` PRIMARY KEY (`eventID`, `daysDate`),
102 CONSTRAINT `events_fk` FOREIGN KEY (`eventID`) REFERENCES `events` (`eventID`) ON DELETE NO ACTION ON UPDATE CASCADE
103);
104
105CREATE TABLE `eventsImage` (
106 `eventID` int(10) NOT NULL,
107 `eventImageURL` VARCHAR(255),
108 `profilePicture` boolean DEFAULT FALSE,
109 CONSTRAINT `eventImage_pk` PRIMARY KEY (`eventID`),
110 CONSTRAINT `events_fk2` FOREIGN KEY (`eventID`) REFERENCES `events` (`eventID`) ON DELETE NO ACTION ON UPDATE CASCADE
111);
112
113CREATE TABLE `dealBooking` (
114 `packageID` int(10) NOT NULL,
115 `bookingID` int(10) NOT NULL AUTO_INCREMENT,
116 `businessID` int(10) NOT NULL,
117 `dealID` int(10) NOT NULL,
118 `startTime` TIME,
119 `endTime` TIME,
120 `date` DATE ,
121 `numberOfAdults` int,
122 `numberOfChildren` int,
123 `bookingStatus` int(1) DEFAULT 0,
124 `valid` boolean DEFAULT TRUE,
125 `qrCodeURL` VARCHAR(255),
126 CONSTRAINT `DealBooking_pk` PRIMARY KEY (`bookingID`),
127 CONSTRAINT `Deal_fk` FOREIGN KEY (`dealID`) REFERENCES `deals` (`dealID`) ON DELETE NO ACTION ON UPDATE CASCADE,
128 CONSTRAINT `Package_fk` FOREIGN KEY (`packageID`) REFERENCES `package` (`packageID`) ON DELETE NO ACTION ON UPDATE CASCADE,
129 CONSTRAINT `Business_fk8` FOREIGN KEY (`businessID`) REFERENCES `businessUser` (`businessID`) ON DELETE NO ACTION ON UPDATE CASCADE
130);
131
132CREATE TABLE `eventBooking` (
133 `bookingID` int(10) NOT NULL AUTO_INCREMENT,
134 `packageID` int(10) NOT NULL,
135 `businessID` int(10) NOT NULL,
136 `eventID` int(10) NOT NULL,
137 `startTime` TIME,
138 `endTime` TIME,
139 `date` DATE ,
140 `bookingValid` boolean DEFAULT FALSE,
141 `valid` boolean DEFAULT TRUE,
142 `qrCodeURL` VARCHAR(255) ,
143 CONSTRAINT `EventsBooking_pk` PRIMARY KEY (`bookingID`),
144 CONSTRAINT `Event_fk` FOREIGN KEY (`eventID`) REFERENCES `events` (`eventID`) ON DELETE NO ACTION ON UPDATE CASCADE,
145 CONSTRAINT `Package_fk2` FOREIGN KEY (`packageID`) REFERENCES `package` (`packageID`) ON DELETE NO ACTION ON UPDATE CASCADE,
146 CONSTRAINT `Business_fk10` FOREIGN KEY (`businessID`) REFERENCES `businessUser` (`businessID`) ON DELETE NO ACTION ON UPDATE CASCADE
147);
148
149CREATE TABLE `businessImageContent` (
150 `businessID` int(10) NOT NULL,
151 `businessImageURL` VARCHAR(255),
152 CONSTRAINT `BuinessImage_pk` PRIMARY KEY (`businessID`),
153 CONSTRAINT `Business_fk9` FOREIGN KEY (`businessID`) REFERENCES `businessUser` (`businessID`) ON DELETE NO ACTION ON UPDATE CASCADE
154);
155
156CREATE TABLE `notifications`(
157 `notificationID` int(10) NOT NULL AUTO_INCREMENT,
158 `description` VARCHAR(255) NOT NULL,
159 `title` VARCHAR(255) NOT NULL,
160 `link` VARCHAR(255) NOT NULL,
161 CONSTRAINT `Notification_Pk` PRIMARY KEY (`notificationID`)
162);
163
164CREATE TABLE `relationUserNotification`(
165 `notificationID` int(10) NOT NULL,
166 `userID` int(10) NOT NULL,
167 `viewed` boolean DEFAULT FALSE,
168 `type` int NOT NULL,
169 CONSTRAINT `Notification_fk` FOREIGN KEY (`notificationID`) REFERENCES `notifications` (`notificationID`) ON DELETE NO ACTION ON UPDATE CASCADE,
170 CONSTRAINT `User_fk1` FOREIGN KEY (`userID`) REFERENCES `user` (`userID`) ON DELETE NO ACTION ON UPDATE CASCADE
171);
172
173CREATE TABLE `relationBusinessUserNotification`(
174 `notificationID` int(10) NOT NULL,
175 `businessID` int(10) NOT NULL,
176 `type` int NOT NULL,
177 CONSTRAINT `Notification_fk3` FOREIGN KEY (`notificationID`) REFERENCES `notifications` (`notificationID`) ON DELETE NO ACTION ON UPDATE CASCADE,
178 CONSTRAINT `Business_fk11` FOREIGN KEY (`businessID`) REFERENCES `businessUser` (`businessID`) ON DELETE NO ACTION ON UPDATE CASCADE
179);
180
181CREATE TABLE `review`(
182 `reviewID` int(10) NOT NULL AUTO_INCREMENT,
183 `userID` int(10) NOT NULL,
184 `businessID` int(10) NOT NULL,
185 `title` VARCHAR(255) NOT NULL,
186 `rating` int(10) NOT NULL,
187 `details` VARCHAR(255) NOT NULL,
188 `date` DATE NOT NULL,
189 `time` TIME NOT NULL,
190 CONSTRAINT `Review_Pk` PRIMARY KEY (`reviewID`),
191 CONSTRAINT `User_fk2` FOREIGN KEY (`userID`) REFERENCES `user` (`userID`) ON DELETE NO ACTION ON UPDATE CASCADE,
192 CONSTRAINT `Business_fk6` FOREIGN KEY (`businessID`) REFERENCES `businessUser` (`businessID`) ON DELETE NO ACTION ON UPDATE CASCADE
193);
194
195CREATE TABLE `recommendations`(
196 `recommendationID` int(10) NOT NULL AUTO_INCREMENT,
197 `userID` int(10) NOT NULL,
198 CONSTRAINT `Recommendation_Pk` PRIMARY KEY (`recommendationID`),
199 CONSTRAINT `User_fk3` FOREIGN KEY (`userID`) REFERENCES `user` (`userID`) ON DELETE NO ACTION ON UPDATE CASCADE
200);
201
202CREATE TABLE `recommendationContent`(
203 `recommendationID` int(10) NOT NULL,
204 `dealID` int(10) NOT NULL,
205 CONSTRAINT `Recommendation_fk` FOREIGN KEY (`recommendationID`) REFERENCES `recommendations` (`recommendationID`) ON DELETE NO ACTION ON UPDATE CASCADE,
206 CONSTRAINT `Deal_fk2` FOREIGN KEY (`dealID`) REFERENCES `deals` (`dealID`) ON DELETE NO ACTION ON UPDATE CASCADE
207);
208
209CREATE TABLE `likedBusinesses` (
210 `businessID` int(10) NOT NULL,
211 `userID` int(10) NOT NULL,
212 `liked` boolean NOT NULL,
213 CONSTRAINT `businessID_fk7` FOREIGN KEY (`businessID`) REFERENCES `businessUser` (`businessID`) ON DELETE NO ACTION ON UPDATE CASCADE,
214 CONSTRAINT `User_fk4` FOREIGN KEY (`userID`) REFERENCES `user` (`userID`) ON DELETE NO ACTION ON UPDATE CASCADE
215);
216
217CREATE TABLE `tag`(
218 `tagID` int(10) NOT NULL UNIQUE,
219 `tag` VARCHAR(255) NOT NULL,
220 CONSTRAINT `Tag_Pk` PRIMARY KEY (`tagID`)
221);
222
223CREATE TABLE `tagReferences`(
224 `tagID` int(10) NOT NULL UNIQUE,
225 `businessID` int(10) NOT NULL,
226 CONSTRAINT `businessTags_Pk` PRIMARY KEY (`tagID`),
227 CONSTRAINT `Business_fk7` FOREIGN KEY (`businessID`) REFERENCES `businessUser` (`businessID`) ON DELETE NO ACTION ON UPDATE CASCADE
228);
229
230--------------- Dummy data ------------------
231
232INSERT INTO User (userName, firstName, lastName, phoneNumber, email, postCode, passWord, profileImageURL, admin) VALUES
233('admin', 'newcastle', 'connect', '0405656544', 'newcastleConnect@gmail.com', '2245', 'password', 'http://www.newcastleConnect.com/img/src/23', TRUE),
234('swagmasta', 'alex', 'perry', '0496858467', 'swagGanga@hotmail.com', '2234', 'thicc', 'http://www.google.com/eggs', FALSE),
235('rich1122', 'richard', 'belling', '49567674', 'r1chardiscool@hotmail.com', '2456','richisdabest', 'none', FALSE);
236
237INSERT INTO categories(category) VALUES
238('food'),
239('experience'),
240('accommodation'),
241('transport');
242
243INSERT INTO businessUser (userName, firstName, lastName, phoneNumber, email, postCode, password, profileImageURL, businessName, street, number, unit, suburb, categoryID, businessDescription, businessOpen, businessClose,latitude, longitude, rating) VALUES
244 ('johnstan', 'john', 'stan', '49565643', 'whiteblueresturant@hotmail.com', '2332', 'white4345', 'http://whiteblueresturant.com/img', 'White Blue Lagoon', 'stanford', '76', 'none', 'newcastle', '1' , 'very yummy food!', '09:00:00', '21:00:00', '-32.434', '45.43', 70);
245
246INSERT INTO businessUser (userName, firstName, lastName, phoneNumber, email, postCode, password, profileImageURL, businessName, street, number, unit, suburb, categoryID, businessDescription, businessOpen, businessClose,latitude, longitude) VALUES
247 ('rafiel', 'rafiel', 'eldamos', '49323235', 'bronosresturant@hotmail.com', '2354', 'eldamos12', 'http://bronos.com/img/src/banner', 'Bronos', 'hunter', '234', 'none', 'newcastle', '1', 'Authentic food for the family! Specials every monday!', '10:00:00', '23:30:00', '32.33', '43.3443'),
248 ('alex', 'alex', 'meron', '493223', 'skyfallINQ@gmail.com', '2376', 'falldown11', 'http:/experiencesky.com/re3r33ff3/e2ddf2f2/e2we2e', 'businessName', 'meron', '5', 'none', 'newcastle', '2', 'Want to do sky diving? Want to experince a thrill?', '07:00:00', '18:00:00', '13.54', '76.43');
249
250INSERT INTO businessImageContent (businessID, businessImageURL) VALUES
251 (2, 'http://bronos.com/img/se3df'),
252 (3,'http:/experiencesky.com/re3r33ff3/f32ff/cache'),
253 (1,'http://whiteblueresturant.com/img/d23d2');
254
255INSERT INTO Deals (businessID, dealTitle, startDate, endDate, validDuration, dealDescription, dealImageURL, activeDeal, price, oldPrice) VALUES
256 ('1', 'Sunday Special', '2017-06-25 06:00:00', '2017-06-25 20:00:00', 10,'$10 fish and chips deal!', 'http://www.whiteblueresturant.com/dealsImg', TRUE,10, 15),
257 ('1', 'Cheap Tuesdays', '2017-06-23 12:00:00', '2017-06-23 18:00:00', 999,'Tuesday lunch specials', 'http://bronos.com/deals/src/', TRUE,16,20),
258 ('3', 'Weekend Deals!', '2017-06-20 00:00:00', '2017-06-23 00:00:00', 50,'Weekend thrills are now cheaper for a limited time!', 'http:/experiencesky.com/32rf33f2/e2ddf2f2/', TRUE,23, 30);
259
260INSERT INTO Package (userID) VALUES
261 ('1'),
262 ('2'),
263 ('3');
264
265INSERT INTO Notifications (description, title, link) VALUES
266('Welcome To Newcastle Connections, We are happy that you have deceided to join in the masses and become a member. experience newcastle greatest deals', 'Welcome New User', " "),
267('Your package has been fully Confirmed', 'Confirmation', ' ');
268
269INSERT INTO RelationUserNotification (notificationID, userID, type) VALUES
270(1, 1, 0),
271(2, 1, 1),
272(1, 2, 0),
273(1, 3, 0);
274
275INSERT INTO relationBusinessUserNotification(notificationID, businessID, type) VALUES
276( 1, 1, 0);
277
278INSERT INTO review (businessID, userID, title, rating, details, date, time) VALUES
279 ('1','1', 'The best!', '8', 'One of the best places I have ever eaten!', '2017-06-20', '11:31:00');
280
281INSERT INTO Recommendations(UserID) VALUES
282 ('1');
283
284INSERT INTO RecommendationContent(recommendationID, dealID) VALUES
285 ('1', '1');
286
287INSERT INTO events(businessID, title, categoriesID, eventDescription, startDate, endDate, price, oldPrice) VALUES
288 ('1', 'The Best event of all days', '1', 'This is a tribute to the best event in the world', '2017-06-10 ', '2017-06-13 ', 150.00, 100);
289
290INSERT INTO eventstimes(eventID, startTime, endTime, daysDate) VALUES
291 ('1', '08:00:00', '22:30:00', '2017-06-10'),
292 ('1', '08:00:00', '22:30:00', '2017-06-11'),
293 ('1', '08:00:00', '22:30:00', '2017-06-12'),
294 ('1', '08:00:00', '22:30:00', '2017-06-13');
295
296INSERT INTO eventsImage(eventID,eventImageURL,profilePicture) VALUES
297 ('1', 'testing url', true);