COP4521-Homework 3 Solved

The objective is to make sure

1.   You are familiar with the principles of Information Management.

2.   You can set up a simple Database and interact with it using SQL.

3.   You can set up a flask server in a Python application to work with the Database.

1         Specifications
For this homework you will be using the sqlite3 and flask libraries to create a database for a text-only ratings and reviews website, to be used by local restaurants. Samples for the different pages of the web application are shown on Section 3. Please make sure you conform to the following specifications:

1.      Create a sqlite3 database for your application. Call it reviewData.db 

2.      Write a Python script called setup.py that connects to the database and creates the tables specified in Steps 3 and 4. (

3.      Create a table called Reviews with the following fields):

•   Username - 40 character text field

•   Restaurant - 50 character text field

•   ReviewTime - SQL Datetime

•   Rating - float

•   Review - 500 character text field

4.      Create a table called Ratings with the following fields ():

•   Restaurant - 50 character text field

•   Food - float

•   Service - float

•   Ambience - float

•   Price - float

•   Overall - float

5.      Create an HTML page called index.html. This page will serve as the homepage for the website. It should include a Welcome Message and links to take users to the other pages in the website.

6.      Create an HTML page called addReview.html. This page will display a form for a user to submit a review for a local restaurant. This will include a “Submit” button. Upon clicking the submit button, the application should insert a row in the Reviews and Ratings tables. ()

7.      Create an HTML page called getReviews.html. This page will display a form for a user to get all the reviews for a particular restaurant. This will include a “Submit” button that will fetch all the reviews for the given restaurant from the Reviews table and show them in the showReviews.html page

8.      Create an HTML page called showReviews.html. This page will display the Username, Review and Overall ratings for a particular restaurant in a table format. This page will be rendered as a result of clicking on “Submit” in the getReviews.html page.

9.      Create an HTML page called showReport.html. This page will fetch and display all the ratings of the top - 10 restaurants by overall rating. If different restaurants have the same overall rating, order them alphabetically. If there are fewer than 10 restaurants in the database, show all of them. ()

10.   All pages except the homepage should have a “back to home” link. ()

11.   Write a Python script called app.py that is going to essentially run the application. This script should contain functions to:

(a)   A function to render the homepage for the root directory of the application.

(b)   A function to add rows to the 2 tables triggered by submitting the form on addReview.html. The data from a single form should be separated into data pertinent to each table and then run

2 SQL insert queries. Make sure you handle exceptional situations by rolling back.

(c)    A function to fetch all reviews for a restaurant, triggered by submitting the form on getReviews.html and rendering showReviews.html.

(d)   A function to run the query to get the top-10 restaurants and render the showReport.html page. (

(e)   If this were the main module, run the application. () 12. Please make sure your submission is well documented ()

13.   Your submission HW3.tar should contain the following files:

•   setup.py

•   app.py

•   index.html

•   showReviews.html

•   showReport.html

•   addReview.html

•   getReview.html

14.   Please note that the SQL commands should handle ALL of the database interactions. You shouldnot be using Python functions to filter the results returned by a slecte all or similar SQL command.

15.   flask requires that the templated HTML files will be in a folder called “templates” in the same folderas the python file. We will use the same setup for tests, even if folder organization is not required for submission.

2         Sample
You do not have to worry about verifying if the data entered by the users would fit in the text fields. The user ratings will also definitely be a number between 1 and 5 (inclusive). This verification will usually be handled by client-side Javascript. This is not the objective of the assignment, nor is it one of the learning objectives of the course.

The samples below are strictly functional and are not very aesthetic, again because that would be handled by client-side CSS, which is not one of the objectives of the assignment.

1.   index.html

 

2.   addReview.html

 

3.   getReviews.html

 

4.   showReviews.html

 

5.   showtReport.html