Choice Appointments Case Study Analysis

Table of ContentsTask 1: Work Centered Analysis Framework3Task 2: Client Interviews7Task 3: Performance Analysis9Task 4: Architecture of the proposed Information system for Choice Appointments Recruiting Company104.1 ERD (Entity Relationship Diagram)104.2 Third Normal Form11Task 5: Data flow diagram (DFD) modeling25Task 6: Database Implementation266.1 Data Dictionary266.2 Relationships336.3 Queries and Reports38Task 7: User Manual43References48

Task 1: Work Centered Analysis Framework

In general a typical Work Centered Analysis Framework (WCA) deals with information systems and the key business components involved building the respective framework. All the business and technical elements of the business can be analyzed using the WCA framework and it clearly declares that, system is always more and has more importance the technology. Irrespective of the technology, system should be built with ample architecture level aspects, such that even a non-technical person can understand the entire flow of the system or application (Reijers, 2011). Typical architecture of WCA framework is as shown below

Fig 1.1: WCA (Work Centered Analysis) framework (Kelly, 2015)

From the above figure it is clear that, WCA is composed of three important levels like customers, products and business process, where the respective business process holds the further elements like participant, information and technology. Thus from the above analysis on WCA it can be noticed that, entire business process is executed to produce the products which were used by the customers ultimately, where the role of technology, information and participants is crucial in this context (Kelly, 2015).

Now the case of Choice Appointments consultants is considered and analyzed using the WCA framework to derive the key components, elements and issues with respective to the level of information given. From the case analysis it can be understood that, Choice appointments mainly deals with staffing services and following are the six elements identified against the WCA framework

Customer

Customers identified for Choice Appointments consultancy as per the WCA framework are as listed below

· Managers of the client companies who are in need to hire an employee or staff

· All the applicants or contractors who receive the responses regarding the job application

· Agencies who receives the report regarding the job opportunities or the guidelines

Product

Following are the products identified for Choice Appointments case study as per the WCA framework

· List of applicants or contractors who fits the job position

· All the selected data items considered regarding the job position of the applicant

· Automatically generated acceptance or rejection letters

· Tests which include both the spelling test and computer tests using the tools like Microsoft Word, Microsoft Access, Microsoft PowerPoint and Microsoft Excel

· Timesheets, invoice and proof of payment maintained over the database and sent to the contractor (Faust, 2014)

Business Process

Following are the key business processes identified using the WCA framework against the case study of Choice Appointments consultancy

· Defining the selection criteria of the applicants

· Receiving the resumes from the applicants

· Scanning the resumes and extracting the required data of the applicants

· Selection of applicant based on the meeting the criteria of job position and thus forwarding the resumes for further processing to the respective hiring managers

· Sending the rejection letter or acceptance letters

· Tracking the entire hiring process

· Conducting the interview selection process using the tests like spelling and computer based tests

· Storing the applicant data for further recruitment or search process

Participants

Participants identified over the case of Choice Appointments using the WCA framework are as listed below

· Managers involved in hiring

· Applicants who apply for the respective job position

· Human resource employees

· Client side managers who request for new staff requirements

Information

Various aspects are considered while dealing with the case study given and the relevant information identified is as listed below

· Information related to list of qualified job applicants

· Information which holds the key description of job openings

· Information related to tests like spell tests and computer based tests

· Information related to one to one interview

· Information related to entire hiring process

· Scanned, selected and rejected resumes

· Database information which holds the respective information, pay slips, invoice and hiring details (Domenech, 2015)

Technology

There are many technological aspects identified over the current case of Choice Appointments consultant and they are as listed below

· Resume scanner

· Online spelling test generator

· Online or automated computer based test generator

· Test evaluation software

· Database that stores resumes, Timesheets, invoice and proof of payment

· Timesheet generator

· Invoice generators

· Proof of payment generator

Missing System elements

Apart from the information sorted based on the WCA framework, still there are some of the elements missing in this context and they are as listed below

