drop table membership;
drop table honorsinfo;
drop table enrollments;
drop table sections;
drop table courses;
drop table clubs;
drop table students;
drop table faculty;

Create table faculty
   (FID          char(6) constraint faculty_ID_PK Primary Key,
    NAME        varchar2(15),
    PHONE       varchar2(8));

create table students
   (SID         char(6) constraint students_SID_pk primary key,
    last        varchar2(12),
    first       varchar2(12),
    middle      char(1),
    major       varchar2(4),
    sex         varchar2(1),
    phone       varchar2(8),
    birthday    date,
    gpa         number(5,3),
    rank        char(2));

Create table clubs 
   (Name        varchar2(10) constraint clubs_name_PK Primary Key,
    AdvisorID   char(6) constraint clubs_advisorid_FK References faculty(FID),
    YearFormed  number(4));

Create table courses
   (coursenum   varchar2(8) constraint courses_coursenum_pk Primary Key,
    name        varchar2(30),
    credits     number(2));

Create table sections
   (term        varchar2(9),
    indexnum    number(4),
    coursenum   varchar2(8),
    days        varchar2(6),
    starttime   varchar2(7),
    endtime     varchar2(7),
    location    varchar2(12),
    instructor  char(6));

Create table enrollments
   (SID char(6),
    term        varchar2(9),
    indexnum    number(4),
    grade       varchar2(2));

Create table membership 
   (SID char(6) constraint membership_studentid_FK References students(SID),
    CLUBNAME varchar2(10) constraint membership_clubname_FK References clubs(name),
    constraint membership_PK Primary Key(SID,CLUBNAME));

Create table honorsinfo 
   (SID char(6) constraint honorsinfo_studentid_FK References students(SID),
    program varchar2(14),  
    honorsgpa number(3,1), 
    mentor char(6),
    constraint honorsinfo_studentid_PK Primary Key (SID));


insert into students values ('123456','Smith','Robert','M','ACTG','M','555-1234','14-MAR-1980',1.8,'SR');
insert into students values ('123457','Jones','Frank','A','MGNT','M','555-9874','24-FEB-1982',3.2,'FR');
insert into students values ('123458','Davis','Julia','B','INSY','F','555-3975','12-MAR-1980',2.2,'SO');
insert into students values ('123459','Adams','Kelly','D','ACTG','F','555-8798','04-JUN-1985',3.5,'JR');
insert into students values ('223451','Smith','Dave','F','MGNT','M','555-9874','01-SEP-1982',3.8,'FR');
insert into students values ('223452','Franks','Marcus','B','INSY','M','555-1234','30-MAR-1980',4.0,'JR');
insert into students values ('223453','Love','Paul','D','ACTG','M','555-7834','22-APR-1980',1.9,'SO');
insert into students values ('223454','Paulson','Ashley','X','ACTG','F','555-1254','05-FEB-1972',2.4,'JR');
insert into students values ('223455','Crabtree','Doreen','G','MGNT','F','555-8798','25-DEC-1980',2.8,'SO');
insert into students values ('223456','Bogart','Karen','C','INSY','F','555-1432','24-DEC-1981',3.0,'JR');
insert into students values ('223457','Woodson','Fred','K','ACTG','M','555-1334','15-MAY-1982',3.9,'FR');
insert into students values ('223458','Hall','Bobby','J','MGNT','M','555-1444','12-AUG-1981',3.2,'JR');
insert into students values ('223459','BirgSong','Carl','Y','INSY','M','555-1114','11-JAN-1980',2.5,'SO');
insert into students values ('323451','Clinton','Carl','F','ACTG','M','555-1123','09-NOV-1983',2.0,'FR');
insert into students values ('323452','Davis','Jane','P','INSY','F','555-1888','29-OCT-1982',2.2,'JR');
insert into students values ('323453','Holt','Kathy','M','INSY','F','555-1988','30-JUL-1964',3.2,'JR');
insert into students values ('323454','Holton','Jacob','N','ACTG','M','555-1111','12-JAN-1981',3.5,'JR');
insert into students values ('323455','Peters','Justin','N','MGNT','M','555-9890','04-MAR-1980',3.4,'SO');
insert into students values ('323456','Haught','Kara','R','INSY','F','555-6756','05-FEB-1982',3.1,'JR');
insert into students values ('323457','Vaught','Jason','A','ACTG','M','555-3999','06-MAR-1983',2.2,'FR');
insert into students values ('323458','Osborne','Mark','B','INSY','M','555-5432','11-DEC-1977',2.4,'FR');
insert into students values ('323459','Smith','Dana','C','MGNT','F','555-7348','11-NOV-1983',2.8,'JR');
insert into students values ('423451','Smith','Doris','K','MGNT','F','555-9800','11-OCT-1981',4.0,'JR');
insert into students values ('423452','Redmond','Jessica','D','INSY','F','555-4007','23-DEC-1982',3.2,'FR');
insert into students values ('423453','Sides','Susan','F','ACTG','F','555-4444','24-JUL-1980',2.7,'SR');
insert into students values ('423454','Fisher','Joe','G','MGNT','M','555-1234','29-JUN-1980',3.6,'SR');
insert into students values ('423455','Bath','Nathan','B','INSY','M','555-3987','20-JAN-1982',3.3,'FR');
insert into students values ('423456','Loney','Larry','B','ACTG','M','555-7634','10-APR-1981',2.3,'SO');

