BUS5BID - Assignment 2: Choice Appointments Database Design

Desklib provides solved assignments and past papers to help students succeed.

BUS5BID - ASSIGNMENT 2

Table of Contents
Introduction................................................................................................................................2
B.................................................................................................................................................3
D.................................................................................................................................................7
E)................................................................................................................................................7
Conclusion..................................................................................................................................9
References................................................................................................................................10
Table of Figures
Figure 1: ERD Diagram.............................................................................................................3
Figure 2: Accounts Table...........................................................................................................5
Figure 3: Applicants Result Table..............................................................................................5
Figure 4: Applicants Table.........................................................................................................5
Figure 5: Client Company Table................................................................................................6
Figure 6: Contracts Table...........................................................................................................6
Figure 7: Jobs Table...................................................................................................................6
Figure 8: Payed Wage Table......................................................................................................6
Figure 9: Applicants Result Table..............................................................................................8
Figure 10: Report 1 of Salary Details........................................................................................8
Figure 11: Report 2 of Salary Details........................................................................................8
Figure 12: Report Available Jobs...............................................................................................9
Figure 13: Report Applicants.....................................................................................................9
2

Introduction
Choice Appointments is the recruitment agency who has many office networks who provide
the various job opportunities in the private and public sectors. So, the organisation was to
divided their process with their working requirements. It is beneficial to control all activities
regarding to it. For managing the complete data, the organisation wants to create database
where they can make records or the job applicants, contractors, job consultants and many
more. In the implementation of database, the data dictionary will be created with the
relational database in MS Access.
3

B.
Figure 1: ERD Diagram
The above shown Entity-Relationship diagram explains the scenario of the mediator company
that actually helps the unemployed people to get the jobs in the companies where the
vacancies are available (Goelman, D. and Dietrich, S.W., 2018). The mediator part gets the
payment that has to be paid to the employee, they cut-off their commissions and the taxes,
then pass the remaining payment to the employee. The ERD explains the whole scenario
really well. The diagram contains 8 entities; each of them explains the importance of it. The
entities present are named as Consultant, Contractors, Applicants, Jobs, PayedWage,
Accounts, ClientCompany and ApplicantResult. Each of the entities also contains an attribute
that uniquely defines the data. Relationships are also being mentioned properly in the
diagram. Foreign keys are created based on the mentioned relationships.
Entity Consultant
 The Consultant entity contains the ConsultantID as a Primary Key in order to uniquely
identify the information of each of the Consultant.
 The other attributes are Name, Contactno, EmailID, ApplicantAssociation,
MonthlyTarget, and Address.
 The Consultant entity makes one to ∞ a relationship with Applicants entity.
Entity Contractors
 The Contractor entity contains the ContractorID as a Primary Key in order to uniquely
identify the information of each of the Contractor.
 The other attributes are ApplicantID, JobID, JoiningDate, and DecidedSalary.
 The Contractor entity makes ∞ to one relationship with Applicants entity. The Contractor
entity makes ∞ to one relationship with Jobs entity. The Contractor entity makes one to
one relationship with PayedWage and Account entities.
4

 JobID and ApplicantID are the foreign keys defined in this entity.
Entity Jobs
 The Jobs entity contains the JobID as a Primary Key in order to uniquely identify the
information of each of the Job.
 The other attributes are CmpnyID, CriteriaDesc, SalaryPerhr, and Vacancy.
 The Job entity makes one to ∞ a relationship with Applicants entity. The Job entity makes
one to ∞ a relationship with Contractors entity.
 CmpnyID is the foreign keys defined in this entity.
Entity Applicants
 The Applicants entity contains the ApplicantID as a Primary Key in order to uniquely
identify the information of each of the Applicants.
 The other attributes are ApplicantName, Qualification, Experience, AppliedJobID,
ConsultantID, Cntctno and Address.
 The Applicant entity makes one to one relationship with ApplicantsResult entity. The
Applicant entity makes one to ∞ a relationship with Contractors, Jobs and Consultant
entity.
 AppliedJobID and ConsultantID are the foreign keys defined in this entity.
Entity Accounts
 The attributes are ContractorID, Accountno, BankName, and BranchAddress.
 The Accounts entity makes one to one relationship with Contractors entity.
 ContractorID is the foreign key defined in this entity.
Entity PayedWage
 The attributes are ContractorID, CompanyID, AmountReceive, ChoiceAppnt, Tax and
TotalPayedWage.
 The PayedWage entity makes one to one relationship with Contractors entity. The
PayedWage entity makes ∞ to one relationship with ClientCompany entity.
 ContractorID and CompanyID are the foreign keys defined in this entity.
Entity ClientCompany
 The ClientCompany entity contains the CompanyID as a Primary Key in order to
uniquely identify the information of each of the ClientCompany.
 The attributes are CompanyName, Contactno, EmailID, and Address.
 The ClientCompany entity makes one to ∞ a relationship with PayedWage entity.
5

Entity ApplicantResult
 The ApplicantResult entity contains the ApplicantID as a Primary Key in order to
uniquely identify the information of each of the ClientCompany (Olaleye, K., 2017).
 The attributes are SpellingTestMarks, PersonalInterviewMarks, TechnicalTestMarks, and
Result.
 The ApplicantResult entity makes one to one relationship with Applicants entity.
