Page 25 - Database-En-V3

2. One-to-many relationship:
For example, tables of students and results in a school database, the student has
multi records of the results for all academic semesters, this means that there are
many results in the academic record table for each student in the students table.
Therefore, the relationship between the Students table and the Academic Record
table is considered a one-to-many relationship.

To represent a one-to-many relationship in a database design, use the primary key
on the "one" side of the relationship and add it as an additional column or columns
to the table on the "many" side of the relationship. In this case, you would add the
Student ID column from the Students table to the Academic Record table.

Access program (example of database management system) will then be able to
use the Student ID in the Academic Records table to locate the correct student for
each semester. The Student ID column in the Academic Record table is called the
foreign key. A foreign key is a primary key of another table.

3. Many-to-many relationship:

Consider the relationship between the course results table and the reservation
courses table. One course may include more than one grade. On the other side,
each record in the Courses results table can have multiple records in the
reservation table. Each record in the reservation table can have multiple records in
the Course results table. This type of relationship is called a many-to-many
relationship. It should be noted that it is important to take both sides of the
relationship into account to discover many-to-many relationships between tables.

How can you solve this connection and relationship between data for two tables?

The solution is to create a third table, often called a connection table, where you
split the many-to-many relationship into two one-to-many relationships. You can
insert the primary key from both tables into the third table. As a result, the third
table records all instances of the relationship.

Defining relationships between tables helps ensure that you have the correct tables
and columns. Emphasize that, when there is a one-to-one or one-to-many
relationship, the embedded tables must share a common column or columns.
When a many-to-many relationship exists, a third table must be provided to
represent the relationship.

18 Training unit in the field of technological information - at the Supreme Council of Universities © Intellectual property rights 2024