/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     27/08/2010 9:57:10                           */
/*==============================================================*/


drop table if exists academic_area;

drop table if exists authority;

drop table if exists authority_charge;

drop table if exists cat_period;

drop table if exists class_group;

drop table if exists country;

drop table if exists course;

drop table if exists currency;

drop table if exists employee;

drop table if exists employee_area;

drop table if exists group_hour;

drop table if exists group_hour_level;

drop table if exists guardian;

drop table if exists hour;

drop table if exists hour_by_group;

drop table if exists level;

drop table if exists level_section_year;

drop table if exists level_section_year_specialty;

drop table if exists period;

drop table if exists period_group_year;

drop table if exists process;

drop table if exists profile;

drop table if exists profile_process;

drop table if exists school_setup;

drop table if exists school_year;

drop table if exists section;

drop table if exists section_by_year;

drop table if exists specialty;

drop table if exists state;

drop table if exists student;

drop table if exists student_guardian;

drop table if exists user;

drop table if exists user_profile;

/*==============================================================*/
/* Table: academic_area                                         */
/*==============================================================*/
create table academic_area
(
   serial_aca           int not null auto_increment,
   name_aca             varchar(150) not null,
   description_aca      text not null,
   status_aca           enum('ACTIVE','INACTIVE') not null,
   primary key (serial_aca)
)
type = innodb;

/*==============================================================*/
/* Table: authority                                             */
/*==============================================================*/
create table authority
(
   serial_aut           int not null auto_increment,
   serial_atc           int,
   degree_aut           varchar(50) not null,
   first_name_aut       varchar(100) not null,
   last_name_aut        varchar(100) not null,
   in_charge_aut        enum('YES','NO') not null,
   primary key (serial_aut)
)
type = innodb;

/*==============================================================*/
/* Table: authority_charge                                      */
/*==============================================================*/
create table authority_charge
(
   serial_atc           int not null auto_increment,
   name_atc             varchar(100) not null,
   description_atc      text,
   status_atc           enum('ACTIVE','INACTIVE') not null,
   primary key (serial_atc)
)
type = innodb;

/*==============================================================*/
/* Table: cat_period                                            */
/*==============================================================*/
create table cat_period
(
   serial_cpd           int not null auto_increment,
   name_cpd             varchar(100) not null,
   description_cpd      text,
   status_cpd           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_cpd)
)
type = innodb;

/*==============================================================*/
/* Table: class_group                                           */
/*==============================================================*/
create table class_group
(
   serial_clg           int not null auto_increment,
   name_clg             varchar(150) not null,
   description_clg      text,
   status_clg           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_clg)
)
type = innodb;

/*==============================================================*/
/* Table: country                                               */
/*==============================================================*/
create table country
(
   serial_cou           int not null auto_increment,
   name_cou             varchar(100),
   status_cou           enum('ACTIVE','INACTIVE') default 'ACTIVE',
   primary key (serial_cou)
)
type = innodb;

/*==============================================================*/
/* Table: course                                                */
/*==============================================================*/
create table course
(
   serial_crs           int not null auto_increment,
   serial_clg           int not null,
   serial_spt           int not null,
   serial_lvl           int not null,
   serial_scy           int not null,
   serial_set           int not null,
   attendance_crs       enum('DAY','CLASS') not null default 'DAY',
   status_crs           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_crs)
)
type = innodb;

/*==============================================================*/
/* Table: currency                                              */
/*==============================================================*/
create table currency
(
   serial_crr           int not null auto_increment,
   abrevation_crr       varchar(4),
   name_crr             varchar(300) not null,
   status_crr           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_crr)
)
type = innodb;

/*==============================================================*/
/* Table: employee                                              */
/*==============================================================*/
create table employee
(
   serial_emp           int not null auto_increment,
   serial_grd           int,
   serial_ste           int,
   serial_cou           int,
   serial_usr           int not null,
   id_emp               varchar(13) not null,
   type_emp             set('TEACHING','ADMINISTRATIVE','SERVICES'),
   status_emp           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   first_name_emp       varchar(255) not null,
   last_name_emp        varchar(255) not null,
   degree_emp           varchar(150),
   gender_emp           enum('MALE','FEMALE') not null,
   marital_status_emp   enum('SINGLE','MARRIED','DIVORCED','WIDOWED','UNMARRIED PARTNERS'),
   birthdate_emp        date not null,
   nationality_emp      varchar(150) not null,
   address_emp          text not null,
   parish_emp           varchar(100),
   sector_emp           varchar(50),
   phone_emp            varchar(13) not null,
   phone2_emp           varchar(13),
   phone3_emp           varchar(13),
   cellphone_emp        varchar(13),
   email_emp            varchar(150),
   city_emp             varchar(150) not null,
   primary key (serial_emp)
)
type = innodb;