C.
Figure 2: Accounts Table
The above-shown table is the Accounts table. The attributes present are ContractorID,
Accountno, BankName, and BranchAddress. Here, the data is populated inside this table
where ContractorID is considered as the Foreign Key.
Figure 3: Applicants Result Table
The above-shown table is the ApplicantsResult table. The attributes are SpellingTestMarks,
PersonalInterviewMarks, TechnicalTestMarks, and Result (Dahab et al., 2018). Here, the data
is populated inside this table where ApplicantID is considered as the Foreign Key.
Figure 4: Applicants Table
The above-shown table is the Applicants table. The attributes are ApplicantName,
Qualification, Experience, AppliedJobID, ConsultantID, Cntctno and Address.
Here, the data is populated inside this table where ApplicantID is considered as the Primary
Key.
6

Figure 5: Client Company Table
The above-shown table is the ClientCompany table. The ClientCompany entity contains the
CompanyID as a Primary Key. The attributes are CompanyName, Contactno, EmailID, and
Address. Here, the data is populated inside this table where CompanyID is considered as the
Primary Key.
The above-shown table is the Consultant table. The Consultant entity contains the
ConsultantID as a Primary Key. The other attributes are Name, Contactno, EmailID,
ApplicantAssociation, MonthlyTarget, and Address. Here, the data is populated inside this
table where ConsultantID is considered as the Primary Key.
Figure 6: Contracts Table
The above-shown table is the Contractors table. The Contractor entity contains the
ContractorID as a Primary Key (Milster, T.D., 2017). The other attributes are ApplicantID,
JobID, JoiningDate, and DecidedSalary. Here, the data is populated inside this table where
ContractorID is considered as the Primary Key. ApplicantID and JobID are the foreign keys
defined.
Figure 7: Jobs Table
The above-shown table is the Jobs table. The Jobs entity contains the JobID as a Primary
Key. The other attributes are CmpnyID, CriteriaDesc, SalaryPerhr, and Vacancy.
Here, the data is populated inside this table where JobID is considered as the Primary Key.
CmpnyID is the foreign key defined.
Figure 8: Payed Wage Table
7

The above-shown table is the PayedWage table. The present attributes are ContractorID,
CompanyID, AmountReceive, ChoiceAppnt, Tax and TotalPayedWage. Here, the data is
populated inside this table where ContractorID and CompanyID are considered as the Foreign
Keys.
8

D.
Figure 9: Applicants Result Table
The first query is used to get the data of the ApplicantResult entity. The data that is being
extracted out is done by using the simple select query. Using ‘*’ with the select keyword, all
the data of the table ApplicantResult is extracted.
E)
Salary Details
Figure 10: Report 1 of Salary Details
This report is created by using the Ms Access in which the Salary details are mentioned. In
this report, the columns are ContractorD, CompanyID, Amount Receive, ChoiceAppnt, tax,
and totalPayedwage (Kale et al., 2016). All these are combined and this report is created.
Within these columns, there are some values that are inserted and the result is found out.
Figure 11: Report 2 of Salary Details
This is another report that contains similar columns but has different values that are entered.
These are contactorD in which the data entered is C111, CompanyID is CMP3,
Amountreceived is $80.00, ChoiceAppnt is $5.00, tax is $5.00 and by this total amount paid
is $70.00.
9

Figure 12: Report Available Jobs
Another report is an available job in which the other columns are JobID, CmpnyID,
Designation, CriteriaDesc, Salaryperhr, and Vacancy. These attributes have different values
that are inserted and on combining them, a report is created which is shown above.
Figure 13: Report Applicants
The report applicants are created by integrating different columns. These columns are named
as ApplicantID, ApplicantName, Qualification, and Experience and ApplicantjobId. These
attributes have different values that are inserted. There are two entries that are shown in the
above screenshot. AP12 and AP13 are two different applicants with different names, a
qualification which is an MBA for both of them, different experience and AppliedJobId.
These are the entries that are entered.
10

Conclusion
In the overall assessment, the database was implemented for the job recruiter agency named
as “Choice Appointments”. In the first part, the data dictionary was created with the help of
normalised Entity Relationship Diagram. In the implementation of data dictionary, the
attributes and the entities were included with proper description and their field formats. In
next task, the database is implemented with the help of data dictionary. In the next part, SQL
Queries is created manually by inserting data in Microsoft Access. Screenshots were also
attached of the queries generated including information added in each table of database.
11

References
 Goelman, D. and Dietrich, S.W., 2018, February. A Visual introduction to conceptual
database design for all. In Proceedings of the 49th ACM Technical Symposium on
Computer Science Education (pp. 320-325). ACM.
 Olaleye, K., 2017. DESIGN AND IMPLEMENTATION OF A WEB-BASED
INFORMATION AND REGISTRATION SYSTEM: A CASE STUDY OF
EDUCATIONAL CONSULTING CENTER.
 Dahab, M.Y., Alnofaie, S. and Kamel, M., 2018. A tutorial on information retrieval using
query expansion. In Intelligent Natural Language Processing: Trends and
Applications (pp. 761-776). Springer, Cham.
 Milster, T.D., 2017, August. Technology tutorial on optical data storage. In Laser Beam
Propagation in the Atmosphere (Vol. 10319, p. 1031902). International Society for
Optics and Photonics.
 Kale, N.S., Haug, K., Conesa, P., Jayseelan, K., Moreno, P., Rocca‐Serra, P., Nainala,
V.C., Spicer, R.A., Williams, M., Li, X. and Salek, R.M., 2016. MetaboLights: An Open‐
Access Database Repository for Metabolomics Data. Current protocols in
bioinformatics, 53(1), pp.14-13.
12