Comparison of Database Management Systems

From virtualMV2015wiki
Jump to: navigation, search
 Home  DBMS <

Comparison of Database Management Systems

Microsoft Access

Creating a Report in Microsoft Access 2007

Overview

  • A part of Microsoft Office
    • “Off the shelf”
    • “Shrink wrapped”
    • Inexpensive/standard on most computers
    • Even Macs have a version
  • Can create complex databases
    • Needs experts (skill)
    • Build/construct a database

Typical users

  • An MS-Access database is typically used in small companies or workgroups with 1-50 users

Features

Microsoft Access has the following features

    • Has graphical tools for creating databases
    • Uses tables, forms, reports, queries, macros etc.
    • Provides wizards to help build tables forms etc.
    • Can interface to other DBMS’s using ODBC
    • Suited to small to median size business
    • Provides security measures
    • Provides a Data dictionary
    • Has compact and repair facilities
    • Can be spilt to allow different views of the database
    • External data can be imported into Access
    • Can create web pages based on the database with ease
    • Has built in Macro functions
    • Uses SQL
    • Can customise forms, reports etc by using Visual Basic Application programming
    • Provides Add in controls (e.g. calendars)
    • Can be merged into word and analysed with Excel etc.

Issues

  • Microsoft access has the following limitations
    • Security. User level security is difficult to do. For example, attaching group security attaches to the MS-Access software rather than the database (mdb) file.
    • Tuning. Does not have the ability to split over multiple Hard Drives, multiple CPUs or to place tables into memory.
    • Locking. Basic handling of concurrent users
    • Backup and recovery at basic level (Backup tends to be of the whole mdb/mdbx file, cannot do incremental backups)
    • ANSI SQL standard often doesn't work - Access has it's own modified version of ANSI SQL

MySQL

Figure 2. MySQL Console in WindowsXP

Overview

  • MySQL is a database engine
  • Has a command line interface that allows the creation of databases and tables, plus using SQL the ability to work with data *add, modify,delete, and view. (A GUI application is available to help write the SQL).
  • Requires "Front-end" applications to access it for end -users. For example, on a local PC/Workgroup this may be C#, (or even Microsoft Access - Forms and Reports), on the Internet PHP or Microsoft ASP.Net.

Typical users

  • An MySQL database can be used by small companies or workgroups, through to very large Internet databases with large numbers of users
  • Examples
    • MediaWiki (wikipedia),
    • Moodle - (A learning management system)).

Features

  • Speed (One of the fastest databases available)
  • Ease of use: when compared to larger databases such as Oracle
  • Uses standard SQL
  • Capability. A multi-threaded server allowing many clients to connect at the same time
  • Fully networked for the Internet with built in security
  • Portability: Runs on a many operating systems and different hardware (from local PC to major Internet Servers)
  • Small size (when compared to other large databases (e.g. Oracle)
  • Availabliity and Cost: Open Source (Free in most situations to use)
  • Open distribution and source code: You can check how it works – if you have the knowledge.
  • Can interface to other DBMS’s using Open Database Connectivity (ODBC)

(Dubois, 2009)[1]

Issues

  • Security
    • MYSQL security is based on the Access Control List (or ACL) for all connections, queries and many other operations that a user can perform. This means that different users will have varying levels of access to certain databases and tables and that they will be limited to what they can do. For example, a user with full privileges to a database will be able to perform operations such as SELECT, DELETE, UPDATE and INSERT, while a user with limited privileges would only be able to use the SELECT operation. (Leidago, 2006)[2]. This is done through the GRANT and REVOKE commands.
  • Backup
    • Use mysqldump to make a copy of the database.
  • Restore
    • Use SOURCE to run a mysqldump script.
  • Integrity
    • Transaction boundary support. Melonfire (2003)[3] has a discussion on transaction boundaries in MySQL

MS-SQL

PostgreSQL

Source: Microsoft SQL Server. (2011, June 7)[4]

Comparison of DBMSs

Source: Comparison of relational database management systems. (2011, June 10)[5]

Icon References.png References

  1. DuBois, P. (2009) MySQL, (4th Ed.) Pearson. ISBN: 0-672-32938-7
  2. Leidago(2006) MySQL Security Overview . Retrieved August 24, 2009 from http://www.devshed.com/c/a/MySQL/MySQL-Security-Overview/
  3. Melonfire(2003) Using Transactions In MySQL (Part 1) .Retrieved August 24, 2009 from http://www.devshed.com/c/a/MySQL/Using-Transactions-In-MySQL-Part-1/
  4. Microsoft SQL Server. (2011, June 7). In Wikipedia, The Free Encyclopedia. Retrieved 02:47, June 24, 2011, from http://en.wikipedia.org/w/index.php?title=Microsoft_SQL_Server
  5. Comparison of relational database management systems. (2011, June 10). In Wikipedia, The Free Encyclopedia. Retrieved 02:46, June 24, 2011, from http://en.wikipedia.org/w/index.php?title=Comparison_of_relational_database_management_systems

Comparison of Database Management Systems. (2017). In virtualMV's ( Michael Verhaart ) wiki. Retrieved December 17, 2017, from http://www.virtualmv.com/wiki/index.php?title=Comparison_of_Database_Management_Systems    (zotero)