· Detailed information related to scanning the resume and selection criteria is missing at the initial stages

· Details on test evaluation is also missing, where the number of marks required for selecting the candidate or number of marks considered for an average candidate

· Information related to job position like permanent or temporary and the respective duration of the job is not clear

· Frequency of the job positions per month or year is not clear with the given case

· Means of communication is not clearly mentioned and how the applicants are communicated once they are selected or there is a ready job position for them

· No detailed information related to timesheet data is not specified over the given case

· No mention of the database stored elements and the information related to reporting structure and access controls over the database is also missing

· Detailed information related to software, hardware and database which is being currently used over Choice Appointments is also missing (Marie, 2014)

Task 2: Client Interviews

Based on the missing system elements as identified in the previous section, interviews are conducted with the Choice Appointments clients and few of the questions are as listed below

1. How frequently do you get the job openings?

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2. How does your clients commence regarding the job openings, if you can’t attend the respective phone calls?

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

3. What exact parameters do you use to scan the resume of the applicants against the available job positions?

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

4. What exact information or attributes are tracked while preparing the timesheets?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

5. Do the tests or interview pattern used to select the candidates in future and if so what constraints are considered?

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6. What is the cutoff mark to be achieved by the applicants to get through the initial screening process?

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

7. What software, hardware and database is being used for the current recruitment process?

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Task 3: Performance Analysis

Performance analysis of the proposed database application is done against various requirements of customers, products and the corresponding processes. Both the Business Value and Customer Value are considered against the current performance analysis and in general the performance analysis of any system considers entire flow of the application, where the level of business process, product and output components is analyzed in this context. For the current case of Choice Appointments recruiting company, business level and process level performance analysis is done and the corresponding discussion is as given below

Overall flow of the current application and the respective business level and process level performance analysis is as explained with the below steps

· Candidates send their resumes to Choice Appointments recruiting agency against the key position available which matches their skills sets or qualification

· Now the consultancy adds the candidate’s profile to the system for further evaluation

· Candidate details will be displayed by the system against the access control list of the users, where the respective administrators can edit, delete or modify the candidate details\

· Now the HR officer can view the candidate profile and schedule the interviews by creating an interview ID and schedule to the respective candidate

· Shortlisted candidates will take the spelling test and computer based test and the test format will be displayed to the candidates

· Now the admin will evaluate the test and updates the score to the system

· Based on the score and personal interviews, performance of the candidate is tested and final list of candidates is prepared by the HR or admin

· Test results, score and other performance aspects of the candidates are stored over the system by allocating a new Candidate profile to the respective candidates and they will be referred as employees then

· Client managers will either approach the HR for any new vacancy over their company through an email or phone call and each of them will be saved over the system

· HR will match the job requirements against the key words of job position and fetches the employee profiles

· Admin will fix the respective wage rates to the respective employees along with the type of contract like temporary or permanent

· Some of the steps are repeated against new candidate profiles and job positions raised by the client companies

Task 4: Architecture of the proposed Information system for Choice Appointments Recruiting Company

A detailed explanation to the case study of Choice Appointments against the key business level and process requirements and performance analysis is given in the previous section. Now the required database modeling is done in this section using the DFD, ERD, tables and query design and the corresponding discussion is as given below

4.1 ERD (Entity Relationship Diagram)

Detailed ERD built based on the Work Centered Analysis Framework implemented on the Choice Appointments case study is as shown below

Fig 4.1.1: ERD of Choice Appointments case study

4.2 Third Normal Form

From the above Entity Relationship Diagram (ERD), it is clear that, each and every identity is identified against all the required relationships, attributes and cardinalities. Low level description of the entities attributes and the relationship is given, where all the entities are decomposed to third normal form and a detailed discussion is as given below along with the tables at 3rd Normal Form and the corresponding data dictionary

Candidate table

Candidate tabled is used to define the attributes and relationship of the candidate entity and the data definition of the respective table in 3rd Normal form are as given below

