ISYS 221 Database Management System Background of AusEd Inc AusEd Inc is a not-for-profit private education organisation. It provides online education in Information Technology and Business to students anywhere in the world. The majority of their students are from outback areas of Australia, Papua New Guinea and the South Pacific Islands. AusEd is committed to learning. It sees its role as enabling students to 'be what they want to be'. By providing education programs through online learning, AusEd gives students the flexibility to study a university degree without the need to visit a campus. AusEd wants to engage and work in partnership with communities, industries and businesses. It seeks to provide multiple learning experiences and opportunities so to enrich the learning journey of its students. These learning opportunities assist in the development of discipline-specific skills and generic skills relevant to life-long learning. AusEd 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 or New Zealand governments or by a government of one of the South Pacific countries. 1. Organisation 1.1 Legal status AusEd is registered in Australia as a not-for-profit corporation. This means that it must comply with obligations specified by the Australian Corporations Act (2001), particularly those pertaining to corporate governance and financial reporting. It is also registered as a non self-accrediting higher education provider. This means that it must satisfy educational standards defined by the Tertiary Education Quality and Standards Agency Act (2011) and must report on its performance to the relevant government-appointed authority. 1.2 Activities 1.2.1 Online education Distance education programs at AusEd can be studied from anywhere in the World but typically in outback areas of Australia, Papua New Guinea and the South Pacific islands. AusEd students pursue their study programs through a variety of means including the use of special study centres, online discussion forums, electronic library resources, direct communication with lecturers, and by receiving study materials online or by post. 1.2.2 Education Programs AusEd's undergraduate and postgraduate programs provide students with a generic foundation to begin a career in information technology or business. Within these programs students study a total of 24 Bachelor-level or 16 Masters-level units which provide them with broad understanding and knowledge across a wide range of areas. In the information technology programs core units provide a solid grounding in what the Australian Computer Society (ACS) calls the Core Body of Knowledge (CBoK) and, in what AusEd calls its Professional Pathway units, students specialise in one or more of the following areas; Network Engineering, Application Development or Database Management. In the business programs there are also core and professional pathway units allowing students to specialise in Accounting, Supply Chain Management or Occupational Health & Safety. Some students study for a double degree in both information technology and business. This requires them to complete a total of 36 Bachelor-level units. 1.3 Organisational Structure AusEd is organised into three divisions: Sales, Course Delivery, and Operations. The Sales division manages sales and marketing operations. The division is also responsible for management of agents and course promotion. The Course Delivery division is responsible for the development of course materials, teaching and assessment, and education administration. The Operations division manages all the core operations including accounting, human resource management and information systems. 1.4 Staff and Locations AusEd has approximately 2,000 (full time equivalent) students and approximately 100 staff more than half of whom work on a casual, part-time basis. 1.4.1 Executive management, operations and education administration AusEd's administrative head office is located in Darwin. This is where executive management is located along with staff in the Operations division and education administrators (ie. staff in the Course Delivery division not directly involved in teaching). 1.4.2 Sales and Marketing Sales and marketing is conducted from offices in Alice Springs, Pt Moresby and Suva. 1.4.3 Teaching staff Lecturers and other teaching staff work from campuses or home offices in most states of Australia and New Zealand. 1.4.4 Special Study Centres In addition to its various offices, AusEd maintains special study centres in Darwin, Alice Springs, Pt Moresby and Suva. These are hubs where students can come if they require support. The Centres give students a place to meet face-to-face with staff and colleagues, to form study groups and to access resources and technology. The Special Study Centres offer facilities such as: • Student computers and work stations. • Support staff to assist with academic enquiries. • Photocopying, scanning and printing facilities. • Multimedia meeting areas with state-of-the-art video conferencing facilities. • Tutorial meeting rooms available for study groups. • Wireless networking to enable connection of student devices and laptops to the Internet and AusEd systems. • Student social areas to engage with other students and staff. • Presentation areas available for information sessions and meetings. 2. Strategic Plan There are two elements to AusEd's strategic plan. The first is to increase income by diversifying sources of funding (ie. beyond existing sources such as Australia's AusAid and New Zealand's NZAid). In tactical terms this means; first, extending educational services to areas with poor and/or unreliable Internet connections; and second, improving reliability of student assessments. The second element of AusEd's strategic plan is to minimise the cost of non-core activities, particularly support operations (eg. accounting, staff recruitment) and technology development. As part of meeting this objective AusEd hopes, wherever practical, to utilise and participate in the development of open source software. 3. Information systems and technologies 3.1 Existing Systems At its head-office in Darwin, AusEd technical staff maintain an accounting system running proprietary MYOB software, a customer relationship management system using open source SugarCRM software, and an email system based on Microsoft Exchange Server 2007. Maintained externally for AusEd by Pacific Systems Pty Ltd in Dunedin, New Zealand, is a learning management system based on the open source Moodle system and a proprietary student management system called Banner. 3.2 Technology plan Following are technological developments which AusEd believes will assist to achieve its strategic objectives; 3.2.1 Project "Off-line Moodle" Develop an add-on to Moodle such that students can maintain on personal computers (running any of MS Windows, Mac OS or Linux Ubuntu operating systems) local databases of Moodle discussion forums (ie. a local database is always updatable and synced with a centralised database when an InternetAusEd Inc is a not-for-profit private education organisation. It provides online education in Information Technology and Business to students anywhere in the world. The majority of their students are from outback areas of Australia, Papua New Guinea and the South Pacific Islands. 3.2.2 Project "Photo Exam" Develop an add-on to Moodle such that students can complete exams on personal computers (running any of MS Windows, Mac OS or Linux Ubuntu operating systems) and be photographed at regular intervals by the computer's on-board camera. The photographs must include a reliable timestamp and be kept securely until transferred to a central location whenever an Internet connection is established. 3.2.3 Project "Accounting System Replacement" Stage 1: Analyse AusEd's requirements for an accounting system and produce necessary documentation. Stage 2: Review open source accounting systems, short-list and assess 3 to 5 possibilities, and implement the chosen system (perhaps after significant modification if such is considered necessary). Review the relative benefits of running the system on an in-house server and externally maintained server. 3.2.4 Project "Human Resources (HR) Management System" Stage 1: Develop HR Database System for the Academic Staff Stage 2: Extend the Database system to include administrative Staff 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 to 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, Birth date, 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 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. Your Project: Last semester, you developed a database for the AusEd's Academic Staff. You used LibreOfice Base to design and implement this database. This semester, you will be re-designing and extending the AusEd's Academic Staff database and create a simple operational database (assgnment1) and simple data "mart" (assgnment 2) using MySQL Workbench. 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 will be provided during the week you are expected to implement a practical activity. In this project, you will create tables, views and triggers using MySQL Workbench. You also need to perform some simple data administration tasks and create a simple operational database and data mart. The following are your project requirements: A . Practical assessment 1 - Create an operation database (assignment 1) 1. Logical database design 2. E-R Diagram a. You are also required to submit the EER design diagram for this project using MySQL Workbench. 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 AusED Academic Staff. b. The following are the activities you expected to do: • create the database tables • normalise the database tables • add sufficient data per table to demonstrate your queries are correct c. The final project should contain normalised tables 4. Queries: a. HR Manager wants to see the following results: • List of all Employees and their contact details (sorted by Last Name, then First Name) • Total number of sessional Academic Staff per location • Total number of full-time Academic Staff per location • List of academic staff, the total hours worked per Academic Staff given the date range (the HR Manager will determine the start and end date). • List of academic staff, their subject and total hours worked per week per subject. • List the names and contact information of the academic staff per location (location will be determined by the HR Manager). B Practical assignment 2 - Create an operation data mart Create a Simple Data Mart "Star" Schema Design (assignment 2) a. Ideally, you need hundreds or thousands of data to be able to make a successful data mart. In this project, you will work on the data that you have to create a data mart design for comparing hours spent by academic staff facilitating online. The data mart schema design will be used to create queries that will help the HR Manager analyse hours spent by online facilitators either by area or by type of activities they perform (e.g., tutorials, assessments, students consultations). The HR Manager should be able to identify who these online facilitators are and their personal information. i. An example query would be: HR Manager needs to know the location and name of online facilitators who are working more than 6 hours a week as online facilitator for the subject ISYS221. ii. You need to submit your proposed star schema design for a data mart that can answer queries like the example above. iii. Use EER Model in MySQL Workbench as a tool to design your schema. iv. Include this schema in you're the submission of your final project. 1. Logical database design 2. E-R Diagram a. You are also required to submit the EER design diagram for this project using MySQL Workbench. 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 AusED Academic Staff. b. The following are the activities you expected to do: i. create the database tables ii. add sufficient data per table to demonstrate your queries are correct c. The final project should contain tables for a data mart 4. Queries: a. HR Manager wants to see the following results: i. List of all Employees and their contact details (sorted by Last Name, then First Name) ii. Total number of sessional Academic Staff per location iii. Total number of full-time Academic Staff per location iv. List of academic staff, the total hours worked per Academic Staff given the date range (the HR Manager will determine the start and end date). v. List of academic staff, their subject and total hours worked per week per subject. vi. List the names and contact information of the academic staff per location (location will be determined by the HR Manager). What do you need to submit in your final project? You need to upload in your e-portfolio your final project containing the following: Assignment 1: • A table containing the element for an operation database • .MWB file containing the E-R Diagram, a backup of the tables you created, data added into the tables, and queries • Document files containing your proposed data base schema diagram and a discussion (justification) of your proposed design. Assignment 2: • A table containing the elements for data Mart • .MWB file containing the E-R Diagram, a backup of the tables you created, data added into the tables, and queries • Document files containing your proposed star schema diagram and a discussion (justification) of your proposed design. Assessment Details: Week 1-5 Mark Due Date Week - Practical 1. Logical database design 2. physical database design (creating a EER diagram using Workbench) 3. Creating Database Schema, data, Views and Indexes 4. Creating queries 25 Week 5 Week 6- 12 –Data Warehousing Design and Data Marts 1. Create a Star Schema using Workbench 2. Creating Database Schema, data, Views and Indexes 3. Creating queries 4. Practical: Upload final practical in e-portfolio (Final Project) 25 Week 12