See the attachment(pdf file).
Microsoft Word - BCS350_201_project_instructions.docx BCS 350: Web Database Development CRN 24518 JLi Spring 2020 ------------------------------------------------------------------------------------------------------------------------------ Capstone Project Instructions ------------------------------------------------------------------------------------------------------------------------------ Objectives: Create a MySQL database and tables Query database using PHP (list, add, search for, delete records in a database) Implement best practices to secure user password Implement form-based web authentication in the user log in process Use prepared statements and user-defined sanitizing functions to prevent certain injection attacks Provide client-side validation using JavaScript and server-side validation with PHP Implement session management on chosen web pages Design a dynamic web database application. Tasks: This project is to acquaint you with fundamental and essential functions when building dynamic web pages with PHP and MySQL. You will make a web application that allows a user, upon successful login, to view, add, search for, and delete records in a MySQL database. The application can register a new user, logs in a returning user, or logs out a user. You will practice on web authentication, input validation, data sanitization, session management, sign-up and sign-in process, connecting to web database, querying database, processing data, and displaying results on web pages. You will make several web pages showing your knowledge and skills of building a database-driven website. The completion of this project will greatly help you in your senior project course. Your project should consist of the following function modules: Registration of a new user. A new user should at least provide email, username, password, and confirm password during the sign-up process. Data validation must be provided (see below for requirements). If any data is invalid, display an error message. If all data are valid, register the user. Check if the username is available. Display an error message if the username is not available and provide a link back to the registration page so user can choose another username to complete the registration. Login of an existing user with correct username and password. If the user enters incorrect username or password, display an error message and provide a link back to the login page so user can reenter the username and password. A main menu is provided to a user upon successful login. The main menu contains links for the following tasks: o Listing records of database table(s). You will design your own application database and decide what information will be listed on the web page. The data must be displayed in a table format. o Adding records into the database. Provide text fields or other HTML forms to allow user to add a record into the database. o Searching for records in the database. Use a drop-down list for user to choose a field to search and a text field for user to enter the information of that field to search for, for example, in our book example, if a user chooses “author” in the drop-down list, then the text field allows the user to enter the author information to look up; if a user chooses “title” in the drop-down list, then the text field allows the user to enter the title information to look up. o Deleting records from the database. You have two ways to do this. The first approach is you can modify book examples to list all records and provide “Delete Record” button for each record. Another approach is you can allow user to search for a record and delete it if the record exists and the user confirms to delete it. o Log out of user. o Once a task is finished, a link should be provided for the user to return to the main menu. Your project should also meet the following requirements: Your database should have two types of tables, application table(s) and a users table. You are free to decide your database and application tables (you can’t use the same publications database as the book example). If your database has one application table, it must have at least 5 fields. Your application table must have a primary key and/or other index for the fields that will be searched by the users. You must have a users table to store the registered user’s information including username, email, and secured password. The username is the primary key. The password must be salted and hashed. Write a php file setupDB.php to create your database tables and populate application table(s) with initial values. The form-based web authentication, not HTTP basic authentication, must be implemented in the user login process. Session management must be provided after successful authentication of a user. A user who has not successfully logged in should not be granted access to any function module in the main menu (not be able to list, add, search for, or delete records). Prompt the user to log in in the case that a user accessed the main menu page without login. All user input data must be sanitized to prevent injection attacks. Prepared statements with placeholders must be used for “adding records” module to sanitize the user input. Input data for new user registration must be validated using both JavaScript and PHP with criteria similar to the book example including the format of email must be valid; no input field is empty; usernames must be at least 6 characters long; passwords must contain at least 8 characters; and passwords must contain at least one of lowercase letters, uppercase letters, and numeric digits. The password must match confirm_password. The navigation and usability of your project should be reasonable and applicable. Some suggestions: add a link to the user login on the user registration page and add a link to the user registration on the user login page so user can switch between sign up and sign in; a main menu page is displayed after user logs in; add a link back to the main menu after each function module is finished. Complete the capstone project report that includes the Self-Assessment of Capstone Project. List your files and briefly describe the purpose of each file. (Feel free to include screenshots to show your work, such as, your web pages before or after an action, the structures of database tables, and data in the tables.) Include the integrity statement I certify that this submission is my own original work with your name as comments in each of your source files. Make a short video to present how your project works. The video presents in the following order: 1) Sign up using invalid values (email: alice, username: alice, password: mypass, confirm_password: mypass2). 2) Sign up using valid values (email:
[email protected], username: alice123, password: Mypass123, and confirm_password: Mypass123). 3) Show all the contents of the users table. 4) Go to the login page and log in with username alice123 and password Mypass123. The user should be directed to the main menu after login. 5) Demonstrate “Listing records” module. 6) Demonstrate “Adding records” module. Add a new record to the database. 7) Display the contents of your application table. 8) Demonstrate “Search for records” module. Search for the newly added record or other records. 9) Demonstrate “Delete records” module. Delete the newly added record. 10) Display the contents of your application table again. 11) Log out. 12) After log out, go to the main menu page by using its URL. Click on “Listing records” link if it is displayed. 13) Sign up using username alice123 again. 14) Feel free to demonstrate other features of your project. Extra credit: Demonstration of creative features or styles of your application. Hint: A project milestone system has been designed to guide you to complete a sequence of tasks that will lead to the successful completion of the capstone project. Please see the attached project milestones document. Submission: You must submit a zip file that contains the following files by May 6. No late submission will be accepted. Missing any part of the following documents will result in a major deduction of total credits. 1. All your source files 2. Project report 3. Project presentation video Grading Criteria: Your project will be graded based on the completeness and correctness of your work listed in the Self-Assessment of Capstone Project. BCS 350: Web Database Development CRN 24518 JLi Spring 2020 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Capstone Project Report ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1. Declaration of the Integrity Statement I certify that this submission is my own original work. Your name and RAM ID: ____________________________________ 2. Complete the Self-Assessment of Capstone Project Requirement Module Points Checklist Completion of the task Respond to the questions or answer Yes - if your project completes the task, No - if your project doesn’t complete the task, Partial – describe what part doesn’t work. Which development stack do you use for your capstone project? AMPPS, XAMPP, WAMPServer, or other. Database design (primary key, indexes) 10 Describe your database, how many tables your database has, and what data are stored in the database. What is the primary key in each table? Any indexes? Has a php file that creates the tables and insert initial data into the application table(s). Listing records 5 List the records in your application table in a table format. Searching for records 10 Use a drop-down list for user to choose a field to search and a text field for user to enter the information of that field to search for. Adding records 5 Provide text fields or other HTML forms to allow user to add a record into the database. Deleting records 5 Allow user to choose a record to delete or search for a record to delete. User sign-up, password security 15 The form contains fields for username, email, password, and confirm_password. Username is checked against the database users