drop table major;

create table major(

id_m varchar2(5) NOT NULL,

description_m varchar2(50),

constraint major_pk primary key (id_m));

 

drop table student;

create table student(

id_stu varchar2(9) NOT NULL,

first_name varchar2(15) NOT NULL,

middle_name varchar2(15),

last_name varchar2(20) NOT NULL,

sec_last_name varchar2(20),

mail_addr varchar2(25) NOT NULL,

res_addr varchar2(25) NOT NULL,

major_id varchar2(15),

phone varchar2(15),

constraint student_pk primary key (id_stu));

 

drop table slevel;

create table slevel(

id_l varchar2(5),

description_l varchar2(50),

constraint slevel_pk primary key (id_l));

 

drop table semester;

create table semester(

id_s varchar2(5),

description_s varchar2(20),

constraint semester_pk primary key (id_s));

 

drop table professor;

create table professor(

id_f varchar2(9) NOT NULL,

first_name varchar2(15) NOT NULL,

middle_name varchar2(15),

last_name varchar2(20) NOT NULL,

sec_last_name varchar2(20),

mail_addr varchar2(25) NOT NULL,

res_addr varchar2(25) NOT NULL,

phone varchar2(15),

degree varchar2(5),

major varchar2(15),

constraint professor_pk primary key (id_f));

 

drop table course;

create table course(

crn varchar2(9) NOT NULL,

name varchar2(30) NOT NULL,

start_time varchar2(20) NOT NULL,

end_time varchar2(20),

days varchar2(25) NOT NULL,

level_id varchar2(15),

fac_id varchar2(9),

sem_id varchar2(5),

room varchar2(15),

constraint course_pk primary key (crn),

constraint foreign_key_course_professor foreign key (fac_id) references professor,

constraint foreign_key_course_slevel foreign key (level_id) references slevel,

constraint foreign_key_course_semester foreign key (sem_id) references semester);

 

drop table enrollment;

create table enrollment(

course_id varchar2(9) NOT NULL,

stu_id varchar2(9) NOT NULL,

e_date date,

constraint foreign_key_enrollment_course foreign key (course_id)

references course,

constraint foreign_key_enrollment_student foreign key (stu_id)

references student);