Креирање на табели

CREATE TABLE actor (
    actor_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY ,
    first_name VARCHAR(45) NOT NULL,
    last_name VARCHAR(45) NOT NULL,
    last_update TIMESTAMP DEFAULT now() NOT NULL
);

CREATE TABLE category (
    category_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(25) NOT NULL,
    last_update TIMESTAMP DEFAULT now() NOT NULL
);

CREATE TABLE language (
    language_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    last_update TIMESTAMP DEFAULT now() NOT NULL
);

CREATE TABLE film (
    film_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    release_year INT,
    language_id BIGINT NOT NULL,
    original_language_id BIGINT,
    rental_duration SMALLINT DEFAULT 3 NOT NULL,
    rental_rate NUMERIC(4,2) DEFAULT 4.99 NOT NULL,
    length SMALLINT,
    replacement_cost NUMERIC(5,2) DEFAULT 19.99 NOT NULL,
    rating VARCHAR(10) DEFAULT 'G',
    last_update TIMESTAMP DEFAULT now() NOT NULL,
    special_features TEXT[],
    CONSTRAINT language_fk FOREIGN KEY (language_id) REFERENCES language(language_id) ON UPDATE CASCADE,
    CONSTRAINT original_language_fk FOREIGN KEY (original_language_id) REFERENCES language(language_id) ON UPDATE CASCADE

);

