-
Notifications
You must be signed in to change notification settings - Fork 0
/
covidDatabaseFinal_Backup.sql
113 lines (76 loc) · 7.48 KB
/
covidDatabaseFinal_Backup.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
CREATE DATABASE `covidtracingapp`;
USE `covidtracingapp`;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`userID` int NOT NULL AUTO_INCREMENT,
`given_name` varchar(30) DEFAULT NULL,
`surname` varchar(30) DEFAULT NULL,
`street_number` varchar(15) DEFAULT NULL,
`street_name` varchar(30) DEFAULT NULL,
`surburb` varchar(30) DEFAULT NULL,
`state` varchar(30) DEFAULT NULL,
`postcode` varchar(15) DEFAULT NULL,
`contact_number` varchar(20) DEFAULT NULL,
`date_of_birth` date DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
`password` varchar(260) DEFAULT NULL,
`isUser` tinyint(1) DEFAULT NULL,
`isVenueManager` tinyint(1) DEFAULT NULL,
`isHealthOfficial` tinyint(1) DEFAULT NULL,
`emailNotification` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`userID`)
)ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES (1,'Aqmal','Pulle','10','Pelagic road','Seaford meadows','SA','5169','0435753189','1999-12-15','[email protected]','d74ff0ee8da3b9806b18c877dbf29bbde50b5bd8e4dad7a3a725000feb82e8f1',0,0,0,1),(2,'haard','shah','130','Ladywood Road','Modbury ','SA','5092','0405673898','2002-10-02','[email protected]','d74ff0ee8da3b9806b18c877dbf29bbde50b5bd8e4dad7a3a725000feb82e8f1',0,1,0,1),(3,'john','depp','64','mooringe ave','north plympton','SA','5037','0433337771','2000-12-19','[email protected]','d74ff0ee8da3b9806b18c877dbf29bbde50b5bd8e4dad7a3a725000feb82e8f1',0,1,1,1),(4,'smith','anthony','48','elston street','brooklyn park','SA','5032','0411773388','1999-03-15','[email protected]','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8',0,1,0,0),(5,'Peter','anthony','10','cudmore terrace','marleston','SA','5033','0485942019','1980-05-12','[email protected]','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8',0,0,0,1),(6,'wiseley','lee','36','ashford road','keswick','SA','5035','0444444450','1994-06-11','[email protected]','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8',0,1,0,1),(7,'person','noname','10','ashford road','keswick','SA','5035','0473728219','1996-06-11','[email protected]','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8',0,1,0,1),(8,'johnson','speak','51','Gage Street','St Morris ','SA','5068','0437729392','2003-11-11','[email protected]','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8',0,1,0,1),(9,'Lally','Singh','12','cudmore terrace','Marleston','VIC','5032','0456377279','1997-06-17','[email protected]','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8',0,1,0,1),(10,'vinay','kumar','10','pelagic street','seaford meadows','SA','5444','0463678910','1994-09-20','[email protected]','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8',0,1,0,1),(11,'Jack','White','23','Grove Street','Kilburn','VIC','5089','0432123456','1990-10-11','[email protected]','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8',0,1,0,1),(12,'Chelsea','Baerwald','40','first street','Melbourne','SA','4000','0473747482','2001-06-07','[email protected]','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8',0,0,0,1),(13,'Jake','Wilder','34','Street Street','Suburb','SA','5000','0490890789','2000-12-10','[email protected]','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8',0,1,0,1),(14,'Shae','Haggis','9b','Britton Avenue','Tranmere','SA','5073','0468585053','1999-05-12','[email protected]','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8',0,0,0,1),(16,'Vinay','Kumar','10','second street','adelaide','SA','5033','0444444','2021-06-03','[email protected]','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8',0,1,0,0),(19,'Haard','Shah','130 ','Grange Road','Modbury Heights','SA','5092','0405123456','2002-10-02','[email protected]','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8',0,0,0,1),(20,'Haard','Shah','130 ','Grange Road','Modbury Heights','SA','5092','0405123456','2002-10-02','[email protected]','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8',0,0,0,1);
UNLOCK TABLES;
DROP TABLE IF EXISTS `venue`;
CREATE TABLE `venue` (
`venueID` int NOT NULL AUTO_INCREMENT,
`venue_name` varchar(50) DEFAULT NULL,
`venue_manager` int DEFAULT NULL,
`capacity` int DEFAULT NULL,
`street_number` varchar(15) DEFAULT NULL,
`street_name` varchar(30) DEFAULT NULL,
`suburb` varchar(30) DEFAULT NULL,
`state` varchar(20) DEFAULT NULL,
`postcode` varchar(15) DEFAULT NULL,
`phone_number` varchar(20) DEFAULT NULL,
PRIMARY KEY (`venueID`),
KEY `venue_manager` (`venue_manager`),
CONSTRAINT `venue_ibfk_1` FOREIGN KEY (`venue_manager`) REFERENCES `users` (`userID`)
) ENGINE=InnoDB AUTO_INCREMENT=1012 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
LOCK TABLES `venue` WRITE;
INSERT INTO `venue` VALUES (1000,'Tomato',2,50,'12','Anzac HWY','Everard','SA','5035','0838193929'),(1001,'Fisher',3,12,'70','Fisher Pl','Mile end','SA','5031','0800000000'),(1002,'Ship CO',4,24,'190','gouger street','adelaide','SA','5000','0837472539'),(1003,'KFC',6,30,'251','Henley Beach Rd','Torrensville','SA','5031','0882341695'),(1004,'Hungry Jacks',7,42,'128 ','Marion Rd','West Richmond','SA','5033','0883523510'),(1005,'House LTD',8,54,'51','Gage St','St Morris','SA','5068','0847463627'),(1006,'Mangal Sweets',9,12,'10/257 ','North East Road','Hampstead Gardens','SA','5086','0420509442'),(1007,'greek on the lake',10,13,'149','Brebner Dr','West Lakes','SA','5031','0411777919'),(1008,'The Elephant British Pub',11,50,'1','Cinema Place','Adelaide','SA','5000','81119999'),(1009,'The Warradale',13,100,'234','Diagonal Road','Warradale','SA','5046','89127654'),(1011,'home club ',16,3,'22','kanbara street','flinders park','SA','5010','084343434');
UNLOCK TABLES;
DROP TABLE IF EXISTS `checkins`;
CREATE TABLE `checkins` (
`checkinID` int NOT NULL AUTO_INCREMENT,
`checkindate` date DEFAULT NULL,
`checkintime` time DEFAULT NULL,
`userID` int DEFAULT NULL,
`venueID` int DEFAULT NULL,
PRIMARY KEY (`checkinID`),
KEY `userID` (`userID`),
KEY `venueID` (`venueID`),
CONSTRAINT `checkins_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `users` (`userID`),
CONSTRAINT `checkins_ibfk_2` FOREIGN KEY (`venueID`) REFERENCES `venue` (`venueID`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
LOCK TABLES `checkins` WRITE;
INSERT INTO `checkins` VALUES (1,'2021-06-13','14:08:41',12,1002),(2,'2021-06-13','14:09:10',12,1005),(3,'2021-06-13','14:09:15',12,1004),(4,'2021-06-13','14:09:20',12,1007),(5,'2021-06-13','14:09:59',12,1008),(6,'2021-06-13','14:14:51',12,1009),(13,'2021-06-13','14:25:06',14,1000),(15,'2021-06-13','14:25:44',14,1001),(16,'2021-06-13','14:25:50',14,1005);
UNLOCK TABLES;
DROP TABLE IF EXISTS `hotspots`;
CREATE TABLE `hotspots` (
`hotspotID` int NOT NULL AUTO_INCREMENT,
`venueID` int DEFAULT NULL,
`hoID` int DEFAULT NULL,
`start_date` date DEFAULT NULL,
`start_time` time DEFAULT NULL,
PRIMARY KEY (`hotspotID`),
KEY `hoID` (`hoID`),
KEY `venueID` (`venueID`),
CONSTRAINT `hotspots_ibfk_1` FOREIGN KEY (`hoID`) REFERENCES `users` (`userID`),
CONSTRAINT `hotspots_ibfk_2` FOREIGN KEY (`venueID`) REFERENCES `venue` (`venueID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
LOCK TABLES `hotspots` WRITE;
INSERT INTO `hotspots` VALUES (2,1005,3,'2021-06-13','00:24:00'),(4,1007,3,'2021-06-08','05:42:00'),(5,1004,3,'2021-06-08','01:59:00'),(6,1008,3,'2021-06-15','08:23:00');
UNLOCK TABLES;