drop table property cascade constraints;
create table property (
  propertyName varchar2(32),
  propertyQuestion  varchar2(256),
  displayorder number(2),
  primary key (propertyName)
);
insert into property values 
  ('publicOrPrivate','Do you like to attend a public or private university?',1);
insert into property values 
  ('region','What region of the country do you prefer?',2);
insert into property values 
  ('state','What state of the country do you prefer?',3);
insert into property values 
  ('focus','Are you interested in schools with a specific focus?',4);
insert into property values 
  ('setting','In what kind of setting are you interested?',5);
insert into property values 
  ('collegeType','What type of college are you interested in?',6);
insert into property values 
  ('collegeSize','What size college are you interested in?',7);
insert into property values 
  ('SATAvg','What SAT Average school are you looking for?',8);
insert into property values 
  ('collegeRanking','What ranking of schools are you searching for?',9);
insert into property values 
  ('sportsTradition','Is collegiate sports tradition of interest to you?',10);
insert into property values 
  ('studentFacultyRatio','How do you feel about student faculty ratios?',11);
insert into property values 
  ('cost','What tuitions are you comfortable with?',12);
insert into property values 
  ('collegeAge','Is college age of any concern to you?',13);
--
--
drop table propertyDetail cascade constraints;
create table propertyDetail (
  propertyName varchar2(32),
  allowedValue varchar2(64),
  allowedValueCode number(3),
  primary key (propertyName,allowedValue),
  foreign key (propertyName) references property
);
insert into propertyDetail values ('publicOrPrivate','Public',1);
insert into propertyDetail values ('publicOrPrivate','Private',2);
insert into propertyDetail values ('region','Southeast',1);
insert into propertyDetail values ('region','Midwest',2);
insert into propertyDetail values ('region','East',3);
insert into propertyDetail values ('region','West',4);
insert into propertyDetail values ('state','Alabama',1);
insert into propertyDetail values ('state','Arizona',2);
insert into propertyDetail values ('state','California',3);
insert into propertyDetail values ('state','Florida',4);
insert into propertyDetail values ('state','Georgia',5);
insert into propertyDetail values ('state','Illinois',6);
insert into propertyDetail values ('state','Indiana',7);
insert into propertyDetail values ('state','Iowa',8);
insert into propertyDetail values ('state','Kansas',9);
insert into propertyDetail values ('state','Kentucky',10);
insert into propertyDetail values ('state','Louisiana',11);
insert into propertyDetail values ('state','Maryland',12);
insert into propertyDetail values ('state','Michigan',13);
insert into propertyDetail values ('state','Minnesota',14);
insert into propertyDetail values ('state','Missouri',15);
insert into propertyDetail values ('state','Nevada',16);
insert into propertyDetail values ('state','North Carolina',17);
insert into propertyDetail values ('state','New Jersey',18);
insert into propertyDetail values ('state','New Mexico',19);
insert into propertyDetail values ('state','New York',20);
insert into propertyDetail values ('state','Ohio',21);
insert into propertyDetail values ('state','Pennsylvania',22);
insert into propertyDetail values ('state','South Carolina',23);
insert into propertyDetail values ('state','South Dakota',24);
insert into propertyDetail values ('state','Tennessee',25);
insert into propertyDetail values ('state','Texas',26);
insert into propertyDetail values ('state','Utah',27);
insert into propertyDetail values ('state','Vermont',28);
insert into propertyDetail values ('state','Virginia',29);
insert into propertyDetail values ('state','Washington',30);
insert into propertyDetail values ('state','West Virginia',31);
insert into propertyDetail values ('state','Wisconsin',32);
insert into propertyDetail values ('state','Wyoming',33);
insert into propertyDetail values ('focus','Men Only',1);
insert into propertyDetail values ('focus','Women Only',2);
insert into propertyDetail values ('focus','Military',3);
insert into propertyDetail values ('focus','Religious',4);
insert into propertyDetail values ('setting','Large City',1);
insert into propertyDetail values ('setting','Small City',2);
insert into propertyDetail values ('setting','Large Town',3);
insert into propertyDetail values ('setting','Small Town',4);
insert into propertyDetail values ('setting','Rural Area',5);
insert into propertyDetail values ('collegeType','Liberal Arts',1);
insert into propertyDetail values ('collegeType','Medical',2);
insert into propertyDetail values ('collegeType','Law',3);
insert into propertyDetail values ('collegeType','Engineering',4);
insert into propertyDetail values ('collegeSize','Large (>20000 students)',1);
insert into propertyDetail values ('collegeSize', 'Medium (5000-20000 students)',2);
insert into propertyDetail values ('collegeSize','Small (<5000 students)',3);
insert into propertyDetail values ('SATAvg','>1400',1);
insert into propertyDetail values ('SATAvg','1200-1400',2);
insert into propertyDetail values ('SATAvg','1000-1200',3);
insert into propertyDetail values ('SATAvg','800-1000',4);
insert into propertyDetail values ('SATAvg','<800',5);
insert into propertyDetail values ('collegeRanking','Top 20',1);
insert into propertyDetail values ('collegeRanking','20-50',2);
insert into propertyDetail values ('collegeRanking','50-100',3);
insert into propertyDetail values ('collegeRanking','100-200',4);
insert into propertyDetail values ('collegeRanking','Others',5);
insert into propertyDetail values ('sportsTradition','Excellent (won major championships)',1);
insert into propertyDetail values ('sportsTradition','Good (competitive)',2);
insert into propertyDetail values ('sportsTradition','Poor (not competitive)',3);
insert into propertyDetail values ('studentFacultyRatio','Small classes (under 10:1)',1);
insert into propertyDetail values ('studentFacultyRatio','Medium classes (10:1 to 20:1)',2);
insert into propertyDetail values ('studentFacultyRatio','Larger classes (above 20:1)',3);
insert into propertyDetail values ('cost','Upto $5000',1);
insert into propertyDetail values ('cost','Upto $10000',2);
insert into propertyDetail values ('cost','Upto $15000',3);
insert into propertyDetail values ('cost','Upto $20000',4);
insert into propertyDetail values ('cost','Above $30000',5);
insert into propertyDetail values ('collegeAge','Old school - Established over 100 yrs ago',1);
insert into propertyDetail values ('collegeAge','Established over 50 yrs ago',2);
insert into propertyDetail values ('collegeAge','New College - establish in past 50 yrs',3);
--
--
drop table userBookmarks cascade constraints;
create table userBookmarks (
  userid    varchar2(16),
  bookmark  varchar2(1024),
  primary key (userid,bookmark),
  foreign key (userid) references users
);
insert into userBookmarks values
  ();
--
--
drop table factTable cascade constraints;
create table factTable (
  name varchar2(64),
  url varchar2(256),
  publicOrPrivate number(3),
  region number(3),
  state number(3),
  focus number(3),
  setting number(3),
  collegeType number(3),
  collegeSize number(3),
  SATAvg number(3),
  collegeRanking number(3),
  sportsTradition number(3),
  studentFacultyRatio number(3),
  cost number(3),
  collegeAge number(3),
  primary key (name)
);
insert into factTable values ('Georgia State University','http://www.gsu.edu',1,1,0,
  .....);
insert into factTable values ('State University of New York, Stony Brook',
                              'http://www.sunysb.edu',1,3,0, ....);
insert into factTable values ('Howard University, Washington DC',
                              'http://www.howard.edu',2,3,1, ....);
insert into factTable values ('Spellman College, Atlanta',
                              'http://www.spellman.edu',2,1,2, ....);
