What types of relationships exist in a database?

In relational databases, there are three main types of relationships:

1. One-to-One Each record in one table is related to only one record in another table. Example: The relationship between User and UserProfile tables, where each user has only one profile.

2. One-to-Many One record in a table can be related to multiple records in another table. Example: The relationship between Author and Book tables, where one author can write multiple books.

3. Many-to-Many Multiple records in one table can be related to multiple records in another table. Implemented through an intermediate (junction) table. Example: The relationship between Student and Course tables, where one student can attend multiple courses, and one course can have multiple students. The junction table can be called StudentCourse.

-- Example schema for Many-to-Many
CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE Course (
    course_id INT PRIMARY KEY,
    title VARCHAR(255)
);

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