CREATE TABLE Term(
Term VARCHAR(20) NOT NULL,
Term_StartDate DATE,
Term_EndDate DATE,
CONSTRAINT Term_pk PRIMARY KEY (Term));


INSERT INTO Term(Term,Term_StartDate,Term_EndDate)VALUES('Fall 2020', '2019-09-05', '2019-12-11');
INSERT INTO Term(Term,Term_StartDate,Term_EndDate) VALUES ('Spring 2020','2020-01-15', '2020-05-06');

CREATE TABLE Course(
Course_Code VARCHAR(20) NOT NULL,
Course_Title VARCHAR(50),
CONSTRAINT Course_pk PRIMARY KEY (Course_Code));

INSERT INTO Course(Course_Code,Course_Title) values ('SLIS LIS485','Introduction to Programming');
INSERT INTO Course(Course_Code,Course_Title) VALUES ('SLIS LIS485','Tech for Information Professionals');
INSERT INTO Course(Course_Code,Course_Title) VALUES ('SLIS LIS458','Database Management Systems');
INSERT INTO Course(Course_Code,Course_Title) VALUES ('SLIS LIS415','Information Organization');
INSERT INTO Course(Course_Code,Course_Title) VALUES ('SLIS LIS407','Information Sources and Services');

CREATE TABLE Classroom(
ClassroomCode INTEGER NOT NULL,
Location VARCHAR (40),
Room INTEGER,
Campus VARCHAR (40),
CONSTRAINT Classroom_pk PRIMARY KEY (ClassroomCode));

INSERT INTO Classroom (ClassroomCode, Location, Room, Campus) VALUES (1,'Palace Road',210,'Main Campus');
INSERT INTO Classroom (ClassroomCode, Location, Room, Campus) VALUES (2,'Online',000,'Online'); 
INSERT INTO Classroom (ClassroomCode, Location, Room, Campus) VALUES (3,'Main Campus Building', 101,'Mount Holyoke');



CREATE TABLE Course_List(
ID Integer NOT NULL,
Course_Code VARCHAR(20),
Term VARCHAR (20),
Instructor VARCHAR (30),
ClassroomCode INTEGER,
CONSTRAINT Course_List_pk PRIMARY KEY (ID),
CONSTRAINT Course_fk FOREIGN KEY (Course_code) REFERENCES Course(Course_code),
CONSTRAINT Term_fk FOREIGN KEY (Term) REFERENCES Term(Term),
CONSTRAINT Classroom_fk FOREIGN KEY (ClassroomCode) REFERENCES Classroom(ClassroomCode));




INSERT INTO Course_List(ID,Course_Code,Term, Instructor, ClassroomCode) VALUES (1,'SLIS LIS485', 'Spring 2020','Dumas',1);
INSERT INTO Course_List(ID,Course_Code,Term, Instructor, ClassroomCode) VALUES (2,'SLIS LIS488', 'Spring 2020','Pollock',1);
INSERT INTO Course_List(ID,Course_Code,Term, Instructor, ClassroomCode) VALUES (3,'SLIS LIS458', 'Spring 2020','Tis',2);
INSERT INTO Course_List(ID,Course_Code,Term, Instructor, ClassroomCode) VALUES (4,'SLIS LIS407', 'Fall 2020','Davis',3);
INSERT INTO Course_List(ID,Course_Code,Term, Instructor, ClassroomCode) VALUES (5,'SLIS LIS458', 'Fall 2020','Williams',2);







