RelationalDesign2: kreiranje_4.2.sql

drop table if exists project.Policy;
drop table if exists project.Customer;
drop table if exists project.Pol_dog;
drop table if exists project.Payment;
drop table if exists project.Package;
drop table if exists project.Covers;
drop table if exists project.Property_pol;
drop table if exists project.Property;
drop table if exists project.Travel_pol;
drop table if exists project.Pol_osi;
drop table if exists project.Auto_pol;
drop table if exists project.Vehicle;

create schema project;

create table project.Package(
  code serial4 primary key,
  title varchar(255) not null,
  total numeric NULL,
  valuet varchar(255) NULL,
  type_pol int4 NULL
);

create table project.Policy(
  p_id serial4 primary key NOT NULL,
  sdate date not null,
  edate date not null,
  package int4,
  constraint fk_package_pol foreign key (package) references project.Package(code)
);

create table project.Payment(
  payment_num serial4 primary key,
  "policy" int4,
  p_date date NULL,
  p_amount int4 NULL,
  visa_number varchar(255)
  constraint fk_payment_pol foreign key (policy) references project.Policy(p_id)
);

create table project.Covers(
  cov_id int serial4 primary key,
  cov_amount int4,
  package int4,
  cov_type varchar(max),
  constraint fk_covers_pol foreign key (package) references project.Package(code)
);


create table project.Customer(
  c_id serial4 primary key, 
  name varchar(50),
  email varchar(50) not null,
  password varchar(50) not null,
  type bool not null
);

create table project.Pol_dog(
  d_embg varchar(13) primary key,
  c_id int4 NULL,
  name varchar(255) not null,
  policy int4,
  surname varchar(255),
  birthdate date not null,
  kontakt varchar(255),
  constraint fk_Profile_User foreign key (c_id) references project.Customer(c_id),
  constraint fk_Pol foreign key (policy) references project.Policy(p_id)
);

CREATE TABLE project.auto_pol (
        a_id serial4 NOT NULL,
        pol_id int4 NULL,
        CONSTRAINT auto_pol_pkey PRIMARY KEY (a_id)
);
ALTER TABLE project.auto_pol ADD CONSTRAINT fk_pol_vehicle FOREIGN KEY (pol_id) REFERENCES project."policy"(p_id);

create table project.Vehicle(
  v_id serial4 primary key,
  policy int4,
  type varchar(255) not null,
  marka varchar(255),
  model varchar(255),
  license_plate varchar(25) not null,
  constraint fk_Pol_veh foreign key (policy) references project.Auto_pol(a_id)
);

create table project.Travel_pol(
  tr_id serial4 primary key,
  pol_id int4 ,
  constraint fk_pol_travel foreign key (pol_id) references project.Policy(p_id)
);

create table project.Pol_osi(
  o_embg varchar(13) primary key,
  policy int4,
  name varchar(255) not null,
  surname varchar(255),
  birthdate date not null,
  kontakt varchar(255),
  constraint fk_Polosi foreign key (policy) references project.Travel_pol(tr_id)
);

create table project.Property_pol(
  pr_id serial4 primary key,
  pol_id int4,
  constraint fk_PolProperty foreign key (pol_id) references project.Policy(p_id)
);

create table project.Property(
  prop_id serial4 primary key,
  policy int4,
  address varchar(255),
  floor int4,
  year_build varchar(255),
  security bool,
  constraint fk_pol_propp foreign key (policy) references project.Property_pol(pr_id)
);