· 7 years ago · Dec 10, 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
238INSERT INTO slide (slide_id, s_description, dig_number, seq_number, person_id)
239 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'),
240 ('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'),
241 ('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'),
242 ('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');
243
244-----------------------------------create view-------------------------------------------------
245
246/* Vy över museets anställda. */
247GO
248CREATE VIEW Employees AS
249 SELECT P.p_firstname AS 'Firstname', P.p_lastname AS 'Lastname', P.p_role AS 'Role'
250 FROM person AS P
251 WHERE P.p_role != 'Student'
252GO
253
254/* Vy över museets studenter. */
255GO
256CREATE VIEW Students AS
257 SELECT P.p_firstname AS 'Firstname', P.p_lastname AS 'Lastname', P.p_role AS 'Role'
258 FROM person AS P
259 WHERE P.p_role = 'Student'
260GO
261
262/* Vy över artefakter i museet. */
263GO
264CREATE VIEW Artifacts_Report AS
265 SELECT A.artifact_id AS 'ID', A.a_description AS 'Description', P.p_lastname + ', ' + P.p_firstname AS 'Found By', D.d_site AS 'Dig Site', L.shelf_id AS 'Shelf Location'
266 FROM artifact AS A
267 INNER JOIN person AS P
268 ON P.person_id = A.person_id
269 INNER JOIN lab_shelf AS L
270 ON L.shelf_id = A.shelf_id
271 INNER JOIN dig AS D
272 ON D.dig_id = A.dig_id
273GO
274
275/* Här räknas alla artefakter och grupperas ihop om de hittats på samma ställe */
276GO
277CREATE VIEW Artifacts_Total AS
278 SELECT D.d_site AS 'Dig Site',
279 COUNT(D.dig_id) as 'Amount of Artifacts'
280 FROM dig AS D
281 INNER JOIN artifact AS A
282 ON A.dig_id = D.dig_id
283 GROUP BY D.d_site;
284GO
285
286GO
287CREATE VIEW Book_Loans AS
288SELECT B.book_name AS 'Book'
289FROM book AS B
290GO
291
292-----------------------------------triggers------------------------------------------
293
294CREATE TRIGGER new_trigger
295 ON person
296 AFTER DELETE
297 AS BEGIN
298 INSERT INTO deleted_person (person_id, p_firstname, p_lastname, p_faculty, p_role)
299 SELECT D.person_id, D.p_firstname, D.p_lastname, D.p_faculty, D.p_role
300 FROM deleted AS D
301END;