· 7 years ago · Nov 20, 2018, 12:54 PM
1DROP DATABASE a16filol;
2CREATE DATABASE a16filol;
3USE a16filol;
4
5-- Koder
6CREATE TABLE DANGER_LEVEL (
7 degree INT NOT NULL,
8 name VARCHAR(30) UNIQUE NOT NULL,
9 PRIMARY KEY(degree)
10) ENGINE=INNODB;
11
12-- Alien
13CREATE TABLE ALIEN (
14 id CHAR(25) NOT NULL UNIQUE,
15 danger_level INT DEFAULT 4,
16 race CHAR(50),
17 PRIMARY KEY (id),
18 FOREIGN KEY (danger_level) REFERENCES DANGER_LEVEL(degree)
19) ENGINE=INNODB;
20
21CREATE TABLE ALIEN_REG (
22 ssn CHAR(12) NOT NULL UNIQUE,
23 a_id CHAR(25) NOT NULL,
24 name VARCHAR(25),
25 planet VARCHAR(25),
26 PRIMARY KEY(ssn),
27 FOREIGN KEY (a_id) REFERENCES ALIEN(id) ON DELETE CASCADE
28) ENGINE=INNODB;
29
30CREATE TABLE ALIEN_OREG (
31 id CHAR(14) NOT NULL UNIQUE,
32 a_id CHAR(25) NOT NULL,
33 name CHAR(25),
34 PRIMARY KEY(id),
35 FOREIGN KEY (a_id) REFERENCES ALIEN(id) ON DELETE CASCADE
36) ENGINE=INNODB;
37
38-- Recursive relation on Alien
39CREATE TABLE ALIEN_REALATIONSHIPS (
40 aone_id CHAR(25) NOT NULL,
41 atwo_id CHAR(25) NOT NULL,
42 PRIMARY KEY(aone_id, atwo_id),
43 FOREIGN KEY (aone_id) REFERENCES ALIEN(id) ON DELETE CASCADE,
44 FOREIGN KEY (atwo_id) REFERENCES ALIEN(id) ON DELETE CASCADE
45) ENGINE=INNODB;
46
47-- Multivalue attribute for alien characteristics
48CREATE TABLE ALIEN_CHARACTERISTICS (
49 id INT NOT NULL AUTO_INCREMENT,
50 a_id CHAR(25) NOT NULL,
51 characteristic CHAR(50),
52 PRIMARY KEY(id),
53 FOREIGN KEY (a_id) REFERENCES ALIEN(id) ON DELETE CASCADE
54) ENGINE=INNODB;
55
56
57-- Ship
58
59CREATE TABLE SHIP (
60 id INT NOT NULL AUTO_INCREMENT,
61 man_planet CHAR(25) NOT NULL,
62 seats INT DEFAULT 1,
63 PRIMARY KEY(id)
64) ENGINE=INNODB;
65
66-- Multivalue attribute for ship charecteristics
67CREATE TABLE SHIP_CHARACTERISTICS (
68 id INT NOT NULL AUTO_INCREMENT,
69 s_id INT NOT NULL,
70 characteristic CHAR(50),
71 PRIMARY KEY (id),
72 FOREIGN KEY (s_id) REFERENCES SHIP(id) ON DELETE CASCADE
73) ENGINE=INNODB;
74
75
76-- WEAPON
77CREATE TABLE WEAPON_HARMLESS(
78 id INT NOT NULL AUTO_INCREMENT,
79 danger_level INT DEFAULT 1,
80 weapon_type CHAR(25),
81 man_planet char(25),
82 purchase_location VARCHAR(25) NOT NULL,
83 PRIMARY KEY (id),
84 FOREIGN KEY (danger_level) REFERENCES DANGER_LEVEL(degree)
85) ENGINE=INNODB;
86
87CREATE TABLE WEAPON_DEANGEROUS (
88 id INT NOT NULL AUTO_INCREMENT,
89 danger_level INT DEFAULT 6,
90 weapon_type VARCHAR(25),
91 man_planet VARCHAR(25),
92 purchase_location VARCHAR(25) NOT NULL,
93 PRIMARY KEY (id),
94 FOREIGN KEY (danger_level) REFERENCES DANGER_LEVEL(degree)
95) ENGINE=INNODB;
96
97-- Multivalue attribute for locations were we can buy weapons
98/*
99CREATE TABLE WEAPON_PURCHASE_LOCATIONS (
100 location VARCHAR(10) NOT NULL,
101 w_id INT NOT NULL,
102 PRIMARY KEY (id, w_id)
103) ENGINE=INNODB;
104*/
105-- PIVOT TABLES
106-- Pivot table for many-many between Alien and ship
107CREATE TABLE ALIEN_SHIP (
108 a_id CHAR(25) NOT NULL,
109 s_id INT NOT NULL,
110 PRIMARY KEY(a_id, s_id),
111 FOREIGN KEY (a_id) REFERENCES ALIEN(id) ON DELETE CASCADE,
112 FOREIGN KEY (s_id) REFERENCES SHIP(id) ON DELETE CASCADE
113) ENGINE=INNODB;
114
115-- Pivot table for many-many between ship and weapon
116CREATE TABLE SHIP_WEAPON_DEANGEROUS (
117 w_id INT NOT NULL,
118 s_id INT NOT NULL,
119 PRIMARY KEY(w_id, s_id),
120 FOREIGN KEY (w_id) REFERENCES WEAPON_DEANGEROUS(id),
121 FOREIGN KEY (s_id) REFERENCES SHIP(id) ON DELETE CASCADE
122) ENGINE=INNODB;
123
124CREATE TABLE SHIP_WEAPON_HARMLESS (
125 w_id INT NOT NULL,
126 s_id INT NOT NULL,
127 PRIMARY KEY(w_id, s_id),
128 FOREIGN KEY (w_id) REFERENCES WEAPON_HARMLESS(id),
129 FOREIGN KEY (s_id) REFERENCES SHIP(id) ON DELETE CASCADE
130) ENGINE=INNODB;
131
132-- Pivot table for alien and weapon
133CREATE TABLE ALIEN_WEAPON_DEANGEROUS (
134 a_id CHAR(25) NOT NULL,
135 w_id INT NOT NULL,
136 PRIMARY KEY(a_id, w_id),
137 FOREIGN KEY (a_id) REFERENCES ALIEN(id) ON DELETE CASCADE,
138 FOREIGN KEY (w_id) REFERENCES WEAPON_DEANGEROUS(id)
139) ENGINE=INNODB;
140
141CREATE TABLE ALIEN_WEAPON_HARMLESS (
142 a_id CHAR(25) NOT NULL,
143 w_id INT NOT NULL,
144 PRIMARY KEY(a_id, w_id),
145 FOREIGN KEY (a_id) REFERENCES ALIEN(id) ON DELETE CASCADE,
146 FOREIGN KEY (w_id) REFERENCES WEAPON_HARMLESS(id)
147) ENGINE=INNODB;
148
149-- Indexes BTREE because of INNODB
150CREATE INDEX REG_ALIEN_NAMES ON ALIEN_REG(NAME ASC) USING BTREE;
151CREATE INDEX OREG_ALIEN_NAMES ON ALIEN_OREG(NAME ASC) USING BTREE;
152CREATE INDEX ALIEN_RACES ON ALIEN(race ASC) USING BTREE;
153CREATE INDEX DANGER_LEVELS_ASC ON DANGER_LEVEL(degree ASC) USING BTREE;
154
155-- Views
156CREATE VIEW ALIEN_WEAPON_DEANGEROUS_TYPES AS
157 SELECT
158 weapon_type, a_id
159 FROM WEAPON_DEANGEROUS, ALIEN_WEAPON_DEANGEROUS
160 WHERE ALIEN_WEAPON_DEANGEROUS.w_id=WEAPON_DEANGEROUS.id AND ALIEN_WEAPON_DEANGEROUS.a_id=a_id;
161
162CREATE VIEW SHIP_WEAPON_DANGEROUS_TYPES AS
163 SELECT
164 weapon_type, s_id
165 FROM WEAPON_DEANGEROUS, SHIP_WEAPON_DEANGEROUS
166 WHERE SHIP_WEAPON_DEANGEROUS.w_id=WEAPON_DEANGEROUS.id AND SHIP_WEAPON_DEANGEROUS.s_id=s_id;
167
168CREATE VIEW TOTAL_NUM_ALIENS AS
169 SELECT COUNT(*) FROM ALIEN;
170
171
172-- INSERTS
173INSERT INTO DANGER_LEVEL VALUES (1, "Harmlös");
174INSERT INTO DANGER_LEVEL VALUES (2, "Halvt harmlös");
175INSERT INTO DANGER_LEVEL VALUES (3, "Ofarlig");
176INSERT INTO DANGER_LEVEL VALUES (4, "Neutral");
177INSERT INTO DANGER_LEVEL VALUES (5, "Svagt farlig");
178INSERT INTO DANGER_LEVEL VALUES (6, "Farlig");
179INSERT INTO DANGER_LEVEL VALUES (7, "Extremt farlig");
180INSERT INTO DANGER_LEVEL VALUES (8, "Spring för livet");
181
182-- RIGHTS
183/*
184CREATE USER 'dangerousalien'@'127.0.0.1' IDENTIFIED BY 'abc123';
185GRANT SELECT ON a16filol.ALIEN TO dangerousalien;
186CREATE VIEW DANGEROUSALIEN AS SELECT SHIP.seats FROM SHIP;
187GRANT SELECT ON a16filol.DANGEROUSALIEN TO dangerousalien;
188*/
189-- PROSEDURER
190
191CREATE TABLE LOG_ACTIVITY (
192 id INT NOT NULL AUTO_INCREMENT,
193 table_affected varchar(40),
194 activity varchar(255),
195 PRIMARY KEY (id)
196) ENGINE=INNODB;
197
198DELIMITER //
199 CREATE TRIGGER INSERTCHECK_ALIEN_DANGER_LEVEL BEFORE INSERT ON ALIEN
200 FOR EACH ROW BEGIN
201 IF(NEW.danger_level < 1 OR NEW.danger_level > 8) THEN
202 SIGNAL SQLSTATE '45000' set message_text='Danger level can only be between 1-8';
203 END IF;
204 END //
205DELIMITER ;
206
207DELIMITER //
208 CREATE PROCEDURE ALIEN_REMOVE(IN ALIEN_ID CHAR(25))
209 BEGIN
210 IF EXISTS (SELECT 1 FROM ALIEN WHERE id=ALIEN_ID) THEN
211 BEGIN
212 DELETE FROM ALIEN WHERE id=ALIEN_ID;
213 INSERT INTO LOG_ACTIVITY (table_affected, activity) VALUES ("ALIEN", CONCAT("Deleted an Alien. ID:", ALIEN_ID));
214 END;
215 ELSE BEGIN
216 SIGNAL SQLSTATE '45002' set message_text='Alien does not exist.';
217 END;
218 END IF;
219 END //
220DELIMITER ;
221
222DELIMITER //
223 CREATE TRIGGER SHIP_DELETE_LOGG_TRIGGER AFTER DELETE ON SHIP
224 FOR EACH ROW BEGIN
225 INSERT INTO LOG_ACTIVITY (table_affected, activity) VALUES ("SHIP", CONCAT("Deleted a Ship ID:", OLD.id));
226 END //
227DELIMITER ;
228
229-- Test the procedure make an alien and then remove it, Because of cascade on FK remove enties
230INSERT INTO ALIEN VALUES("1321313131", 1, "Roger");
231INSERT INTO ALIEN_REG (ssn, a_id, name, planet) VALUES ("201805050451", "1321313131", "Frank", "Pluto");
232INSERT INTO ALIEN VALUES("142146512", 6, "Ragge");
233
234-- Utility counter for alien because of the maximum amount of utilities a Alien can have
235DELIMITER //
236 CREATE PROCEDURE TOTAL_ALIEN_UTILITES(IN ALIEN_ID CHAR(25), OUT TOTAL_UTILITES INT)
237 BEGIN
238 SET TOTAL_UTILITES = 0;
239 SET TOTAL_UTILITES = (
240 SELECT COUNT(*) FROM (
241 SELECT w_id FROM ALIEN_WEAPON_DEANGEROUS WHERE ALIEN_WEAPON_DEANGEROUS.a_id=ALIEN_ID
242 UNION ALL
243 SELECT w_id FROM ALIEN_WEAPON_HARMLESS WHERE ALIEN_WEAPON_HARMLESS.a_id=ALIEN_ID
244 UNION ALL
245 SELECT s_id FROM ALIEN_SHIP WHERE ALIEN_SHIP.a_id=ALIEN_ID) as t);
246 END //
247DELIMITER ;
248
249-- Data for testing Alien utilites
250INSERT INTO WEAPON_HARMLESS(weapon_type, purchase_location) VALUES ("Vattenpistol", "Pluto");
251INSERT INTO WEAPON_DEANGEROUS(weapon_type, purchase_location) VALUES ("Gevär", "Saturnus");
252INSERT INTO WEAPON_DEANGEROUS(weapon_type, purchase_location) VALUES ("Plutevär", "Pluto");
253INSERT INTO SHIP (man_planet) VALUE ("Pluto");
254INSERT INTO SHIP (man_planet) VALUE ("Pluto");
255
256INSERT INTO ALIEN_WEAPON_DEANGEROUS (a_id, w_id) VALUES ("1321313131", 1);
257INSERT INTO ALIEN_WEAPON_HARMLESS (a_id, w_id) VALUES ("1321313131", 1);
258INSERT INTO ALIEN_WEAPON_DEANGEROUS (a_id, w_id) VALUES ("1321313131", 2);
259INSERT INTO ALIEN_SHIP (a_id, s_id) VALUES ("1321313131", 1);
260INSERT INTO ALIEN_SHIP (a_id, s_id) VALUES ("1321313131", 2);
261
262DELETE FROM SHIP WHERE id=1;
263
264CALL TOTAL_ALIEN_UTILITES("1321313131", @total_utilites);
265SELECT @total_utilites;
266
267-- Trigger for checking that a alien dose not have over 15 Utilities (Max 15)
268DELIMITER //
269 CREATE TRIGGER ALIEN_UTILITY_WEAPON_DANGEROUS_INSERT BEFORE INSERT ON ALIEN_WEAPON_DEANGEROUS
270 FOR EACH ROW BEGIN
271 CALL TOTAL_ALIEN_UTILITES(NEW.a_id, @total);
272
273 if(@total > 15) THEN
274 SIGNAL SQLSTATE '45001' set message_text='To many utilites for this Alien (Max 15)';
275 END IF;
276 END //
277DELIMITER ;
278
279DELIMITER //
280 CREATE TRIGGER ALIEN_UTILITY_WEAPON_HARMLESS_INSERT BEFORE INSERT ON ALIEN_WEAPON_HARMLESS
281 FOR EACH ROW BEGIN
282 CALL TOTAL_ALIEN_UTILITES(NEW.a_id, @total);
283
284 if(@total > 15) THEN
285 SIGNAL SQLSTATE '45001' set message_text='To many utilites for this Alien (Max 15)';
286 END IF;
287 END //
288DELIMITER ;
289
290DELIMITER //
291 CREATE TRIGGER ALIEN_UTILITY_SHIP_INSERT BEFORE INSERT ON ALIEN_SHIP
292 FOR EACH ROW BEGIN
293 CALL TOTAL_ALIEN_UTILITES(NEW.a_id, @total);
294
295 if(@total > 15) THEN
296 SIGNAL SQLSTATE '45001' set message_text='To many utilites for this Alien (Max 15)';
297 END IF;
298 END //
299DELIMITER ;
300
301INSERT INTO `a16filol`.`ALIEN_CHARACTERISTICS` (`a_id`, `characteristic`) VALUES ('1321313131', 'Svänger snabbt');
302INSERT INTO `a16filol`.`ALIEN_CHARACTERISTICS` (`a_id`, `characteristic`) VALUES ('1321313131', 'Snabb');
303
304INSERT INTO `a16filol`.`SHIP_CHARACTERISTICS` (`s_id`, `characteristic`) VALUES ('2', 'Stor');
305
306CREATE VIEW WEAPONS AS
307 SELECT *, 'DANGEROUS' as danger_type FROM WEAPON_DEANGEROUS
308 UNION
309 SELECT *, 'HARMLESS' as danger_type FROM WEAPON_HARMLESS;
310
311DELIMITER //
312 CREATE PROCEDURE DELETE_WEAPON(IN id INT, IN dtype CHAR(25))
313 BEGIN
314 IF (dtype LIKE "HARMLESS") THEN
315 DELETE FROM ALIEN_WEAPON_HARMLESS WHERE ALIEN_WEAPON_HARMLESS.w_id=id;
316 DELETE FROM WEAPON_HARMLESS WHERE WEAPON_HARMLESS.id=id;
317 ELSE
318 DELETE FROM ALIEN_WEAPON_DEANGEROUS WHERE ALIEN_WEAPON_DEANGEROUS.w_id=id;
319 DELETE FROM WEAPON_DEANGEROUS WHERE WEAPON_DEANGEROUS.id=id;
320 END IF ;
321 END //
322DELIMITER ;
323
324DELIMITER //
325 CREATE PROCEDURE UPDATE_WEAPON(IN in_id INT(11), IN in_danger_level INT, IN in_weapon_type VARCHAR(25), IN in_man_planet VARCHAR(25), IN in_purchase_location VARCHAR(25), IN dtype CHAR(25))
326 BEGIN
327 IF (dtype LIKE "HARMLESS") THEN
328 IF (in_danger_level < 5) THEN
329 UPDATE WEAPON_HARMLESS SET WEAPON_HARMLESS.man_planet=in_man_planet, WEAPON_HARMLESS.weapon_type=in_weapon_type, WEAPON_HARMLESS.purchase_location=in_purchase_location, WEAPON_HARMLESS.danger_level=in_danger_level WHERE WEAPON_HARMLESS.id=in_id;
330 ELSE
331 INSERT INTO WEAPON_DEANGEROUS (man_planet, weapon_type, purchase_location, danger_level) VALUES (in_man_planet, in_weapon_type, in_purchase_location, in_danger_level);
332 CALL DELETE_WEAPON(in_id, dtype);
333 end if ;
334 ELSE
335 IF (in_danger_level > 4) THEN
336 UPDATE WEAPON_DEANGEROUS SET WEAPON_DEANGEROUS.man_planet=in_man_planet, WEAPON_DEANGEROUS.weapon_type=in_weapon_type, WEAPON_DEANGEROUS.purchase_location=in_purchase_location, WEAPON_DEANGEROUS.danger_level=in_danger_level WHERE WEAPON_DEANGEROUS.id=in_id;
337 ELSE
338 INSERT INTO WEAPON_HARMLESS (man_planet, weapon_type, purchase_location, danger_level) VALUES (in_man_planet, in_weapon_type, in_purchase_location, in_danger_level);
339 CALL DELETE_WEAPON(in_id, dtype);
340 END IF ;
341 END IF ;
342 END //
343DELIMITER ;