drop table USERS cascade constraints;
drop table SCHOOLS cascade constraints;
drop table COURSES cascade constraints;
drop table ENROLL_IN cascade constraints;
drop table POSTS cascade constraints;


CREATE TABLE users(
Login VARCHAR2(25),
First_name VARCHAR2(25),
Last_name VARCHAR2(25),
Age INTEGER,
Member_since Date,
Primary Key (Login));

INSERT INTO USERS VALUES ('dsmith','David','Smith',35,to_date('2011/02/01:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
INSERT INTO USERS VALUES ('troberts','Tom','Roberts',28,to_date('2011/12/01:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
INSERT INTO USERS VALUES ('rscott','Richard','Scott',21,to_date('2012/01/01:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
INSERT INTO USERS VALUES ('nmoore','Nicole','Moore',22,to_date('2012/01/11:11:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));


CREATE TABLE Schools(
SID VARCHAR2(25),
Sname VARCHAR2(50),
City VARCHAR2(25),
State VARCHAR2(2),
Zip VARCHAR2(5),
Primary Key (SID));

INSERT INTO SCHOOLS VALUES ('GMU','George Mason University','FAIRFAX','VA','22030');
INSERT INTO SCHOOLS VALUES ('GWU','George Washington University','WASHINGTON DC','DC','20008');

CREATE TABLE Courses(
CID VARCHAR2(25),
SID VARCHAR2(25),
Code VARCHAR2(25),
Num VARCHAR2(25),
Sec VARCHAR2(25),
Title VARCHAR2(25),
Status VARCHAR2(25),                          --active,Inactive
Semester VARCHAR2(25),
Primary Key (CID),
Foreign Key (SID) REFERENCES Schools (SID));

INSERT INTO COURSES VALUES ('C001','GMU','CS','450','001','DATABASE','active','SPRING 12');
INSERT INTO COURSES VALUES ('C002','GMU','CS','750','001','DATA MINING','active','SPRING 12');


CREATE TABLE Enroll_in(
Login VARCHAR2(25),
CID VARCHAR2(25),
Role VARCHAR2(25),      --INSTRUCTOR, TA, STUDENT
Notification_Preference VARCHAR2(25),  --real time, daily email, no email
Primary Key (Login, CID),
Foreign Key (Login) REFERENCES users (Login),
Foreign Key (CID) REFERENCES Courses (CID));

INSERT INTO ENROLL_IN VALUES ('dsmith','C001','Instructor','Real Time');
INSERT INTO ENROLL_IN VALUES ('troberts','C001','TA','Real Time');
INSERT INTO ENROLL_IN VALUES ('rscott','C001','Student','Daily Email');
INSERT INTO ENROLL_IN VALUES ('nmoore','C001','Student','No Email');
INSERT INTO ENROLL_IN VALUES ('dsmith','C002','Student','Real Time');


CREATE TABLE Posts(
PID VARCHAR2(25),
Posted_By VARCHAR2(25),
CID VARCHAR2(25),
Content VARCHAR2(25),
Post_Time DATE,
Original_PID VARCHAR2(25),
Primary Key (PID), 
Foreign Key (Posted_By) REFERENCES Users (Login),
Foreign Key (CID) REFERENCES Courses(CID),
Foreign Key (Original_PID) REFERENCES Posts(PID));

INSERT INTO POSTS VALUES ('P01','dsmith','C001','HW1 ANNOUNCEMENT', to_date('2012/02/01:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'),null);
INSERT INTO POSTS VALUES ('P02','dsmith','C001','HW2 ANNOUNCEMENT', to_date('2012/02/23:11:00:00PM', 'yyyy/mm/dd:hh:mi:ssam'),null);
INSERT INTO POSTS VALUES ('P03','troberts','C001','QESTION 1', to_date('2012/02/03:11:00:00PM', 'yyyy/mm/dd:hh:mi:ssam'),'P01');
INSERT INTO POSTS VALUES ('P04','nmoore','C001','Answer 1', to_date('2012/02/03:11:00:00PM', 'yyyy/mm/dd:hh:mi:ssam'),'P03');
INSERT INTO POSTS VALUES ('P05','rscott','C001','QUESTION 2', to_date('2012/02/05:11:00:00PM', 'yyyy/mm/dd:hh:mi:ssam'),'P01');
INSERT INTO POSTS VALUES ('P06','troberts','C001','QUESTION 3', to_date('2012/02/05:11:00:00PM', 'yyyy/mm/dd:hh:mi:ssam'),'P02');
INSERT INTO POSTS VALUES ('P07','rscott','C001','QUESTION 4', to_date('2012/02/28:11:00:00PM', 'yyyy/mm/dd:hh:mi:ssam'),null);
INSERT INTO POSTS VALUES ('P08','dsmith','C001','ANSWER 2', to_date('2012/02/28:11:00:00PM', 'yyyy/mm/dd:hh:mi:ssam'),'P05');
INSERT INTO POSTS VALUES ('P09','rscott','C001','QUESTION 5', to_date('2012/03/01:11:00:00PM', 'yyyy/mm/dd:hh:mi:ssam'),'P08');
INSERT INTO POSTS VALUES ('P10','dsmith','C002','QUESTION 1', to_date('2012/03/03:11:00:00PM', 'yyyy/mm/dd:hh:mi:ssam'),null);
