PostgreSQL table relationships examples

gistfile1.txt

DROP schema IF EXISTS anime cascade; --USE ONLY IN PRE-PROD ENVIRONMENTS
DROP schema IF EXISTS audio cascade; --USE ONLY IN PRE-PROD ENVIRONMENTS
DROP schema IF EXISTS persons cascade; --USE ONLY IN PRE-PROD ENVIRONMENTS
DROP schema IF EXISTS students cascade; --USE ONLY IN PRE-PROD ENVIRONMENTS

create schema anime;

create table anime.anime
(
id serial not null,
name varchar not null
);

create unique index anime_id_uindex on anime.anime (id);

alter table anime.anime add constraint anime_pk primary key (id);


/* ******************************************
	Just example with constraints
*********************************************/
create schema audio;

create table audio.audio_track
(
    audio_track_id   text not null,
    audio_country_id text not null,
    name             text,
    constraint audio_track_pkey
        primary key (audio_track_id, audio_country_id)
);

create table audio.audio_chart
(
    audio_chart_id   text not null
        constraint audio_chart_pkey
            primary key,
    audio_country_id text not null,
    --audio_country_id text not null
        --constraint audio_chart_country_id_fk
            --references region,
    name             text not null
);

create table audio.audio_chart_track_statistic
(
    chart_date       date     not null,
    audio_track_id   text     not null,
    audio_country_id text     not null,
    audio_chart_id   text     not null
        constraint audio_chart_id_fk
            references audio.audio_chart,
    current_position smallint not null,
    constraint audio_chart_track_statistic_pkey
        primary key (audio_chart_id, current_position, chart_date),
    constraint audio_track_id
        foreign key (audio_track_id, audio_country_id) references audio.audio_track
);



-- for the reference:
-- https://github.com/RAstardzhiev/Software-University-SoftUni/tree/master/Databases%20Basics%20-%20MS%20SQL%20Server/Table-Relations-Exercises
-- https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de
-- https://www.sqlshack.com/learn-sql-types-of-relations/

create schema persons;

/* ******************************************
	Problem 1.	One-To-One Relationship
*********************************************/

CREATE TABLE persons.Passports
(
             PassportID     serial  NOT NULL,
             --PassportID     INT IDENTITY(101, 1) NOT NULL,
             PassportNumber CHAR(8) NOT NULL,
             CONSTRAINT PK_Passports PRIMARY KEY(PassportID)
);

CREATE TABLE persons.Persons
(
             PersonId   serial NOT NULL,  --PK
             --PersonId   INT IDENTITY NOT NULL,
             FirstName  VARCHAR(50) NOT NULL,
             Salary     DECIMAL(10, 2),
             PassportID INT--UNIQUE
             UNIQUE NOT NULL,
             CONSTRAINT PK_Persons PRIMARY KEY(PersonId),
             CONSTRAINT FK_Persons_Passports FOREIGN KEY(PassportID) REFERENCES persons.Passports(PassportID) ON DELETE CASCADE
);

INSERT INTO persons.Passports (PassportNumber)
VALUES
(
       'N34FG21B'
),
(
       'K65LO4R7'
),
(
       'ZE657QP2'
);

INSERT INTO persons.Persons (FirstName, Salary, PassportID)
VALUES
(
       'Roberto',
       43300.00,
       2
),
(
       'Tom',
       56100.00,
       3
),
(
       'Yana',
       60200.00,
       1
);

/* ******************************************
	Problem 2.	One-To-Many Relationship
*********************************************/

CREATE TABLE persons.Manufacturers
(
             ManufacturerID serial NOT NULL,
             Name           VARCHAR(50) NOT NULL,
             EstablishedOn  DATE DEFAULT now(),
             CONSTRAINT PK_Manufacturers PRIMARY KEY(ManufacturerID)
);

CREATE TABLE persons.Models
(
             ModelID        INT NOT NULL,
             Name           VARCHAR(50) NOT NULL,
             ManufacturerID INT NOT NULL,
             CONSTRAINT PK_Models PRIMARY KEY(ModelID),
             CONSTRAINT FK_Models_Manufacturers FOREIGN KEY(ManufacturerID) REFERENCES persons.Manufacturers(ManufacturerID)
);

INSERT INTO persons.Manufacturers (Name, EstablishedOn)
VALUES
(
       'BMW',
       '07/03/1916'
),
(
       'Tesla',
       '01/01/2003'
),
(
       'Lada',
       '01/05/1966'
);

INSERT INTO persons.Models
VALUES
(
       1,
       'X1',
       1
),
(
       2,
       'i6',
       1
),
(
       3,
       'Model S',
       2
),
(
       4,
       'Model X',
       2
),
(
       5,
       'Model 3',
       2
),
(
       6,
       'Nova',
       3
);
/* ******************************************
	One-To-Many Relationship QUERIES
--GET ALL Manufacturers and models
SELECT  ma.manufacturerid, ma.name,  ma.establishedon, modelid, mo.name, mo.manufacturerid
FROM persons.manufacturers ma, persons.models mo where ma.manufacturerid = mo.manufacturerid;
--OR:
SELECT * from persons.manufacturers manufacturers inner join persons.models models on manufacturers .manufacturerid = models.manufacturerid;
*********************************************/

/* ******************************************
	Problem 3.	Many-To-Many Relationship
*********************************************/

create schema students;

CREATE TABLE students.Students
(
             StudentID serial NOT NULL,
             Name VARCHAR(255) NOT NULL,
             CONSTRAINT PK_Students PRIMARY KEY(StudentID)
);

CREATE TABLE students.Exams
(

             ExamID INT NOT NULL,
             Name VARCHAR(255) NOT NULL,
             CONSTRAINT PK_Exams PRIMARY KEY(ExamID)
);

CREATE TABLE students.StudentsExams
(
             StudentID INT NOT NULL,
             ExamID    INT NOT NULL,
             CONSTRAINT PK_StudentsExams PRIMARY KEY(StudentID, ExamID),
             CONSTRAINT FK_StudentsExams_Students FOREIGN KEY(StudentID) REFERENCES students.Students(StudentID),
             CONSTRAINT FK_StudentsExams_Exams FOREIGN KEY(ExamID) REFERENCES students.Exams(ExamID)
);

INSERT INTO students.Students (name)
VALUES
(
       'Mila'
),
(
       'Toni'
),
(
       'Ron'
);

INSERT INTO students.Exams
VALUES
(
       1,
       'SpringMVC'
),
(
       2,
       'Neo4j'
),
(
       3,
       'Oracle 11g'
);

INSERT INTO students.StudentsExams
VALUES
(
       1,
       1
),
(
       1,
       2
),
(
       2,
       1
),
(
       3,
       3
),
(
       2,
       2
),
(
       2,
       3
);