/*==============================================================*/
/* dbms name:      mysql 5.0                                    */
/* created on:     21/05/2015 15:54:51                          */
/*==============================================================*/


drop table if exists alumno;

drop table if exists curso;

drop table if exists curso_materia;

drop table if exists materia;

drop table if exists novedad;

drop table if exists paralelo;

drop table if exists profesor;

/*==============================================================*/
/* table: alumno                                                */
/*==============================================================*/
create table alumno
(
   serial_alu           int not null,
   serial_par           int,
   serial_cur           int,
   nombre_alu           varchar(50) not null,
   apellido_alu         varchar(50) not null,
   clave_alu            varchar(25) not null,
   primary key (serial_alu)
);

/*==============================================================*/
/* table: curso                                                 */
/*==============================================================*/
create table curso
(
   serial_cur           int not null,
   nombre_cur           varchar(50) not null,
   paralelo_cur         varchar(5) not null,
   primary key (serial_cur)
);

/*==============================================================*/
/* table: curso_materia                                         */
/*==============================================================*/
create table curso_materia
(
   serial_curmat        int not null,
   serial_pro           int,
   serial_cur           int,
   serial_mat           int,
   descripcion_curmat   varchar(50),
   primary key (serial_curmat)
);

/*==============================================================*/
/* table: materia                                               */
/*==============================================================*/
create table materia
(
   serial_mat           int not null,
   nombre_mat           varchar(50) not null,
   descripcion_mat      varchar(50),
   primary key (serial_mat)
);

/*==============================================================*/
/* table: novedad                                               */
/*==============================================================*/
create table novedad
(
   serial_nov           int not null,
   serial_curmat        int,
   serial_alu           int,
   descripcion_nov      varchar(100) not null,
   observacion_nov      varchar(50),
   primary key (serial_nov)
);

/*==============================================================*/
/* table: paralelo                                              */
/*==============================================================*/
create table paralelo
(
   serial_par           int not null,
   serial_cur           int,
   nombre_par           varchar(12),
   primary key (serial_par)
);

/*==============================================================*/
/* table: profesor                                              */
/*==============================================================*/
create table profesor
(
   serial_pro           int not null,
   nombre_pro           varchar(50) not null,
   apellido_pro         varchar(50) not null,
   telefono_pro         varchar(12),
   direccion_pro        varchar(50),
   cedula_pro           varchar(15) not null,
   clave_pro            varchar(20) not null,
   primary key (serial_pro)
);

alter table alumno add constraint fk_relationship_4 foreign key (serial_cur)
      references curso (serial_cur) on delete restrict on update restrict;

alter table alumno add constraint fk_relationship_8 foreign key (serial_par)
      references paralelo (serial_par) on delete restrict on update restrict;

alter table curso_materia add constraint fk_relationship_1 foreign key (serial_cur)
      references curso (serial_cur) on delete restrict on update restrict;

alter table curso_materia add constraint fk_relationship_2 foreign key (serial_mat)
      references materia (serial_mat) on delete restrict on update restrict;

alter table curso_materia add constraint fk_relationship_3 foreign key (serial_pro)
      references profesor (serial_pro) on delete restrict on update restrict;

alter table novedad add constraint fk_relationship_5 foreign key (serial_alu)
      references alumno (serial_alu) on delete restrict on update restrict;

alter table novedad add constraint fk_relationship_6 foreign key (serial_curmat)
      references curso_materia (serial_curmat) on delete restrict on update restrict;

alter table paralelo add constraint fk_relationship_7 foreign key (serial_cur)
      references curso (serial_cur) on delete restrict on update restrict;
