About this article
This article serves as an introduction to relational database concepts. This post is the first on a series exploring the subject of relational databases for the full-stack developer. Among other components, we define tables, columns, rows, relationships, identifiers, keys, indexes, constraints, and schemas.
For quick access, here is the full list of the articles on the topic of relational database design:
- Introduction to Relational Databases (this article)
- Database Design: Entities, Attributes, and Relationships
- Database Design: Entity-Relationship Diagrams
- Database Design: Normalization
- Database Design: Entity-Relationship Diagram to Structured Query Language
- Deploying PostgreSQL for development and testing
- Structured Query Language Cheatsheet
- Working with databases from Python
Introduction
As a full-stack developer, you often work on projects that need to keep track of information. Online stores, blogs, games, and social media applications are a few of these types of projects.
For tracking information, persisting it between sessions, and querying it efficiently, nothing beats a database.
What is a database?
A database is an organized set of data stored electronically in a computer system. Databases required a software layer that serves as an interface between the data and the users. This layer is known as a database management system or DBMS.
There are many kinds of databases, but in general, you may label them as relational and non-relational databases. Relational databases are by far the most common. According to a ranking for April 2020, 7 out of the top 10 most popular databases are relational.
You need databases to provide persistent storage to your applications, to permit concurrent access to data by multiple users or programs, and to protect data consistency.
Non-relational databases
There are many types of non-relational databases (also known as NoSQL). Each type has a niche use case. Some NoSQL database types are:
- Key-Value Stores (i.e., Redis)
- Document Stores (i.e., MongoDB)
- Search Engines (i.e., ElasticSearch)
- Wide Column Stores (i.e., Cassandra)
- Graph Databases (i.e., Amazon Neptune)
Relational databases
A relational database consists of a collection of organized, inter-related data.
Relational databases store items in tables, and strict rules define the structure of each table and the relationships between data in different tables.
Relational databases excel at storing and retrieving structured data and at ensuring atomicity, consistency, isolation, and durability (ACID) of database transactions.
Relational Database Components
Some important components of a relational database are:
- A table is a two-dimensional representation of a business entity we need to track. Tables contain columns and rows.
- Table columns represent the entity’s attributes or properties. Each column in a table should map to one and only one entity attribute. Each column has a data type.
- Table rows represent unique instances of the entity.
- An identifier is a column (attribute) we can use to identify rows (entity instances). They can be unique (identifies exactly one instance), non-unique (identifies several instances), or composite (consists of several columns).
- A primary key is a table’s unique identifier. Primary keys cannot be null and, once set, cannot be changed.
- A foreign key is a primary key in one table that we use as a non-unique identifier in another table. Foreign keys form relationships between two tables.
- A relationship is a link between rows in two different tables or rows in the same table. Relationships can be one-to-one, one-to-many, or many-to-many.
- An index is a data structure that improves the speed of data retrieval operations on a table.
- Structured query language or SQL is the language used by relational database management systems to write and retrieve data from the database.
- Constraints are rules added to our table definitions to enforce referential integrity and prevent logically incomplete data from being added to the database. Unique keys and foreign keys are important constraints. Other constraint examples are NOT NULL which rejects a record if a particular field is empty and UNIQUE which rejects a record if a field’s value is already present in another record.
- A database schema defines how tables are laid out and are related to other tables. Schemas include information about table columns, data types, constraints, and indices.
Relational Database Management Systems
There are several commercial relational databases. Some examples include Oracle, MS SQL Server, and IBM Db2.
For your projects, you are likely to use an open-source relational database like MariaDB, PostgreSQL, or SQLLite, among many others.
As of April 2020, the market split for relational databases is about 50% commercial and 50% open-source.
Conclusion
We hope you enjoyed this brief introduction to relational database concepts. In the next article, we explore relational database design, starting with the discovery of entities, attributes, and relationships. See you there!
References
- Database management system
- Relational databases
- NoSQL databases
- Database engine rankings
- Database table
- Database column
- Database row
- Database relationships
- Database schema
- Structured query language
- Computer science: ACIDity
- Computer science: persistence
- Computer science: concurrency
- Computer science: consistency
You may also like:
