When I first started building websites 25 years ago, everything was static. Our primary building block was HTML, with a bit of CSS and JavaScript sprinkled in for good measure. Our content would be incorporated into the HTML documents, and so there was no separation of functionality and content.
Regardless, I was just happy to be able to build these amazing things that I could publish for the world to see.
But within a few years I had quickly learned about the power of databases and how they could completely transform the websites we were building at the time.
Fast forward to today, and databases are everywhere. I always knew that databases were a key element to building better websites, but I had no idea just how important they would become in the years to come.
If you’re starting out in your database journey, it can be easy to be overwhelmed by the various lingo being thrown around in the space. And that’s where I want to help.
Below are ten basic concepts that are essential to understand when working with databases.
Database Management System (DBMS)
A Database Management System, typically referred to by its acronym DBMS, is software designed to create, maintain, and manage databases. It serves as an interface between the database and its end users or applications, allowing users to retrieve, update, and manage how the information is organised and optimised.
DBMSs can be quite complex, providing a broad range of functionality in areas such as data security, data integrity, concurrent access control, replication, analytical processing and data mining, and much more. Many DBMSs also offer backup and recovery mechanisms to prevent data loss in case of system failures.
Popular examples of database management systems include MySQL, Oracle, Microsoft SQL Server, PostgreSQL, SQLite, and Microsoft Access.
Relational Database
A relational database is a type of database that stores and organises data in tables with predefined relationships between them. It is based on the relational model proposed by Edgar Codd in 1970. In a relational database, each table consists of rows (also called records or tuples) and columns (also known as fields or attributes).
All of the aforementioned DBMSs are relational database management systems. This has the acronym RDBMS.
The power of relational databases lies in their ability to establish relationships between tables using keys (which I’ll explain later). This structure allows for efficient data retrieval and manipulation through a standardised query language called SQL (Structured Query Language).
SQL (Structured Query Language)
SQL is a standard language for managing and manipulating relational databases. It provides a set of commands to perform various operations on the data, such as:
SELECT: Retrieve data from one or more tablesINSERT: Add new data to a tableUPDATE: Modify existing data in a tableDELETE: Remove data from a tableCREATE: Create new database objects (such as tables, views, indexes)ALTER: Modify the structure of existing database objectsDROP: Delete database objects
Learning SQL is essential for anyone working with relational databases, as it allows you to interact with the data effectively and efficiently.
See my SQL Tutorial for Beginners for a comprehensive introduction to SQL programming (Hint: It’s easier than you might think!)
Tables, Rows, and Columns
In a relational database, data is organised into tables. A table is a collection of related data entries consisting of rows and columns. Each row represents a unique record or instance of the entity being described, while each column represents a specific attribute or characteristic of that entity.
For example, a Customers table might have columns such as CustomerID, FirstName, LastName, Email, and PhoneNumber. Each row in this table would represent a unique customer, with their specific details filled in for each column.
Primary Key
A primary key is a column or a set of columns in a table that uniquely identifies each row. It ensures that each record in the table is unique and can be distinguished from all others. Primary keys are crucial for establishing relationships between tables and maintaining data integrity.
For instance, in our Customers table, the CustomerID could serve as the primary key, as each customer would have a unique identifier.
Foreign Key
A foreign key is a column or set of columns in one table that refers to the primary key of another table. It establishes a link between two tables, creating a relationship between them. Foreign keys are essential for maintaining referential integrity and enabling complex queries across multiple tables.
For example, an Orders table might have a CustomerID column as a foreign key, which references the CustomerID primary key in the Customers table. This relationship allows us to associate each order with a specific customer.
Indexing
Indexing is a database optimisation technique that improves the speed of data retrieval operations. An index is a data structure that allows the database engine to quickly locate and access the rows in a table based on the values of one or more columns.
While indexes can significantly enhance query performance, they come with a trade off. Indexes require additional storage space and can slow down data modification operations (INSERT, UPDATE, DELETE) as the index needs to be updated along with the data.
ACID Properties
ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are processed reliably and maintain data integrity:
- Atomicity: A transaction is treated as a single, indivisible unit. It either completes entirely or fails completely.
- Consistency: A transaction brings the database from one valid state to another, maintaining all predefined rules and constraints.
- Isolation: Concurrent transactions do not interfere with each other, ensuring that the database remains in a consistent state.
- Durability: Once a transaction is committed, its effects are permanent and survive any subsequent system failures.
Understanding ACID properties is crucial for developing robust and reliable database applications.
Normalisation
Normalisation (or normalization, depending on your locale) is the process of organising data in a database in a way that reduces redundancy and improves data integrity. It involves breaking down large tables into smaller, more manageable ones and establishing relationships between them. The goal is to eliminate data anomalies and ensure that data is stored logically and efficiently.
For example, if you have a Customers table, you’d normally create a separate table for the products they can order (you could call this table Products). You’d create another table for customers’ orders (perhaps called Orders). And if each order could contain multiple items, you’d typically create yet another table to store each order item (perhaps called OrderItems). All these tables would be linked by their primary key, which allows you to find related data across all these tables (such as all orders by a given customer).
Database Transactions
A database transaction is a sequence of one or more database operations (such as read, write, update, or delete) that are executed as a single unit of work. Transactions ensure data consistency and integrity by grouping related operations together.
The concept of transactions is closely tied to the ACID properties mentioned earlier. Transactions allow multiple users to work with the database concurrently without interfering with each other’s work. They also provide a mechanism for rolling back changes if an error occurs during the transaction, ensuring that the database remains in a consistent state.
Understanding transactions is crucial for developing applications that interact with databases, especially in multi-user environments or when dealing with critical data operations.
See my SQL Transactions Tutorial for a beginners’ guide to writing transactions when programming in SQL.
Conclusion
The above ten database concepts formed the basis for my own understanding of database systems. This allowed me to increase my understanding in leaps and bounds over the coming years, due to the fact that I understood some of the key concepts of database management systems. I hope I’ve helped you with your own journey.