CREATE TABLE curriculum.regulation_master (regulation_id int(10) unsigned NOT NULL AUTO_INCREMENT, branch_id int(10) unsigned DEFAULT NULL, regulation_name varchar(50) DEFAULT NULL, rules varchar(150) 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 (regulation_id),UNIQUE KEY (branch_id,regulation_name),FOREIGN KEY (branch_id) REFERENCES camps.master_branch (branch_id));



CREATE TABLE curriculum.subject_type (st_id int(10) unsigned NOT NULL AUTO_INCREMENT, st_name varchar(100) DEFAULT NULL, status int(1) unsigned NOT NULL DEFAULT 1, spl_type int(10) DEFAULT 0, PRIMARY KEY (st_id));



INSERT INTO curriculum.subject_type (type_name,status,spl_type) VALUES ('CORE-THEORY',1,3),('CORE-LABORATORY',1,3),('LANGUAGE ELECTIVES',1,10),('DISCIPLINE ELECTIVES',1,2),('PHYSICS ELECTIVES',1,5),('CHEMISTRY ELECTIVES',1,6),('ENTREPRENEURSHIP ELECTIVES',1,7),('ONE CREDIT COURSES',1,1),('SPECIAL COURSES',1,1),('PROJECT',1,3),('TECHNICAL SEMINAR',1,3),('SELF-STUDY ELECTIVES',1,1),('MATHEMATICS ELECTIVES',1,8),('OPEN ELECTIVES',1,2),('ADDITIONAL ONE CREDIT COURSE',1,1),('SKILL DEVELOPMENT',1,0),('ONLINE COURSES',1,2),('HUMANITIES AND SOCIAL SCIENCE COURSES I',1,11),('HUMANITIES AND SOCIAL SCIENCE COURSES II',1,12);



CREATE TABLE curriculum.subject_reappearence_category (src_id int(10) unsigned NOT NULL AUTO_INCREMENT, src_type varchar(200), status int(1) unsigned DEFAULT NULL, inserted_by int(10) unsigned DEFAULT NULL, inserted_date datetime DEFAULT NULL, updated_by int(10) unsigned DEFAULT NULL, updated_date datetime DEFAULT NULL, PRIMARY KEY (src_id));



INSERT INTO curriculum.subject_reappearence_category(src_type, status) VALUES('compulsory reregistration of same course - supplementary exam not allowed',1),('compulsory reregister of same course -supplementary exam allowed',1),('may register different course - supplementary allowed',1),('may register different course - supplementary not allowed',1),('reregister not allowed - supplementary exam alone allowed',1);



INSERT IGNORE INTO curriculum.regulation_master SELECT NULL,branch_id,reg,NULL,1,1,inserted_date1,1,inserted_date1 FROM (SELECT mb.`branch_id`,acc_year,SUBSTR(`acc_year`,1,4) reg,MOD((mpi.`ay_id`-minay),mpp.`minimum_years`) a,DATE(CONCAT(SUBSTR(`acc_year`,1,4),"-",ROUND(RAND()*(03-01)+01),'-',ROUND(RAND()*(28-01)+01))) + INTERVAL RAND()*(58-01)+01 MINUTE + INTERVAL RAND()*(16-08)+08 HOUR + INTERVAL RAND()*(58-01)+01 SECOND inserted_date, inserted_date1 FROM camps.`master_branch` mb INNER JOIN camps.master_programme_intake mpi ON mpi.branch_id=mb.`branch_id` INNER JOIN (SELECT branch_id,MIN(mpi.`ay_id`) minay FROM camps.master_programme_intake mpi GROUP BY mpi.`branch_id`) mpi1 ON mpi1.branch_id=mb.`branch_id` INNER JOIN camps.master_academic_year may ON may.`ay_id`=mpi.`ay_id` INNER JOIN `camps`.`master_programme_pattern` mpp ON mpp.`prog_pattern_id`=mb.`prog_pattern_id` INNER JOIN (SELECT ay_id,DATE(CONCAT(SUBSTR(`acc_year`,1,4),"-",ROUND(RAND()*(03-01)+01),'-',ROUND(RAND()*(28-01)+01))) + INTERVAL RAND()*(58-01)+01 MINUTE + INTERVAL RAND()*(16-08)+08 HOUR + INTERVAL RAND()*(58-01)+01 SECOND inserted_date1 FROM camps.`master_academic_year` )b ON b.ay_id=may.ay_id HAVING a=0 ORDER BY reg, branch_id)a



