provsql/doc/tutorial/solution.sql at master · PierreSenellart/provsql · GitHub

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
144
145
146
147
148
-- Q2
SET
  search_path TO public,
  provsql;

-- Q3
CREATE TABLE s AS
SELECT
  time,
  person.name AS person,
  p2.name AS witness,
  room.name AS room
FROM
  sightings
  JOIN person ON person = id
  JOIN person AS p2 ON witness = p2.id
  JOIN room ON room = room.id;

-- Q4
SELECT
  add_provenance ('s');

SELECT
  create_provenance_mapping ('witness_mapping', 's', 'witness');

SELECT
  *
FROM
  witness_mapping;

-- Q5
SELECT
  s1.time,
  s1.person,
  s1.room
FROM
  s AS s1,
  s AS s2
WHERE
  s1.person = s2.person
  AND s1.time = s2.time
  AND s1.room <> s2.room;

-- Q6
SELECT
  s1.time,
  s1.person,
  s1.room,
  sr_formula (provenance (), 'witness_mapping')
FROM
  s AS s1,
  s AS s2
WHERE
  s1.person = s2.person
  AND s1.time = s2.time
  AND s1.room <> s2.room;

-- Q7
CREATE TABLE consistent_s AS
SELECT
  time,
  person,
  room
FROM
  s
EXCEPT
SELECT
  s1.time,
  s1.person,
  s1.room
FROM
  s AS s1,
  s AS s2
WHERE
  s1.person = s2.person
  AND s1.time = s2.time
  AND s1.room <> s2.room;

SELECT
  *,
  sr_formula (provenance (), 'witness_mapping')
FROM
  consistent_s;

-- Q8
CREATE TABLE suspects AS
SELECT DISTINCT
  person
FROM
  consistent_s
WHERE
  room LIKE '% bedroom'
  AND time BETWEEN '00:00:00' AND '08:00:00';

SELECT
  *,
  sr_formula (provenance (), 'witness_mapping')
FROM
  suspects;

-- Q9
ALTER table s
ADD COLUMN count int;

UPDATE s
SET
  count = 1;

SELECT
  create_provenance_mapping ('count_mapping', 's', 'count');

SELECT
  *,
  sr_counting (provenance (), 'count_mapping') AS c
FROM
  suspects
ORDER BY
  c;

-- Q10
ALTER table s
ADD COLUMN reliability float;

UPDATE s
SET
  reliability = score
FROM
  reliability,
  person
WHERE
  reliability.person = person.id
  AND person.name = s.witness;

SELECT
  set_prob (provenance (), reliability)
FROM
  s;

-- Q11
SELECT
  *,
  sr_formula (provenance (), 'witness_mapping'),
  probability_evaluate (provenance (), 'possible-worlds')
FROM
  suspects
WHERE
  probability_evaluate (provenance (), 'possible-worlds') > 0.99
  AND person <> 'Daphine';