· 8 years ago · Oct 24, 2017, 10:34 AM
1/* Test Data Refresh SQL Script for Release 1*/
2/* Directions: Copy the statements below into mySQL Workbench to add the data into the table in the DEFAULT SCHEMA. */
3/* BE SURE TO SET THE DEFAULT SCHEMA TO FESTIVALDATABASE in the SQL EDITOR*/
4
5DELETE FROM EVENTSIGNUP WHERE SIGNUPID >=1;
6DELETE FROM EVENT WHERE EVENTID >=1;
7DELETE FROM VISITOR WHERE VISITORID >=1;
8
9INSERT INTO EVENT (EVENTID,NAME,DESCRIPTION,PLACES,DURATION,EVENTTYPE,SEATSAVAILABLE)
10VALUES ('1001','Rose Parade','Floats, Music and More','Rose Garden','0900-1400','Tour','4000'),
11 ('1002','NCCL Semi Finals','North Division Cricket Match','New Codington Stadium','1800-2200','Sporting Event','5000'),
12 ('1003','Fireworks Show','Spectacular Display at River','New Codington River Walk','1900-2000','Attraction','1000'),
13 ('1004','Garden Tour','Tour the Beautiful Rose Garden','Rose Garden','1000-1400','Tour','1'),
14 ('1005','NCCL Championship','North vs. South Cricket Match','New Codington Stadium','1800-2200','Sporting Event','5000'),
15 ('1006','Pavlova - All World Tour','Pavlova in Concert','New Codington Music Hall','1930-2130','Concert','1000')
16;
17
18
19
20
21INSERT INTO VISITOR (VISITORID,USERNAME,PASSWORD,FIRSTNAME,LASTNAME,EMAIL,PHONENUMBER)
22VALUES ('1001','bsmith','password','Bob','Smith','bsmith@email.com','748937487'),
23 ('1002','npatel','password','Nitin','Patel','npatel@email.com','3392382'),
24 ('1003','jjones','password','James','Jones','jjones@email.com','2342343244'),
25 ('1004','jfrancois','password','Jacques','Francois','jfrancois@email.com','1234567890'),
26 ('1005','rkreiger','password','Robert','Kreiger','rkreiger@email.com','49872938'),
27 ('1006','ylee','password','Yi-Hui','Lee','ylee@email.com','21239393')
28;
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43CREATE DATABASE `festivaldatabase`;
44
45USE `festivaldatabase`;
46
47DROP TABLE IF EXISTS `festivaldatabase`.`event`;
48CREATE TABLE `festivaldatabase`.`event` (
49 `eventid` int(11) NOT NULL,
50 `name` varchar(40) NOT NULL,
51 `description` varchar(40) NOT NULL,
52 `places` varchar(40) NOT NULL,
53 `duration` varchar(20) DEFAULT NULL,
54 `eventtype` varchar(40) DEFAULT NULL,
55 `seatsavailable` int(11) DEFAULT NULL,
56 PRIMARY KEY (`eventid`)
57);
58
59DROP TABLE IF EXISTS `festivaldatabase`.`visitor`;
60CREATE TABLE `festivaldatabase`.`visitor` (
61 `visitorid` int(11) NOT NULL AUTO_INCREMENT,
62 `username` varchar(40) NOT NULL,
63 `password` varchar(40) NOT NULL,
64 `firstname` varchar(40) NOT NULL,
65 `lastname` varchar(40) NOT NULL,
66 `email` varchar(50) DEFAULT NULL,
67 `phonenumber` varchar(15) DEFAULT NULL,
68 `address` varchar(30) DEFAULT NULL,
69 PRIMARY KEY (`visitorid`)
70);
71
72DROP TABLE IF EXISTS `festivaldatabase`.`eventsignup`;
73CREATE TABLE `festivaldatabase`.`eventsignup` (
74 `signupid` int(11) NOT NULL AUTO_INCREMENT,
75 `eventid` int(11) DEFAULT NULL,
76 `visitorid` int(11) DEFAULT NULL,
77 PRIMARY KEY (`signupid`),
78 KEY `eventid` (`eventid`),
79 KEY `visitorid` (`visitorid`),
80 CONSTRAINT `eventsignup_ibfk_2` FOREIGN KEY (`visitorid`) REFERENCES `visitor` (`visitorid`),
81 CONSTRAINT `eventsignup_ibfk_3` FOREIGN KEY (`eventid`) REFERENCES `event` (`eventid`)
82);
83
84
85insert into EVENT(eventid,name,description,places,duration,eventtype,seatsavailable)
86values(1001,'Rose Parade','Studies in Botany','New Codington Rose Garden','0900 - 1300','Concert',50),
87(1002,'NCCL Cricket League Final','North Town vs. South Town','New Codington Stadium','1300 - 1600','Sporting Event',200),
88(1003,'Fireworks Show','Spectacular Display at River','PLACE3','2000 - 2200','',650);
89
90
91INSERT INTO VISITOR (VISITORID,USERNAME,PASSWORD,FIRSTNAME,LASTNAME,EMAIL,PHONENUMBER)
92VALUES ('1001','bsmith','password','Bob','Smith','bsmith@email.com','748937487'),
93 ('1002','npatel','password','Nitin','Patel','npatel@email.com','3392382'),
94 ('1003','jjones','password','James','Jones','jjones@email.com','2342343244'),
95 ('1004','jfrancois','password','Jacques','Francois','jfrancois@email.com','1234567890'),
96 ('1005','rkreiger','password','Robert','Kreiger','rkreiger@email.com','49872938'),
97 ('1006','ylee','password','Yi-Hui','Lee','ylee@email.com','21239393')
98;