How is the relationship achieved in a relational database?

Relationships in relational DBMS are achieved through foreign keys.

A foreign key is a column or a set of columns in one table that references the primary key in another table. This establishes a link between the records in the two tables.

Examples of relationship types:

• One-to-Many: One record in the first table is related to multiple records in the second table. For example, one department can have many employees. In the Employees table, there will be a foreign key referencing the primary key in the Departments table.
• Many-to-Many: Multiple records in the first table are related to multiple records in the second table. For example, a student can study multiple courses, and a course can be studied by multiple students. This is implemented using an intermediate (junction) table containing foreign keys referencing the primary keys of both tables.
• One-to-One: One record in the first table is related to only one record in the second table. This is a less common relationship type and is often used for vertical partitioning of a table or storing optional data. It is implemented with a foreign key in the second table referencing the primary key in the first table, often with a unique constraint on the foreign key.

Example of creating a table with a foreign key in SQL:

-- Create Departments table
CREATE TABLE Departments (
    department_id INT PRIMARY KEY, -- Primary key
    department_name VARCHAR(100)
);

-- Create Employees table with a foreign key
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT, -- Column for foreign key
    FOREIGN KEY (department_id) REFERENCES Departments(department_id) -- Foreign key definition
);

Foreign keys ensure referential integrity, preventing the creation of "orphan" records (when a foreign key references a non-existent record in the related table).