Databases Checkpoint

Introduction

This checkpoint is intended to help you assess what you learned from our introductory articles to Databases, where we defined databases and introduced common database management systems. You can use this checkpoint to test your knowledge on these topics, review key terms and commands, and find resources for continued learning.

A database is any logically modeled collection of information or data. When people refer to a “database” in the context of websites, applications, and the cloud, they often mean a computer program that manages data stored on a computer. These programs, known formally as database management systems (DBMS), can be combined with other programs (like a web server and a front-end framework) to form production-ready applications.

In this checkpoint, you’ll find two sections that synthesize the central ideas from the introductory articles: a brief explanation of what a database is (including subsections on relational and non-relational databases) and a section on how to interact with your DBMS through the command line or graphical user interfaces. In each of these sections, there are interactive components to help you test your knowledge. At the end of this checkpoint, you will find opportunities for continued learning about database management systems, fully managed databases, and building your apps with backend databases.

Resources

• An Introduction to Databases
• Understanding SQL Constraints
• SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems
• A Comparison of NoSQL Database Management Systems and Models
• How To Install and Secure Redis on Ubuntu 22.04
• How To Perform CRUD Operations in MongoDB

What Is a Database?

A database is any logically modeled collection of information, and a database management system is what most people think of when they think “I know what a database is!” You use a database management system (DBMS), which is a computer program designed to interact with the information, to access and manipulate the information stored in your database.

There are three common relational models used for database systems:

These relational models structure how databases can relate to each other.

There are two categories for database management: relational and non-relational databases. In the following subsections, you will learn about each type and the common DBMSs for those types.

Relational Databases

A relational database organizes information through relations, which you might recognize as a table.

When information is stored in a database and organized in the relation, it can be accessed through queries that make a structured request for information. Many relational databases use the Structured Query Language, commonly referred to as SQL to manage queries to the database.

You can use SQL constraints when designing your database. These constraints impose restrictions on what changes can be made to the data in the table.

Some open-source relational database management systems built with SQL include MySQL, MariaDB, PostgreSQL, and SQLite. Continue learning about relational databases with Understanding Relational Databases and review common relational DBMS with SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.

Now that you know about relational databases, you can understand their counterpart: non-relational databases.

Non-relational and NoSQL Databases

If you need to store data in an unstructured way, a non-relational database provides an alternative model. Because a non-relational database does not use SQL, it is sometimes referred to as a NoSQL database.

There are a variety of available options for a non-relational database, such as key-value stores, columnar databases, document stores, and graph databases. Each of these models attends to possible issues with using a relational database, including horizontal scaling, eventual consistency across nodes, and unstructured data management.

You can check your knowledge about which popular non-relational databases management systems align with the type of database model with the following interactive dropdown feature.

Whether you are using a relational or a non-relational database, you are likely building an application that includes a database management system as part of its stack.

Building an Application Stack

The database management system is most often deployed as an essential aspect of a larger application. These applications are sometimes called stacks, such as the LAMP stack or Elastic stack.

If you set up a remote server with your application stack, it is recommended that you encrypt your data to protect your system from malicious interference. You can encrypt communications using transport layer security (TLS), which will convert data in motion into a ciphertext that can only be decrypting by the right cipher. The static data stored in your database will remain unencrypted unless using a DBMS that offers data at rest encryption.

To manage your database, you may opt do so directly from the command line interface or through a graphical user interface.

Using the Command Line with your DBMS

You began to use the Linux command line with our introductory articles on cloud servers and you configured a web server with the introductory articles on web server solutions. Through the articles on databases, you have continued to develop familiarity with the command line using commands such as:

• grep to search plain-text data for a specific text or string.
• netstat to check network configuration with the flags -lnp to show listening sockets (-l), numeric addresses (-n), and the PID and name of the program for each socket (-p).
• systemctl to control the systemd service.

You have also experimented with the command line tools that come with different database management systems in order to interact with the database installation. The CLI tool enables you to execute commands on the database server and work interactivley from your terminal window. The following table lists common DBMSs and their associated CLI tool:

There are also third-party command line clients for some database management systems, such as Redli for Redis.

When you use the command line to work with your database system, you open a database-specific server prompt, typically associated with your user account for that database management system. For example, if you were to open a MySQL server prompt and log in with your MySQL user, you would review a database prompt like so:

Each DBMS command-line client has its own syntax for commands.

After learning about SQL constraints, you might use those constraints with a MySQL database by running these commands:

• CREATE DATABASE to create a database.
• USE to select a database.
• CREATE TABLE to create a table with specifications for the columns and constraints applied to those columns.
• ALTER TABLE with ADD to add constraints to an existing table and with DROP CONSTRAINT to delete a constraint from an existing table.

You can continue to develop your MySQL database skills with the How To Use SQL series.

With Redis, you installed and secured Redis with the following commands and experimented with renaming commands:

• auth to authenticate clients for database access.
• exit and quit to exit the Redis-CLI prompt.
• get to retrieve the key value.
• ping to test connectivity.
• set to set keys.

And, in MongoDB shell, you used binary JSON (known as BSON) to run CRUD operations with the following methods of query filtering:

• count method to check the object count in a specified collection.
• deleteOne to remove the first document that matches the specifications.
• deleteMany to remove multiple objects at once.
• find to retrieve documents in your MongoDB database with the pretty printing feature to make the lines more readable.
• insertOne method to create individual documents.
• insertManymethod to insert multiple documents in a single operation or collection.
• ObjectId object datatype for storing object identifiers.
• updateOne to update a single document with specified keys.
• updateMany to update every document in a collection that matches the specified filters.

You will likely use CRUD operations to interact with your data across many database management systems.

While you may opt to manage your database directly from the command line, you can also use a graphical user interface (GUI) for many common database management systems.

Using a Graphical User Interface

There are many different GUI tools for working with your database if you decide against using the designed CLI tool.

To handle MySQL administration over the web, you can use phpMyAdmin by installing and securing phpMyAdmin on many different operating systems or connecting remotely to a MySQL Managed Database. You can also use MySQL Workbench to connect to a MySQL server remotely.

Similar to phpMyAdmin, pgAdmin is a web interface for managing PostgreSQL. You can install and configure pgAdmin in server mode or use it to schedule automatic backups with pgAgent.

For MongoDB, you might consider using MongoDB Compass as the graphical interface to access your database.

Whether you choose to use the command line or a graphical interface to manage your database, you now have the tools necessary to manage your database system.

What’s Next?

With a stronger understanding of databases and popular database management systems, you can store and manage your data or build an application that uses a database system.

For more about working with specific database management systems, you can follow our How To Use SQL and How To Manage Data with MongoDB series. If you run into issues with MySQL, you can debug with How To Troubleshoot Issues in MySQL. For MongoDB issues, assess how your issues related to How To Perform CRUD Operations in MongoDB.

When you’re ready to build your apps with databases, try following these tutorials for common application stack setups:

• How To Install Linux, Apache, MySQL, PHP (LAMP) Stack on Ubuntu 22.04
• How To Install Linux, Nginx, MySQL, PHP (LEMP stack) on Ubuntu 22.04
• How To Install Linux, OpenLiteSpeed, MariaDB, PHP (LOMP stack) on Ubuntu 22.04

If you prefer building your apps with fully managed databases, check out the DigitalOcean offerings for managed MongoDB clusters, MySQL or PostgreSQL hosting, and managed Redis. You can also choose a popular database option for a 1-click installation in the DigitalOcean Marketplace.

With your newfound knowledge of databases, you can also continue your cloud journey with containers and security. If you haven’t yet, check out our introductory articles on cloud servers and web servers.