Appendix A

NZ Cover Staff Agency (NZCSA) Case Study : Agency Hiring Temporary Employees for Company Placements

Auckland Cover Staff Agency called ACSA is an Auckland-based hiring agency that places temporary workers in companies that are in and around Auckland (e.g., Auckland CBD, Silverdale, Orewa, etc.) that have a shortfall in of employees (especially during their peak times). In recent years, the agency has had great success and would now like to expand their business to other NZ regions. They would like to open four more offices in the other main NZ cities (Tauranga, Wellington, Christchurch, and Dunedin). To reflect this, they have recently applied to New Zealand Companies Office for change of agency name and would like to be called NZ Cover Staff Agency (NZCSA).

The Director of the new company NZCSA requires you to design a database to assist with the administration of their hiring and placement services. The requirements collection and analysis phase of the database design process has provided the following requirements for the database followed by examples of the query transactions that must all be supported by the database.

Following is a description of how their main business operates:

• NZCSA maintains a list of applicants who are willing to work on a temporary basis to fill in temporary (temp) positions offered by companies to cover their employee shortfall especially during their peak business times. If an applicant has worked before with the agency, that applicant has that job history. Each time the applicant works, one additional job history record is created. Obviously if the applicant has never worked with the agency, no job history exists.
• Agency requires to maintain applicant’s qualification information (for example description, minimum duration, date of accomplishing the qualification, etc.). Each applicant can have several qualifications. For example, an applicant may have done both BInfSc and Oracle Database Certification.
• To generate additional income and to provide better service to both applicants and companies, NZCSA also offers courses to help applicants improve their qualifications. Every course develops one specific qualification; however, obviously NZCSA does not offer a course for every qualification (e.g., courses for the qualification BInfSc are not offered as this qualification is typically gained from a recognised university). Some qualifications have numerous courses that contribute towards that qualification. Courses that cover fundamental topics do not require any prerequisite qualifications. However, courses that cover advanced topics may need specific qualification(s) as prerequisites. A course can have several prerequisites and a qualification can be a prerequisite for more than one course.
• Courses offered by the agency are taught during the scheduled sessions. The training session is the presentation of a single course. Over time, NZCSA offers many training sessions for each course; however, training sessions for the new courses may not be scheduled immediately. To attend a training session, applicants need to enroll for all the course sessions and pay the required fee. Enrolment fee needs to be paid at least a week prior to the training session start date. A training session can accommodate several applicants but for quality purposes (e.g., trainer: applicant ratio, etc.), the session sizes are restricted; the size can vary depending on the course requirements (e.g., practical courses may take up to 10 applicants and theoretical courses can take up to 20 applicants). A new training session initially will not have any registrations.
• Courses offered by the agency are currently limited to the Auckland region but in future are expected to be offered in other regions as well. This requirement needs to be accommodated in the design.
• NZCSA obviously maintains a list of companies that request temporary employees. The company’s location will determine the agency’s branch (e.g., the Wellington branch is better suited for Palmerston North companies) that will be responsible for helping them with potential openings and meeting relevant requirements. Each time a company requests a temporary employee, NZCSA makes an entry in the openings file. NZCSA uses special codes (such as DBA-Oracle; SYS-1) to describe an applicant’s qualifications (Database Administrator, Oracle; Systems Analyst, Level 1) for an opening. This file holds all the necessary information about the sought opening such as company name, essential qualifications, a starting date, an expected ending date, hourly pay rate, etc. In addition, it can be assumed that each opening requires only one particular/main qualification. Many applicants can fill an opening, and an applicant can fill many openings.
• When an applicant’s qualification matches, the job is assigned, and an entry is made in the placement record file. That file contains information about the opening and the applicant and information such as the total number of hours placed is logged. In addition, the applicant’s job history is updated (i.e., an entry is made in the applicant’s job history).