Unit 04: Database Design and Development

Database Service Report
Database Design and Development

Page 2
ASSIGNMENT 1 FRONT SHEET
Qualification TEC Level 5 HND Diploma in Computing
Unit number and title Unit 04: Database Design & Development
Submission date Date Received 1st submission
Re-submission Date Date Received 2nd submission
Student Name Le Vo Hong Ngoc Student ID TCS18005
Class GCS0805_PPT Assessor name Nguyen Van Son
Student declaration
I certify that the assignment submission is entirely my own work and I fully understand the consequences of plagiarism. I understand that
making a false declaration is a form of malpractice.
Student’s signature
Grading grid
P1 M1 D1

 Summative Feedback:  Resubmission Feedback:
Grade: Assessor Signature: Date:
Signature & Date:

ASSIGNMENT 1 BRIEF
Qualification BTEC Level 5 HND Diploma in Computing
Unit number Unit 04: Database Design & Development
Assignment title
Academic Year
Unit Tutor
Issue date Submission date
IV name and date
Submission Format:
Format: This assignment is an Individual assignment and specifically including 1 document:
You must use font Calibri size 12, set number of the pages and use multiple line spacing at
1.3. Margins must be: left: 1.25 cm; right: 1 cm; top: 1 cm and bottom: 1 cm. The reference
follows Harvard referencing system. The recommended word limit is 2.000-2.500 words. You
will not be penalized for exceeding the total word limit. The cover page of the report has to
be the Assignment front sheet 1.
Submission Students are compulsory to submit the assignment in due date and in a way requested by
the Tutors. The form of submission will be a soft copy posted on
http://cms.greenwich.edu.vn/
Note: The Assignment must be your own work, and not copied by or from another student or from
books etc. If you use ideas, quotes or data (such as diagrams) from books, journals or other sources, you
must reference your sources, using the Harvard style. Make sure that you know how to reference properly,

You are employed as a Database Developer for a large IT consultancy company. The company has been
approached by FPT university which is expanding due to the growth of the number of students. FPT is
currently facing difficulties in dealing with managing the university. It decided to develop several academic
systems to manage the university easier including: Online Library system, Student Grading System,
Attendance System, CMS System, Scheduling System, Enrolment Systems, and so on.
You are tasked to select one of those systems to develop database for FPT university. Your tasks are to:
Work with FPT to find out about current requirements for each system
Analyze the requirements and produce clear statements of user and system requirements.
Design a relational database system using appropriate design tools and techniques
Develop a fully functional relational database system, based on an existing system design.
Test the system against user and system requirements.
Produce technical and user documentation
Part 1 (Assignment 1)
Before you start the development process, your manager has asked you to produce a report for the CEO
of FPT, containing:
1. Clear statements of user and system requirements. The system must have at least 2 user roles, including
business processes and statistical reports for FPT university managers.
2. The design of the relational database system using appropriate design tools and techniques. It should
contain at least four interrelated tables.
You would prefer to produce a more detailed document, so you will produce a comprehensive design for a
fully functional system which will include interface and output designs, data validations and cover data
normalization.
Your manager would like on the report your assessment of the effectiveness of the design in relation to user
and system requirements.
Part 2 (Assignment 2)
Once the designs have been accepted by your manager you have been asked to:

 Assessing whether meaningful data has been extracted through the use of query tools to produce
appropriate management information.
 Evaluating the effectiveness of the database solution in relation to user and system requirements,
and suggest improvements.
3. Once the system has been developed, you will test the system and your manager will complete a witness
statement indicating how your tests are performing against user and system requirements.
Besides, you will produce a brief report assessing the effectiveness of the testing, including an explanation
of the choice of test data used.
4. Lastly you will produce technical and user documentation which will be given to the company.
You want to provide some graphical representations for ease of reference in the technical guide, so you
have decided to produce a technical and user documentation for a fully functional system, including
diagrams showing movement of data through the system, and flowcharts describing how the system
works.

