RelationalDesign2: kreiranje_3.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 integer primary key,
  title varchar(255) not null,
  total decimal,
  valuet varchar(255),
  type_pol INT
);

create table project.Policy(
  p_id int identity(1,1) primary key,
  sdate date not null,
  edate date not null,
  package integer,
  constraint fk_package_pol foreign key (package) references project.Package(code)
);

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

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

create table project.Customer(
  c_id integer identity(1,1) primary key, 
  name varchar(50),
  email varchar(50) not null,
  password varchar(50) not null,
  type bit not null
);

create table project.Pol_dog(
  d_embg varchar(13) primary key,
  c_id integer identity(1,1),
  name varchar(255) not null,
  policy integer,
  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 integer identity(1,1) primary key,
  pol_id identity(1,1) integer,
  constraint fk_pol_vehicle foreign key (pol_id) references project.Policy(p_id)
);

create table project.Vehicle(
  v_id int identity(1,1) primary key,
  policy integer,
  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 integer identity(1,1) primary key,
  pol_id integer identity(1,1),
  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 integer,
  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 integer identity(1,1) primary key,
  pol_id integer identity(1,1),
  constraint fk_PolProperty foreign key (pol_id) references project.Policy(p_id)
);

create table project.Property(
  prop_id integer identity(1,1) primary key,
  policy integer,
  address varchar(255),
  floor integer,
  year_build varchar(255),
  security bit,
  constraint fk_pol_propp foreign key (policy) references project.Property_pol(pr_id)
);