Having been impressed with your website development skills, FitNotFat are keen for you to develop a database system to manage their membership.
The basic tables are given in the file fnf_ddl.sql. Do not add any fields or tables or change any field or table names as these will be referenced in the demonstration.
The specific tasks to be undertaken are as follows.
1. Add relevant foreign keys to the tables.
2. Populate the database with the data given in the file fnf_data.sql
Construct the following queries, inserts and updates.
3. Select all details of each member.
4. List each members names and age together with the name of their trainer, no IDs to be displayed.
5. Insert a trainer.
6. Delete a trainer.
7. Update a members last name given their first and last names.
8. Enrol a member in a class given the member ID and the class ID.
9. List each members names and the classes that each member is enrolled on together with the class trainers name.
10. Show payment details for a member given their name. Both first name and last name together with all payment details should be included, no IDs to be included.
11. Enrol a member in a class given the members names and the class name.
12. Given a trainers name list all classes that s/he takes, together with the names of members in those classes ordered by last name and then by first name of the member.
Additional Information.
The following information is intended to clarify the structure of the database.
There are five tables, member, _class, trainer, enrolment and payment.
Each member may be enrolled on many _classes and each _class has a single trainer. Each member is also assigned their own trainer. Each member makes a number of payments over time.
Write PHP programs to execute the following tasks using the SQL statements from part A. An index page should be included to access the other pages.
Suitable checks for erroneous or missing data should be included with suitable error messages. Where applicable output should be in a clear tabular format with suitable headings.
The generated HTML should validate without error.
Ensure that you also keep your SQL queries and the DDL as a separate file. If a PHP program does not produce the required result marks can be gained from executing the SQL query in PHPMyAdmin.
1. Not relevant to part B.
2. Not relevant to part B.
3. Select all details of each member.
4. List each members names and age together with the name of their trainer, no IDs to be displayed.
5. Insert a trainer.
6. Delete a trainer.
7. Update a members last name given their first and last names.
8. Enrol a member in a class given the member ID and the class ID.
9. List each members names and the classes that each member is enrolled on together with the class trainers name.
10. Show payment details for a member given their name. Both first name and last name together with all payment details should be included, no IDs to be included.
11. Enrol a member in a class given the members names and the class name.
12. Given a trainers name list all classes that s/he takes, together with the names of members in those classes ordered by last name and then by first name of the member.
One randomly selected output page validates.
General use of HTML/CSS