Changes between Initial Version and Version 1 of RelationalDesign

Timestamp:

01/11/25 23:11:56 (10 months ago)

Author:

211561

Comment:

--

RelationalDesign

@@ +1 @@
+= Логички и физички дизајн
+
+== DDL скрипта
+
+{{{#!sql
+-- 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 скрипта
+
+{{{#!sql
+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 релациски дијаграм
+
+[[Image(DBeaver_Relational_Diagram.png)]]