Database or Spreadsheet: Which Is More Practical - Research Paper Example

Database or Spreadsheet - Which is More Practical? Faculty Table of Contents I. Introduction II. Database and Spreadsheet III. Access and Excel - Similarity IV. Technical Comparison of Database and Spreadsheet V. Access Vs. Excel i. When to Use Access ii. When to Use Excel VI. Conclusion References I. Introduction "The most common task in Excel is the management of lists. If you can store your data logically in a single worksheet or table, then do it." "In Access, you have a primary key. In Excel, you have a cell reference. In life, you have a social security number. Its the best unique identifier you have." (Nelson 2006) It is a challenging task to choose whether to manage data with database or spreadsheet. Both database and spreadsheet allow user to enter, maintain, manipulate, and print data. Both database or spreadsheet software is inexpensive and easy to purchase. Now question comes in mind that which one is better for use. This paper will look on this aspect of which is more practical, database and spreadsheet. In this paper, the main focus will be on the two software (programs) database or spreadsheet as Microsoft Access and Microsoft Excel (MS Access and MS Excel). In section two of this paper, overview of database and spreadsheet is given. Section three of this paper discusses various types of similarity that both MS Access and MS Excel have. In section four of the paper, the technical analysis of the software’s will be presented. And at last in section five, which software MS Access and MS Excel can be used in which situation will be discussed. II. Database and Spreadsheet A spreadsheet is a rectangular table of information. It means for the format used to present bookkeeping ledgers. The most common use of spreadsheets is to manage basic information such as telephone numbers, employee names, financial data, calculations, addresses and many more other things. Spreadsheets can store data but in actual it is not performs that job as easily as a database can perform. In actual, a spreadsheet is not considered as a database. It some thing like a robust, multifaceted calculator and data analyzer that incidentally can perform some database functions. As spreadsheets are intended to handle numerical data, they will also handle text, but they are not suited to handling complex queries and intradata relationships, so they are not used in data handling systems. Various types of spreadsheet programs that are used are KSpread, OpenOffice.org Calc or Microsoft Excel. Microsoft Excel and other spreadsheet software programs are not database management systems. They stores data in rows and columns called worksheets and areas within the worksheet, where the rows and columns intersect are called cells. Database in general is a collection of data arranged for ease and speed of search and retrieval. Database is also called as databank. In technical perspective database can be defined as a set of related files that is created and managed by a database management system (DBMS). Now these days, database management system can manage any form of data. The forma of data may include text, images, sound and video. Today some of the common database management systems that are used are Oracle, Microsoft SQL Server (MSSQL), Microsoft Access (MS Access), MySQL and DB2. Microsoft Access and other relational database software programs store data in tables. Although it look similar to worksheets but they function quite differently. Microsoft Office Excel: It is a spreadsheet program. Microsoft Excel is written and distributed by Microsoft. It is used mainly in computers that uses Microsoft Windows operating system or Apple Macintosh computers. It features an intuitive interface and capable calculation and graphing tools. Because of aggressive marketing, Microsoft has made Excel one of the most popular spreadsheet applications that are used these days. It is the dominant spreadsheet application available for Microsoft Windows and Apple Macintosh platforms. The latest release of Excel is Microsoft Office 2007 Excel (beta version). Microsoft Office Access: It is a relational database management system from Microsoft. Microsoft Office Access comes packaged with Microsoft Office Professional which combines an interface with the relational Microsoft Jet Database Engine with. Microsoft Access can use/extract data stored in Access or jet, Microsoft SQL Server, Oracle, MySQL or any ODBC-compliant data container. Software developers and students use it to develop application software. This is the database that is used by most of the unskilled developer (students) for building simple application programs. Microsoft Access supports some object-oriented techniques but it can not be considered as a fully object-oriented development tool. III. Access and Excel – Similarity In previous section spreadsheet and database was discussed. Also in previous section an overview of Microsoft Access and Microsoft Excel was presented. Now In this section the similarity that Access and Excel contains will be discussed. In both Access and Excel user can run queries to sort and filter data, as well as user can run complicated calculations to derive the information they want. One important similar feature that Access and Excel both contains is use of Microsoft PivotTable and Microsoft PivotChart views to work with data interactively. Both uses forms to add, update, delete and navigate user data. Access and Excel both are capable of report generations on data and view in multiple formats. One of the important feature that both contains is to connect to external data and view, query, and edit it without having to import. They both can imports data form external databases and also capable of export of data too. Both can create web pages to display data as read-only or to access it in an updatable format and can create a Microsoft Word mail merge (Nelson 2006). Both Microsoft Access and Microsoft Excel organize data in columns. Columns are also called as fields and store a particular kind of information, or data type. In both programs at the top of each column, the first cell is used to label the column. There is one difference in terminology. What is called a row in Excel is called a record in Access. IV. Technical Comparison of Database and Spreadsheet There are several technical issues on the basis of which Databases and Spreadsheets can be compared. Some of the technical aspects that will be used for comparison are referential data integrity, data redundancy, data integrity and validity, limiting data view, performance and capacity, convenient data entry, reports, programming, multiuse, and security (Database and Spreadsheet nd.). In the following paragraphs these technical issues are explained in detail. Referential Data Integrity and Data redundancy: In this point of view Access get preferences over Excel. Because in case of Access referential data integrity is there and Data redundancy problem can be solved by using multiple number of tables. Data Integrity and Validity: In this point of view Access get preferences over Excel. Limiting Data View: In case of Excel, it displays all the rows and columns of a worksheet (table) which is annoying in case of very large data sheets. Although it can be filtered in Excel. But sometimes it can lead to mistakes when calculating sums, averages, etc. this problem is solved in case of Access by offering queries, forms and reports. Performance and Capacity: Excel containing large data sets takes times to open. It loads lots of data to the computer memory while opening and most of the data loaded are probably unnecessary for the moment. Access unlike spreadsheets load data from computer storage only when needed. Data Entry: Although latest editions of Excel enable users to design data entry forms. But such forms are useful only if data cannot be conveniently displayed in tabular view. Fields for data entries are placed loosely within Excel and very often are not secure against the users intentional or accidental intervention. Reports: Access enable grouping, limiting and summing up data in a form of a report. Excel is usually printed in a form of small tables without fully automatic control over page divisions and the layout of fields. Programming: Applications for creating databases often contain full programming languages. Data processing within a spreadsheet is usually done via a graphical users interface which may slow down the data processing speed. Database is capable of working in background, outside of graphical interfaces. Multiuse: It is hard to imagine a multiuse of one spreadsheet. Databases have been designed mainly with multi-user access in mind. Even for the simplest version locking at a particular table rows level is possible, which enables easy sharing of table data. Security: Although spreadsheets can be secured with the help of password, but it is not useful and the password can be cracked as it is sometimes stored in the spreadsheets file itself. In case of database the security is more and the user can access those sections (tables) or views for which the user is assigned. (Database and Spreadsheet nd.) V. Access Vs. Excel There are two types of data, flat or non-relational and relational data. If data can be stored in a single table or worksheet and each column is directly related and need only reside in a single (flat) table, then it is a case of flat or non-relational data. Such types of data can be stored in any application Access or Excel. If there is a relationship exists for columns in one table to other than this is a case of relational data and it is preferred to store d in Access or other RDBMS applications. Also Both Access and Excel provide unique identifiers. In access it is termed as primary key, whish uniquely identifies each record of the database tables. In case of Excel each row is numbered and the column is denoted by letter, cross section of this is termed as cell and example of this is as E7. In following sections the case will be discussed when access or Excel should be preferred for storing data. i. When to Use Access Access is used in cases where the database is relational, changes over the time and the size of the database is large enough. Suppose user have to store complex dynamic data and to make report on it, now in this case the data may changes over time and have mutable table (relationship on these tables) that is not possible to store easily in Excel. By using Access user can generate reports easily in such type of database even if the database changes regularly. Another case when user need to be able to create custom reports on the hurry. In such scenario, the user can design new queries easily in order to create a custom report. Another case when Access is preferable over Excel is when data stored for a period of time. It will be easier for easier to user to take the data later in any format in excel or any other database when it is stored in Access (Kevin 2006). Below is the summary of the cases when the data should be stored in Access: If there is a requirement of a relational database to store data (multiple tables ) If there is a need to add more tables in the future If very large amount of data (thousands of entries) is to be stored. If the data that is mostly of the long text string type as compared to numeric which can not fit in Excel. If data rely on multiple external databases to derive and analyze (Import and Export of data from on database to other). If there is a need to maintain constant connectivity to a large external database such as one built with Microsoft SQL Server. If the data required to run complex queries. If many people working in the database and want robust options to expose that data for updating. (Nelson 2006) ii. When to Use Excel Excel is used in the cases when the data is flat. Suppose user has to create a report only once from a set of data, then in this case Excel will be preferred. Second case is when the relationships is not going to change, this means that the structure of the data will remain s same so Excel can be used in this case. Another case when Excel can be used is when the report format of the data is not going to change (Kevin 2006). Below is the summary of the cases when the data should be stored in Excel: If there is a requirement of a flat or non-relational view of data If the data is mostly numeric. If user want to run primarily calculations and statistical comparisons on data. If the dataset is manageable in size (not more than 15,000 rows). (Nelson 2006) VI. Conclusion In conclusion, to select database or spreadsheet depends upon the case whether the data is relational or not. If data can be stored in a single table or worksheet, then one can probably get by using Excel or some other spreadsheet software. If data needs to be stored in more than one table, then one can need a relational database program such as Access or a similar database management program. Another important scenario is the period of time, report generation and size of the data on which selection of database or spreadsheet depends. References Database and Spreadsheet. Introduction to Databases. Retrieved on 29 November 2006 from http://docs.kde.org/development/en/koffice/kexi/database-and-spreadsheet.html Nelson, E. 2006. Using Access or Excel to manage your data. Retrieved on 29 November 2006 from http://office.microsoft.com/en-us/help/HA010429181033.aspx Kevin 2006. Access Vs. Excel: When to Use Excel. Retrieved on 29 November 2006 from http://www.workplacelife.com/2006/05/09/access-vs-excel-when-to-use-excel/ Kevin 2006. Access Vs. Excel: When to Use Access. Retrieved on 29 November 2006 from http://www.workplacelife.com/2006/04/27/access-vs-excel-when-to-use-access/ Microsoft Excel. Retrieved on 29 November 2006 from http://en.wikipedia.org/wiki/Microsoft_Excel Microsoft Access. Retrieved on 29 November 2006 from http://en.wikipedia.org/wiki/Microsoft_Access Read More