Types of relationships in relational databases. How are One-to-One and Many-to-Many implemented?

In relational databases, relationships between tables can be of several types:

• One-to-One: Each record in the first table is linked to exactly one record in the second table.
• One-to-Many: One record in the first table is linked to multiple records in the second.
• Many-to-Many: Records in the first table can be linked to multiple records in the second, and vice versa.

Implementation of One-to-One:

Usually implemented using a foreign key with a unique constraint in one of the tables.

CREATE TABLE Person (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Passport (
    id INT PRIMARY KEY,
    person_id INT UNIQUE,
    passport_number VARCHAR(50),
    FOREIGN KEY (person_id) REFERENCES Person(id)
);

Here, person_id in the Passport table references Person and is unique, ensuring a one-to-one relationship.

Implementation of Many-to-Many:

Requires creating an intermediate (join) table that contains foreign keys to both related tables.

CREATE TABLE Student (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Course (
    id INT PRIMARY KEY,
    title VARCHAR(100)
);

CREATE TABLE Student_Course (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Student(id),
    FOREIGN KEY (course_id) REFERENCES Course(id)
);

Thus, a student can be enrolled in multiple courses, and a course can have many students.