Based on the above case study, "Want-2-Work", present each of the following:

1) ER diagram

2) Relational model

3) Rules or assumptions made

4) Between 3 or 5 queries using the relational model (inclusive of join, group by and order by clauses).
 

Transcribed Image Text:

CASE STUDY "Want-2-Work" is a job placement agency. Like many other such agencies, their primary business is bringing applicants and employers together. There are many people with varying skills that are either unemployed and looking for a job or are currently unhappy with their current job who are looking for another job. There are also many employers with various jobs that they have within their company that they need to fill with the right applicants. The business has these two primary customer groups: employers and applicants. Employers provide position descriptions, i.e. jobs. This would include all the usual aspects such as the title of the positions, duties, required skills, required qualifications etc. etc. You may have seen position advertisements in the newspapers that provide these sorts of details. The job agency also maintains details about the applicants. All the usual personal details of course, plus details normally found in resumes, such as skills and qualifications, interests/hobbies, work experience etc. At any one time, an employer can have several positions open, and there will naturally be many applicants for each position. Applicants can be assigned to interviews for various positions, and can have first, second and even third interviews before they might be appointed to a position. The interview information needed by the Job agency includes the interview number (is it the 1st interview or 2nd...etc), who is attending the interview, the date time and location of the interview. The location can be physical or online, with appropriate adherence to the social distancing laws during the COVID-19 isolation. Therefore, location maybe at a physical location or an online invite to a communication medium that must provide audio and video capabilities (like Zoom and Skype). The success of an applicant's application in getting a job is also recorded. All this information needs to be stored in the database so that the job agency can keep track of employers, positions, applicants, applicant applications and interviews sessions. It is very important to the job agency that they have all this data so they can run regular reports on outstanding jobs and reports on applicants that have not been placed into a job. CASE STUDY "Want-2-Work" is a job placement agency. Like many other such agencies, their primary business is bringing applicants and employers together. There are many people with varying skills that are either unemployed and looking for a job or are currently unhappy with their current job who are looking for another job. There are also many employers with various jobs that they have within their company that they need to fill with the right applicants. The business has these two primary customer groups: employers and applicants. Employers provide position descriptions, i.e. jobs. This would include all the usual aspects such as the title of the positions, duties, required skills, required qualifications etc. etc. You may have seen position advertisements in the newspapers that provide these sorts of details. The job agency also maintains details about the applicants. All the usual personal details of course, plus details normally found in resumes, such as skills and qualifications, interests/hobbies, work experience etc. At any one time, an employer can have several positions open, and there will naturally be many applicants for each position. Applicants can be assigned to interviews for various positions, and can have first, second and even third interviews before they might be appointed to a position. The interview information needed by the Job agency includes the interview number (is it the 1st interview or 2nd...etc), who is attending the interview, the date time and location of the interview. The location can be physical or online, with appropriate adherence to the social distancing laws during the COVID-19 isolation. Therefore, location maybe at a physical location or an online invite to a communication medium that must provide audio and video capabilities (like Zoom and Skype). The success of an applicant's application in getting a job is also recorded. All this information needs to be stored in the database so that the job agency can keep track of employers, positions, applicants, applicant applications and interviews sessions. It is very important to the job agency that they have all this data so they can run regular reports on outstanding jobs and reports on applicants that have not been placed into a job.