Lw1rVXPA

1create schema sailors
2;
3
4SET search_path TO sailors
5;
6
7alter schema sailors owner to postgres
8;
9
10create table if not exists sailors
11(
12  sid integer not null
13  constraint sailors_pkey
14  primary key,
15  sname varchar(30),
16  rating integer,
17  age real
18)
19;
20
21Create INDEX idx_sailor_Btree_Age on sailors(age);
22Create INDEX idx_sailor_Btree_Sname on sailors(sname);
23Create Index idx_sailor_Btree_rating_age on sailors(rating,age);
24CLUSTER sailors USING idx_sailor_Btree_rating_age;
25
26alter table sailors owner to postgres
27;
28
29create table if not exists boats
30(
31  bid integer not null
32  constraint boats_pkey
33  primary key,
34  bname varchar(30),
35  color varchar(20)
36)
37;
38
39--Create INDEX idx_boats_hash_color on boats USING hash(color);
40Create INDEX idx_boats_Btree_color on boats(color);
41CLUSTER boats USING idx_boats_Btree_color;
42
43alter table boats owner to postgres
44;
45
46create table if not exists reserves
47(
48  day date not null,
49  sid integer not null
50  constraint reserves_sailors_sid_fk
51  references sailors,
52  bid integer not null
53  constraint reserves_boats_bid_fk
54  references boats,
55  constraint reserves_pk
56  primary key (day, sid, bid)
57)
58;
59
60Create INDEX idx_boats_hash_sid on reserves USING hash(sid);
61Create INDEX idx_boats_hash_bid on reserves USING hash(bid);
62CLUSTER reserves USING reserves_pk;
63
64alter table reserves owner to postgres
65;