Insert into faculty values ('987651','Dr. Jones','555-8734');
Insert into faculty values ('987652','Dr. Smith','555-9786');
Insert into faculty values ('987653','Dr. Davis','555-1265');
Insert into faculty values ('987654','Dr. Hawking','444-3423');
Insert into faculty values ('987655','Dr. Ravell','555-7846');

Insert into clubs values ('Chess','987655',1954);
Insert into clubs values ('Book','987652',1933);
Insert into clubs values ('Scuba','987651',1965);
Insert into clubs values ('Acting','987655',1977);
Insert into clubs values ('Photo','987651',1962);
Insert into clubs values ('Ski','987653',1988);

Insert into courses values ('ACTG 311','Cost Accounting',3);
Insert into courses values ('ACTG 313','Intermediate Accounting I',3);
Insert into courses values ('ACTG 314','Intermediate Accounting II',3);
Insert into courses values ('ACTG 316','Accounting Information Systems',3);
Insert into courses values ('MGNT 321','Principles of Management',3);
Insert into courses values ('MGNT 322','Organizational Behavior',3);
Insert into courses values ('INSY 181','Bus. App. of MicroComputers',3);

Insert into sections values ('Spring 02',1001,'ACTG 311','M W F','08:00am','08:50am','Whitt 222', '987651');
Insert into sections values ('Spring 02',1002,'ACTG 311','M W F','09:00am','09:50am','Whitt 222', '987651');
Insert into sections values ('Spring 02',1003,'ACTG 313','M W F','11:00am','11:50am','Whitt 208', '987652');
Insert into sections values ('Spring 02',1004,'ACTG 316','M W F','12:00pm','12:50pm','Whitt 205', '987655');
Insert into sections values ('Spring 02',1005,'MGNT 321','T H','09:30am','10:45am','Whitt 104', '987653');
Insert into sections values ('Spring 02',1006,'MGNT 321','T H','11:00am','12:15pm','Whitt 008', '987653');
Insert into sections values ('Spring 02',1007,'MGNT 322','M W F','08:00am','08:50am','Whitt 212', '987654');
Insert into sections values ('Spring 02',1008,'MGNT 322','T H','12:30pm','01:45pm','Whitt 120', '987654');
Insert into sections values ('Spring 02',1009,'MGNT 322','T H','02:00pm','03:15pm','Whitt 120', '987654');
Insert into sections values ('Spring 02',1010,'INSY 181','T H','02:00pm','03:15pm','Whitt 124', '987651');
Insert into sections values ('Fall 02',1001,'ACTG 311','M W F','09:00am','09:50am','Whitt 222', '987651');
Insert into sections values ('Fall 02',1002,'ACTG 313','M W F','11:00am','11:50am','Whitt 208', '987652');
Insert into sections values ('Fall 02',1003,'ACTG 313','M W F','12:00pm','12:50pm','Whitt 205', '987652');
Insert into sections values ('Fall 02',1006,'ACTG 314','M W F','03:00pm','03:50pm','Whitt 205', '987652');
Insert into sections values ('Fall 02',1004,'MGNT 321','T H','09:30am','10:45am','Whitt 008', '987653');
Insert into sections values ('Fall 02',1005,'MGNT 321','T H','11:00am','12:15pm','Whitt 104', '987653');
Insert into sections values ('Spring 03',1001,'ACTG 311','M W F','08:00am','08:50am','Whitt 222', '987651');
Insert into sections values ('Spring 03',1002,'ACTG 311','M W F','09:00am','09:50am','Whitt 222', '987651');
Insert into sections values ('Spring 03',1003,'ACTG 314','M W F','11:00am','11:50am','Whitt 208', '987652');
Insert into sections values ('Spring 03',1004,'ACTG 316','M W F','12:00pm','12:50pm','Whitt 205', '987655');
Insert into sections values ('Spring 03',1005,'MGNT 321','T H','09:30am','10:45am','Whitt 104', '987653');
Insert into sections values ('Spring 03',1006,'MGNT 321','T H','11:00am','12:15pm','Whitt 008', '987653');
Insert into sections values ('Spring 03',1007,'MGNT 322','M W F','08:00am','08:50am','Whitt 212', '987654');
Insert into sections values ('Spring 03',1008,'MGNT 322','T H','12:30pm','01:45pm','Whitt 120', '987654');
Insert into sections values ('Spring 03',1009,'INSY 181','T H','12:30pm','01:45pm','Whitt 124', '987655');