/*==============================================================*/
/* Table: employee_area                                         */
/*==============================================================*/
create table employee_area
(
   serial_aca           int not null,
   serial_emp           int not null,
   is_director_eba      enum('YES','NO') default 'NO'
)
type = innodb;

/*==============================================================*/
/* Table: group_hour                                            */
/*==============================================================*/
create table group_hour
(
   serial_gph           int not null auto_increment,
   name_gph             varchar(100) not null,
   description_gph      text,
   status_gph           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_gph)
)
type = innodb;

/*==============================================================*/
/* Table: group_hour_level                                      */
/*==============================================================*/
create table group_hour_level
(
   serial_gph           int not null,
   serial_lvl           int not null,
   serial_scy           int not null,
   serial_set           int not null,
   working_days_ghl     set('MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY','SUNDAY'),
   status_ghl           enum('ACTIVE','INACTIVE') default 'ACTIVE',
   primary key (serial_gph, serial_lvl, serial_scy, serial_set)
)
type = innodb;

/*==============================================================*/
/* Table: guardian                                              */
/*==============================================================*/
create table guardian
(
   serial_grd           int not null auto_increment,
   serial_emp           int,
   serial_usr           int not null,
   id_grd               varchar(13) not null,
   primary key (serial_grd)
)
type = innodb;

/*==============================================================*/
/* Table: hour                                                  */
/*==============================================================*/
create table hour
(
   serial_hrs           int not null auto_increment,
   start_hrs            time not null,
   end_hrs              time not null,
   status_hrs           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_hrs)
)
type = innodb;

/*==============================================================*/
/* Table: hour_by_group                                         */
/*==============================================================*/
create table hour_by_group
(
   serial_gph           int,
   serial_hrs           int,
   status_hbg           enum('ACTIVE','INACTIVE')
)
type = innodb;

/*==============================================================*/
/* Table: level                                                 */
/*==============================================================*/
create table level
(
   serial_lvl           int not null auto_increment,
   name_lvl             varchar(150) not null,
   description_lvl      text,
   status_lvl           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_lvl)
)
type = innodb;

/*==============================================================*/
/* Table: level_section_year                                    */
/*==============================================================*/
create table level_section_year
(
   serial_pgy           int,
   serial_lvl           int not null,
   serial_scy           int not null,
   serial_set           int not null,
   status_lsy           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_lvl, serial_scy, serial_set)
)
type = innodb;

/*==============================================================*/
/* Table: level_section_year_specialty                          */
/*==============================================================*/
create table level_section_year_specialty
(
   serial_spt           int not null,
   serial_lvl           int not null,
   serial_scy           int not null,
   serial_set           int not null,
   status_lss           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_spt, serial_lvl, serial_scy, serial_set)
)
type = innodb;

/*==============================================================*/
/* Table: period                                                */
/*==============================================================*/
create table period
(
   serial_prd           int not null auto_increment,
   serial_pgy           int,
   name_prd             varchar(255),
   start_date_prd       date not null,
   end_date_prd         date,
   status_prd           enum('ACTIVE','INACTIVE','BLOCKED','ENDED') not null default 'ACTIVE',
   primary key (serial_prd)
)
type = innodb;

/*==============================================================*/
/* Table: period_group_year                                     */
/*==============================================================*/
create table period_group_year
(
   serial_pgy           int not null auto_increment,
   serial_cpd           int,
   serial_scy           int,
   name_pgy             varchar(255),
   status_pgy           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_pgy)
)
type = innodb;

/*==============================================================*/
/* Table: process                                               */
/*==============================================================*/
create table process
(
   serial_prc           int not null auto_increment,
   prc_serial_prc       int,
   name_prc             varchar(150) not null,
   link_prc             varchar(1024) not null,
   weight_prc           decimal(5,1) not null,
   primary key (serial_prc)
)
type = innodb;

/*==============================================================*/
/* Table: profile                                               */
/*==============================================================*/
create table profile
(
   serial_prf           int not null auto_increment,
   name_prf             varchar(150) not null,
   type_prf             enum('INTERNAL','EXTERNAL') not null,
   status_prf           enum('ACTIVE','INACTIVE') not null,
   owner_prf            enum('SYSTEM','USER') not null default 'USER',
   primary key (serial_prf)
)
type = innodb;

