What Kind of Relations Exist in the Database?
A relational database is a kind of network of entities and attributes. As we saw in the example with the customer list, it can save us from entering the same information many times. Instead, we can enter it in a single place and thendefine relations between different entries.
When we define such relations, there are two things we need to consider: what is called “cardinality” and “membership".
The cardinality is the connection in the relation between the entities. Membership is about whether a relation must exist or not. So, you’ve got it now!
No, we understand that this is not self-explanatory. So let’s delve into it. We start with cardinality.
Cardinality
There are three forms of cardinality in a relational database: One-to-one, one-to-many, and many-to-many. Let’s take them in turn:
One-to-one
A one-to-one relation indicates that an instance (data row) of an entity has one – and only one! – relation to an instance of another entity. For example:
- A country has a capital; a capital belongs to a country
- A citizen has a passport; a passport belongs to a citizen
People can have multiple citizenships, but within one country they only have one passport—and conversely, only one passport belongs to one person.
However, people get new passports every few years, and there are historical examples of countries that have changed their capital. So we see that it is necessary to separate these entities.
You can see these examples illustrated below. The words ("has”, “belongs to”) are there only to make the relation readable and logical for us humans—they do not exist in the database itself. The horizontal lines between the entities show the relation, which is created in the database. The vertical lines that cross the relation line close to the entities, tell the database that the relation is one-to-one; that a country can only have one capital and a citizen can only have one passport at a time.
One-to-many
In one-to-many relations, it is indicated that one instance of an entity can have a relation to many instances of another entity. For example:
- A person owns many books; many books belong to a person
- A recycling station contains many recycling points (such as glass and metal, paper and cardboard, plastic, compost, general waste); many recycling points are located at a recycling station.
The side that there is only one of (to the left in the figure) is the same as in one-to-one relations. The other side, where many instances can be included, is shown with three lines – what is called “crow’s feet”:
Many-to-many
The last cardinality is many-to-many relations. Many-to-many relations indicate that many instances of an entity can have relations to many instances of another entity. For example:
- Many customers buy many products; many products are bought by many customers
- Many consumers use many recycling points; many recycling points are used by many consumers
In a grocery store, there are many customers who buy milk, and many cartons of milk are bought by many customers. The same with bread, coffee, and all the other things we need.
We illustrate many-to-many relations with crow’s feet at both ends of the relation:
By using the plural form, we can simplify the reading of the relations without using the word “many”:
Customers buy products, products are bought by customers. Consumers use recycling points, recycling points are used by consumers.
Are you following? This was cardinality, or the connection in the relationship between the entities. Let’s now look at the other important aspect of a relationship, namely membership.
Membership
Membership adds information to the cardinality and comes in two mutually exclusive variants:
- Mandatory membership
- Optional membership
Here, we also need to delve a little deeper to understand what this actually means:
Membership in one-to-one relationships
We can extend the cardinality in one-to-one relationships with memberships. For example:
- One and only one country has a capital; one and only one capital belongs to one country
- One and only one citizen has a passport; one or zero passports belong to a citizen
Do you see the difference from before? We have now included information about whether a relationship must exist or not.
The first relationship in this example has mandatory membership. All countries have capitals, and all capitals belong to a country. This relationship must therefore exist. We cannot have a country that does not have a capital, and there is no capital that does not belong to a country.
Mandatory membership between entities in a relational database means that one cannot legally store an instance of one of the entities without it having a relationship to an instance in the other entity. In this example, it would mean that one cannot store a country in the database without an associated capital, nor store a city as a capital without it having a relationship to a country.
However, the second relationship is not mandatory. A person is a citizen of a country, but that person does not need to have a passport.
Optional memberships tell the relational database that there may be relationships between instances in the entities, but that instances can be stored without this relationship existing for the individual instance.A citizen can be registered without the citizen having to have a passport.
We read: One and only one country has one and only one capital, one and only one capital belongs to one and only one country. One and only one citizen has zero or one passport, one or zero passports belong to one and only one citizen.
Notice the nuance that a passport cannot exist in this database without it belonging to a citizen.
In certain databases—for example, related to logistics and production of blank passports—this could have been useful. By adding zero to the membership at the citizen entity in the relationship, the database could store passports that belong to zero or one citizen.
Membership in one-to-many relationships
Next, we can extend the cardinality in one-to-many relationships with memberships.
- One and only one person owns 0, 1, or many books; 0, 1, or many books belong to one and only one person
- One and only one recycling station contains one or many recycling points, one or many recycling points are located at one and only one recycling station
The first example is not mandatory. Persons present in the database may, but need not, own one or more books. We show this with zero next to the crow’s feet on the many-side of the relationship.
With the recycling stations, it’s a bit different. Logically, it makes little sense to say that we have a recycling station without any return points. In a relational database that lists recycling stations in a place, it should not be possible to register stations where no one can deliver any form of waste or recycled material.
Therefore, we add a line to the many-side and say that there can be many, and there must be at least one recycling point if a recycling station is to be stored in the database. We do the same on the one-side, thereby stating that the database does not accept the storage of recycling points that are not located at a recycling station.
We read: One and only one person owns zero, one, or many books; many, one, or zero books are owned by one and only one person. One and only one recycling station contains one or many recycling points; many or one recycling points are located at one and only one recycling station.
As in the example with citizens and passports above, we make assumptions in the last example—in this case, that we cannot have a station without points, and points cannot exist if they are not located at a station.
We call these rules database semantics.
There are no formal rules for how such rules are established, they are based on the logic of what the database is to be used for, and are defined by us humans when the database modelling begins.
If, in our example, we imagine that the database is created by an organisation with the purpose of making practical information available about where the population in an area can deliver different types of waste, our limits make sense. This organisation has no immediate benefit from knowing about points that no one can use, or areas where stations could have been, if there are no points there.
For a hypothetical waste management company, on the other hand, it would certainly have been interesting to know about both points that are in storage as well as qualified areas where stations could be set up in the future, which would have changed the membership to non-obligatory in the database.
Membership in many-to-many relationships
Moving on to the many-to-many relationships, we see that the same repeats there. Membership thus works in the same way regardless of whether the cardinality is one-to-one, one-to-many or many-to-many.
- 0, 1 or many customers buy 1 or many products; 1 or many products are bought by 0, 1 or many customers
- 0, 1 or many consumers use 0, 1 or many recycling points; 0, 1 or many recycling points are used by 0, 1 or many consumers
There may be many instances of products in the store that many customers buy in a day, but it does not mean that all customers who enter the store must buy that product.
Not all products have instances sold every day, but the products must exist for the customer instances to be able to choose them.
Similarly, we can look at our recycling points. Many consumers use many recycling points and many recycling points are used by many consumers, but it is not the case that all consumers must use the recycling points, or that all recycling points must be used by all, or any of, the customers.
Thus, we have these zeros on the crow’s feet on each side of the cardinality in this relation.
We read: Many customers buy one or many products; one or many products are bought by zero, one, or many customers. Many consumers use zero, one, or many recycling points; many recycling points are used by zero, one, or many consumers.
Again, we see database semantic choices, such as a customer must buy a product. We will not delve into this further, but a good way to start the formulation of database semantics is to write in your own words what the database should be able to show, and thus what it must be able to store. This work should start early in the modelling process.
Insight
Databases and code
You have now learned a lot about the logic in databases, but let’s be honest: We have gone a bit around the bush. In a real database, there are not really any crow’s feet or boxes that we draw lines between. Instead, there are rows and columns with data ... and there is SQL.
SQL, or Structured Query Language, is your best friend when working with relational databases in practice. Then we work in a specific software: a database management system. Here we can sort, explore and, for example, give other apps the opportunity to search and retrieve data from the database.
To do this, we write commands with the SQL language. This is a so-called query language, a kind of computer language which is made specifically to be used with relational databases.
It is by writing instructions in SQL that we, for example, retrieve or update entries in the database.
The commands we write are largely ordinary English—only that we have more or less replaced normal English grammar with SQL grammar.
What we enter are commands such as “Select” (to retrieve data), “Update” (to update data), “Delete” (to delete data) and “Insert into” (to add data) - along with the specific entities and attributes we want to choose, update, delete or change.
It might look like this, for example: “SELECT Firstname, Lastname FROM Customer;”, Then we will get a list of first names and last names (attributes) from the customer entity. The commands are like a question, which gives a response. That’s why these are called “queries”.
We will not delve further into SQL here, but now you know how this really works. For our part, we are not quite done with the crow’s feet just yet…