Introduction
This is an individual assignment in which you are required to develop a dynamic web application as described below using PHP, MySQL, JavaScript and CSS.
Details of the weight of the assignment and due date are given in the course description.
Task Description
Database Structure
The web application uses a 3-table relational database stored on a server that records the details of movie reviews. The structure of the database is:
REVIEWER (id, name, email, password)
MOVIE (id, title, release_date)
REVIEW (reviewer_id, movie_id, rating, review_date, comment, helpful)
Each record in the REVIEW table refers to one movie reviewed by one reviewer. Primary keys have been underlined.
The reviewer_id and movie_id fields form a compound primary key, and refer to the id fields in the REVIEWER and MOVIE tables respectively.
Both date fields may be stored as either ‘YYYYMMDD' strings, or using MySQL date types. Comment should be of the TEXT datatype.
The rating field is a numeric value between 0 and 10.
The password field should be a VARCHAR field of at least 125 characters.
The helpful field is a numeric value contains a count of the number of people who have found this review useful or interesting.
Initial data
When the database is created it should be populated with data of your own invention. Include this data as part of your written report. Each table should have between 3 and 6 records initially.
Creating the database
Create an SQL file that creates a database on the server, creates the three tables above, and populates it with your initial data.
Initial password data should be generated using, at minimum, the md5() PHP or MySQL function. If possible use the PHP password_hash() or crypt() functions, as available, to generate password hashes. It is acceptable for all initial REVIEWER records to share the same password.
Include a reviewer with email/username ‘tutor' and password ‘guest'.
Test your database by writing a query on the command line that displays all initial data, and include it in your report.
Reviewing movies
Use PHP and CSS to create a page that displays a list of movies.
When a reviewer selects a movie from the list, they should be taken to movie review page where they can see the reviews for that movie, in order from most helpful to least helpful.
Hint: consider passing the id of the movie using the querystring.
Create a form that allows a reviewer to add a review of their own for a movie, if they haven't already. Use appropriate HTML inputs to improve the user experience - that is, consider using drop-downs or radio buttons to present rating selections.
Use Javascript to validate the input, enforcing that the comment field is at least 10 characters in length. Write PHP code to add the review to the database.
User accounts
Write an HTML form that allows new reviewers to sign up to create a review. The form should request a name, email address and password. The password must be hashed before storing it in the database.
Write PHP code to allow reviewers to log in and log out. This will require the use of sessions or cookies. Only allow logged-in reviewers to add new movie reviews. The list of movies and movie review pages should be visible to anybody without logging in.
Aggregate data
For movies that have at least 3 reviews, display the average review at the top of their review page. The average should be calculated using an SQL query.
Create a page that contains a list of reviewers, and for each reviewer the number of movies they have reviewed.
On the movie list page, include a list of top 3 reviewers, ordered by how many people in total found their reviews helpful, from most helpful to least helpful. This should also be calculated using a single SQL query.
Additional task for ITECH6224 Students
On the topic of "Key-Value Databases" identify four (4), relevant, independent resources. Use these resources to write a discussion of the advantages and limitations of Key-Value Databases compared to relational databases in in your own words. Take care to cite appropriately. Word count should be about 500 words.
Further details
Documentation
Include a written report containing:
- Initial data details
- The query you used to test your database
- A list of parts of the assignment you have completed or not completed.
- Details of specific assistance you received from people other than your lecturer or tutor, and the names of those assisting.
Assignment support
This assignment is supported by the first 5 lectures and the first 6 labs. Work on the assignment should be spread over a number of weeks after the relevant lab has been mastered.