In a typical database, you can create, among other
things, two tables that each has a primary key and one of them has a foreign
key. As seen previously, the foreign key allows a child table to get records
from a parent table.

Normally, each record in the child table gets 0 or
only one value from the parent table and a record in the parent table can
provide one of its values to many records of the child table. An example
would be a list of employees where each employee belongs to a department.
Obviously, each employee can belong to only one department but many
employees can belong to the same department. This can be illustrated as
follows:

This type of relationship is referred to as
one-to-many. This is the most regular type of relationship used in a
relational database and that's the type we have used so far.

Mutual reference is a scenario in which each of two
tables references the other. As a variant to a one-to-many relationship,
some records of a table A may get their foreign value from a table B, then
some records of table B may get their foreign value from a table C, and
finally some records of table C would get their foreign value from table
A.

Another variant is where some records of a table A
would get their foreign value from a table B but also some records of the
table B would get their foreign value from table A. To illustrate, once
again imagine you have a table of employees and each employee is recorded
as belonging to a certain department. Obviously, an employee can (should)
belong to only one department. This can be illustrated as follows:

For each department, you may want to specify who the
manager is. Obviously, the manager must be an employee, from the table of
employees. This can be illustrated as follows:

If you decide to create a diagram (especially if you
didn't create the primary and foreign keys), you should have a link going
from each table to the other, using the appropriate fields. Here is an
example:

When creating the records, you can proceed as done so
far. Here are examples:

Imagine you have two lists where a value from one list
can provide 0 or 1 value to a record of the other list, and only one
record of a child list can get its foreign value from the other list. This
can be illustrated as follows:

This type of relationship is referred to as
one-to-one. To give you an example, imagine you have a list of employees
and you want to specify the supervisor or manager of each employee. This
can be illustrated as follows:

By definition, a manager is primarily an employee like
any other. This means that the primary information of a manager is the
same as that of any other employee. This also implies that if you had to
use separate tables, one for managers and another for employees, you would
have two similar tables, and there is a chance that information would be
duplicated in both tables. As a result, a one-to-one relationship is
usually created using only one table, in which case the table would
reference itself. In other words, some records would reference (be linked
to) other records of the same table. This can be illustrated as follows:

The regular SELECT * statement of this table only
shows the list of employees and the supervisor of each employee appears
only as a number, which can make it difficult to actually identify the
supervisor:

By using a join, you can create a SELECT
statement where the JOIN is ON itself.
When formulating the statement, you must use the table twice, in which
case you should (must) create an alias for each. Here is an example:

Just as done for many tables in a database, you can
create a table that relates to itself. To start, the table must have a
primary key. Since you are creating an actual relationship, the table must
have a foreign key and that key must reference the primary key of the same
table. Of course, the name of the column that represents the foreign key
must have a different name than that of the primary key column. Here is an
example:

Imagine you have a list of students who are
registering for courses in a new semester:

A student can register for one course (a type of one-to-one
relationship)

A student can register for many courses

Put it another way, many courses can have been registered by one
student

Many students can register for the same course:

If many students can register for the same course,
this means that one course can have been registered by many students:

As a result, many students can register for many courses (or many
students can share many courses):

This type of relationship is referred to as
many-to-many.

Most of the time, to implement a many-to-many
relationship, besides the two tables that hold the normal records, you
would create one more table referred to as a junction table. The job of
the junction table is to get a value from one table, associate it to the
desired value of another table, repeat this step as many times as
necessary, and produce the necessary list. This can be illustrated as
follows:

Obviously, the junction table should (must) have a
foreign key for each of the concerned tables. Here is an example of such a
table:

As a variance of a many-to-many relationship, instead of
just two tables, you can create a junction table that unites three or more
tables. Once again, consider the example of students registering for
courses:

When a semester starts, a student must select a semester for the
courses he wants to attend, and there are many courses available for
that semester. This means that the student would select a semester and
select one or more courses he wishes to attend. This can be illustrated
as follows:

Many students can register for the same semester:

To help them plan their academic career, many schools allow a
student to register courses for more than one semester. In this case, a
student can select (an) additional semester(s) and select courses she
wants to attend during each semester (in this example, we don't account
for a student who is repeating (re-taking) a course):

As a result, over the course of academic years:

A student would have attended many semesters:

Many students would have attended many semesters (or a semester
can "have" many students):

A semester would show courses that were attended by many
students:

Many semesters would show many courses that were available

A course can be offered in many semesters

Many courses can be offered in many semesters

A course can have many students who attended it

A course can have many students who attended it during different
semesters

The records of many students would show many semesters they used
to attend many courses

You create the junction table the same way you do for
two tables: Add a foreign key for each of the tables. During data entry:

Select a value from the first table. For our example, this would be
the student number of the student who is registering for the course

Select a value from the second table. The value must appropriately
correspond to that of the first table. For our example, this would be
the semester during which the selected student wants to attend one or
more courses

Select a value from the third table. The value must appropriately
correspond to that of the first table and that of the second table. For
our example, this would be the course that the student selected in the
first table wants to attend during the semester selected in the second
tabe

The beauty of this variant of a many-to-many
relationship would be revealed during data analysis when you want to find
out

Whether a certain course is available for a certain semester (for
one reason or another, some courses are not offered during some
semesters)

What (the names of) students registered for what semester. This
information helps with school statistics (enrollment, etc)

What courses a student attended during a certain semester

Did the student attend that course already?

How many courses (credits) has the student accumulated already?

Based on the student's major, is the course required for the
major? Is it required for the minor? Or is it an elective?

How many students have already registered for a certain course that
would start soon. That would allow you to find out whether: