Appendix

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 in and around Auckland (e.g. Silverdale, Orewa, etc.) that have a shortfall of employees (especially during their peak times). In the 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 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 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 the description of how their main business operates:

NZCSA maintains a list of applicants who are willing to work on 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 particular 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 qualification information (for example description, minimum duration, date of accomplishing the qualification, etc.). Each applicant can have several qualifications and each qualification may be earned by more than one applicant. For example, it is possible a Bachelor of Information Sciences (BInfSc) degree or an Oracle Database Certification is done by more than one applicant. Likewise, 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 multiple courses that develop that qualification. Courses that cover basic 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 training sessions. A training session is the presentation of a single course. Overtime, 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 the sessions and pay the required fee. Enrolment fee needs to be paid at least a week before the training session start date. A training session can accommodate several applicants but for quality, the session sizes are restricted, and 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 will not initially have any registrations.

NZCSA obviously maintains a list of companies that request temporary employees. The companys location will determine the agencys branch (e.g., Wellington branch is better suited for Palmerston North companies) that will be responsible for helping them with potential openings and 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 applicants 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 applicants qualification matches, the job is assigned, and an entry is made in the placement record file. That file maintains information about the opening and the applicant and information such as total number of hours placed is logged. In addition, the applicants job history is updated (i.e. an entry is made in the applicants job history).

Create a logical (relational) data model (e.g. Database or ER diagram). Use J Developer or Visio to create this diagram.

Use appropriate naming conventions while naming tables, attributes, relationships, etc. in your diagram. Also, use appropriate attribute data-types, data sizes, etc. in the diagram and ensure to flag PKs, FKs, etc.

There should be two business rules for each relationship (association) shown on diagram. State these business rules very clearly under a separate section (in your report). Similarly, state your reasonable assumptions (this helps us to better evaluate your design) under a separate section.

Examine each tables (entity's) attributes for dependencies and apply the rules of normalisation discussed in Chapter 6 of the textbook. You are required to show the detailed normalisation process {e.g. how you move from lower normal form (e.g. 1NF) to the next (e.g. 2NF)} in the submitted work. Use dependency diagrams for depicting all the relationships among a tables attributes. Normalise tables up to the highest possible NF.