FnHwpWXa

1
2drop table if exists event;
3drop table if exists city;
4drop table if exists disaster;
5drop table if exists prediction;
6drop table if exists measures;
7
8create table if not exists event(
9	etype varchar(15),
10	description varchar(100),
11primary key (etype));
12
13create table if not exists city(
14	cname varchar(15),
15	country varchar(15),
16	population int,
17primary key (cname));
18
19create table if not exists disaster(
20	cname varchar(15),
21	dyear numeric(4,0),
22	etype varchar(15),
23	casualties int,
24primary key (cname, dyear),
25foreign key (cname) references city,
26foreign key (etype) references event);
27
28create table if not exists prediction(
29	cname varchar(15),
30	etype varchar(15),
31	casualties int,
32primary key (cname, etype),
33foreign key (cname) references city,
34foreign key (etype) references event);
35
36create table if not exists measures(
37	etype varchar(15),
38	provider varchar(15),
39	mcost int,
40	percent int,
41primary key (etype, provider),
42foreign key (etype) references event);
43
44insert into event values 
45	('volcano', 'lava, ash, rock and gasses eruption'),
46	('earthquake', 'from 5 in Richter scale'),
47	('flood', 'overflowing of a large amount of water over what is normally dry land'),
48	('fire', 'burning that produces flames that send out heat and light and sometimes smoke'),
49	('tornado', 'funnel-shaped vortex of violently rotating winds advancing beneath a large storm system')
50
51insert into city values 
52	('Kagoshima', 'Japan', 700000),
53	('Naples', 'Italy', 3000000),
54	('Pasto', 'Columbia', 450000),
55	('Hilo', 'Hawaii', 43000),
56	('Tsfat', 'Israel', 3600);
57
58insert into disaster values 
59	('Kagoshima', 1915, 'volcano', 100),
60	('Kagoshima', 1974, 'volcano', 50),
61	('Kagoshima', 2017, 'volcano', 20),
62	('Kagoshima', 1993, 'flood', 2600),
63	('Kagoshima', 1914, 'earthquake', 35),
64	('Naples', 1906, 'volcano', 50),
65	('Naples', 1944, 'volcano', 35),
66	('Naples', 1979, 'volcano', 50),
67	('Naples', 1998, 'flood', 200),
68	('Pasto', 1988, 'volcano', 30),
69	('Pasto', 1993, 'volcano', 45),
70	('Pasto', 2002, 'volcano', 15),
71	('Pasto', 2008, 'volcano', 30),
72	('Pasto', 2010, 'volcano', 30),
73	('Pasto', 2018, 'flood',15),
74	('Hilo', 1903, 'volcano', 500),
75	('Hilo', 1914, 'volcano', 300),
76	('Hilo', 1926, 'volcano', 20),
77	('Hilo', 1984, 'volcano', 50),
78	('Hilo', 2015, 'tornado', 50),
79	('Hilo', 2011, 'tornado', 70),
80	('Hilo', 2002, 'tornado', 5),
81	('Hilo', 1989, 'tornado', 50),
82	('Hilo', 1971, 'tornado', 85),
83	('Tsfat', 1837, 'earthquake', 5000);
84
85insert into prediction values 
86	('Naples', 'volcano', 50),
87	('Pasto', 'volcano', 500),
88	('Pasto', 'tornado', 200);
89	
90insert into measures values 
91	('volcano', 'fire department', 500000, 85),
92	('volcano', 'police', 200000, 70),
93	('flood', 'fire department', 1000000, 60);