SQL and NoSQL Databases
What is a Database?
A database is an organized collection of data. Collecting, storing, and analyzing very large amounts of data can be a daunting task, but is made much easier with the use of databases.
Information stored within databases may or may not have strong relationships between each other. As programmers, it is in our best interest to understand whether or not there are strong relationships between our data, because this determines what type of database is best suited for our task.
When the data has many relationships, we ideally use relational (SQL) databases.
When the data has few or no relationships, or frequently changing relationships, it's better to use non-relational (NoSQL) databases.
What is a Relational (SQL) Database?
SQL stands for Structured Query Language. An SQL database is a relational database meaning that data is stored in tables, records, in which relationships between tables are well defined. This allows us to use tables to retrieve data from other tables.
Think of a table as a collection of people, each person being an entry, or "record", in the table. There may be one table named "Engineering Students" and another table named "UWaterloo Clubs". These are collections of people and clubs, respectively. A student part of "Engineering Students" can also relate to multiple clubs under the "UWaterloo Clubs" table. This is called a one-to-many relation. In general, there are multiple different dependencies:
- One-to-One: one record relates to only one other record in another table
- One-to-Many: one record relates to many records in another table
- Many-to-Many: many table records relate to many other table records in another table
| Word | Plain English |
|---|---|
| table | a set of data elements, containing columns of data fields and rows of entries |
| record (or row) | an entry to the table; an instance of what the table is collecting, with necessary fields |
| column | vertical grouping on table; indicates the fields in a given table |
| field | a single data structure for a single piece of data |
- MySQL
- SQL Server
- Microsoft Access
- Postgres
What is a Non-Relational (NoSQL) Database?
A NoSQL database is a non-relational database, meaning that the data is structured using relationships other than the tabular relations of SQL databases. As such, there are many different types of NoSQL databases.
For example, MongoDB is a beginner friendly NoSQL database that is document-based. You can think of MongoDB as a book with pages, where each page is a document and each book is a collection.
- Key-value store
- Document store (eg. MongoDB)
- Graph
- Object database
- Tabular
- MongoDB
- Cassandra
- Elasticsearch
- Couchbase
Analogous Terminology between SQL & MongoDB (NoSQL Database)
| SQL | MongoDB |
|---|---|
| database | database |
| table | collection |
| row | document/BSON document |
| column | field |
| table joins | $lookup, embedded documents |
| primary key | primary key |
In order to retrieve information from a database (SQL or NoSQL), we as programmers need to write a query. A query is code that pulls desired information from a database. Although a query is often one-line of code, it is good practice to stylize a given query into multiples lines for ease of readability. To learn more about querying, please visit the below:
- Quackit, MS Access Tutorial
- w3schools, SQL Tutorial
- tutorialspoint, MongoDB Tutorial