

drop table transcript;
drop table cross_listings;
drop table requires;
drop table meeting_times ;
drop table classes;
drop table courses ;
drop table classrooms ;
drop table students;
drop table faculty;
drop table majors;
drop table departments;


create table departments(
    id       int constraint department_pk primary key
    , name   varchar(255) NOT NULL
    , office varchar(40)  
    , phone  varchar(12)
    ) ;

create table majors(
    abrv     varchar(4) constraint majors_pk primary key
    , name   varchar(255) NOT NULL
    , dept_id int
    ,  constraint major_fk foreign key (dept_id) references departments(id)
    ); 

create table students (
    id       int constraint student_pk primary key
    , name   varchar(255) NOT NULL
    , email  varchar(255) NOT NULL
    , address varchar(255)
    , year   numeric(4)   NOT NULL
    , gpa    numeric(4,2)
    , major  varchar(4)   NOT NULL
    , constraint students_fk foreign key (major) references majors(abrv)
    ) ;

create table faculty (
    id       int constraint faculty_pk primary key
    , name   varchar(255) NOT NULL
    , email  varchar(255) NOT NULL
    , office varchar(40)
    , dept_id int NOT NULL
    , constraint faculty_fk foreign key (dept_id) references departments(id)
    ) ;


create table courses(
    id         int constraint courses_pk primary key
    , major    varchar(4) NOT NULL
    , crscode  varchar(4) NOT NULL
    , crsname  varchar(255) NOT NULL
    , credits  int    NOT NULL  
    , whenoffered  varchar(255) --'Spring only', 'Fall and Spring', etc.
    , iscatalog  boolean
    , constraint courses_fk foreign key (major) references majors(abrv)
    ) ;

create table cross_listings(
    cross_id   int constraint cross_listings_pk primary key
    , major    varchar(4) NOT NULL
    , crscode  varchar(4) NOT NULL
    , main_id  int    NOT NULL
    , constraint cross_listings_fk foreign key (main_id) references courses(id)
    , constraint cross_listings_fk2 foreign key (major) references majors(abrv)
    ) ;
    

create table requires(
    course_id        int
    , prereq_id      int
    , isenforced     boolean
    , enforcedsince  date
    , constraint requires_pk primary key (course_id, prereq_id)
    , constraint requires_fk foreign key (course_id) references courses(id)
    , constraint requires_fk2 foreign key (prereq_id) references courses(id)
    ) ;


create table classrooms(
    id     int   constraint classrooms_pk primary key
    , building    varchar(40)  NOT NULL
    , room        varchar(4)   NOT NULL
    , numseats    int 
    , notes       varchar(100) --super hot, broken seats, etc.
    ) ;

create table classes(
    course_id    int
    , semester   varchar(10)
    , year       numeric(4)
    , section    numeric(2)
    , classroom_id   int
    , instructor_id  int
    , constraint classes_pk primary key (course_id, semester, year, section)
    , constraint classes_fk foreign key (course_id) references courses(id)
    , constraint classes_fk2 foreign key (classroom_id) references classrooms(id)
    , constraint classes_fk3 foreign key (instructor_id) references faculty(id)
    );

create table meeting_times(
    id int  constraint meeting_times_pk primary key
    , course_id    int
    , semester     varchar(10)
    , year         numeric(4)
    , section      numeric(2)
    , meetingtime  time
    , days         char(4)  --'M R', 'T F', 'W'
    , listingorder int    
    , constraint meeting_times_fk foreign key (course_id, semester, year, section)
      references classes(course_id, semester, year, section)
    ) ;

create table transcript(
    student_id   int
    , course_id  int
    , semester   varchar(10)
    , year       numeric(4)
    , section    numeric(2)
    , grade      varchar(2)
    , gradechanged  date
    , constraint transcript_pk primary key (student_id, course_id, semester, year, section)
    , constraint transcript_fk foreign key (student_id) references students(id)
    , constraint transcript_fks foreign key (course_id, semester, year, section)
      references classes(course_id, semester, year, section)
    ) ;


