SQL and NoSQL Databases
A database is an organized collection of data [1]. Databases are especially important when there are large amounts of data being collected, stored, and analyzed.
- University of Waterloo stores all student information (student ID, first name, last name, grades, etc.) in the form of a database
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.
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 that 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 another record in another table
- One to Many: one record relates to many records in another table
- Many to One: many records relate to another table record
- 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 |
Showing the columns of a MySQL database
SHOW COLUMNS FROM `players_two`;
+---------------------------+-------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+-------------+------+-----+---------+-------------------+
| id | int(11) | NO | PRI | NULL | STORED GENERATED |
| player_and_games | json | NO | | NULL | |
| names_virtual | varchar(20) | NO | MUL | NULL | VIRTUAL GENERATED |
| times_virtual | int(11) | NO | MUL | NULL | VIRTUAL GENERATED |
| tennis_won_virtual | int(11) | NO | MUL | NULL | VIRTUAL GENERATED |
| tennis_lost_virtual | int(11) | NO | MUL | NULL | VIRTUAL GENERATED |
| battlefield_level_virtual | int(11) | NO | MUL | NULL | VIRTUAL GENERATED |
+---------------------------+-------------+------+-----+---------+-------------------+
A more complex query...
SELECT `id`, `names_virtual`, `tennis_won_virtual`, `battlefield_level_virtual`, `times_virtual` FROM `players` WHERE (`battlefield_level_virtual` > 50 AND `tennis_won_virtual` > 50) ORDER BY `times_virtual` ASC;
+----+---------------+--------------------+---------------------------+---------------+
| id | names_virtual | tennis_won_virtual | battlefield_level_virtual | times_virtual |
+----+---------------+--------------------+---------------------------+---------------+
| 5 | Phil | 130 | 98 | 7 |
| 6 | Henry | 68 | 87 | 17 |
+----+---------------+--------------------+---------------------------+---------------+
A NoSQL database is a non-relational database meaning that
Database Terminology & Concepts [1]
| SQL/Relational Database Term | NoSQL/MongoDB Term |
|---|---|
| database | database |
| table | collection |
| row | document/BSON document |
| column | field |
- [1] Wikipedia (https://en.wikipedia.org/wiki/Database)
- [1] Wikipedia (https://en.wikipedia.org/wiki/Relational_database)