Background of AusEd Inc

This report discusses the development of AusEd HR department data management system. Currently the HR department is using spreadsheets to keep and maintain its data. AusEd has a very large number of employees thus accessing and maintaining the data using spreadsheets is becoming a very hard task for the HR department. Thus, there is need for a data and information management system that will make data management easier for HR department. This report discusses different aspects of the system that will be considered during the development process of the system.

The AusEd HR department project will involve creating a database to manage the data and information of the department. The database will be created suing Libre Office base and will run under the MySQL database server. To create the database, configuration of the Libre office base will have to be done to make sure that Libre base can access the MySQL database server.

After configuration, the next stage of the project will be to define the entities from the case study which will be analyzed and normalized to come up with entities that will be implemented as tables in the database. After defining the entities making up the complete database, the next step will be to implement the entities as tables using Libre base. This phase of the project will involve creating all the entities and their relationships to make sure the database is a standard relational database.

After implementing the tables and the relationships the next step will be testing the database and the best way is to use sample data to populate different tables making up the database and then running queries to test that the database has no logical errors that could affect it once it’s implemented for full use by the department.

The services to be performed by the system are;

Addition and maintenance of employee information. The employee information will include all their personal details

Addition and maintenance of the employees’ education background.

Addition and maintenance of employees’ employment background.

Maintenance of timesheet for the teaching staff.

Addition and maintenance of the list of subject taught by every staff.

Addition and maintenance of the information about teaching staff including their personal details.

Addition and maintenance of staff leave and absence information.

Addition and maintenance of the professional development activities and different employees that attend the development activities.

Context diagram

 

Context diagram description

According to the context diagram shown above of the HR department management system there are two external actors interacting with the system. The two external actors are employees and staff. The employee has the most interactions with the system as shown in the diagram. Each interaction with the system results with a message from the HR management system thus the employee adds different type of information and the system responds with a message which can either be a success message or an error message. The staff is the other external actor who interacts with the system. A staff records his or her attendance timesheets using the system. The system responds with a success or error message.

Project Overview

Functional requirements are the requirements of the system that the user interacts with directly in order to achieve certain goals using the system. For the HR department management system the functional requirements are;

The employee should be able to add, update or delete a record of another employee.

An employee should be able to add, update or delete the educational background information of an employee.

An employee should be able to add, update or delete the employment information of an employee.

An employee should be able to add, update or delete different organizations that employees have worked in.

An employee should be able to add, update or delete the staff timesheet information.

An employee should be able to add, update or delete the staff leaves and absences information using the system.

An employee should be able to fetch all the data of the every table making up the database.

 

Data dictionary

Entity	Attribute	Data Type	Key
Staff	StaffID	INT	PK
	Name	VARCHAR
	Location	VARCHAR
	Contacts	VARCHAR
	Status	VARCHAR
	Type	VARCHAR
	position	VARCHAR
Employee_education_background	recordID	INT	PK
	employeeID	INT	FK
	YearStartedWorking	INT
	YearFinishedWorking	INT
	Position	VARCHAR
	orgID	INT	FK
Organisations	orgID	INT	PK
	Name	VARCHAR
	location	VARCHAR
Staff_timesheet	staffID	INT	PK
	hrsWorked	INT
	workType	VARCHAR
	hourlyRate	INT
	subjectName	VARCHAR
	NoOfStudents	INT

 

The tables have been implemented using MySQL. The creation script is shown below;

