qzB07kMA

1CREATE DATABASE IF NOT EXISTS volunteermatch;
2USE volunteermatch;
3
4CREATE TABLE contact(
5contact_id INT AUTO_INCREMENT,
6contact_email VARCHAR(45),
7contact_first_name VARCHAR(20),
8contact_last_name VARCHAR(40),
9contact_phone INT(8),
10CONSTRAINT PRIMARY KEY (contact_id)
11);
12
13INSERT INTO contact VALUES
14(NULL, 'jules@gmail.com', 'Julie', 'Gundersen', 12345678),
15(NULL, 'elias@gmail.com', 'Elias', 'Brynestad', 23456789);
16
17CREATE TABLE location(
18loc_id INT NOT NULL AUTO_INCREMENT, 
19loc_country VARCHAR(20) NOT NULL,
20loc_zip INT(8) NOT NULL,
21loc_street_1 VARCHAR(45),
22loc_street_2 VARCHAR(10),
23loc_city VARCHAR(30),
24loc_region VARCHAR(30),
25CONSTRAINT PRIMARY KEY (loc_id)
26);
27
28INSERT INTO location VALUES
29(NULL, 'Norway', 1234, 'Gateveien 32', 'Melkeveien 3', 'Kristiansand', 'Vest-Agder'),
30(NULL, 'USA', 4630, 'Trump street 13', 'Obama 1', 'Los Angeles', 'California');
31
32CREATE TABLE account(
33acc_id INT AUTO_INCREMENT,
34acc_email VARCHAR(20) NOT NULL,
35acc_first_name VARCHAR(30) NOT NULL,
36acc_last_name VARCHAR(40) NOT NULL,
37acc_password VARCHAR(25),
38acc_phone INT,
39loc_id INT NOT NULL,
40cau_id INT NOT NULL,
41CONSTRAINT PRIMARY KEY (acc_id),
42CONSTRAINT FOREIGN KEY (loc_id) REFERENCES location (loc_id),
43CONSTRAINT FOREIGN KEY (cau_id) REFERENCES account_cause (cau_id)
44);
45
46INSERT INTO account VALUES
47(NULL, 'kris@hotmail.com', 'Kristoffer', 'Slettebakken', 'ilovemusic<3', 84930284, 1, 1),
48(NULL, 'pingpong@gmail.com', 'Phuong', 'Pham', 'noodles4life', 66748392, 2, 1);
49
50CREATE TABLE organization(
51org_id INT NOT NULL AUTO_INCREMENT,
52org_name VARCHAR(30) NOT NULL,
53org_mission TEXT,
54org_description TEXT,
55org_phone INT,
56org_type VARCHAR(30),
57org_registered_date DATE,
58org_website_URL TINYTEXT,
59org_linkedin_URL TINYTEXT,
60org_facebook_URL TINYTEXT,
61org_twitter_URL TINYTEXT,
62org_image_URL TINYTEXT,
63contact_id INT,
64loc_id INT NOT NULL,
65CONSTRAINT PRIMARY KEY (org_id),
66CONSTRAINT FOREIGN KEY (loc_id) REFERENCES location (loc_id),
67CONSTRAINT FOREIGN KEY (cau_id) REFERENCES organization_cause (cau_id)
68);
69
70INSERT INTO organization VALUES
71(NULL, 'Wood Worm Workers', 'Organization with a focus surrounding the population of worms in mahogany trees in Malaysia.', 'Organization with 4 employees based in Zimbabwe.', '87655578', 'Private org.', '01012018', 'wwww.www.com', NULL, NULL, NULL, NULL, 1, 1),
72(NULL, 'Grey Goose Shelters', 'We are making a stand against the oppression against grey gooses all around the world.', '8400 employees. Organization based in Marokko', '98555325', 'US GOV. org.', '02032018', 'wwww.ggs.com', NULL, NULL, NULL, NULL, 2, 2
73);
74
75CREATE TABLE opportunity(
76opp_id INT AUTO_INCREMENT,
77opp_title VARCHAR(45),
78opp_description TEXT,
79opp_creation_date DATE,
80opp_max_volunteer INT,
81opp_min_age INT,
82opp_start_datetime DATETIME,
83opp_end_datetime DATETIME,
84opp_status VARCHAR(30),
85opp_great_for VARCHAR(50),
86loc_id INT,
87contact_id INT,
88CONSTRAINT PRIMARY KEY (opp_id),
89CONSTRAINT FOREIGN KEY (loc_id) REFERENCES location (loc_id),
90CONSTRAINT FOREIGN KEY (contact_id) REFERENCES contact (contact_id)
91);
92
93INSERT INTO opportunity VALUES
94(NULL, 'Counter', 'Count the amount of wood worms in Malaysia', 2019-01-01, 10, 15, '2019-02-01 08:00:00', '2019-03-01 14:00:00', 'Active', 'Groups', 1, 1),
95(NULL, 'Lifesaver', 'CPR to the gooses nearby', 2018-07-25, 5, 18, '2018-08-01 08:00:00', '2018-08-31 19:00:00', 'Inactive', 'Kids', 2, 2);
96
97CREATE TABLE interest(
98acc_id INT,
99opp_id INT,
100interest_datetime DATETIME,
101interest_status VARCHAR(15),
102interest_commitment_start DATETIME,
103interest_commitment_end DATETIME,
104CONSTRAINT PRIMARY KEY (acc_id, opp_id),
105CONSTRAINT FOREIGN KEY (acc_id) REFERENCES account (acc_id),
106CONSTRAINT FOREIGN KEY (opp_id) REFERENCES opportunity (opp_id)
107);
108
109INSERT INTO interest VALUES
110(1, 1, '2018-12-30 12:09:10', 'WILL ATTEND', '2019-01-14 10:00:00', '2019-01-06 13:00:00'),
111(2, 2, '2018-07-31 14:51:13', 'INQUIRY', '2018-08-01 08:00:00', '2019-01-06 18:30:00');
112
113CREATE TABLE timesheet(
114acc_id INT,
115opp_id INT,
116volunteer_date DATETIME,
117timesheet_hours INT (3),
118timesheet_entry_date DATETIME,
119timesheet_member_approval INT,
120timesheet_status VARCHAR(20),
121CONSTRAINT PRIMARY KEY (acc_id, opp_id),
122CONSTRAINT FOREIGN KEY (acc_id) REFERENCES account (acc_id),
123CONSTRAINT FOREIGN KEY (opp_id) REFERENCES opportunity (opp_id),
124CONSTRAINT FOREIGN KEY (timesheet_member_approval) REFERENCES account (acc_id)
125);
126
127INSERT INTO timesheet VALUES
128(1, 2, '2019-02-01 09:13:10', 4, '2019-02-01 13:13:10', 'PENDING'),
129(2, 1, '2019-02-07 12:45:30', 7, '2018-02-07 19:45:30', 'APPROVED');
130
131CREATE TABLE skill_category(
132skill_cat_id INT NOT NULL AUTO_INCREMENT,
133skill_cat_name VARCHAR(45),
134CONSTRAINT PRIMARY KEY (skill_cat_id)
135);
136
137INSERT INTO skill_category VALUES
138(NULL, 'Animals and Environment'),
139(NULL, 'Language');
140
141CREATE TABLE skill(
142skill_id INT AUTO_INCREMENT,
143skill_name VARCHAR(45),
144skill_cat_id INT,
145CONSTRAINT PRIMARY KEY (skill_id),
146CONSTRAINT FOREIGN KEY (skill_cat_id) REFERENCES skill_category(skill_cat_id)
147);
148
149INSERT INTO skill VALUES
150(NULL, 'Animal CPR', 1),
151(NULL, 'French', 2);
152
153CREATE TABLE account_skill(
154acc_id INT,
155skill_id INT,
156CONSTRAINT PRIMARY KEY (acc_id, skill_id),
157CONSTRAINT FOREIGN KEY (acc_id) REFERENCES account (acc_id),
158CONSTRAINT FOREIGN KEY (skill_id) REFERENCES skill (skill_id)
159);
160
161INSERT INTO account_skill VALUES
162(1, 1),
163(2, 2);
164
165CREATE TABLE opportunity_skill(
166opp_id INT, 
167skill_id INT, 
168CONSTRAINT PRIMARY KEY (opp_id, skill_id),
169CONSTRAINT FOREIGN KEY (opp_id) REFERENCES opportunity(opp_id),
170CONSTRAINT FOREIGN KEY (skill_id) REFERENCES skill(skill_id)
171);
172
173INSERT INTO opportunity_skill VALUES
174(1, 1),
175(2, 2);
176
177CREATE TABLE cause(
178cau_id INT NOT NULL AUTO_INCREMENT,
179cau_name VARCHAR(30),
180CONSTRAINT PRIMARY KEY (cau_id)
181);
182
183INSERT INTO cause VALUES
184(NULL, 'Environment'),
185(NULL, 'International');
186
187CREATE TABLE organization_cause(
188cau_id INT, 
189org_id INT,
190CONSTRAINT PRIMARY KEY (cau_id, org_id),
191CONSTRAINT FOREIGN KEY (cau_id) REFERENCES cause (cau_id),
192CONSTRAINT FOREIGN KEY (org_id) REFERENCES organization (org_id)
193);
194
195INSERT INTO organization_cause VALUES
196(1, 1),
197(2, 2);
198
199CREATE TABLE account_cause(
200cau_id INT, 
201acc_id INT,
202CONSTRAINT PRIMARY KEY (cau_id, acc_id),
203CONSTRAINT FOREIGN KEY (cau_id) REFERENCES cause (cau_id),
204CONSTRAINT FOREIGN KEY (acc_id) REFERENCES account (acc_id)
205);
206
207INSERT INTO account_cause VALUES
208(1, 1),
209(2, 2);