RelationalModel: V2_Init_DB.sql

drop table if exists public.Wine_Type cascade;
drop table if exists public.Wine cascade;
drop table if exists public.Address cascade;
drop table if exists public.Customer_Type cascade;
drop table if exists public.Customer cascade;
drop table if exists public.Vehicle_Type cascade;
drop table if exists public.Vehicle_Details cascade;
drop table if exists public.Vehicle cascade;
drop table if exists public.Payment cascade;
drop table if exists public.Expense_Type cascade;
drop table if exists public.Warehouse cascade;
drop table if exists public.Employee cascade;
drop table if exists public.Employee_Drives_Vehicle cascade;
drop table if exists public.Shipment cascade;
drop table if exists public.Shipment_Has_Expense_Of_Expense_Type cascade;
drop table if exists public.Shipment_Load cascade;


create table public.Wine_Type(
            Wine_Type_Id serial primary key,
            Wine_Type_Name varchar(50) unique not null,
            Wine_Type_Description varchar(200) not null,
            Wine_Type_Region varchar(50) not null
);

create table public.Wine(
                Wine_Id serial primary key,             
                Wine_Name varchar(50) unique not null,
                Base_Price double precision not null,
                Year_Produced date not null,
                Wine_Type_Id integer not null,
                foreign key (Wine_Type_Id) references public.Wine_Type (Wine_Type_Id)   
);

create table public.Customer_Type(
            Customer_Type_Id serial primary key,
            Customer_Type_Name varchar(50) unique not null,
            Customer_Type_Description varchar(200) not null
);

create table public.Address(
            Address_Id serial primary key,
            Street varchar(70) unique not null,
            City varchar(50) not null,
                Building_Number integer not null,
            Postcode varchar(20) not null
);

create table public.Customer(
            Customer_Id serial primary key,
            Customer_Name varchar(50) unique not null,
            Customer_Email varchar(70) not null,
            Customer_Phone_Number varchar(100) not null,
            Customer_Type_Id integer not null,
            Address_Id integer not null,
                foreign key (Customer_Type_Id) references public.Customer_Type (Customer_Type_Id),
                foreign key (Address_Id) references public.Address (Address_Id)
);

create table public.Vehicle_Type(
            Vehicle_Type_Id serial primary key,
            Vehicle_Type_Name varchar(50) unique not null
);

create table public.Vehicle_Details(
            Vehicle_Details_Id serial primary key,
            Vehicle_Type_Id integer not null,
            Make varchar(50) not null,
            Model varchar(50) not null,
            Capacity integer not null,
                foreign key (Vehicle_Type_Id) references public.Vehicle_Type (Vehicle_Type_Id)
);

create table public.Vehicle(
            Vehicle_Id serial primary key,
            Vehicle_Details_Id integer not null,
            Registration varchar(50) not null,
                foreign key (Vehicle_Details_Id) references public.Vehicle_Details (Vehicle_Details_Id)
);

create table public.Payment(
            Payment_Id serial primary key,
            Payment_Status integer not null
);

create table public.Expense_Type(
            Expense_Type_Id serial primary key,
            Expense_Type_Name varchar(50) unique not null,
            Expense_Type_Description varchar(200) not null
);

create table public.Warehouse(
            Warehouse_Id serial primary key,
            Warehouse_Name varchar(50) unique not null,
            Address_Id integer not null,    
            foreign key (Address_Id) references public.Address (Address_Id)
);

create table public.Employee(
            Employee_Id serial primary key,
            Employee_Name varchar(50) not null,
            Employee_Surname varchar(50) not null,
            Warehouse_Id integer not null,    
            foreign key (Warehouse_Id) references public.Warehouse (Warehouse_Id)
);

create table public.Employee_Drives_Vehicle(
            Employee_Id integer not null,
            Vehicle_Id integer not null,
            primary key (Employee_Id, Vehicle_Id), -- Composite primary key
            foreign key (Vehicle_Id) references public.Vehicle (Vehicle_Id),
            foreign key (Employee_Id) references public.Employee (Employee_Id)
);

create table public.Shipment(
                Shipment_Id serial primary key,
                Employee_Id integer not null,
            Payment_Id integer not null,
            Vehicle_Id integer not null,
            Shipment_Date date not null,
            foreign key (Employee_Id) references public.Employee (Employee_Id),
            foreign key (Payment_Id) references public.Payment (Payment_Id),
            foreign key (Vehicle_Id) references public.Vehicle (Vehicle_Id)
);

create table public.Shipment_Has_Expense_Of_Expense_Type(
            Expense_Type_Id integer not null,
            Shipment_Id integer not null,
                Amount integer not null,
            primary key (Expense_Type_Id, Shipment_Id), -- Composite primary key
            foreign key (Expense_Type_Id) references public.Expense_Type (Expense_Type_Id),
            foreign key (Shipment_Id) references public.Shipment (Shipment_Id)
);

create table public.Shipment_Load(
            Shipment_Id  integer not null,
            Customer_Id  integer not null,
            Wine_Id  integer not null,
            Wine_Base_Price double precision not null,
            Wine_Agreed_Price double precision not null,    
            Wine_Quantity integer not null,
            primary key (Shipment_Id, Customer_Id, Wine_Id), -- Composite primary key
            foreign key (Shipment_Id) references public.Shipment (Shipment_Id),
            foreign key (Customer_Id) references public.Customer (Customer_Id),
            foreign key (Wine_Id) references public.Wine (Wine_Id)
);