Reference no: EM132312985

Project

Since its inception, AusEd Inc has used a spreadsheet software to maintain and monitor its employee information. Its human resource (HR) department is currently struggling get the updated information of its entire academic and administrative staff. The HR manager maintains the following spreadsheets:

1. Employee personal information (Employee ID, Title, First Name, Middle Name, Last Name, Birth date, Street Address, City State, Postal Code, Phone Number, Mobile Number, Date Hired, Date Terminated, TFN, Department, Location)

2. Employee Education Background (Employee ID, Title, First Name, Last Name, Highest Education Attainment, Qualification, Year Completed, School)

3. Employee Employment Background (Employee ID, Title, First Name, Last Name, Year Started Working, Year Finished Working, Position, Organisation Name, Location)

4. Timesheet for teaching staff (name of staff, hours worked per week, type of work, (e.g., online facilitation, assessment marking, consultation), hourly rate, subject name, number of students)

5. List of subjects that teaching staff is approved to teach (name, list of subjects)

6. Information about the teaching staff(name, location, contact details, status (parttime, full-time, sessional)

7. Administrative staff information (name, position, status (part time or full-time)

8. Teaching staff and administrative staff leave/absence spreadsheet (name of staff, position, status, no of leave allowed, no of sick leave allowed, dates of leave/absences, type of leave, remaining leave, remaining sick leave, paid/not paid leave, status (approved or not)

9. Staff Professional Development Activities (name of staff, date of activity, type of activity, description of activity, report submitted, funding type)

Aside from the abovementioned spreadsheets, the HR Manager maintains a physical file of all the Curriculum Vitae’s of all the staff and just reads the CVs when information is needed.

Because of this, the HR Manager spends time sorting and looking at the staff’s records to get the information needed by several stakeholders in AusEd (e.g., management, payroll, staff) Management has determined that the company’s plan of extending its education services to different areas means it is no longer feasible to use spreadsheets to maintain the organisation’s human resource data.

A human resource development database system needs to be designed to be able to store and retrieve all employee related information in an organized and efficient manner. In addition, the spreadsheets that the AusEd Human Resource (HR) manager maintains contain a lot of redundant data. The spreadsheets also contain attributes (columns) that should belong to other spreadsheets.

At the end of the semester, you are expected to develop a database for the ACADEMIC STAFF of AusEd’s Human Resource. You need to design and implement the database using LibreOffice Base, MYSQL Workbench and MYSQL database Server. You will be having practical activities during the semester and each practical activity contributes to the completion of your project. Additional information you need in developing the database for AusEd will be provided during the week you are expected to implement a practical activity.

In this project, you will only be developing a database for storing and retrieving information about the AusEd Academic Staffs. You need to create tables, reports and forms using MySQL Workbench and LibreOffice Base. You also need to create SQL queries and/view views, normalised tables and create E-R Diagrams.

The following are the requirements of AusED HR that you need to develop for your project:

1.Forms:

i. HR Manager should have the three forms to enter the information about AusEd’s employees (personal, educational and employment background)

ii. HR Manager should have the form to enter information about the allocation of subject per Academic Staff

iii. Academic Staff should have the form to enter the weekly timesheet

2.Queries:

HR Manager wants to see the following reports:

i. List of all Employees and their phone contact details (sorted by Last Name, then First Name, Phone Number, Mobile Number)

ii. List and total number of all full-time Academic Staff and their location

iii. List and total number of all part-time Academic Staff and their location

iv. List and total number of sessional Academic Staff and their location

v. List of all employees and their employment background (sorted by year from most recent employment)

vi. List the combined total hours worked of all the academic staff per week, the maximum hours worked, minimum hours worked, the name and subject of the academic staff who had the maximum hours, the name and subject of the academic staff who had the minimum hours worked.

vii. List of how many subjects each academic staff is teaching for each semester (show the first name, last name and the number of subjects being taught).

viii. List of academic staff, their subject and their total hours worked per semester per academic staff.

ix. List the names of all employees and how long they have worked for AusEd Table.

In order for you to create the forms and required reports, you must first create tables that will store all the data about the AusED Academic Staff. The following are the activities you expected to do:

i. create normalised database tables with appropriate attributes, appropriate primary keys, foreign keys, and appropriate constraints, and suitable indexes.

ii. update the database tables using SQL statements to add at least 10 different data per table

4. E-R Diagram

You are also required to submit the E-R diagram for this project using MySQL Workbench.

Attachment:- Data Management System.rar