What is a relational database? (2024)

A relational database is a type of database that stores and provides access to datapoints that are related to one another. Relational databases are based on the relational model,an intuitive, straightforward way of representing data in tables. In a relationaldatabase, each row in the table is a record with a unique ID called the key. The columnsof the table hold attributes of the data, and each record usually has a value for eachattribute, making it easy to establish the relationships among data points.

A relational database example

Here’s a simple example of two tables a small business might use to process orders forits products. The first table is a customer info table, so each record includes acustomer’s name, address, shipping and billing information, phone number, and othercontact information. Each bit of information (each attribute) is in its own column, andthe database assigns a unique ID (a key) to each row. In the second table—a customerorder table—each record includes the ID of the customer that placed the order, theproduct ordered, the quantity, the selected size and color, and so on—but not thecustomer’s name or contact information.

These two tables have only one thing in common: the ID column (the key). But because ofthat common column, the relational database can create a relationship between the twotables. Then, when the company’s order processing application submits an order to thedatabase, the database can go to the customer order table, pull the correct informationabout the product order, and use the customer ID from that table to look up thecustomer’s billing and shipping information in the customer info table. The warehousecan then pull the correct product, the customer can receive timely delivery of theorder, and the company can get paid.

How relational databases are structured

The relational model means that the logical data structures—the data tables, views, andindexes—are separate from the physical storage structures. This separation means thatdatabase administrators can manage physical data storage without affecting access tothat data as a logical structure. For example, renaming a database file does not renamethe tables stored within it.

The distinction between logical and physical also applies to database operations, whichare clearly defined actions that enable applications to manipulate the data and structures ofthe database. Logical operations allow an application to specify the content it needs,and physical operations determine how that data should be accessed and then carries outthe task.

To ensure that data is always accurate and accessible, relational databases followcertain integrity rules. For example, an integrity rule can specify that duplicate rowsare not allowed in a table in order to eliminate the potential for erroneous informationentering the database.

The relational model

In the early years of databases, every application stored data in its own uniquestructure. When developers wanted to build applications to use that data, they had toknow a lot about the particular data structure to find the data they needed. These datastructures were inefficient, hard to maintain, and hard to optimize for delivering goodapplication performance. The relational database model was designed to solve the problemof multiple arbitrary data structures.

The relational data model provided a standard way of representing and querying data thatcould be used by any application. From the beginning, developers recognized that thechief strength of the relational database model was in its use of tables, which were anintuitive, efficient, and flexible way to store and access structured information.

Over time, another strength of the relational model emerged as developers began to usestructured query language (SQL) to write and query data in a database. For many years,SQL has been widely used as the language for database queries. Based on relationalalgebra, SQL provides an internally consistent mathematical language that makes iteasier to improve the performance of all database queries. In comparison, otherapproaches must define individual queries.

Benefits of relational database management system

The simple yet powerful relational model is used by organizations of all types and sizesfor a broad variety of information needs. Relational databases are used to trackinventories, process ecommerce transactions, manage huge amounts of mission-criticalcustomer information, and much more. A relational database can be considered for anyinformation need in which data points relate to each other and must be managed in asecure, rules-based, consistent way.

Relational databases have been around since the 1970s. Today, the advantages of therelational model continue to make it the most widely accepted model for databases.

Relational model and data consistency

The relational model is the best at maintaining data consistency across applications anddatabase copies (called instances). For example, when a customer deposits money at anATM and then looks at the account balance on a mobile phone, the customer expects to seethat deposit reflected immediately in an updated account balance. Relational databasesexcel at this kind of data consistency, ensuring that multiple instances of a databasehave the same data all the time.

It’s difficult for other types of databases to maintain this level of timely consistencywith large amounts of data. Some recent databases, such as NoSQL, can supply only“eventual consistency.” Under this principle, when the database is scaled or whenmultiple users access the same data at the same time, the data needs some time to “catchup.” Eventual consistency is acceptable for some uses, such as to maintain listings in aproduct catalog, but for critical business operations such as shopping carttransactions, the relational database is still the gold standard.

Commitment and atomicity

