/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     28/09/2010 10:44:36                          */
/*==============================================================*/


drop table if exists academic_area;

drop table if exists allowed_pages_by_process;

drop table if exists attendees_level_optional;

drop table if exists authority;

drop table if exists authority_charge;

drop table if exists cat_period;

drop table if exists cat_subject;

drop table if exists classroom;

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 degree;

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 language;

drop table if exists level;

drop table if exists level_section_year;

drop table if exists level_section_year_specialty;

drop table if exists master_degree;

drop table if exists medical_file;

drop table if exists nationality;

drop table if exists optional_subject;

drop table if exists optional_subject_level_specialty;

drop table if exists period;

drop table if exists period_group_year;

drop table if exists pre_registration;

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_by_course;

drop table if exists student_guardian;

drop table if exists subject;

drop table if exists subject_course;

drop table if exists subject_course_period;

drop table if exists teacher_degree;

drop table if exists teacher_hours_year;

drop table if exists teacher_master_degree;

drop table if exists teacher_specialty;

drop table if exists university;

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: allowed_pages_by_process                              */
/*==============================================================*/
create table allowed_pages_by_process
(
   serial_prc           int,
   url_dsc              varchar(1024) not null
)
type = innodb;

/*==============================================================*/
/* Table: attendees_level_optional                              */
/*==============================================================*/
create table attendees_level_optional
(
   serial_atd           int not null auto_increment,
   serial_osl           int,
   serial_std           int,
   status_atd           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_atd)
)
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: cat_subject                                           */
/*==============================================================*/
create table cat_subject
(
   serial_csb           int not null auto_increment,
   serial_lng           int,
   cat_serial_csb       int,
   serial_aca           int,
   code_csb             varchar(8) not null,
   name_csb             varchar(255) not null,
   status_csb           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_csb)
)
type = innodb;

/*==============================================================*/
/* Table: classroom                                             */
/*==============================================================*/
create table classroom
(
   serial_clr           int not null auto_increment,
   name_clr             varchar(80) not null,
   capacity_clr         int not null,
   description_clr      text not null,
   status_clr           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_clr)
)
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: degree                                                */
/*==============================================================*/
create table degree
(
   serial_dgr           int not null auto_increment,
   name_dgr             varchar(255) not null,
   status_dgr           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_dgr)
)
type = innodb;

/*==============================================================*/
/* Table: employee                                              */
/*==============================================================*/
create table employee
(
   serial_emp           int not null auto_increment,
   serial_grd           int,
   serial_nnt           int,
   serial_ste           int,
   serial_cou           int,
   serial_tst           int,
   serial_mdg           int,
   serial_tdg           int,
   serial_dgr           int,
   serial_unv           int,
   serial_tmd           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,
   specialty_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,
   address_emp          text not null,
   city_emp             varchar(150) 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),
   family_dependents_emp int,
   student_dependents_emp int,
   teaching_since_tea_emp date,
   seniority_university_tea_emp int,
   date_start_in_school_emp date 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',
   status_eba           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_aca, serial_emp)
)
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,
   first_name_grd       varchar(512) not null,
   last_name_grd        varchar(512) not null,
   birthdate_grd        date not null,
   gender_grd           enum('MALE','FEMALE') not null,
   address_grd          text not null,
   phone_grd            varchar(13) not null,
   status_grd           enum('ACTIVE','INACTIVE','BLOCKED') not null default 'ACTIVE',
   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_hbg           int not null auto_increment,
   serial_gph           int,
   serial_hrs           int,
   status_hbg           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   type_hbg             enum('NORMAL','LUNCH') not null default 'NORMAL',
   primary key (serial_hbg)
)
type = innodb;

/*==============================================================*/
/* Table: language                                              */
/*==============================================================*/
create table language
(
   serial_lng           int not null auto_increment,
   abbreviation_lng     varchar(3),
   name_lng             varchar(50) not null,
   primary key (serial_lng)
)
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: master_degree                                         */
/*==============================================================*/
create table master_degree
(
   serial_mdg           int not null auto_increment,
   name_mdg             varchar(255) not null,
   status_mdg           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_mdg)
)
type = innodb;

/*==============================================================*/
/* Table: medical_file                                          */
/*==============================================================*/
create table medical_file
(
   serial_std           int,
   chronic_diseases_smf enum('YES','NO') not null,
   chronic_diseases_desc_smf text
)
type = innodb;

/*==============================================================*/
/* Table: nationality                                           */
/*==============================================================*/
create table nationality
(
   serial_nnt           int not null auto_increment,
   name_nnt             varchar(255) not null,
   status_nnt           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_nnt)
)
type = innodb;

/*==============================================================*/
/* Table: optional_subject                                      */
/*==============================================================*/
create table optional_subject
(
   serial_osb           int not null auto_increment,
   serial_csb           int,
   serial_clg           int,
   serial_aca           int,
   serial_emp           int,
   status_osb           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_osb)
)
type = innodb;

