· 7 years ago · Dec 06, 2018, 01:58 PM
1DROP TABLE IF EXISTS Lab9_Stations,Lab9_Pollutant,Lab9_Monitors;
2
3CREATE TABLE Lab9_Stations(
4EIONetCode VARCHAR NOT NULL,
5 Station TEXT NOT NULL,
6 Status TEXT NOT NULL,
7 CONSTRAINT Lab9_Stations_PKEY PRIMARY KEY (EIONetCode)
8 );
9 insert into Lab9_Stations (EIONetCode, Station, Status) values
10 ('IE0145A','Mayo Claremorris','Rural-Remote');
11 insert into Lab9_Stations (EIONetCode, Station, Status) values
12 ('IE0028A','Dublin Rathmines','Urban');
13 insert into Lab9_Stations (EIONetCode, Station, Status) values
14 ('IE0125A','Galway City','Suburban');
15 insert into Lab9_Stations (EIONetCode, Station, Status) values
16 ('IE0111A','Laois Emo Court','Rural-regional');
17 insert into Lab9_Stations (EIONetCode, Station, Status) values
18 ('IE0090A','Monaghan Kilkitt','Rural-regional');
19 insert into Lab9_Stations (EIONetCode, Station, Status) values
20 ('IE0147A','Kilkenny Seville Lodge','Suburban');
21
22
23
24 CREATE TABLE Lab9_Pollutant(
25 EEAPol INTEGER NOT NULL,
26 Pollutant TEXT NOT NULL,
27 Notation TEXT NOT NULL,
28 CONSTRAINT Lab9_Pollutant_PKEY PRIMARY KEY (EEAPol)
29 );
30
31 insert into Lab9_Pollutant (EEAPol,Pollutant, Notation) values
32 ('1','Sulphur dioxide (air)','SO2');
33 insert into Lab9_Pollutant (EEAPol,Pollutant, Notation) values
34 ('10','Carbon monoxide (air)','CO');
35 insert into Lab9_Pollutant (EEAPol,Pollutant, Notation) values
36 ('7','Ozone','03 (air)');
37 insert into Lab9_Pollutant (EEAPol,Pollutant, Notation) values
38 ('8','Nitrogen dioxide (air)','NO2');
39 insert into Lab9_Pollutant (EEAPol,Pollutant, Notation) values
40 ('5','Particulate matter<10 (aersol)','PM10');
41 insert into Lab9_Pollutant (EEAPol,Pollutant, Notation) values
42 ('6001','Particulate matter<2.5 (aersol)','PM2.5');
43
44
45 CREATE TABLE Lab9_Monitors (
46 EIONetCode VARCHAR NOT NULL References Lab9_Stations (EIONetCode) ON UPDATE CASCADE ON DELETE CASCADE,
47 EEAPol VARCHAR NOT NULL References Lab9_Pollutant(EEAPol) ON UPDATE CASCADE ON DELETE CASCADE,
48 CONSTRAINT Lab9_Monitors_PK PRIMARY KEY (EIONetCode,EEAPol)
49 );
50
51 --RATHMINES
52 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0028A',1);
53 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0028A',10);
54 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0028A',7);
55 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0028A',8);
56 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0028A',5);
57 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0028A',6001);
58
59
60 --Mayo Claremorris
61 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0145A',7);
62 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0145A',8);
63 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0145A',5);
64 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0145A',6001);
65
66
67
68 --Laois Emo Court
69 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0111A',7);
70 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0111A',8);
71 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0111A',5);
72 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0111A',6001);
73
74 --Monaghan Kilkitt
75 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0090A',7);
76 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0090A',8);
77 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0090A',5);
78 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0090A',6001);
79
80 --Galway City
81 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0125A',1);
82 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0125A',8);
83 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0125A',5);
84 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0125A',6001);
85
86
87 --Kilkenny Seville Lodge
88 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0147A',1);
89 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0147A',8);
90 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0147A',5);
91 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0147A',6001);