/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     21/08/2015 12:19:03                          */
/*==============================================================*/


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;

drop table if exists usuario;

/*==============================================================*/
/* Table: alumno                                                */
/*==============================================================*/
create table alumno
(
   serial_alu           int(11) not null auto_increment,
   serial_par           int(11) not null,
   nombre_alu           varchar(50) not null,
   apellido_alu         varchar(50) not null,
   clave_alu            varchar(25) not null,
   primary key (serial_alu),
   unique key AK_CLAVEALU_UNICA (clave_alu)
)ENGINE=InnoDB;

/*==============================================================*/
/* Table: curso                                                 */
/*==============================================================*/
create table curso
(
   serial_cur           int(11) not null auto_increment,
   nombre_cur           varchar(50) not null,
   descripcion_cur      varchar(50) not null,
   primary key (serial_cur)
)ENGINE=InnoDB;

/*==============================================================*/
/* Table: curso_materia                                         */
/*==============================================================*/
create table curso_materia
(
   serial_curmat        int(11) not null auto_increment,
   serial_par           int(11) not null,
   serial_pro           int(11) not null,
   serial_cur           int(11) not null,
   serial_mat           int(11) not null,
   descripcion_curmat   varchar(50) default NULL,
   primary key (serial_curmat)
)ENGINE=InnoDB;

/*==============================================================*/
/* Table: materia                                               */
/*==============================================================*/
create table materia
(
   serial_mat           int(11) not null auto_increment,
   nombre_mat           varchar(50) not null,
   descripcion_mat      varchar(50) default NULL,
   primary key (serial_mat)
)ENGINE=InnoDB;

/*==============================================================*/
/* Table: novedad                                               */
/*==============================================================*/
create table novedad
(
   serial_nov           int(11) not null auto_increment,
   serial_curmat        int(11) not null,
   serial_alu           int(11) not null,
   descripcion_nov      varchar(100) not null,
   observacion_nov      varchar(50) default NULL,
   fecha_nov            date not null,
   primary key (serial_nov)
)ENGINE=InnoDB;

/*==============================================================*/
/* Table: paralelo                                              */
/*==============================================================*/
create table paralelo
(
   serial_par           int(11) not null auto_increment,
   serial_cur           int(11) not null,
   nombre_par           varchar(12) default NULL,
   primary key (serial_par)
)ENGINE=InnoDB;

/*==============================================================*/
/* Table: profesor                                              */
/*==============================================================*/
create table profesor
(
   serial_pro           int(11) not null auto_increment,
   nombre_pro           varchar(50) not null,
   apellido_pro         varchar(50) not null,
   telefono_pro         varchar(12) default NULL,
   direccion_pro        varchar(50) default NULL,
   cedula_pro           varchar(15) not null,
   clave_pro            varchar(20) not null,
   primary key (serial_pro),
   unique key AK_CED_RUC_UNICO (cedula_pro),
   unique key AK_CLAVE_UNICA (clave_pro)
)ENGINE=InnoDB;

/*==============================================================*/
/* Table: usuario                                               */
/*==============================================================*/
create table usuario
(
   serial_usu           int(11) not null auto_increment,
   apellido_usu         varchar(50) not null,
   nombre_usu           varchar(50) not null,
   clave_usu            varchar(25) not null,
   primary key (serial_usu)
)ENGINE=InnoDB;

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

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

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

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

alter table curso_materia add constraint FK_RELATIONSHIP_8 foreign key (serial_par)
      references paralelo (serial_par) 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;

