hgFENvqw

1create table if not exists edificios (
2    id int primary key auto_increment,
3    designacao char(1)
4) ENGINE = InnoDB;
5
6create table if not exists salas (
7    id int primary key auto_increment,
8    num_sala int,
9    id_edificio int,
10    x int,
11    y int,
12    foreign key (id_edificio) references edificios(id) on delete cascade
13) ENGINE = InnoDB;
14
15create table if not exists disciplinas (
16    id int primary key auto_increment,
17    nome varchar(255)
18) ENGINE = InnoDB;
19
20create table if not exists cursos (
21    id int primary key auto_increment,
22    nome varchar(255)
23) ENGINE = InnoDB;
24
25create table if not exists turmas (
26    id int primary key auto_increment,
27    ano char(2),
28    turma char(1),
29    ano_letivo varchar(10),
30    id_curso int,
31    foreign key (id_curso) references cursos(id) on delete cascade
32) ENGINE = InnoDB;
33
34create table if not exists recrutamento(
35    id int primary key auto_increment,
36    grupo varchar(3),
37    nome varchar(255)
38) ENGINE = InnoDB;
39
40create table if not exists nivel (
41    id int primary key auto_increment,
42    tipo varchar(255)
43) ENGINE = InnoDB;
44
45create table if not exists tipo_cacifo(
46    id int PRIMARY KEY AUTO_INCREMENT,
47    cacifo_tipo VARCHAR(10),
48    valor decimal(4,2),
49    num_pessoa int
50) ENGINE = InnoDB;
51
52create table if not exists cacifos(
53    id int PRIMARY KEY AUTO_INCREMENT,
54    num_cacifo int,
55    id_tipo_cacifo int,
56    id_edificio int,
57    FOREIGN KEY(id_tipo_cacifo) REFERENCES tipo_cacifo(id),
58    FOREIGN KEY(id_edificio) REFERENCES edificios(id)
59) ENGINE = InnoDB;
60
61create table if not exists utilizadores (
62    id int primary key auto_increment,
63    nome varchar(255),
64    apelido varchar(255),
65    password varchar(255),
66    email varchar(255),
67    bi_cc varchar(255),
68    nif varchar(255),
69    telefone varchar(255),
70    n_processo varchar(7),
71    id_recrutamento int,
72    id_turma int,
73    id_cacifo int,
74    id_nivel int,
75    foreign key (id_recrutamento) references recrutamento(id) on delete cascade,
76    foreign key (id_turma) references turmas(id) on delete cascade,
77    foreign key (id_cacifo) references cacifos(id) on delete cascade,
78    foreign key (id_nivel) references nivel(id) on delete cascade
79) ENGINE = InnoDB;
80
81create table if not exists cursos_disciplinas (
82    id_curso int,
83    id_disciplina int,
84    ano char(2),
85    id_utilizador int,
86    primary key (id_curso, id_disciplina),
87    foreign key (id_curso) references cursos(id) on delete cascade,
88    foreign key (id_disciplina) references disciplinas(id) on delete cascade,
89    foreign key (id_utilizador) references utilizadores(id) on delete cascade
90) ENGINE = InnoDB;
91
92create table if not exists notif (
93    id int primary key auto_increment,
94    titulo varchar(255),
95    msg varchar(255),
96    tempo timestamp,
97    id_utilizador int,
98    foreign key (id_utilizador) references utilizadores(id) on delete cascade
99) ENGINE = InnoDB;
100
101create table if not exists tipo_permuta (
102    id int primary key auto_increment,
103    designacao varchar(255)
104) ENGINE = InnoDB;
105
106create table if not exists permutas (
107    id int primary key auto_increment,
108    id_tipo int,
109    id_utilizador int,
110    id_utilizador_sub int,
111    id_turma int,
112    id_disciplina int,
113    id_disciplina_new int,
114    data date,
115    data_new date,
116    hora time,
117    hora_new time,
118    id_sala int,
119    id_sala_new int,
120    motivo text,
121    foreign key (id_tipo) references tipo_permuta(id) on delete cascade,
122    foreign key (id_utilizador) references utilizadores(id) on delete cascade,
123    foreign key (id_utilizador_sub) references utilizadores(id) on delete cascade,
124    foreign key (id_turma) references turmas(id) on delete cascade,
125    foreign key (id_disciplina) references disciplinas(id) on delete cascade,
126    foreign key (id_disciplina_new) references disciplinas(id) on delete cascade,
127    foreign key (id_sala) references salas(id) on delete cascade,
128    foreign key (id_sala_new) references salas(id) on delete cascade
129) ENGINE = InnoDB;
130
131create table if not exists equipamentos(
132	id int primary key AUTO_INCREMENT,
133	designacao varchar(25),
134    id_sala int,
135    foreign key (id_sala) references salas(id) on delete cascade
136) ENGINE = InnoDB;
137
138create table if not exists pc(
139	id int primary key AUTO_INCREMENT,
140    id_sala int,
141    x int,
142    y int,
143    foreign key (id_sala) references salas(id) on delete cascade
144) ENGINE = InnoDB;
145
146create table if not exists hardware(
147	id int primary key AUTO_INCREMENT,
148    id_pc int,
149	designacao enum('CPU','MotherBoard','RAM','Disco','Fonte','Caixa'),
150	especificacao varchar(255),
151    foreign key (id_pc) references pc(id) on delete cascade
152) ENGINE = InnoDB;
153
154create table if not exists software(
155	id int primary key AUTO_INCREMENT,
156	designacao varchar(255)
157) ENGINE = InnoDB;
158
159create table if not exists pc_software(
160	id_pc_software int primary key AUTO_INCREMENT,
161	id_pc int,
162	id_software int,
163	especificacao varchar(255),
164	foreign key (id_pc) references pc(id) on delete cascade,
165	foreign key (id_software) references software(id) on delete cascade
166) ENGINE = InnoDB;
167
168create table if not exists avarias(
169	id int primary key AUTO_INCREMENT,
170	id_utilizador int,
171	id_pc int,
172	hora datetime,
173	observacao varchar(255),
174	foreign key (id_utilizador) references utilizadores(id),
175	foreign key (id_pc) references pc(id)
176) ENGINE = InnoDB;