Get Web Assignment Help by Live Online Developers

The chosen scenario for this project is a Survey application. The idea came from popular survey sites like Google and Survey Monkey. Users can respond to surveys and their answers are recorded in a database.

With the above scenario, the following EER diagram has been made:

A respondent is a person who takes a survey. A respondent can participate in many surveys but is not allowed to re-take a survey that he/she has already finished.

A survey contains questions that a respondent has to answer to. A survey has at least 1 question for the respondents to answer.

A question is a data that is prompted for the respondent to answer. There are 2 types of questions, one of which the user has to select from a given choice and another where the user has to type in their answer as text. There are 2 types of multiple-choice questions, one of which is where the user can choose only one as an answer and the other where the user can select multiple answers.

The name of the database is “survey”. It is created using the following SQL code:
create database survey;
use survey;

The survey table stores all survey information. It is created using the following SQL code:
create table survey (
  survey_id integer primary key,
  title varchar(100) not null,
  description varchar(1000) not null,
  create_date date not null
);
The following are the SQL codes that would create initial survey data:
insert into survey values(1, 'Animals', 'A survey about you and animals.', curdate());
insert into survey values(2, 'Places', 'A suvey about you and places.', curdate());

The question table stores questions of a survey. A question references the survey table in order to keep track of which question is owned by a survey. The question table is created using the following SQL code:
create table question (
  question_id integer primary key,
  survey_id integer not null,
  question varchar(1000) not null,
  question_type char(1) not null,
  foreign key (survey_id) references survey (survey_id)
);
The following are the SQL codes that would create the questions for each survey:
insert into question values(1, 1, 'Which animal do you like the most?', 'M');
insert into question values(2, 1, 'Why do you like that chosen animal?', 'E');
insert into question values(3, 2, 'Which of the following places you want to visit?', 'M');
insert into question values(4, 2, 'What other places do you like to visit?', 'E');
Letter ‘M’ represents a code that indicates that a question is a multiple choice question while ‘E’ indicates that a question is an essay question.

The multiple-choice question is the subtype representation of a question from the ER diagram. All multiple-choice questions will be on this table. It references the question table in order to retain inherited attributes. The Multiple Choice Question table is created using the following SQL code:
create table multiple_choice_question (
  question_id integer primary key,
  choice_type char(1) not null,
  foreign key(question_id) references question (question_id)
);
The following are the SQL codes that would assign which questions are multiple-choice:
insert into multiple_choice_question values (1, 'S');
insert into multiple_choice_question values (3, 'X');
Letter ‘S’ represents a code that indicates that only 1 answer can be chosen for the multiple-choice question. On the other hand, ‘X’ represents a code that indicates that more than 1 answer can be chosen for the multiple-choice question.

This table stores the choices for a multiple-choice question. It references the Multiple Choice Question table in order to know which choices are owned by a question. The Multiple Choice Question Choices table is created using the following SQL code:
create table multiple_choice_question_choices (
  question_id integer not null,
  choice_id integer not null,
  choice varchar(1000) not null,
  primary key (question_id, choice_id),
  foreign key (question_id) references multiple_choice_question (question_id)
);
The following are the SQL codes that would create the choices of the multiple-choice questions:
insert into multiple_choice_question_choices values (1, 1, 'Cat');
insert into multiple_choice_question_choices values (1, 2, 'Dog');
insert into multiple_choice_question_choices values (1, 3, 'Rabbit');
insert into multiple_choice_question_choices values (3, 4, 'Japan');
insert into multiple_choice_question_choices values (3, 5, 'Ireland');
insert into multiple_choice_question_choices values (3, 6, 'Greece');

The essay question table is the subtype representation of a question from the ER diagram. All essay answerable questions are stored on this table. It references the question table in order to retain inherited attributes. The table is created using the following SQL code:
create table essay_question (
  question_id integer primary key,
  foreign key(question_id) references question(question_id)
);
The following are the SQL codes that assign essay questions:
insert into essay_question values(2);
insert into essay_question values(4);

This table stores all the information about who takes surveys. It is created using the following SQL code:
create table respondent (
  respondent_email varchar(100) primary key,
  first_name varchar(50) not null,
  last_name varchar(50) not null,
  birth_date date not null,
  gender char(1) not null
);
The following are the SQL codes that create the initial respondent's data:
insert into respondent values ('[email protected]', 'Michael', 'Jordan', '1988-01-02', 'M');
insert into respondent values ('[email protected]', 'George', 'Washington', '1960-01-02', 'F');

The Survey Respondents table stores details on which survey has already been taken by a respondent. It references the Respondents table to know the person who took the survey. It also references the Survey table to know what survey was taken by the respondent. This table allows checking and making sure that a respondent does not re-take a survey. The table is created using the following SQL code:
create table respondent (
  respondent_email varchar(100) primary key,
  first_name varchar(50) not null,
  last_name varchar(50) not null,
  birth_date date not null,
  gender char(1) not null
);
The following are the SQL codes that assign which surveys have been taken by the respondents:
insert into survey_respondents values('[email protected]', 2, '2020-01-11');
insert into survey_respondents values('[email protected]', 2, '2020-01-11');
insert into survey_respondents values('[email protected]', 1, '2020-01-11');