/*CREATE TABLE curriculum.subject_master (subject_id int(11) unsigned NOT NULL AUTO_INCREMENT, subject_code varchar(15) NOT NULL, subject_title varchar(200) DEFAULT NULL, L float unsigned DEFAULT 0, T float unsigned DEFAULT 0, P float unsigned DEFAULT 0, C float unsigned DEFAULT 0, min_int_mark double unsigned DEFAULT 0, max_int_mark double unsigned DEFAULT 0, min_ext_mark double unsigned DEFAULT 0, max_ext_mark double unsigned DEFAULT 0, st_id int(10) unsigned DEFAULT NULL, src_id int(10) unsigned DEFAULT NULL, im_validity int(1) DEFAULT 2, min_int_mark_aiv double DEFAULT 0, max_int_mark_aiv double DEFAULT 0, min_ext_mark_aiv double DEFAULT 50, max_ext_mark_aiv double DEFAULT 100, inserted_by int(10) unsigned DEFAULT NULL, inserted_date datetime DEFAULT NULL, updated_by int(10) unsigned DEFAULT NULL, updated_date datetime DEFAULT NULL, PRIMARY KEY (subject_id), UNIQUE KEY (subject_code), FOREIGN KEY (st_id) REFERENCES subject_type (st_id), FOREIGN KEY (src_id) REFERENCES subject_reappearence_category (src_id));/* CREATE TABLE subject_master ( subject_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, dis_code varchar(50) DEFAULT NULL, sub_code varchar(50) DEFAULT NULL, sub_name varchar(100) DEFAULT NULL, lecture int(10) unsigned DEFAULT NULL, tutorial int(10) unsigned DEFAULT NULL, practical int(10) unsigned DEFAULT NULL, credit double unsigned DEFAULT NULL, min_int_mark int(10) unsigned DEFAULT '0', max_int_mark int(10) unsigned DEFAULT '25', min_ext_mark int(10) unsigned DEFAULT '30', max_ext_mark int(10) unsigned DEFAULT '75', min_pass int(11) DEFAULT '40', 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 (subject_id), UNIQUE KEY (sub_code));



CREATE TABLE curriculum.regulation_subject_mapping (rsm_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, regulation_id INT(10) UNSIGNED DEFAULT NULL, subject_id INT(11) UNSIGNED DEFAULT NULL, st_id INT(10) UNSIGNED DEFAULT NULL, prog_period_id INT(10) UNSIGNED DEFAULT NULL,order_no INT(10) UNSIGNED 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 (rsm_id), UNIQUE KEY (regulation_id,subject_id),FOREIGN KEY (regulation_id) REFERENCES curriculum.regulation_master (regulation_id),FOREIGN KEY (subject_id) REFERENCES curriculum.subject_master (subject_id),FOREIGN KEY (st_id) REFERENCES curriculum.subject_type (st_id),FOREIGN KEY (prog_period_id) REFERENCES camps.master_programme_period_det (prog_period_id));



CREATE TABLE curriculum.elective_master (elective_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, elective_name varchar(150) DEFAULT NULL, st_id int(10) unsigned DEFAULT NULL, regulation_id int(10) unsigned DEFAULT NULL, prog_period_id int(10) unsigned DEFAULT NULL, credit double unsigned DEFAULT NULL, order_no int(10) unsigned DEFAULT NULL, status int(10) 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 (elective_id), FOREIGN KEY (regulation_id) REFERENCES curriculum.regulation_master (regulation_id), FOREIGN KEY (st_id) REFERENCES curriculum.subject_type (st_id), FOREIGN KEY (prog_period_id) REFERENCES camps.master_programme_period_det (prog_period_id));




Download Queries