create table Mainuser ( userid int not null primary key,
email varchar(30),
uname varchar(20),
pwd varchar(20));

create table User_Student( userid int primary key references Mainuser(userid),
userid_status varchar(1),
year_of_join int, year_of_join_status varchar(2),
semester int, semester_status varchar(2),
degree_status varchar(20), degree_status_status varchar(2),
bonus_credit FLOAT, bonus_status varchar(2),
degree_type varchar(10), degree_type_status varchar(2),
constraint student_CK check ( userid_status in ('Y','y','N','n')),
constraint student_CK1 check ( year_of_join_status in ('Y','y', 'N','n')),
constraint student_CK2 check (semester_status in ('Y','y','N','n')),
constraint student_CK3 check (degree_status_status in ('Y','y','N','n')),
constraint student_CK4 check (bonus_status in ('Y','y','N','n')),
constraint student_CK5 check (degree_type_status in ('Y','y','N','n'))
);

commit;

create table User_Faculty( userid int primary key references Mainuser(userid),
fname varchar(20),
year_of_join int,
current_position varchar(30),
experience varchar(50)
);

commit;

create table course( courseid int primary key,
course_name varchar(50)
);

create table User_TA (userid int primary key references Mainuser(userid),
courseid int,
office_hours int,
constraint TA_FK foreign key(courseid) references course(courseid)
);

create table Moderator (Moderator_id int not null primary key,
description varchar(20)
);

create table Site_administrator( admin_id int primary key references User_Faculty(userid));

create table Course_Interest_group( interest_gid int,
gname varchar(20),
courseid int,
facultyid int,
past_gpa float,
constraint IG_PK primary key(interest_gid, courseid, facultyid),
constraint IG_FK1 foreign key(courseid) references course(courseid),
constraint IG_FK2 foreign key(facultyid) references User_Faculty(userid)
);

create table interst_club( club_id int primary key,
description varchar(20),
uname varchar(20),
pwd varchar(10)
);

create table group_disforum ( df_id int,
ig_id int,
constraint gdf_FK foreign key(ig_id) references Course_Interest_group(interest_gid),
constraint gdf_PK primary key(df_id, ig_id),
comments varchar(150)
);

create table club_disforum( cdf_id int primary key,
cl_id int,
comments varchar(150),
constraint club_FK foreign key(cl_id) references interst_club(club_id)
);

create table course_disforum( course_df_id int,
courseid int,
comments varchar(150),
constraint Course_FK foreign key (courseid) references course(courseid)
);

create table teaches( userid int,
courseid int,
constraint teaches_FK foreign key(courseid) references course(courseid),
constraint teaches_FK1 foreign key(userid) references Mainuser(userid),
constraint teaches_PK primary key(userid, courseid)
);

create table enrolls ( student_id int, course_id int,
constraint enrolls_FK foreign key(student_id) references User_student(userid),
constraint enrolls_FK2 foreign key(course_id) references course(courseid),
constraint enrolls_PK primary key(student_id, course_id)
);

commit;
create table joins_IG ( userid int,
ig_id int,
constraint joins_FK foreign key( userid) references Mainuser(userid),
constraint joins_FK2 foreign key(ig_id) references Course_Interest_group(interest_gid),
constraint joins_PK primary key(userid, ig_id)
);

create table joins_club( userid int,
club_id int,
constraint joins_club_FK foreign key(userid) references Mainuser(userid),
constraint joins_club_FK2 foreign key(club_id) references  interst_club(club_id),
constraint joins_PK1 primary key(userid, club_id)
);

create table assigns( admin_id int, moderator_id int,
constraint assigns_FK foreign key(admin_id) references Site_administrator( admin_id),
constraint assigns_FK2 foreign key(moderator_id) references Moderator(Moderator_id)
);

commit;

create table has_club (course_id int, club_id int,
constraint club_FK1 foreign key(course_id) references course(courseid),
constraint club_FK2 foreign key(club_id) references interst_club( club_id),
constraint club_PK primary key(course_id, club_id)
);

commit;

create table has_ig( courseid, ig_id,
constraint ig_FK3 foreign key(courseid) references course(courseid),
constraint ig_FK4 foreign key (ig_id) references Course_Interest_group( interest_gid),
constraint ig_PK2 primary key(courseid, ig_id)
);

create table has_grp_mod ( moderator_id int, ig_id int,
constraint grp_FK foreign key( moderator_id) references Moderator(Moderator_id),
constraint grp_FK1 foreign key(ig_id) references Course_Interest_group( interest_gid),
constraint grp_PK primary key( moderator_id, ig_id)
);

create table has_club_mod (moderator_id, club_id,
constraint club_FK3 foreign key(moderator_id) references Moderator(Moderator_id),
constraint club_FK4 foreign key(club_id) references interst_club( club_id),
constraint club_PK1 primary key(moderator_id, club_id)
);