The Database Schema

As mentioned earlier, the development team has decided to use the existing Microsoft SQL 2000 database with some modifications. Figure 15.7 shows the database schema for the RecruitmentApp database.

Figure 15.7: The schema for the RecruitmentApp database.

The database schema includes the following four tables:

• Applicants. This table contains applicant details, such as an applicant's ID, name, address, contact number, e-mail address, job position applied for, qualifications, experience, skills, date of applying, and date of joining.

• Skills. This table lists standard skills as defined by the HR department. It mentions skill details, such as a skill ID and its description.

• Applicants_Skills. This table acts as a junction table, containing a link between applicants and skills, and it contains fields such as Applicant ID and Skill ID.

• Logins. This table contains login details, such as UserID and Password.

Now let's look at the relationship between these tables. Every applicant can have multiple skills. This information is stored in a separate table, Applicants_Skills, which acts as a junction table between the two tables, Applicants and Skills. In the Applicants_Skills table, ApplicantID and SkillID are foreign keys that reference the Applicants table and the Skills table, respectively. Logins is an independent table.

Database Structure

The personal details of the applicants are stored in the Applicants table, shown in Figure 15.8.

Figure 15.8: The design of the Applicants table.

The Applicants table contains ApplicantID as the primary key column, and its datatype is AutoNumber. The other fields in this table are ApplicantName, Address, ContactNo, EmailAddress, AppliedFor, ApplicationDate, DateOfJoining, QualificationDetails, and ExperienceYears.

Smart Stores identifies a set of skills as standard skills. These skills are stored in the Skills table. When entering information about an applicant, users store information about the skills of a particular applicant by selecting them from the Skills table. The structure of the Skills table is shown in Figure 15.9. It contains two columns. SkillID is the primary key column. Description is the other column.

Figure 15.9: The design of the Skills table.

The Applicants_Skills table stores the skill details of applicants. It stores a separate record for each skill that an applicant possesses. It also stores the applicant's number of years of experience in that particular skill. The structure of the Applicants_Skills table is shown in Figure 15.10. It contains Applicant ID and SkillID as primary key columns.

Figure 15.10: The design of the Applicants_Skills table.

The database also contains another table called Logins. The database administrator of Smart Stores will manage this table. The fields in this table are UserID and Password. The structure of the Logins table is shown in Figure 15.11. It contains UserID as the primary key column.

Figure 15.11: The design of the Logins table.