Overview
Databases are rather simple. In this article, we will compare them to folders and files within a computer. To be more specific, we’ll talk about folders and Excel files.
At a high level, the hierarchy from largest to smallest goes: server, database, schema, table, records/rows, values.
A computer is setup similarly: computer/machine, folders, Excel files, rows/columns, cells with values.
Let’s start at the very top with servers. A database lives on a server. In the analogy using folders and Excel files, the server would be the computer or machine that the Excel file is saved on.
The database would be a parent folder. Within there, we have “schemas”. We can think of schemas as children folders within the parent folder. Schemas help organize the database, similar to how folders organize files.
Schemas have tables inside. For now, we can say that tables are basically Excel files. Tables, like Excel files, contain rows (also known as records) and columns (also known as fields). Both Excel and databases use the terms “records” and “fields”. If you didn’t know that before, now you know.
The intersection between a row and a column is a cell/value.
Types of Databases
In the same way that different vehicles exist for different purposes,there are different types of databases. There are also different brands, makes, and models.
As far as cars we have: Honda, Nissan, BMW, Ford. In the world of databases, we have: Amazon Redshift, Aurora, Microsoft SQL Server, Oracle’s MySQL.
Racing cars are for going fast. Pickup trucks are used to haul loads and work. Vans are similar to pickup trucks, but usually store a lot of little things, while pickup trucks store medium to large objects.
Transactional databases are used to support live applications such as websites and point of sale systems (the software that cashiers use). The purpose of the transactional database is to store the information provided by a user. For example, when you go to a website and input your e-mail to subscribe to a mailing list, that record of data is saved into a database. When you are rung out at a store by a cashier, the information is quickly saved into a table in the database. “Milk for $3, Brown Eggs for $4, Bread for $3.”
Analytic databases and data warehouses are used for reporting or computationally heavy processes. Some examples include artificial intelligence and other analysis. Using the list of subscribers from a website as an example, we have all of the e-mails from everyone who wants to be a subscriber. We can analyze where the subscribers come from (geographically) and what language they speak. Analyzing this will let us know what languages you need to translate your newsletter into. Maybe we have each person’s purchase history. Using an analytic database, we can analyze their history to predict what they may want next and present them with offers.
Jobs Dealing with Databases
You don’t have to know every little thing about databases. There are different jobs for different aspects of databases. The person who maintains the structure and access to the database is known as the database administrator.
Other individuals may also need access to the database. Your company may have a staff member who analyzes the data in Excel. Their title may be one of many: marketing analyst, technical analyst, data analyst, report developer, report writer, SQL developer, and many more.
The way in which an administrator and analyst use and view databases are completely different. The administrator creates new users, monitors the performance, and does the overall up-keeping. The analyst communicates with the database to extract data and ultimately analyze it in another tool (Excel, PowerBI, Tableau). Developers, analysts, and database administrators communicate with databases using a language called SQL.
Now you know a little something about databases.
Read the I Know Nothing About SQL article to learn about SQL.