· 7 years ago · Nov 27, 2018, 03:44 PM
1BEGIN TRANSACTION;
2CREATE TABLE IF NOT EXISTS `Instituicao` (
3 `sigla` TEXT NOT NULL UNIQUE,
4 `email` TEXT NOT NULL UNIQUE,
5 `nome` TEXT NOT NULL,
6 `telefone` TEXT NOT NULL,
7 `cep` TEXT NOT NULL,
8 `estado` TEXT NOT NULL,
9 `cidade` TEXT NOT NULL,
10 `bairro` TEXT NOT NULL,
11 `logradouro` TEXT NOT NULL,
12 `numero` INTEGER NOT NULL,
13 PRIMARY KEY(`sigla`)
14);
15CREATE TABLE IF NOT EXISTS `Disciplina_Gamificada` (
16 `nome` TEXT NOT NULL,
17 `codigo` INTEGER NOT NULL UNIQUE,
18 `vagas` INTEGER NOT NULL,
19 `sigla` TEXT NOT NULL,
20 FOREIGN KEY(`sigla`) REFERENCES `Instituicao`(`sigla`) ON DELETE CASCADE ON UPDATE CASCADE
21 PRIMARY KEY(`codigo`,`sigla`)
22);
23CREATE TABLE IF NOT EXISTS `Tipo_Tarefa` (
24 `tipo` TEXT NOT NULL PRIMARY KEY UNIQUE,
25 `dificuldade` INTEGER NOT NULL
26);
27CREATE TABLE IF NOT EXISTS `Tarefa` (
28 `codigo` INTEGER NOT NULL UNIQUE,
29 `cod_d` INTEGER NOT NULL,
30 `tipo_t` TEXT NOT NULL,
31 `valor` INTEGER NOT NULL,
32 FOREIGN KEY(`cod_d`) REFERENCES `Disciplina_Gamificada`(`codigo`) ON DELETE CASCADE ON UPDATE CASCADE,
33 FOREIGN KEY(`tipo_t`) REFERENCES `Tipo_Tarefa`(`tipo`) ON DELETE CASCADE ON UPDATE CASCADE,
34 PRIMARY KEY(`codigo`, `cod_d`, `tipo_t`)
35);
36CREATE TABLE IF NOT EXISTS `Desafio` (
37 `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
38 `nome` TEXT NOT NULL,
39 `descricao` TEXT NOT NULL,
40 `imagem` TEXT NOT NULL,
41 `emblema` TEXT NOT NULL
42);
43CREATE TABLE IF NOT EXISTS `Contem` (
44 `id` INTEGER NOT NULL,
45 `codigo` INTEGER NOT NULL,
46 FOREIGN KEY(`id`) REFERENCES `Desafio`(`id`),
47 FOREIGN KEY(`codigo`) REFERENCES `Disciplina_Gamificada`(`codigo`)
48 PRIMARY KEY(`id`, `codigo`)
49);
50CREATE TABLE IF NOT EXISTS `Usuario` (
51 `cpf` TEXT NOT NULL PRIMARY KEY UNIQUE,
52 `nome` TEXT NOT NULL,
53 `avatar` TEXT NOT NULL,
54 `email` TEXT NOT NULL
55);
56CREATE TABLE IF NOT EXISTS `Professor` (
57 `cpf` TEXT NOT NULL PRIMARY KEY UNIQUE,
58 `depto` TEXT NOT NULL,
59 FOREIGN KEY(`cpf`) REFERENCES `Usuario`(`cpf`)
60);
61CREATE TABLE IF NOT EXISTS `Aluno` (
62 `cpf` TEXT NOT NULL PRIMARY KEY UNIQUE,
63 `grupo` TEXT NOT NULL,
64 `saldo` REAL NOT NULL,
65 `sigla` TEXT NOT NULL,
66 `curso` TEXT NOT NULL,
67 FOREIGN KEY(`sigla`) REFERENCES `Instituicao`(`sigla`),
68 FOREIGN KEY(`cpf`) REFERENCES `Usuario`(`cpf`)
69);
70CREATE TABLE IF NOT EXISTS `Participa` (
71 `cpf` TEXT NOT NULL,
72 `codigo` INTEGER NOT NULL,
73 FOREIGN KEY(`cpf`) REFERENCES `Aluno`(`cpf`),
74 FOREIGN KEY(`codigo`) REFERENCES `Disciplina_Gamificada`(`codigo`),
75 PRIMARY KEY(`cpf`, `codigo`)
76);
77CREATE TABLE IF NOT EXISTS `Ministra` (
78 `cpf` TEXT NOT NULL,
79 `codigo` INTEGER NOT NULL,
80 FOREIGN KEY(`cpf`) REFERENCES `Professor`(`cpf`),
81 FOREIGN KEY(`codigo`) REFERENCES `Disciplina_Gamificada`(`codigo`),
82 PRIMARY KEY(`cpf`, `codigo`)
83);
84CREATE TABLE IF NOT EXISTS `Giro_Roleta` (
85 `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
86 `cpf` TEXT NOT NULL,
87 `premio` TEXT NOT NULL,
88 FOREIGN KEY(`cpf`) REFERENCES `Aluno`(`cpf`)
89);
90CREATE TABLE IF NOT EXISTS `Postagem` (
91 `cpf` TEXT NOT NULL,
92 `hora` TEXT NOT NULL,
93 `assunto` TEXT NOT NULL,
94 `mensagem` TEXT NOT NULL,
95 PRIMARY KEY(`cpf`,`hora`),
96 FOREIGN KEY(`cpf`) REFERENCES `Usuario`(`cpf`) ON DELETE CASCADE ON UPDATE CASCADE
97);
98CREATE TABLE IF NOT EXISTS `Forum` (
99 `id` INTEGER NOT NULL PRIMARY KEY UNIQUE,
100 `nome` TEXT NOT NULL UNIQUE,
101 `link` TEXT NOT NULL UNIQUE,
102 `tema` TEXT NOT NULL
103);
104CREATE TABLE IF NOT EXISTS `Publica` (
105 `cpf` TEXT NOT NULL,
106 `id` INTEGER NOT NULL,
107 `hora` TEXT NOT NULL,
108 FOREIGN KEY(`id`) REFERENCES `Forum`(`id`),
109 FOREIGN KEY(`cpf`, `hora`) REFERENCES `Postagem`(`cpf`, `hora`),
110 PRIMARY KEY(`cpf`, `hora`, `id`)
111);
112COMMIT;