Relational Databases and SQL

A relational database is a database that utilizes the relational model of data. The relational model, invented by Edgar F. Codd in 1970, organizes data as relations, or sets of tuples. Each tuple consists of a series of attributes that generally describe the tuple. For example, we could imagine a customer relationship where each tuple represents a customer. Each tuple would then have attributes describing a single customer, giving information such as the last name, first name, and age, perhaps in the format (Smith, John, 27). One or more of the attributes is used to uniquely identify a tuple in a relation and is called the relational key. The relational model then allows logical operations to be performed between relations.

In a relational database, relations are usually implemented as tables, as in an Excel spreadsheet. Each row of the table is a tuple, and the attributes are represented as columns of the table. While not technically required, most tables in a relational database have a column referred to as the primary key, which uniquely identifies a row of the database. Every column also has a data type, which describes the type of data in the column. Tables are then usually collected together in common collections in databases called schemas. These tables usually are loaded with processes known as Extract, Transform, Load jobs (or ETL for short).

Tables are usually referred to in queries in the format [schema].[table]. For example, a products table in the analytics schema would be generally referred to as analytics.product. However, there is also a special schema called the public schema. This is a default schema where, if you do not explicitly mention a schema, then the database uses the public schema; for example, the public.products table and the products table are the same.

The software used to manage relational databases on a computer is referred to as a relational database management system (RDBMS). SQL is the language utilized by users of an RDBMS to access and interact with a relational database.

Note

Virtually all relational databases that use SQL deviate from the relational model in some basic way. For example, not every table has a specified relational key. Additionally, a relational model does not technically allow for duplicate rows, but you can have duplicate rows in a relational database. These differences are minor and will not matter for the vast majority of readers of this book.

Advantages and Disadvantages of SQL Databases

Since the release of Oracle Database in 1979, SQL has become an industry standard for data in nearly all computer applications—and for good reason. SQL databases provide a ton of advantages that make it the de facto choice for many applications:

• Intuitive: Relations represented as tables are a common data structure that almost everyone understands. As such, working with and reasoning about relational databases is much easier than doing so with other models.
• Efficient: Using a technique known as normalization, relational databases allow the representation of data without unnecessarily repeating it. As such, relational databases can represent large amounts of information while utilizing less space. This reduced storage footprint also allows the database to reduce operation costs, making well-designed relational databases quick to process.
• Declarative: SQL is a declarative language, meaning that when you write code, you only need to tell the computer what data you want, and the RDBMS takes care of determining how to execute the SQL code. You never have to worry about telling the computer how to access and pull data in the table.
• Robust: Most popular SQL databases have a property known as atomicity, consistency, isolation, and durability (ACID) compliance, which guarantees the validity of the data, even if the hardware fails.

That said, there are still some downsides to SQL databases, which are as follows:

• Relatively Lower Specificity: While SQL is declarative, its functionality can often be limited to what has already been programmed into it. Although most popular RDBMS software is updated constantly with new functionality being built all the time, it can be difficult to process and work with data structures and algorithms that are not programmed into an RBDMS.
• Limited Scalability: SQL databases are incredibly robust, but this robustness comes at a cost. As the amount of information, you have doubles, the cost of resources more than doubles. When very large volumes of information are involved, other data stores such as NoSQL databases may actually be better.
• Object-Relation Mismatch Impedance: While tables are a very intuitive data structure, they are not necessarily the best format for representing objects in a computer. This primarily occurs because objects often have attributes that have many-to-many relationships. For instance, a customer for a company may own multiple products, but each product may have multiple customers. For an object in a computer, we could easily represent this as a list attribute under the customer object. However, in a normalized database, a customer's products would potentially have to be represented using three different tables, each of which must be updated for every new purchase, recall, and return.