/*==============================================================*/
/* Table: profile_process                                       */
/*==============================================================*/
create table profile_process
(
   serial_prf           int,
   serial_prc           int
)
type = innodb;

/*==============================================================*/
/* Table: school_setup                                          */
/*==============================================================*/
create table school_setup
(
   serial_ste           int,
   serial_crr           int,
   name_sst             varchar(1024) not null,
   city_sst             varchar(100) not null,
   address_sst          text not null,
   phone1_sst           varchar(20) not null,
   phone2_sst           varchar(20),
   phone3_sst           varchar(20),
   contact_name_sst     varchar(250) not null,
   contact_phone_sst    varchar(20) not null,
   logo_sst             varchar(100) not null,
   slogan_sst           varchar(300),
   main_color_sst       varchar(7) not null,
   secondary_color_sst  varchar(7) not null
)
type = innodb;

/*==============================================================*/
/* Table: school_year                                           */
/*==============================================================*/
create table school_year
(
   serial_scy           int not null auto_increment,
   start_date_scy       date not null,
   end_date_scy         date not null,
   status_scy           set('ACTIVE','CURRENT','ENDED','NEW','INACTIVE') not null default 'NEW',
   name_scy             varchar(150) not null,
   primary key (serial_scy)
)
type = innodb;

/*==============================================================*/
/* Table: section                                               */
/*==============================================================*/
create table section
(
   serial_set           int not null auto_increment,
   name_set             varchar(150) not null,
   description_set      text,
   status_set           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   type_set             enum('DAY','EVENING','NIGHT') not null default 'DAY',
   primary key (serial_set)
)
type = innodb;

/*==============================================================*/
/* Table: section_by_year                                       */
/*==============================================================*/
create table section_by_year
(
   serial_scy           int not null,
   serial_set           int not null,
   status_sby           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_scy, serial_set)
)
type = innodb;

/*==============================================================*/
/* Table: specialty                                             */
/*==============================================================*/
create table specialty
(
   serial_spt           int not null auto_increment,
   name_spt             varchar(150) not null,
   description_spt      text,
   status_spt           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_spt)
)
type = innodb;

/*==============================================================*/
/* Table: state                                                 */
/*==============================================================*/
create table state
(
   serial_ste           int not null auto_increment,
   serial_cou           int,
   name_ste             varchar(100) not null,
   status_ste           enum('ACTIVE','INACTIVE') default 'ACTIVE',
   primary key (serial_ste)
)
type = innodb;

/*==============================================================*/
/* Table: student                                               */
/*==============================================================*/
create table student
(
   serial_std           int not null auto_increment,
   serial_usr           int not null,
   id_std               varchar(13) not null,
   primary key (serial_std)
)
type = innodb;

/*==============================================================*/
/* Table: student_guardian                                      */
/*==============================================================*/
create table student_guardian
(
   serial_grd           int,
   serial_std           int
)
type = innodb;

/*==============================================================*/
/* Table: user                                                  */
/*==============================================================*/
create table user
(
   serial_usr           int not null auto_increment,
   serial_grd           int,
   serial_std           int,
   serial_emp           int,
   username_usr         varchar(150) not null,
   password_usr         varchar(300),
   photo_usr            varchar(1024),
   status_usr           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_usr)
)
type = innodb;

/*==============================================================*/
/* Table: user_profile                                          */
/*==============================================================*/
create table user_profile
(
   serial_prf           int,
   serial_usr           int
)
type = innodb;

alter table authority add constraint fk_has_charge foreign key (serial_atc)
      references authority_charge (serial_atc) on delete restrict on update restrict;

alter table course add constraint fk_selected_class foreign key (serial_clg)
      references class_group (serial_clg) on delete restrict on update restrict;

alter table course add constraint fk_selected_speciality foreign key (serial_spt, serial_lvl, serial_scy, serial_set)
      references level_section_year_specialty (serial_spt, serial_lvl, serial_scy, serial_set) on delete restrict on update restrict;

alter table employee add constraint fk_employee_birth_country foreign key (serial_cou)
      references country (serial_cou) on delete restrict on update restrict;

alter table employee add constraint fk_employee_birth_state foreign key (serial_ste)
      references state (serial_ste) on delete restrict on update restrict;

alter table employee add constraint fk_employee_guardian2 foreign key (serial_grd)
      references guardian (serial_grd) on delete restrict on update restrict;

alter table employee add constraint fk_employee_user foreign key (serial_usr)
      references user (serial_usr) on delete restrict on update restrict;

alter table employee_area add constraint fk_area_by_employee foreign key (serial_aca)
      references academic_area (serial_aca) on delete restrict on update restrict;

