Project Title: Bahamas Sports Physio Center - BSPC.

Your team is required to design a relational database application system for a "realistic" situation, followed by an implementation of the system running on the DB server MySQL managed by AITS. The application is a two-tier system, a client side as a browser and http server with PHP parser at the server side. The BSPC system is expected to support all representative queries and transactions, and produce various desired reports. Each team member will be responsible for the entire project and at least a well-defined portion of the project, to be agreed upon by the team members. You will be provided a 20 minutes time slot to demonstrate that your system performs as expected. A schedule for the demos will be prepared and posted. The project report must be submitted at demo time.

Project Description

A new physio center called Bahamas Sports Physio Center wants to implement a system to organize the operational management for its patients. As a database designer, you must ensure the integrity, security and reliability of the BSPC system by optimizing the design.

Registration: When a physio patient arrives at the physio center, the receptionist checks to see if the patient is already registered with the center. If the patient is already registered then his/her Personal Health Number is retrieved; otherwise, the receptionist will register the new patient. The physio center only treats physio patients who are at least 18 years old.

Appointment: Each physio patient must be referred by a physio trainer and must present a prescription number to the receptionist. A physio patient may be referred to different physio doctors at different times in different centers by the trainer. When a patient visits the center at the appointed time he/she will be assigned to a specific Physical therapist/Doctor referred by a trainer. A receptionist as an admin should be able to make appointments on behalf of a patient if requested. A patient can also make an appointment for a specific date and time eight weeks in advance limited to one appointment for any given day.

Therapists/Doctors details: The center only employs Physio Therapists who have at least 2 years of prior experience and Doctors with at least 6 years. The center records doctors/therapist availability for next 60 days.

Prescription, Treatment and diagnosis: Each prescription must be recorded by the BSPC system. Each prescription will list a diagnosis such as a strained muscle therapy or muscle pull therapy and the description of the doctor note (max. text limit of 100 words). The system must also record which therapists treated the physio patient on a specific visit to the center, treatment received and equipment used. A patient can receive more than one type of treatment and use more than one piece of equipment at any given visit. Sample equipment stationary bicycle , treadmill, weights. Sample treatment heat, ice, electrical stimulation, ultrasound massage.

Access rights: Two kinds of users can access the system: At Administrator (receptionist/nurse/doctor) level and user (patient) level.

Administrator level: Receptionist: Receptionist should be able to retrieve staff information (list of doctors, therapist and nurses available on a given date), update appointment for patient, view patient past records and update staff details.

Nurse/doctor: Can update records, insert, alter and view patients updated record.

Patient level: Patient: Can only view and make an appointment. If the patient is referred by a trainer, for a specific treatment with specific therapist, system should not allow patient to make an appointment with other doctor for the same treatment.

Payment: Patients can pay by cash, cheque, credit or debit cards. Payment method and details of payments must be recorded. The card details should be sent daily to the card processing agency to verify the payments including the amount and date.

User interface: Use the MySQL DBMS to develop a miniature database application system for the BSPC system. The BSBC system should provide its users with a good graphical user interface that is simple and dedicated for novice users. The system should be able to create/delete/edit/view whether for members or users of all kinds needed for the operations of the physio center.

As an admin, a different authorization needs to implement wherein required tasks such as Create, Update, View of details can be done and must ensure the traceability, for ex. Initially a patient being referred by a trainer, to the therapy suggested given by either therapist or prescription by a doctor and payment details. You must identify what attributes are likely to be stored for each entity based on the prior knowledge or research. Implement your design but include the Primary Keys, Foreign Keys and constraints. Populate each table with at least five records.

The following reports must be supported by the BSPC system: 1. How many patients has each physio therapists seen in a specified period of time? 2. Which piece of equipment has never been used? 3. List all information available for physio patients who have been at the center. 4. List all the information available for therapists who have been at the center. 5. List all the information available for therapists who work at the center. 6. List detail of reservations for a specific patient. 7. list availability for physio therapist/doctor during a specified period of time.

You need to include at least one more report that you see needed by every user type of the BSPC system. You need to consider the patients, the receptionists, the therapists and the doctors as users of the BSPC system. This means that you need to generate at least four extra reports to satisfy the need of these four types of users.

It is expected that he members of a team will discuss the application and educate themselves with some additional relevant information to enrich their application system to make it more realistic.

What you should hand in:

1. Develop an E/R diagram for the entity sets and relationships described above. Determine the key attributes and the multiplicity of the relationships. The design should be as compact as possible without sacrificing the required objectives. Make sure you state clearly any reasonable assumption made in your design, which is not specified in the requirements specified above.

2. Convert your E/R diagram into a relational database schema. Make necessary refinements to the schema, if possible. Identify various integrity constraints such as primary keys, foreign keys, functional dependencies, and referential constraints. Make sure that your database schema is at least in 3NF.

3. Provide implementation details of your database system in MySQL with a suitable user interface using HTML and PHP. Populate your tables with enough data to show various functionalities of your system (10 to 20 tuples per table on average).

You will need to submit a hard copy of your project report documenting your project and must include details on:

The design of the DB using an E/R data model. Its conversion into a relational model satisfying at least 3NF. The user-interface for each supported application and reports. A sample session for each application (user guide). All DDL codes.