HWEDzVdR

1drop database if exists atividade5;
2create database atividade5;
3use atividade5;
4
5create table alunos (alu_id int not null auto_increment,
6					 alu_nome varchar(60) not null,
7					 alu_local varchar(30),
8					 alu_dnsc date not null,
9					 alu_sexo char(1) not null,
10					 alu_email varchar(30),
11					 alu_cur_id int,
12					 primary key (alu_id));
13		     		     
14create table cursos (cur_id int not null auto_increment,
15					 cur_nome varchar(40) not null,
16					 primary key (cur_id));
17		           
18create table departamentos (dep_id int not null auto_increment,
19						    dep_nome varchar(60) not null,
20							dep_sigla char(3) not null,
21							primary key (dep_id));	     
22		           
23create table disciplinas (dis_id int not null auto_increment,
24						  dis_nome varchar(40) not null,
25					      dis_creditos tinyint not null,
26						  dis_dep_id int,
27						  primary key (dis_id));
28		    
29create table planoestudos (pla_cur_id int not null,
30						   pla_dis_id int not null,
31						   pla_semestre tinyint not null,
32						   primary key (pla_cur_id, pla_dis_id));		
33		            		                 		     
34create table inscricoes (ins_id int not null auto_increment,
35						 ins_alu_id int not null,
36						 ins_pla_cur_id int not null,
37						 ins_pla_dis_id int not null,
38						 ins_dt_inscricao date not null,
39						 ins_dt_avaliacao date,
40						 ins_nota decimal(4,2),
41						 primary key (ins_id));
42
43-- Chaves estrangeiras
44alter table alunos add constraint alunos_fk_cursos
45            foreign key (alu_cur_id) references cursos(cur_id) 
46			ON DELETE NO ACTION ON UPDATE NO ACTION;
47            
48alter table disciplinas add constraint disciplinas_fk_departamentos
49            foreign key (dis_dep_id) references departamentos(dep_id) 
50			ON DELETE NO ACTION ON UPDATE NO ACTION;
51            
52alter table planoestudos add constraint planoestudos_fk_cursos
53            foreign key (pla_cur_id) references cursos(cur_id) 
54			ON DELETE NO ACTION ON UPDATE NO ACTION;            
55            
56alter table planoestudos add constraint planoestudos_fk_disciplinas
57            foreign key (pla_dis_id) references disciplinas(dis_id) 
58			ON DELETE NO ACTION ON UPDATE NO ACTION;            
59
60alter table inscricoes add constraint inscricoes_fk_alunos
61            foreign key (ins_alu_id) references alunos(alu_id) 
62			-- ON DELETE CASCADE ON UPDATE NO ACTION; 
63			ON DELETE NO ACTION ON UPDATE NO ACTION; 
64                        
65alter table inscricoes add constraint inscricoes_fk_planoestudos
66            foreign key (ins_pla_cur_id,ins_pla_dis_id) references planoestudos(pla_cur_id,pla_dis_id) 
67			ON DELETE NO ACTION ON UPDATE NO ACTION;