Attribute

Description

Data Type

Notes

CandidateID

Unique ID which represents a candidate

Integer

Primary key of the table

Fk_SalutationID

Represents the salutation Id

Integer

Foreign key and references from the Salutation table

FirstName

Represents the first name of the candidate

Varchar(20)

First name of the candidate which is referred over other tables as well

MiddleName

Represents the middle name of the candidate

Varchar(20)

Middle name of the candidate which is referred over the other tables as well

LastName

Represents the last name of the candidate

Varchar(20)

Last name of the candidate which is referred over the other tables as well

Fk_SuffixID

Represent the unique id of Suffix attribute

Varchar(20)

Foreign key from Suffix table and acts as secondary key here

MailingAddress

Address of communication

Varchar(20)

Address of the candidate and is used for further reference and communication

AddressLine

Address of communication

Varchar(20)

Address of the candidate and is used for further reference and communication

City

City of the candidate

Varchar(20)

City of the candidate and is used for further reference and communication

Fk_StateProvince

State province of the candidate

Varchar(20)

State province of the candidate and is from the foreign key which is referred from State Province table

Postal Code

Postal code of the communication address

Varchar(20)

Postal code of the candidate and is used for further communication

Fk_CountryID

Country ID of the candidate

Varchar(20)

Country ID is the primary key which is referred from Country table

EmailAddress

Email address of the candidate

Varchar(20)

Email address which is used for frequent communication

DirectPhone

Phone number of the candidate

Varchar(20)

Phone number of the candidate which is used for frequent communication

MobilePhone

Mobile phone number of the candidate

Varchar(20)

Mobile phone of the candidate which is used for frequent communication

Fk_CampaignID

Campaign ID of the campaign conducted

Varchar(20)

Campaign ID which acts as foreign key and referred from Campaign table

Fk_EEOCategory

EEO category ID

Varchar(20)

EEOCategory ID which acts as foreign key and referred from EEO Category table

Gender

Gender of the candidate

Varchar(20)

Candidate gender

Location table

Attribute

Description

Data Type

Notes

City

City holds the information related to city which is used for communication

Varchar(20)

City used for mailing communication

Fk_StateProvince

State province of the candidate

Varchar(20)

State province of the candidate and is from the foreign key which is referred from State Province table

Postal Code

Postal code of the communication address

Varchar(20)

Postal code of the candidate and is used for further communication

Fk_CountryID

Country ID of the candidate

Varchar(20)

Country ID is the primary key which is referred from Country table

LocationName

Represents the location name

Varchar(20)

Location name used for mailing communication

LocationID

Represents the location unique ID

Varchar(20)

Primary key

Campaign Table

Attribute

Description

Data Type

Notes

CampaignName

Name of the campaign

Varchar(20)

Campaign name used during selecting the candidates

StartDate

Starting date of the campaign

Date

Starting date of the campaign

EndDate

End date of the campaign

Date

End date of the campaign

Fk_CampaignType

Type of the Campaign

Varchar(20)

Foreign key

CampaignID

Unique ID used to represent the campaign

Varchar(20)

Primary key

OpeningStatus Table

Attribute

Description

Data Type

Notes

OpeningStatusID

Unique Opening Status ID of the job opening

Varchar(20)

Primary key

OpeningStatus

Status of the job opening

Varchar(20)

Job opening status and is updated against the system workflow changes

InterviewType Table

Attribute

Description

Data Type

Notes

InterviewType

Interview type represents the nature of the interview conducted to the candidate

Varchar(20)

Type of interview and acts as foreign key over Interview table

InterviewTypeID

Unique ID of interview

Varchar(20)

Primary key of the table

Recruiter Table

Attribute

Description

Data Type

Notes

RecruiterID

Unique ID which represents a recruiter

Integer

Primary key of the table

Fk_SalutationID

Represents the salutation Id

Integer

Foreign key and references from the Salutation table

