Relational Database Management System (Dbms)

Running head TERM PROJECT Term Project and Proposal Name University Course Date of con take a leakity Abstract Business corporations often require summarized pieces of selective information in the form of pivot tables for efficient and strategic decision-making. However, the enforce of pivot tables poses certain data reposition problems especially in the light of the need to aim the formulas stored with data. This paper presents one data store 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 circumspection governing body, in this case, Microsoft Access to manage the problem Purpose and goal of this project To develop an mitigated data storage governing body using Microsoft Access 2010 as the Relational Database Management system (DBMS) that would make generation of turn 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 m any(prenominal) users. The database administrator, known as DBA in short, will carry the tasks of managing and documentation the database system through giving gateway 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 as well as have par ametric 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 optical representation using charts. According to the sales manager, pivot tables could do the summary he needed b atomic number 18ly could not work out exactly how to bring home the bacon this.He used conditional formatting to ensure that cell backgrounds argon automatically colored based on the input value. 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 lag 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 come-at-able to issue 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 Microsof t Access, Microsoft SQL Server, Oracle, or MySQL database management system. It would then become easier for the sales manager (user) to produce the desired pivot table and consequently the needed summary data to aid decision-making.This paper implements the resolving 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 side with values o, wh, and wa. This would result in several enters per day but each record would represent only one Employee. With this form of normalization, it is possible and plain to lay down 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 sto ring this kind of data. The hobby is a list of some of these benefits to the sales business and users * Database 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 impose 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 retreat 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 programme 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 anoma lises 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 handle 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) Da te WorkStatus tblDepartment (PK Number) DeptID DeptName The following Entity Relationship ride shows the 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 tagged 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 too 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 followi ng pivot table employs filters based on the mode of working wa, wh, or O. Filtering by wa gives Moreover, a query that displays every verbal expression 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 qryAllDetail s 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 report 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