1 |
|
---|
2 |
|
---|
3 | set search_path = "project";
|
---|
4 |
|
---|
5 |
|
---|
6 |
|
---|
7 | -- Delete tables if they exist
|
---|
8 | drop table if exists project.Customer;
|
---|
9 | drop table if exists project.Policy;
|
---|
10 | drop table if exists project.Pol_dog;
|
---|
11 | drop table if exists project.Pol_osi;
|
---|
12 | drop table if exists project.Vehicle;
|
---|
13 | drop table if exists project.Package;
|
---|
14 | drop table if exists project.Pol_auto;
|
---|
15 | drop table if exists project.Pol_travel;
|
---|
16 |
|
---|
17 |
|
---|
18 | drop domain if exists project.string_dolg;
|
---|
19 | drop domain if exists project.string_kratok;
|
---|
20 |
|
---|
21 | -- отстранување на шемата
|
---|
22 |
|
---|
23 | drop schema if exists project;
|
---|
24 |
|
---|
25 |
|
---|
26 | -- креирање на шемата
|
---|
27 |
|
---|
28 | create schema project;
|
---|
29 |
|
---|
30 | -- се поставува патека односно шема во која ќе се извршуваат командите
|
---|
31 |
|
---|
32 | set search_path = project;
|
---|
33 |
|
---|
34 | -- се креираат потребните домени - нови типови податоци
|
---|
35 |
|
---|
36 | create domain project.string_dolg AS character varying(4000);
|
---|
37 | create domain project.string_kratok AS character varying(500);
|
---|
38 |
|
---|
39 |
|
---|
40 |
|
---|
41 | create table project.Customer(
|
---|
42 | c_id integer primary key,
|
---|
43 | name varchar(50),
|
---|
44 | email varchar(50) not null,
|
---|
45 | password varchar(50) not null,
|
---|
46 | type bit not null
|
---|
47 | );
|
---|
48 |
|
---|
49 | create table project.Package(
|
---|
50 | code integer primary key,
|
---|
51 | title varchar(255) not null,
|
---|
52 | covers varchar(255) not null,
|
---|
53 | total decimal,
|
---|
54 | valuet varchar(255)
|
---|
55 | );
|
---|
56 |
|
---|
57 | create table project.Policy(
|
---|
58 | p_id int primary key,
|
---|
59 | sdate date not null,
|
---|
60 | edate date not null,
|
---|
61 | package integer,
|
---|
62 | constraint fk_package_pol foreign key (package) references project.Package(code)
|
---|
63 | );
|
---|
64 |
|
---|
65 | create table project.Pol_dog(
|
---|
66 | d_embg varchar(13) primary key,
|
---|
67 | c_id integer,
|
---|
68 | name varchar(255) not null,
|
---|
69 | policy integer,
|
---|
70 | surname varchar(255),
|
---|
71 | birthdate date not null,
|
---|
72 | kontakt varchar(255),
|
---|
73 | constraint fk_Profile_User foreign key (c_id) references project.Customer(c_id),
|
---|
74 | constraint fk_Pol foreign key (policy) references project.Policy(p_id)
|
---|
75 | );
|
---|
76 |
|
---|
77 | create table project.Pol_auto(
|
---|
78 | a_id integer primary key,
|
---|
79 | pol_id integer,
|
---|
80 | constraint fk_pol_vehicle foreign key (pol_id) references project.Policy(p_id)
|
---|
81 | );
|
---|
82 |
|
---|
83 | create table project.Pol_travel(
|
---|
84 | tr_id integer primary key,
|
---|
85 | pol_id integer,
|
---|
86 | constraint fk_pol_vehicle foreign key (pol_id) references project.Policy(p_id)
|
---|
87 | );
|
---|
88 |
|
---|
89 |
|
---|
90 | create table project.Pol_osi(
|
---|
91 | o_embg varchar(13) primary key,
|
---|
92 | policy integer,
|
---|
93 | name varchar(255) not null,
|
---|
94 | surname varchar(255),
|
---|
95 | birthdate date not null,
|
---|
96 | kontakt varchar(255),
|
---|
97 | constraint fk_Pol foreign key (policy) references project.Pol_travel(tr_id)
|
---|
98 | );
|
---|
99 |
|
---|
100 |
|
---|
101 | create table project.Vehicle(
|
---|
102 | v_id int primary key,
|
---|
103 | policy integer,
|
---|
104 | type varchar(255) not null,
|
---|
105 | marka varchar(255),
|
---|
106 | model varchar(255),
|
---|
107 | license_plate varchar(25) not null,
|
---|
108 | constraint fk_Pol foreign key (policy) references project.Pol_auto(a_id)
|
---|
109 | );
|
---|
110 |
|
---|
111 |
|
---|
112 |
|
---|
113 |
|
---|
114 |
|
---|