· 7 years ago · Dec 18, 2018, 01:48 PM
1-- DDL
2
3-- (1) DROPS
4-- (2) CREATE TABLES
5-- (3) INSERT VALUES
6-- (4) CREATE VIEWS
7-- (5) CREATE TRIGGERS/PROCEDURES
8DROP VIEW IF EXISTS books_loans;
9DROP VIEW IF EXISTS slide_loans;
10DROP VIEW IF EXISTS artefact_loans;
11DROP VIEW IF EXISTS books_in;
12DROP VIEW IF EXISTS artefacts_total;
13DROP VIEW IF EXISTS total_loans;
14
15DROP TRIGGER IF EXISTS delete_dig_person;
16DROP TRIGGER IF EXISTS person_cleaner;
17
18DROP TABLE IF EXISTS loans;
19DROP TABLE IF EXISTS slides;
20DROP TABLE IF EXISTS books;
21DROP TABLE IF EXISTS artefacts;
22DROP TABLE IF EXISTS persons;
23DROP TABLE IF EXISTS deleted_persons;
24DROP TABLE IF EXISTS digs;
25DROP TABLE IF EXISTS documents;
26
27
28
29-- person (person_id, p_name)
30CREATE TABLE digs (
31 PRIMARY KEY (dig_id),
32
33 dig_site VARCHAR(100),
34 dig_id INT,
35 d_description VARCHAR(250),
36
37);
38
39CREATE TABLE persons (
40 PRIMARY KEY(p_id),
41
42 p_id INT,
43 firstname VARCHAR(35),
44 lastname VARCHAR(35),
45 dig_id INT,
46
47 FOREIGN KEY (dig_id)
48 REFERENCES digs(dig_id)
49
50);
51
52CREATE TABLE deleted_persons (
53 PRIMARY KEY(p_id),
54
55 p_id INT,
56 firstname VARCHAR(35),
57 lastname VARCHAR(35),
58 dig_id INT,
59
60 FOREIGN KEY (dig_id)
61 REFERENCES digs(dig_id)
62);
63
64
65-- documents (document_id, title, published_year, d_location, author, person)
66CREATE TABLE documents (
67 PRIMARY KEY(d_id),
68
69 d_id INT,
70 title VARCHAR(30),
71 published_year INT,
72 d_location VARCHAR(30),
73 author VARCHAR(30),
74
75 );
76
77-- artefacts(a_id, sequential_artefact, coordinate, depth, date_found, description, location, date_out, date_back)
78
79
80
81CREATE TABLE artefacts(
82 PRIMARY KEY(a_id),
83
84 a_id AS CONCAT (dig_id, '-', sequential_artefact) PERSISTED NOT NULL,
85 sequential_artefact INT,
86 coordinate VARCHAR(30),
87 depth VARCHAR(30),
88 date_found DATE,
89 a_descritpion VARCHAR(50),
90 a_location VARCHAR(30),
91 dig_id INT,
92
93
94-- FOREIGN KEY (dig_id) -- DETTA FUNGERAR EJ???
95-- REFERENCES digs(dig_id) -- VARFÖR FUNGERAR INTE DETTA???
96
97);
98
99CREATE TABLE books(
100 PRIMARY KEY (b_id),
101
102 b_id INT,
103 title VARCHAR(30),
104 published_year VARCHAR(10),
105 author VARCHAR(35),
106 b_location VARCHAR(50),
107
108
109);
110
111CREATE TABLE slides(
112 PRIMARY KEY (s_id),
113
114 s_id AS CONCAT (dig_id, '-', sequential_slide) PERSISTED NOT NULL,
115 sequential_slide INT,
116 s_subject VARCHAR(35),
117 s_description VARCHAR(40),
118 s_location VARCHAR(35),
119 dig_id INT,
120
121
122
123-- CONSTRAINT fk_inv_dig_id
124 FOREIGN KEY (dig_id)
125 REFERENCES digs(dig_id)
126-- ON DELETE CASCADE
127);
128
129CREATE TABLE loans(
130 PRIMARY KEY (l_id),
131
132 l_id INT,
133 date_out DATE,
134 date_back DATE,
135 s_id VARCHAR(25),
136 b_id INT,
137 a_id VARCHAR(25),
138 p_id INT
139
140
141 FOREIGN KEY (s_id)
142 REFERENCES slides(s_id),
143
144 FOREIGN KEY (b_id)
145 REFERENCES books(b_id),
146
147 FOREIGN KEY (a_id)
148 REFERENCES artefacts(a_id),
149
150 CONSTRAINT fk_inv_loans_p_id
151 FOREIGN KEY (p_id)
152 REFERENCES persons (p_id)
153 ON DELETE CASCADE
154
155);
156
157 INSERT INTO digs (dig_site, dig_id, d_description)
158 VALUES ('Chan-Chan', 12, 'Beach on the coast of the commune of MehuÃn in southern Chile.'),
159 ('Monte Verde', 43, 'Monte Verde is an archaeological site in southern Chile, located near Puerto Montt, Southern Chile'),
160 ('Fell Cave', 9, 'Cueva Fell is a natural cave and archaeological site in southern Patagonia.'),
161 ('Pali Aike', 4, 'The Pali-Aike National Park is a park located in the Magallanes Region of the Chilean Patagonia.'),
162 ('An Yang', 6, 'An yang is a prefecture-level city in Henan province, China.'),
163 ('Kykkos Monastery', 21, 'Kykkos Monastery which lies 20 km west of Pedoulas, is one of the wealthiest and best-known monasteries in Cyprus.'),
164 ('Notre Dame de tyre', 67, 'Notre Dame de Tyre or Our Lady of Tyre is a monastic church in Nicosia.');
165
166 INSERT INTO persons (p_id, firstname, lastname, dig_id)
167 VALUES (21, 'Bengt', 'Andersson',12),
168 (32, 'Anna', 'Nilsson',12),
169 (33, 'Johan', 'Ahlberg',12),
170 (97, 'Anders', 'Svensson',43),
171 (12, 'Jennifer', 'Berg',43),
172 (11, 'Emelie', 'Palner',9),
173 (77, 'Elinor', 'Nyberg',9),
174 (87, 'Hannes', 'Lindgren',9),
175 (99, 'Jacob', 'Lycke',4),
176 (94, 'Olivia', 'Wennborg',4),
177 (55, 'Johan', 'Haggar',4),
178 (67, 'Axel', 'Guditz',4),
179 (61, 'Jonathan', 'Solberg',6),
180 (81, 'Sara', 'Andersdotter',21),
181 (85, 'Amanda', 'Silfversparre',21),
182 (74, 'Niklas', 'Guldsked',21),
183 (49, 'Sven', 'Laxnacke',67),
184 (41, 'Calle', 'Hoffeinheim',67);
185
186
187 INSERT INTO documents (d_id, title, published_year, d_location, author)
188 VALUES (392, 'Life On Earth', 1992, 'Shelf: A', 'James Cameron'),
189 (3582, 'Book Of Life', 2002, 'Shelf: D', 'Niklas Berg'),
190 (4921, 'Artefacts 101', 1952, 'Shelf: B', 'Gunnar Martin'),
191 (353, 'Cosmos', 2016, 'Shelf: C', 'Joseph Bang'),
192 (981, 'Space', 2002, 'Shelf: G', 'Anna Smith'),
193 (111, 'Moon landing 1969', 1970, 'Shelf: C', 'Anna Smith'),
194 (455, 'Festerist 101', 1994, 'Shelf: A', 'Clarence Coleman'),
195 (12, 'History of Tour de France', 2005, 'Shelf: A', 'Phil Stevenson'),
196 (71, 'How to dig a tunnel', 2010, 'Shelf: A', 'Christopher O.Neil'),
197 (9746, 'Basejump', 2013, 'Shelf: B', 'Dustin Hoffman'),
198 (32221, 'History of dinosaurs', 1987, 'Shelf C', 'Michael Bay'),
199 (4942837, 'Formel 1', 2018, 'Shelf: D', 'Stan Lee');
200
201
202 INSERT INTO books (b_id, title, published_year, author, b_location)
203 VALUES (123, 'FOR AZEROTH!', 2007,'Dillon McNillon', 'Borrowed'),
204 (435, 'National Treasure', 2003, 'Nicolas Cage', 'Shelf:A22'),
205 (563, 'WAOOW', 2001, 'Owen Wilson', 'Shelf:A22'),
206 (152, 'Nonstop', 2018, 'Drake', 'Borrowed'),
207 (880, 'Bridget Jones dagbok', 2002, 'Yung Nylla', 'Shelf:B12'),
208 (981, 'Life of Dillon', 2001, 'John Cleese', 'Borrowed'),
209 (654, 'Hejhej', 2005, 'J&J', 'Borrowed');
210
211
212
213 INSERT INTO artefacts (sequential_artefact, coordinate, depth, date_found, a_descritpion, a_location,dig_id)
214 VALUES (465, '30:40', '20 cm', '2017-05-23', 'Small round object', 'Borrowed', 21),
215 (266, '30:45', '15 cm', '2017-05-23', 'Small round object', 'Borrowed', 43),
216 (266, '30:35', '39 cm', '2017-05-23', 'Small round object', 'Borrowed', 9),
217
218 (275, '32:42', '10 cm', '1997-05-29', 'Silver coin', 'Borrowed',6),
219 (765, '32:42', '180 cm', '1997-05-12', 'Gold Coin', 'Borrowed',6),
220 (865, '32:42', '20 cm', '1997-05-27', 'Chinese sword', 'Borrowed',6),
221 (145, '32:32', '0.5 cm', '1997-01-12', 'Chinese golden knife', 'Borrowed',6),
222 (132, '32:41', '200 cm', '1997-09-01', 'Chinese helmet', 'Borrowed',6),
223 (166, '29:45', '234 cm', '1997-07-14', 'Chinese breast plate', 'Borrowed',6),
224 (125, '27:43', '500 cm', '1997-06-14', 'Chinese shield', 'Borrowed',6),
225
226 (151, '12:92:14', '37 cm', '1999-02-24', 'Wooden crucifix', 'In-House',21),
227 (118, '30:40:21', '94 cm', '1992-05-19', 'Golden ring', 'In-House', 21),
228 (119, '30:40:23', '122 cm', '1993-08-17', 'Monk cape', 'In-House', 21),
229 (117, '30:40:55', '431 cm', '1992-01-12', 'Golden chain', 'In-House',21),
230 (110, '30:40:12', '12 cm', '1992-08-24', 'Candle holder', 'In-House', 21),
231 (109, '30:40:98', '542 cm', '1992-02-02', 'Golden candle holder', 'In-House', 21),
232 (108, '30:40:24', '122 cm', '1993-02-23', 'Metal crucifix', 'In-House', 21),
233 (102, '30:40:77', '32 cm', '1993-05-29', 'Wooden bowl', 'In-House', 21),
234 (100, '30:40:98', '96 cm', '1992-06-28', 'black priest cape', 'In-House', 21),
235
236 (964, '98:34', '200 cm', '2015-02-23', 'wolf skeleton', 'Borrowed',4),
237 (965, '95:42', '95 cm', '2015-03-24', 'Puma skeleton', 'Borrowed', 4),
238 (936, '93:43', '94 cm', '2015-03-22', 'Patagonian Hairy Armadillo', 'Borrowed',4),
239
240 (462, '432:431', '0,1 cm', '1992-04-11', 'Old brick wall', 'Borrowed',67),
241
242 (732, '84:42', '43 cm', '2013-05-21', 'Stuffed Lizard', 'In-House',43),
243 (757, '82:53', '53 cm', '2013-08-22', 'Snake skin', 'In-House',43),
244 (752, '81:95', '123 cm', '2017-08-19', 'Puma Skeleton', 'In-House',43),
245
246
247 (343, '11:40', '13 cm', '1982-01-23', 'Fish skeleton', 'In-house', 9),
248 (365, '11:42', '11 cm', '1983-04-13', 'Big fish skeleton', 'In-House', 9),
249 (375, '11:53', '8 cm', '1982-06-19', 'Medium fish skeleton', 'In-House',9),
250 (392, '11:52', '1.5 cm', '1983-05-29', 'Random fish skeleton', 'In-House', 9),
251 (326, '11:39', '5.5 cm', '1982-09-27', 'Old seaweed', 'Borrowed', 9),
252
253 (753, '321:401', '00 cm', '1999-05-23', 'Rock statue', 'Borrowed', 12),
254 (725, '324:402', '45 cm', '1999-03-23', 'Wooden statue', 'Borrowed',12),
255 (769, '381:471', '921 cm', '1999-06-28', 'Metal statue', 'Borrowed', 12),
256 (726, '339:412', '549 cm', '2000-03-12', 'Round random thing', 'Borrowed',12),
257
258
259
260 (212, '282:52', '70 cm', '2017-02-22', 'Small round object', 'In-House', 12),
261 (214, '284:42', '30 cm', '2017-05-23', 'Dinosaur bone', 'Borrowed', 12),
262 (285, '295:15', '65 cm', '2017-03-18', 'Skin bone', 'In-House',9),
263 (286, '251:12', '800 cm', '2015-05-23', 'Mammoth', 'In-House',2),
264 (203, '295:09', '300 cm', '2012-11-23', 'Tiger bone', 'Borrowed',12),
265
266 (434, '302:140', '80 cm', '2002-02-06', 'Small round object', 'Borrowed',6),
267 (491, '350:470', '470 cm', '2004-03-12', 'Big round object', 'In-House',4),
268 (461, '730:430', '205 cm', '2007-06-21', 'Small round object', 'Borrowed',12),
269
270 (453, '230:240', '240 cm', '2011-02-23', 'Small round object', 'Borrowed', 12),
271 (361, '530:340', '120 cm', '2013-01-23', 'Small round object', 'Borrowed',12);
272
273
274
275 INSERT INTO slides(sequential_slide, s_subject, s_description, s_location, dig_id)
276 VALUES (721, 'Animal tooth', 'Pictures of tiger tooth', 'In-House', 12),
277 (123, 'Animal tooth', 'Pictures of Megaladon tooth', 'Borrowed',12),
278 (46, 'Animal tooth', 'Pictures of snake tooth','In-house',12),
279 (15, 'Fish', 'Fish documents', 'In-House',43),
280 (426, 'Human', 'Pictures of human bone', 'In-house',4),
281 (646, 'Wand', 'Picture of Voldemorts wand', 'In-house', 67),
282 (223, 'Fish', 'Octopus', 'In-House',21),
283
284 (536, 'Animal tooth', 'Pictures of mammoth bone', 'Borrowed',67),
285 (234, 'Coin', 'Documents of chinese Coins','Borrowed',9),
286 (921, 'Sword', 'Journals swords from the roman empire', 'Borrowed',6),
287 (391, 'Fish', 'Pictures of dead fishes', 'In-house',21),
288 (999, 'Crucifix', 'Journals of crucifixs', 'In-house',43),
289
290 (175, 'Statue', 'Drawings of old statues', 'Borrowed',67),
291 (382, 'Wood', 'Wooden artefacts', 'Borrowed',4),
292 (322, 'Wood', 'Wooden artefacts', 'Borrowed',4);
293
294
295
296 INSERT INTO loans (l_id, date_out, date_back, s_id, b_id, a_id ,p_id)
297 VALUES (48223, '2017-09-21', '2018-02-03', '12-721', NULL, NULL, 21),
298 (14221, '2018-02-19', NULL,'12-123', NULL, NULL, 61),
299 (37281, '2016-05-28', '2016-08-12', '12-46', NULL, NULL, 41),
300 (49274, NULL, NULL, '43-15', NULL, NULL, 97),
301 (84982, '2018-01-22', '2018-07-09', '4-426', NULL, NULL, 85),
302 (96827, '1999-04-13', '1999-07-16', '67-646',NULL, NULL, 11),
303
304 (23181, '2017-10-11', '2018-01-03', NULL, 435, NULL, 81),
305 (68023, '2017-03-12', '2018-05-29', NULL, 123, NULL, 49),
306 (67492, '2018-04-11', NULL, NULL, 563, NULL, 12),
307 (75939, '1997-05-11', '2000-05-11', NULL, 152, NULL, 85),
308 (30202, '2018-01-14', '2018-03-20', NULL, 880, NULL, 67),
309
310 (84114, '2018-02-01', '2018-02-21', NULL, NULL, '12-203', 32),
311 (91322, '2018-04-02', '2018-05-21', NULL, NULL, '43-732', 41),
312 (30345, '2017-02-14', '2018-05-14', NULL, NULL, '21-100', 77),
313 (50389, '2018-01-12', '2018-11-12', NULL, NULL, '9-285', 55),
314 (82849, '2018-06-12', NULL, NULL, NULL, '2-286', 49),
315 (48295, '2016-03-17', '2017-07-18', NULL, NULL, '4-965', 33),
316 (84593, '2018-09-21', NULL, NULL, NULL, '4-936', 32);
317
318
319
320 --------------------------------------------------------------------------------------------------------------------------------------------------
321 -- Första Viewn, kollar vilka böcker som är utlånade
322GO
323CREATE VIEW books_loans AS
324 SELECT L.l_id AS 'Loan ID',
325 (L.b_id) AS 'Book ID',
326 (P.p_id) AS 'Person ID',
327 (P.firstname) AS 'First name',
328 (P.lastname) AS 'Last name'
329 FROM loans AS L
330 INNER JOIN persons AS P
331 ON L.p_id = P.p_id
332 WHERE L.b_id IS NOT NULL;
333
334GO
335 --------------------------------------------------------------------------------------------------------------------------------------------------
336 -- View två som visar vilka slides som är utlånade.
337GO
338CREATE VIEW slide_loans AS
339 SELECT L.l_id AS 'Loan ID',
340 (L.s_id) AS 'Slide ID',
341 (P.p_id) AS 'Person ID',
342 (P.firstname) AS 'First name',
343 (P.lastname) AS 'Last name'
344 FROM loans AS L
345 INNER JOIN persons AS P
346 ON L.p_id = P.p_id
347 WHERE L.s_id IS NOT NULL;
348GO
349 --------------------------------------------------------------------------------------------------------------------------------------------------
350
351GO
352CREATE VIEW artefact_loans AS
353 SELECT L.l_id AS 'Loan ID',
354 (L.a_id) AS 'Artefact ID',
355 (P.p_id) AS 'Person ID',
356 (P.firstname) AS 'First name',
357 (P.lastname) AS 'Last name'
358 FROM loans AS L
359 INNER JOIN persons AS P
360 ON L.p_id = P.p_id
361 WHERE L.a_id IS NOT NULL;
362GO
363--------------------------------------------------------------------------------------------------------------
364GO
365CREATE VIEW total_loans AS
366 SELECT COUNT (L.b_id) AS 'Number of book loans', COUNT (L.s_id) as 'Numbers of slide Loans', COUNT (L.a_id) AS 'Numbers of artefact loans'
367 FROM loans AS L
368GO
369-----------------------------------------------------------------------------------------------------------------
370
371GO
372CREATE TRIGGER delete_loans_person
373 ON persons
374 FOR DELETE
375 AS BEGIN
376 DELETE FROM loans
377 WHERE loans.p_id IN (
378 SELECT deleted.p_id FROM deleted
379 );
380END ;
381GO
382--------------------------------------------------------------------------------------------------------------
383
384--------------------------------------------------------------------------------------------------------------
385/*
386GO
387CREATE TRIGGER delete_dig_person
388 ON persons
389 FOR DELETE
390 AS BEGIN
391
392 DELETE FROM slides
393 WHERE slides.dig_id IN (
394 SELECT digs.dig_id FROM digs
395 INNER JOIN deleted
396 ON digs.p_id = deleted.p_id
397 );
398
399 DELETE FROM digs
400 WHERE digs.p_id IN (
401 SELECT deleted.p_id FROM deleted
402 );
403END ;
404GO
405*/
406--------------------------------------------------------------------------------------------------------------
407 -- DENNA TRIGGER FUNGERAR -----
408GO
409CREATE TRIGGER person_cleaner
410 ON persons
411 AFTER DELETE
412 AS BEGIN
413 INSERT INTO deleted_persons (p_id, firstname, lastname, dig_id)
414 SELECT D.p_id, D.firstname, D.lastname, D.dig_id
415 FROM deleted AS D
416END;
417GO
418----------------------------------------------------------------------------------------------------------------------
419/* SELECT * FROM slides
420 WHERE slides.dig_id IN (
421 SELECT digs.dig_id FROM digs
422 INNER JOIN persons
423 ON digs.p_id = persons.p_id
424 );
425 */
426--------------------------------------------------------------------------------------------------------------------