-
Notifications
You must be signed in to change notification settings - Fork 0
/
TableCreation.sql
159 lines (138 loc) · 6.18 KB
/
TableCreation.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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
create Database if not exists heroku_efc788d1dc18da4;
create table if not exists heroku_efc788d1dc18da4.USER(
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
Email varchar(255),
Password varchar(255) NOT NULL,
FName varchar(255),
MName varchar(255),
LName varchar(255));
INSERT INTO heroku_efc788d1dc18da4.USER (`ID`, `Email`, `Password`, `FName`, `MName`, `LName`) VALUES
(1, '[email protected]', 123, 'Admin', 'Aditos', 'Adminson');
create table if not exists heroku_efc788d1dc18da4.ADMIN (
ID int NOT NULL PRIMARY KEY,
FOREIGN KEY (ID) REFERENCES USER(ID)
ON DELETE CASCADE
ON UPDATE CASCADE);
USE heroku_efc788d1dc18da4;
INSERT INTO heroku_efc788d1dc18da4.ADMIN (`ID`) VALUES
(1);
create table if not exists heroku_efc788d1dc18da4.PATIENT (
ID int NOT NULL PRIMARY KEY,
Age int,
PhoneNumber varchar(255),
Address varchar(255),
PostalCode varchar(255),
Country varchar(255),
Province varchar(255),
City varchar(255),
FOREIGN KEY (ID) REFERENCES USER(ID)
ON DELETE CASCADE
ON UPDATE CASCADE);
create table if not exists heroku_efc788d1dc18da4.HEALTH_PROFILE (
ID int NOT NULL,
HealthNumber int NOT NULL PRIMARY KEY,
FOREIGN KEY (ID) REFERENCES USER(ID)
ON DELETE CASCADE
ON UPDATE CASCADE);
create table if not exists heroku_efc788d1dc18da4.VACCINE (
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
Name varchar(255),
Advisery varchar(255),
Description varchar(255),
URL varchar(255),
CreaterID int,
FOREIGN KEY (CreaterID) REFERENCES ADMIN(ID)
ON DELETE CASCADE
ON UPDATE CASCADE);
USE heroku_efc788d1dc18da4;
INSERT INTO heroku_efc788d1dc18da4.VACCINE (`ID`, `Name`, `Advisery`, `Description`, `URL`, `CreaterID`) VALUES
(1, 'COVID 19 - Moderna', '2 doses, 1 after another', '95% efficacy rate against COVID-19', 'https://www.hopkinsmedicine.org/-/media/images/health/1_-conditions/coronavirus/vaccine-hero.ashx', 1),
(2, 'CPSC 471', '1 dose', 'Cures all depression.', 'https://s7280.pcdn.co/wp-content/uploads/2016/06/database-blue.png', 1),
(3, 'Jensusm - Long&Long', '3 doses, 2 days apart. Jens will give presents!', 'Jensusm, the ever lasting illness. This is a severe condition that must be solved as soon as detected. Failure to acquire treatment for this will summon Jensusm to give you presents. DO NOT accept those presents! DO NOT EVER Accept them! REPEAT!!! DO NOT!', 'https://media3.s-nbcnews.com/i/newscms/2020_19/3339206/200506-covid-19-virus-al-1213_d926b22a18280ebb7babc72b5dce8a02.jpg', 1),
(4, 'DNA obliterator', 'No doses, its lethal, never take!', 'This is NOT a vaccine! This is simply murder! This is design to literally melt your DNA and cause total organ failure! It is a painful form of euthanasia, some may even call it torture. YOUR CELLS WILL LITERALLY MELT IF YOU TAKE THIS!', 'https://www.paho.org/sites/default/files/card/2021-03/covid-19-dna-1500x765.jpg', 1),
(5, 'COVID 19 - AstraZeneca', '2 doses, 1 week apart', 'Vaccine for COVID-19, from AstraZeneca!', 'https://globalnews.ca/wp-content/uploads/2021/03/CP120562673-e1617145038750.jpg?quality=85&strip=all&w=720', 1);
create table if not exists heroku_efc788d1dc18da4.CLINIC (
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
Name varchar(255),
Address varchar(255),
PostalCode varchar(255),
Country varchar(255),
Province varchar(255),
City varchar(255),
URL varchar(255),
CreaterID int,
FOREIGN KEY (CreaterID) REFERENCES ADMIN(ID)
ON DELETE CASCADE
ON UPDATE CASCADE);
USE heroku_efc788d1dc18da4;
INSERT INTO heroku_efc788d1dc18da4.CLINIC (`ID`, `Name`, `Address`, `PostalCode`, `Country`, `Province`, `City`, `URL`, `CreaterID`) VALUES
(1, 'Clinic of the Union Against Jensusm', '823 Jensus Rememberance Street NW', 'U0D 7N4', 'CANADA', 'AB', 'CALGARY', 'https://i.redd.it/y2p02vwo8vx31.png', 1),
(2, 'The Shrine of Reda Alhajj (Elhajj)', '300 Lovers St SW', 'Y3K U8B', 'CANADA', 'AB', 'CALGARY', 'https://i.kym-cdn.com/photos/images/original/001/230/083/d68.jpg', 1),
(3, 'The Mayo Clinic', '3319 26 Ave NE', 'T1Y 6L4', 'CANADA', 'AB', 'CALGARY', 'https://s.abcnews.com/images/Health/mayo-clinic-covid-03-gty-llr-201118_1605730703009_hpMain_16x9_992.jpg', 1),
(4, 'Legacy Medical Centre', '2419 Ave. SW', 'T3K 8F1', 'CANADA', 'AB', 'CALGARY', 'https://www.brockvillegeneralhospital.ca/en/resourcesGeneral/feature_01.jpg', 1),
(5, 'Universal Long&Long Clinic', '321 Longist St NW', 'L0L 0L0', 'CANADA', 'AB', 'CALGARY', 'https://images-na.ssl-images-amazon.com/images/I/71RwPUtQ-BL._AC_SL1500_.jpg', 1),
(6, 'University of Calgary Wellness Services', '2500 University Drive NW', 'T2N 1N4', 'CANADA', 'AB', 'CALGARY', 'https://www.ucalgary.ca/live-uc-ucalgary-site/sites/default/files/styles/ucws_hero_cta_desktop/public/2019-03/MacHall_011A8586_F.jpg', 1);
create table if not exists heroku_efc788d1dc18da4.CLINIC_PHONE_NUMBER (
CID int NOT NULL,
PhoneNumber varchar(255) NOT NULL,
CONSTRAINT PK_PN PRIMARY KEY (CID,PhoneNumber),
FOREIGN KEY (CID) REFERENCES CLINIC(ID)
ON DELETE CASCADE
ON UPDATE CASCADE);
USE heroku_efc788d1dc18da4;
INSERT INTO heroku_efc788d1dc18da4.CLINIC_PHONE_NUMBER (`CID`, `PhoneNumber`) VALUES
(1, '421-792-3712'),
(1, '456-327-3616'),
(2, '420-420-4200'),
(3, '214-513-4252'),
(4, '281-642-0131'),
(5, '123-862-1345'),
(5, '521-856-2199'),
(5, '531-612-3511'),
(6, '403-210-9355');
create table if not exists heroku_efc788d1dc18da4.VACCINE_SUPPORT (
CID int NOT NULL,
VID int NOT NULL,
CONSTRAINT PK_VS PRIMARY KEY (CID,VID),
FOREIGN KEY (CID) REFERENCES CLINIC(ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (VID) REFERENCES VACCINE(ID)
ON DELETE CASCADE
ON UPDATE CASCADE
);
USE heroku_efc788d1dc18da4;
INSERT INTO heroku_efc788d1dc18da4.VACCINE_SUPPORT (`CID`, `VID`) VALUES
(1, 3),
(1, 2),
(2, 3),
(3, 1),
(3, 5),
(4, 1),
(4, 5),
(5, 3),
(5, 4),
(6, 2),
(6, 5);
create table if not exists heroku_efc788d1dc18da4.REQUEST_APPOINTMENT (
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
Date varchar(255),
PID int,
VID int,
FOREIGN KEY (PID) REFERENCES PATIENT(ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (VID) REFERENCES VACCINE(ID)
ON DELETE CASCADE
ON UPDATE CASCADE);
create table if not exists heroku_efc788d1dc18da4.APPOINTMENT (
RID int NOT NULL PRIMARY KEY,
Date varchar(255) NOT NULL,
Status varchar(255),
CID int,
FOREIGN KEY (RID) REFERENCES REQUEST_APPOINTMENT(ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (CID) REFERENCES CLINIC(ID)
ON DELETE CASCADE
ON UPDATE CASCADE);