-
Notifications
You must be signed in to change notification settings - Fork 4
/
schema.sql
90 lines (83 loc) · 2.67 KB
/
schema.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
CREATE TABLE IF NOT EXISTS usuarios (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nome VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
senha VARCHAR(255) NOT NULL,
tipo TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS cursos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nome TEXT NOT NULL,
descricao ,
imagem TEXT
);
CREATE TABLE IF NOT EXISTS inscricoes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
aluno_id INTEGER NOT NULL,
curso_id INTEGER NOT NULL,
data_inscricao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (aluno_id) REFERENCES usuarios(id),
FOREIGN KEY (curso_id) REFERENCES cursos(id)
);
CREATE TABLE IF NOT EXISTS modulos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
titulo TEXT NOT NULL,
descricao TEXT,
curso_id INTEGER,
professor_id INTEGER,
FOREIGN KEY (curso_id) REFERENCES cursos(id),
FOREIGN KEY (professor_id) REFERENCES usuarios(id)
);
CREATE TABLE IF NOT EXISTS aulas (
id INTEGER PRIMARY KEY AUTOINCREMENT,
modulo_id INTEGER NOT NULL,
curso_id INTEGER NOT NULL,
titulo TEXT NOT NULL,
descricao TEXT,
conteudo_nome TEXT,
arquivo TEXT,
FOREIGN KEY (modulo_id) REFERENCES modulos(id)
);
CREATE TABLE IF NOT EXISTS perguntas (
id INTEGER PRIMARY KEY AUTOINCREMENT,
aula_id INTEGER NOT NULL,
texto TEXT NOT NULL,
FOREIGN KEY (aula_id) REFERENCES aulas(id)
);
CREATE TABLE IF NOT EXISTS respostas (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
pergunta_id INTEGER NOT NULL,
resposta TEXT NOT NULL,
nota INTEGER,
aula_id INTEGER NOT NULL,
FOREIGN KEY (aula_id) REFERENCES aulas(id),
FOREIGN KEY (user_id) REFERENCES usuarios(id),
FOREIGN KEY (pergunta_id) REFERENCES perguntas(id)
);
CREATE TABLE IF NOT EXISTS progresso_atividades (
user_id INTEGER NOT NULL,
modulo_id INTEGER NOT NULL,
aula_id INTEGER NOT NULL,
completou BOOLEAN NOT NULL DEFAULT 0,
PRIMARY KEY (user_id, modulo_id, aula_id),
FOREIGN KEY (user_id) REFERENCES usuarios(id),
FOREIGN KEY (aula_id) REFERENCES aulas(id),
FOREIGN KEY (modulo_id) REFERENCES modulos(id)
);
CREATE TABLE IF NOT EXISTS progresso_aulas (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
aula_id INTEGER NOT NULL,
concluida BOOLEAN DEFAULT 0,
FOREIGN KEY (user_id) REFERENCES usuarios(id),
FOREIGN KEY (aula_id) REFERENCES aulas(id)
);
CREATE TABLE IF NOT EXISTS progresso_cursos (
user_id INTEGER NOT NULL,
curso_id INTEGER NOT NULL,
progresso FLOAT DEFAULT 0,
PRIMARY KEY (user_id, curso_id),
FOREIGN KEY (user_id) REFERENCES usuarios(id),
FOREIGN KEY (curso_id) REFERENCES cursos(id)
);