/*==============================================================*/
/* Table: optional_subject_level_specialty                      */
/*==============================================================*/
create table optional_subject_level_specialty
(
   serial_osl           int not null auto_increment,
   serial_osb           int,
   serial_spt           int,
   serial_lvl           int,
   serial_scy           int,
   serial_set           int,
   status_osl           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   grade_type_osl       enum('QUALITATIVE','QUANTITATIVE') not null default 'QUANTITATIVE',
   primary key (serial_osl)
)
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: pre_registration                                      */
/*==============================================================*/
create table pre_registration
(
   serial_prt           int not null auto_increment,
   serial_lvl           int,
   serial_nnt           int,
   serial_usr           int,
   serial_dgr           int,
   first_name_prt       varchar(512) not null,
   last_name_prt        varchar(512) not null,
   relationship_prt     enum('PARENT','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') not null,
   birthdate_prt        date not null,
   gender_prt           enum('MALE','FEMALE') not null,
   address_prt          text not null,
   phone_prt            varchar(13) not null,
   status_prt           enum('ACCEPTED','DENIED','STAND_BY','PROCESSED') not null default 'STAND_BY',
   first_name_father_prt varchar(100),
   last_name_father_prt varchar(100),
   first_name_mother_prt varchar(100),
   last_name_mother_prt varchar(100),
   city_birth_prt       varchar(100) not null,
   blood_type_prt       varchar(10) not null,
   allergies_prt        text,
   lives_with_prt       enum('PARENT','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') not null,
   photo_prt            varchar(1024) not null,
   rep_first_name_prt   varchar(100) not null,
   rep_last_name_prt    varchar(100) not null,
   rep_relationship_prt enum('PARENT','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') not null,
   rep_birthdate_prt    date not null,
   rep_city_birth_prt   varchar(100) not null,
   rep_marital_status_prt enum('SINGLE','MARRIED','DIVORCED','WIDOWED','UNMARRIED PARTNERS') not null,
   rep_occupation_prt   varchar(100) not null,
   rep_address_prt      text not null,
   rep_phone_prt        varchar(13) not null,
   rep_mobile_prt       varchar(13),
   rep_company_name_prt varchar(80),
   rep_company_activity_prt text,
   rep_company_phone_prt varchar(13),
   rep_photo_prt        varchar(1024) not null,
   rep_gender_prt       enum('MALE','FEMALE') not null,
   primary key (serial_prt)
)
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,
   status_prc           enum('LOCKABLE','UNLOCKABLE') not null default 'LOCKABLE',
   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,
   status_ppr           enum('ACTIVE','BLOCKED') default 'ACTIVE'
)
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_current_lvl   int,
   serial_nnt           int,
   serial_usr           int not null,
   id_std               varchar(13) not null,
   first_name_std       varchar(255) not null,
   last_name_std        varchar(255) not null,
   birthdate_std        date not null,
   gender_std           enum('MALE','FEMALE') not null,
   address_std          text,
   phone_std            varchar(13),
   status_std           enum('ACTIVE','INACTIVE','BLOCKED') not null default 'INACTIVE',
   primary key (serial_std)
)
type = innodb;

/*==============================================================*/
/* Table: student_by_course                                     */
/*==============================================================*/
create table student_by_course
(
   serial_std           int not null,
   serial_crs           int not null,
   status_stc           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_std, serial_crs)
)
type = innodb;

/*==============================================================*/
/* Table: student_guardian                                      */
/*==============================================================*/
create table student_guardian
(
   serial_grd           int,
   serial_std           int,
   relationship_stg     enum('PARENT','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') not null
)
type = innodb;

/*==============================================================*/
/* Table: subject                                               */
/*==============================================================*/
create table subject
(
   serial_sbj           int not null auto_increment,
   serial_csb           int,
   serial_spt           int,
   serial_lvl           int,
   serial_scy           int,
   serial_set           int,
   status_sbj           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_sbj)
)
type = innodb;

/*==============================================================*/
/* Table: subject_course                                        */
/*==============================================================*/
create table subject_course
(
   serial_sbc           int not null auto_increment,
   serial_sbj           int,
   serial_crs           int,
   serial_aca           int,
   serial_emp           int,
   status_sbc           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   grade_type_sbc       enum('QUALITATIVE','QUANTITATIVE') not null default 'QUANTITATIVE',
   hours_week_sbc       int not null,
   primary key (serial_sbc)
)
type = innodb;

/*==============================================================*/
/* Table: subject_course_period                                 */
/*==============================================================*/
create table subject_course_period
(
   serial_prd           int,
   serial_sbc           int,
   status_scp           enum('ACTIVE','INACTIVE') not null default 'ACTIVE'
)
type = innodb;

/*==============================================================*/
/* Table: teacher_degree                                        */
/*==============================================================*/
create table teacher_degree
(
   serial_tdg           int not null auto_increment,
   name_tdg             varchar(255) not null,
   status_tdg           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_tdg)
)
type = innodb;

/*==============================================================*/
/* Table: teacher_hours_year                                    */
/*==============================================================*/
create table teacher_hours_year
(
   serial_thy           int not null auto_increment,
   serial_scy           int,
   serial_emp           int,
   start_hour_thy       time not null,
   end_hour_thy         time not null,
   status_thy           enum('ACTIVE','INACTIVE','ASSIGNED') not null default 'ACTIVE',
   labor_day_thy        enum('MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY','SUNDAY') not null,
   primary key (serial_thy)
)
type = innodb;

