WikiStart: project_kreiranje.sql

set search_path = "project";



-- Delete tables if they exist
drop table if exists project.Customer;
drop table if exists project.Policy;
drop table if exists project.Pol_dog;
drop table if exists project.Pol_osi;
drop table if exists project.Vehicle;
drop table if exists project.Package;
drop table if exists project.Pol_auto;
drop table if exists project.Pol_travel;


drop domain if exists project.string_dolg;
drop domain if exists project.string_kratok;

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

drop schema if exists project;
 

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

create schema project;

-- се поставува патека односно шема во која ќе се извршуваат командите

set search_path = project;

-- се креираат потребните домени - нови типови податоци

create domain project.string_dolg AS character varying(4000);
create domain project.string_kratok AS character varying(500);



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.Package(
code integer primary key,
title varchar(255) not null,
covers 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.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.Pol_auto(
a_id integer primary key,
pol_id integer,
constraint fk_pol_vehicle foreign key (pol_id) references project.Policy(p_id)
);

create table project.Pol_travel(
tr_id integer primary key,
pol_id integer,
constraint fk_pol_vehicle 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_Pol foreign key (policy) references project.Pol_travel(tr_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 foreign key (policy) references project.Pol_auto(a_id)
);