· 7 years ago · Jan 15, 2019, 03:20 PM
1-- DDL
2
3--DROPS
4DROP TABLE IF EXISTS library_rentals;
5DROP TABLE IF EXISTS patrons;
6DROP TABLE IF EXISTS copies;
7DROP TABLE IF EXISTS dig_papers;
8DROP TABLE IF EXISTS written_content;
9DROP TABLE IF EXISTS slide_rentals;
10DROP TABLE IF EXISTS slide_topic;
11DROP TABLE IF EXISTS topics;
12DROP TABLE IF EXISTS slides;
13DROP TABLE IF EXISTS staff;
14DROP TABLE IF EXISTS displays;
15DROP TABLE IF EXISTS exhibitions;
16DROP TABLE IF EXISTS artefacts;
17DROP TABLE IF EXISTS digs;
18DROP VIEW IF EXISTS dig_presentation;
19DROP VIEW IF EXISTS slide_presentation;
20DROP VIEW IF EXISTS artefact_presentation;
21
22--CREATE TABLES
23CREATE TABLE digs (
24 PRIMARY KEY (dig_id),
25 dig_id INT,
26 dig_name VARCHAR(40) NOT NULL,
27 dig_description VARCHAR(300) NULL,
28 dig_location VARCHAR(40) NOT NULL,
29 begin_date DATE NOT NULL,
30 end_date DATE NULL
31);
32--------------------------------------------------------------------------------
33CREATE TABLE artefacts (
34 PRIMARY KEY (artefact_id),
35 artefact_id INT NOT NULL,
36 artefact_name VARCHAR(30) NULL,
37 artefact_description VARCHAR(150) NULL,
38 artefact_tag VARCHAR(20) NOT NULL, --CHECK
39 artefact_shelf VARCHAR(5) NOT NULL,
40 date_found DATE NOT NULL,
41 dig INT NOT NULL,
42 grid_x INT NOT NULL,
43 grid_y INT NOT NULL,
44 depth INT NOT NULL,
45 CHECK(artefact_tag IN ('Weapon', 'Equipment', 'Item', 'Emblems', 'Jewelry', 'Camp Items')),
46 FOREIGN KEY (dig)
47 REFERENCES digs(dig_id)
48);
49--------------------------------------------------------------------------------
50CREATE TABLE exhibitions (
51 PRIMARY KEY (exhibition_id),
52 exhibition_id INT NOT NULL,
53 exhibition_name VARCHAR(40) NOT NULL,
54 exhibition_description VARCHAR(300) NULL
55);
56--------------------------------------------------------------------------------
57CREATE TABLE displays (
58 PRIMARY KEY (artefact, exhibition),
59 artefact INT NOT NULL,
60 exhibition INT NOT NULL,
61 begin_date DATE NOT NULL,
62 end_date DATE NOT NULL,
63 FOREIGN KEY (artefact)
64 REFERENCES artefacts(artefact_id),
65 FOREIGN KEY (exhibition)
66 REFERENCES exhibitions(exhibition_id)
67);
68--------------------------------------------------------------------------------
69CREATE TABLE staff (
70 PRIMARY KEY (employee_id),
71 employee_id INT NOT NULL,
72 employee_name VARCHAR(50) NOT NULL,
73 employee_phone VARCHAR(15) NOT NULL UNIQUE,
74 employee_email VARCHAR(50) NOT NULL UNIQUE,
75);
76--------------------------------------------------------------------------------
77CREATE TABLE slides (
78 PRIMARY KEY (slide_id),
79 slide_id INT NOT NULL,
80 dig INT NOT NULL,
81 slide_folder VARCHAR(10) NOT NULL,
82 FOREIGN KEY (dig)
83 REFERENCES digs(dig_id)
84);
85--------------------------------------------------------------------------------
86CREATE TABLE topics (
87 PRIMARY KEY (topic_index),
88 topic_index INT NOT NULL,
89 topic_name VARCHAR(30) NOT NULL,
90 topic_description VARCHAR(100) NULL
91);
92--------------------------------------------------------------------------------
93CREATE TABLE slide_topic (
94 PRIMARY KEY (slide, topic),
95 slide INT NOT NULL,
96 topic INT NOT NULL,
97 FOREIGN KEY (slide)
98 REFERENCES slides(slide_id),
99 FOREIGN KEY (topic)
100 REFERENCES topics(topic_index)
101);
102--------------------------------------------------------------------------------
103CREATE TABLE slide_rentals (
104 PRIMARY KEY (slide, employee),
105 slide INT NOT NULL,
106 employee INT NOT NULL,
107 date_borrowed DATE NOT NULL,
108 FOREIGN KEY (slide)
109 REFERENCES slides(slide_id),
110 FOREIGN KEY (employee)
111 REFERENCES staff(employee_id)
112);
113
114--------------------------------------------------------------------------------
115CREATE TABLE written_content (
116 PRIMARY KEY (wc_id),
117 wc_id INT NOT NULL,
118 title VARCHAR(50) NOT NULL,
119 category VARCHAR(7) NOT NULL,
120 published DATE NOT NULL,
121 main_author VARCHAR(50) NOT NULL,
122 wc_shelf VARCHAR(5) NOT NULL,
123 CHECK(category IN ('Book', 'Journal', 'Paper', 'Article')),
124);
125--------------------------------------------------------------------------------
126CREATE TABLE dig_papers (
127 PRIMARY KEY(dig, wc),
128 dig INT NOT NULL,
129 wc INT NOT NULL,
130 FOREIGN KEY (dig)
131 REFERENCES digs(dig_id),
132 FOREIGN KEY (wc)
133 REFERENCES written_content(wc_id)
134);
135--------------------------------------------------------------------------------
136CREATE TABLE copies (
137 PRIMARY KEY(copy_id),
138 copy_id INT NOT NULL,
139 wc INT NOT NULL,
140 shelf VARCHAR(4) NOT NULL,
141 copy_nr INT NOT NULL,
142 FOREIGN KEY (wc)
143 REFERENCES written_content(wc_id)
144);
145--------------------------------------------------------------------------------
146CREATE TABLE patrons (
147 PRIMARY KEY(patron_id),
148 patron_id INT NOT NULL,
149 first_name VARCHAR(25) NOT NULL,
150 last_name VARCHAR(25) NOT NULL,
151 email VARCHAR(50) NOT NULL UNIQUE,
152 phone VARCHAR(15) NOT NULL UNIQUE,
153);
154--------------------------------------------------------------------------------
155CREATE TABLE library_rentals (
156 PRIMARY KEY(copy_id, patron),
157 copy_id INT NOT NULL,
158 patron INT NOT NULL,
159 date_borrowed DATE NOT NULL,
160 date_returned DATE NULL,
161 FOREIGN KEY (copy_id)
162 REFERENCES copies(copy_id),
163 FOREIGN KEY (patron)
164 REFERENCES patrons(patron_id)
165);
166----------------------------------------------------------------------------------------------------------------------------------------------------------------
167----------------------------------------------------------------------------------------------------------------------------------------------------------------
168----------------------------------------------------------------------------------------------------------------------------------------------------------------
169--INSERT VALUES
170
171INSERT INTO digs (dig_id, dig_name, dig_description, dig_location, begin_date)
172 VALUES (1, 'Birka', 'The Viking City of Birka is situated on the island of Björkö in Lake Mälaren, Birka was one of the most important trading centers for Vikings.', 'Stockholm', '1902-01-01'),
173 (2, 'Sigtuna', 'Sigtuna is Sweden’s oldest preserved city, founded around 970 by Erik Segersäll, and its layout and size are essentially the same as when it was established.', 'Sigtuna City', '1912-02-02'),
174 (3, 'Uppåkra', 'The original foundation of Uppåkra is dated to the last century BC, although its importance appears to have increased in the fifth century.', 'Staffanstorp', '1920-03-03'),
175 (4, 'Helgö', 'The old trading town on Helgö began to emerge around the year 200 AD, 500 years before Birka at Björkö. The first archaeological dig in 1954 uncovered the remains of the early settlement, including a workshop area which attracted international interest.', 'Stockholm', '1929-04-04'),
176 (5, 'Gamla Uppsala', 'As early as the 3rd century AD and the 4th century AD and onwards, it was an important religious, economic and political centre.', 'Uppsala', '1938-05-05'),
177 (6, 'Lapphyttan', 'Lapphyttan or Lapphyttejarn in Norberg Municipality, Sweden, may be regarded as the type site for the Medieval Blast Furnace.', 'Bergslagen', '1950-06-06');
178----------------------------------------------------------------------------------------------------------------------------------------------------------------
179INSERT INTO artefacts (artefact_id, artefact_name, artefact_description, artefact_tag, artefact_shelf, date_found, dig, grid_x, grid_y, depth)
180 VALUES (1, 'Spike Hammer', 'En hammare', 'Weapon', 'C21', '1912-10-01', 1, 2, 1, 10),
181 (2, 'Snake Ring', 'The one true ring', 'Jewelry', 'B54', '1914-10-14', 1, 5, 5, 1),
182 (3, 'Battle Helmet', 'En hjälm med ett hål i', 'Equipment', 'A54', '1922-10-12', 1, 2, 2, 2),
183 (4, 'Executioners Helmet', 'Massa blod på hjälmen', 'Equipment', 'D53', '1927-02-21', 1, 3, 5, 3),
184 (5, 'Battle Armor', 'En stridsväst från imperiets soldater', 'Equipment', 'A43', '1932-03-21', 1, 5, 1, 4),
185 (6, 'Chain Mace', 'En klubba av stål', 'Weapon', 'A75', '1933-04-26', 2, 4, 3, 5),
186 (7, 'Thors Hammer', 'Den tillhör en asagud, kanske', 'Weapon', 'B56', '1939-05-19', 2, 1, 1, 6),
187 (8, 'Thors Helmet', 'En hjälm som tillhör thor', 'Equipment', 'C93', '1943-08-18', 2, 2, 2, 7),
188 (9, 'Viking Greaves', 'Någonting någonting vikingasaker', 'Equipment', 'D46', '1944-01-04', 2, 1, 5, 8),
189 (10, 'Layered Vest', 'En VÄST', 'Equipment', 'A31', '1949-11-28', 2, 3, 4, 9),
190 (11, 'Poll Axe', 'En yxa som röstade i senaste valet', 'Weapon', 'B17', '1955-04-12', 3, 4, 3, 11),
191 (12, 'Wolfs Head', 'Varghuvud som konstigt nog inte ruttnat på 3000 år', 'Jewelry', 'C71', '1958-04-10', 3, 2, 5, 12),
192 (13, 'Oriental Blade', 'Asiatisk kniv, väldigt liten', 'Weapon', 'D57', '1963-10-17', 3, 5, 4, 2),
193 (14, 'Steel Flail', 'Någonting av stål', 'Weapon', 'A66', '1964-11-23', 3, 3, 3, 3),
194 (15, 'Destroyers Armor', 'Imperiets förstörare hade denna på sig', 'Equipment', 'B11', '1967-05-11', 3, 1, 2, 5),
195 (16, 'Jarls Sword', 'Hövdingen hade ett fiiint svärd som var gjort i frigolit konstigt nog', 'Weapon', 'C22', '1971-08-23', 4, 4, 2, 1),
196 (17, 'Reinforced Bow', 'Pilbåge förstärkt av armeringsjärn', 'Weapon', 'D1', '1978-02-15', 4, 4, 5, 0),
197 (18, 'Bear Claw', 'Bamse saknar denna', 'Weapon', 'B62', '1979-07-16', 4, 2, 4, 5),
198 (19, 'Javelin', 'En missil som uppfanns av amerikanarna', 'Weapon', 'C4', '1986-08-01', 4, 1, 1, 1),
199 (20, 'Scavenger Pick', 'En hacka', 'Equipment', 'A99', '1988-12-01', 4, 2, 2, 2),
200 (21, 'Armor of Might', 'En väst för fattiga bönder', 'Equipment', 'B7', '1990-05-17', 5, 3, 3, 3),
201 (22, 'Pridecoloured dagger', 'Även på vikingatiden var det viktigt med LGBT-rörelsen', 'Emblems', 'C15', '1994-04-20', 5, 4, 4, 4),
202 (23, 'Scramaseax', 'Jag har ingen aning vad detta är', 'Equipment', 'D43', '2003-07-14', 5, 5, 5, 5),
203 (24, 'Viking Maul', 'Darth maul cosplay', 'Weapon', 'A8', '2003-10-29', 5, 3, 2, 1),
204 (25, 'Flaming Boots', 'De har brunnit under marken i tusentals år', 'Equipment', 'B5', '2005-06-08', 5, 1, 2, 3),
205 (26, 'Copper Helmet', 'Bara en vanlig hjälm', 'Equipment', 'C1', '2002-10-03', 5, 4, 3, 2),
206 (27, 'Cheifs Cloak', 'Chefen har nya kläder', 'Equipment', 'D2', '1999-12-24', 6, 5, 4, 3),
207 (28, 'Yew Bow', 'Ja du', 'Weapon', 'R2D2', '1992-04-20', 6, 2, 1, 5),
208 (29, 'Old Dagger', 'En fullständigt modern kniv', 'Weapon', 'C3PO', '1990-10-08', 6, 3, 4, 5),
209 (30, 'Serpent Head', 'Läs harry potter 2 för mer detaljer', 'Equipment', 'D95', '1984-11-29', 6, 4, 3, 2);
210----------------------------------------------------------------------------------------------------------------------------------------------------------------
211INSERT INTO staff(employee_id, employee_name, employee_phone, employee_email)
212 VALUES (1, 'Gunder Nordström', '0418-7602859', 'GundNord@mailmetrash.se'),
213 (2, 'Carola MÃ¥rtensson', '0152-5474554', 'Carola@finest.se'),
214 (3, 'Tyko Ã…berg', ' 0586-7250753', 'Tyko@hotmail.com'),
215 (4, 'Fjalar Eliasson', '0553-1896844', 'Fjaleli@liu.se'),
216 (5, 'Lukas Nordin', '0910-4600520', 'lukas_badboy@live.se'),
217 (6, 'Master Chief', '090-6435401', 'MC@halo.gov'),
218 (7, 'Fritjof Hermansson', '044-5502426', 'fritjof@riksdagen.se'),
219 (8, 'Virgin Näslund', '0393-4984238', 'thona113@student.liu.se');
220----------------------------------------------------------------------------------------------------------------------------------------------------------------
221INSERT INTO slides(slide_id, dig, slide_folder)
222 VALUES (1, 1, 'A11'),
223 (2, 1, 'B22'),
224 (3, 2, 'C33'),
225 (4, 2, 'D44'),
226 (5, 3, 'A55'),
227 (6, 3, 'B66'),
228 (7, 4, 'C77'),
229 (8, 4, 'D88'),
230 (9, 5, 'A99'),
231 (10, 5, 'B43'),
232 (11, 6, 'C21'),
233 (12, 6, 'D65'),
234 (13, 1, 'A85'),
235 (14, 2, 'B93'),
236 (15, 3, 'C54');
237----------------------------------------------------------------------------------------------------------------------------------------------------------------
238INSERT INTO written_content (wc_id, title, category, published, main_author, wc_shelf)
239 VALUES (1, 'The Lost City of the Monkey God: A True Story', 'Book', '2002-10-10', 'Douglas Preston', 'A21'),
240 (2, 'Fingerprints of the Gods', 'Book', '2010-12-01', 'Graham Hancock', 'B44'),
241 (3, 'Turn Right at Machu Picchu', 'Book', '1999-03-01', 'Mark Adams', 'C65'),
242 (4, 'A Practical Handbook of Archaeology', 'Book', '2015-06-06', 'Cristopher Catling', 'D24'),
243 (5, 'The Archaeological Journal Vol. 120', 'Journal', '1963-01-01', 'Mark Pearce', 'A54'),
244 (6, 'Accordia Research Papers 13', 'Journal', '2013-04-03', 'Ruth D. Whitehouse', 'B75'),
245 (7, 'Current Swedish Archaeology Vol. 17', 'Journal', '2017-01-05', 'Fredrik Fahlander', 'C43'),
246 (8, 'Viking Fund Medalists 1954', 'Article', '1955-06-01', 'Robert Redfield', 'D61'),
247 (9, 'Social Scandinavia in the Viking Age', 'Article', '1922-03-01', 'Mary Wilhelmine Williams', 'A34'),
248 (10, 'Were the Vikings Really Green??', 'Article', '2018-07-02', 'Kathryn A. Catlin', 'B59'),
249 (11, 'Viking Review *clap* *clap*', 'Article', '2006-06-06', 'Felix Memer', 'D66');
250----------------------------------------------------------------------------------------------------------------------------------------------------------------
251INSERT INTO copies(copy_id, wc, copy_nr, shelf)
252 VALUES (1, 1, 1, 'A11'),
253 (2, 1, 2, 'B11'),
254 (3, 2, 1, 'C11'),
255 (4, 2, 2, 'D11'),
256 (5, 3, 1, 'A22'),
257 (6, 3, 2, 'B22'),
258 (7, 4, 1, 'C22'),
259 (8, 4, 2, 'D22'),
260 (9, 5, 1, 'A33'),
261 (10, 5, 2, 'B33'),
262 (11, 6, 1, 'C33'),
263 (12, 6, 2, 'D33'),
264 (13, 7, 1, 'A44'),
265 (14, 7, 2, 'B44'),
266 (15, 8, 1, 'C44'),
267 (16, 8, 2, 'D44'),
268 (17, 9, 1, 'A55'),
269 (18, 9, 2, 'B55'),
270 (19, 10, 1, 'C55'),
271 (20, 10, 2, 'D55'),
272 (21, 11, 1, 'A66'),
273 (22, 11, 2, 'B66');
274----------------------------------------------------------------------------------------------------------------------------------------------------------------
275INSERT INTO patrons(patron_id, first_name, last_name, email, phone)
276 VALUES (1, 'Samir', 'Axelsson', 'SamirAxelsson@rhyta.com', '0684-9664860'),
277 (2, 'Pelle', 'Nilsson', 'PelleNilsson@dayrep.com', '013-5649521'),
278 (3, 'Alfons', 'Aleksson', 'AlfonsAleksson@rhyta.com', '08-2446412'),
279 (4, 'Melvin', 'Hermansson', 'MelvinHermansson@teleworm.us', '0910-2885983'),
280 (5, 'Carlos', 'MÃ¥nsson', 'CarlosMansson@jourrapide.com', '0542-5463763');
281----------------------------------------------------------------------------------------------------------------------------------------------------------------
282INSERT INTO library_rentals(copy_id, patron, date_borrowed, date_returned)
283 VALUES (1, 4, '2006-06-06','2006-07-06'),
284 (10, 4, '2006-06-06','2006-08-06'),
285 (5, 3, '2007-04-06','2007-05-06'),
286 (4, 1, '2007-04-07','2007-05-07'),
287 (12, 2, '2008-06-06','2008-07-06'),
288 (2, 5, '2009-11-07','2009-12-08'),
289 (22, 3, '2010-01-01','2010-01-06'),
290 (3, 1, '2011-04-06','2011-05-06'),
291 (21, 3, '2018-12-06', NULL),
292 (19, 1, '2018-11-30', NULL),
293 (17, 5, '2018-12-15', NULL);
294----------------------------------------------------------------------------------------------------------------------------------------------------------------
295INSERT INTO slide_rentals(slide, employee, date_borrowed)
296 VALUES (1,7,'2006-06-06'),
297 (10,4,'2006-06-06'),
298 (5,3,'2007-04-06'),
299 (4,1,'2007-04-07'),
300 (9,2,'2008-06-06'),
301 (2,5,'2009-11-07');
302----------------------------------------------------------------------------------------------------------------------------------------------------------------
303INSERT INTO dig_papers(dig, wc)
304 VALUES (4,10),
305 (4,11),
306 (5,2),
307 (4,2),
308 (2,4),
309 (1,5),
310 (6,2),
311 (6,3);
312----------------------------------------------------------------------------------------------------------------------------------------------------------------
313INSERT INTO exhibitions(exhibition_id, exhibition_name, exhibition_description)
314 VALUES (1, 'Vikings armor', 'A variety of diffrent types of vikingsarmor'),
315 (2, 'Ranged weapons','Bows and javelins'),
316 (3, 'Helmets', 'Viking helmets'),
317 (4, 'Swords','Swords forged by vikings'),
318 (5, 'Rare items','Rare findings from our digs');
319----------------------------------------------------------------------------------------------------------------------------------------------------------------
320INSERT INTO displays(artefact, exhibition, begin_date, end_date)
321 VALUES (3, 1, '2018-01-01', '2018-12-31'),
322 (5, 1, '2018-01-01', '2018-12-31'),
323 (10, 1, '2018-01-01', '2018-12-31'),
324 (15, 1, '2018-01-01', '2018-12-31'),
325 (17, 2, '2018-05-01', '2018-12-31'),
326 (19, 2, '2018-05-01', '2018-12-31'),
327 (23, 2, '2018-05-01', '2018-12-31'),
328 (28, 2, '2018-05-01', '2018-12-31'),
329 (4, 3, '2017-10-01', '2018-12-31'),
330 (8, 3, '2017-10-01', '2018-12-31'),
331 (12, 3, '2017-10-01', '2018-12-31'),
332 (26, 3, '2017-10-01', '2018-12-31'),
333 (13, 4, '2018-08-01', '2019-03-01'),
334 (16, 4, '2018-08-01', '2019-03-01'),
335 (22, 4, '2018-08-01', '2019-03-01'),
336 (29, 4, '2018-08-01', '2019-03-01'),
337 (2, 5, '2018-12-01', '2019-12-31'),
338 (7, 5, '2018-12-01', '2019-12-31'),
339 (21, 5, '2018-12-01', '2019-12-31'),
340 (25, 5, '2018-12-01', '2019-12-31'),
341 (30, 5, '2018-12-01', '2019-12-31');
342----------------------------------------------------------------------------------------------------------------------------------------------------------------
343INSERT INTO topics(topic_index, topic_name, topic_description)
344 VALUES (1, 'Skeletons', 'Wubba lubba doot doot.'),
345 (2, 'Unknown', 'X-files stuff.'),
346 (3, 'Dig-site', 'Id plow that.'),
347 (4, 'Weapons', 'Sword, daggers, maces etc.'),
348 (5, 'Equipment', 'Armor, clothes, shoes etc.');
349----------------------------------------------------------------------------------------------------------------------------------------------------------------
350INSERT INTO slide_topic(slide, topic)
351 VALUES (1, 1),
352 (2, 1),
353 (3, 1),
354 (4, 2),
355 (5, 2),
356 (6, 2),
357 (7, 3),
358 (8, 3),
359 (9, 3),
360 (10, 4),
361 (11, 4),
362 (12, 4),
363 (13, 5),
364 (14, 5),
365 (15, 5);
366--CREATE VIEWS
367----------------------------------------------------------------------------------------------------------------------------------------------------------------
368----------------------------------------------------------------------------------------------------------------------------------------------------------------
369
370-- PRESENTERAR DIGS OCH HUR MÃ…NGA ARTEFAKTER OCH SLIDES SOM FINNS PÃ… VARJE DIG.
371GO
372CREATE VIEW dig_presentation AS
373 SELECT D.dig_id, D.dig_name, COUNT(DISTINCT A.artefact_id) as 'Collected Artefacts', COUNT(DISTINCT S.slide_id) AS 'Slides Taken'
374 FROM digs D
375 LEFT JOIN artefacts A
376 ON (D.dig_id = A.dig)
377 LEFT JOIN slides S
378 ON (D.dig_id = S.dig)
379GROUP BY D.dig_id, D.dig_name;
380
381
382-- PRESENTERAR ALLA SLIDES SAMT INFORMATION OM DESSA.
383GO
384CREATE VIEW slide_presentation AS
385SELECT S.slide AS 'Slide ID', D.dig_name AS 'Digsite', T.topic_name AS 'Topic', E.employee_name AS 'Borrowed By', R.date_borrowed AS 'Date Borrowed', Z.slide_folder AS 'Designated Folder'
386 FROM slide_topic S
387 LEFT JOIN slides Z
388 ON (S.slide = Z.slide_id)
389 LEFT JOIN digs D
390 ON (Z.dig = D.dig_id)
391 LEFT JOIN topics T
392 ON (S.topic = T.topic_index)
393 LEFT JOIN slide_rentals R
394 ON (S.slide = R.slide)
395 LEFT JOIN staff E
396 ON (R.employee = e.employee_id);
397
398-- PRESENTERAR ALLA ARTEFAKTER SAMT INFORMATION OM DESSA
399GO
400CREATE VIEW artefact_presentation AS
401SELECT A.Artefact_id AS 'Artefact ID', A.artefact_name AS 'Artefact Name', A.artefact_shelf AS Shelf, A.date_found AS 'Date Found', D.dig_name AS 'Dig Site', E.exhibition_name AS 'Exhibition'
402 FROM Artefacts A
403 LEFT JOIN digs D
404 ON (A.dig = D.dig_id)
405 LEFT JOIN displays
406 ON (A.artefact_id = displays.artefact)
407 LEFT JOIN exhibitions E
408 ON (displays.exhibition = E.exhibition_id);
409
410
411--CREATE TRIGGERS/PROCEDURES
412GO
413CREATE TRIGGER topic_cleaner
414 ON topics
415 AFTER DELETE
416 AS BEGIN
417 DELETE FROM slide_topic
418 WHERE slide_topic.topic IN (
419 SELECT deleted.topic_index FROM deleted
420);
421END;