Advertisement
Guest User

Untitled

a guest
Dec 18th, 2018
67
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 16.63 KB | None | 0 0
  1. -- DDL
  2.  
  3. -- (1) DROPS
  4. -- (2) CREATE TABLES
  5. -- (3) INSERT VALUES
  6. -- (4) CREATE VIEWS
  7. -- (5) CREATE TRIGGERS/PROCEDURES
  8. DROP VIEW IF EXISTS books_loans;
  9. DROP VIEW IF EXISTS slide_loans;
  10. DROP VIEW IF EXISTS artefact_loans;
  11. DROP VIEW IF EXISTS books_in;
  12. DROP VIEW IF EXISTS artefacts_total;
  13. DROP VIEW IF EXISTS total_loans;
  14.  
  15. DROP TRIGGER IF EXISTS delete_dig_person;
  16. DROP TRIGGER IF EXISTS person_cleaner;
  17.  
  18. DROP TABLE IF EXISTS loans;
  19. DROP TABLE IF EXISTS slides;
  20. DROP TABLE IF EXISTS books;
  21. DROP TABLE IF EXISTS artefacts;
  22. DROP TABLE IF EXISTS persons;
  23. DROP TABLE IF EXISTS deleted_persons;
  24. DROP TABLE IF EXISTS digs;
  25. DROP TABLE IF EXISTS documents;
  26.  
  27.  
  28.  
  29. -- person (person_id, p_name)
  30. CREATE 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.  
  39. CREATE 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.  
  52. CREATE 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)
  66. CREATE 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.  
  81. CREATE 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.  
  98. CREATE 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.  
  110. CREATE 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.  
  128. CREATE 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
  319. GO
  320. CREATE 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.  
  331. GO
  332. --------------------------------------------------------------------------------------------------------------------------------------------------
  333. -- View två som visar vilka slides som är utlånade.
  334. GO
  335. CREATE 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;
  345. GO
  346. --------------------------------------------------------------------------------------------------------------------------------------------------
  347.  
  348. GO
  349. CREATE 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;
  359. GO
  360. --------------------------------------------------------------------------------------------------------------
  361. GO
  362. CREATE 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
  365. GO
  366. -----------------------------------------------------------------------------------------------------------------
  367.  
  368. GO
  369. CREATE 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. );
  377. END ;
  378. GO
  379. --------------------------------------------------------------------------------------------------------------
  380.  
  381. --------------------------------------------------------------------------------------------------------------
  382. /*
  383. GO
  384. CREATE 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. );
  400. END ;
  401. GO
  402. */
  403. --------------------------------------------------------------------------------------------------------------
  404. -- DENNA TRIGGER FUNGERAR -----
  405. GO
  406. CREATE 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
  413. END;
  414. GO
  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. --------------------------------------------------------------------------------------------------------------------
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement