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.
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`
—
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.
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download