iiiu3RXS

1DROP TABLE IF EXISTS question_votes;
2DROP TABLE IF EXISTS question_choices;
3DROP TABLE IF EXISTS question;
4DROP TABLE IF EXISTS survey;
5DROP TABLE IF EXISTS users;
6
7
8CREATE TABLE IF NOT EXISTS  users (
9	id serial PRIMARY KEY,
10	username VARCHAR(50) NOT NULL UNIQUE,
11	PASSWORD VARCHAR(100),
12   created_on DATE NOT NULL DEFAULT CURRENT_DATE
13);
14
15CREATE TABLE survey (
16	id serial PRIMARY KEY,
17	user_id serial NOT NULL,
18	survey_name VARCHAR(150) NOT NULL,
19	survey_string TEXT,
20	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
21	deadline TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP + INTERVAL '5 days',
22	FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
23);
24
25
26CREATE TABLE question (
27	id serial PRIMARY KEY,
28	survey_id serial,
29	question_name VARCHAR(200),
30	FOREIGN KEY(survey_id) REFERENCES survey(id) ON DELETE CASCADE
31);
32
33CREATE TABLE question_choices (
34	id serial PRIMARY KEY,
35	question_id serial,
36	choice VARCHAR(200),
37   FOREIGN KEY(question_id) REFERENCES question(id) ON DELETE CASCADE
38);
39
40CREATE TABLE question_votes (
41   id serial PRIMARY KEY,
42   question_choices_id serial,
43	score INT DEFAULT 0,
44   FOREIGN KEY (question_choices_id) REFERENCES question_choices(id) ON DELETE CASCADE
45);
46