curriculum/challenges/english/blocks/lecture-working-with-relational-databases/687ea8e0cab41019dc8a728a.md
Like you learned in previous lessons, relational databases store data in tables, which are made of rows and columns.
The "relational" aspect of "relational databases" comes from the fact that records in different tables can have different types of relationships.
There are five main types of relations:
Two tables have a one-to-one relationship when each record in the first table can be associated with at most one record in the second table, and vice versa.
For example, let's say a company only assigns one vehicle per employee.
In that case, the database could have an employees table and a vehicles table, where each employee record corresponds to one, and only one, vehicle record in the database.
In contrast, one-to-many relationships occur when one record in a table can be related to one or more records in another table.
For example, if we have a customers table and an orders table, each customer could be related to one or more orders but each order can only be related to one customer.
This is the same as the many-to-one relation, but from the opposite perspective. One or more orders can be related to one customer.
Many-to-many relationships occur when a record in a table can be related to multiple records in another table and vice versa.
For example, let's analyze a database for a library. For simplicity purposes, let's assume that this database only has two tables: books and authors.
An author can write multiple books, and a single book can be written by multiple authors.
Implementing this type of relationship is a little bit more complex because the relational model at its core doesn't support many-to-many relationships directly.
To handle this, you would usually create an intermediate table, also known as a junction table.
This table solves these initial limitations by transforming the many-to-many relationship into two one-to-many relationships.
It creates a one-to-many relationship between the first table and the junction table and a one-to-many relationship between the second table and the junction table.
The name of the junction table is usually a combination of the entities. In the context of our example of books and authors, you may call it books_authors.
In the junction table itself, you would associate the authors and their books by creating two columns: a column for the author's id (author_id) and another column for the book's id (book_id).
These would be foreign keys referencing their corresponding primary keys in the authors and books tables, respectively.
With this junction table, you'll be able to query all the authors of a given book or all books written by a given author.
Using a junction table makes it easier to query the data, prevents redundancy, and simplifies the database schema. They are very helpful for implementing many-to-many relationships.
And finally, we find self-referencing relationships, which occur when the records of a table can be related to other records on the same table. This is also known as a recursive relationship.
Understanding these relationships is essential for designing and modeling complex and efficient relational databases.
In which type of relationship can one record in Table A be associated with zero, one, or many records in Table B, while each record in Table B can be associated with only one record in Table A?
One-to-one
Think about which relationship type explicitly describes a single entity on one side relating to multiple entities on the other.
One-to-many
Many-to-many
Think about which relationship type explicitly describes a single entity on one side relating to multiple entities on the other.
Self-referencing
Think about which relationship type explicitly describes a single entity on one side relating to multiple entities on the other.
2
What is a many-to-many relationship in a relational database?
A relationship where one row in a table is related to many rows in another table.
Think about how the entities can be related to each other.
A relationship where one row in a table is related to only one row in another table.
Think about how the entities can be related to each other.
A relationship where multiple rows in one table are related to multiple rows in another table.
A relationship where there is no connection between the tables.
Think about how the entities can be related to each other.
3
Which type of relationship involves records within the same table being linked to other records in that same table?
One-to-many
Think about a scenario where an entity has a relationship with other entities of the same type.
Many-to-one
Think about a scenario where an entity has a relationship with other entities of the same type.
Many-to-many
Think about a scenario where an entity has a relationship with other entities of the same type.
Self-referencing
4