FirstName

Represents the first name of the recruiter

Varchar(20)

First name of the recruiter which is referred over other tables as well

MiddleName

Represents the middle name of the recruiter

Varchar(20)

Middle name of the recruiter which is referred over the other tables as well

LastName

Represents the last name of the recruiter

Varchar(20)

Last name of the recruiter which is referred over the other tables as well

Fk_SuffixID

Represent the unique id of Suffix attribute

Varchar(20)

Foreign key from Suffix table and acts as secondary key here

EmailAddress

Email address of the recruiter

Varchar(20)

Email address which is used for frequent communication

DirectPhone

Phone number of the recruiter

Varchar(20)

Phone number of the recruiter which is used for frequent communication

MobilePhone

Mobile phone number of the recruiter

Varchar(20)

Mobile phone of the recruiter which is used for frequent communication

Interview Table

Attribute

Description

Data Type

Notes

InterviewDate

Date of Interview

Date

Date of interview

Notes

Additional description about the interview

Varchar(20)

Additional notes which is used as the search content about the interview and the schedules

Fk_InterviewTypeID

ID of the interview type

Varchar(20)

Foreign key and has the object reference from InterviewType table

InterviewNumber

Number of the interview scheduled

Integer

Interview number which is conducted by a specific recruiter

InterviewID

Unique Id of the interview

Varchar(20)

Primary key

Fk_CandidateID

Unique id of the candidate who is attending the interview

Integer

Foreign key and has the reference from the Candidate table

Fk_OpeningId

Unique id of the opening scheduled

Varchar(20)

Foreign key and has the reference from the Opening table

DegreeRequired table

Attribute

Description

Data Type

Notes

DegreeRequired

Actual qualification required for the candidate

Varchar(20)

Candidate minimum qualification to attend the interview

DegreeRequiredID

Unique id of the degree required

Varchar(20)

Primary key

CandidateStatus Table

Attribute

Description

Data Type

Notes

CandidateStatusID

Unique Candidate Status ID to attend the interview or take appointment

Varchar(20)

Primary key

CandidateStatus

Status of the candidate

Varchar(20)

Candidate status and is updated against the system workflow changes

Department Table

Attribute

Description

Data Type

Notes

DepartmentName

Department name for which the candidate is selected

Varchar(20)

Department name for which the candidate is selected

DepartmentNumber

Department number for which the candidate is selected

Varchar(20)

Department number for which the candidate is selected

PrimaryContactName

Contact name of the primary employer

Varchar(20)

Contact name of the primary employer

PrimaryContacteEmail

Email id of the primary employer

Varchar(20)

Email id of the primary employer

FinalApproverName

Name of the final approver

Varchar(20)

Name of the final approver

FinalApproverEmail

Mail id of the final approver

Varchar(20)

Mail id of the final approver

DepartmentID

Unique ID of the department

Varchar(20)

Primary key

Suffix Table

Attribute

Description

Data Type

Notes

Suffix

Suffix of the candidate or recruiter

Varchar(20)

Candidate’s or Recruiter’s suffix and is used for further references over other tables

SuffixID

Unique Suffix Id

Varchar(20)

Primary Key

HiringManager Table

Attribute

Description

Data Type

Notes

HiringManagerID

Unique ID which represents a hiring manager

Varchar(20)

Primary key of the table

Fk_SalutationID

Represents the salutation Id

Integer

Foreign key and references from the Salutation table

FirstName

Represents the first name of the hiring manager

Varchar(20)

First name of the hiring manager which is referred over other tables as well

MiddleName

Represents the middle name of the hiring manager

Varchar(20)

Middle name of the hiring manager which is referred over the other tables as well

LastName

Represents the last name of the hiring manager

Varchar(20)

Last name of the hiring manager which is referred over the other tables as well

Fk_SuffixID

Represent the unique id of Suffix attribute

Varchar(20)

Foreign key from Suffix table and acts as secondary key here

EmailAddress

Email address of the hiring manager

Varchar(20)

Email address which is used for frequent communication

DirectPhone

Phone number of the hiring manager

Varchar(20)

Phone number of the hiring manager which is used for frequent communication

MobilePhone

Mobile phone number of the hiring manager

Varchar(20)

Mobile phone of the hiring manager which is used for frequent communication

Title

Title given to the hiring manager

Varchar(20)

Title of the hiring manager

Opening Table

Attribute

Description

Data Type

Notes

PositionName

Name of the position for which the job is initiated

Varchar(20)

Name of the position for which the job is initiated

Description

Description of the job position

Varchar(20)

Description of the job position

Fk_OpeningStatus

Status of the current opening

Varchar(20)

Status of the current opening against position

InternalDateOpen

Opening date internal to the company

Varchar(20)

Opening date internal to the company

ExternalDateOpen

Opening date external to the company

Varchar(20)

Opening date external to the company

InternalDateClosed

Closing date internal to the company

Varchar(20)

Closing date internal to the company

ExternalDateClosed

Closing date external to the company

Varchar(20)

Closing date external to the company

NumberofPositions

Number of positions open for the job

Integer

Job opening count

SalaryRangeLow

Lowest salary offered

Integer

Minimum range of salary

SalaryRangeHigh

Higher salary offered

Integer

Maximum salary range

Fk_DegreeRequriedID

Minimum qualification required

Varchar(20)

Foreign key reference from Degree required table

OpeningID

Unique ID of the respective opening

Varchar(20)

Primary key

Fk_LocationID

Location of the job opening

Varchar(20)

Foreign key reference from the Location table

Fk_DepartmentID

Department of the job opening

Varchar(20)

Foreign key reference from the Department table

Fk_RecruiterID

Recruiter of the job position

Varchar(20)

Foreign key reference from the Recruiter table

Fk_HiringManagerID

Hiring manager of the job position

Varchar(20)

Foreign key reference from the HiringManager table

ApplicationRequired

Condition if the application is required or not

Boolean

Yes or No

ReferenceRequired

Condition of the reference is required or not

Boolean

Yes or No

Fk_EEOJobCategoryID

EEO category ID of the jog

Varchar(20)

EEO category ID of the jog

EEOCategory Table

Attribute

Description

Data Type

Notes

CategoryID

Unique ID of EEO Category

Varchar(20)

Primary key

CategoryName

EEO category name

Varchar(20)

Category name

EEOJobCateogry Table

Attribute

Description

Data Type

Notes

JobCategoryID

Unique ID of EEO Job Category

Varchar(20)

Primary key

JobCategory

EEO Job category name

Varchar(20)

Category name of EEO job

JobCategoryNumber

Category number of EEO job

Varchar(20)

Category number

StateProvince Table

Attribute

Description

Data Type

Notes

StateProvince

State province information

Varchar(20)

State province

StateProvinceID

Unique id of state province

Varchar(20)

Primary key

Country Table

Attribute

Description

Data Type

Notes

Country

Country information

Varchar(20)

Country information

CountryID

Unique id of country

Varchar(20)

Primary key

Skill Table

Attribute

Description

Data Type

Notes

SkillID

Unique id of candidate skills

Varchar(20)

Primary key

SkillName

Candidate skill name

Varchar(20)

Skill name

Salutation Table

Attribute

Description

Data Type

Notes

Salutation

Salutation information

Varchar(20)

Salutation information

SalutationID

Unique id of Salutation

Varchar(20)

Primary key

Task 5: Data flow diagram (DFD) modeling

Fig 5.1: Recruitment process DFD

Fig 5.2: Payroll process DFD

Task 6: Database Implementation

6.1 Data Dictionary

Proposed database of Choice Appointments is implemented over MS Access and the respective tables are as provided below

Candidate Table

Location Table

OpeningStatus Table

Salutation Table

Campaign Table

InterviewType Table

Recruiter Table

Interview Table

DegreeRequired Table

CandidateStatus Table

Department Table

Suffix Table

HiringManager Table

Opening Table

StateProvince Table

Country Table

Skill Table

6.2 Relationships

Following are some of the important relationships developed among the tables as shown in the previous section

Candidate Status and OpeningCandidate Join

OpeningStatus and Opening

Recruiter and Opening

Salutation and Candidate

Candidate and Campaign

EEOJobCategory and Opening

Skill and CandidateSkillJoin

HiringManager and Opening

Country and Candidate

Suffix and Candidate

EEOCategory and Candidate

6.3 Queries and Reports

SELECT tblCampaign.CampaignName, tblCampaign.Owner, tblEEOJobCategory.*

FROM tblCampaign, tblDegreeRequired, tblEEOJobCategory;

SELECT tblCampaign.[Date-Created], tblDepartment.DepartmentName, tblInterview.InterviewDate, tblInterviewType.InterviewType

FROM tblCampaign, tblDepartment, tblInterviewType INNER JOIN tblInterview ON tblInterviewType.InterviewTypeID = tblInterview.fk_InterviewTypeID;

SELECT tblDepartment.*, tblOpeningStatus.OpeningStatus, tblRecruiter.fk_SalutationID, tblOpeningCandidateJoin_audit.zMachine

FROM tblDepartment, tblOpeningStatus, tblRecruiter, tblOpeningCandidateJoin_audit;

SELECT tblCampaign.StartDate, tblCandidate.FirstName, tblDegreeRequired.DegreeRequired, tblDepartment.[Created-By]

FROM tblDegreeRequired, tblDepartment, tblCandidate INNER JOIN tblCampaign ON tblCandidate.fk_CampaignID = tblCampaign.CampaignID;

SELECT tblCountry.CountryID, [tblHiring Manager].MiddleName, tblInterviewType.*, tblOpening.Description

FROM tblCountry, tblInterviewType, [tblHiring Manager] INNER JOIN tblOpening ON [tblHiring Manager].[Hiring ManagerID] = tblOpening.[fk_Hiring ManagerID];

SELECT tblCandidateSkillJoin.*, [tblHiring Manager].fk_SuffixID, tblRecruiter.EmailAddress

FROM tblCandidateSkillJoin, [tblHiring Manager], tblRecruiter;

Task 7: User Manual

Following steps can be followed to implement and run the proposed database for Choice Appointments case study

Job openings against the candidate skills can be added to the database using the below table and the respective snapshot is as shown below

Candidates’ information can be saved over the database using the Candidate table and the respective snapshot is as shown below

Skills required for a specific job can be searched using the search facility and the respective snapshot is as given below

Campaigns information can be added to the Campaigns table and the respective snapshot is as given below

All the queries and reports are maintained over a separate report table and the corresponding snapshot is as shown below

Report of the entire job openings summary is shown as below once the Open link is clicked

Details of Hiring Managers are as saved over the respective table and the corresponding snapshot is as shown below

Details of Recruiters are saved over the respective table and the corresponding snapshot is as shown below

Separate Form directory is maintained across a new table and the corresponding snapshot is as shown below

References

· Domenech, S. (2015). Optimization methods applied to the design of eco-industrial parks: A literature review. Journal of Cleaner Production, 87(15), 303-320

· Faust, O. (2014). The role of real-time in biomedical science: A meta-analysis on computational complexity, delay and speedup. Computers in Biology and Medicine, 58(1), 73-84.

· Kelly, M. (2015). The Materials-centred Approach to Public Library Collection Development: A Defense. Library Philosophy and Practice (e-journal), 1(1), 20-43.

· Marie, T. (2014). Computational intelligence in control. Annual Reviews in Control, 38(2), 233-239.

· Reijers, H. (2011). Best practices in business process redesign: An overview and qualitative evaluation of successful redesign heuristics. Omega, 33(4), 16-23.

48 | Page