Purpose: The purpose of the assignment is the implementation of an application that stores data in a MySQL database.
STEP 1: create a new database and name it with a name composed of your surname and student ID. (example: Bertolotto123456).
STEP 2: your database will represent the following scenario: "Candidates are called for interviews by companies who have open positions with specific requirements".
Your database must include the following information and may include any other information that you consider necessary for representing the concepts and implementing the queries listed below:
- Candidate details: CandidateID, Name, address, telephone number, skills (administrative, managerial, etc.).
- Position details: PositionID, company, type of position, and skills required.
- Company details: CompanyID, Company name, reference person's name, address and telephone number.
- Interview details: You must decide what information should be used to best represent this concept based on the constraints and information provided below.
- Constraints:
- One company can request many interviews for a position.
- One candidate can be called for many interviews in relation to a position.
- One company can hire many candidates in relation to a position.
- Each candidate may only be hired by one company and in relation to only one position.
- Each candidate can have many skills.
- Each position can require many skills.
NOTE: You must create table(s) and relationships that will allow you to represent the fact that interviews occur on particular dates, and whether a candidate is hired for a position after an interview or not (i.e., whether an interview has been successful).
STEP 3: For every table, create a stored procedure that includes a parametric query that allows you to insert a new row in such a table.
STEP 4: Implement the following queries (some of which are parametric) using stored procedures:
- Find the candidates with a given name.
- Find the candidates with a given CandidateID.
- Find the companies with a given name.
- Find the companies with a given CompanyID.
- Find the candidates who have a skill required by a given PositionID.
- Find the positions with a given PositionID.
- Find the positions requiring a given skill.
- Find the number of positions that require administrative skills.
- Find the interviews that occurred on a particular date.
- Find the name and CandidateID of candidates that were interviewed at least twice.
- Sort the positions according to the companies who are offering them.
STEP 5: export your database onto a self-contained .sql file which should have the same name as your database
STEP 6: prepare the related documentation as detailed in the next page.