Learning Outcomes and Assessment Criteria
Pass Merit Distinction
LO1 Use an appropriate design tool to design a relational database system for a substantial
problem
P1 Design a relational
database system using
appropriate design tools
and techniques, containing
at least four interrelated
tables, with clear
statements of user and
system requirements.
M1 Produce a comprehensive
design for a fully functional
system which includes
interface and output designs,
data validations and data
normalisation.
D1 Assess the effectiveness of
the design in relation to user and
system requirements.

TABLE CONTENT
1. Introduction. ................................................................................................................................ 10
1.1. Overview ............................................................................................................................... 10
1.2. System description ............................................................................................................... 10
1.3. Problem definition ............................................................................................................... 11
1.3.1. Goals ............................................................................................................................... 11
1.3.2. Objectives....................................................................................................................... 11
1.3.3. Critical success factors ................................................................................................... 11
1.3.4. Organization chart and responsibilities ........................................................................ 11
1.4. Feasibility study: ................................................................................................................... 12
1.5. Human resources management general rules: ................................................................... 12
1.6. literature review: .................................................................................................................. 13
1.6.1. Introduction ................................................................................................................... 13
1.6.2. Human resource information systems types: .............................................................. 13
1.6.2.1. Employee information systems .............................................................................. 13
1.6.2.2. Position control systems......................................................................................... 14
1.6.2.3. Applicant selection and placement information systems ..................................... 14
1.6.2.4. Performance management information systems.................................................. 14
1.6.2.5. FPT University reporting and compliance information systems ........................... 14
2. System analysis ........................................................................................................................... 15
2.1. Entity Relationship Diagram (ERD) ...................................................................................... 15
2.1.1. Human Resource Management System entities and their attributes: ....................... 15

2.1.6.3. Departments ........................................................................................................... 20
2.1.6.4. Trainings .................................................................................................................. 20
2.1.6.5. Vacations ................................................................................................................. 21
2.1.6.6. Salaries .................................................................................................................... 21
2.1.6.7. Attendance .............................................................................................................. 22
2.1.6.8. Evaluations .............................................................................................................. 22
2.1.6.9. Employee_Vacations .............................................................................................. 22

1. Introduction.
1.1. Overview
For more than a century now, human resource management, as a discipline and practice in the
management of people in an organization, has evolved and developed into different areas.
These disciplines and practices have gone through a process of trial and error, theory building andtesting
of various concepts by practicing managers and academics. The underlying forces behind the evolution
and development of human resource management have been (and still are) mainly environmental, and
the quest for knowledge of better ways of acquiring and utilizing labor.
The changing organizational environment in the marketplace pushed managers to improve efficiency in
the production and service delivery processes by increasing their ability to use thebest practices of
people management at the time. That is, employee management techniques ormethods that would
improve production, reduce service delivery costs, and at the same time ensure sustained availability of
competent staff in the organization.
This study is devoted to providing organizations with a human resource management system ofthe
evolution and development of human resource management and the way it works and influences people
management in contemporary organizations.
1.2. System description
Human resources management system (HRMS) was created to include the best practices
for service human resources departments within the company and is the work of all employees
department.
The target group of the system that serves the human resource procedures is special for the employees
and managers.
This system is also classified according to staff branches and departments as it is classified according to
the work of multiple systems; In addition to that it is organized in terms of personnel (promotions -
bonuses– trainings- Benefit).
This system of work area makes it easy to enter movements daily work , also organizes
holidays, whether in the day or hours , Emission a monthly report full and detailed and accurate for all
staff movements within a specified period (work required -work actual ).