Relational databases handle business rules and policies at a very granular level, withstrict policies about commitment (that is, making a change to the database permanent).For example, consider an inventory database that tracks three parts that are always usedtogether. When one part is pulled from inventory, the other two must also be pulled. Ifone of the three parts isn’t available, none of the parts should be pulled—all threeparts must be available before the database makes any commitment. A relational databasewon’t commit for one part until it knows it can commit for all three. This multifacetedcommitment capability is called atomicity. Atomicity is the key to keeping data accuratein the database and ensuring that it is compliant with the rules, regulations, andpolicies of the business.

ACID properties and RDBMS

Four crucial properties define relational database transactions: atomicity, consistency,isolation, and durability—typically referred to as ACID.

• Atomicity defines all the elements that make up a complete databasetransaction.
• Consistency defines the rules for maintaining data points in acorrect state after a transaction.
• Isolation keeps the effect of a transaction invisible to othersuntil it is committed, to avoid confusion.
• Durability ensures that data changes become permanent once thetransaction is committed.

Stored procedures and relational databases

Data access involves many repetitive actions. For example, a simple query to getinformation from a data table may need to be repeated hundreds or thousands of times toproduce the desired result. These data access functions require some type of code toaccess the database. Application developers don’t want to write new code for thesefunctions in each new application. Luckily, relational databases allow storedprocedures, which are blocks of code that can be accessed with a simple applicationcall. For example, a single stored procedure can provide consistent record tagging forusers of multiple applications. Stored procedures can also help developers ensure thatcertain data functions in the application are implemented in a specific way.

Database locking and concurrency

Conflicts can arise in a database when multiple users or applications attempt to changethe same data at the same time. Locking and concurrency techniques reduce the potentialfor conflicts while maintaining the integrity of the data.

Locking prevents other users and applications from accessing data while it is beingupdated. In some databases, locking applies to the entire table, which creates anegative impact on application performance. Other databases, such as Oracle relationaldatabases, apply locks at the record level, leaving the other records within the tableavailable, helping ensure better application performance.

Concurrency manages the activity when multiple users or applications invoke queries atthe same time on the same database. This capability provides the right access to usersand applications according to policies defined for data control.

What to look for when selecting a relational database

The software used to store, manage, query, and retrieve data stored in a relationaldatabase is called a relational database management system (RDBMS). The RDBMS providesan interface between users and applications and the database, as well as administrativefunctions for managing data storage, access, and performance.

Several factors can guide your decision when choosing among database types and relationaldatabase products. The RDBMS you choose will depend on your business needs. Ask yourselfthe following questions:

• What are our data accuracy requirements? Will data storage and accuracy rely onbusiness logic? Does our data have stringent requirements for accuracy (for example,financial data and government reports)?
• Do we need scalability? What is the scale of the data to be managed, and what is itsanticipated growth? Will the database model need to support mirrored database copies(as separate instances) for scalability? If so, can it maintain data consistencyacross those instances?
• How important is concurrency? Will multiple users and applications need simultaneousdata access? Does the database software support concurrency while protecting thedata?
• What are our performance and reliability needs? Do we need a high-performance,high-reliability product? What are the requirements for query-response performance?What are the vendor’s commitments for service level agreements (SLAs) or unplanneddowntime?

The relational database of the future: The self-driving database

Over the years, relational databases have gotten better, faster, stronger, and easier towork with. But they’ve also gotten more complex, and administering the database has longbeen a full-time job. Instead of using their expertise to focus on developing innovativeapplications that bring value to the business, developers have had to spend most oftheir time on the management activity needed to optimize database performance.

Today, autonomous technology is building upon thestrengths of the relational model, clouddatabase technology, and machinelearning to deliver a new type of relational database. The self-driving database(also known as the autonomous database) maintains the power and advantages of therelational model but uses artificial intelligence (AI), machine learning, and automationto monitor and improve query performance and management tasks. For example, to improvequery performance, the self-driving database can hypothesize and test indexes to makequeries faster, and then push the best ones into production—all on its own. Theself-driving database makes these improvements continuously, without the need for humaninvolvement.

Autonomous technologyfrees up developers from the mundane tasks of managing the database. For instance, theyno longer have to determine infrastructure requirements in advance. Instead, with aself-driving database, they can add storage and compute resources as needed to supportdatabase growth. With just a few steps, developers can easily create an autonomousrelational database, accelerating the time for application development.