Project 10.12
Web Survey Management System

Using PL/SQL Web Toolkit and the mod_plsql Apache module implement a Web application that manages the creation and deployment of online surveys. The system has two types of users: surveyors and survey takers. Surveyors should be able to create, edit, and delete surveys. Each survey consists of a set of questions and each question contains a question number, a question description, its answer type (one of 4 possible GUI elements: text box, text area, radio button or select list), and an answer description (properties of the GUI elements such as width of text box, values associated with the radio buttons etc).

Surveyors should also have the ability to assign survey takers with surveys. Survey takers should be able to take surveys that are assigned to them. They usually will be given a deadline before which they should submit the survey. They may take the survey any number of times before the deadline and each time save their responses. They will have an option of submitting the survey. However, once a survey is submitted, they should not be able to take the survey again.

The system should also provide the ability to display results of a survey to the surveyors. The formatting is left upto you.

The data associated with the system is stored in a relational database with the following schema: 

CREATE TABLE surveyors ( -- surveyor information
  vno		NUMBER(10)   PRIMARY KEY,
  name		VARCHAR2(30) NOT NULL,
  email		VARCHAR2(50) UNIQUE,
  password	VARCHAR2(20) NOT NULL
);

CREATE TABLE surveys ( -- information about surveys
  sno		NUMBER(10) PRIMARY KEY,
  sname	     VARCHAR2(100) UNIQUE,
  firstdate	DATE,
  lastdate	DATE,
  vno		NUMBER(10) REFERENCES surveyors
);

CREATE TABLE questions( -- questions for each survey
  sno		NUMBER(10)	REFERENCES surveys,
  qno		NUMBER(10)	CHECK(qno>=1),
  question	VARCHAR2(150),
  answtype	VARCHAR2(15),
  answdesc	VARCHAR2(400),
  PRIMARY KEY (sno, qno)
);

CREATE TABLE takers ( -- survey takers
  tno		NUMBER(10)	PRIMARY KEY,
  email		VARCHAR2(50)	UNIQUE,
  password	VARCHAR2(20)
);

CREATE TABLE takesurveys ( -- records survey takers for each survey
  tno		NUMBER(10)	REFERENCES takers,
  sno		NUMBER(10)	REFERENCES surveys,
  finishdate	DATE,
  isdone      CHAR	CHECK(isdone IN ('Y', 'N')),
  PRIMARY KEY (sno, tno)
);

CREATE TABLE answers( -- records responses to survey questions
  sno		NUMBER(10),
  tno		NUMBER(10),
  qno		NUMBER(10),
  answer	VARCHAR2(200),
  PRIMARY KEY (sno, tno, qno),
  FOREIGN KEY (sno, tno) REFERENCES takesurveys,
  FOREIGN KEY (sno, qno) REFERENCES questions
);
Some examples of rows in the question table are: 
insert into questions values(1,1,'How old are you?','TextField','2,3');
insert into questions values(1,2,'Enter your comments','TextArea','80,5');
insert into questions values(1,2,'Your Class','RadioButton','Freshman,Sophomore,Junior,Senior');
The SelectList question is similar to RadioButton question except that the user may choose one or more of the options listed when answering this question.

The initial sign in screen is shown here

After a survey taker signs in, they would see this screen.