Scenario A gym has several personal trainers (PTs) who each have one or more specialisms. Currently, PT bookings are managed using a paper form and a diary. When a client books a PT session, the staff member completes the form which is then filed in the filing cabinet and also adds the booking to the gym diary. The form includes the fields: client name personal trainer name date time duration focus (e.g. weight loss, muscle gain, flexibility) This system works but as the gym is becoming more popular, it is becoming time-consuming. Particularly when multiple members of staff want to take, check and/or update bookings at the same time. Computers are located at locations throughout the gym and any staff member will be able to use it to check, update or create a new booking. Allowing clients to view their own bookings (e.g. on the website) could be possible in the future, but is outside the scope of the current project, which will only be accessed by staff. 4 Detailed Software Description It is recommended that you complete the tasks in the following order as the later sub-tasks will require the earlier ones. 4.1 Database Design a normalised (3NF) relational database for the above scenario, and create an ER diagram of the design. Ensure the multiplicities, domains and keys are specified correctly. Write the SQL to create the tables and insert some sample data. Once you have the database designed and implemented, design the SQL queries required to allow the required functionality, i.e.: add booking update booking delete booking list bookings for given PT for given client for specified date 4.2 Server Design and implement a (command line) server program which connects to the, previously implemented, database. It should have methods to achieve the required functionality (using the SQL queries you developed for the database):

Add booking shouldnt allow double bookings

List bookings all bookings bookings for a given PT bookings for a given client bookings on a particular day Update booking shouldnt allow double bookings Delete booking It should also have a method to load the given data file and insert it into the database. The sever program should provide the application protocol in Table 1 to clients using TCP streams (the server can be tested using Telnet). Add the required code to allow multiple client programs to connect to the server simultaneously.

Table 1: Application Protocol

Command	Description	Server Response
ADD booking-details	adds the booking to the database (if no clashes)	confirmation of success or failure (with reason)
LISTALL	Retrieve a list of all bookings in the system	Returns a string of all bookings (separated by newlines)
LISTPT pt-id	Retrieve a list of all bookings in the system for the given PT	Returns a string of all bookings of the given PT (separated by newlines)
LISTCLIENT client-id	Retrieve a list of all bookings in the system for the given client	Returns a string of all bookings of the given client (separated by newlines)
LISTDAY date	Retrieve a list of all bookings in the system on the given date	Returns a string of all bookings on the given date (separated by newlines)
UPDATE booking-id booking-details	Update the booking with the specified id	Confirmation of success or failure (with reason)
DELETE booking-id	Remove the booking with the specified id from the system	Confirmation of success or failure (with reason)

Client:

Design and implement Java class(es) to be used by the client GUI to connect to the server and (using the application protocol) execute the required functionality (this can be tested with a basic command line program). Design and implement an intuitive JavaFX GUI program which utilises these Java class(es). This GUI program should allow the staff to add, list, update and delete PT bookings. It should also have a method to disconnect from the server (freeing up the server for other clients).