In the realm of relational databases, cardinality relationships define the connections between tables and govern how instances of one entity relate to instances of another. Let's delve into three cardinality relationships with a consistent example, illustrating each with table declarations.

1. One-to-One (1:1) Relationship

In a one-to-one relationship, each record in the first table corresponds to exactly one record in the second table, and vice versa. Consider the relationship between Students and DormRooms:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    dorm_room_id INT UNIQUE,
    FOREIGN KEY (dorm_room_id) REFERENCES DormRooms(dorm_room_id)
);

CREATE TABLE DormRooms (
    dorm_room_id INT PRIMARY KEY,
    room_number INT
);

Here, each student is assigned one dorm room, and each dorm room is assigned to one student.

2. One-to-Many (1:N) Relationship

In a one-to-many relationship, each record in the first table can be associated with multiple records in the second table, but each record in the second table is associated with only one record in the first table. Consider the relationship between Departments and Professors:

CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE Professors (
    professor_id INT PRIMARY KEY,
    professor_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

In this case, each department can have multiple professors, but each professor is associated with only one department.

3. Many-to-Many (N:N) Relationship

In a many-to-many relationship, multiple records in the first table can be associated with multiple records in the second table, and vice versa. Consider the relationship between Students and Courses:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50)
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

CREATE TABLE StudentCourses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

In this scenario, many students can enroll in multiple courses, and each course can have multiple students.

Understanding these cardinality relationships is essential for designing robust and efficient relational databases, ensuring the integrity and consistency of data across tables.