alter table employee_area add constraint fk_employee_in_area foreign key (serial_emp)
      references employee (serial_emp) on delete restrict on update restrict;

alter table group_hour_level add constraint fk_group_hour_for_level foreign key (serial_gph)
      references group_hour (serial_gph) on delete restrict on update restrict;

alter table group_hour_level add constraint fk_levels_in_group_hour foreign key (serial_lvl, serial_scy, serial_set)
      references level_section_year (serial_lvl, serial_scy, serial_set) on delete restrict on update restrict;

alter table guardian add constraint fk_employee_guardian foreign key (serial_emp)
      references employee (serial_emp) on delete restrict on update restrict;

alter table guardian add constraint fk_guardian_user foreign key (serial_usr)
      references user (serial_usr) on delete restrict on update restrict;

alter table hour_by_group add constraint fk_for_group foreign key (serial_gph)
      references group_hour (serial_gph) on delete restrict on update restrict;

alter table hour_by_group add constraint fk_is_in_group foreign key (serial_hrs)
      references hour (serial_hrs) on delete restrict on update restrict;

alter table level_section_year add constraint fk_level_in_section foreign key (serial_lvl)
      references level (serial_lvl) on delete restrict on update restrict;

alter table level_section_year add constraint fk_period_group_level foreign key (serial_pgy)
      references period_group_year (serial_pgy) on delete restrict on update restrict;

alter table level_section_year add constraint fk_section_for_level foreign key (serial_scy, serial_set)
      references section_by_year (serial_scy, serial_set) on delete restrict on update restrict;

alter table level_section_year_specialty add constraint fk_selcted_specialty foreign key (serial_spt)
      references specialty (serial_spt) on delete restrict on update restrict;

alter table level_section_year_specialty add constraint fk_selected_level foreign key (serial_lvl, serial_scy, serial_set)
      references level_section_year (serial_lvl, serial_scy, serial_set) on delete restrict on update restrict;

alter table period add constraint fk_periods_by_group foreign key (serial_pgy)
      references period_group_year (serial_pgy) on delete restrict on update restrict;

alter table period_group_year add constraint fk_periods_in_year foreign key (serial_scy)
      references school_year (serial_scy) on delete restrict on update restrict;

alter table period_group_year add constraint fk_period_for_year foreign key (serial_cpd)
      references cat_period (serial_cpd) on delete restrict on update restrict;

alter table process add constraint fk_parent_process foreign key (prc_serial_prc)
      references process (serial_prc) on delete restrict on update restrict;

alter table profile_process add constraint fk_has_process foreign key (serial_prf)
      references profile (serial_prf) on delete restrict on update restrict;

alter table profile_process add constraint fk_in_profile foreign key (serial_prc)
      references process (serial_prc) on delete restrict on update restrict;

alter table school_setup add constraint fk_currency_for_school foreign key (serial_crr)
      references currency (serial_crr) on delete restrict on update restrict;

alter table school_setup add constraint fk_school_state foreign key (serial_ste)
      references state (serial_ste) on delete restrict on update restrict;

alter table section_by_year add constraint fk_section_in_year foreign key (serial_set)
      references section (serial_set) on delete restrict on update restrict;

alter table section_by_year add constraint fk_year_sections foreign key (serial_scy)
      references school_year (serial_scy) on delete restrict on update restrict;

alter table state add constraint fk_country_state foreign key (serial_cou)
      references country (serial_cou) on delete restrict on update restrict;

alter table student add constraint fk_student_user foreign key (serial_usr)
      references user (serial_usr) on delete restrict on update restrict;

alter table student_guardian add constraint fk_has_guardian foreign key (serial_std)
      references student (serial_std) on delete restrict on update restrict;

alter table student_guardian add constraint fk_is_guardian foreign key (serial_grd)
      references guardian (serial_grd) on delete restrict on update restrict;

alter table user add constraint fk_employee_user2 foreign key (serial_emp)
      references employee (serial_emp) on delete restrict on update restrict;

alter table user add constraint fk_guardian_user2 foreign key (serial_grd)
      references guardian (serial_grd) on delete restrict on update restrict;

alter table user add constraint fk_student_user2 foreign key (serial_std)
      references student (serial_std) on delete restrict on update restrict;

alter table user_profile add constraint fk_for_user foreign key (serial_prf)
      references profile (serial_prf) on delete restrict on update restrict;

alter table user_profile add constraint fk_has_profile foreign key (serial_usr)
      references user (serial_usr) on delete restrict on update restrict;