微信客服：xiaoxionga100
微信客服：ITCS521
sql代写-CSE 581
时间：2022-05-08
CSE 581 Introduction to Database Management Systems Spring 2022
1
Project 2: Recruitment Management System Database
A. Introduction
1) Human Resources (HR) department in any company is involved in developing and administering
programs that are designed to increase the effectiveness of the organization. It includes the
entire spectrum of creating, managing, and cultivating the employer-employee relationship.
2) Human resource management is therefore focused on a number of major areas, including:
a. Recruitment
b. Compensation and benefits
c. Training and learning
d. Labor and employee relations
e. Organization development
3) In this project, you will design, implement, and test a database for the Recruitment branch of
the HR Department in a company called Ubuntu.
4) Tracking every candidate’s status is the focus of this database. The interview process affects a
candidate’s status from time-to-time. applied, interview 1, … interview n, rejected, waiting if
complained, offer extended, offer negotiated, offer accepted, onboarding, employee
onboarded successfully, blacklisted for not joining after accepting, offer declined, on-call for
next job opportunity.
5) For the purpose of this project, you are the database admin and thus the ‘point-of-contact’ for
all candidates for any job-opening for any position.
B. The Project Statement
1) Candidate applies for job openings at Company Ubuntu.
2) Company rejects or selects the candidate for 1st interview, which can be online or onsite.
3) Company rejects or selects the candidate for the following interviews till the final ‘nth’ interview.
4) Any of these interviews can be online or onsite.
5) Interviews have start time and end time. One interview may involve multiple interviewers.
6) Interviews may include multiple tests, which can again be online or onsite. Tests would have
start and end time. A test may need to be graded by multiple interviewers with a simple result,
passed or failed.
7) The candidate can be rejected after any of the ‘n’ interviews.
8) If a rejected candidate re-applies for any job-opening, a brand-new status flow starts for that
candidate. There is no limit on the number of times a candidate can re-apply or the time period
after which a candidate can re-apply.
9) If a rejected candidate complains about the hiring or interviewing process, the company puts
the candidate on a ‘waiting’ status. The ‘Complaint Handling’ branch of the HR department
looks into the complaint (with whose working we are not concerned). If the Complaint
department finds that the complaint is correct, a re-interview takes place and status is changed
accordingly. However, if the Complaint Department finds that the complaint is invalid, status is
CSE 581 Introduction to Database Management Systems Spring 2022
2
again changed to ‘rejected’.
10) If after the final ‘nth’ interview the company likes the candidate but there are no more job
openings available for the position interviewed for, the candidate is put on a “on-call for next
job opportunity” status. This means that the company will themselves contact this candidate for
interviews during the next round of recruitment.
11) If after the final ‘nth’ interview the company likes the candidate and there is a job opening
available, an offer is extended to the candidate.
12) Whether a candidate is selected or rejected after (1) the ‘application scanning’ phase in the
beginning or (2) after any of the ‘n’ interviews, is decided by the interview team of the company.
You as a database admin are not concerned with the internal working of this interview team.
When someone from the interview team updates the answer for a particular interview, you as
admin will change the candidate’s status accordingly.
13) The candidate can either decline or accept this offer or can negotiate ‘n’ number of times and
then decline or accept the negotiated offer. Status is changed accordingly (Declined, Accepted,
Negotiating).
14) If the given or negotiated offer is accepted, the onboarding procedure starts. The onboarding
procedure involves candidate’s background check, and collection and checking of all
documents required for employment.
15) If the candidate declines the given or negotiated offer, s/he is again put on the “on-call for next
job opportunity” status.
16) If onboarding is successfully completed, the candidate becomes an employee. For the purpose
of this project, this is where our recruitment procedure will end.
17) If the onboarding is unsuccessful (some joining requirements were not met) the candidate is
again put on the “on-call for next job opportunity” status.
18) If after accepting the offer and/or after successful completion of onboarding, the candidate
does not join the company, s/he is blacklisted. This means that the candidate cannot again
apply for any job-opening in the company.
19) If any interview is onsite, your database needs to keep track of the following things per
candidate: Airline reservation details, Hotel reservation details, Car Rental details.
20) The candidate will be asked to submit their expense receipts - all expenses that the company
says it will cover for food, etc. during the period of the onsite recruitment process. The
reimbursement team will update whether the reimbursement that the candidate is asking for is
valid and how much reimbursement will be given.
21) If any interview is online or at a location close to the candidate’s residence, one or more of the
above given details would not be needed.
22) The job openings can be for any department’s any position with the following details: actual
job description including code, name, description, job start date, job type, job, type, job
medium, job category, job platform, job start date, number of positions, etc.
23) Job Type can be Summer Internship, Full-time Job, or Contract-based.
24) Job Medium can be Online or Onsite.
25) Job Categories can be such as “IT”, “software design”, “testing”, “finance”, etc.
CSE 581 Introduction to Database Management Systems Spring 2022
3
26) Job Position can be anything like “IT Manager”, “Software Developer”, etc.
27) Job Platform is the medium used to post the job opening such as an online job board, company
webpage, etc.
28) The number of job openings per position are fixed and need to be tracked. Every time a position
is filled by a candidate (that is when the status becomes ‘offer extended’), the number of job
openings for that position should be reduced by 1).
29) And every time an offer is declined, or somebody is blacklisted, the corresponding job opening
should be increased by 1.
30) Although here we have only one db admin, the database has other roles of interviewers,
onboarding team specialist, etc., with the power to change a few things in the database. Thus,
when the reducing or increasing of the number of job-openings by 1 is taking place, make sure
that nobody else can do that at the same time, else there would be a synchronization issue.
31) Per interview, the candidate can give a review for the interviewer, and the interviewer can give
a review for the candidate.
32) Make the status changes due to the on-going interview process per candidate as automatic as
possible as the design of your database can allow.
C. Main tables to start your database design off with:
1) Job: position, title, type, medium, numbers of positions, etc.
2) Job Openings: job, numbers of positions, etc.
3) Candidates: name, email, phone, short profile, etc.
4) Documents: candidate, CVs, reference letters, cover letter, etc. Please note that documents
table have links to actual documents.
5) Application: candidate, job opening, etc.
6) Interviewers: name, department, title, etc.
7) Interviews: application, type, start time, end times, interviewer, etc.
8) Tests: application, type, start time, end times, answers, grade, etc.
9) Evaluation: application, evaluation notes, result, etc.
10) Reimbursement: application, request, processed, amount, etc. Please note that this includes
airfare, car rental, hotel reservation, food, etc.
11) Onboarding: candidate, job, start date, etc.
Technical Requirements:
A. Design
1) Determine the information to store, including everything mentioned in the Project Statement.
Once everything asked is taken care of, you can add your own design constraints.
2) Determine what tables to form, starting off with the suggested list of tables as listed above.
3) Determine what information to store per table.
4) Determine the logic and thus queries you would be needing for testing your design and
implementation.
CSE 581 Introduction to Database Management Systems Spring 2022
4
5) Determine potential integrity and security issues.
6) Design a descriptive E/R diagram, using draw.io, Vertabelo, Visual Paradigm, or any similar tool.
7) Normalize your design into its 3rd Normal Form. Your final database design should have at
least 16 to 20 tables.
8) Please do NOT cross lines and please make your design easy to read. A full screenshot of your
design is required, which should also be readable.
9) CamelCase naming standard is required (If a name is formed of multiple words that are joined
together as a single word, the first letter of each of the multiple words should be capitalized
and no underscore is allowed).
10) Please use full word to name your tables/columns. Do NOT use shortcuts.
11) Consider several business reports to produce.
B. Implementation
1) Create your schema
2) Create your tables with columns, primary keys, foreign keys, proper data types, nullabilities, and
necessary relationships and constraints
3) Address potential integrity and security issues
4) Create 4 views, 4 stored procedures, 4 user defined functions, 4 triggers, and 4 transactions
5) Include 4 scripts to create users with various security levels, passwords, and roles
Please note that you are expected to code by yourself! No auto-generated SQL is accepted!
C. Testing
1) Please populate your database by inserting about 5 meaningful test data into each table
2) Demonstrate your database’s reliability and logic through several nontrivial scenarios and
transactions to test your ♦design, ♦views, ♦stored procedures, ♦functions, ♦scripts, and
♦transactions. For every scenario, please add a comment to explain the purpose of that
scenario.
D. Report
Please include the following sections in a single PDF or DOC file:
a) Cover page: a descriptive title, your information, a short abstract.
b) Design: your introduction, design considerations, E/R diagram, database objects.
c) Implementation: SQL source codes with your descriptive comments.
d) Testing: testcases with your descriptive comments.
e) Conclusions: your final analysis and remarks.
f) Appendix: screenshots from SQL Server for codes, testing, etc.
Please review the grading rubric for the project report on Blackboard! All reports that adhere
to basic standards of grammar and spelling, and section/page formatting will have a base
score of 15 points.
1
Project 2: Recruitment Management System Database
A. Introduction
1) Human Resources (HR) department in any company is involved in developing and administering
programs that are designed to increase the effectiveness of the organization. It includes the
entire spectrum of creating, managing, and cultivating the employer-employee relationship.
2) Human resource management is therefore focused on a number of major areas, including:
a. Recruitment
b. Compensation and benefits
c. Training and learning
d. Labor and employee relations
e. Organization development
3) In this project, you will design, implement, and test a database for the Recruitment branch of
the HR Department in a company called Ubuntu.
4) Tracking every candidate’s status is the focus of this database. The interview process affects a
candidate’s status from time-to-time. applied, interview 1, … interview n, rejected, waiting if
complained, offer extended, offer negotiated, offer accepted, onboarding, employee
onboarded successfully, blacklisted for not joining after accepting, offer declined, on-call for
next job opportunity.
5) For the purpose of this project, you are the database admin and thus the ‘point-of-contact’ for
all candidates for any job-opening for any position.
B. The Project Statement
1) Candidate applies for job openings at Company Ubuntu.
2) Company rejects or selects the candidate for 1st interview, which can be online or onsite.
3) Company rejects or selects the candidate for the following interviews till the final ‘nth’ interview.
4) Any of these interviews can be online or onsite.
5) Interviews have start time and end time. One interview may involve multiple interviewers.
6) Interviews may include multiple tests, which can again be online or onsite. Tests would have
start and end time. A test may need to be graded by multiple interviewers with a simple result,
passed or failed.
7) The candidate can be rejected after any of the ‘n’ interviews.
8) If a rejected candidate re-applies for any job-opening, a brand-new status flow starts for that
candidate. There is no limit on the number of times a candidate can re-apply or the time period
after which a candidate can re-apply.
9) If a rejected candidate complains about the hiring or interviewing process, the company puts
the candidate on a ‘waiting’ status. The ‘Complaint Handling’ branch of the HR department
looks into the complaint (with whose working we are not concerned). If the Complaint
department finds that the complaint is correct, a re-interview takes place and status is changed
accordingly. However, if the Complaint Department finds that the complaint is invalid, status is
CSE 581 Introduction to Database Management Systems Spring 2022
2
again changed to ‘rejected’.
10) If after the final ‘nth’ interview the company likes the candidate but there are no more job
openings available for the position interviewed for, the candidate is put on a “on-call for next
job opportunity” status. This means that the company will themselves contact this candidate for
interviews during the next round of recruitment.
11) If after the final ‘nth’ interview the company likes the candidate and there is a job opening
available, an offer is extended to the candidate.
12) Whether a candidate is selected or rejected after (1) the ‘application scanning’ phase in the
beginning or (2) after any of the ‘n’ interviews, is decided by the interview team of the company.
You as a database admin are not concerned with the internal working of this interview team.
When someone from the interview team updates the answer for a particular interview, you as
admin will change the candidate’s status accordingly.
13) The candidate can either decline or accept this offer or can negotiate ‘n’ number of times and
then decline or accept the negotiated offer. Status is changed accordingly (Declined, Accepted,
Negotiating).
14) If the given or negotiated offer is accepted, the onboarding procedure starts. The onboarding
procedure involves candidate’s background check, and collection and checking of all
documents required for employment.
15) If the candidate declines the given or negotiated offer, s/he is again put on the “on-call for next
job opportunity” status.
16) If onboarding is successfully completed, the candidate becomes an employee. For the purpose
of this project, this is where our recruitment procedure will end.
17) If the onboarding is unsuccessful (some joining requirements were not met) the candidate is
again put on the “on-call for next job opportunity” status.
18) If after accepting the offer and/or after successful completion of onboarding, the candidate
does not join the company, s/he is blacklisted. This means that the candidate cannot again
apply for any job-opening in the company.
19) If any interview is onsite, your database needs to keep track of the following things per
candidate: Airline reservation details, Hotel reservation details, Car Rental details.
20) The candidate will be asked to submit their expense receipts - all expenses that the company
says it will cover for food, etc. during the period of the onsite recruitment process. The
reimbursement team will update whether the reimbursement that the candidate is asking for is
valid and how much reimbursement will be given.
21) If any interview is online or at a location close to the candidate’s residence, one or more of the
above given details would not be needed.
22) The job openings can be for any department’s any position with the following details: actual
job description including code, name, description, job start date, job type, job, type, job
medium, job category, job platform, job start date, number of positions, etc.
23) Job Type can be Summer Internship, Full-time Job, or Contract-based.
24) Job Medium can be Online or Onsite.
25) Job Categories can be such as “IT”, “software design”, “testing”, “finance”, etc.
CSE 581 Introduction to Database Management Systems Spring 2022
3
26) Job Position can be anything like “IT Manager”, “Software Developer”, etc.
27) Job Platform is the medium used to post the job opening such as an online job board, company
webpage, etc.
28) The number of job openings per position are fixed and need to be tracked. Every time a position
is filled by a candidate (that is when the status becomes ‘offer extended’), the number of job
openings for that position should be reduced by 1).
29) And every time an offer is declined, or somebody is blacklisted, the corresponding job opening
should be increased by 1.
30) Although here we have only one db admin, the database has other roles of interviewers,
onboarding team specialist, etc., with the power to change a few things in the database. Thus,
when the reducing or increasing of the number of job-openings by 1 is taking place, make sure
that nobody else can do that at the same time, else there would be a synchronization issue.
31) Per interview, the candidate can give a review for the interviewer, and the interviewer can give
a review for the candidate.
32) Make the status changes due to the on-going interview process per candidate as automatic as
possible as the design of your database can allow.
C. Main tables to start your database design off with:
1) Job: position, title, type, medium, numbers of positions, etc.
2) Job Openings: job, numbers of positions, etc.
3) Candidates: name, email, phone, short profile, etc.
4) Documents: candidate, CVs, reference letters, cover letter, etc. Please note that documents
table have links to actual documents.
5) Application: candidate, job opening, etc.
6) Interviewers: name, department, title, etc.
7) Interviews: application, type, start time, end times, interviewer, etc.
8) Tests: application, type, start time, end times, answers, grade, etc.
9) Evaluation: application, evaluation notes, result, etc.
10) Reimbursement: application, request, processed, amount, etc. Please note that this includes
airfare, car rental, hotel reservation, food, etc.
11) Onboarding: candidate, job, start date, etc.
Technical Requirements:
A. Design
1) Determine the information to store, including everything mentioned in the Project Statement.
Once everything asked is taken care of, you can add your own design constraints.
2) Determine what tables to form, starting off with the suggested list of tables as listed above.
3) Determine what information to store per table.
4) Determine the logic and thus queries you would be needing for testing your design and
implementation.
CSE 581 Introduction to Database Management Systems Spring 2022
4
5) Determine potential integrity and security issues.
6) Design a descriptive E/R diagram, using draw.io, Vertabelo, Visual Paradigm, or any similar tool.
7) Normalize your design into its 3rd Normal Form. Your final database design should have at
least 16 to 20 tables.
8) Please do NOT cross lines and please make your design easy to read. A full screenshot of your
design is required, which should also be readable.
9) CamelCase naming standard is required (If a name is formed of multiple words that are joined
together as a single word, the first letter of each of the multiple words should be capitalized
and no underscore is allowed).
10) Please use full word to name your tables/columns. Do NOT use shortcuts.
11) Consider several business reports to produce.
B. Implementation
1) Create your schema
2) Create your tables with columns, primary keys, foreign keys, proper data types, nullabilities, and
necessary relationships and constraints
3) Address potential integrity and security issues
4) Create 4 views, 4 stored procedures, 4 user defined functions, 4 triggers, and 4 transactions
5) Include 4 scripts to create users with various security levels, passwords, and roles
Please note that you are expected to code by yourself! No auto-generated SQL is accepted!
C. Testing
1) Please populate your database by inserting about 5 meaningful test data into each table
2) Demonstrate your database’s reliability and logic through several nontrivial scenarios and
transactions to test your ♦design, ♦views, ♦stored procedures, ♦functions, ♦scripts, and
♦transactions. For every scenario, please add a comment to explain the purpose of that
scenario.
D. Report
Please include the following sections in a single PDF or DOC file:
a) Cover page: a descriptive title, your information, a short abstract.
b) Design: your introduction, design considerations, E/R diagram, database objects.
c) Implementation: SQL source codes with your descriptive comments.
d) Testing: testcases with your descriptive comments.
e) Conclusions: your final analysis and remarks.
f) Appendix: screenshots from SQL Server for codes, testing, etc.
Please review the grading rubric for the project report on Blackboard! All reports that adhere
to basic standards of grammar and spelling, and section/page formatting will have a base
score of 15 points.
