SQL guide: A mathematical view towards data (Part 1)
Before we set the mathematical tone for our data, lets get familiar with a few mathematical terms.
- Set: A set is a collection of distinct data structures.
- Multi-set (aka 'bag'): A multi-set is a collection of data structures where even duplicates are allowed.
For example, A = { 1, 2, 3, 4, 5, 6 } is a set whereas on contrast,
B = { 1, 2, 3, 4, 5, 2 } is not a set but a multi-set.
If you want to see from a perspective of a database table then:
Table 1:
| Brand | Model | Launched | Buyer |
|---|---|---|---|
| BMW | X5 | 2006 | Torsten Frings |
| Audi | Q7 | 2009 | David Villa |
| Mercedes | GLE | 2003 | Lukas Podolski |
| Cadilac | XT4 | 2009 | Carles Puyol |
Table 2:
| Brand | Model | Launched | Buyer |
|---|---|---|---|
| BMW | X5 | 2006 | Torsten Frings |
| Audi | Q7 | 2009 | David Villa |
| Mercedes | GLE | 2003 | Lukas Podolski |
| Cadilac | XT4 | 2009 | Carles Puyol |
| BMW | X5 | 2006 | Torsten Frings |
In the above image, table 1 is a set whereas table 2 is a multi-set (take a glance at the first and last entry).
NOTE:
Do not get confused because of repeated entries like '2009' in the Launched column of table 1. We need to consider the entire row as an element of the set; not just a column value!
Three-valued Logic:
As software engineers we are familiar with the boolean values and logic. But have you ever thought of whether there might be a possibility of something beyond the boolean logic?
If not, then lets get straight up to the world of 3-valued logical system.
Now, you might be wondering why do we need such a system? What are the 3 members of this system?
The answer to all your rising questions is NULL. Null is the culprit to blame here.
Null is something which can be inferred in many ways when encountered in a database table:
- Does not exist
- Not applicable
- A missing value
- Unknown
And null itself is the third member of our 3-valued logic system along with true and false.
Lets just dive straight into a truth table and a few logical propositions which will certainly come in handy when you'll dive straight into writing some business queries for your back-end services.
Consider, U = Unknown (or null), T = True and F = False.
| x | y | x AND y | x OR y | NOT x |
|---|---|---|---|---|
| F | F | F | F | T |
| F | T | F | T | T |
| F | U | F | U | T |
| T | F | F | T | F |
| T | T | T | T | F |
| T | U | U | T | F |
| U | F | F | U | U |
| U | T | U | T | U |
| U | U | U | U | U |
REMEMBER: So going forward, never forget that SQL uses 3-valued logic and not boolean logic.
What is an SQL Table?
An SQL table is a multi-set of tuples (rows).
- Duplicates matter.
- Nulls are allowed.
- Primary key is not mandatory to have for a table.
So, in the world of sets, table 2 gets narrowed down into table 1 but in the world of databases, table 1 != table 2.
That's it for the mathematical intuition which we need to have before writing SQL queries.
In part 2, we shall level a further up on our conceptual knowledge, begin with writing complex queries and explore all common misinterpretations we carry with ourselves while writing SQL queries which might lead to delusional results.
