· 7 years ago · Feb 07, 2019, 03:56 PM
1create table if not exists "user"
2(
3 id serial not null
4 constraint user_pk
5 primary key,
6 email_id varchar(64) not null,
7 password varchar(64) not null,
8 first_name varchar(64) not null,
9 last_name varchar(64) not null,
10 user_type varchar(64) not null
11);
12
13alter table "user" owner to khanacademy;
14
15create unique index if not exists user_email_id_uindex
16 on "user" (email_id);
17
18create unique index if not exists user_user_id_uindex
19 on "user" (id);
20
21create table if not exists "group"
22(
23 id serial not null
24 constraint group_pk
25 primary key,
26 name varchar(64) not null,
27 subject varchar(64) not null,
28 teacher_id integer not null
29 constraint group_user_id_fk
30 references "user"
31);
32
33alter table "group" owner to khanacademy;
34
35create unique index if not exists group_id_uindex
36 on "group" (id);
37
38create table if not exists topic
39(
40 id serial not null
41 constraint topic_pk
42 primary key,
43 name varchar(64) not null,
44 subject varchar(64)
45);
46
47alter table topic owner to khanacademy;
48
49create table if not exists content
50(
51 id serial not null
52 constraint content_pk
53 primary key,
54 name varchar(64) not null,
55 description varchar(200),
56 topic_id integer not null
57 constraint content_topic_id_fk
58 references topic,
59 type varchar(16) not null
60);
61
62alter table content owner to khanacademy;
63
64create unique index if not exists content_id_uindex
65 on content (id);
66
67create table if not exists document
68(
69 id integer not null
70 constraint document_pk
71 primary key
72 constraint document_content_id_fk
73 references content
74 on delete cascade,
75 body varchar(200) not null
76);
77
78alter table document owner to khanacademy;
79
80create unique index if not exists document_id_uindex
81 on document (id);
82
83create table if not exists video
84(
85 id integer not null
86 constraint video_pk
87 primary key
88 constraint video_content_id_fk
89 references content
90 on delete cascade,
91 link varchar(64) not null,
92 duration integer not null
93);
94
95alter table video owner to khanacademy;
96
97create unique index if not exists video_id_uindex
98 on video (id);
99
100create table if not exists question
101(
102 id integer not null
103 constraint question_pk
104 primary key
105 constraint question_content_id_fk
106 references content
107 on delete cascade,
108 statement varchar(200) not null,
109 answer integer not null,
110 difficulty integer not null
111);
112
113alter table question owner to khanacademy;
114
115create unique index if not exists question_id_uindex
116 on question (id);
117
118create table if not exists user_video
119(
120 user_id integer not null
121 constraint user_video_pk
122 primary key
123 constraint user_video_user_user_id_fk
124 references "user"
125 on delete cascade,
126 video_id integer not null
127 constraint user_video_video_id_fk
128 references video
129 on delete cascade,
130 seen_upto integer not null
131);
132
133comment on table user_video is 'Stores video watch history of user';
134
135alter table user_video owner to khanacademy;
136
137create unique index if not exists user_video_user_id_uindex
138 on user_video (user_id);
139
140create unique index if not exists user_video_video_id_uindex
141 on user_video (video_id);
142
143create unique index if not exists topic_id_uindex
144 on topic (id);
145
146create unique index if not exists topic_name_uindex
147 on topic (name);
148
149create table if not exists user_question
150(
151 user_id integer not null
152 constraint user_question_user_user_id_fk
153 references "user",
154 question_id integer not null
155 constraint user_question_question_id_fk
156 references question,
157 given_answer integer not null,
158 is_correct boolean not null
159);
160
161alter table user_question owner to khanacademy;
162
163create table if not exists user_question_topic
164(
165 user_id integer not null
166 constraint user_question_topic_user_user_id_fk
167 references "user"
168 on delete cascade,
169 topic_id integer not null
170 constraint user_question_topic_topic_id_fk
171 references topic
172 on delete cascade,
173 attempted_questions integer not null,
174 correct_answers integer not null,
175 streak integer not null
176);
177
178comment on table user_question_topic is 'Users'' statistics on questions of a certain topic';
179
180alter table user_question_topic owner to khanacademy;
181
182create table if not exists user_group
183(
184 user_id integer
185 constraint user_group_user_id_fk
186 references "user",
187 group_id integer
188 constraint user_group_group_id_fk
189 references "group"
190);
191
192comment on table user_group is 'Member list of groups';
193
194alter table user_group owner to khanacademy;