What is a relational database? A deep dive

What is a relational database?#

A relational database, or SQL database, stores data in tables and allows easy access to related data points. In a relational database, each row in the database table is a record with a unique identifier called a key, and each column holds attributes of the data.

The tables in a relational database are based on the relational model, which is a simple, intuitive way to represent data. Let’s take a look at some of the fundamental concepts of the relational data model in relation to a sample table. The relation is Student and the attributes are First_Name, Student_ID, and Student_Age.

Student

• Attributes: The columns in the table. Attributes define a relationship, like First_Name or Student_ID.
• Relation schema: Represents the name of the relation with its attributes. For example, Student (First_Name, Student_ID, Student_Age) is a relation schema for Student.
• Tuples: A single row of a table containing a single record. The table above has three tuples, and one of them is Elliot 17 20.
• Degree: The number of attributes in the relation. The above relation has three.
• Carindality: The number of tuples in the relation. The above relation has three.

Relational Database Management System#

A relational database management system (RDBMS) is the software that is used to record, manipulate, and retrieve data in a relational database. This software is a great way to work with related data points that need to be maintained in a safe and consistent way.

MySQL is the most popular open-source relational database management system (RDBMS). Some other popular relational database software includes:

• MongoDB
• PostgreSQL
• Microsoft SQL Server
• Oracle Database
• Amazon Relational Database Service (RDS)
• IBM Informix

Non-relational databases#

Non-relational databases, or NoSQL databases, store data in a non-tabular way. They use a variety of different data models to access and manage types of data. NoSQL databases are great for applications with large data sets because they are highly responsive, scalable, and adaptive.

NoSQL databases provide highly functional APIs and data types that are built for their corresponding data models and are optimized for higher performance.

There are four main types of NoSQL databases:

• Key-value stores
• Graph databases
• Column-oriented
• Document store

Note: NoSQL stands for not only SQL because you can either use them with or without SQL.

Advantages of relational databases#

There are many advantages to using the relational database model for data management and data storage, including:

• Flexibility: It’s easy to expand, update, and delete data whenever needed.
• Atomicity: Atomic transactions ensure that you either commit to the entire transaction or have no transaction at all. If there’s a lost connection, the relational database goes back to its previous state.
• Durability: The changes that are made to the database will survive permanently, even in the event of a system crash.
• Consistency: The only data allowed to be written into the database is data that follows data validation rules.
  

Relational database uses#

Relational databases are useful for many different things, such as:

• Application development
• Data warehousing
• Data maintenance in applications
• Storing structured data
• Logging applications
• Etc.

They’re also useful in many different industries, such as:

What is SQL?#

SQL, or Structured Query Language, is the programming language used to work with data in a relational database. All of the major relational database management systems use SQL as their database language.

SQL is the standard choice because of its benefits. With SQL, users can:

• Access data in their RDMS
• Describe, define, and manipulate data
• Use SQL modules, libraries, and pre-compilers to embed it into other programming languages
• Make and delete databases and tables
• Set permissions on tables, processes, and views

You can use SQL statements to interact with your relational database. These commands are organized into three different groups:

• Data Definition Language (DDL)

  • CREATE
  • ALTER
  • DROP

• Data Manipulation Langauge (DML)

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

• Data Control Language (DCL)

  • GRANT
  • REVOKE

Let’s take a look at how to use one of these commands. Here’s how to use the CREATE DATABASE command:

If you want to create two databases and then display the list of your databases, you can do this:

Note: The SHOW DATABASE command is used to display your databases.

Properties of relational tables#

Let’s explore some of the characteristics of relational tables:

Each row is unique

No two rows in a table are the same.

Values are atomic

Atomic values can’t be broken down into smaller pieces. Relational tables don’t contain repeating groups or multi-valued attributes. This simplifies data manipulation.

Column values are of the same type

All values in a column come from the same domain based on their data type. This allows for simplified data access because you can be sure of the type of data contained within a given column.

The sequence of columns doesn’t matter

Columns can be retrieved in any order. This allows users to share the same table without worrying about its organization.

The sequence of rows doesn’t matter

The rows of a relational table can be retrieved in any order. Adding new data to a table doesn’t affect existing queries.

Each column has a different name

Since the sequence of columns doesn’t matter, so they must be referred to by name. A column name doesn’t need to be unique within an entire database, but only within the relation it belongs in.

Relational database example#

Relational databases work in many different scenarios. Let’s say there’s a college that wants to maintain information about its students, departments, and instructors. We can do that easily with a relational database. The table below shows the relational database structure and some sample data records.

Student table

Department table

Instructor table

This database has three tables that store data records of the same type in an organized way. Once all the data is defined and constructed into the database, you can begin working with a database management system (DBMS) to retrieve information from the different tables.

Wrapping up and next steps#

Congratulations on taking your first steps with relational databases! They are a great tool for working with structured data. There are still many things to learn about databases, such as:

• Primary keys and foreign keys
• Normalization
• Boyce-Codd Normal Form

To learn these concepts and more, check out Educative’s course Database Design Fundamentals for Software Engineers. In this course, you’ll learn about the fundamentals of databases and SQL. After completing the course, you’ll be ready to move on to more advanced concepts involving database systems and SQL.

Happy learning!

Continue reading about databases and SQL#

• The complete guide to system design in 2022
• What is a Database Foreign Key? A beginner’s tutorial
• What is a Database Query? SQL and NoSQL queries explained
• MySQL Workbench tutorial: complete guide to the RDBMS tool