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