3WRxeyy6

1Drop schema if exists xestion_lumnos;
2create schema xestion_alumnos charset utf8 collate utf8_spanish2_ci;
3use xestion_alumnos;
4create table curso (
5codigo char(4)  charset latin1 collate latin1_bin not null primary key,
6nome varchar(60) not null
7) engine = innodb;
8
9create table modulo (
10codigo char(4)  charset latin1 collate latin1_bin not null primary key,
11nome varchar(60) not null
12) engine = innodb;
13
14create table horario (
15codigocurso char(4)  charset latin1 collate latin1_bin not null primary key,
16codigomodulo char(4)  charset latin1 collate latin1_bin not null primary key,
17dia smallint not null primary key,
18hora smallint not null primary key,
19auta varchar(5) not null
20) engine = innodb;
21
22create table profesor (
23codigo char(4)  charset latin1 collate latin1_bin not null primary key,
24nome varchar(60) not null,
25enderezo varchar(60) null,
26telefono char(9) charset latin1 collate latin1_bin null,
27) engine = innodb;
28
29create table imparte (
30codigoprof char(4)  charset latin1 collate latin1_bin not null primary key,
31codigomodulo char(4)  charset latin1 collate latin1_bin not null primary key,
32codigocurso char(4)  charset latin1 collate latin1_bin not null primary key,
33) engine = innodb;
34
35create table alumno (
36codigo integer not null primary key,
37nome varchar(60) not null,
38enderezo varchar(60) null,
39telefono char(9) charset latin1 collate latin1_bin primary null,
40cursa varchar(4) null
41)engine =innodb;
42
43create table falta (
44codigoalumno integer not null primary key,
45data date not null primary key,
46hora smallint not null primary key,
47dia smallint not null,
48xustificada char(1) charset latin1 collate latin1_bin not null,
49xustidicacion varchar(100) null
50) engine =innodb;
51
52create table dia (
53numero smallint primary key,
54nome varchar(10) not null,
55) engine =innodb;
56
57create table hora (
58numero smallint not null primary key,
59horacomezo varchar(5) not null,
60horafinal varchar(5) not null
61) engine=innodb;
62
63alter table horario
64add foreing key (codigocurso) references curso (codigo) on delete cascade,
65add foreing key (codigomodulo) references modulo (codigo) ,
66add foreing key (dia) references dia (numero),
67add foreing key (hora) references hora (numero),
68
69alter table imparte
70add foreing key (codigoprof) references profesor (codigo) on delete cascade,
71add foreing key (codigomodulo) references modulo (codigo) on delete,
72cascade,
73add foreing key (codigocurso) references curso (codigo) on delete cascade,
74
75
76
77alter table alumno
78add foreing key (cursa) references curso (codigo) on delete set null,
79
80
81alter table falta
82add foreing key (codigoalumno) references alumno (codigo) on delete cascade,
83add foreing key (dia) references dia (numero),
84add foreing key (hora) references hora (numero);