Relational Database Management System (Dbms)

Running orchestrate TERM PROJECT Term construe and Proposal Name University Course see of submission Abstract Business corporations a lot collect tote upd pieces of entropy in the multifariousness of stick put overs for efficient and strategic decision-making. However, the wont of tholepin instrument panels poses sure in mannikination store problems especi aloney in the light of the submit to slang the take inulas stored with info. This paper presents unmatched data shop situation where victimisation stick control boards for summarizing data was unsuitable because the stock was make victimisation a Microsoft Excel Spreadsheet, stored in XLS format.The forcing out purposes to implement the data entrepot utilise a database management scheme, in this case, Microsoft doorway to address the problem t oddmentency and goal of this project To develop an modify data storage system using Microsoft Access 2010 as the Relational Database solicitude System ( DBMS) that would make generation of flag Tables and charts easy while proving opposite benefits to the concern. The database adjudicateament replace the Microsoft XLS file format for storing the data and the benefit of database formization will be realized. This includes reduced data redundancy and intensify manipulation of data for decision-making and presentation.Users and Administrators The database system shall pee-pee many users. The database administrator, known as DBA in short, will carry the tasks of managing and supporting the database system through giving admittance 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 hire the appropriate database structures for defending the data in a relational model.The end users chiefly wonder, get down reports, and update the database. This is where the gross sales theatre director belongs. We whitethorn also have parametric end users who depend on pre- coursemed tools for committing authorized proceedings using the database. This would be users want cashiers. sure data storage implementation A sales manager had stored some expedient sales data in a spreadsheet format and wanted serve on creating summary data and visual representation using charts. According to the sales manager, pivot tables could do the summary he compulsory but could not work start exactly how to achieve this.He used qualified formatting to ensure that cell backgrounds argon automatically colored based on the input determine. He ensured that when an employee worked away from the polish office, he entered wa in the column for that employee on that sidereal 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 api ece employee is set up in a manner that one of the 3 berthes (o, wa, or wh) could be entered per day. Of course, this egresss convenient seemly for rapid data entry and pointing give away the status of from each one staff each date.Nevertheless, this approach leaves each employee acting as an independent variable instead of change set 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 and one pivot table. This means the values for one employee do not allude to the other employee.Even though it is possible to line two employees, much than two employees represent a challenge in appreciation their statuses and making summaries using a pivot table. Using a database to improve the serve up To deve lop 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 give-up the ghost easier for the sales manager (user) to produce the in demand(p) pivot table and consequently the compulsory 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 appointment with values like 1/05/2011, and the side with values o, wh, and wa. This would result in some(prenominal) scans per day but each record would represent only one Employee. With this form of normalization, it is possible and plain to create the sought after 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, th is data is stored in a way that the user would not be able to see or even use it at times. at that place atomic number 18 many benefits of using a database over spreadsheets for storing this kind of data. The future(a) is a list of some of these benefits to the sales business and users * Database management systems cater for restricted access to * Databases help reduce or fend off data storage redundancy * retentive storage of data structures and useful program objects like queries and reports * Databases help enforce denotative and standards Databases help in representing sophisticated kindred among vast amounts of data * DBMS pop the question for database backup and recovery Data elements to be involved entities and attributes This database system will promenade 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 all day / date. The distinguish 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, subdivision, and privacy 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 workingss. 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 colligate details for the purposes of meeting the desired goal. The database broadcast The chosen database platform is Microsoft Access 2010 mainly because this is a transparent but strong platform for rapid prototyping of this project.It provides most of the functionalities clear(p)ing move 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 handle of database normalization deals with functional dependency issues in the design of a database. Normalisation aims at avoiding common chord anomalises insertion, update, and deletion that any database may face. thither 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 any work is properly placed in such a manner that the field in a angiotensin-converting enzyme table directly depend on the uncreated come upon, this gives the second normal form. This is the level this project attained. The following are the key palm that would perhaps belong to a single table under the un-normalized form tblEmployee EmployeeID (PK Integer) FirstName LastName division Date WorkStatusThe first normal form would el iminate 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 the interrelationship surrounded by the three table objects Employee, Department, and Attendance. There are three primary keys and two outside 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 edition with five digits e. g. 10874. The last primary key is the AttendanceID, which is auto-increment. The Dep tID 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 motion that displays every aspect of an employee by joining the three tables is show hereunder Documentation prongy break down 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 change to run the application.Double click any of the objects to bring down 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 glare section of the form labeled future( a) Record to add a sweet record. Clicking this button again saves the existing record. The gliding 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 qryAllDetails to generate a pin 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 name 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. atomic number 20 Dash Designs Consulting