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