For this project you can pair up with another student to complete the final project. You are not
required to work with another student but can do so if you choose. If you are looking for
another person to work with, you might want to post on the course discussion board. If you
decide to work with another person, that is your decision and your responsibility. I will not
arbitrate team difficulties so if someone drops the ball, the other person has to pick it up. You
do not have to have teams approved ahead of time so I will only know who gets credit by what
is turned in by whom.
Your team has been tasked with creating a database to handle the online job application
system for a small business. These are the parameters for the database:
• Each applicant and manager must login to the system from the same login page. (HINT:
A supertype!)
• An applicant will fill out a form about themselves.
• An applicant can then apply for many jobs and add additional information about their
skills or qualifications specifically for that job.
• Each job is assigned to one of six departments in the company: sales, logistics,
marketing, technology, accounting, and purchasing.
• Each department is overseen by a manager who will be able to review the applications
that have been submitted for his/her department.
Each applicant must complete the following on their application:
First Name, Last Name, Address, City, State, Zip, Email, Contact Number. Work history
including job title, start date, end date, description of duties, and reason for leaving.
Each employee must indicate the highest level of education completed and should have
the ability to enter degrees and certifications earned and the school where they were
earned.
For each actual job applied for, they can add additional information specific to that job.
For example, if a job requires C# experience, the applicant could add a note to that
specific application about their experience with C#.
To post a job, a manager must provide the following:
Job title, job description, department, required experience, preferred experience,
employment start date, salary, beginning review date, and closing date.
For your project, you must submit the following:
• A working database file that implements your database design. I recommend Access
but you can use SQL Server Express or MySQL or Oracle Express if you are more familiar
with those products. Other DB products, probably not, but ask me if you really want to
use something else. All students in the class have access to Access through Azure where
you downloaded Visio earlier in the semester. Each table must have at least 5 rows of
data.
• Table shorthand including primary key identification
• ERD for system
• Discussion of why/how all of your tables have been normalized. Address each table
individually.
• Data dictionary for system.
• Standard (non-Access specific) SQL commands to do the following. If you copy your SQL
from Access and it contains things that are specific to Access, you will not receive ANY
credit for this section. Write the code outside of your database!
o Create a list of applications for a specific job (you supply the specific job number
from your database) that includes the applicant’s ID, first name, last, name and
contact number.
o Create a list of applications that were submitted after 3/14/2020.
o Create a list of jobs, including job ID and job title, that are currently open in the
marketing department.
o Create a list of jobs that a specific applicant has applied for, listing job ID, Job
title, and closing date. You can choose the ID of a specific applicant in your
database for this one.
o Create a list of all open jobs with jobID, job title, closing date, and manager
assigned to review.
• Any business requirements that have played a part in the design of your database.
Zip all files and submit by the due date. If you are working in pairs, both students need to
submit the assignment and you also must make sure that both students’ names are on all of the
documentation.
