BACKGROUND IMAGE: iSTOCK/GETTY IMAGES

This content is part of the Essential Guide: Evolution of Windows Azure SQL Database
Definition

relational database

A relational database is a set of formally described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. The standard user and application programming interface (API) of a relational database is the Structured Query Language (SQL). SQL statements are used both for interactive queries for information from a relational database and for gathering data for reports.

What is in a relational database model?

The relational database was invented in 1970 by E. F. Codd, then a young programmer at IBM. In his paper, "A Relational Model of Data for Large Shared Data Banks," Codd proposed shifting from storing data in hierarchical or navigational structures to organizing data in tables containing rows and columns.

Each table, which is sometimes called a relation, in a relational database contains one or more data categories in columns, or attributes. Each row, also called a record or tuple, contains a unique instance of data, or key, for the categories defined by the columns. Each table has a unique primary key, which identifies the information in a table. The relationship between tables can then be set via the use of foreign keys -- a field in a table that links to the primary key of another table.

For example, a typical business order entry database would include a table that described a customer with columns for name, address, phone number and so forth. Another table would describe an order: product, customer, date, sales price and so forth. A user of a relational database can then obtain a view of the database to fit their needs. For example, a branch office manager might like a view or report on all customers that bought products after a certain date. A financial services manager in the same company could, from the same tables, obtain a report on accounts that need to be paid.

Relational database terms
A relational database includes tables containing rows and columns.

When creating a relational database, you can define the domain of possible values in a data column and further constraints that may apply to that data value. For example, a domain of possible customers could allow up to 10 possible customer names but be constrained in one table to allowing only three of these customer names to be specifiable. Two constraints relate to data integrity and the primary and foreign keys:

  • Entity integrity ensures that the primary key in a table is unique and that the value is not set to null.
  • Referential integrity requires that every value in a foreign key column will be found in the primary key of the table from which it originated.

Examples of relational databases

Standard relational databases enable users to manage predefined data relationships across multiple databases. Popular examples of relational databases include Microsoft SQL Server, Oracle Database, MySQL and IBM DB2.

Cloud-based relational databases, or database as a service (DBaaS), are also widely used because they enable companies to outsource database maintenance, patching and infrastructure support requirements. Cloud relational databases include Amazon Relational Database Service (RDS), Google Cloud SQL, IBM DB2 on Cloud, SQL Azure and Oracle Cloud.

Types of databases

There are a number of database categories, from basic flat files that aren't relational to NoSQL to newer graph databases that are considered even more relational than standard relational databases.

A flat file database consists of a single table of data that has no interrelation -- typically text files. This type of file enables users to specify data attributes, such as columns and data types.

Pros and cons of flat file vs. relational database
Flat file vs. relational database

A NoSQL database is an alternative to relational databases that's especially useful for working with large sets of distributed data. These databases can support a variety of data models, including key-value, document, columnar and graph formats.

A graph database expands beyond traditional column- and row-based relational data models; this NoSQL database uses nodes and edges that represent connections between data relationships and can discover new relationships between the data. Graph databases are more sophisticated than relational databases, and thus, their uses include fraud detection or web recommendation engines.

An object relational database (ORD) is composed of both a relational database management system (RDBMS) and an object-oriented database management system (OODBMS). An ORD contains characteristics of both the RDBMS and OODBMS models. In an ORD, a traditional database is used to store the data. It is then accessed and manipulated using queries written in a query language, such as SQL. Therefore, the basic approach of an ORD is based on a relational database.

However, an ORD can also be considered object storage, particularly for software written in the object-oriented programming language (OOP), thus pulling on object-oriented characteristics. In this situation, APIs are utilized in the storage and retrieval of data.

Advantages of relational databases

The main advantage of relational databases is that they enable users to easily categorize and store data that can later be queried and filtered to extract specific information for reports. Relational databases are also easy to extend and aren't reliant on physical organization. After the original database creation, a new data category can be added without all existing applications being modified.

