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 relational (SQL) databases.
When the data has few or no relationships, we ideally 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 means allows us to tables can 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
- 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