· 8 years ago · Dec 04, 2017, 03:18 PM
1CREATE TABLE IF NOT EXISTS Heroes (
2 hero_id INT NOT NULL,
3 hero_name VARCHAR(20) NOT NULL,
4 villain_id INT,
5 team_id INT,
6 hq_id INT,
7 battle_id INT,
8 power_id INT,
9 PRIMARY KEY (hero_id)
10);
11
12CREATE TABLE IF NOT EXISTS Villains (
13 villain_id INT NOT NULL,
14 villain_name VARCHAR(20) NOT NULL,
15 team_id INT,
16 hq_id INT,
17 battle_id INT,
18 power_id INT,
19 PRIMARY KEY (villain_id)
20);
21
22CREATE TABLE IF NOT EXISTS Teams (
23 team_id INT NOT NULL,
24 team_name VARCHAR(35) NOT NULL,
25 allignment VARCHAR(10),
26 hq_id INT,
27 battle_id INT,
28 no_of_members INT,
29 PRIMARY KEY (team_id)
30);
31
32CREATE TABLE IF NOT EXISTS HQ (
33 hq_id INT NOT NULL,
34 hq_name VARCHAR(45) NOT NULL,
35 location VARCHAR(255),
36 PRIMARY KEY (hq_id)
37);
38
39CREATE TABLE IF NOT EXISTS Powers (
40 power_id INT NOT NULL,
41 power_name VARCHAR(50) NOT NULL,
42 description VARCHAR(255),
43 is_fatal VARCHAR(20),
44 rating INT,
45 PRIMARY KEY (power_id)
46);
47
48CREATE TABLE IF NOT EXISTS hasPowers (
49 hero_id INT NOT NULL,
50 power_id INT NOT NULL,
51 PRIMARY KEY (hero_id, power_id)
52);
53
54CREATE TABLE IF NOT EXISTS Battles (
55 battle_id INT NOT NULL,
56 battle_name VARCHAR(45) NOT NULL,
57 location VARCHAR(255),
58 victor VARCHAR(255),
59 PRIMARY KEY (battle_id)
60);
61
62CREATE TABLE IF NOT EXISTS battleParticipants (
63 participant_id INT NOT NULL,
64 battle_id INT NOT NULL,
65 PRIMARY KEY (participant_id, battle_id)
66);
67
68
69ALTER TABLE Heroes ADD CHECK(hero_id>0 AND hero_id<100);
70
71ALTER TABLE Villains ADD CHECK(villain_id>99 AND villain_id<1000);
72
73ALTER TABLE Teams ADD CHECK(team_id>999 AND team_id<10000);
74
75ALTER TABLE HQ ADD CHECK(hq_id>9999 AND hq_id<100000);
76
77ALTER TABLE Powers ADD CHECK(power_id>99999 AND power_id<1000000);
78
79ALTER TABLE Battles ADD CHECK(battle_id>999999 AND battle_id<10000000);
80
81ALTER TABLE Powers ADD CHECK(is_fatal IN ('fatal', 'damagable', 'non fatal', 'healing'));
82
83ALTER TABLE Powers ADD CHECK(rating>=0 AND rating <=10);
84
85
86INSERT INTO Powers VALUES(100001, 'Superstrength', 'The ability to lift 10x as much as any human being.', 'damagable', 9);
87INSERT INTO Powers VALUES(100002, 'Superspeed', 'The ability to run as fast as the speed of sound.', 'non fatal', 7);
88INSERT INTO Powers VALUES(100003, 'Telekineses', 'The ability to control or manipulate objects with ones mind', 'non fatal', 9);
89INSERT INTO Powers VALUES(100004, 'Atlantean Biological Adaption', 'The ability to communicate and summon an army of sea creatures', 'damagable', 8);
90INSERT INTO Powers VALUES(100005, 'X-Ray Vision', 'The ability to see through objects', 'non fatal', 5);
91INSERT INTO Powers VALUES(100006, 'Lazer Vision', 'The ability to fire lazers from ones eyes', 'fatal', 6);
92INSERT INTO Powers VALUES(100007, 'Enhanced inteligence',
93 'The ability to have increased probability reasoning and make spilt second decisions for a greater benefit in the fight', 'non fatal', 8);
94INSERT INTO Powers VALUES(100008, 'Martial Arts', 'Increased acrobatic skill in regards to fighting', 'damagable', 4);
95INSERT INTO Powers VALUES(100009, 'Stealth', 'The ability to avoid confrontation by seaking past ones enemy', 'non fatal', 3);
96INSERT INTO Powers VALUES(100010, 'Adamantiam Claws', 'The ability to summon sword like steel claws from ones knuckles', 'fatal', 8);
97INSERT INTO Powers VALUES(100011, 'Magnetic Field Manipulation',
98 'The ability to control and manipulate objects made of metal to be shaped into anything at ones will', 'damagable', 9);
99INSERT INTO Powers VALUES(100012, 'Rapid Healing', 'The ability to rapidly heal any wounds or amupations', 'healing', 8);
100INSERT INTO Powers VALUES(100013, 'Bestial Senses', 'The ability to have extra heightened senses', 'non fatal', 5);
101INSERT INTO Powers VALUES(100014, 'Steel Exoskeleton', 'Metal Suit designed for use in combat as well as protection from damage', 'damagable', 9);
102INSERT INTO Powers VALUES(100015, 'Power of the Gods', 'Possession of the abilities of a God. Super strength, increased healing and godly gifts.', 'damagable', 10);
103INSERT INTO Powers VALUES(100016, 'Psychotic Manipulation', 'The ability to manipulate people to your will using psychotic gasses', 'fatal', 8);
104
105
106INSERT INTO hasPowers VALUES(200002, 100001);
107INSERT INTO hasPowers VALUES(200004, 100001);
108INSERT INTO hasPowers VALUES(200008, 100001);
109INSERT INTO hasPowers VALUES(200010, 100001);
110INSERT INTO hasPowers VALUES(200011, 100001);
111INSERT INTO hasPowers VALUES(200005, 100002);
112INSERT INTO hasPowers VALUES(200007, 100003);
113INSERT INTO hasPowers VALUES(200003, 100004);
114INSERT INTO hasPowers VALUES(200002, 100005);
115INSERT INTO hasPowers VALUES(200002, 100006);
116INSERT INTO hasPowers VALUES(200007, 100007);
117INSERT INTO hasPowers VALUES(200001, 100007);
118INSERT INTO hasPowers VALUES(200001, 100008);
119INSERT INTO hasPowers VALUES(200012, 100008);
120INSERT INTO hasPowers VALUES(200001, 100009);
121INSERT INTO hasPowers VALUES(200006, 100010);
122INSERT INTO hasPowers VALUES(200006, 100012);
123INSERT INTO hasPowers VALUES(200012, 100012);
124INSERT INTO hasPowers VALUES(200005, 100012);
125INSERT INTO hasPowers VALUES(200006, 100013);
126INSERT INTO hasPowers VALUES(200009, 100014);
127INSERT INTO hasPowers VALUES(200010, 100015);
128INSERT INTO hasPowers VALUES(300003, 100001);
129INSERT INTO hasPowers VALUES(300006, 100001);
130INSERT INTO hasPowers VALUES(300005, 100001);
131INSERT INTO hasPowers VALUES(300009, 100001);
132INSERT INTO hasPowers VALUES(300008, 100001);
133INSERT INTO hasPowers VALUES(300011, 100002);
134INSERT INTO hasPowers VALUES(300008, 100006);
135INSERT INTO hasPowers VALUES(300010, 100007);
136INSERT INTO hasPowers VALUES(300005, 100008);
137INSERT INTO hasPowers VALUES(300001, 100011);
138INSERT INTO hasPowers VALUES(300011, 100012);
139INSERT INTO hasPowers VALUES(300002, 100013);
140INSERT INTO hasPowers VALUES(300008, 100013);
141INSERT INTO hasPowers VALUES(300009, 100015);
142INSERT INTO hasPowers VALUES(300004, 100015);
143INSERT INTO hasPowers VALUES(300007, 100016);
144INSERT INTO hasPowers VALUES(300004, 100016);
145
146
147INSERT INTO Battles VALUES(1000001, 'Battle of New York', 'New York City, New York', 'The Avengers');
148INSERT INTO Battles VALUES(1000002, 'Batman vs Superman', 'Metropolis City', 'Fight Conceded');
149INSERT INTO Battles VALUES(1000003, 'Arkham Asylum', 'Arkham Asylum, Arkham Island', 'Batman');
150INSERT INTO Battles VALUES(1000004, 'God of War Battle', 'Nazi Armanent Camp, France', 'Wonder Woman');
151INSERT INTO Battles VALUES(1000005, 'Doomsday', 'Metropolis', 'Doomsday');
152INSERT INTO Battles VALUES(1000006, 'Final Stand', 'Alcatraz Island, San Francisco', 'The X-Men');
153INSERT INTO Battles VALUES(1000007, 'Battle for the Statue of Liberty', 'Ellis Island, New York', 'The X-Men');
154INSERT INTO Battles VALUES(1000008, 'Civil War', 'Various Locations', 'Captain America');
155INSERT INTO Battles VALUES(1000009, 'Battle of Sokovia', 'Sokovia', 'Avengers with serious loss of life to local residents');
156INSERT INTO Battles VALUES(1000010, 'Battle for S.H.I.E.L.D', 'S.H.I.E.L.D Hellicarrier', 'Captain America with destruction of S.H.I.E.L.D by Hydra');
157INSERT INTO Battles VALUES(1000011, 'Aquaman vs Black Manta', NULL, 'Aquaman');
158INSERT INTO Battles VALUES(1000012, 'Deadpool vs Ajax', NULL, 'Deadpool');
159
160
161INSERT INTO battleParticipants VALUES(4000004, 1000001);
162INSERT INTO battleParticipants VALUES(3000004, 1000001);
163INSERT INTO battleParticipants VALUES(2000008, 1000001);
164INSERT INTO battleParticipants VALUES(2000009, 1000001);
165INSERT INTO battleParticipants VALUES(2000010, 1000001);
166INSERT INTO battleParticipants VALUES(2000011, 1000001);
167INSERT INTO battleParticipants VALUES(2000001, 1000002);
168INSERT INTO battleParticipants VALUES(2000002, 1000002);
169INSERT INTO battleParticipants VALUES(2000001, 1000003);
170INSERT INTO battleParticipants VALUES(3000007, 1000003);
171INSERT INTO battleParticipants VALUES(2000004, 1000004);
172INSERT INTO battleParticipants VALUES(3000009, 1000004);
173INSERT INTO battleParticipants VALUES(2000002, 1000005);
174INSERT INTO battleParticipants VALUES(3000006, 1000005);
175INSERT INTO battleParticipants VALUES(3000002, 1000006);
176INSERT INTO battleParticipants VALUES(3000001, 1000006);
177INSERT INTO battleParticipants VALUES(2000007, 1000006);
178INSERT INTO battleParticipants VALUES(2000006, 1000006);
179INSERT INTO battleParticipants VALUES(3000002, 1000007);
180INSERT INTO battleParticipants VALUES(3000001, 1000007);
181INSERT INTO battleParticipants VALUES(2000007, 1000007);
182INSERT INTO battleParticipants VALUES(2000006, 1000007);
183INSERT INTO battleParticipants VALUES(2000009, 1000008);
184INSERT INTO battleParticipants VALUES(2000008, 1000009);
185INSERT INTO battleParticipants VALUES(2000008, 1000010);
186INSERT INTO battleParticipants VALUES(3000005, 1000010);
187INSERT INTO battleParticipants VALUES(2000003, 1000011);
188INSERT INTO battleParticipants VALUES(000008, 1000011);
189INSERT INTO battleParticipants VALUES(3000003, 1000012);
190INSERT INTO battleParticipants VALUES(2000012, 1000012);
191
192
193INSERT INTO HQ VALUES(10001, 'Batcave', 'Wayne Manor, Gotham City');
194INSERT INTO HQ VALUES(10002, 'Fortress of Solitude', 'Antarctica');
195INSERT INTO HQ VALUES(10003, 'Atlantis', 'Unknown');
196INSERT INTO HQ VALUES(10004, 'Themyscira', 'Aegean Sea');
197INSERT INTO HQ VALUES(10005, 'Xaviers School for Gifted Youngsters', 'Salem Center, New York');
198INSERT INTO HQ VALUES(10006, 'War Room X', 'Savage Land, Antarctica');
199INSERT INTO HQ VALUES(10007, 'Avengers Tower', 'New York City, New York');
200INSERT INTO HQ VALUES(10008, 'Asgard', 'Nine Realms');
201INSERT INTO HQ VALUES(10009, 'Joker Funland', 'Arkham City District, Gotham City');
202INSERT INTO HQ VALUES(10010, 'Ultronic Territories', 'Alaska');
203INSERT INTO HQ VALUES(10011, 'The Watchtower', 'In Orbit of Earth');
204
205
206INSERT INTO Teams VALUES(1001, 'Justice League', 'Good', 10001, 4000001, 0);
207INSERT INTO Teams VALUES(1002, 'X-Men', 'Good', 10005, 4000002, 0);
208INSERT INTO Teams VALUES(1003, 'Magnetos X-Men', 'Evil', NULL, 4000003, 0);
209INSERT INTO Teams VALUES(1004, 'The Avengers', 'Good', 10007, 4000004, 0);
210INSERT INTO Teams VALUES(1005, 'Hydra', 'Evil', NULL, 4000005, 0);
211INSERT INTO Teams VALUES(1006, 'Secret Society of Super-Villains', 'Evil', NULL, 4000005, 0);
212
213
214INSERT INTO Villains VALUES(101, 'Magneto', 1003, 10006, 3000001, 300001);
215INSERT INTO Villains VALUES(102, 'Sabretooth', 1003, 10006, 3000002, 300002);
216INSERT INTO Villains VALUES(103, 'Ajax', NULL, NULL, 3000003, 300003);
217INSERT INTO Villains VALUES(104, 'Loki', NULL, 10008, 3000004, 300004);
218INSERT INTO Villains VALUES(105, 'Winter Soldier', 1005, NULL, 3000005, 300005);
219INSERT INTO Villains VALUES(106, 'Doomsday', NULL, NULL, 3000006, 300006);
220INSERT INTO Villains VALUES(107, 'The Joker', NULL, 10009, 3000007, 300007);
221INSERT INTO Villains VALUES(108, 'Black Mantra', NULL, NULL, 3000008, 300008);
222INSERT INTO Villains VALUES(109, 'Ares', NULL, NULL, 3000009, 300009);
223INSERT INTO Villains VALUES(110, 'Ultron', NULL, 10011, 3000010, 300010);
224INSERT INTO Villains VALUES(111, 'Professor Zoom', 1006, NULL, 3000011, 300011);
225
226
227CREATE TRIGGER team_no_inc_hero
228 BEFORE INSERT ON Heroes
229 FOR EACH ROW
230BEGIN
231 UPDATE Teams SET no_of_members = no_of_members + 1
232 WHERE Teams.team_id = :New.team_id;
233END;
234.
235RUN;
236
237CREATE TRIGGER team_no_inc_villain
238 BEFORE INSERT ON Villains
239 FOR EACH ROW
240BEGIN
241 UPDATE Teams SET no_of_members = no_of_members + 1
242 WHERE Teams.team_id = :New.team_id;
243END;
244.
245RUN;
246
247
248INSERT INTO Heroes VALUES(1, 'Batman', 107, 1001, 10001, 2000001, 200001);
249INSERT INTO Heroes VALUES(2, 'Superman', 106, 1001, 10002, 2000002, 200002);
250INSERT INTO Heroes VALUES(3, 'Aquaman', 108, 1001, 10003, 2000003, 200003);
251INSERT INTO Heroes VALUES(4, 'Wonder Woman', 109, 1001, 10004, 2000004, 200004);
252INSERT INTO Heroes VALUES(5, 'Flash', 111, 1001, 10011, 2000005, 200005);
253INSERT INTO Heroes VALUES(6, 'Wolverine', 102, 1002, 10005, 2000006, 200006);
254INSERT INTO Heroes VALUES(7, 'Charles Xavier', 101, 1002, 10005, 2000007, 200007);
255INSERT INTO Heroes VALUES(8, 'Captain America', 105, 1004, 10007, 2000008, 200008);
256INSERT INTO Heroes VALUES(9, 'Iron Man', 110, 1004, 10007, 2000009, 200009);
257INSERT INTO Heroes VALUES(10, 'Hulk', 104, 1004, 10007, 2000010, 200010);
258INSERT INTO Heroes VALUES(11, 'Thor', 104, 1004, 10008, 2000011, 200011);
259INSERT INTO Heroes VALUES(12, 'Deadpool', 103, NULL, NULL, 2000012, 200012);
260
261
262ALTER TABLE Heroes ADD FOREIGN KEY(villain_id) REFERENCES Villains(villain_id);
263ALTER TABLE Heroes ADD FOREIGN KEY(team_id) REFERENCES Teams(team_id);
264ALTER TABLE Heroes ADD FOREIGN KEY(hq_id) REFERENCES HQ(hq_id);
265
266ALTER TABLE Villains ADD FOREIGN KEY(team_id) REFERENCES Teams(team_id);
267ALTER TABLE Villains ADD FOREIGN KEY(hq_id) REFERENCES HQ(hq_id);
268
269ALTER TABLE Teams ADD FOREIGN KEY(hq_id) REFERENCES HQ(hq_id);
270
271CREATE VIEW IF NOT EXISTS Rivalry( h_name, v_name)
272AS SELECT Heroes.hero_name, Villains.villain_name
273FROM Heroes, Villains
274 WHERE Heroes.villain_id = Villains.villain_id;
275
276CREATE VIEW IF NOT EXISTS HeroesTeam( h_name, t_name)
277AS SELECT Heroes.hero_name, Teams.team_name
278FROM Heroes, Teams
279 WHERE Heroes.team_id = Teams.team_id;
280
281CREATE VIEW IF NOT EXISTS VillainsTeam( v_name, t_name)
282AS SELECT Villains.villain_name, Teams.team_name
283FROM Villains, Teams
284 WHERE Villains.team_id = Teams.team_id;
285
286
287CREATE VIEW IF NOT EXISTS HeroesBattle( h_name, b_name, b_win)
288AS SELECT Heroes.hero_name, Battles.battle_name, Battles.victor
289FROM Heroes, Battles, battleParticipants
290 WHERE Heroes.battle_id = battleParticipants.participant_id
291 AND battleParticipants.battle_id = Battles.battle_id;
292
293CREATE VIEW IF NOT EXISTS VillainsBattle( h_name, b_name, b_win)
294AS SELECT Villains.villain_name, Battles.battle_name, Battles.victor
295FROM Villains, Battles, battleParticipants
296 WHERE Villains.battle_id = battleParticipants.participant_id
297 AND battleParticipants.battle_id = Battles.battle_id;
298
299CREATE VIEW IF NOT EXISTS TeamsBattle( h_name, b_name, b_win)
300AS SELECT Teams.team_name, Battles.battle_name, Battles.victor
301FROM Teams, Battles, battleParticipants
302 WHERE Teams.battle_id = battleParticipants.participant_id
303 AND battleParticipants.battle_id = Battles.battle_id;
304
305CREATE VIEW IF NOT EXISTS HeroPowers( h_name, p_name, p_des, p_fat)
306AS SELECT Heroes.hero_name, Powers.power_name, Powers.description, Powers.is_fatal
307FROM Heroes, Powers, hasPowers
308 WHERE Heroes.power_id = hasPowers.hero_id
309 AND hasPowers.power_id = Powers.power_id;
310
311CREATE VIEW IF NOT EXISTS VillainPowers( v_name, p_name, p_des, p_fat)
312AS SELECT Villains.villain_name, Powers.power_name, Powers.description, Powers.is_fatal
313FROM Villains, Powers, hasPowers
314 WHERE Villains.power_id = hasPowers.hero_id
315 AND hasPowers.power_id = Powers.power_id;