rem SQL Data Model for Directory
rem Chuck Ehrlich 4/19/00

rem Person includes faculty, staff, advisory board, etc.
create table person (
  id integer,
  last varchar(30),
  first varchar(30),
  degrees varchar(30),
  title varchar(30),
  wtn varchar (20),
  htn varchar (20),
  ssn varchar (15),
  email varchar(50),
  fn varchar(20),
  url varchar(50),
  picture varchar(50),
  office varchar(50),
  hours varchar(100),
  school integer,
  dept varchar(10),
  status varchar(15),
  faculty_p char,
  staff_p char,
  advisory_p char,
  student_p char,
  update date,
  bio varchar(4000));

Q: what information do we need to link faculty with evaluation info?

rem School defines schools
create table school(
  id integer,
  name varchar(50);
  
insert into school (id, integer) values (10001, 'School of Business');

rem Q: do we need Program?
rem Program table, defines degree programs
create table program(
  id integer,
  school integer,
  short varchar(10),
  full varchar(80));


rem Group general group definition
create table group(
  id integer,
  school integer,
  short varchar(10),
  full varchar(80))

insert into group (id, school, short, full) values
 (1001, 10001, 'BSBA', 'Bachelor of Science in Business Administration');
insert into group (id, school, short, full) values
 (2001, 10001, 'MBA', 'Masters of Business Administration');

rem Group Membership
create table members(
  id integer,
  person integer,
  group integer);

rem Departments
create table depart(
  id integer,
  school integer,
  short varchar(10),
  full varchar(50)
  fn varchar(20));
  
insert into depart(id, school, short, full) values (410, 10001, 'Acctg', 'Accounting');
insert into depart(id, school, short, full) values (420, 10001, 'Mgmt', 'Management');
insert into depart(id, school, short, full) values (430, 10001, 'Fin', 'Finance');
insert into depart(id, school, short, full) values (440, 10001, 'Intl', 'International');
insert into depart(id, school, short, full) values (450, 10001, 'Mktg', 'Marketing');
insert into depart(id, school, short, full) values (460, 10001, 'Tele', 'Telecom');
insert into depart(id, school, short, full) values (470, 10001, 'Hosp', 'Hospitality');
insert into depart(id, school, short, full) values (480, 10001, 'IDS', 'Decision Sciences');
insert into depart(id, school, short, full) values (490, 10001, 'Hosp', 'Hospitality');
insert into depart(id, school, short, full) values
 (510, 10001, 'MIS', 'Management Information Systems');
insert into depart(id, school, short, full) values (520, 10001, 'Law', 'Law');
insert into depart(id, school, short, full) values (530, 10001, 'Comm', 'Communications');
insert into depart(id, school, short, full) values (540, 10001, 'Econ', 'Economics');

rem Status helper table w/values for Person reflect teaching
create table status(
  id integer,
  short varchar(15));

insert into status (id, short) values (110, 'teaching');
insert into status (id, short) values (120, 'not teaching');
insert into status (id, short) values (130, 'sabbatical');
insert into status (id, short) values (140, 'emeritus');
insert into status (id, short) values (210, 'admin');
insert into status (id, short) values (800, 'on leave');
insert into status (id, short) values (900, 'other');
insert into status (id, short) values (999, 'unknown');

rem Semester helper table, current_p is current semester
create table semester(
  id integer,
  year varchar(10),
  semester varchar(10),
  session varchar(10)
  current_p char);

insert into semester(id, year, session, current_p) values 
  (199804, '1998-1999', 'Fall ''98', 'F98', 'f');
insert into semester(id, year, session, current_p) values 
  (199901, '1998-1999', 'Intersession ''99', 'I99', 'f');
insert into semester(id, year, session, current_p) values 
  (199902, '1998-1999', 'Spring ''99', 'S99', 'f');
insert into semester(id, year, session, current_p) values 
  (199903, '1998-1999', 'Summer ''99', 'U99', 'f');
insert into semester(id, year, session, current_p) values 
  (199904, '1999-2000', 'Fall ''99', 'F99', 'f');
insert into semester(id, year, session, current_p) values 
  (200001, '1999-2000', 'Intersession ''00' ,'I00', 'f');
insert into semester(id, year, session, current_p) values 
  (200002, '1999-2000', 'Spring ''00', 'S00', 'f');
insert into semester(id, year, session, current_p) values 
  (200003, '1999-2000', 'Summer ''00', 'U00', 't');
insert into semester(id, year, session, current_p) values 
  (200004, '2000-2001', 'Fall ''00', 'F00', 'f');

rem Course as defined in the catalog
create table course(
  id integer,
  number varchar(20),
  title varchar(50),
  credits integer,
  prereq varchar(80),
  description varchar(4000));

rem Section of a Course where the real information lives
create table section(
  id integer,
  number varchar(20),
  session varchar(10),
  title varchar(50),
  time varchar(50),
  location varchar(50),
  url varchar(50),
  syllabus varchar(50)
  description varchar(4000));
Q: What other information do we need for a Section?  Description, learning goals, etc.?

rem Teaches links Person (teacher) and Section 
create table teaches(
  id integer,
  person integer,
  section integer);
  
rem Offerings describes courses for a semester
creat table offerings(
  id integer,
  session varchar(10),
  program
  section integer,
  
rem Comment on a Section of a Course
create table comment(
  id integer,
  person integer,
  section integer,
  submit date,
  approved_p char,
  text varchar(4000));