Employee Management System ISYS6169 – Database System final report

By:

  • Rio Adi Nugraha (2201798856)
  • Hengky Sanjaya (2201852492)
  • Ryan Rusli (2201832446)
  • Naufal Basyah (2201840334)

Introduction

Problem Descriptions:

In order for a company to function it must have a system to organize its own resources. One resource that is crucial yet sometimes overlooked is human resources. A company must at all times have access to all the required data related to an employee. The required data includes but not limited their wages, leave allowances, work ratings, contract type, etc. in today’s era HR becomes a given when talking about any workplace. 

In the interview we conducted we have surmised that a significant number of HR managers still have all their data in excel. This has its own share of problems such as field organization, scalability, and data integrity. We asked a number of questions such as “what do you use to store data?” and “What do you think can be improved?”. The answers we obtained as well as research results led to our conclusion that database might be a better means of data storage for this matter. Hence we have come up with the following problem statement:

How can the usage of databases improve the efficiency of an HR department in a company?

The above is the end goal of the project where everything is based on. Deliberations towards UI design, transaction requirements all have the above basis for the overall design of it.

Team members and Roles:

Rio Adi Nugraha: Database Designer, Database implementation, Interviewer 

My job is to interview the target audience and then design the database to suit the needs of the company. I was given the excel spreadsheet with limited data. My job is to convert this excel spreadsheet into a cohesive relational database. I contribute with implementing the database into a server. Creating the table and connecting the foreign key is also part of my responsibility. After the product is complete and in between it is also my responsibility to communicate with the client to see if the product suits their preferences. 

Hengky Sanjaya: Database Designer, Programmer

I am responsible to design the outline of database tables with team. Analyze the data from the company given in excel format and translate into the relational tables. Create and build the application in desktop platform with technologies of C# as main programming language and MySQL as database storage. Responsible to build features of application such as login, register employees, entry employee data, report, import data, etc.

Ryan Rusli: Database Designer, Programmer

I am responsible for designing a majority of the class models of the program and for most of the data management interface. I also contributed in the design of the database table with the other group members. I also handled most of the data insertion, retrieval, update and deletion functions and  interactions with the database. I also created the employee management page which manages email, phone, address and job history.

Naufal F. Basyah: Report Check and Miscellaneous 

I am mostly responsible for the report checks and details that need to be made i.e. ERD and relation description. I also contribute in a smaller scale in miscellaneous task as an extra pair of hands.

Database Design

Entity-Relationship Diagram:

Relations Description:

TABLE `Bank`

 (

  `ID` int(50) NOT NULL AUTO_INCREMENT,

  `Name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`ID`)

)

TABLE `Branch`

 (

  `ID` int(50) NOT NULL AUTO_INCREMENT,

  `Name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`ID`)

 TABLE `ContractType` 

(

  `ID` int(50) NOT NULL AUTO_INCREMENT,

  `Name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`ID`)

TABLE `Department`

 (

  `ID` int(50) NOT NULL AUTO_INCREMENT,

  `Name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`ID`)

)

TABLE `Employee`

 (

  `NIK` int(40) NOT NULL,

  `Fullname` varchar(255) DEFAULT NULL,

  `Nickname` varchar(255) DEFAULT NULL,

  `KTP` varchar(50) DEFAULT NULL,

  `Jamsostek` varchar(255) DEFAULT NULL,

  `BankID` int(10) DEFAULT NULL,

  `Rekening` varchar(50) DEFAULT NULL,

  `NPWP` varchar(50) DEFAULT NULL,

  `StatusPajak` varchar(15) DEFAULT NULL,

  `DOB` date DEFAULT NULL,

  `Gender` varchar(1) DEFAULT NULL,

  `Religion` varchar(50) DEFAULT NULL,

  `MaritalStatus` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`NIK`),

  KEY `BankID` (`BankID`),

  CONSTRAINT `Employee_ibfk_1` FOREIGN KEY (`BankID`) REFERENCES `Bank` (`ID`)

