-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathschema-part3.sql
143 lines (130 loc) · 4.13 KB
/
schema-part3.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
DROP TABLE IF EXISTS trip CASCADE;
DROP TABLE IF EXISTS reservation CASCADE;
DROP TABLE IF EXISTS schedule CASCADE;
DROP TABLE IF EXISTS boat_vhf CASCADE;
DROP TABLE IF EXISTS boat CASCADE;
DROP TABLE IF EXISTS owner CASCADE;
DROP TABLE IF EXISTS sailor CASCADE;
DROP TABLE IF EXISTS person CASCADE;
DROP TABLE IF EXISTS port CASCADE;
DROP TABLE IF EXISTS wharf CASCADE;
DROP TABLE IF EXISTS marina CASCADE;
DROP TABLE IF EXISTS location CASCADE;
DROP TABLE IF EXISTS country CASCADE;
CREATE TABLE country
(
flag VARCHAR(150) UNIQUE NOT NULL,
name VARCHAR(70) UNIQUE NOT NULL,
iso_code CHAR(2),
PRIMARY KEY (iso_code)
);
CREATE TABLE location
(
name VARCHAR(80) NOT NULL,
latitude NUMERIC(8, 6),
longitude NUMERIC(9, 6),
iso_code CHAR(2),
PRIMARY KEY (latitude, longitude),
FOREIGN KEY (iso_code) REFERENCES country (iso_code)
);
CREATE TABLE marina
(
latitude NUMERIC(8, 6),
longitude NUMERIC(9, 6),
FOREIGN KEY (latitude, longitude) REFERENCES location (latitude, longitude)
);
CREATE TABLE wharf
(
latitude NUMERIC(8, 6),
longitude NUMERIC(9, 6),
FOREIGN KEY (latitude, longitude) REFERENCES location (latitude, longitude)
);
CREATE TABLE port
(
latitude NUMERIC(8, 6),
longitude NUMERIC(9, 6),
FOREIGN KEY (latitude, longitude) REFERENCES location (latitude, longitude)
);
CREATE TABLE person
(
id VARCHAR(20),
name VARCHAR(80) NOT NULL,
iso_code CHAR(2),
PRIMARY KEY (id, iso_code),
FOREIGN KEY (iso_code) REFERENCES country (iso_code)
);
CREATE TABLE sailor
(
id VARCHAR(20),
iso_code CHAR(2),
PRIMARY KEY (id, iso_code),
FOREIGN KEY (id, iso_code) REFERENCES person (id, iso_code)
);
CREATE TABLE owner
(
id VARCHAR(20),
iso_code CHAR(2),
birthdate DATE NOT NULL,
PRIMARY KEY (id, iso_code),
FOREIGN KEY (id, iso_code) REFERENCES person (id, iso_code)
);
CREATE TABLE boat
(
name VARCHAR(30) NOT NULL,
year SMALLINT NOT NULL,
cni VARCHAR(15),
iso_code CHAR(2),
id_owner VARCHAR(20),
iso_code_owner CHAR(2),
PRIMARY KEY (cni, iso_code),
FOREIGN KEY (iso_code) REFERENCES country (iso_code),
FOREIGN KEY (id_owner, iso_code_owner) REFERENCES owner (id, iso_code)
);
CREATE TABLE boat_vhf
(
mmsi NUMERIC(9) NOT NULL,
cni VARCHAR(15),
iso_code CHAR(2),
PRIMARY KEY (cni, iso_code),
FOREIGN KEY (cni, iso_code) REFERENCES boat (cni, iso_code)
);
CREATE TABLE schedule
(
start_date DATE,
end_date DATE,
CHECK (end_date > start_date),
PRIMARY KEY (start_date, end_date)
);
CREATE TABLE reservation
(
cni VARCHAR(15),
iso_code_boat CHAR(2),
id_sailor VARCHAR(20),
iso_code_sailor CHAR(2),
start_date DATE,
end_date DATE,
PRIMARY KEY (cni, iso_code_boat, id_sailor, iso_code_sailor, start_date, end_date),
FOREIGN KEY (cni, iso_code_boat) REFERENCES boat (cni, iso_code),
FOREIGN KEY (id_sailor, iso_code_sailor) REFERENCES sailor (id, iso_code),
FOREIGN KEY (start_date, end_date) REFERENCES schedule (start_date, end_date)
);
CREATE TABLE trip
(
date DATE,
duration SMALLINT NOT NULL,
cni VARCHAR(15),
iso_code_boat CHAR(2),
id_sailor VARCHAR(20),
iso_code_sailor CHAR(2),
start_date DATE,
end_date DATE,
start_latitude NUMERIC(8, 6),
start_longitude NUMERIC(9, 6),
end_latitude NUMERIC(8, 6),
end_longitude NUMERIC(9, 6),
PRIMARY KEY (date, cni, iso_code_boat, id_sailor, iso_code_sailor, start_date, end_date),
FOREIGN KEY (cni, iso_code_boat, id_sailor, iso_code_sailor, start_date, end_date) REFERENCES
reservation (cni, iso_code_boat, id_sailor, iso_code_sailor, start_date, end_date),
FOREIGN KEY (start_latitude, start_longitude) REFERENCES location (latitude, longitude),
FOREIGN KEY (end_latitude, end_longitude) REFERENCES location (latitude, longitude)
);