Term Project and Proposal
Date of submission:
Business corporations often require summarized pieces of data in the form of pivot tables for efficient and strategic decision-making. However, the use of pivot tables poses certain data storage problems especially in the light of the need to have the formulas stored with data. This paper presents one data storage situation where using pivot tables for summarizing data was unsuitable because the storage was done using a Microsoft Excel Spreadsheet, stored in XLS format. The project purposes to implement the data storage using a database management system, in this case, Microsoft Access to address the problem
Purpose and goal of this project
To develop an improved data storage system using Microsoft Access 2010 as the Relational Database Management System (DBMS) that would make generation of Pivot Tables and charts easy while proving other benefits to the business. The database will replace the Microsoft XLS file format for storing the data and the benefit of database normalization will be realized. This includes reduced data redundancy and enhanced manipulation of data for decision-making and presentation. Users and Administrators
The database system shall have many users. The database administrator, known as DBA in short, will carry the tasks of managing and supporting the database system through giving access rights to other users, acquisition of database resources, and coordination and monitoring of the users of the database. The actual developer or designer of this database would identify what important data needs to be stored in the database system, and select the appropriate database structures for representing the data in a relational model. The end users mainly query, generate reports, and update the database. This is where the sales manager belongs. We may also have parametric end users who depend on pre-programmed tools for committing certain transactions using the database. This would be users like cashiers. Current data storage implementation
A sales manager had stored some useful sales data in a spreadsheet format and wanted help on creating summary data and visual representation using charts. According to the sales manager, pivot tables could do the summary he needed but could not work out exactly how to achieve this. He used conditional formatting to ensure that cell backgrounds are automatically colored based on the input values. He ensured that when an employee worked away from the office, he entered wa in the column for that employee on that day. When an employee worked at home, he entered wh, and when he/she was off-duty, he entered o against that employee.
The problem with this data storage is that each employee is set up in a manner that one of the three statuses (o, wa, or wh) could be entered per day. Of course, this appears convenient enough for rapid data entry and pointing out the status of each staff each date. Nevertheless, this approach leaves each employee acting as an independent variable instead of varying values or classes of the variable Employee. The storage, for instance, disregards Al, Clara, or Joel as the Employee entity, not both. This is similar to storing data in a report generated from a database instead of storing such data in a database table.
Consequently, this poses a challenge when attempting to summarize data by means of just one pivot table. This means the values for one employee do not relate to the other employee. Even though it is possible to distinguish two employees, more than two employees represent a challenge in understanding their statuses and making summaries using a pivot table. Using a database to improve the process
To develop an improved data storage system, we would store this data in one of the popular relational databases such as Microsoft Access, Microsoft SQL Server, Oracle, or MySQL database management system....