Building a Micro-Polling Website: Designing and Implementing the Database

Answered

This is the third in the series of assignments for building a micro-polling website. In the first assignment, you built an interface mock-up of the site. In the second assignment, you implemented the client-side programming. For this assignment, you will design and implement the database. The last two assignments will have you will use PHP and MySQL to implement the server-side programming, and perform AJAXbased updating of the website. Because the assignments build upon each other, you may wish to made modifications to your previous assignment submission before starting this assignment.

A critical element of many Web-based applications is the ability to store and query data. As a result, it is necessary to design an appropriate database for storing the data, construct the database in MySQL, and write the queries to store and retrieve the data.

This assignment is divided into three different components:

1. ERD and database specification

Your task here is to create an appropriate Entity Relationship Diagram (ERD) for the data associated with your online application. This ERD must document the data model for the entire system, and should conform to the second normal form specification for the data model.

Based on this ERD, provide the SQL statements that build the table structure for your database.

Note that you should carefully read through the next two sections of this assignment so that you fully understand what data is to be stored and retrieved. This will help you
to make important design decisions for the database.

2. Data Storage Queries

There are a number of forms in this web application that will require that data be stored in the database. Your task here is to write sample queries to verify that the data storage is working correctly.

a. Sign-up Form

save the new user information in the database
for the avatar image/graphic, you can assume that the URL is known at this point

b. Poll Creation Form

save the question and the answer alternatives in the database

c. Poll Vote Page

save the data for this particular vote in the database

3. Data Retrieval Queries

There are a number of pages in this web application that require that data be extracted from the database. Your task here is to write sample queries to verify that the data is
being extracted correctly.

a. Main Page

retrieve the five most recent active polls (question and answer alternatives), ordered with most recent first

b. Login Form

given the username and password, retrieve the user ID, matching screen name, and avatar graphic
if the username and password pair do not match, return an empty result set

c. Poll Management Page

given a specific user ID, retrieve all of the user’s polls (question, answer alternatives, number of votes for each alternative, and the date/time of the most recent vote)

d. Poll Vote Page