Hey Guys! It’s been a while since I’ve written a blog post.
Last week I started my master’s in Computer Science at Kennesaw State University, and one of the classes I’m taking this semester is Database Systems. I’ve worked as a software engineer for years and have worked on applications that rely on databases throughout my career and in my personal projects, but I’ve mostly interacted with them from the application side.
Going back to school gives me an opportunity to go back to the fundamentals and understand some of the technologies I’ve been using at a deeper level.
I’ve also decided to use writing as part of how I study. Before reading each section, I come up with a question I want the reading to answer. Afterward, I close the book and try to answer that question from memory in my own words.
This post is the result of doing that with Chapter 1 of my Database Systems textbook.
Databases Are Everywhere
First of all, I learned that the Apple II came out in 1977. I didn’t realize Apple went that far back. It also got my attention because I’ve personally decided to relearn C programming and recently learned that it was created in the early 1970s as well.
I also learned that Amazon was founded in 1995, a year before I was born. That was shocking to me because I thought Amazon, at least the version I’m familiar with today, was a relatively more recent thing than that (early 2000s or 2010s).
I learned that so much of what we use today, from mobile apps, to websites and web apps, to IoT and the services built around them heavily depend on databases.
A database is where the facts and numbers, which we call data, that an application needs are stored. When a user makes a request, server-side software can retrieve the data from the database and return it to the client, where it is presented as information.
Databases can be hosted on a server, but the database server doesn’t necessarily have to be the same machine as the application server providing the application’s services.
Without databases we would not be able to fulfill many of those requests from users, and many services modern web and mobile applications provide would not be possible.
Relational Databases
A relational database is a database that is made up of tables, which contain rows (or records) and columns (or fields), and the relationships between those tables.
The purpose of databases is to keep track of data, which is then used to generate information based on a context created by user requests.
Each row in the table has a field with a value that uniquely identifies it, called a primary key. A primary key is generally stable. If that primary key is automatically generated inside the database and assigned to that row or record we call that a surrogate key.
A foreign key is a field whose value references a key, typically the primary key, in another table and is used to create relationships between tables.
Databases can also exist at drastically different scales:
- Small/personal database → perhaps one user and around 10 rows
- CRM database → more users and substantially more rows
- E-commerce database → potentially millions of users and billions of rows
But a database itself is part of a larger system.
The Database System
A database system is a system used to create, process and manage a database. It’s usually made up of users, database applications, Structured Query Language (SQL), a Database Management System (DBMS), and the database.
The user uses a database application (or group of applications) to read, create or modify, and query data, and produces reports to convey information.
The database application is the user-facing application. It may contain forms the user interacts with to create, modify, and request data. The interactions can result in SQL queries being sent to the DBMS, and the retrieved data can be displayed to the user in the database application.
The DBMS is software used to create, process, and administer the database. It receives requests expressed in SQL from the database application and translates those requests into operations on the database.
As stated earlier, in the relational model we’re discussing here, a database is a collection of tables, each with records and fields, and the relationships between those tables. The database may also contain tables of metadata, which is data about the existing data.
So let’s say we have a Flutter app with the following screen:
Edit Profile
Name: Christian
City: Atlanta
And we change the city to Stone Mountain and tap Save
The database application interprets that action, eventually resulting in a SQL request being sent to the DBMS to modify the city.
The DBMS receives the SQL request, determines what operation needs to happen, makes the change in the appropriate record and field in the database, and communicates the updated results back to the database application.
The database application then updates the UI to reflect the successful change and may notify the user that the profile was updated.
So the model is as follows:
User ⇄ Database Application ⇄ SQL ⇄ DBMS ⇄ Database
Personal vs. Enterprise-Class Database Systems
Personal database systems are intended for small-scale use and usually involve one user or a small number of users.
An example we use in class is Microsoft Access, which technically is a bundle of DBMS functionality and application-generation tools, and hides much of the underlying database technology.
Enterprise-class database systems are meant for larger organizational systems and are capable of supporting many concurrent users and much greater scale. Oracle and MySQL are some good examples.
An enterprise-class DBMS can still be used to build a personal database system. The distinction is more about the requirements and scale of the system than simply which DBMS product is being used.
Designing a Database
Database design is the process of properly designing the tables of a database and the relationships between them. Designing databases is an important yet difficult skill, which is why there are a lot of poorly designed databases.
There are three ways to approach database design:
- design from existing data
- design from an existing database
- design for new systems
Designing databases from existing data is usually done using a set of principles called normalization. We will dive deeper into what that is in later reading assignments in class.
Designing databases for a new system is usually done by getting the list of requirements for the new system, coming up with a data model based on those requirements, and designing the database based on the data model. This is a process I am very familiar with since I’ve used it for my own personal projects and while working in industry.
Designing a database from an existing database is called database redesign, and there are two ways to do database redesign.
The first is database migration, which is the process of taking data from one database to a new database, usually because of new requirements.
The second involves combining two databases into a new database.
Different People Need Different Database Knowledge
There are two broad categories of people who use database technologies: users and database administrators.
Users consist of knowledge workers and programmers. Knowledge workers prepare reports, mine data, and perform other types of data analysis, while programmers write applications that process the database.
Database administrators (DBAs) design, construct, and manage databases.
Both need to have a strong foundational understanding of database systems, but depending on which path you take in your career, you would focus on different parts of database systems.
From File Processing to Relational Databases and NoSQL
In the early years of database processing, data was primarily managed using file-processing systems.
In 1970, Edgar Frank Codd, or E. F. Codd, while working at IBM, introduced the relational model for organizing database data using ideas from relational algebra. He eventually convinced IBM to develop relational-model DBMS products, which resulted in IBM DB2 and its variants.
By 1980, the relational model had moved from Codd’s theoretical work into commercially successful relational DBMS products. One prominent relational DBMS at that time was Oracle Database, in part because of its ability to run across many computers and operating systems.
The 1980s also brought the rise of object-oriented programming, and around 1990 object-oriented DBMSs (OODBMS) appeared. The idea was that since projects were increasingly being organized as objects, we should store those objects directly as they are.
While this made intuitive sense, it didn’t catch on. Organizations already had enormous amounts of data stored in relational format in databases, and it would have been extremely expensive to move all of that data into object-oriented databases. Object-oriented databases also had no significant advantage over relational databases for most commercial database processing.
Today relational databases remain the industry standard, but there has also been a resurgence of non-relational databases associated with the need to process and manage the variety and enormous volume of data associated with Big Data.
I also learned that NoSQL is actually somewhat of a mislabel for the movement toward non-relational databases. Non-relational databases may still use SQL or SQL-like query languages. NoSQL is therefore also interpreted as “Not only SQL”.
My mind was actually f***in blown by this lol. That’s so cool. I love finding out things like this.
Why Does Everything Keep Coming Back to the 1970s?
One thing I’m noticing from independently studying and reading academic texts is that a lot of things about computing and computer science seem to originate around 1970 specifically, or the 1970s in general.
Codd introduced the relational model in 1970. C was developed in the early 1970s. Apple was founded in the mid-1970s. And the deeper I go into the foundations of computer science, the more I seem to keep running into this period.
I’ve spent years working with technologies built on top of these ideas without necessarily knowing where those ideas came from. Going back to school and studying the fundamentals is starting to expose that history to me.
I wonder why the 1970s were such a productive period in computer science.