Insert into enrollments values ('423456','Spring 02',1003,'A');
Insert into enrollments values ('423456','Spring 02',1010,'B');
Insert into enrollments values ('423456','Fall 02',1006,'A');
Insert into enrollments values ('423456','Fall 02',1005,'B');
Insert into enrollments values ('423455','Spring 02',1003,'C');
Insert into enrollments values ('423455','Spring 02',1003,'C');
Insert into enrollments values ('423455','Fall 02',1006,'A');
Insert into enrollments values ('423455','Fall 02',1005,'B');
Insert into enrollments values ('423454','Spring 02',1003,'A');
Insert into enrollments values ('423454','Spring 02',1010,'B');
Insert into enrollments values ('423454','Fall 02',1006,'A');
Insert into enrollments values ('423454','Fall 02',1005,'C');
Insert into enrollments values ('423453','Spring 02',1003,'A');
Insert into enrollments values ('423453','Spring 02',1005,'B');
Insert into enrollments values ('423453','Fall 02',1006,'B');
Insert into enrollments values ('423453','Fall 02',1001,'F');
Insert into enrollments values ('123456','Fall 02',1002,'A');
Insert into enrollments values ('123456','Fall 02',1004,'B');
Insert into enrollments values ('123456','Fall 02',1001,'A');
Insert into enrollments values ('123456','Spring 03',1007,null);
Insert into enrollments values ('123456','Spring 03',1003,null);
Insert into enrollments values ('123456','Spring 03',1004,null);
Insert into enrollments values ('123457','Spring 03',1004,null);
Insert into enrollments values ('123457','Spring 03',1001,null);
Insert into enrollments values ('123457','Spring 03',1006,null);
Insert into enrollments values ('123458','Fall 02',1002,'C');
Insert into enrollments values ('123458','Fall 02',1001,'B');
Insert into enrollments values ('123458','Fall 02',1004,'F');
Insert into enrollments values ('123458','Spring 03',1006,null);
Insert into enrollments values ('123458','Spring 03',1004,null);
Insert into enrollments values ('123458','Spring 03',1003,null);
Insert into enrollments values ('123459','Fall 02',1003,'C');
Insert into enrollments values ('123459','Fall 02',1004,'C');
Insert into enrollments values ('123459','Spring 03',1003,null);
Insert into enrollments values ('123459','Spring 03',1007,null);
Insert into enrollments values ('123459','Spring 03',1002,null);
Insert into enrollments values ('223451','Fall 02',1005,'A');
Insert into enrollments values ('223451','Fall 02',1003,'A');
Insert into enrollments values ('223451','Spring 03',1004,null);
Insert into enrollments values ('223451','Spring 03',1002,null);

Insert into membership values (323451,'Chess');
Insert into membership values (323451,'Book');
Insert into membership values (323453,'Photo');
Insert into membership values (323455,'Chess');
Insert into membership values (323455,'Scuba');
Insert into membership values (323455,'Photo');
Insert into membership values (323458,'Scuba');
Insert into membership values (323458,'Photo');
Insert into membership values (123456,'Chess');
Insert into membership values (123456,'Book');
Insert into membership values (123458,'Photo');
Insert into membership values (123459,'Chess');
Insert into membership values (123459,'Scuba');
Insert into membership values (123459,'Photo');
Insert into membership values (223451,'Scuba');
Insert into membership values (223451,'Photo');
Insert into membership values (423452,'Chess');
Insert into membership values (423452,'Book');
Insert into membership values (423453,'Photo');
Insert into membership values (423454,'Chess');
Insert into membership values (423454,'Scuba');
Insert into membership values (423454,'Photo');
Insert into membership values (423456,'Scuba');
Insert into membership values (423456,'Photo');

Insert into honorsinfo values (223452,'Database',4.0,'987655');
Insert into honorsinfo values (223457,'Taxation',4.0,'987651');

Commit;