How Do Databases Do What They Do?

What is a database, and how does it work? You hear the term in movies all the time. You hear it shorthand, bandied about at libraries and schools; you may even know a little about what it is, but if you’re asked to explain you might say, “you know, data, in a base, all put together, and stuff.” How, then, do databases work? That question might be little more flummoxing, and the answer has to do with a programming language called SQL.

In short, a database is an organized collection of data. Our world is run off of databases: they contain our bank records, our friend lists, our restaurant orders, even our bike-sharing services. Imagine a trove of points of data, like the orders for a restaurant. Let’s call it Bestaurant. You want to organize all of Bestaurant’s orders. Your database would have columns listing the order number, the order type, the amount, any special requests, the price; row after row of orders would be organized into those columns. You might then further divide up those orders by day, so every day, the database would add a new table listing the orders from that day. So far, so good. Databases of this type, where the data is organized by tables and can be accessed easily, are called relational databases.

If you have ever used Microsoft Excel, you know the basics of how a database looks. However, Excel itself is not a program to build databases. In fact, a database can contain any type of information: not just text, but pictures, audio and video files, even bits of computer code. So, every one of Bestaurant’s orders could have a picture of it sorted in to the database as well.

How do you use a database? In order to access the information of a relational database, you use a programming language called SQL, short for “Structured Query Language.” According to Accelebrate, an IT training firm that offers SQL server training, SQL is “the universal language used for querying and interacting with relational databases.” Say you want to look up every steak dinner order at Bestaurant from last February. SQL would allow you to send a query to your database that would return every order of that type. As this Thought Co. article explains, SQL is the means by which you change the structure of tables; add and delete rows of data; and look up sets of data.

SQL is a powerful and flexible tool for managing a database, but for the most part, in our lives, SQL is invisible. This means that when Bestaurant’s bartender is on her behind-the-counter terminal, trying to determine how many Manhattans she moved last week, her search on that computer generates an SQL query which then returns the requested data from the database. It’s the same when she realizes one of those Manhattans was entered twice by mistake. When she selects and deletes that drink from last Wednesday, it generates an SQL query to delete that row on that table, and that Manhattan disappears forever. However, she will never see the SQL her work generates, just the graphic user interface of her terminal.

So, you have your bartenders and waiters at Bestaurant, entering, manipulating, and deleting information in the database through the restaurant’s tablets and computers. In this imaginary scenario, their computers comprise the “front end” of the system. The “back end,” in other words the program running the database, is housed on a database server, maybe onsite, or maybe (these days) it runs on “the cloud”. The program on their computers and tablets generates queries in SQL, which is sent to the server, and the database “reads” their requests and retrieves the desired information. It is a simple and elegant solution to a very modern question: how do you organize and manipulate massive amounts of data? The simple answer to that question, the relational database and SQL, is elegant, invisible, and underpins every aspect of our lives.