Tutorial: Databases
This tutorial introduces what databases are and their basic structuresDatabases Overview
Data is all around us, from Amazon history purchases to student grades contained inside a gradebook. What to do with all this data depends on context, but all data requires storage. Most websites today use databases, including video streaming, social media, e-commerce, finances, and many others.
What is a Database?
A database is a collection of data stored electronically within a computer system. Within a database, they are typically stored in rows and columns to make processing, managing, updating, and organizing efficient. As mentioned before, most databases use SQL to write and query data. To query data is to take an action on a database such as SELECT, INSERT, UPDATE, or DELETE. Quorum does not use SQL syntax for databases. There are many different types of databases, but for our purposes we will mostly be working with a relational database. Here are brief descriptions of the many different types of databases organizations use [1]:
| Database Type | Structure | Functionality |
|---|---|---|
| Relational | Tables with columns and rows | Provides the most efficient and flexible way to access structured information |
| Object-Oriented | In the form of objects | Database for object-oriented programming |
| Distributed | Two or more files located in different machines | To store data on multiple computers in the same location or scattered on different networks |
| Data warehouse | Central repository for data | Fast query and analysis |
| NoSQL | Allows unstructured and semistructured data to be stored and manipulated | Databases for more complex web applications |
| Graph | Entities and relationships between entities | tailored to very specific scientific, financial, or other functions |
| Open Sourced | Same as SQL / NoSQL database | Source code is open sourced |
| Cloud | collection of data, either structured or unstructured, that resides on a private, public, or hybrid cloud computing platform | traditional and database as a service (DBaaS). With DBaaS, administrative tasks and maintenance are performed by a service provider |
| Multimodel | Various structures | combine different types of database models into a single, integrated back end |
| JSON / Document | JSON formats | storing, retrieving, and managing document-oriented information |
| Self-Driving | ML Models | automate database tuning, security, backups, updates, and other routine management tasks |
In order to run our database, we will need to use database software to help assist in creating, editing, and maintaining our records. Such software allows users to manage and access data easily. In the current version, Quorum supports only MySQL, an open source database implementation. It was designed and optimized for web applications and can run on any platform.
Database Table Structure
Our database structure will be similar to what we see on Google Sheets and Microsoft Excel: a table of rows and columns. The rows of a table represent entities or events in some application domain. The columns of a table represent row attributes. Let's go through an example of our relational database structure. Let us have a model of a group of information regarding students, classes, and professors. Each box is represented by a table:
| StudentID | LastName | FirstName | Phone | GPA |
|---|---|---|---|---|
| 68765707 | Doe | Jane | 7024933451 | 3.8 |
| 47587465 | Doe | John | 7256731912 | 4.0 |
| 68764700 | Vader | Darth | 7024318418 | 3.8 |
| CourseID | Title | Semester | Units | Location | Time | InstructorID |
|---|---|---|---|---|---|---|
| 55461 | CS 420 | FA 2021 | 3 | MOP 203 | 1:00 PM | 23452 |
| 52551 | MUS 115 | SP 2022 | 3 | TYB 455 | 2:30 PM | 31341 |
| 64513 | HIST 202 | FA 2022 | 3 | RCC 110 | 8:30 AM | 31313 |
| InstructorID | LastName | FirstName | Rank | Department |
|---|---|---|---|---|
| 23452 | Montana | Hannah | AP | MUS |
| 31341 | Sparrow | Jack | AP | HIST |
| 31313 | Possible | Kim | AP | CS |
These tables contain unique values like student ID, instructor ID, and course ID and we refer to these as keys. The keys in one table can refer to a row in another table such as matching the Instructor key inside of Courses to Instructor ID key inside of Instructor. These so-called "foreign keys" are a common way of tracking data between tables. Note that Quorum only currently supports relational databases.
References
[1]: Oracle, What is a Database? https://www.oracle.com/database/what-is-database.
Next Tutorial
In the next tutorial, we will discuss MySQL, which describes how to install and set up MySQL.