CREATE TABLE camps.student_admission_master ( sam_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, old_admission_no bigint(20) unsigned DEFAULT NULL, student_id bigint(20) unsigned DEFAULT NULL, doa date DEFAULT NULL, branch_id int(10) unsigned DEFAULT NULL, sp_id bigint(20) unsigned DEFAULT NULL, student_status enum('CONTINUING','DISCONTINUED','DECEASED','DEBARRED','COMPLETED','LONG ABSENT') DEFAULT NULL, inserted_by bigint(20) unsigned DEFAULT NULL, inserted_date datetime DEFAULT NULL, updated_by bigint(20) unsigned DEFAULT NULL, updated_date datetime DEFAULT NULL, PRIMARY KEY (sam_id), FOREIGN KEY (student_id) REFERENCES camps.student_master (student_id), FOREIGN KEY (branch_id) REFERENCES camps.master_branch (branch_id));



CREATE TABLE camps.student_promotion ( sp_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, term_id INT(10) UNSIGNED DEFAULT NULL, branch_id INT(10) UNSIGNED DEFAULT NULL, student_id BIGINT(20) UNSIGNED DEFAULT NULL, prog_period_id INT(10) UNSIGNED DEFAULT NULL, roll_no VARCHAR(50) DEFAULT NULL, section VARCHAR(10) DEFAULT NULL, STATUS INT(1) UNSIGNED DEFAULT '1', inserted_by BIGINT(20) UNSIGNED DEFAULT NULL, inserted_date DATETIME DEFAULT NULL, updated_by BIGINT(20) UNSIGNED DEFAULT NULL, updated_date DATETIME DEFAULT NULL, PRIMARY KEY (sp_id), UNIQUE KEY (term_id,student_id),FOREIGN KEY (term_id) REFERENCES master_programme_term (term_id), FOREIGN KEY (student_id) REFERENCES student_master (student_id), FOREIGN KEY (branch_id) REFERENCES master_branch (branch_id), FOREIGN KEY (prog_period_id) REFERENCES master_programme_period_det (prog_period_id));



ALTER TABLE camps.student_admission_master add FOREIGN KEY (sp_id) REFERENCES camps.student_promotion (sp_id);



CREATE TABLE camps.student_transfer_certificate (stc_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, student_id BIGINT(20) UNSIGNED DEFAULT NULL, tc_date DATE DEFAULT NULL, last_class_date DATE DEFAULT NULL, reason_for_leaving VARCHAR(250) DEFAULT NULL, tc_conduct VARCHAR(150) DEFAULT NULL, next_higher_class VARCHAR(150) DEFAULT NULL, description TEXT, previous_tc_no BIGINT(20) UNSIGNED DEFAULT NULL, inserted_by BIGINT(20) UNSIGNED DEFAULT NULL, inserted_date DATETIME DEFAULT NULL, updated_by BIGINT(20) UNSIGNED DEFAULT NULL, updated_date DATETIME DEFAULT NULL, PRIMARY KEY (stc_id), UNIQUE KEY student_id (student_id,previous_tc_no), FOREIGN KEY (student_id) REFERENCES student_master (student_id));



ALTER TABLE camps.`master_programme` ADD COLUMN (programme_level VARCHAR(50));




Download Queries