QuestionaireSql

/* sql to maintain a database of questionaires on various issues
The idea is to have unlimited number of questionaires without
restricting the number of questions per questionaire and giving
flexibility of adding/removing questions at any time. The only fixed
thing is that each question will have 5 options for answer - none, some, 
many, most, all. Grading is done either directly or inversely depending
on the question.  */


create database whatever_change_name;
use whatever_change_name;

/* questionaire table */

create table questionaire (
	id INT AUTO_INCREMENT not null UNIQUE,
	description text not null,
	name varchar(100) not null PRIMARY KEY
) ;

/* questions table - foreign key links to questionaire
ulta field determines order of scoring. Rank refers to the order
in which the questions are asked. Weighting reflects that
all questions are not equal */



create table questions
	(
	id INT AUTO_INCREMENT  not null UNIQUE,
	questionaire INT not null references questionaire(id),
	rank INT not null check(rank > 0),
	name VARCHAR(200) not null,
        weighting INT not null default 1,
	ulta ENUM('0','1') not null default 0,
        primary key(questionaire,name)
	) ENGINE = INNODB;

/* institutions table just name is enough here. Force the user
to enter the institution name seperately to avoid duplication due
to wrong spelling */

create table institutions
	(
	id INT AUTO_INCREMENT  not null UNIQUE,
	name varchar(200) not null primary key
	);

/* answers table - just a valid email address is enough as most 
respondees would like to remain anonymous. Ensure that combination
of emailid and institution is unique */

create table answers
	(
	id INT AUTO_INCREMENT  not null UNIQUE,
	email varchar(200) not null,
	institution int not null references institutions(id),
        comments text,
	primary key(email, institution) 
	)ENGINE = INNODB;


/* answer rows - the meat of the database */
create table answerrows
	(
	id int not null references answers(id),
	question int not null references questions(id),
	score int not null check(score >= 0 and score <=5),
	primary key(id,question)
	)ENGINE = INNODB ;