This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.
This Report reviews three of different types of Database Management Systems (DBMS). They are Microsoft Access 2007, Microsoft SQL Server 2005 and Oracle11g.
The report identifies the strengths and weakness of each DBMS systems and examines their general features, usability and capability and makes comparison with each others. For the weakness of each DBMS some improvements will be provided.
The report introduces one NZ company that uses each of the DBMS systems and an explanation of the reasons is included.
Some WebPages is used in this report that can be found in the references.
Database1: Microsoft Access 2007
Ms Access 2007 specifications:
Ms Access 2007 is based on the windows desktop relational database management system. which is developed by Microsoft. Access 2007 provides 7 objects: Table, query, forms, reports, pages, macros, modules and uses the 7 objects to build up a database system.
Ms Access 2007 provides wizard, generator, templates to help user to create database, such as interface design, data query and report generation. These offers bring convenience to user to build up a database system with multiple functions but without coding.
Below is a list of some of the main features:
- Friendly and easy user interface
- Lowest Total costs of ownership
- Support SQL-92
- Support Open Database connectivity
- PDF and XPS support.
Ms Access 2007 is a simple software with intuitive user interface. It provides a completely new experience with the Office Fluent user interface, new navigation pane, and tabbed window views. No deep database knowledge is required, with its Fluent user interface and interactive design capabilities, any user can start tracking information and creating reports to make more informed decisions.
Because of the simplicity of Ms Access 2007, the total costs of ownership of this database product are relatively low. The main cost consists of buying the software. But normally the software is bundled with Ms office 2007 system. The software is very simple and has a well explanatory user guide, therefore even a basic computer user with limited knowledge in database still can operate it without the needs in attending costly training courses.
Ms Access 2007 supports SQL-92 for scripting. As Access is standalone application, all the forms, queries are stored in the database and can only be used within the application scope.
Using Access powerful Dynamic data Exchange and Object Linking and embedding, the characteristics, bitmap, sound, Excel forms, word documents can be embedded in the data tables. It can create dynamic databases. Access also link the dynamic data on the network. User can generate HTML document by database object and easily build internet/ Intranet applications.
With Office Access 2007 can save a report as a Portable Document Format file (PDF) or in XML Paper Specification (XPS) format for printing, posting, and e-mail distribution. By saving report as a PDF or XPS file can capture report information in an easily distributed form that retains all the formatting characteristics and yet does not require others to have Office Access 2007 to print or review the report.
Ms Access 2007 has its own authentication method. It provides a simple password protected mode for a database file. Therefore each time a password is required in an attempt to open a file.
- Security Issues
- Concurrent issues
- Limited caching feature
- Backup / recovery issue
- Capacity issues
There is no other extensive security protection features available in Access. And access cannot be used for network service user and they have to take network file- sharing. Users need to write data by sharing writing. Therefore user can overwrite the original database by file replacement which will cause severe security problems.
Access has poor support in concurrency, so it is only suitable to use in a small scale project with a few concurrent users of the database.
The caching feature provided by Access is relatively limited and external access to the database requires drivers such as OLEDB or ODBC. The overall processing speed of the database is relatively low, and depending on the speed of the underlying file system.
There is no integrated backup/ recovery mechanism with DBMS itself, so this can only be achieved by using external methods such as backup of the main database file or storage the file on RAID enabled volume.
Because Access is a single file database, data is only stored in a central place, and the total amount of data is limited to max size of 2GB. So if the database is used for several years, one has to start to worry if the database is full.
- Increase the capacity of the database so that it can store more data.
- Enhance the security to make the database safer and more professional.
Database2: Microsoft SQL Server 2005
Ms SQL Server 2005 specifications:
Ms SQL Server 2005 is a mid-range database product of Microsoft. Unlike Ms Access, SQL server is a pure database. So end users cannot use the product directly and have to go through an application developed by third parties. Ms SQL Server 2005 severs as a data layer in many software development projects and it comes with certain useful yet easy to use tools, e.g. Management Studio and Query Analyzer for developers.
Ms SQL Server 2005 is a comprehensive, integrated, end- to end data solutions. It allows enterprise user to manage enterprise data in a safe, reliable and efficient platform.
Its main features are as follows:
- Uses T- SQL Language
- Provide high performance
- Has backup function and fast recovery
- Database Mirroring
- Data Security and Data integrity
Ms SQL Server 2005 uses Transact- SQL as scripting Language, it is used in database scripting objects such as stored procedures and triggers.
Ms SQL Server 2005 also supports integration with Ms.NET framework through the SQL CLR component. This allows the SQL Server to store procedures to execute codes which is written in NET. Framework supported language such as C++ . NET. SQL server that is based on the SQL-92 standard.
Ms SQL Server 2005 is designed to cope with multi-user environment to perform concurrent access. It uses some modes to maintain integrity and resolve deadlocks. And it also uses reader / Writer locks in multiple levels to ensure that the maximum throughput of data is achieved.
Ms SQL Server 2005 is ACID compliance. It supports transactions and logging to ensure that each operation is either totally completed or nothing is done when failed and no intermediate is written to the database.
Ms SQL Server 2005 has backup functionality integrated to allow tape backup or replication with other SQL server instances to provide redundancy.
SQL Server 2005 improves the availability of SQL Server databases with a new and faster recovery option. Users can reconnect to a recovering database after the transaction log has been rolled forward. Earlier versions of SQL Server required users to wait until incomplete transactions had rolled back, even if the users did not need to access the affected parts of the database.
SQL server 2005 significantly enhances the capabilities of log shipping by providing a database mirroring option. Database mirroring allows continuous steaming of the transaction log form a source server to a single destination server.
Ms SQL Server 2005 has a two modes in authentication: the database native authentication mode or windows integrated authentication mode. Both of them allow users to be group in different roles with specific rights to access different objects in the database.
Auditing is also supported and is integrated with the windows Event log.
Data encryption is natively supported by SQL server, in some case encryption is completely transparent to developers.
- Limited platform
- User interface
- Total cost of Ownership
- Limitation of peer-to- peer Transaction Replication
- All participating database do not contain the identical schema
- Object names, object schema, and publication names are not indented among the participating databases.
- Row and column filtering is not supported in peer-to -peer replication.
The primary drawback of Ms SQL Server 2005 is that it is only available on windows platform and does not offer cross platform functionality.
There are no available tools for developers to design user interface. Developers have to use other software such as Power Builder to develop user interface.
Due to the complexity level of the database, operating an application with Ms SQL Server usually requires a database administrator. And any new features being added to the application may also need the participation of capable database programmer. Therefore the Total cost ownership involved is not only limited to the licensing of the database product..
Ms SQL 2005 includes a new peer- to- peer transaction replication feature that improves support for data scale out using replication. However this feature has the following limitations:
- Add new tool or function for user interface design
- Cross multiple platform should be allowed
Database3: Oracle 11g
Oracle 11g specifications:
This Database does not only store data, it also implements management of information throughout its life cycle. Oracle 11g database is the database designed for grid computing. It is a high- efficiency, high- reliability, high throughput database solution.
Oracle 11g is only suitable for advanced/experience administrators and developers. It providers a variety of tools to support and maintain the database.
- Support all environment
- Uses PL/ SQL Language
- Data integrity and Data security
- Built for the grid
- Through standardization on low- cost serve and storage
- Via dynamic provisioning of all the database and application servers
- Through the end - to- end automation of day- to- day management tasks, allow a single administer to simultaneously handle hundreds of servers.
Oracle 11g database supports all operating system including Windows, Linux and Unix. It also supports all hardware configuration from small single processor machines to high-end SMP end environments. Cluster and Grid environments are also supported with the oracle Real Application Cluster option.
Oracle uses PL/SQL scripting language. It not only conforms fully to the SQL-99 standard, it also implements certain new features in SQL-2003. Oracle database covers most features of the SQL Server, such as pessimistic and optimistic modes for concurrency control, cache management and a wide range of indexing methods. All of these allow the developers to fine tune the database to achieve the best performance.
Oracle is ACID compliant and uses transaction and logging to ensure data integrity.
Data backup and replication is also available.
Oracle allows the administrator to apply permission at different level on database objects. It also allows stored/ transmitting data to be encrypted by using the transparent data encryption and other methods.
Grid computing is the coordinated use of a large number of low- cost servers and storage devices acting as one shared computer resource.
Oracle Database 11g allows users to adopt Grid computing in three easy steps with minimal investment, zero disruption and fast return on investment:
- Total cost Of Ownership is high
- Take longer to learn and it is not as simple.
- When crossing different operating systems, Oracle 11g does not perform data integrity as well as SQL server.
- Complex to manage
Oracle database have different versions that is suitable for different scopes. The main costs involved in ownership, besides the licensing costs, are mainly the costs in maintenance, administration, and hardware. Since oracle can be used to run in all different kinds of operating systems, the developer has to invest more effort to cope with different environments. As a result, continuous training is required and the costs of ownership can be out of expectation.
Need more professional technician.
Oracle requires application and schema design changes.
- Professional technician is needed to manage and use oracle 11g
- Reduce the cost so that more enterprise can use it.
Comparison of three DBMS
From the above discussion about the three types of DBMS, we can see that Ms Access uses single file database and the other two are using multi file database.
Ms access 2007 is lowest in total cost of ownership, after that is Ms SQL server 2005 and oracle 11g has the highest total cost of ownership.
Ms Access 2007 is for small database, SQL is for mid ranges of database and Oracle11g is suitable for big enterprise.
Oracle 11g has more secure database than SQL 2005, but Ms Access cannot be compared at this point with them because Access 2007 cannot provide more feature in this area.
Oracle 11g supports all operating system, but the other two only can be used in windows platform.
Both SQL 2005 and Oracle 11g are highly scalable and reliable database platforms that can run most demanding mission and critical enterprise application. However SQL 2005 is substantially easier to manage and is more cost effective than Oracle 11g.
Three NZ companies use each DBMS
- Ms Access client in Auckland : De Carol Ltd, 9 Melrose Rd, Mt Albert
- Ms SQL Server 2005 client in New Zealand: ASB bank
- Oracle 11g Client New Zealand: Air New Zealand
De Carol Ltd is a small company that makes curtain for houses. My friend Natasha is an accountant in the company. This company only has 9 staff including the boss. The user is only my friend and the boss, So using Access 2007 is the best choice for them, My friend builds the database and maintains the database even though she does not have a lot of IT knowledge.
ASB Bank built a Base II risk management data mart on Microsoft Serve SQL 2005.
Because ASB Base 11 Risk Management data mart belongs to mid range database system, and Ms server SQL 2005 can provide high availability, security enhancements and embedded reporting. Also Ms SQL 2005 is not too costly.
Air New Zealand is one of the biggest company in New Zealand, even though using oracle 11g is more costly, but oracle has performance high security and data integrity.
As it is a huge company, it needs the biggest database as well.
Each of the DBMS is introduced and the three NZ company's database are evaluated We obtain some tips on how to choose the database for any company in the future. For example, we should consider certain areas about the company requirement, the company operating system, capabilities size of database, total cost of ownership, and data security and data integrity etc.
- Microsoft Corporation (2007.). Access 2007 specifications. Retrieved August 23, 2008, from http://office.microsoft.com/en-us/access/HA100307391033.aspx
- Microsoft Corporation (2007.). Access projects and data access pages now use ANSI SQL-92 syntax Retrieved August 23, 2008, from http://office.microsoft.com/en-us/access/HA010345621033.aspx
- Wisdomforce (2007.). SQL Server 2005 Features Comparison. Retrieved August 23, 2008, from http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
- Microsoft (2007.). Ms SQL 2005 Vs. Oracle 10g. Retrieved August 23, 2008, from http://www.wisdomforce.com/dweb/resources/docs/MSSQL2005_ORACLE10g_compare.pdf
- Oracle (No date.). Oracle 11 g Edition. Retrieved August 23, 2008, from http://www.oracle.com/database/product_editions.html
- ASB Bank (2007.). ASB Bank built a Base II risk management data mart on Microsoft Serve SQL 2005. Retrieved August 23, 2008, from http://download.microsoft.com/documents/customerevidence/22658_ASB_Bank_SQLServer2005_Final.doc
- CIO (2008.). Air New Zealand using Oracle. Retrieved August 23, 2008, from http://www.cio.com/article/13191/Corporate_Moves_AirNew_Zealand_CIO_appointed_CEO_and_more