​​ CRICOS Provider No. 00103D Insert file name here Page 1 of 4 ITECH3224/6224 World Wide Web Technology 2 1607 Assignment 1 – Family Tree 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 Application For this assignment, you will create a simple web-based family tree application. Database Structure The web application uses a relational database to create a family tree application. The database has the following table structure: Person (id, name, birth_date) Parent (parent_id, child_id) Each record in the Person table refers to a single person in the family tree. Primary keys are indicated with underlines or bold formatting, and foreign keys are italicized. The following constraints should be applied in implementing the application:  The id field in the Person table should be an integer primary key that uniquely identifies each person, and should automatically increment.  The parent_id and child_id fields in the Parent table form a compound primary key, and both refer to the id field in the Person table. One entry in this table represents one parent/child relationship, e.g. parent_id=5, child_id=6 means person 5 is parent of person 6. To represent both parents of a person, there will need to be two entries in the Parent table.  There must be no cycles or infinite loops, e.g. A is parent of B, B is parent of C, and C is parent of A. Such a construct is not possible in real life. CRICOS Provider No. 00103D ITECH3224_6224 1607 Assignment 1 family tree Page 2 of 4  The name field refers to the full name of the person, e.g. John Smith. There are no separate fields for first name, family name etc. This field should be limited to 100 characters.  The birth_date field refers to the day the person was born. It should be a MySQL 'DATE' type field. It must be supplied for each person, and may not be NULL. When entering data, care must be taken that all parents are older than their children. Initial data When the database is created it should be populated with data of your own choosing. Include this data as part of your written report. Include yourself as a person, using your full name and real date of birth. The family tree should include at least six other people, though you are free to use fictitious examples. Your initial family tree should include examples of a sibling, an aunt or uncle, and a grandparent. Creating the database Create an SQL file that creates a database on the server, creates the table above, and populates it with your initial data. Test your database by writing queries on the command line that display all initial data using SELECT statements, and include the queries in your report. Login as admin Write a PHP/HTML form that allows the user to login as admin. Only admins can create person and relationship records. The username is 'admin', and the password is 'password123' (no quotes). For security reasons, your PHP code should verify the password against a hash, not against plain text. The string 'password123' must not appear in your PHP code. Use the crypt() or password_verify() functions to achieve this. It is ok to verify the password in code without hitting the database. Use cookies or sessions to remember that the user is logged in as admin Creating a person (admin) Write an HTML form that adds a new person to the database. The form should request a name and a date of birth. Using PHP, validate that the date of birth is valid, and is in a format that your MySQL database will accept (e.g. yyyy-mm-dd). Use PHP to limit the length of the name to 100 characters. Creating a parent/child relationship (admin) Write an HTML form that adds a new parent/child relationship to the database. The parent_id and child_id may be entered as numbers. It is not necessary to provide a facility to 'look up' either person from the existing database - just the id number will be sufficient. Using PHP, validate that the parent_id and child_id exist in the database. CRICOS Provider No. 00103D ITECH3224_6224 1607 Assignment 1 family tree Page 3 of 4 Viewing a person Write PHP and HTML to display a single person from the database. The person id should be obtained from the URL via a $_GET parameter. All of their data from the database should be displayed. The end user does not have be logged in as admin to view a person's record. Provide a link to the person's parent(s) if present. Grandparents Write PHP and HTML code to display a list of all the grandparents in the database, sorted in ascending date order – that is, the oldest grandparents are at the top. Aggregate data Complete the following using SQL aggregation such as COUNT and SUM, subqueries or nested SELECT statements, inner joins and (left or right) outer joins.  Create a page that contains a list of all people with only one parent, ordered by the age of that parent in descending date order (youngest parent first). Additional task for ITECH6224 Students 1. If user unsuccessfully attempts to login as admin 3 times in a row, lock them out from further login attempts for 60 seconds. 2. Provide functionality to change or delete an existing person in the database. 3. Provide functionality to search for a person in the database by all or part of their name. Further details Documentation Include a written report containing:  Initial data details  The SQL queries 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. Refer to the Course Description for details of submission to Moodle, late assignments, extensions, special consideration, plagiarism, student support, presentation of academic work and adopted reference style. Submission All files should be zipped and uploaded to Moodle by the due date and time. Check with your tutor as to whether a hard copy is required in addition to the electronic submission. CRICOS Provider No. 00103D ITECH3224_6224 1607 Assignment 1 family tree Page 4 of 4 ITECH3224/6224 World Wide Web Technology 2 Assignment 1 Marking Guide Name ID Marker Criterion Maximum Obtained Initial data – Requirements satisfied 1 Creating the database: Table structure, data types, field lengths, initial data entry 1 Admin login - Login form is well designed and functional - Server-side validation of username and password - Appropriate password hashing 4 1 2 1 Creating people - Only admins can create new people - Use of PHP to verify date of birth - Use of PHP to limit length of name 4 2 1 1 Create parent/child relationship - Only admins can create parent/child relationships - Use of PHP to validate parent/child ids 3 2 1 Other tasks - View person record with links to parent - Display all grandparents - Display people with only one parent 4 2 1 1 - Extra tasks (ITECH6224 only) - Lockout admin login after 3 attempts - Change or delete existing person records - Search for person by name 5 2 2 1 Documentation - Initial data - Completion of tasks - Assistance statement 3 1 1 1 Code quality - may lose up to 2 marks for; - Invalid HTML - PHP with errors or warnings - Poorly formatted or commented code 0 Totals - ITECH3224 - ITECH6224 Final scaled mark (correct to 1 decimal place) 20 25 20 Comments: