The task involves the creation of an Entity-Relationship (ER) diagram that effectively portrays the clinic’s model.

Project 1COP 4710
Introduction:
In our class, we’ve discussed various methods used during the database design phase. Our primary emphasis
has been on utilizing (ER) diagrams to illustrate a representation of important entities and their
interconnections. The objective of this project is to provide you with a practical opportunity to create a
conceptual model for a practical issue. By doing so, you can gain insight into the procedures akin to those
employed by professional database designers. Accomplishing this task entails not only comprehending the
concepts taught in class but also applying creative thinking.
Objective:
For this assignment, you’ll be provided with a detailed outline of the requirements for the Blue
Shield Clinic (BSC), a compact physiotherapy clinic. Using the techniques covered in our course,
your task involves applying them to the given specifications. This will result in the creation of an
Entity-Relationship (ER) diagram that effectively portrays the clinic’s model.
Requirements Specification:
The essential data and relationships that the clinic must retain are outlined below. Keep in mind
that these descriptions might not offer complete guidance for establishing all necessary constraints
in the database. Consequently, you’ll need to make assumptions about relationships that haven’t
been explicitly defined. Furthermore, your design might not be capable of enforcing all the
requirements provided. It’s important to identify any unmet requirements and explain the reasons
behind the challenges in enforcing them.
1. Patient: refers to individuals undergoing treatment at the clinic. Each patient possesses a
distinct identification number, along with their first and last names, insurance provider
details, gender, date of birth, and a record of contraindications such as allergies to certain
medications or physical limitations. Patients can have multiple contraindications, and there
is no limit to the number they may have.
2. Therapist: The clinic distinguishes therapists through their employee ID, maintaining
records of their first and last names, address, salary, and an their unique state license
number. Therapists fall into two categories:
• PT: Physical Therapists
•
PTA: Physical Therapist Assistants, who can work either full-time or part-time.
3. Visit: A visit at the clinic entails a patient being attended to by a therapist. Patients can
•
•
•
undergo multiple visits with the same or different therapists, but they are only scheduled
for one visit per day. In addition to recording the patient’s details, therapist’s involvement,
and the date, we maintain data on the patient’s pain level, blood pressure, range of motion,
overall status (visit outcome), and a summarized report of the therapist’s assessment. The
measurement of blood pressure and range of motion might not be conducted during every
visit, depending on the patient’s health condition. Visits fall into three categories:
Initial Visit: This marks a patient’s first encounter with the clinic for a particular episode.
The patient is required to be seen by a PT (not a PTA), and this visit must encompass a
report and an evaluation of impairment (as outlined in Assessment below).
Regular Visit: Subsequent visits after the initial one must be linked to an episode (as
explained later). These visits do not necessitate a report, and there are no restrictions
regarding PTAs.
Assessment: Occurring every 8th visit or serving as the concluding visit for a given
episode. A PT (not a PTA) must administer assessments, which include a report and an
impairment evaluation known as a “G-code.” G-codes comprise a standardized code
coupled with a modifier that denotes functional impairment ranging from 0% to 100% (
0%-25%, 25%-50%, 50%-75%, 75%-100%).
4. Episode: Episodes represent significant concerns that patients require physical therapy to
address. They serve as the primary framework for organizing visits and evaluating patients’
progress over time. Every patient’s episode starts with an initial visit and involves 1 to 3
corresponding diagnostic codes, along with 1 to 3 treatment codes. These episodes include
an initial prognosis, a designated frequency of weekly visits, a variable count of treatment
goals (with a minimum of one), and completed episodes feature a concluding date. Patients
might undergo multiple episodes related to the same issue (diagnostic code), yet a single
visit cannot trigger more than one episode.
Diagnostic and treatment codes adhere to the International Classification of Diseases, 9th
revision (ICD-9 codes). These codes come with accompanying descriptions. It’s important
to note that diagnostic and treatment codes are distinct; they do not overlap. Each treatment
goal encompasses a description, an estimated completion date (which should land on an
assessment date), and a status that is updated when the goal is attained or adjusted during
that assessment.
5. Exercises: Exercises are activities that patients undertake during visits, guided by
therapists. Each exercise is denoted by a standardized Current Procedural Terminology
(CPT) code and is linked to a description and a billing rate. Patients can engage in multiple
exercises during a visit, with at least one exercise always being performed. We document
the duration of each exercise in minutes.
6. Outcome Measures: These are standardized assessment methods employed during
evaluations of patients. Each outcome measure is distinguished by an ID value and
accompanied by a description. Since diverse measures are evaluated using distinct scales,
we also store the maximum achievable score for each outcome measure. During each
assessment, we administer at least one outcome measure and retain the patient’s score for
each measure. The impairment level (G-code) for an assessment is established based on
the patient’s scores across the outcome measures.
Deliverables:
A PDF document that includes the following:
1- An ER diagram for the clinic described above
2- List of relational schemas (relational implementations) that correspond to the ER diagram
you developed
3- List of any assumptions you made in developing your EER diagram and the constraints
that your model does not satisfy (if any), along with a short description of why these
constraints are difficult to enforce
Tools:
You can use a tool of your choice to generate the diagram. Examples:
–
yEd for free at: http://www.yworks.com/en/products yed about.html
LucidChart (www.lucidchart.com), a free online diagram-ming tool
Microsoft Visio (available for free at apps.usf.edu)
MS PowerPoint
Any tool you use should offer the option to export your work as (or “Save As”) a PDF