— What's up with Jo?
— Can't stay in a relationship…
Modeling
Identify entities
ERD
Template
1. identify entities
Online Recruitment Agency
This case study is about a recruitment agency that matches prospective candidates with jobs from client companies and specializes in the computing industry. The agency stores its data in a variety of MS Office files on a SharePoint file server.
The agency has asked you to design and develop a database system that will enable them to improve the service they offer both to the candidates looking for a job and to the client companies who advertise their job vacancies.
The small agency consists of two directors, five recruitment consultants who market the agency to clients and candidates, and seven administrators who manage the day to day operations and provide reception duties such as answering the phone.
Each candidate that registers with the agency must provide the following information:
- Names
- Address
- Contact Number
- Email Address
- Age
- Experience
- Qualifications (e.g. diplomas, accreditations etc.)
- Specific Skills (e.g. SQL, Java, PHP etc.)
A candidate can only have one registration with the agency.
When a client company places a vacancy with the agency, they provide the following information:
- Company Name
- Address
- Contact Person
- Contact Address
- Contact Tel No
- Contact Email
- Job Title
- Salary Offered
- Location of Job (an area such as Hampshire, Dorset, London)
- Start Date
- Qualifications Required
- Experience Required
- Specific Skills Required
A client may advertise one or more jobs with the agency. Jobs are divided into the following categories:
- Programming
- Systems Analysis
- Support/Help Desk
- Network
- Database Developers
Using the information provided by the candidate and the client the agency can then match suitable candidates to suitable vacancies and they then arrange an interview.
The client company may select one or more candidates for an interview but they may not select anyone at all if nobody has the skills and qualifications required. Candidates can attend interviews for one or more jobs if they match the criteria for the job. The system records all interviews attended by a candidate.
If the candidate is successful and accepts the job this is recorded on the system so that they are not put forward for any more interviews. The system also records that the vacancy has been filled so that the job is no longer advertised. This also allows the agency to keep a record of which candidate got which job.
If a candidate is successful in the interview, the client that is going to employ them will pay the agency a fee for their service. There is no charge to candidates at any time. The agency sends the client an invoice, which is recorded on the system.
If the candidate is unsuccessful in the interview this is recorded on the system with a brief reason why, e.g. unsuitable location, insufficient salary, job given to a better candidate etc.
The agency needs to produce the following reports:
| Client List: | a list of all the clients the agency has |
| Candidate List: | a list of all the candidates registered with the agency (who are available for interviews). |
| Interview List: | for each vacancy a list of candidates who have been selected for interview with the date and time of each interview. |
| Payment List: | a list of all the clients who have not paid their fee |
| Outstanding Payments: | a total of all the money owed to the agency from their clients |
| Success Summary: | the total number of vacancies filled in the last month |
2. put them on diagram
3. draw relationships
3'. check for recursive relationships
4. check all entities are connected
5. check all reports are covered
6. squash most 1-to-1s
7. break most cycles
8. resolve *-to-*s?
9. add attributes?
Enhanced ERD
1. inheritance
1a. specialization
1b. generalization
2. weak/strong entities
3. aggregation and composition
Modeling traps
1. fan trap
2. chasm trap