RelationDesign: schema_v5.sql

drop schema if exists triger cascade;
create schema triger;

drop table if exists dealership cascade;
drop table if exists vehicle cascade;
drop table if exists client cascade;
drop table if exists agreement cascade;
drop table if exists payment cascade;


CREATE TABLE dealership (
    Tax_Nr varchar(50) primary key,
    D_Name varchar(50) not null,
    Email varchar(50) not null,
    Pass varchar(50) not null,
    CEO varchar(50),
    Telephones varchar(200)[],
    Addresses varchar(200)[] not null
);

CREATE TYPE body_type AS ENUM ('Motorcycle', 'Car', 'Truck', 'Van');
CREATE TYPE color_type AS ENUM ('black', 'white', 'red', 'blue', 'green', 'yellow', 'orange', 'purple', 'silver', 'grey', 'cyan', 'magenta');

CREATE TABLE vehicle (
    VIN varchar(50) primary key,
    Body body_type not null,
    Model varchar(50) not null,
    P_Year integer not null,
    Engine varchar(100) not null,
    Color color_type,
    Price integer not null,
    Status boolean not null,
    Tax_Nr varchar(50) not null,
    constraint fk_Dealership foreign key (Tax_Nr) references dealership(Tax_Nr)
);

CREATE TABLE client (
    EMBG varchar(50) primary key,
    C_Name varchar(50) not null,
    Email varchar(50) not null,
    Pass varchar(50) not null,
    Telephone varchar(50)
);

CREATE TABLE agreement (
    A_Id serial primary key,
    Price Integer not null,
    Dependent varchar(200)[] not null,
    Status boolean not null,
    Datum date not null,
    Tax_Nr varchar(50) not null,
    VIN varchar(50) not null,
    EMBG varchar(50) not null,
    constraint fk_Dealership foreign key (Tax_Nr) references dealership(Tax_Nr),
    constraint fk_Vehicle foreign key (VIN) references vehicle(VIN),
    constraint fk_Client foreign key (EMBG) references client(EMBG)
   );

  CREATE TABLE payment(
        P_Id serial primary key,
    Bank varchar(50) not null,
    Account varchar(100) not null,
    Amount integer not null,
    EMBG varchar(50) not null,
    A_Id integer not null,
    constraint fk_Agreement foreign key (A_Id) references agreement(A_Id),
    constraint fk_Client foreign key (EMBG) references client(EMBG)
);