/*==============================================================*/ /* DBMS name: MySQL 5.0 */ /* Created on: 05/10/2010 11:57:34 */ /*==============================================================*/ drop table if exists academic_area; drop table if exists activity_type; 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 bus; drop table if exists bus_driver; drop table if exists bus_shift_year; 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 grade_criteria; 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 pre_registration_period; drop table if exists process; drop table if exists profile; drop table if exists profile_process; drop table if exists route; 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 shift; 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 student_year; 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: activity_type */ /*==============================================================*/ create table activity_type ( serial_att int not null auto_increment, name_att varchar(256) not null, status_att enum('ACTIVE','INACTIVE') not null default 'ACTIVE', primary key (serial_att) ) 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: bus */ /*==============================================================*/ create table bus ( serial_bus int not null auto_increment, code_bus varchar(13) not null, plate_num_bus varchar(12) not null, description_bus text not null, status_bus enum('ACTIVE','INACTIVE') not null default 'ACTIVE', primary key (serial_bus) ) type = innodb; /*==============================================================*/ /* Table: bus_driver */ /*==============================================================*/ create table bus_driver ( serial_bdr int not null auto_increment, id_bdr varchar(13) not null, first_name_bdr varchar(512) not null, last_name_bdr varchar(512) not null, mobile1_bdr varchar(13), mobile2_bdr varchar(13), phone1_bdr varchar(13) not null, phone2_bdr varchar(13), phone3_bdr varchar(13), address_bdr text not null, drive_license_bdr varchar(20) not null, license_expire_date_bdr date not null, status_bdr enum('ACTIVE','INACTIVE') not null default 'ACTIVE', primary key (serial_bdr) ) type = innodb; /*==============================================================*/ /* Table: bus_shift_year */ /*==============================================================*/ create table bus_shift_year ( serial_scy int, serial_bus int, serial_sft int, serial_rot int, serial_bdr int, bus_serial_bdr int, status_bpy enum('ACTIVE','INACTIVE') not null default 'ACTIVE' ) 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: grade_criteria */ /*==============================================================*/ create table grade_criteria ( serial_gct int not null auto_increment, name_gct varchar(256) not null, status_gct enum('ACTIVE','INACTIVE') not null default 'ACTIVE', primary key (serial_gct) ) 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', occupation_grd varchar(50), company_name_grd varchar(100), company_activity_grd varchar(80), company_phone_grd varchar(13), 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) not null, start_date_prd date not null, end_date_prd date not null, max_grade_date_prd date not null, status_prd enum('ACTIVE','INACTIVE','BLOCKED','ENDED') not null default 'ACTIVE', min_grade_prd decimal(5,2) not null, max_grade_prd decimal(5,2) not null, warning_grade_prd decimal(5,2) not null, weight_prd decimal(5,2) not null, 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_nnt int, serial_usr int, serial_dgr int, serial_lvl int, serial_scy int, serial_set 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, rep_id_prt varchar(13) not null, primary key (serial_prt) ) type = innodb; /*==============================================================*/ /* Table: pre_registration_period */ /*==============================================================*/ create table pre_registration_period ( serial_scy int, start_date_prp date not null, end_date_prp date not null, status_prp enum('ACTIVE','INACTIVE','CLOSED','OPEN') not null default 'ACTIVE' ) 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','NON-LOCKABLE') 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: route */ /*==============================================================*/ create table route ( serial_rot int not null auto_increment, code_rot varchar(10) not null, description_rot text not null, status_rot enum('ACTIVE','INACTIVE') not null default 'ACTIVE', primary key (serial_rot) ) 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: shift */ /*==============================================================*/ create table shift ( serial_sft int not null auto_increment, name_sft varchar(120) not null, status_sft enum('ACTIVE','INACTIVE') not null default 'ACTIVE', primary key (serial_sft) ) 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_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: student_year */ /*==============================================================*/ create table student_year ( serial_std int, serial_lvl int, serial_scy int, serial_set int, matricule_number_sty varchar(20) not null, status_sty enum('ACCEPTED','REGISTERED','MATRICULE') 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', weight_scp decimal(5,2) ) 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 bus_shift_year add constraint fk_relationship_76 foreign key (serial_scy) references school_year (serial_scy) on delete restrict on update restrict; alter table bus_shift_year add constraint fk_relationship_77 foreign key (bus_serial_bdr) references bus_driver (serial_bdr) on delete restrict on update restrict; alter table bus_shift_year add constraint fk_relationship_78 foreign key (serial_bus) references bus (serial_bus) on delete restrict on update restrict; alter table bus_shift_year add constraint fk_relationship_79 foreign key (serial_rot) references route (serial_rot) on delete restrict on update restrict; alter table bus_shift_year add constraint fk_relationship_80 foreign key (serial_sft) references shift (serial_sft) on delete restrict on update restrict; alter table bus_shift_year add constraint fk_relationship_81 foreign key (serial_bdr) references bus_driver (serial_bdr) 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, serial_scy, serial_set) references level_section_year (serial_lvl, serial_scy, serial_set) 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 pre_registration_period add constraint fk_relationship_73 foreign key (serial_scy) references school_year (serial_scy) 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_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 student_year add constraint fk_relationship_74 foreign key (serial_std) references student (serial_std) on delete restrict on update restrict; alter table student_year add constraint fk_relationship_75 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 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;