RelationalDesign2: kreiranje.sql

set search_path = "project";



-- Delete tables if they exist
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;


-- отстранување на шемата

drop schema if exists project;
 

-- креирање на шемата

create schema project;

create table project.Package(
code integer primary key,
title varchar(255) not null,
total decimal,
valuet varchar(255)
);

create table project.Policy(
p_id int 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,
constraint fk_payment_pol foreign key (policy) references project.Policy(p_id)
);

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

create table project.Customer(
c_id integer 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,
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 primary key,
pol_id integer,
constraint fk_pol_vehicle foreign key (pol_id) references project.Policy(p_id)
);

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

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