Логички и физички дизајн

DDL скрипта

-- DDL for Deleting Tables
DROP TABLE IF EXISTS ORDER_PRODUCT;
DROP TABLE IF EXISTS INVOICE;
DROP TABLE IF EXISTS PAYMENT;
DROP TABLE IF EXISTS ORDER;
DROP TABLE IF EXISTS TRANSPORT;
DROP TABLE IF EXISTS RECEIVER;
DROP TABLE IF EXISTS BUYER;
DROP TABLE IF EXISTS PRODUCT;
DROP TABLE IF EXISTS PRODUCER;

-- DDL for Creating Tables

-- Creating the PRODUCER table
CREATE TABLE PRODUCER (
    producer_id SERIAL PRIMARY KEY,
    company_name VARCHAR(100),
    country_of_origin VARCHAR(30),
    tax_id VARCHAR(20),
    registration_number VARCHAR(30),
    bank_account_number VARCHAR(20),
    bank_name VARCHAR(100),
    swift_number VARCHAR(20),
    iban VARCHAR(34),
    export_license_number VARCHAR(20)
);

-- Creating the PRODUCT table
CREATE TABLE PRODUCT (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    hs_code VARCHAR(10),
    description TEXT,
    unit_of_measure VARCHAR(10),
    price DECIMAL(10, 2),
    producer_id INT REFERENCES PRODUCER(producer_id)
);

-- Creating the BUYER table
CREATE TABLE BUYER (
    buyer_id SERIAL PRIMARY KEY,
    company_name VARCHAR(100),
    billing_address VARCHAR(200),
    country VARCHAR(50),
    tax_id VARCHAR(20),
    registration_number VARCHAR(30)
);

-- Creating the RECEIVER table
CREATE TABLE RECEIVER (
    receiver_id SERIAL PRIMARY KEY,
    company_name VARCHAR(100),
    shipping_address VARCHAR(200),
    country VARCHAR(50),
    phone_number VARCHAR(15),
    contact_person VARCHAR(100)
);

-- Creating the TRANSPORT table
CREATE TABLE TRANSPORT (
    transport_id SERIAL PRIMARY KEY,
    carrier_name VARCHAR(100),
    departure_point VARCHAR(50),
    arrival_point VARCHAR(50),
    estimated_departure DATE,
    estimated_arrival DATE,
    total_distance DECIMAL(10, 2),
    special_requirements TEXT,
    insurance_conditions VARCHAR(200),
    incoterm VARCHAR(20)
);

-- Creating the ORDER table
CREATE TABLE ORDER (
    order_id SERIAL PRIMARY KEY,
    order_date DATE,
    estimated_delivery_date DATE,
    status VARCHAR(20),
    employee_id INT,
    buyer_id INT REFERENCES BUYER(buyer_id),
    receiver_id INT REFERENCES RECEIVER(receiver_id),
    transport_id INT REFERENCES TRANSPORT(transport_id)
);

-- Creating the PAYMENT table
CREATE TABLE PAYMENT (
    payment_id SERIAL PRIMARY KEY,
    amount DECIMAL(10, 2),
    exchange_rate DECIMAL(10, 6),
    currency VARCHAR(3),
    payment_status VARCHAR(20),
    payment_method VARCHAR(20),
    due_date DATE,
    payment_date DATE,
    order_id INT REFERENCES ORDER(order_id)
);

-- Creating the INVOICE table
CREATE TABLE INVOICE (
    invoice_key SERIAL PRIMARY KEY,
    invoice_date DATE,
    status VARCHAR(20),
    total_amount DECIMAL(10, 2),
    order_id INT REFERENCES ORDER(order_id)
);

-- Creating the ORDER_PRODUCT table (Many-to-Many relationship between ORDER and PRODUCT)
CREATE TABLE ORDER_PRODUCT (
    order_id INT REFERENCES ORDER(order_id),
    product_id INT REFERENCES PRODUCT(product_id),
    PRIMARY KEY (order_id, product_id)
);

DML скрипта

INSERT INTO PRODUCER (company_name, country_of_origin, tax_id, registration_number, bank_account_number, bank_name, swift_number, iban, export_license_number) VALUES
('Producer A', 'Country X', 'TX123', 'RN123', 'BAN123', 'Bank A', 'SW123', 'IBAN123', 'EL123');

INSERT INTO PRODUCT (name, hs_code, description, unit_of_measure, price, producer_id) VALUES
('Product 1', 'HS101', 'Description 1', 'Unit 1', 100.00, 1),
('Product 2', 'HS102', 'Description 2', 'Unit 2', 150.00, 1);

INSERT INTO BUYER (company_name, billing_address, country, tax_id, registration_number) VALUES
('Buyer A', 'Address 1', 'Country Y', 'TX456', 'RN456');

INSERT INTO RECEIVER (company_name, shipping_address, country, phone_number, contact_person) VALUES
('Receiver A', 'Shipping Address 1', 'Country Z', '1234567890', 'Contact A');

INSERT INTO TRANSPORT (carrier_name, departure_point, arrival_point, estimated_departure, estimated_arrival, total_distance, special_requirements, insurance_conditions, incoterm) VALUES
('Transport A', 'Point A', 'Point B', '2022-01-01', '2022-01-02', 1000.00, 'None', 'Condition A', 'FOB');

INSERT INTO ORDER (order_date, estimated_delivery_date, status, employee_id, buyer_id, receiver_id, transport_id) VALUES
('2022-01-01', '2022-02-01', 'Pending', 1, 1, 1, 1);

INSERT INTO PAYMENT (amount, exchange_rate, currency, payment_status, payment_method, due_date, payment_date, order_id) VALUES
(1000.00, 1.000000, 'USD', 'Completed', 'Credit Card', '2022-01-15', '2022-01-10', 1);

INSERT INTO INVOICE (invoice_date, status, total_amount, order_id) VALUES
('2022-01-10', 'Issued', 1000.00, 1);

INSERT INTO ORDER_PRODUCT (order_id, product_id) VALUES
(1, 1),
(1, 2);

DBeaver релациски дијаграм