Reference no: EM132176577
Assessment: Database Application - Case Study
Learning Outcomes
b) Explain the characteristics of relational databases and their role in creation and communication of business intelligence.
c) Identify and assess IT controls, auditing, ethical, privacy and security issues with respect to information.
d) Apply technical knowledge and skills in creating information for the workplace using spreadsheets and relational databases.
e) Communicate with IT professionals, stakeholders and user groups of information systems.
ASSESSMENT - Database Application Case Study
CASE STUDY
Background of Torrens We Help Inc.
Torrens We Help Inc (TWHI) is a not-for-profit private education organisation. It provides online education in farming anywhere in the world. The majority of their students are from outback areas of Australia, Papua New Guinea and New Zealand.
TWHI wants to engage and work in partnership with communities, industries and businesses. It seeks to provide multiple learning experiences and opportunities to enrich the learning journey of its students. TWHI prides itself on being an inclusive provider, offering higher education to people who might not otherwise have the opportunity to experience it. To this end many of its students have their fees paid partially or fully by the Australian, PNG or New Zealand government.
One of their projects is called Project "HR DATABASE". The following are the stages:
Stage 1: Develop HR Database System for the Academic Staff
Stage 2: Extend the Database system to include administrative Staff
Stage 3: Develop a prototype data mart for the HR System that will be used as basis for the development of the data warehouse for the entire university.
You have been assigned to develop the stage 1 of their project.
Project Description:
TWHI 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:
- Employee information (Employee ID, Title, First Name, Middle Name, Last Name, Birthdate, Street Address, City State, Postal Code, Phone Number, Mobile Number, Date Hired, Date Terminated, TFN, Department, Location)
- 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)
- List of subjects that teaching staff is approved to teach (name, list of subjects)
- Information about the teaching staff (name, location, contact details, status (part-time, full-time, sessional)
- Administrative staff information (name, position, status (part time or full-time)
- 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)
- 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 Curriuculum 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.
You are expected to develop a database for the ACADEMIC STAFF of TWHI Human Resource. You need to design and implement the database using Microsoft Access or LibreOfficeBase.
In this project, you are required to design the database and develop the database for storing and retrieving information about the TWHI Academic Staff. You need to create tables, queries, reports and forms.
The following are some of the requirements for TWHI HR:
1. Forms:
a. HR Manager should have the form to enter the information about AusED's employees
b. HR Manager should have the form to enter information about the allocation of subject per Academic Staff
2. Reports:
a. HR Manager wants to see the following reports:
- List of all Employees and their contact details (sorted by Last Name, then First Name)
- List and total number of all full-time Academic Staff and their location
- List and total number of all part-time Academic Staff and their location
- List and total number of sessional Academic Staff and their location
- List the names of all academic staff and the type of work they are doing.
- List the names of all employees and how long they have worked for TWHI
3. Tables:
a. In order for you to create the forms and required reports, you must first create tables that will store all the data about the Academic Staff.
b. The following are the activities you expected to do:
- create the database tables
- normalise the database tables
- update the database tables using SQL statements
- add at least 10 different data per table
c. The final project should contain normalised tables
4. E-R Diagram
a. You are also required to submit the E-R diagram for this project using any diagram editor.