- The system flexible to add, delete, edit for employee record and all information belong tohuman
resources management.
1.3. Problem definition
1.3.1. Goals
The aim of this study is to build the human resources management system to increase the efficiency
of the performance and the safety of the decisions taken and the advancement of the institution
The researcher will study the human resource management system requirements used in any
company in order to build a human resource management system efficiently.
1.3.2. Objectives
We will build a web-based human resource management system (HRMS) to increase the
performance and organization of the entire range of human resources management services by
developing these sections:
- Rules section.
- Compute the net salary after adding salary, promotions percentage and loans.
- Detailed reports section.
- Direct email communication.
- Statistical data charts.
- Training section.
- Vacations section.
- Salary.
- Attendance.
1.3.3. Critical success factors
1. Increased performance of the entire range of human resources management services.
2. Provide a single point of contact for customers to take human resources management system
(HRMS) procedures or to request any clarification.
3. Expand the coverage of the human resources management system (HRMS) to include the entire
human resources services, including human resources management system (HRMS) planning,
performance management and Internet recruitment systems.

Table (1.1): Organization chart and responsibilities.
Question Objective Activity Target Audiences
How to control on
the access of the
system?
Making saved
authentication to
the system
Developing Rules
section
HRMS give employee rules to
access any par of the system
How to Compute
the net Salary for
the employees?
To compute the
net salary for
everyone on the
system
Computing net Salary
after adding Salary,
promotions
percentage and loans.
HRMS compute net salary for all
employees on the system.
How to get all
Detailed information
about any part of the
system?
To get detailed
reports about any
part of the system
Developing Detailed
Reports section
HRMS will extract all data on the
system
How to make
direct communicate
with the employees?
To communicate
with all employees
quickly
Managing
communication
with whole system
members.
Employees who are members on
human resource management
system (HRMS).
How to realize the
change of data on
the system?
To represent all
data on thesystem
Representing data on
the system in graphs
and statistics
All data on the system stored on
database.
1.4. Feasibility study:
Information has been collected by reference to sources, written references and informationpublished in
scientific, research and literary sites on the Internet
1.5. Human resources management general rules:
The human resources management team suggests to the management team how to strategically
manage people as business resources. This includes managing recruiting and hiring employees,
coordinating employee benefits and suggesting employee training and development strategies. In this
way, general rules related to human resources management system.

4. Since the usual accounting definition of an asset involves the right to receive economic
benefits in the future, human assets are appropriately classified as accounting assets.
5. It is theoretically possible to identify and measure human resource cost and benefits within
an organization.
6. Information with respect to human resource costs and benefits should be useful in the
process of planning, controlling, evaluating and predicting organizational performance.
1.6. literature review:
1.6.1. Introduction
Human resources are those who make up the workforce of some particular organization, economy or business
sector. The "human capital" term may be understood as a synonym to the "human resources’" one. But at the
same time, “human capital” one usually refers to a narrower view such as the knowledge the individuals
embody as well as the economic growth. Some other terms that sometimes may be used instead of the
“human resources” one include "talent", "manpower", "labor", "people" or "personnel".
Any human-resources department of some organization is expected to perform human resource management,
overseeing different aspects of employment (e.g., compliance with labor law and some of the employment
standards), some aspects of dismissal and recruitment, as well as an administration of the employees’ benefits.
Any human resources manager is known to be in charge for determining the work-related needs of the staff,
using temporary staff or hiring employees in order to fill such needs, recruiting and training the best
employees, supervise their work, managing employees’ relations, payrolls, benefits and compensations,
preparing employees’ personal policies and records, ensuring high performance, ensuring equal opportunities
for all the employees, dealing with discrimination and other performance issues. Ensuring that human
resources practices conform to the regulations and pushing the employees' motivation so they achieve more
within their organization, human resources managers might find the concept draw pro software a useful tool
for making the needed human resources related drawings.
The human resources flowcharts solution can be used while creating the human resources related drawings in
the concept draw pro diagramming and drawing software, providing with a comprehensive collection of
human resources themed icons, connectors, and images from the available stencil libraries, helping illustrating
flowcharts, info graphics and workflow diagrams.
Using this solution for mapping the human resources related management processes on every phase of human