TABLE `EmployeeEducation`

 (

  `ID` int(50) NOT NULL AUTO_INCREMENT,

  `NIK` int(40) DEFAULT NULL,

  `EducationLevel` varchar(255) DEFAULT NULL,

  `Institution` varchar(255) DEFAULT NULL,

  `Major` varchar(255) DEFAULT NULL,

  `GraduationYear` date DEFAULT NULL,

  `Score` float DEFAULT NULL,

  PRIMARY KEY (`ID`),

  KEY `NIK` (`NIK`),

  CONSTRAINT `EmployeeEducation_ibfk_1` FOREIGN KEY (`NIK`) REFERENCES `Employee` (`NIK`),

  CONSTRAINT `EmployeeEducation_ibfk_2` FOREIGN KEY (`NIK`) REFERENCES `Employee` (`NIK`)

TABLE `EmployeeFamily`

 (

  `ID` int(50) NOT NULL AUTO_INCREMENT,

  `NAME` varchar(255) DEFAULT NULL,

  `Gender` varchar(1) DEFAULT NULL,

  `DOB` date DEFAULT NULL,

  `EmployeeRelationshipID` int(50) DEFAULT NULL,

  `NIK` int(40) DEFAULT NULL,

  PRIMARY KEY (`ID`),

  KEY `NIK` (`NIK`),

  KEY `EmployeeRelationshipID` (`EmployeeRelationshipID`),

  CONSTRAINT `EmployeeFamily_ibfk_1` FOREIGN KEY (`NIK`) REFERENCES `Employee` (`NIK`),

  CONSTRAINT `EmployeeFamily_ibfk_2` FOREIGN KEY (`EmployeeRelationshipID`) REFERENCES `EmployeeRelationship` (`ID`)

TABLE `EmployeeJobHistory`

 (

  `ID` int(50) NOT NULL AUTO_INCREMENT,

  `NIK` int(40) DEFAULT NULL,

  `Company` varchar(255) DEFAULT NULL,

  `Pos` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`ID`),

  KEY `NIK` (`NIK`),

  CONSTRAINT `EmployeeJobHistory_ibfk_1` FOREIGN KEY (`NIK`) REFERENCES `Employee` (`NIK`)

)

TABLE `EmployeeRelationship`

 (

  `ID` int(50) NOT NULL AUTO_INCREMENT,

  `Relationship` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`ID`)

TABLE `Employee_Email`

 (

  `ID` int(50) NOT NULL AUTO_INCREMENT,

  `NIK` int(40) DEFAULT NULL,

  `EmailAddress` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`ID`),

  KEY `NIK` (`NIK`),

  CONSTRAINT `Employee_Email_ibfk_1` FOREIGN KEY (`NIK`) REFERENCES `Employee` (`NIK`)

TABLE `Employee_Phones` 

(

  `ID` int(50) NOT NULL AUTO_INCREMENT,

  `NIK` int(40) NOT NULL,

  `PhoneNumber` varchar(20) DEFAULT NULL,

  `PhoneType` varchar(30) DEFAULT NULL,

  PRIMARY KEY (`ID`),

  KEY `NIK` (`NIK`),

  CONSTRAINT `Employee_Phones_ibfk_1` FOREIGN KEY (`NIK`) REFERENCES `Employee` (`NIK`)

TABLE `Level` 

(

  `ID` int(50) NOT NULL AUTO_INCREMENT,

  `Name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`ID`)

TABLE `Notes`

 (

  `note` varchar(255) DEFAULT NULL

TABLE `Pos`

 (

  `ID` int(50) NOT NULL AUTO_INCREMENT,

  `Name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`ID`)

TABLE `Transaction` 

(

  `ID` int(50) NOT NULL AUTO_INCREMENT,

  `NIK` int(40) NOT NULL,

  `TransactionTypeID` int(50) DEFAULT NULL,

  `NoSurat` varchar(255) DEFAULT NULL,

  `EffectiveDate` date DEFAULT NULL,

  `EndDate` date DEFAULT NULL,

  `ContractTypeID` int(50) DEFAULT NULL,

  `BranchID` int(50) DEFAULT NULL,

  `DepartmentID` int(50) DEFAULT NULL,

  `PositionID` int(50) NOT NULL,

  `LevelID` int(50) NOT NULL,

  `Reasons` varchar(255) DEFAULT NULL,

  `Notes` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`ID`),

  KEY `NIK` (`NIK`),

  KEY `LevelID` (`LevelID`),

  KEY `PositionID` (`PositionID`),

  KEY `DepartmentID` (`DepartmentID`),

  KEY `BranchID` (`BranchID`),

  KEY `ContractTypeID` (`ContractTypeID`),

  KEY `TransactionTypeID` (`TransactionTypeID`),

  CONSTRAINT `Transaction_ibfk_1` FOREIGN KEY (`NIK`) REFERENCES `Employee` (`NIK`),

  CONSTRAINT `Transaction_ibfk_2` FOREIGN KEY (`LevelID`) REFERENCES `Level` (`ID`),

  CONSTRAINT `Transaction_ibfk_3` FOREIGN KEY (`PositionID`) REFERENCES `Pos` (`ID`),

  CONSTRAINT `Transaction_ibfk_4` FOREIGN KEY (`DepartmentID`) REFERENCES `Department` (`ID`),

  CONSTRAINT `Transaction_ibfk_5` FOREIGN KEY (`BranchID`) REFERENCES `Branch` (`ID`),

  CONSTRAINT `Transaction_ibfk_6` FOREIGN KEY (`ContractTypeID`) REFERENCES `ContractType` (`ID`),

  CONSTRAINT `Transaction_ibfk_7` FOREIGN KEY (`TransactionTypeID`) REFERENCES `TransactionType` (`ID`)

TABLE `TransactionType` 

(

  `ID` int(50) NOT NULL AUTO_INCREMENT,

  `Name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`ID`)

)

TABLE `User`

(

 `ID` int(11) NOT NULL AUTO_INCREMENT,

`Username` varchar(50) DEFAULT NULL,

`Password` varchar(50) DEFAULT NULL,

`Role` varchar(20) DEFAULT NULL,

PRIMARY KEY (`ID`)

)

Normalisation:

Functional Dependency:

The left column shows the attributes that the right side is dependent towards 

Example:

NIK——>employeeFullName,KTP

Attribute ———>Determines
NIK———>employeeFullName, employeeNickName,KTP,Jamsostek,BankID,Rekening,NPWP,StatusPajak,DOB,Gender,Religion,MaritalStatus
employeePhone.ID——–>employeeID,phoneNumber,phoneType
employeeEmail.ID———>employeeID,emailAddress,emailAddress
Transaction.ID———>employeeID,transactionType,noSurat,effectiveDate,endDate,contractTypeID,branchID,departmentID,positionID,levelID,Reason,Notes
TransactionType.ID——–>transactionName
contractType.ID———>contractName
branch.ID———>branchName
department.ID———>departmentName
position.ID———>positionName
level.ID———>levelName
branch.ID———>branchName
bank.ID———>bankName
employeeAddress.ID——>employeeAddressDetail,employeeID,employeeAddressType
employeeRelationship.ID———>Relationship
employeeFamily.ID——–>Name,Gender,DOB,employeeRelationShipID,employeeID
employeeEducation.ID—>educationLevel,institution,major,graduationYear,score,employeeID
employeeJobHistory.ID—>employeeID,Company,Position

Normalisation justification:

The first normal form dictates all the attribute within a relation needs to be single valued. This means that multiple valued attributes such as if a person has 2 jobs is converted into values within different rows. It is difficult to show that within the above diagram. There are examples showing this possibility was closed with our design. For example if an employee has multiple phone numbers. These phone numbers will have unique phone ID generated hence does not depend on the person rather it depends on the ID. There are some attributes we assume will be singular such as gender,KTP,and Nickname. With the condition set that these values are singular then all attributes should be singular in nature therefore adhering to the first Normal Form.

The second normal form dictates that there exists no partial dependency no non-prime attribute is dependent on any other subset of the primary key. Because we designed it in a way that all the tables have a unique primary key every attribute is determined by their respective primary key. The lack of composite keys means that no partial dependency is present within the database. Some of the tables might seem necessary however we designed it so it would be customizable if required later on. 

The third normal form dictates there exists no transitive dependency between the columns. As shown previously all the attributes within a relation are dependent on their respective primary key and by extension and definition this allows the database to be in third normal form. 

Sample queries

provide some samples queries (at least 5) to generate reports 

Sample 1:

Query to generate the number of employees from both genders.

“SELECT Gender, count(*) ‘total’ FROM Employee GROUP BY Gender”

Sample 2:

Query to generate the number of employees for each marital status (single, married, divorced).

“SELECT MaritalStatus, count(*) ‘total’ FROM Employee group by MaritalStatus”

Sample 3:

Query to

 “SELECT t.*, tt.Name ‘TransactionType’, ct.Name ‘Contract Type’, 

                                b.name ‘Branch’, d.name ‘Department’, p.name ‘Position’, l.name ‘Level’  

                                FROM transaction t 

                                JOIN transactiontype tt 

                                    ON t.transactiontypeid = tt.id 

                                JOIN contracttype ct 

                                   ON t.contracttypeid = ct.id 

                                JOIN branch b 

                                    ON  t.branchid = b.id  

                                JOIN department d 

                                    ON t.departmentid = d.id 

                                JOIN pos p 

                                    ON t.positionid = p.id 

                                JOIN level l 

                                    ON n t.levelid = l.id;”;

Sample 4:

“SELECT * FROM employee e JOIN employeefamily ef ON ef.nik = e.nik join employeeaddress ea ON ea.employeenik = e.nik JOIN employee_phones ep ON ep.nik = e.nik where e.nik IN (SELECT nik FROM transaction WHERE enddate < now());”

Sample 5:

Query to generate a report that provides information on the amount of employees in the age groups of under 20 years old, between 20 and 30 and between 31 and 40.

“SELECT IF(year(now())-year(DOB) < 20, ‘Under 20’,  

                                        IF(year(now()) – year(DOB) <= 30, ’20 – 30′, 

                                        IF(year(now()) – year(DOB) <= 40, ’31 – 40′,’Above 40′))) AS category,  

                                        count(*) ‘total’  

                            FROM Employee GROUP BY category”

Sample 6:

 SELECT * FROM employee e 

JOIN EmployeeFamily ef 

ON ef.nik = e.nik 

JOIN EmployeeAddress ea 

ON n ea.employeenik = e.nik 

JOIN employee_phones ep 

ON ep.nik = e.nik 

JOIN 

(

SELECT  nik FROM transaction tr 

WHERE enddate < now() AND (tr.positionid = 1 or tr.departmentid = 1)

) AS t 

ON e.nik = t.nik;

User Interface

Login Screen:

Main Form:

Options Available On the Headers:

Adding New Employee:

Entering Employee Family Information:

Selecting Employee

Adding Employee Family Relationship

Deleting Relationship

Entry Phone Data

Entry Email Data

Entry Address Data

Entry Education Data

Entry Job History Data

Register New Employee

Edit Transaction

Bank Management:

Branch Management:

Contract Management:

Department Management:

Level Management:

Position Management:

Transaction Type Management:

Employee Report:

Transaction Report:

Employee Recap Data

Importing CSV Files:

 Imported data will be inserted into the database.

Database Security

  1. User access management

There is only one admin set to work within the database. This admin controls users underneath it. Meaning the admin has the power to limit or grant more access to the database if so required. In a company setting it means that a singular admin or super admin can delegate parts of the work to their underlings. Since this is targeted towards HR, an HR manager can give some access to the employee. This access can vary from just view only or (write and view). The default is view only. This means that some other employees that don’t need to edit can use it as a reference for views. Furthermore this prevents unauthorized personnel from compromising the data within the database. 

  1. SQL injection countermeasures

The code incorporates methods to counteract SQL injections. The main one being the handling of Prepared Statements. Look at the screenshot below

As you can parameters addwithvalue is added within the code which would mean that the user is forced to to insert the data in at least a suitable form so if for example a query added DROP TABLE query after the original query it would not register as that and would instead simply be a string therefore preventing major vulnerabilities in the form of SQL injections. 

The above screenshot also shows the username and password countermeasures against sql injections. As seen in the screenshot username and password being the likely target of injections have addwithvalue functions thereby creating a barrier for sql injections from running within the server. Furthermore, it also adds an if function that checks if  the correct username and password is detected to prevent users with unauthorized privileges from disrupting the data

CONCLUSION

In conclusion we were tasked with creating a database based on a certain company’s excel spreadsheet that fulfills the same capabilities as the original spreadsheet. Through this spreadsheet we created an ERD that has all the required links while also fulfilling the criterias to be in third normal form. Afterwards we created the database within mysql and tested it to make sure it is in working condition. Lastly we then coded it within visual studio in order to create a cohesive UI that integrates this database with pre built queries.

Source Code

https://github.com/hengkysanjaya123/FinalProjectDB

Video

https://www.youtube.com/watch?v=paxYiB0INmg

Member’s Blog

http://2201852492hengky.blog.binusian.org/2020/01/07/individual-team-member-contributions/

http://2201798856rio.blog.binusian.org/2020/01/07/database-final-project-contribution/

http://2201832446ryan.blog.binusian.org/2020/01/07/database-systems-final-project-contribution/

Posted in Uncategorized | Leave a comment

Individual team member contributions

Naufal F. Basyah: Report Check and Miscellaneous 

I am mostly responsible for the report checks and details that need to be made i.e. ERD and relation description. I also contribute in a smaller scale in miscellaneous task as an extra pair of hands.

Posted in Uncategorized | Leave a comment

Employee Management System utilizing Databases (Final Project Proposal)

Project Details

Team Members:

Hengky Sanjaya (2201852492)

Naufal F. Basyah (2201840334)

Rio Adi Nugraha (2201798856)

Ryan Rusli (2201832446)

  1. Problem Statement

In order for a company to function it must have a system to organize its own resources. One resource that is crucial yet sometimes overlooked is human resources. A company must at all times have access to all the required data related to an employee. The required data includes but not limited their wages, leave allowances, work ratings, contract type, etc. in today’s era HR becomes a given when talking about any workplace. 

In the interview we conducted we have surmised that a significant number of HR managers still have all their data in excel. This has its own share of problems such as field organization, scalability, and data integrity. We asked a number of questions such as “what do you use to store data?” and “What do you think can be improved?”. The answers we obtained as well as research results led to our conclusion that database might be a better means of data storage for this matter. Hence we have come up with the following problem statement:

How can the usage of databases improve the efficiency of an HR department in a company?

The above is the end goal of the project where everything is based on. Deliberations towards UI design, transaction requirements all have the above basis for the overall design of it.

  1. Target User

The target user is an HR manager of a company. Currently, according to our interview there are plenty of HR managers who use excel as their main source of data storage. This has a suite of problems we are trying to solve by converting that data storage into a database. This in theory should ease their workload and apply scalability to the system. 

  1. List of Relations
Posted in Uncategorized | Leave a comment

Hello world!

Welcome to Binusian blog.
This is the first post of any blog.binusian.org member blog. Edit or delete it, then start blogging!
Happy Blogging 🙂

Posted in Uncategorized | 1 Comment