Other relational database advantages include:

  • Accuracy: Data is stored just once, eliminating data deduplication.
  • Flexibility: Complex queries are easy for users to carry out.
  • Collaboration: Multiple users can access the same database.
  • Trust: Relational database models are mature and well-understood.
  • Security: Data in tables within a RDBMS can be limited to allow access by only particular users.

Differences between a database and a relational database

The majority of software products in today's market incorporate both relational database and regular database compliances. Therefore, they can manage databases in the relational tabular form as well as in file form, or both. Essentially, in today's market, a relational database is a database and vice versa; however, there are still major differences in data storage between the two systems.

The most important difference is that a relational database stores data in a tabular form -- or arranged in a table with rows and columns -- while a database stores data as files. Other differences include:

  • Database normalization is present in a relational database while it is not present in a database.
  • A relational database supports a distributed database while a database does not support a distributed database.
  • In a relational database, the data values are stored in the form of tables and each table possesses a primary key. In a database, data is normally stored in hierarchical or navigational form.
  • Since data is stored in the form of tables in a relational database, then the relationship between these data values is stored as well. Since a database stores data as files, then there is not relationship between the values or tables.
  • In a relational database, the integrity constraints are defined for the purpose of an ACID On the other hand, a database does not utilize any security to protect against data manipulation.
  • While a relational database is designed to support large amounts of data and multiple users, a database is designed to deal with small amounts of data and one single user.

One final, major distinction is that the data storage in a relational database is accessible, meaning the value can be updated by the system. Furthermore, the data within an RDBMS is physically and logically independent.

Physical data independence refers to a system's capacity to make changes to the inner schema without altering the external schemas or application programs. Inner schema alterations may include:

  • the use of new storage devices;
  • modifying indexes;
  • changing from a specific access method to a different one;
  • utilizing different data structures and
  • using various storage structures or file organizations.

Logical data independence is a system's ability to alter the conceptual schema without altering the external schema or application programs. Conceptual schema alterations may include the addition or deletion of new relationships, entities or attributes without altering existing external schemas or rewriting application programs.

This was last updated in July 2019

Continue Reading About relational database

Join the conversation

10 comments

Send me notifications when other members comment.

Please create a username to comment.

How does your organization utilize relational databases, and have you considered alternatives like NoSQL?
Cancel
As I read the Bible on a daily basis I want to capture my thoughts of different words or topics to a 'database' so later I can search by either word, scripture reference, or my thoughts. Basically I want to build my own 'concordance' of my study. I want to be able to open, for instance, the topic of 'wind' where I have previously entered a scripture reference and add another reference under 'wind'. I understand that a database can do this, but I get confused by how to do this. Can anyone help?
Cancel
I couldn't remember what a relational database was. Your definition sufficed! It was well written.
Cancel
Thanks for the feedback! It's greatly appreciated.
Cancel
This was not helpful at all. You guys need to work on it and come up with new and more precise information. This kind of information is of no use. I hope you will take my feedback seriously. Thank You. Abdul Moiz
Cancel
Hi Abdul! I wanted to let you know that we're updating this definition to be more informative, and we absolutely take feedback seriously.

Thanks for taking the time to reach out.
Cancel
Hey! Me again xD
Can anyone here help me with DBMS?
Cancel
At present our company uses Access with back end tables to capture details on customers generators and parts - prices. We then can generate quotations and cost sheets from word and excel. This is all locally based on a server. Due to it is success I have been asked to enable it to go nationally. Microsoft are no longer recommending "Access Services for new web apps and web databases". Can you please recommend the best route I should take as I have 9 relationship table. I have no experience in Power Apps Azure etc so I am not sure where to start to develop and how to transfer or link to my existing details.
Cancel
Here, how to design a relational database for a sports company?  how to describe a transaction table in sports ralstional database? 
Cancel
this saved me big time! thank u
Cancel

-ADS BY GOOGLE

File Extensions and File Formats

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close