This table keeps track of the choices that have been chosen as an answer by the respondent in a Multiple Choice Question. It references the Respondent table to know the person who chose the answer. It also references the Multiple Choice Question Choices table to know the information about the chosen answer by the respondent. The table is created using the following SQL code:
create table respondent_multiple_choice_answer (
  respondent_email varchar(100) not null,
  question_id integer not null,
  choice_id integer not null,
  primary key(respondent_email, question_id, choice_id),
  foreign key(respondent_email) references respondent(respondent_email),
  foreign key(question_id, choice_id) references multiple_choice_question_choices(question_id, choice_id)
);
The following are the SQL codes that assign which choices have been chosen by the respondents in a multiple-choice question:
insert into respondent_multiple_choice_answer values('[email protected]', 1, 3);
insert into respondent_multiple_choice_answer values('[email protected]', 3, 6);
insert into respondent_multiple_choice_answer values('[email protected]', 3, 4);
insert into respondent_multiple_choice_answer values('[email protected]', 3, 5);

The Respondent Essay Answer table stores all essay answers of a respondent to an essay question of a survey. It references the Respondent table to know the person who took the survey. It also references the Essay Question table to know which question is being answered. The table is created using the following SQL code:
create table respondent_essay_answer (
  respondent_email varchar(100) not null,
  question_id integer not null,
  essay_answer varchar(1000) not null,
  primary key(respondent_email, question_id),
  foreign key(question_id) references essay_question(question_id)
);
The following are the SQL codes that logs the essay answers written by the respondents in an essay question:
insert into respondent_essay_answer values('[email protected]', 4, 'Romania');
insert into respondent_essay_answer values('[email protected]', 4, 'Indenosia');
insert into respondent_essay_answer values('[email protected]', 2, 'It is cute.');

This embedded query calculates and returns the average number of responses to all surveys:
select avg(num_respondents) as avg_num_responses from (select survey_respondents.survey_id, count(survey_respondents.survey_id) as num_respondents from survey_respondents group by survey_respondents.survey_id) respondents_count;

This query returns all survey information and the number of respondents for each survey.
select survey.survey_id, survey.title, survey.create_date, count(survey_respondents.survey_id) as num_respondents from survey left join survey_respondents on survey.survey_id = survey_respondents.survey_id group by survey.survey_id, survey.description, survey.create_date;

This query returns the details of a specific survey:
select * from survey where survey_id=?;

This query returns all the details of a respondent on a multiple-choice question. It also returns the choices that they have chosen when prompted to answer the question:
select respondent.respondent_email, (year(curdate()) - year(respondent.birth_date)) as age, respondent.gender, multiple_choice_question_choices.choice from respondent join respondent_multiple_choice_answer on respondent.respondent_email = respondent_multiple_choice_answer.respondent_email join multiple_choice_question_choices on respondent_multiple_choice_answer.choice_id = multiple_choice_question_choices.choice_id where multiple_choice_question_choices.question_id=?;

This query returns all the details of a respondent on an essay question. It also returns their essay answer when prompted to answer the question:
select respondent.respondent_email, respondent_essay_answer.essay_answer, (year(curdate()) - year(respondent.birth_date)) as age, respondent.genderfrom respondent join respondent_essay_answer on respondent.respondent_email = respondent_essay_answer.respondent_email where respondent_essay_answer.question_id=?;

This query returns all questions of a survey, it is used when a respondent needs to answer surveys, viewing survey results, and or when recording the respondent's responses to the database:
select * from question where survey_id=?;

This query is used to retrieve multiple-choice questions. It is used for filtering:
select * from multiple_choice_question where question_id=?;

This query returns all available options a respondent can choose for multiple questions:
select * from multiple_choice_question_choices where question_id=?;

This query returns a count of 1 or 0 to check if a respondent has taken a survey. This is a useful indicator to know where the respondent already took a survey:
select count(*) as respondent_count from survey_respondents where respondent_email=? and survey_id=?;

After a respondent has taken a survey, their details are logged so that they cannot retake the survey again.
insert into survey_respondents values(?, ?, curdate())

This query returns a result of 1 or 0 to check the existence of a respondent. It is used to know whether a submitted survey requires adding a new respondent or updating it:
select count(*) as respondents_count from respondent where respondent_email=?;

This query is used to insert a new respondent data when the respondent is new to the system where they do not have any previous data saved:
insert into respondent values(?, ?, ?, ?, ?);

This query updates the details of an existing respondent. When a respondent has submitted a response to a survey but have previous data currently, then it gets updated:
update respondent set first_name=?, last_name=?, birth_date=?, gender=? where respondent_email=?;

This query inserts to the database the selected choice of the user as a response to a multiple-choice question:
insert into respondent_multiple_choice_answer values(?, ?, ?);

This query inserts to the database the entered answer of the user as a response to an essay question:
insert into respondent_multiple_choice_answer values(?, ?, ?);

This script is used for making a connection to the database. It contains the configuration as well such as the database name, username, password, and URL. This script is not meant for viewing in the browser but is used by other PHP scripts that require database operations.

This script will make a query to the database to select all surveys. The script will display all surveys including how many respondents are there. For each survey, the user can either view the responses to a survey or take a survey.

This script will receive the ID of a survey to be taken. It will then query the database to retrieve the details of the survey and the questions. All details are then displayed. Multiple choice questions use radio buttons and checkboxes while Essay questions use a text area for answers.

Users are not required to answer all the questions however they are required to enter their email, name, birth date, and gender when submitting the survey.

This script is called when the user submits the survey. This script will retrieve all answers of the user and then saves them into the database.

This script is meant for viewing the details of a survey including the responses to the questions. It displays who are the respondents, their gender, and their age which are somewhat important if classifying responses.

1. Full implementation of the EER diagram to a physical database.
2. Complex EER and Logical model.
3. Complex implementation where relations are optimized, appropriate relationship constraints, and keys.
4. All database tables are appropriately normalized to at least 3rd normal form.
5. Use of advanced MySQL statements.
6. A half-baked working PHP survey-based system, it lacks the administrator part.