Prototype Database Web System for TipTop Music Club and Fanzine

Case Study Scenario

You are a consultant working for a web development company who have been awarded the contract to design a fresh, dynamic and interactive web site for ‘TipTop music’ a retro music club and fanzine.

TipTop is owned and operated by an enthusiast (Jim Jones) who understands that the popularity of the club is due to his focus on the music rather than purely business factors. As such, he wishes to replicate the community experience of the paper magazine by including social networking concepts such as allowing customers to share each other’s music reviews, and by structuring the site using the latest web design ideas.

Since TipTop stores valuable data, a secure system is both legally required and expected by club members.

This phase of the project requires you to develop a prototype database web system, which will implement only part of the functionality that the final system will require. Refer to the following ‘Tasks’ section for details of the prototype requirements.

The design team that you work with has already identified that given the facilities provided by TipTop’s ISP (Internet Service Provider) this project will need to be capable of connecting to a MySQL database management system (DBMS) and will be developed using PHP as server-side scripting language.

A SQL script file and sample queries for creating and populating the database tables will be given on Blackboard.

The web site should be compatible with the current version of xampp and be portable. That is, you should not assume that your assignment will start in the root folder of the web server. It is essential that clear indications are given as to which source code file principally implements the required elements. This will be done by including a page ‘features.txt’ that lists the element, and the web page on which it is implemented. All data should be appropriately validated and protected against SQL Injection and stored cross-site scripting attacks.

Using the above TipTop music case study, develop a web solution that includes the following:

Practical Web Solution

Design and create appropriate HTML5/PHP web pages (connecting to the underlying MySQL database) to provide the following functionality:

• Create a home page (called html) located in the root of your web site (i.e. in http://localhost/KF7013/directory), which contains the appropriate links to the other sections of your web site, including the page containing your critical discussion. Note: depending on your design, this page may purely act as a redirect to a PHP script. [Please include a footnote that states "This site is an assignment for the Northumbria University module KF7013: Website Development and Deployment]
  Pages giving information about TipTop Music should be accessible to non-members without login (for example, the home page and contact pages). You should make up reasonable English text so the site looks realistic.
• The public should be able to see a list of music categories that are dynamically retrieved from the database. When one of the music categories is selected, an appropriately formatted list of albums in that category should be shown
• Provide one logon form that will allow club members and system administrators to log onto and use certain parts of the web site. If a user logs on as a member, then display page(s) relevant to either the member or administrator functionality.
• Club Member Functionality:If a user is logged on as a member, they should be able to:
  • View a list of their own reviews and dates created.
  • Delete a review which they wrote.
  • Add or Edit a review that they contributed. (The changes must be recorded in the database, using an UPDATE SQL query.)
• Administrator Functionality:A user logged on as a system administrator, should be able to view a page that lists all the members, in alphabetical order by surname. It will also display a summary of their reviews:
• The Administrator may create and delete club members. The form used to create a new member should ensure that data are valid. For example, that a member's password has at least six characters, with one or more numbers, that house number are numeric. Data validity should be checked on the client and indicated without screen refreshes.

Additional Requirements:

Your code must comply with the following requirements:

• HTML5 should be used on all web pages (NOTHTML4, XHTML, or similar).
• CSS should be used in order to separate style from content. tags should not be used; tables should only be used for tabular data.
• PHP must be used as the server-side scripting language.
• Web page content used should be dynamic (if the data is stored in the database, it should be retrieved from there, not hard coded into the web page).
• Your code should be structured in such a way as to promote code re-use (for example, place code that is used on more than one page in a separate file to be imported into all web pages that need it. You should use PHP and JavaScript (jQuery) functions as appropriate).
• All images used on the web site must be stored in the ./assets/imagesstylesheets in ./assets/stylesheets and JavaScript in ./assets/scripts/ directories, whilst php and html files should in ./content/.
• The database tables that are provided in the SQL script must be used. You are not permitted to alter the data structurein any way although you are expected to alter the data dynamically though your application.
• The integrity of the data should be maintained at all times.
• Security issues as discussed in the module need to be addressed. That is, your application should resist injection attacks, and cross site-scripting. All forms should validate their client-side data using either jQuery or JavaScript. ALL database queries must use prepared statements.
• The solution should be independent of the data, so that security data is retrieved from the database, and not hard coded into the web pages.
• Any orphaned pages/scripts -- pages or scripts that are not linked to the main pages in any way -- will not be marked (excludes index.html/index.php).
• PHP sessions should be used to provide application security. It should not be possible to access restricted pages once the owner has logged out.
• Sensitive data such as passwords should be stored encrypted.
• Use comments to reference code fragments (in any language) that you did not write yourself.
• Code that is program generated will NOT attract credit.