· 7 years ago · Dec 13, 2018, 09:38 AM
1-- DDL
2-- (1) DROPS
3-- (2) CREATE TABLES
4-- (3) INSERT VALUES
5-- (4) CREATE VIEWS
6-- (5) CREATE TRIGGERS/PROCEDURES
7
8DROP TABLE IF EXISTS artifact;
9DROP TABLE IF EXISTS journal;
10DROP TABLE IF EXISTS book;
11DROP TABLE IF EXISTS slide;
12DROP TABLE IF EXISTS dig;
13DROP TABLE IF EXISTS lab_shelf;
14DROP TABLE IF EXISTS person;
15
16DROP VIEW IF EXISTS Employees;
17DROP VIEW IF EXISTS Students;
18DROP VIEW IF EXISTS Artifacts_Report;
19DROP VIEW IF EXISTS Artifacts_Total;
20DROP VIEW IF EXISTS Book_Loans;
21
22-----------------------------------create table-------------------------------------------------
23
24/* Skapar tabell för entitet person */
25CREATE TABLE person (
26 PRIMARY KEY (person_id),
27 person_id VARCHAR(11),
28 p_firstname VARCHAR(15) NOT NULL,
29 p_lastname VARCHAR(15) NOT NULL,
30 p_faculty VARCHAR(30) NOT NULL,
31 p_role VARCHAR(30) NOT NULL,
32);
33
34/* Skapar tabell för entitet lab_shelf */
35CREATE TABLE lab_shelf (
36 PRIMARY KEY (shelf_id),
37 shelf_id VARCHAR(4),
38 s_row INT NOT NULL,
39);
40
41 /* Skapar tabell för entitet dig */
42CREATE TABLE dig (
43 PRIMARY KEY (dig_id),
44 dig_id INT,
45 d_site VARCHAR(20) NOT NULL,
46 d_equipment VARCHAR(50),
47 documentation VARCHAR(100),
48);
49
50 /* Skapar tabell för entitet slide */
51CREATE TABLE slide (
52 PRIMARY KEY (slide_id),
53 slide_id VARCHAR(20),
54 s_description VARCHAR(500),
55 dig_number INT NOT NULL,
56 seq_number INT NOT NULL,
57 person_id VARCHAR(11),
58 FOREIGN KEY (person_id)
59 REFERENCES person(person_id)
60);
61
62 /* Skapar tabell för entitet book */
63CREATE TABLE book (
64 PRIMARY KEY (book_id),
65 book_id INT,
66 book_name VARCHAR(50) NOT NULL,
67 author_name VARCHAR(30) NOT NULL,
68 publisher VARCHAR(30) NOT NULL,
69 barcode INT NOT NULL,
70 loaner VARCHAR(11),
71 FOREIGN KEY (loaner)
72 REFERENCES person(person_id),
73 );
74
75/* Skapar tabell för entitet journal */
76CREATE TABLE journal (
77 PRIMARY KEY (journal_id),
78 journal_id INT,
79 author_name VARCHAR(30) NOT NULL,
80 publisher VARCHAR(30) NOT NULL,
81 person_id VARCHAR(11),
82 FOREIGN KEY (person_id)
83 REFERENCES person(person_id),
84);
85
86/* Skapar tabell för entitet artifact */
87CREATE TABLE artifact (
88 PRIMARY KEY (artifact_id),
89 artifact_id VARCHAR(5),
90 a_description VARCHAR(100) NOT NULL,
91 person_id VARCHAR(11),
92 shelf_id VARCHAR(4),
93 dig_id INT,
94 FOREIGN KEY (person_id)
95 REFERENCES person(person_id),
96 FOREIGN KEY (shelf_id)
97 REFERENCES lab_shelf(shelf_id),
98 FOREIGN KEY (dig_id)
99 REFERENCES dig(dig_id),
100 );
101
102CREATE TABLE book_loan (
103 PRIMARY KEY (
104
105-----------------------------------insert into-------------------------------------------------
106
107/* Sätter in värden i person */
108INSERT INTO person (person_id, p_firstname, p_lastname, p_faculty, p_role)
109 VALUES ('930521-5543', 'Markus', 'Johansson', 'Archeological Faculty', 'Student'),
110 ('860523-1314', 'Truls', 'Joelsson', 'Archeological faculty', 'Field Researcher'),
111 ('870523-2487', 'Kruls', 'Poelsson', 'Archeological faculty', 'Slide Librarian'),
112 ('681011-9965', 'Erik', 'Babbensson', 'Archeological faculty', 'Student'),
113 ('050528-6541', 'Keno', 'Lettovinne', 'Archeological faculty', 'Director'),
114 ('760526-4343', 'Flemming', 'Dansk', 'Archeological faculty', 'Museum Librarian'),
115 ('370522-5442', 'Indiana', 'John', 'Archeological faculty', 'Lab Supervisor'),
116 ('860523-5465', 'Petder', 'Pikatchu', 'Archeological faculty', 'Field Researcher'),
117 ('800523-7815', 'Toel', 'Dillonsson', 'Archeological faculty', 'Field Researcher'),
118 ('840524-1214', 'Hannes', 'Haggarmannen', 'Archeological faculty', 'Student'),
119 ('960205-6216', 'Hannes', 'Hackersson', 'Archeological faculty', 'Student'),
120 ('830625-2519', 'Oskar', 'Von Sfvinhufvud', 'Archeological faculty', 'Field Researcher'),
121 ('821223-8921', 'Masse', 'Mattsson', 'Archeological faculty', 'Field Researcher'),
122 ('831027-5234', 'Johanna', 'Isakasson', 'Archeological faculty', 'Student'),
123 ('810921-5516', 'Johan', 'Svensson', 'Archeological faculty', 'Student'),
124 ('750317-8212', 'Joakim', 'Storboga', 'Archeological faculty', 'Student');
125
126/* Sätter in värden i lab_shelf */
127INSERT INTO lab_shelf (shelf_id, s_row)
128 VALUES ('77B', 1),
129 ('28C', 1),
130 ('69B', 2),
131 ('69C', 2),
132 ('70A', 2),
133 ('124B', 2),
134 ('1A', 1),
135 ('2A', 1),
136 ('3A', 1),
137 ('4A', 2),
138 ('5A', 2),
139 ('5B', 2),
140 ('6A', 2),
141 ('7A', 3),
142 ('7B', 3),
143 ('7C', 3),
144 ('8A', 3),
145 ('8B', 3),
146 ('9A', 3),
147 ('10A', 1),
148 ('10B', 1),
149 ('10C', 1),
150 ('11A', 1),
151 ('11B', 1),
152 ('11C', 1),
153 ('12A', 1),
154 ('12B', 1),
155 ('13B', 2),
156 ('14B', 2),
157 ('15B', 2),
158 ('16B', 3),
159 ('17B', 3),
160 ('18B', 3),
161 ('19C', 1),
162 ('20C', 1),
163 ('21A', 1),
164 ('21B', 1),
165 ('21C', 1),
166 ('22C', 2),
167 ('23C', 2),
168 ('24C', 2),
169 ('25A', 3),
170 ('26C', 3),
171 ('27D', 1),
172 ('288D', 1),
173 ('29D', 2),
174 ('30D', 2);
175
176/* Sätter in värden i dig */
177INSERT INTO dig (dig_id, documentation, d_equipment, d_site)
178 VALUES (300, 'Digital Documentation', 'Shovel', 'Hannes backyard'),
179 (301, 'Digital Documentation', 'Drill', 'Flemmingsberg'),
180 (302, 'Digital Documentation', 'Shovel and Drill', 'Ängelholm'),
181 (303, 'Digital Documentation', 'Plastic Shovel & Plastic Bucket', 'Tofta Beachclub'),
182 (304, 'Digital Documentation', 'Jack Vegas Machine', 'Säffle Bar&Krog'),
183 (305, 'Digital Documentation', 'Nose', 'Näs'),
184 (306, 'Digital Documentation', 'Rope & Sledgehammer', 'Jungle Toilet'),
185 (307, 'Digital Documentation', 'Drill', 'Bifrost'),
186 (308, 'Digital Documentation', 'Falukorv', 'Falun'),
187 (309, 'Digital Documentation', 'Hammer', 'Copenhagen'),
188 (310, 'Digital Documentation', 'Axe', 'Colonia'),
189 (311, 'Digital Documentation', 'Chain Saw', 'Valla');
190
191/* Sätter in värden i artifact */
192INSERT INTO artifact (artifact_id, a_description, person_id, shelf_id, dig_id)
193 VALUES ('00001', 'A small rauk from Fårö', '860523-1314', '10B', 302),
194 ('00002', 'Old rocket ship from Mars', '800523-7815', '69B', 300),
195 ('00003', 'Koenigsegg', '860523-5465', '124B', 305),
196 ('00004', 'Koenigsegg', '860523-5465', '288D', 303),
197 ('00005', 'Emerald stone', '860523-1314', '10C', 300),
198 ('00006', 'Gold coin', '830625-2519', '16B', 304),
199 ('00007', 'A faded flower', '821223-8921', '24C', 305),
200 ('00008', 'Palm leaf', '830625-2519', '5A', 306),
201 ('00009', 'Tarzans hair', '830625-2519', '5B', 306),
202 ('00010', 'T-Rex', '821223-8921', '8A', 307),
203 ('00011', 'Stegosaurus', '821223-8921', '8B', 307),
204 ('00012', 'Wasa crispbread', '800523-7815', '1A', 308),
205 ('00013', 'Cigarette butt', '860523-1314', '10A', 300),
206 ('00014', 'T-Rex favorite toy', '821223-8921', '9A', 307),
207 ('00015', 'Viking sword', '800523-7815', '69C', 301),
208 ('00016', 'Runestone', '800523-7815', '70A', 301),
209 ('00017', 'Slot machine', '830625-2519', '18B', 307),
210 ('00018', 'Shark necklace', '830625-2519', '6A', 306),
211 ('00019', 'Ancient Tuborg', '821223-8921', '11A', 309),
212 ('00020', 'Wienerbrö', '821223-8921', '11B', 309),
213 ('00021', 'Mads Mikkelsen', '821223-8921', '11C', 309),
214 ('00022', 'A crashed SAS-plane', '821223-8921', '12B', 309),
215 ('00023', 'Lord Bendtner', '821223-8921', '12A', 309),
216 ('00024', 'Yung Nyllas bike', '860523-1314', '21C', 310),
217 ('00025', 'An old sauna', '860523-1314', '21B', 310),
218 ('00026', 'A key', '860523-1314', '21A', 310),
219 ('00027', 'A book about ancient Colonia', '860523-1314', '25A', 310),
220 ('00028', 'Diamond', '830625-2519', '7A', 311),
221 ('00029', 'A dollar bill', '830625-2519', '7B', 311),
222 ('00030', 'A coffee cup', '830625-2519', '7C', 311);
223
224INSERT INTO journal (journal_id, author_name, publisher, person_id)
225 VALUES (13, 'oskar', 'bengan', '840524-1214');
226
227INSERT INTO book (book_id, book_name, author_name, publisher, barcode, loaner)
228 VALUES (1337, 'How to be vegan', 'Mange the munk', 'Vegan Publisher', 478194672, '840524-1214'),
229 (1338, 'How to be gey', 'Hanký the pank', 'Christian Publisher', 675487214, '681011-9965');
230
231INSERT INTO slide (slide_id, s_description, dig_number, seq_number, person_id)
232 VALUES ('309-01', 'In the deep concrete jungle of norreport we found an ancient can of the world famous Tuborg Grön. It has a few scratches but is still in good condition', 309, 01, '821223-8921'),
233 ('309-02', 'After two months of hard work I found queen Margrethe the seconds old Winerbrö, it still has sprinkles of her favourite topping, gold.', 309, 02, '821223-8921'),
234 ('309-03', 'On an crispy December morning we found a drunk Mads Mikkelsen in a ditch near Ströget, he couldnt explain how he got there but he was in a good mood.', 309, 03, '821223-8921'),
235 ('309-04', 'In the third month of our journey we found a boeing 747 from SAS called "The Viking" in the beautiful inlet of Öresund', 309, 04, '821223-8921');
236
237-----------------------------------create view-------------------------------------------------
238
239/* Vy över museets anställda. */
240GO
241CREATE VIEW Employees AS
242 SELECT P.p_firstname AS 'Firstname', P.p_lastname AS 'Lastname', P.p_role AS 'Role'
243 FROM person AS P
244 WHERE P.p_role != 'Student'
245GO
246
247/* Vy över museets studenter. */
248GO
249CREATE VIEW Students AS
250 SELECT P.p_firstname AS 'Firstname', P.p_lastname AS 'Lastname', P.p_role AS 'Role'
251 FROM person AS P
252 WHERE P.p_role = 'Student'
253GO
254
255/* Vy över artefakter i museet. */
256GO
257CREATE VIEW Artifacts_Report AS
258 SELECT A.artifact_id AS 'ID',
259 A.a_description AS 'Description',
260 P.p_lastname + ', ' + P.p_firstname AS 'Found By',
261 D.d_site AS 'Dig Site',
262 L.shelf_id AS 'Shelf Location'
263 FROM artifact AS A
264 INNER JOIN person AS P
265 ON P.person_id = A.person_id
266 INNER JOIN lab_shelf AS L
267 ON L.shelf_id = A.shelf_id
268 INNER JOIN dig AS D
269 ON D.dig_id = A.dig_id
270GO
271
272/* Här räknas alla artefakter och grupperas ihop om de hittats på samma ställe */
273GO
274CREATE VIEW Artifacts_Total AS
275 SELECT D.d_site AS 'Dig Site',
276 COUNT(D.dig_id) as 'Amount of Artifacts'
277 FROM dig AS D
278 INNER JOIN artifact AS A
279 ON A.dig_id = D.dig_id
280 GROUP BY D.d_site;
281GO
282
283GO
284CREATE VIEW Book_Loans AS
285 SELECT B.book_name AS 'Book'
286 FROM book AS B
287GO
288
289-----------------------------------triggers------------------------------------------
290
291/*Bra vid boklån*/
292/*
293CREATE TRIGGER new_trigger
294 ON person
295 AFTER INSERT
296 AS BEGIN
297 UPDATE deleted_person (person_id, p_firstname, p_lastname, p_faculty, p_role)
298 SELECT D.person_id, D.p_firstname, D.p_lastname, D.p_faculty, D.p_role
299 FROM deleted AS D
300END;*/