drop table student cascade constraints;
create table student (
  snum  number(9),
  sname varchar2(30),
  major varchar2(30),
  slevel varchar2(20),
  age   number(3),
  primary key (snum));
drop table faculty cascade constraints;
create table faculty (
  fid    number(4),
  fname  varchar2(30),
  deptid number(3),
  primary key (fid));
drop table class cascade constraints;
create table class (
  cname      varchar2(30),
  meets_at   varchar2(10),
  room       varchar2(10),
  faculty_id number(4),
  primary key (cname,faculty_id),
  foreign key (faculty_id) references faculty);
drop table enrolled cascade constraints;
create table enrolled (
  snum  number(9),
  cname varchar2(30),
  primary key (snum,cname),
  foreign key (snum) references student,
  foreign key (cname) references class);

delete from enrolled;
delete from class;
delete from faculty;
delete from student;
insert into student values (1111,'Mohan','CS','JR',32);
insert into student values (1112,'Anand','CS','SR',24);
insert into student values (1113,'Raj','Math','FR',18);
insert into student values (1114,'Dev','Math','JR',22);
insert into student values (1115,'Karim','CS','SR',26);
insert into student values (1116,'Ahmed','History','SR',29);
insert into student values (1117,'Sohail','History','SR',26);
insert into student values (1118,'Ganesh','CS','FR',25);
insert into student values (1119,'Karthik','CS','FR',20);
insert into faculty values (10,'Jones',1);
insert into faculty values (11,'Smith',1);
insert into faculty values (12,'Blake',2);
insert into faculty values (13,'John',2);
insert into class values ('Database','10.00','R128',10);
insert into class values ('C++','9.00','114CS',11);
insert into class values ('Java','11.00','106CS',10);
insert into class values ('Indian History','11.00','100G',12);
insert into class values ('US History','1.00','106G',12);
insert into class values ('US History','2.00','106G',13);
insert into enrolled values (1111,'Database');
insert into enrolled values (1111,'Java');
insert into enrolled values (1112,'Java');
insert into enrolled values (1112,'C++');
insert into enrolled values (1113,'C++');
insert into enrolled values (1114,'Java');
insert into enrolled values (1116,'US History');
insert into enrolled values (1116,'Indian History');
insert into enrolled values (1118,'Java');
insert into enrolled values (1119,'Java');
insert into enrolled values (1116,'Java');
insert into enrolled values (1116,'Database');
