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. see image.

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.
• 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.

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.

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).