Design the Logical Model of Your Relational Database

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?

A 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.

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.

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.

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.