project.sql

DROP DATABASE IF EXISTS ensf380project;
CREATE DATABASE ensf380project;
\c ensf380project


CREATE TABLE INQUIRER (
    id 			SERIAL PRIMARY KEY,
    firstName 	VARCHAR(50) NOT NULL,
    lastName 	VARCHAR(50),
    phoneNumber VARCHAR(20) NOT NULL
);
INSERT INTO INQUIRER (id, firstName, lastName, phoneNumber) VALUES
(1, 'Dominik', 'Pflug', '123-456-9831'),
(2, 'Yaa', 'Odei', '123-456-8913'),
(3, 'Cecilia', 'Cobos', '123-456-7891'),
(4, 'Hongjoo', 'Park', '123-456-8912');
INSERT INTO INQUIRER (id, firstName, phoneNumber) VALUES
(5, 'Saartje', '123-456-7234'),
(6, 'Urjoshi', '456-123-4281');

CREATE TABLE INQUIRY_LOG (
    id SERIAL PRIMARY KEY,
    inquirer int NOT NULL,
    callDate DATE NOT NULL,
    details VARCHAR(500) NOT NULL,
    foreign key (inquirer) references INQUIRER(id) ON UPDATE CASCADE
);

INSERT INTO INQUIRY_LOG (id, inquirer, callDate, details) VALUES
(1, 1, '2024-02-28', 'Theresa Pflug'),
(2, 2, '2024-02-28', 'Offer to assist as volunteer'),
(3, 3, '2024-03-01', 'Valesk Souza'),
(4, 1, '2024-03-01', 'Theresa Pflug'),
(5, 1, '2024-03-02', 'Theresa Pflug'),
(6, 4, '2024-03-02', 'Yoyo Jefferson and Roisin Fitzgerald'),
(7, 5, '2024-03-02', 'Henk Wouters'),
(8, 3, '2024-03-03', 'Melinda'),
(9, 6, '2024-03-04', 'Julius');

CREATE TABLE DISASTERVICTIM (
	id 			SERIAL PRIMARY KEY,
    firstName 	VARCHAR(50) NOT NULL,
    lastName 	VARCHAR(50),
    entryDate 	DATE NOT NULL,
    gender 		VARCHAR(50),
    diet 		VARCHAR(100),  
    comments 	VARCHAR(500),
    location	VARCHAR(100) NOT NULL,
    foreign key (location) references LOCATION(name)
);
INSERT INTO DISASTERVICTIM (id, firstName, lastName, entryDate, location) VALUES
(1, "Victor", "Lee", "2024-01-03", "University of Calgary"),
(2, "Jenny", "Jefferson", "2024-02-12", "University of Calgary"),
(3, "Bennet", "Downing", "2024-02-13", "TELUS Conventiion Centre");

CREATE TABLE MEDICALRECORDS (
	victimID			INT NOT NULL,
    treatmentDetails 	VARCHAR(100),
    dateOfTreatment 	DATE NOT NULL,
    location 			VARCHAR(100) NOT NULL,
	foreign key (vitimID) references DISASTERVICTIM(id),
    foreign key (location) references LOCATION(name)
);
INSERT INTO MEDICALRECORDS (victimID, treatmentDetails, datOfTreatment, location) VALUES
(1, "Treated minor scrapes on leg", "2024-01-08", "University of Calgary"),
(1, "Quick check-up", "2024-02-14", "University of Calgary"),
(2, "Treated twisted ankle", "2024-02-22", "University of Calgary"),
(3, "Treated bruises and small scratches", "2024-03-30", "TELUS Convention Centre");

CREATE TABLE LOCATION (
	name		VARCHAR(100) NOT NULL,
    address		VARCHAR(100) NOT NULL
);

INSERT INTO LOCATION (name, address) VALUES 
("TELUS Convention Centre", "136 8 Ave SE"),
("University of Calgary",  "2500 University Dr NW");

CREATE TABLE SUPPLIES (
	type		VARCHAR(50) NOT NULL,
    quantity	INT NOT NULL,
	victimID	INT,
    location	VARCHAR(100),
    foreign key (victimID) references DISASTERVICTIM(id),
    foreign key (location) references LOCATION(name)
);

INSERT INTO SUPPLIES (type, quantity, location) VALUES
("bandaids", 200, "University of Calgary"),
("ice packs", 50, "University of Calgary"),
("water bottles", 200, "University of Calgary"),
("bandaids", 300, "TELUS Convention Centre"),
("ice packs", 100, "TELUS Convention Centre"),
("water bottles", 250, "TELUS Convention Centre");

INSERT INTO SUPPLIES (type, quantity, victimID) VALUES
("bandaids", 2, 1),
("ice packs", 1, 1),
("water bottles", 2, 1),
("bandaids", 6, 2),
("water bottles", 1, 2),
("ice packs", 1, 3);