CREATE TABLE film_category (
    film_id BIGINT NOT NULL,
    category_id BIGINT NOT NULL,
    last_update TIMESTAMP DEFAULT now() NOT NULL,
    CONSTRAINT film_category_pk PRIMARY KEY (film_id, category_id),
    CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category(category_id) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE film_actor (
    actor_id BIGINT NOT NULL,
    film_id BIGINT NOT NULL,
    last_update TIMESTAMP DEFAULT now() NOT NULL,
    CONSTRAINT pk_film_actor PRIMARY KEY (actor_id,film_id),
    CONSTRAINT fk_actor FOREIGN KEY(actor_id) REFERENCES actor(actor_id) ON DELETE  RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_film FOREIGN KEY (film_id) REFERENCES film(film_id) ON DELETE  RESTRICT ON UPDATE CASCADE
);

CREATE TABLE country (
    country_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    country VARCHAR(50) NOT NULL,
    last_update TIMESTAMP DEFAULT now() NOT NULL
);

CREATE TABLE city (
    city_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    city VARCHAR(50) NOT NULL,
    country_id BIGINT NOT NULL,
    last_update TIMESTAMP DEFAULT now() NOT NULL,
    CONSTRAINT country_fk FOREIGN KEY (country_id) REFERENCES country(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE address (
    address_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    address VARCHAR(50) NOT NULL,
    address2 VARCHAR(50),
    district VARCHAR(20) NOT NULL,
    city_id BIGINT NOT NULL,
    postal_code VARCHAR(10),
    phone VARCHAR(20) NOT NULL,
    last_update TIMESTAMP DEFAULT now() NOT NULL,
    CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES city(city_id) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE store (
    store_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    manager_staff_id BIGINT NOT NULL UNIQUE,
    address_id BIGINT NOT NULL,
    last_update TIMESTAMP DEFAULT now() NOT NULL,
    CONSTRAINT address_fk FOREIGN KEY(address_id) REFERENCES address(address_id) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE customer (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    store_id BIGINT NOT NULL,
    first_name VARCHAR(45) NOT NULL,
    last_name VARCHAR(45) NOT NULL,
    email VARCHAR(50),
    address_id BIGINT NOT NULL,
    active BOOLEAN DEFAULT true NOT NULL,
    create_date DATE DEFAULT CURRENT_DATE NOT NULL,
    last_update TIMESTAMP DEFAULT now() NOT NULL,
    CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address(address_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store(store_id) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE staff (
    staff_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(45) NOT NULL,
    last_name VARCHAR(45) NOT NULL,
    address_id BIGINT NOT NULL,
    email VARCHAR(50),
    store_id BIGINT NOT NULL,
    active BOOLEAN DEFAULT true NOT NULL,
    username VARCHAR(16) NOT NULL,
    password VARCHAR(40),
    last_update TIMESTAMP DEFAULT now() NOT NULL,
    picture bytea,
    CONSTRAINT staff_address_fk FOREIGN KEY(address_id) REFERENCES address(address_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT staff_store_fk FOREIGN KEY(store_id) REFERENCES store(store_id) ON UPDATE CASCADE ON DELETE RESTRICT

);

CREATE TABLE inventory (
    inventory_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    film_id BIGINT NOT NULL,
    store_id BIGINT NOT NULL,
    last_update TIMESTAMP DEFAULT now() NOT NULL,
    CONSTRAINT film_fk FOREIGN KEY(film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT store_fk FOREIGN KEY (store_id) REFERENCES store(store_id) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE rental (
    rental_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    rental_date TIMESTAMP DEFAULT now() NOT NULL,
    inventory_id BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,
    return_date TIMESTAMP,
    staff_id BIGINT NOT NULL,
    last_update TIMESTAMP DEFAULT now() NOT NULL,
    CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory(inventory_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE payment (
    payment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    staff_id BIGINT NOT NULL,
    rental_id BIGINT NOT NULL,
    amount numeric(5,2) NOT NULL,
    payment_date TIMESTAMP DEFAULT now() NOT NULL,
    CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental(rental_id) ON UPDATE CASCADE ON DELETE RESTRICT
);

Пример скрипти за креирање на тест податоци

INSERT INTO language (name)
VALUES ('English'), ('Spanish'), ('French'), ('German'), ('Italian');

INSERT INTO category (name)
SELECT 'Category ' || i
FROM generate_series(1, 20) AS i;

INSERT INTO actor (first_name, last_name)
SELECT 'Actor' || i, 'Lastname' || i
FROM generate_series(1, 100) AS i;

INSERT INTO film
(title, description, release_year, language_id, original_language_id, rental_duration,
 rental_rate, length, replacement_cost, rating)
SELECT
    'Film #' || i,
    'Description for film ' || i,
    1980 + (i % 40),
    1 + (i % 5),
    NULL,
    3 + (i % 5),
    2.99 + (i % 5),
    60 + (i % 90),
    10.00 + (i % 20),
    CASE WHEN i % 5 = 0 THEN 'PG'
         WHEN i % 5 = 1 THEN 'G'
         WHEN i % 5 = 2 THEN 'PG-13'
         WHEN i % 5 = 3 THEN 'R'
         ELSE 'NC-17'
    END
FROM generate_series(1, 500) AS i;

INSERT INTO film_category (film_id, category_id)
SELECT f.film_id, ((f.film_id + gs - 1) % 20) + 1
FROM film f
CROSS JOIN generate_series(0, 2) AS gs;

INSERT INTO film_actor (actor_id, film_id)
SELECT ((f.film_id + gs - 1) % 100) + 1, f.film_id
FROM film f
CROSS JOIN generate_series(0, 3) AS gs;

INSERT INTO country (country)
VALUES ('USA'), ('UK'), ('Germany'), ('France'), ('Spain');

INSERT INTO city (city, country_id)
SELECT 'City ' || i, (1 + (i % 5))
FROM generate_series(1, 20) AS i;

INSERT INTO address (address, address2, district, city_id, postal_code, phone)
SELECT
    'Address ' || i,
    NULL,
    'District ' || (i % 10),
    1 + (i % 20),
    '1000' || (i % 10),
    '555-000-' || i
FROM generate_series(1, 200) AS i;

INSERT INTO store (manager_staff_id, address_id)
SELECT i, i
FROM generate_series(1, 5) AS i;

INSERT INTO staff (first_name, last_name, address_id, email, store_id, active, username, password)
SELECT
    'Staff' || i,
    'Lastname' || i,
    1 + (i % 50),
    'staff' || i || '@example.com',
    1 + (i % 5),
    TRUE,
    'staff_user_' || i,
    'password'
FROM generate_series(1, 10) AS i;

INSERT INTO customer (store_id, first_name, last_name, email, address_id)
SELECT
    1 + (i % 5),
    'Customer' || i,
    'Lastname' || i,
    'customer' || i || '@example.com',
    1 + (i % 200)
FROM generate_series(1, 500) AS i;

INSERT INTO inventory (film_id, store_id)
SELECT
    1 + (i % 500),
    1 + (i % 5)
FROM generate_series(1, 5000) AS i;

INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
SELECT
    NOW() - (i || ' hours')::interval,
    1 + (i % 5000),
    1 + (i % 500),
    1 + (i % 10)
FROM generate_series(1, 5000) AS i;

INSERT INTO payment (customer_id, staff_id, rental_id, amount)
SELECT
    1 + (i % 500),
    1 + (i % 10),
    i,
    0.99 + (i % 10)
FROM generate_series(1, 5000) AS i;