Please help me create a ERD (Entity Relationship Diagram) using the…
Please help me create a ERD (Entity Relationship Diagram) using the business operations below and use Crows Foot Notation. And underline the Primary Key and ensure all relationships are properly connected to the appropriate entities. Also, please If a specialization hierarchy is included, it will be drawn using Category symbol notation or draw a big N.
Instructions:
The local corrections office needs a new system to track convicts that are released on parole. Their existing system is so
outdated that they have chosen to start over from the beginning with a whole new system instead of upgrading the old
system. The new Convicts On Parole System (COPS) will rely on a database to store the required data. Draw a
complete ERD that depicts the data model to record the required information. The data model must be neat and
professional in appearance. The assignment must be completed through your individual efforts – giving or seeking assistance from anyone
other than the instructor is not permitted. The final ERD should be in a single MS Word document and uploaded to D2L in
the Homework2 dropbox.
Convicts may be eligible to be placed on parole after serving a minimum percentage of their prison
sentence. While on parole, they are still under strict supervision by parole officers. For example, a
convict given an 8-year prison sentence may serve 5 years in jail and then 3 years on parole. Only
convicts that have been granted parole will be kept in this system. Each parolee is given a parolee
number. Since, unfortunately, many criminals return to jail many times, it is possible for a person to be
placed on parole many times throughout their lifetime. For simplicity, each time a person is placed on
parole, it is referred to as a parole case or just a “case”. Even if the person commits other crimes later and
ends up back in jail and on a different case at a later time, the same parolee number is used for that
person. In addition to the parolee number, the parolee’s name, date of birth, ethnicity, and date of death
are recorded in the system. Note that date of death will only have a value if the person dies after being
placed on parole. Each time a person is placed on a case, certain data must be recorded about the case –
the date the parole case is approved by the parole board, the date the parole case begins (i.e., the date the
parole begins), and the date the parole case is scheduled to end are recorded when the parole board
approves the parolee for parole. Also, the individual’s height, weight, hair color, and eye color at the time
the parole case begins (recorded on the day that they are released from holding) are also recorded in the
system. Each case is associated with only one parolee.
Responsibility for supervising a parolee while they are on a case is given to a parole officer. An officer
may be supervising many different cases. Normally, a single case is handled by a single parole officer,
but it is possible for parole officers to retire or leave the corrections department for other reasons.
Therefore, it is possible for a single case to be assigned to more than one officer, but only one at a time.
However, the system must track every officer that has ever been assigned that case and the date that the
assignment is made. Every case must have a parole officer. New parole officers may be entered into the
system so their training can be recorded, but they typically are not assigned any cases until they have had
time to settle in at the office. In addition, new parole officers are assigned one other parole officer to be
their mentor. A parole officer can mentor several other officers. Not all officers are mentors and not all
officers (especially ones that have been on the job for a long time) have a mentor. For each parole officer,
the officer’s employee number, badge number, name, hire date, office number, and office phone number
are recorded.
Parole officers are required to attend training courses periodically to help ensure that they are aware of
changes in policies and procedures, the newest techniques for parole investigation and monitoring, and
self-defense. The courses are offered by the corrections department. For each course, the course name,
description, type, and length in hours are kept in the system. The course types can be SD (self-defense),
PP (policy and procedures), or IT (investigations and tactics). Courses are offered on a regular basis.
Each time a course is offered, the date, starting time, and location of the course offering must be recorded
in the system. The location is a building address and room number. An officer can register to attend
many different course offerings, and each course offering can be attended by many different officers.
New officers may not have registered to attend any course offerings at first, but all officers must attend at
least 2 course offerings each year. Naturally, when the course offering is originally listed in the system,
no officers will have registered for it yet. The date on which each officer registers must also be recorded.
Most courses will have been offered many times, but the system should support the creation of new
courses that have not been scheduled to be offered yet. All course offerings must be associated with one
and only one course.
Parole officers will schedule appointments with the parolees whose parole cases they are supervising. It
is the parolee’s responsibility to show up on time for any appointment that their parole officer schedules
for them. It is uncommon for a parole officer to schedule an appointment with a parolee whose case they
are not supervising, but it is possible especially with new officers that do not have cases of their own yet.
However, not all officers will have scheduled an appointment and not all parolees will have had an
appointment set for them. All appointments are between a single parole officer and a single parolee.
When an appointment is set, the date, time, type and location for the appointment are recorded.
Appointment type can be either “informed” or “uninformed.” An informed appointment is one for which
the parolee is informed of the appointment. An uninformed appointment is one for which the parole
officer plans a surprise visit, usually either at the parolee’s place of employment or home address, but just
as with scheduled appointments, the location must be recorded. Regardless of the type of appointment,
the officer should be able to record comments about what was said and done in the appointment after it is
concluded. Only appointments between officers and parolees are kept in this system.
Parolees are required to have a place to live and a job. Failure to maintain either one of these by the
parolee can result in the parolee being returned to prison. Unfortunately, it is not uncommon for parolees
to move frequently and to struggle with keeping a job. A place to live and a job are arranged for the
parolee before they are actually released on a parole case, but the arrangements may not be made before
the parole case is entered in the system. For each job held by a parolee, the employer’s name, address,
job title, job start date, and job end date are kept in the system. The users are not interested in the
employers and the jobs beyond knowing that the parolee has the job. That is, they do not track the job
other than in the job’s relationship to the parolee. The same is true for addresses. The system must
support tracking a parolee’s address history – every address that the parolee lives at over time must be
kept in the system. For the address, the street, city, state, ZIP code, approximate move in date, and
approximate move out date are recorded.