/*==============================================================*/
/* Table: teacher_master_degree                                 */
/*==============================================================*/
create table teacher_master_degree
(
   serial_tmd           int not null auto_increment,
   name_tmd             varchar(255) not null,
   status_tmd           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_tmd)
)
type = innodb;

/*==============================================================*/
/* Table: teacher_specialty                                     */
/*==============================================================*/
create table teacher_specialty
(
   serial_tst           int not null auto_increment,
   name_tst             varchar(255) not null,
   status_tst           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_tst)
)
type = innodb;

/*==============================================================*/
/* Table: university                                            */
/*==============================================================*/
create table university
(
   serial_unv           int not null auto_increment,
   name_unv             varchar(255) not null,
   status_unv           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   primary key (serial_unv)
)
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,
   status_upr           enum('ACTIVE','INACTIVE') not null default 'ACTIVE'
)
type = innodb;

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

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

alter table attendees_level_optional add constraint fk_subject_for_attendees foreign key (serial_osl)
      references optional_subject_level_specialty (serial_osl) on delete restrict on update restrict;

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 cat_subject add constraint fk_child_parent foreign key (cat_serial_csb)
      references cat_subject (serial_csb) on delete restrict on update restrict;

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

alter table cat_subject add constraint fk_relationship_68 foreign key (serial_lng)
      references language (serial_lng) 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 add constraint fk_relationship_54 foreign key (serial_nnt)
      references nationality (serial_nnt) on delete restrict on update restrict;

alter table employee add constraint fk_relationship_55 foreign key (serial_tdg)
      references teacher_degree (serial_tdg) on delete restrict on update restrict;

alter table employee add constraint fk_relationship_56 foreign key (serial_tmd)
      references teacher_master_degree (serial_tmd) on delete restrict on update restrict;

alter table employee add constraint fk_relationship_57 foreign key (serial_tst)
      references teacher_specialty (serial_tst) on delete restrict on update restrict;

alter table employee add constraint fk_relationship_58 foreign key (serial_dgr)
      references degree (serial_dgr) on delete restrict on update restrict;

alter table employee add constraint fk_relationship_59 foreign key (serial_mdg)
      references master_degree (serial_mdg) on delete restrict on update restrict;

alter table employee add constraint fk_relationship_60 foreign key (serial_unv)
      references university (serial_unv) 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 medical_file add constraint fk_relationship_65 foreign key (serial_std)
      references student (serial_std) on delete restrict on update restrict;

alter table optional_subject add constraint fk_relationship_53 foreign key (serial_csb)
      references cat_subject (serial_csb) on delete restrict on update restrict;

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

alter table optional_subject add constraint fk_teacher_for_optional foreign key (serial_aca, serial_emp)
      references employee_area (serial_aca, serial_emp) on delete restrict on update restrict;

alter table optional_subject_level_specialty add constraint fk_level_with_optional 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 optional_subject_level_specialty add constraint fk_optional_for_level foreign key (serial_osb)
      references optional_subject (serial_osb) 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 pre_registration add constraint fk_registered_nationality foreign key (serial_nnt)
      references nationality (serial_nnt) on delete restrict on update restrict;

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

alter table pre_registration add constraint fk_relationship_67 foreign key (serial_dgr)
      references degree (serial_dgr) on delete restrict on update restrict;

alter table pre_registration add constraint fk_user_register foreign key (serial_usr)
      references user (serial_usr) 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_relationship_61 foreign key (serial_nnt)
      references nationality (serial_nnt) on delete restrict on update restrict;

alter table student add constraint fk_relationship_63 foreign key (serial_current_lvl)
      references level (serial_lvl) 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_by_course add constraint fk_course_for_student foreign key (serial_crs)
      references course (serial_crs) on delete restrict on update restrict;

alter table student_by_course add constraint fk_student_in_course foreign key (serial_std)
      references student (serial_std) 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 subject add constraint fk_subject_fo__level 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 subject add constraint fk_year_subject foreign key (serial_csb)
      references cat_subject (serial_csb) on delete restrict on update restrict;

alter table subject_course add constraint fk_course_with_subject foreign key (serial_crs)
      references course (serial_crs) on delete restrict on update restrict;

alter table subject_course add constraint fk_is_teacher foreign key (serial_aca, serial_emp)
      references employee_area (serial_aca, serial_emp) on delete restrict on update restrict;

alter table subject_course add constraint fk_subject_for_course foreign key (serial_sbj)
      references subject (serial_sbj) on delete restrict on update restrict;

alter table subject_course_period add constraint fk_relationship_71 foreign key (serial_prd)
      references period (serial_prd) on delete restrict on update restrict;

alter table subject_course_period add constraint fk_relationship_72 foreign key (serial_sbc)
      references subject_course (serial_sbc) on delete restrict on update restrict;

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

alter table teacher_hours_year add constraint fk_relationship_70 foreign key (serial_scy)
      references school_year (serial_scy) 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;

