proj2.sql

drop table logs;
drop table prerequisites;
drop table enrollments;
drop table classes;
drop table courses;
drop table students;

create table students (sid char(4) primary key check (sid like 'B%'),
firstname varchar2(15) not null, lastname varchar2(15) not null, status varchar2(10)
check (status in ('freshman', 'sophomore', 'junior', 'senior', 'graduate')),
gpa number(3,2) check (gpa between 0 and 4.0), email varchar2(20) unique);

create table courses (dept_code varchar2(4) not null, course# number(3) not null
check (course# between 100 and 799), title varchar2(20) not null,
primary key (dept_code, course#));

create table prerequisites (dept_code varchar2(4) not null,
course# number(3) not null, pre_dept_code varchar2(4) not null,
pre_course# number(3) not null,
primary key (dept_code, course#, pre_dept_code, pre_course#),
foreign key (dept_code, course#) references courses on delete cascade,
foreign key (pre_dept_code, pre_course#) references courses on delete cascade);

create table classes (classid char(5) primary key check (classid like 'c%'),
dept_code varchar2(4) not null, course# number(3) not null,
sect# number(2), year number(4), semester varchar2(6)
check (semester in ('Spring', 'Fall', 'Summer')), limit number(3),
class_size number(3), foreign key (dept_code, course#) references courses
on delete cascade, unique(dept_code, course#, sect#, year, semester),
check (class_size <= limit));

create table enrollments (sid char(4) references students, classid char(5) references classes,
lgrade char check (lgrade in ('A', 'B', 'C', 'D', 'F', 'I', null)), primary key (sid, classid));

create table logs (logid number(4) primary key, who varchar2(10) not null, time date not null,
table_name varchar2(20) not null, operation varchar2(6) not null, key_value varchar2(15));