CREATE TABLE IF NOT EXISTS `activity_staff` (

  `activityID` int(11) NOT NULL,

  `staffID` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

— ——————————————————–

—

— Table structure for table `devactivities`

—

CREATE TABLE IF NOT EXISTS `devactivities` (

`activityID` int(11) NOT NULL,

  `type` varchar(50) NOT NULL,

  `date` date NOT NULL,

  `description` varchar(500) NOT NULL,

  `reportSubmitted` varchar(50) NOT NULL,

  `fundingType` varchar(50) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

—

— Table structure for table `employee`

—

CREATE TABLE IF NOT EXISTS `employee` (

`employeeID` int(11) NOT NULL,

  `title` varchar(50) NOT NULL,

  `firstName` varchar(50) NOT NULL,

  `MiddleName` varchar(50) NOT NULL,

  `lastName` varchar(50) NOT NULL,

  `BirthDate` date NOT NULL,

  `streetAddress` varchar(50) NOT NULL,

  `cityState` varchar(50) NOT NULL,

  `postalCode` int(11) NOT NULL,

  `phoneNO` varchar(50) NOT NULL,

  `mobileNO` varchar(50) NOT NULL,

  `dateHired` date NOT NULL,

  `TFN` varchar(25) NOT NULL,

  `department` varchar(50) NOT NULL,

  `location` varchar(50) NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

—

— Table structure for table `employee_education_background`

CREATE TABLE IF NOT EXISTS `employee_education_background` (

  `employeeID` int(11) NOT NULL,

  `highestEducation` varchar(100) NOT NULL,

  `qualififcation` varchar(100) NOT NULL,

  `yearCompleted` int(11) NOT NULL,

  `school` varchar(100) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

—

— Table structure for table `employee_employment_background`

—

CREATE TABLE IF NOT EXISTS `employee_employment_background` (

`recordID` int(11) NOT NULL,

  `yearStartedWorking` int(11) NOT NULL,

  `yearFinishedWorking` int(11) NOT NULL,

  `position` varchar(50) NOT NULL,

  `orgID` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

—

— Table structure for table `orgnisation`

Database Creation

—

CREATE TABLE IF NOT EXISTS `orgnisation` (

`orgID` int(11) NOT NULL,

  `name` varchar(50) NOT NULL,

  `location` varchar(50) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

—

— Table structure for table `staff`–

CREATE TABLE IF NOT EXISTS `staff` (

`staffID` int(11) NOT NULL,

  `name` varchar(50) NOT NULL,

  `location` varchar(50) NOT NULL,

  `contacts` varchar(50) NOT NULL,

  `status` varchar(50) NOT NULL,

  `type` varchar(50) NOT NULL,

  `position` varchar(50) NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

—

— Table structure for table `staffleaveabence_spreadsheet`

CREATE TABLE IF NOT EXISTS `staffleaveabence_spreadsheet` (

  `staffID` int(11) NOT NULL,

  `leaveNoAllowed` int(11) NOT NULL,

  `sickleaveNoAllowed` int(11) NOT NULL,

  `remainigLeave` int(11) NOT NULL,

  `remainingSickLeave` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

—

— Table structure for table `staff_leave_dates`-

CREATE TABLE IF NOT EXISTS `staff_leave_dates` (

`recordID` int(11) NOT NULL,

  `typeOfLeave` varchar(50) NOT NULL,

  `paid_not_paid` varchar(50) NOT NULL,

  `status` varchar(50) NOT NULL,

  `dateOfLeave` date NOT NULL,

  `StaffID` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

—

— Table structure for table `staff_subjects`

CREATE TABLE IF NOT EXISTS `staff_subjects` (

`recordID` int(11) NOT NULL,

  `staffID` int(11) NOT NULL,

  `subjectName` varchar(50) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

—

— Table structure for table `staff_timesheet`

CREATE TABLE IF NOT EXISTS `staff_timesheet` (

`recordID` int(11) NOT NULL,

  `hrsWorked` int(11) NOT NULL,

  `workType` varchar(50) NOT NULL,

  `hourlyRate` decimal(10,0) NOT NULL,

  `subjectName` varchar(50) NOT NULL,

  `numberOfStudents` int(11) NOT NULL,

  `staffID` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

— Indexes for dumped tables

— Indexes for table `activity_staff`–

ALTER TABLE `activity_staff`

 ADD PRIMARY KEY (`activityID`,`staffID`), ADD KEY `staffID` (`staffID`);

— Indexes for table `devactivities`–

ALTER TABLE `devactivities`

 ADD PRIMARY KEY (`activityID`);

— Indexes for table `employee`–

ALTER TABLE `employee`

 ADD PRIMARY KEY (`employeeID`);

— Indexes for table `employee_education_background`–

ALTER TABLE `employee_education_background`

 ADD KEY `employeeID` (`employeeID`);

— Indexes for table `employee_employment_background`

ALTER TABLE `employee_employment_background`

 ADD PRIMARY KEY (`recordID`), ADD KEY `orgID` (`orgID`);

— Indexes for table `orgnisation`–

ALTER TABLE `orgnisation`

 ADD PRIMARY KEY (`orgID`);

— Indexes for table `staff`–

ALTER TABLE `staff`

 ADD PRIMARY KEY (`staffID`);

— Indexes for table `staffleaveabence_spreadsheet`–

ALTER TABLE `staffleaveabence_spreadsheet`

 ADD KEY `staffID` (`staffID`);

— Indexes for table `staff_leave_dates`

—

ALTER TABLE `staff_leave_dates`

 ADD PRIMARY KEY (`recordID`), ADD KEY `StaffID` (`StaffID`);

— Indexes for table `staff_subjects`–

ALTER TABLE `staff_subjects`

 ADD PRIMARY KEY (`recordID`), ADD KEY `staffID` (`staffID`);

— Indexes for table `staff_timesheet`–

ALTER TABLE `staff_timesheet`

 ADD PRIMARY KEY (`recordID`), ADD KEY `staffID` (`staffID`);

— AUTO_INCREMENT for dumped tables

— AUTO_INCREMENT for table `devactivities`

—

ALTER TABLE `devactivities`

MODIFY `activityID` int(11) NOT NULL AUTO_INCREMENT;

—

— AUTO_INCREMENT for table `employee`

—

ALTER TABLE `employee`

MODIFY `employeeID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;

—

— AUTO_INCREMENT for table `employee_employment_background`

—

ALTER TABLE `employee_employment_background`

MODIFY `recordID` int(11) NOT NULL AUTO_INCREMENT;

—

— AUTO_INCREMENT for table `orgnisation`

—

ALTER TABLE `orgnisation`

MODIFY `orgID` int(11) NOT NULL AUTO_INCREMENT;

—

— AUTO_INCREMENT for table `staff`

—

ALTER TABLE `staff`

MODIFY `staffID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;

—

— AUTO_INCREMENT for table `staff_leave_dates`

—

ALTER TABLE `staff_leave_dates`

MODIFY `recordID` int(11) NOT NULL AUTO_INCREMENT;

—

— AUTO_INCREMENT for table `staff_subjects`

—

ALTER TABLE `staff_subjects`

MODIFY `recordID` int(11) NOT NULL AUTO_INCREMENT;

—

— AUTO_INCREMENT for table `staff_timesheet`

—

ALTER TABLE `staff_timesheet`

MODIFY `recordID` int(11) NOT NULL AUTO_INCREMENT;

—

— Constraints for dumped tables

— Constraints for table `activity_staff`

—

ALTER TABLE `activity_staff`

ADD CONSTRAINT `activity_staff_ibfk_1` FOREIGN KEY (`activityID`) REFERENCES `devactivities` (`activityID`),

ADD CONSTRAINT `activity_staff_ibfk_2` FOREIGN KEY (`staffID`) REFERENCES `staff` (`staffID`);

— Constraints for table `employee_education_background`–

ALTER TABLE `employee_education_background`

ADD CONSTRAINT `employee_education_background_ibfk_1` FOREIGN KEY (`employeeID`) REFERENCES `employee` (`employeeID`);

— Constraints for table `employee_employment_background`

—

ALTER TABLE `employee_employment_background`

ADD CONSTRAINT `employee_employment_background_ibfk_1` FOREIGN KEY (`orgID`) REFERENCES `orgnisation` (`orgID`);

— Constraints for table `staffleaveabence_spreadsheet`–

ALTER TABLE `staffleaveabence_spreadsheet`

ADD CONSTRAINT `staffleaveabence_spreadsheet_ibfk_1` FOREIGN KEY (`staffID`) REFERENCES `staff` (`staffID`);

— Constraints for table `staff_leave_dates`–

ALTER TABLE `staff_leave_dates`

ADD CONSTRAINT `staff_leave_dates_ibfk_1` FOREIGN KEY (`StaffID`) REFERENCES `staff` (`staffID`);

— Constraints for table `staff_subjects`–

ALTER TABLE `staff_subjects`

ADD CONSTRAINT `staff_subjects_ibfk_1` FOREIGN KEY (`staffID`) REFERENCES `staff` (`staffID`);

— Constraints for table `staff_timesheet`–

ALTER TABLE `staff_timesheet`

ADD CONSTRAINT `staff_timesheet_ibfk_1` FOREIGN KEY (`staffID`) REFERENCES `staff` (`staffID`);

/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;

/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;

/*!40101 SET [email protected]_COLLATION_CONNECTION */;

The following are constraints of the system

All the data entered in different tables should match with the data types specified in the tables

Referential integrity should be enforced at all times when either inserting, updating or deleting a record for any table of the database that is related to another table.

The length of data entered should match the data length specified for every column in all tables.