Relational databases connect tables through three fundamental relationship types: one-to-one, one-to-many, and many-to-many. Each follows the third normal form (3NF) principles with specific key and constraint strategies.

1. One-to-One Relationship

One entity is linked to exactly one instance of another entity. The primary table holds an id as a primary key. The dependent table includes a foreign key column with a UNIQUE constraint to enforce the one-to-one cardinality.

CREATE TABLE employee (
 emp_id VARCHAR(32) PRIMARY KEY,
 name VARCHAR(30)
);

CREATE TABLE spouse (
 spouse_id VARCHAR(32) PRIMARY KEY,
 name VARCHAR(30),
 employee_id VARCHAR(32) UNIQUE,
 CONSTRAINT fk_spouse_employee FOREIGN KEY (employee_id) REFERENCES employee(emp_id)
);

Without the UNIQUE constraint on employee_id, the relationship would become one-to-many. For example:

INSERT INTO employee VALUES ('E01', 'Alice');
INSERT INTO employee VALUES ('E02', 'Bob');
INSERT INTO employee VALUES ('E03', 'Charlie');

INSERT INTO spouse VALUES ('S01', 'Diana', 'E02');
INSERT INTO spouse VALUES ('S02', 'Eve', 'E01');
-- This would fail because E01 already linked to S02:
INSERT INTO spouse VALUES ('S03', 'Frank', 'E01');  -- UNIQUE violation
-- This fails because E04 does not exist in employee:
INSERT INTO spouse VALUES ('S03', 'Grace', 'E04');  -- Foreign key violation

INSERT INTO employee VALUES ('E04', 'Hank');
INSERT INTO spouse VALUES ('S04', 'Ivy', 'E04');    -- OK

Query to retrieve married couples:

SELECT e.name AS employee_name, s.name AS spouse_name
FROM employee e
INNER JOIN spouse s ON e.emp_id = s.employee_id;

2. One-to-Many Relationship

One record in the primary table can be associated with many records in the dependent table. The primary table has a primary key, and the dependent table includes a foreign key (without UNIQUE).

CREATE TABLE customer (
 cust_id VARCHAR(32) PRIMARY KEY,
 name VARCHAR(30),
 email VARCHAR(50)
);

CREATE TABLE order_tbl (
 order_id VARCHAR(32) PRIMARY KEY,
 product VARCHAR(30),
 price NUMERIC(10,2),
 cust_id VARCHAR(32),
 CONSTRAINT fk_order_customer FOREIGN KEY (cust_id) REFERENCES customer(cust_id)
);

Insert sample data:

INSERT INTO customer VALUES ('C01', 'John', 'john@example.com');
INSERT INTO customer VALUES ('C02', 'Jane', 'jane@example.com');
INSERT INTO customer VALUES ('C03', 'Jim', 'jim@example.com');

INSERT INTO order_tbl VALUES ('O001', 'Laptop', 800, 'C01');
INSERT INTO order_tbl VALUES ('O002', 'Mouse', 25, 'C01');
INSERT INTO order_tbl VALUES ('O003', 'Keyboard', 45, 'C01');
INSERT INTO order_tbl VALUES ('O004', 'Monitor', 200, 'C02');

Foreign key can be NULL to represent unassigned orders:

INSERT INTO order_tbl (order_id, product, price) VALUES ('O005', 'Cable', 10);
INSERT INTO order_tbl (order_id, product, price) VALUES ('O006', 'Hub', 15);

Query to list customers with their orders:

SELECT c.name AS customer_name, o.product, o.price
FROM customer c
INNER JOIN order_tbl o ON c.cust_id = o.cust_id;

3. Many-to-Many Relationship

Each entity can relate to many instances of the other. This requires three tables: two entity tables and one junction (relationship) table. The junction table uses a composite primary key and two foreign keys.

Step 1: Create entity tables with primary keys only.

CREATE TABLE student (
 stud_id VARCHAR(32) PRIMARY KEY,
 name VARCHAR(30)
);

CREATE TABLE course (
 course_id VARCHAR(32) PRIMARY KEY,
 title VARCHAR(30)
);

Step 2: Create the junction table, then add composite primary key and foreign keys (order matters).

CREATE TABLE enrollment (
 stud_id VARCHAR(32) NOT NULL,
 course_id VARCHAR(32)
);

ALTER TABLE enrollment ADD CONSTRAINT pk_enrollment PRIMARY KEY (stud_id, course_id);
ALTER TABLE enrollment ADD CONSTRAINT fk_enroll_student FOREIGN KEY (stud_id) REFERENCES student(stud_id);
ALTER TABLE enrollment ADD CONSTRAINT fk_enroll_course FOREIGN KEY (course_id) REFERENCES course(course_id);

Insert sample data:

INSERT INTO student VALUES ('S001', 'Linda');
INSERT INTO student VALUES ('S002', 'Mike');
INSERT INTO student VALUES ('S003', 'Nancy');

INSERT INTO course VALUES ('C101', 'Math');
INSERT INTO course VALUES ('C102', 'Physics');
INSERT INTO course VALUES ('C103', 'Chemistry');
INSERT INTO course VALUES ('C104', 'Biology');
INSERT INTO course VALUES ('C105', 'History');

INSERT INTO enrollment VALUES ('S001', 'C101');
INSERT INTO enrollment VALUES ('S001', 'C103');
INSERT INTO enrollment VALUES ('S001', 'C104');
INSERT INTO enrollment VALUES ('S002', 'C102');
INSERT INTO enrollment VALUES ('S002', 'C103');

Query to show which students are enrolled in which courses:

SELECT s.name, c.title
FROM student s
INNER JOIN enrollment e ON s.stud_id = e.stud_id
INNER JOIN course c ON c.course_id = e.course_id;