Relational Database Management System (Dbms)

Running head: TERM PROJECT Term Project and Proposal Name: University: Course: Date of submission: Abstract 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 w ill 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 trans actions 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. It would then become easier for the s ales manager (user) to produce the desired pivot table and consequently the needed summary data to aid decision-making.This paper implements the solution using Microsoft Access 2007. In this case, the table would have each Employee with the values such as Al and Clara, the Date with values like 1/05/2011, and the Status with values o, wh, and wa. This would result in several records per day but each record would represent only one Employee. With this form of normalization, it is possible and plain to create the desired pivot table. In fact, pivot tables generated from this normalized data would be able to answer questions like â€Å"which employee worked away on a certain day of the month? Unfortunately, though, this data is stored in a way that the user would not be able to see or even use it at times. There are many benefits of using a database over spreadsheets for storing this kind of data. The following is a list of some of these benefits to the sales business and users: * Dat abase management systems provide for restricted access to * Databases help reduce or avoid data storage redundancy * Persistent storage of data structures and useful program objects like queries and reports * Databases help enforce referential and standards Databases help in representing sophisticated relationship among vast amounts of data * DBMS provide for database backup and recovery Data elements to be involved: entities and attributes This database system will center on capturing employee details with respect to whether they worked on a certain day or not, and if they did work, in which mode. The database will have each record represent a certain employee every day / date. The key table entities would be Employee, Date, and Status. Contents of that table may include Employee Number, Employee name, Employment Date, Age, Gender, Contacts, Job Title, Department, and Retirement Age.These two tables would be helpful in producing the required information the sales manager needs for this project: to know who worked on what data and the mode of working. In addition, this would help to know who took leave when and how many employees took off in a certain day or month for instance. This database would have so much but we restrict ourselves to Employee related details for the purposes of meeting the desired goal. The database platform The chosen database platform is Microsoft Access 2010 mainly because this is a simple but powerful platform for rapid prototyping of this project.It provides most of the functionalities enterprise level systems like Oracle provide and perhaps at higher levels it may be necessary to upgrade the database to Oracle, SQL Server, or MySQL. Database normalization and design of actual tables The process of database normalization deals with functional dependency issues in the design of a database. Normalisation aims at avoiding three anomalises: insertion, update, and deletion that any database may face. There are several normal forms but the first three are the most important.In this context, the Employee details regarding department and status of work are independent entities and require separate tables. This gives the first normal form. When every field is properly placed in such a manner that the fields in a single table directly depend on the primary key, this gives the second normal form. This is the level this project attained. The following are the key fields that would perhaps belong to a single table under the un-normalized form: tblEmployee EmployeeID (PK Integer) FirstName LastName Department Date WorkStatusThe first normal form would eliminate recurring values such as WorkStatus and Department column. The results in having two separate tables for recording employee details and attendance details as follows: tblEmployee EmployeeID (PK Integer) FirstName LastName tblAttendance AttendanceID (PK Autonumber) Date WorkStatus tblDepartment (PK Number) DeptID DeptName The following Entity Relationship Model shows t he interrelationship between the three table objects: Employee, Department, and Attendance. There are three primary keys and two foreign keys.The primary key for table Department is labeled DeptID for uniquely identifying each department. The field takes a maximum of one digit (1 – 9) hoping the company does not have more than 9 departments. The field is auto-increment meaning it is auto-generated during registration of a department. The EmpID is the primary key for Employee entity and is Text with five digits e. g. 10874. The last primary key is the AttendanceID, which is auto-increment. The DeptID in tblEmployee is a foreign key to link tblEmployee with tblDepartment. EmpID in tblAttendance is also a foreign key for association with tblEmployee.This way, all the three tables are related and referential integrity and update integrity enforced. For the test data entered, the following pivot table employs filters based on the mode of working â€Å"wa†, â€Å"wh†, or â€Å"O’†. Filtering by â€Å"wa† gives: Moreover, a query that displays every aspect of an employee by joining the three tables is show hereunder: Documentation Double click the file â€Å"Employee Attendance DB. accdb† to open the database (Note: file opens with Microsoft Excel 2007/2010 only]. You may have to click on Options button that appear at the Toolbars section and choose â€Å"Enable† to run the application.Double click any of the objects to begin interacting with the object (Table, Query, or Form). To use a form to input data, double click the form and click right arrow key button at the lower section of the form labeled â€Å"Next Record† to add a new record. Clicking this button again saves the existing record. The navigation tool is shown hereunder: This is a simple database system but caters for the needs of the sales manager in checking the attendance of the employees working in the department by using the query â€Å"q ryAllDetails† to generate a Pivot table showing the attendance of employees in the Sales department only, for instance.This is achieved by double-clicking the query > right-clicking on the tab representing the open query > selecting PivotTableView > Selecting the appropriate fields from the PivotTable Field List by double-clicking them > and finally selecting â€Å"Sales† using the list-box under the heading â€Å"DeptName† in the generated PivotTable. There is much more power in using this database than is possible using Excel. References Maletsky, J. & Rafael, S. (2008). Microsoft Access 2007 Analyze Data with Pivot Tables. California: Dash Designs Consulting