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.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
