Overview The physical therapy organization has many locations in several states.

Question

Overview  

The physical therapy organization has many locations in several states. The organization treats patients for injuries. Each injury is given a treatment plan, and is treated over a period of time at one of the organization’s locations, by two or more therapists (at least two therapists are assigned for full schedule coverage). The therapy organization keeps notes on how each plan is progressing.

The Patient table contains data for the patients treated by the physical therapy organization, including the patient’s name, birth date, gender, address, the date the patient started working with the organization, any special comments the organization records about the patient. The State table contains both a full name and an abbreviated name for the states in which a patient lives. The Therapist table contains data for the therapists employed by the organization, including their name and birth date, their license number for the state, the number of years they have practiced physical therapy, and when they began employment with the organization.  

The Treatment_plan table contains data for the treatment injuries, including when treatment began and ended (treatment_end_date will be null until the treatment period ends), and the location the patient is treated. The Location table contains the offices where a patient can be treated. The Treatment_notes table is important to the organization because allows the therapists to record any number of notes (comments) about how treatment is progressing.  Lastly, the Assigned table keeps track of which therapists have been assigned to which clients for which treatment plans.

Application The application developed for the organization, and which uses this schema, is named TheraTrack. It is a day?to?day business system that allows the therapists to track patients, their injuries and treatment plans, and the physical therapists that treat them. The data is updated throughout each day with a series of data entry screens as new patients enroll for treatment, as treatment plans progress, and as therapists join or leave the organization. Other than supporting the aforementioned data entry screens, TheraTrack also supports searching for patients, treatment plans, and therapists. The application development team was able to pull out two queries to help you. The first is one of the queries used by the search screen that allows TheraTrack users to search for specific patients by name.  

SELECT * FROM Patient

WHERE Patient.last_name = ? AND Patient.first_name = ?;

Note that the question marks (?) in the query are parameters that are passed to the query dynamically at runtime. They are populated with whatever values the user enters on the search screen. The team managed to capture a screenshot for the screen as well, which is the following:

The team also pulls out a query TheraTrack uses to list out the most recent treatment plans that have are not yet finished, along with the therapists that are treating that client for that treatment plan.

SELECT * FROM Treatment_plan JOIN Treatment_notes ON Treatment_notes.treatment_plan_id = Treatment_plan.treatment_plan_id JOIN Location ON Location.location_id = Treatment_plan.location_id JOIN Assigned ON Assigned.treatment_plan_id = Treatment_plan.treament_plan_id JOIN Therapist ON Therapist.therapist_id = Assigned.therapist_id WHERE Treatment_plan.treatment_begin_date > ? AND Treatment_plan.treatment_end_date IS NULL;

2. Next, you need to identify all of the foreign key columns in the schema. As described in the first section, these all need to be indexed. You will need to decide whether to make them unique indexes, or non?unique indexes. List out all foreign key columns in tablename.columnname format, indicate for each whether a unique or non?unique index is needed, and explain your choice.

Patient Assigned Therapist O. assigned_id (PK) 1..1therapist id PK) patient_id (PK] first name last_name birth_date became patient on date comments on patient gender street1 street2 city state id FK1 postal code patient_id (FK1) therapist id (FK2) treatment plan id (FK3) first_name last name birth date therapist license_number began_employment date years practicing_therapy Treatment_notes Treatment plan Has a treatment-notes id {PK) treatment notes treatment plan_id FK1) treatment_plan_id (PK) treatment_begin_date treatment_end date location_id FK1 Lives In 1..1 1..1 Treated At State Location state_id PK) state full_name state abbreviated name location_id (PK) location name

Explanation / Answer

1. Patient_id is primary key in patient table and foreign key in assigned table. So that patient information can fetch using its id.

2. Therapist_id is primary key in therapist table and foreign key in assigned table. So that it's easy to know which therapist is assigned to which patient.

3. Treatment_plan_id is primary key in treatment_plan table and foreign key in assigned table and treatment_notes table.so that treatment notes easily related to patient and treatment id.

4. Location_id is primary key in location table and foreign key in treatment_plan table.so that patient location trace easily.

5. State_id is primary key in state table and foreign key in patient table. So that patient address can verify

6. Treatment_notes_id is primary key in treatment_notes. So that it has unique Identity

7. Assigned_id is primary key in assigned table. So that patient status can trace.

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.