Use Sophia to knock out your gen-ed requirements quickly and affordably. Learn more
×

Commercial Databases

Author: Sophia

what's covered
This tutorial explores some of the most common commercial databases in seven parts:
  1. Introduction
  2. SQLite
  3. MS Access
  4. SQL Server
  5. Oracle
  6. MySQL and MariaDB
  7. PostgreSQL

1. Introduction

There are dozens of relational database management systems that are available for implementation. Some of the common commercial databases that exist include SQLite, MS Access, Oracle, SQL Server, PostgreSQL, MySQL, and MariaDB.

2. SQLite

SQLite is a self-contained database that is file-based and completely open-source. It is known for its portability, reliability, and performance even in low-memory environments. All the transactions are ACID compliant (atomicity/consistency/integrity/durability) even in cases where there’s a system crash or power outage. It is unique in that it is a serverless database. Most of the other databases require a server process that programs connect to when performing database requests. SQLite, however, accesses the database and reads from and writes to the database disk file directly. By doing so, there is no need to configure a server or to configure programs to connect to the database. If programs can access the disk that SQLite is on, they can access it directly. Limitations include limited concurrency, as only one process can make changes to the database at a time. Multiple processes can read and query from it. There is also no user management with SQLite. As long as the underlying operating system gives users permission to access the disk file, the user has access to the database.

3. MS Access

MS Access by Microsoft is not as popular as it once was, but it is still quite common. Similar to SQLite, the database is integrated into a single file that can be distributed. It has a well-developed toolkit that allows smaller organizations to build forms, reports, and other functionality within the database. It does not require any special hardware, although it only runs on Windows. An MS Access database is frequently thought of as a single user database, but it can support a limited number of concurrent users. One of the key benefits of MS Access is using it as a user interface front end for other databases that are more robust, like SQL Server or other databases that offer ODBC connection. It is not one that should be used with any sensitive or regulated data, if you need role-based security, if you need many concurrent users, or if you need high performance.

4. SQL Server

SQL Server is a more robust Microsoft product with a variety of editions like its smaller-scale Express, to an Enterprise edition that has high-end data center capabilities. SQL Server isn’t only a database engine, it has many other business intelligence tools. SQL Server uses its own version of SQL with Transact SQL. It is a tool that is highly supported, but it also comes at a high price. The Enterprise edition, for example, can cost upwards of $14,000 per-core license. Unlike MS Access, it can run on Linux, as well as on the cloud environment.

5. Oracle

Oracle has established itself as one of the top database systems used in enterprise solutions. Similar to SQL Server, there are many different variants of Oracle depending on the needs of the organization. The XE or Express edition is free for training purposes (Oracle only charges for Enterprise use). The cost for Oracle is one of the highest, and it can be quite complex to determine the end cost. Oracle also has PL/SQL, which is a procedural language that has more capabilities to group procedures and functions together into packages. There are endless tools and features available for Oracle, many of which are quite useful. Additionally, even with the high cost, it is extremely reliable, fast, and easy to support.

6. MySQL and MariaDB

MySQL and MariaDB are some of the most popular open-source databases. MySQL in particular is now owned by Oracle, and powers many of the largest databases including Twitter, YouTube, Airbnb, Facebook, and Netflix. MySQL was designed for speed and reliability. Once MySQL was taken over by Oracle, the original MySQL developers split off to create MariaDB as a fork of the MySQL code, as they worried that Oracle had planned to kill MySQL. MySQL does have to be installed on a server. There are a lot of third-party tools that can be used with MySQL/MariaDB, including phpMyAdmin.

7. PostgreSQL

PostgreSQL is one of the most advanced open-source relational databases. It was created with the goal of being standard compliant and highly extensible. Standard compliance is a big element of PostgreSQL that differentiates it from other databases. It is an object/relational database, meaning that although it is mostly a relational database, it does offer functionality like table inheritance that is implemented in object databases. PostgreSQL isn’t as widely used as MySQL due to a lack of additional third-party tools. However, PostgreSQL is compatible with many programming languages and platforms, making it much easier to migrate the database from one operating system to another, or integrate it within a given tool.


summary
There are many different common database options including SQLite, MS Access, SQL Server, PostgreSQL, MySQL, MariaDB and Oracle.