--- Tuples

insert into departments values(1, 'Physical Magic', 'Main 101', '555-1234');
insert into departments values(2, 'Psychic Magic', 'Main 202', '555-3421');
insert into departments values(3, 'Illusions', 'Main 302', '444-1242');
insert into departments values(4, 'Healing', 'Main 302', '213-1245');
insert into departments values(5, 'Knowledge', 'Main 204', '942-1245');
insert into departments values(6, 'Natural Magic', 'Main 101', '215-2416');

insert into majors values('PHYS', 'Physical Magic', 1);
insert into majors values('PHOS', 'Phosphormancy', 1);
insert into majors values('ROSO', 'Repair of small objects', 1);
insert into majors values('TRAV', 'Traveler', 1);
insert into majors values('PSYC', 'Psychic Magic', 2) ;
insert into majors values('ILLU', 'Illusions', 3);
insert into majors values('HEAL', 'Healing', 4);
insert into majors values('KNOW', 'Knowledge', 5);
insert into majors values('NATU', 'Natural Magic', 6);

insert into students values(111, 'Quentin Coldwater', 'qc@brakebills.edu', 'The Maze 11', 2020, null, 'ROSO') ;
insert into students values(212, 'William Adiyodi', 'wa@brakebills.edu', 'The Sea', 2019, 2.5, 'TRAV'); 
insert into students values(215, 'Janet Pluchinsky', 'jp@brakebills.edu', 'Main 404', 2019, 3.6, 'PHYS');
insert into students values(145, 'Alice Quinn', 'aq@brakebills.edu', 'The Maze 21', 2020, null, 'PHOS') ; 
insert into students values(231, 'Eliot Waugh', 'ew@@brakebills.edu', 'The Maze 23', 2018, 3.6, 'PHYS') ;

insert into faculty values(1, 'Henry Fogg', 'hf@brakebills.edu', 'Clock Tower 123', 5) ;
insert into faculty values(2, 'Pearl Sunderland', 'ps@brakebills.edu', 'Clock Tower 124', 3) ;
insert into faculty values(3, 'Professor March', 'pm@brakebills.edu', 'Clock Tower 125', 1) ;
insert into faculty values(4, 'Professor Mayakovsky', '@brakebills.edu', 'Brakebills South 1', 6) ;
insert into faculty values(5, 'Melanie Van Der Weghe', '@brakebills.edu', 'Clock Tower 126', 5) ;

insert into courses values(1, 'PHYS', 101, 'Spellcasting', 4, 'Spring', True) ;
insert into courses values(2, 'ILLU', 101, 'Mazes', 4, 'Fall', True) ;
insert into courses values(3, 'PHYS', 204, 'Minor Mendings', 4, 'Spring', True) ;
insert into courses values(4, 'KNOW', 205, 'Practical Applications', 4, 'Spring and Fall', False) ;
insert into courses values(5, 'PHYS', 401, 'Advanced Spellcasting', 4, 'Spring', False) ;
insert into courses values(6, 'TRAV', 601, 'Alternate Dimensions', 3, 'Fall', False) ;


insert into cross_listings values(1, 'PSYC', 101, 2) ;
insert into cross_listings values(2, 'PHYS', 601, 6) ;

insert into requires values(5, 1, True, date '2011-01-01');
insert into requires values(4, 1, True, date '2015-08-01'); 
insert into requires values(6, 4, False, null); 
insert into requires values(6, 5, False, null); 

insert into classrooms values(1, 'Main House', 12, 40, 'monster danger');
insert into classrooms values(2, 'Main House', 15, 20, null);
insert into classrooms values(3, 'Main House', 21, 10, 'small');
insert into classrooms values(4, 'Clock Tower', 11, 5, '');
insert into classrooms values(5, 'Clock Tower', 12, 8, 'invisible walls') ;
insert into classrooms values(6, 'Brakebills South', 1, 21, 'cold');

