drop database if exists icrt2;
create database icrt2;
use icrt2;

------------------------------------------

create table t_course(

  course_id int(10) auto_increment,
  dept_id int(10),
  category_id int(2),
  course_name blob not null,
  course_duration float(1)not null,
  course_details blob not null,
  isdelete int(1) not null,

  primary key(course_id)

  
)ENGINE=INNODB;

create table t_sem(

  sem_id int(10) auto_increment,
  sem_name varchar(100) not null,
  
  primary key(sem_id)

)ENGINE=INNODB;


create table t_course_sem(

  course_sem_id int(10) auto_increment,
  course_id int(10),
  sem_id int(10),

  primary key(course_sem_id),
  foreign key(course_id) references t_course(course_id),
  foreign key(sem_id) references t_sem(sem_id)
  
)ENGINE=INNODB;


insert into t_sem values(1,'first');
insert into t_sem values(2,'second');
insert into t_sem values(3,'third');
insert into t_sem values(4,'fourth');
insert into t_sem values(5,'fifth');
insert into t_sem values(6,'sixth');
insert into t_sem values(7,'seventh');
insert into t_sem values(8,'eight');

insert into t_course values(1,1,3,'B.Tech',4,'CS',0);
insert into t_course values(2,1,2,'M.Tech',2,'CS',0);

insert into t_course_sem values(1,1,1);
insert into t_course_sem values(2,1,2);
insert into t_course_sem values(3,1,3);
insert into t_course_sem values(4,1,4);
insert into t_course_sem values(5,2,1);
insert into t_course_sem values(6,2,2);
insert into t_course_sem values(7,2,3);
insert into t_course_sem values(8,2,4);