• 4 hours
  • Easy

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 5/25/20

Make Use of a Database Management System (DBMS)

Log in or subscribe for free to enjoy all this course has to offer!

Having looked at entities, attributes, and entity relationship diagrams, let's look at the features of a Database Management System.

What Is the Role of the DBMS?

database management system, or DBMS, is an all-inclusive management system for your database. It is the intermediary, or the interface, between the user and the existing data.

What does that mean exactly?

The DBMS is not the same as a database. The database management system is the software that allows the user access to the database and other objects. It provides security and maintains all of the objects, such as tables, within the database. Databases are usually referred to as the database inside the DBMS.

Examples of database management systems include SQL Server, Oracle, MySQL, PostgreSQL, and MariaDB.

What Are Common DBMS Features?

I’m glad you asked! Common DBMS objects are tables, stored procedures, triggers, views, synonyms, and security.

A DBMS also houses the logic to retrieve and manipulate data based on the SQL executed. The user doesn’t have to worry about how the data is retrieved since the DBMS handles the logic based on the executed SQL. Security features maintain passwords, as well as users and their level of access to the data or other structures within the database.

One other feature is the ability to schedule and run jobs at a specific time, which is often during non-peak hours, like overnight.

The screenshot below is the Object Explorer from SQL Server displaying many of the features available with this DBMS.

SQL Server DBMS
SQL Server DBMS with many of the management options

What exactly is a job?

Simply put, a job is an automation that runs an SQL statement, stored procedure, or other pre-written SQL code at a specified day and time interval.  Backing up data or transferring daily transaction information are two examples of how jobs would be used to automate repetitive tasks. Options can be set to email specific people when the job fails, succeeds, or completes. The completes option notifies the recipient when the job is done, whether it fails or succeeds. It can be more reliable than remembering to run them by hand, and it doesn’t force the database administrator (DBA) to be awake at 3 am to run the code either!

SQL Server, Oracle, MySQL, PostgreSQL, or MariaDB?

A number of factors, including personal experience and preference, can help with this decision. Cost, overall functionality, and amount of data stored and accessed should also be considered. Most databases can handle small or medium amounts of data from hundreds to thousands or tens of thousands of records. In this situation, it may make sense to choose an open source or free version. Systems with large amounts of data would benefit more from a subscription or license-based option, like SQL Server or Oracle, the latter being better at maintaining and retrieving large scale data.

The choice is up to the database developer, but he/she should consider the current and future needs of the organization. Planning for the future is a great way to determine which DBMS would be most appropriate and cost-effective. If you need a number of licenses and are concerned about money, open source options such as MariaDB and PostgreSQL would be the better choice. Licensed alternatives like SQL Server or Oracle typically require a license for each user, but usually come with some support from the software company; whereas, the open source alternatives usually don’t, aside from online technical forums.

Data from DB-Engines Ranking

Oracle

MySQL

SQL Server

PostgreSQL

MariaDB

Rank: (May, 2019)

1

2

3

4

12

License

Proprietary

Public

Proprietary

Open Source

Public

Maintainer

Oracle Corp.

Oracle Corp.

Microsoft

PostgreSQL

MariaDB Community

First Public Release

1979

1995

1989

1989

2010

Let’s Recap!

  • A database management system, DBMS, is software that maintains and manages the features of a database.

  • Common DBMS features include tables, stored procedures, synonyms, jobs, and security features.

  • Many factors can go into selecting a DBMS system including cost, ability to handle large data, and whether or not a license needs to be purchased.

Now that you're ready to work with a Database Management System, let's take a look at one of the first steps of designing a database: defining your data fields with the right data type.

Example of certificate of achievement
Example of certificate of achievement