insert into classes values(1, 'Spring', 2014, 1, 1, 3) ;
insert into classes values(1, 'Spring', 2015, 1, 1, 3) ;
insert into classes values(1, 'Spring', 2016, 1, 1, 1) ;
insert into classes values(2, 'Fall', 2014, 1, 2, 2) ;
insert into classes values(2, 'Fall', 2015, 1, 2, 5) ;
insert into classes values(3, 'Spring', 2015, 1, 4, 3) ;
insert into classes values(4, 'Spring', 2015, 1, 5, 2) ;
insert into classes values(5, 'Fall', 2015, 1, 6, 4) ;
insert into classes values(5, 'Fall', 2015, 2, 6, 4) ;
insert into classes values(6, 'Spring', 2015, 1, 6, 4) ;
insert into classes values(6, 'Spring', 2016, 1, 6, 4) ;

insert into meeting_times values(1, 1, 'Spring', 2014, 1, time '14:00:00', 'MR', 1 ) ;
insert into meeting_times values(2, 1, 'Spring', 2014, 1,  time '10:00:00', 'W', 2 ) ;
insert into meeting_times values(3, 1, 'Spring', 2015, 1,  time '14:00:00', 'MR', 1) ;
insert into meeting_times values(4, 1, 'Spring', 2015, 1,  time '10:00:00', 'W', 2) ;
insert into meeting_times values(5, 1, 'Spring', 2016, 1,  time '16:00:00', 'MR', 1) ;
insert into meeting_times values(6, 1, 'Spring', 2016, 1,  time '18:00:00', 'W', 2) ;
insert into meeting_times values(7, 2, 'Fall', 2014, 1, time '10:00:00', 'F') ;
insert into meeting_times values(8, 2, 'Fall', 2015, 1, time '12:00:00', 'W') ;
insert into meeting_times values(9, 3, 'Spring', 2015, 1, time '12:00:00', 'T') ;
insert into meeting_times values(10, 3, 'Spring', 2015, 1, time '15:00:00', 'MWR') ;
insert into meeting_times values(11, 4, 'Spring', 2015, 1, time '14:00:00', 'WF') ;
insert into meeting_times values(12, 5, 'Fall', 2015, 1, time '10:00:00', null, null) ;
insert into meeting_times values(13, 5, 'Fall', 2015, 2, time '20:00:00', 'MWRF' ) ;


insert into transcript values(111, 1, 'Spring', 2016, 1, null, null);
insert into transcript values(145, 1, 'Spring', 2016, 1, null, null);
insert into transcript values(212, 3, 'Spring', 2015, 1, 'B-', null);
insert into transcript values(212, 5, 'Fall', 2015, 1, 'B', null);
insert into transcript values(212, 6, 'Spring', 2015, 1, 'I', null);
insert into transcript values(212, 6, 'Spring', 2016, 1, null, null);
insert into transcript values(215, 1, 'Spring', 2014, 1, 'A-', null);
insert into transcript values(215, 2, 'Fall', 2014, 1, 'A', null);
insert into transcript values(215, 5, 'Fall', 2015, 1, 'A-', null);
insert into transcript values(231, 1, 'Spring', 2014, 1, 'A', null);
insert into transcript values(231, 2, 'Fall', 2014, 1, 'A', null);
insert into transcript values(231, 3, 'Spring', 2015, 1, 'A-', null);
insert into transcript values(231, 4, 'Spring', 2015, 1, 'A-', null);
insert into transcript values(231, 5, 'Fall', 2015, 2, 'B+', null);
insert into transcript values(231, 6, 'Spring', 2016, 1, null, null);

insert into students values(431, 'Kady Orloff-Diaz', 'ko@alumni.brakebills.edu', null, 2020, null, 'PHYS');
insert into faculty values(6, 'Jennifer London', 'jl@brakebills.edu', 'Dungeons',  5) ;
insert into courses values(7, 'PHYS', 501, 'Transmogrification', 6, 'Fall', False);
insert into classes values(6, 'Spring', 2016, 2,6,4) ;

insert into transcript values(231, 1, 'Spring', 2015, 1, 'B', NULL);
