Details of the weight of the assignment and due date are given in the course description.
For this assignment, you will create a simple web-based family tree application.
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. see image.
The following constraints should be applied in implementing the application:
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.
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.
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 persons record.
Provide a link to the persons parent(s) if present.
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.
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).