The human resource department must maintain information on each of the organization’s
employees for a variety of decision and reporting purposes. One part of this employee information
system is a set of human resource profile records.
An employee profile usually contains personal and organization-related information, such as name,
address, sex, minority status, marital status, citizenship, years of service or seniority data,
education and training, previous experience, employment history within the organization, salary
rate, salary or wage grade, and retirement and health plan choices.
The employee inventory may also contain data about employee preferences for geographical
locations and work shifts.
Another part of an employee is an employee skills inventory which contains information about
every employee, such as work experience, work preferences, test scores, interests, and special
skills or proficiencies
1.6.2.2. Position control systems
A job is usually defined as a group of identical positions. Every position consists of tasks
performed by one worker. The purpose of the position control system is to identify each position
in the organization, the job title within which the position is classified, and the employee currently
assigned to the position.
Reference to the position control system allows a human resource manager to identify the details
about unfilled positions.
1.6.2.3. Applicant selection and placement information systems
After jobs and the employee requirements for those jobs have been identified and after a suitable
pool of job candidates has been recruited, the candidates must be screened, evaluated, selected,
and placed in the positions that are open.
The primary purpose of the applicant selection and placement information system is to assist
human resource staff in these tasks.
1.6.2.4. Performance management information systems
Performance management information systems include performance appraisal data and
productivity information data. Performance management information systems data is frequently
used as evidence in employee grievance matters.
Careful documentation of employee performance was measured and reported is critical to

2. System analysis
2.1. Entity Relationship Diagram (ERD)
The entity relationship (ER) diagram represents the model of human resource management
system entity. The entity-relationship diagram of human resource management system shows all the
visual instrument of database tables and the relations between trainings, departments,employee, salary
etc. it used structure data and to define the relationships between structured data groups of human
resource management system functionalities. The main entities of the human resource management
system are employee, trainings, departments, salary and salary.
2.1.1. Human Resource Management System entities and their attributes:
o Employee Entity: Attributes of Employee are employee_id, employee_name,
employee_mobile, employee_email, employee_username, employee_password,
employee_address.
o Trainings Entity: Attributes of Trainings are training_id, training_employee_id,
training_registration, training_name, training_type, training_year, training_description
o Vacation Entity: Attributes of vacations are vacation_id, vacation_employee_id,
vacation_from_date, vacation_type, vacation_to_date
o Departments Entity: Attributes of Departments are department_id, department_name,
department_type, department_description.
o Salary Entity: Attributes of Salary are salary_id, salary_employee_id, salary_amount,
salary_total, salary_type, salary_description
o Attendance Entity: Attributes of Attendance are attendance_id, attendance_employee_id,
attendance_date
o Evaluation Entity: Attributes of Evaluation are eval_id, employee_id, eval_value,notes
2.1.2. Description of Human Resource Management System Database
As shown in figure (2.13) we notice that human resources management system database contains
seven table and the detailed relations between them can be described as:
o The details of employee is store into the employee tables respective with all tables

Figure (2.13) ER diagram for Human Resource Management System
2.1.3. Use case diagram
As shown in figure (2.14) administrator of the proposed system and the employee have various
activities shared with login activity.

2.1.4. Activity diagram
As shown in figure (2.15) administrator of the proposed system can create vacations for employee
who is a member of the proposed system. If there is an error the system will notify the manager

2.1.5. Logical Design ERD

2.1.6. Database design
This section describes the eight tables that are linked to our project. These six tables is described in
the following point
o Logins.
o Employees.
o Trainings
o Vacations.
o Attendances.
o Salaries.
o Department.
o Employee_Vacations
o Evaluations
2.1.6.1. Logins
The admin table stores the information about human resources manager profile. All the human
resources managers who will manage the system will be saved in this table.
Table 2.1 Logins
Field name Data type Allow Nulls Description
Username Varchar(50) Primary key, Username login
[Password] Varchar(50) x Password Login
[Role] Bit x decentralization
Employee_ID Int x Employee code Foreign key

