A tutorial on an intermediate SQL topic. These observations are a great example of a database with a many-to-many relationship. Again, let me use astronomy for a SQL example.
“_In the mystical moist night-air, and from time to time, Look’d up in perfect silence at the stars._” — Walt Whitman
Recently, I came across a personal journal where I noted the objects I observed in the night sky with my telescope. Most of the entries are from high school and college summer breaks when I could stay up later at night and (somewhat) sleep in the next day. I’d start every summer by observing the late Spring constellations like Hercules with his Great Globular Cluster. Sometime around the middle of July, I would see a beautiful crescent Moon setting over Lake Michigan. This was also my favorite time to see The Summer Triangle with Lyra and it’s Ring Nebula. Finally, I’d end the summer by looking for the Andromeda Galaxy.
These deep-sky objects (objects like globular clusters and galaxies) occur relative to the same constellations. However, there are some objects (those inside the solar system) like the Moon, planets, and comets that change positions and move relative to the constellations throughout the year. In other words, I can observe the Moon or Jupiter in different constellations such as Taurus in the winter and Leo in the spring.
These observations are a great example of a database with a many-to-many relationship. Again, let me use astronomy for a SQL example. Let’s begin.
What is a many-to-many relationship in a SQL database? It’s an instance where multiple records in one table are related to multiple records in another table. After a bit of searching, a common example is a database made for a school. The school has many students that can take many classes at a time. At the same time, there isn’t one student per class — but many students per class. (For more on this example, I recommend reading this DZone tutorial.)
I want to use a different example to showcase this concept. Consider a list of constellations you can see in the summer (in the below picture on the left) and a list of objects that can be seen in the summer night sky (on the right). The list of objects are in some constellation from the left list. For example, the Ring Nebula can be observed in the constellation Lyra but the Moon can be observed next to many constellations in the Summer (here, Scorpius and Pisces). You can also see M-80 in Scorpius but M-82 is seen in Ursa Major.
The relationship between items in two lists.
How do you store this information in a database? Well, let’s try using one table. Coded up in a table called
wrong_table, this looks like the following.
One table to store the information.
But, how can we query this table? Specifically, look at the record for Scorpius and the record for Pisces.
For Scorpius, M-80 and The Moon occur together as a text field. The Moon also occurs in the Pisces record. Say I want to count how many times The Moon occurs. Let’s try.
SELECT * FROM wrong_table WHERE name_obs = 'The Moon';
Alright, that’s odd — the result is just the Pisces record. Why? The Moon’s instance in the record for Scorpius is tied to M-80 in that
M-80, The Moon are in the same field and not distinct from each other. How can I count occurrences of The Moon if one record is not distinct from M-80?
Ok, let’s try another approach. Say we put each occurrence in its own column, like in
A view of wrong_table_2.
There are a few issues with this. Let’s try finding the instances of The Moon.
SELECT * FROM wrong_table_2 WHERE name_obs_1 = 'The Moon' OR name_obs_2 = 'The Moon';
Ok, now let’s try to count the instances… but when we do, the columns used in the
WHERE line can theoretically grow (countably) infinitely long. Meaning, I can add more observations to this table for the Scorpius record.
What a mess!
SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.
Your Ultimate Guide to SQL Join: CROSS JOIN. In this tutorial, you will learn how to use the SQL Server CROSS JOIN to implement a combination of 2 tables without a join condition.
Your Ultimate Guide to SQL Join: INNER JOIN. This article covers information about SQL Server INNER JOIN including definition of SQL Join, syntax and 10 examples of INNER JOINs.
Your Ultimate Guide to SQL Joins: OUTER JOIN. In this article, we'll discuss SQL Server OUTER JOIN command: its types, practical uses, and differences from INNER JOIN.
SQL Self Join is a join in which a table is joined with itself means we are joining a table with that same table (also called Unary relationships).