2.1.6.2. Employees
The table employee stores the information about employee profile. The entire employees who
are added by the manager in the system will be saved in this table.
Table 2.2 Employee
2.1.6.3. Departments
The table department stores all departments requested through the system will issue. All of
these department data will be in this table.
Table 2.3 Department
2.1.6.4. Trainings
The table training stores all trainings which are added by the manager. All of these trainings
data will be in this table.
Table 2.4 Trainings
Field name Data type Allow Nulls Description
Employee_ id Int Primary key, Username login
Employee_ password Varchar (20) x Password of Employee
Employee_email Varchar (50) x Email of Employee
Employee_name Varchar (50) x Full name of Employee
Employee_ address Varchar (50) x Address of Employee
Employee_ mobile Varchar (11) x Phone of Employee
Department_ID Int x Department code, Foreign key
Field name Data type Allow Nulls Description
Department_ID Int Code Department , Primary key
Department_title Varchar (20) x Name Department

2.1.6.5. Vacations
The table vacation stores all vacations which are added by employees through the system. All
of these vacations data will be in this table.
Table 2.5 vacations
2.1.6.6. Salaries
Table 3.6 salary
Field name Data type Allow Nulls Description
Vacation_ id Int Code of vacation
Vacation _title Varchar (11) x Name of vacation
Vacation _from_date Date x Start day
Vacation_to_date Date x End day
Employee_id Int x Employee code Foreign key
Field name Data type Allow Nulls Description
id Int Code of Salary
Salary float x Salary of employee
Bonus float x Money bonus
Loan float x Money Loan
Last_update Date x Last_update
Employee_id Int x Employee code Foreign key

2.1.6.7. Attendance
The table attendance stores all attendance data belong to employee through the system added
by him. All of these attendance data will be in this table .
Table 3.7 attendance
2.1.6.8. Evaluations
Table 3.8 Evaluation
2.1.6.9. Employee_Vacations
Table 3.8 Evaluation
Field name Data type Allow Nulls Description
id Int Code of Attendace
Att_type Varchar (50) x Type of Attendace
Att_time_date Date x Start of Attendace
Employee_id Int x Employee code Foreign key
Field name Data type Allow Nulls Description
Eval_id Int Code of Valuation
User_id Int x Employee code Foreign key
Eval_value Float x Start of Attendace
Notes Text x Description of valuation
Field name Data type Allow Nulls Description

REFERENCES
[1] FARNHAM, D. & J. PIMLOTT (1992), Understanding industrial relations. London.
[2] ARMSTRONG, M. (1995), A Handbook of Personnel Management Practice. London.
[3] Myriaaison (2014), the waterfall model explained.
[4] James Rumbaugh, et al: The Unified Modeling Language Reference Manual, Addison-Wesley
[5]freeprojectz, [online] Available at: https://www.freeprojectz.com/dfd/human-resource-management-
systemdataflow-diagram [Accessed 24 October. 2020]
[6] freeprojectz,[online] Available at: https://www.freeprojectz.com/entity-relationship/human-resource-
managementsystem-er-diagram [Accessed 24 October. 2020]
[7] creately,[online] Available at https://creately.com/diagram/example/h88w3c102/HR/Management/System
[Accessed 24 October. 2020]
[8] Why to Learn SQL? , [online] Available at: https://www.tutorialspoint.com/sql/index.htm [Accessed 24 October.
2020]
[9] Learning SQL, [online] Available at: https://www.w3schools.com/sql/default.asp [Accessed 24 October. 2020].
[10] Semtek.com.vn, (2019). Tìm hiểu 5 phần mềm web server phổ biến nhất hiện nay, [online] Available at:
https://www.semtek.com.vn/web-server-2/ [Accessed 24 October. 2020].
[11] azure.microsoft.com powerful (August 12, 2019). Six ways we’re making Azure reservations even more, [online]
Available at: https://azure.microsoft.com/en-us/blog/6-ways-were-making-azure-reservations-even-more-powerful/
[Accessed 24 October. 2020].