1wHrwvge

1/* Drop all tables if they exist */
2
3DROP TABLE IF EXISTS `Heroes`;
4DROP TABLE IF EXISTS `Villains`;
5DROP TABLE IF EXISTS `Planets`;
6DROP TABLE IF EXISTS `Teams`;
7DROP TABLE IF EXISTS `Abilities`;
8DROP TABLE IF EXISTS `TeamMembers`;
9DROP TABLE IF EXISTS `HeroAbilities`;
10
11/* Create Planets table */
12
13CREATE TABLE `Planets` (
14	`id` 			INT(9) 		NOT NULL,
15	`name`			VARCHAR(50)	NOT NULL,
16	`population-size`	BIGINT(20)	NOT NULL,
17	PRIMARY KEY (`id`)
18)Engine=INNODB AUTO_INCREMENT=56 DEFAULT CHARSET=latin1;
19
20/* Populate Planets table */
21
22INSERT INTO `Planets` (`name`, `population-size`)
23VALUES
24(040, 'Earth', 4000000000),
25(050, 'Asgard', 50000),
26(060, 'Wakanda', 6000000),
27(070, 'Hala', 1000000);
28
29/* Create Heroes table */
30
31CREATE TABLE `Heroes` (
32	`id` 		INT(20) 	NOT NULL,
33	`first-name`	VARCHAR(20)	NOT NULL,
34	`last-name`	VARCHAR(20)	DEFAULT NULL,
35	`species`	VARCHAR(50)	NOT NULL,
36	`age`		BIGINT(20)	DEFAULT NULL,
37	`alias`		VARCHAR(20)	DEFAULT NULL,
38	`love-interest`	VARCHAR(50)	DEFAULT NULL,
39	`home-planet`	INT(9)		NOT NULL,
40	PRIMARY KEY (`id`),
41	FOREIGN KEY (`home-planet`) REFERENCES `Planets`(`id`) ON UPDATE CASCADE ON DELETE CASCADE
42)Engine=InnoDB DEFAULT CHARSET=latin1;
43
44/* Populate Heroes table */
45
46INSERT INTO `Heroes` (`id`, `first-name`, `last-name`, `species`, `age`, `alias`, `love-interest`, `home-planet`)
47VALUES
48(1, 'Tony','Stark', 'Human', 48, 'Iron Man', 'Pepper Potts', 040),
49(2, 'Bruce','Banner', 'Human', 39, 'Hulk', 'Betty Ross', 040),
50(3, 'Thor','Odinson', 'God', 1054, NULL, 'Jane Foster', 'Asgard', 050),
51(4, 'Steve','Rogers', 'Human', 100, 'Captain America', 'Sharon Carter', 040),
52(5, 'Peter','Quill', 'Human/Celestial', 38, 'Star-Lord', 'Gamora', 040),
53(6, 'Scott','Lang', 'Human', 49, 'Ant Man', 'Hope van Dyne', 040),
54(7, 'Peter','Parker', 'Human', 16, 'Spider-Man', 'Mary-Jane Watson', 040),
55(8, 'Stephen','Strange', 'Human', 47, 'Doctor Strange', 'Clea', 040),
56(9, ’T\’Challa', NULL, 'Human', 40, 'Black Panther', 'Ororo Munroe', 060),
57(10, 'Carol','Danvers', 'Human/Kree', 40, 'Captain Marvel', NULL, 070);
58
59
60/* Create Villains table */
61
62CREATE TABLE `Villains` (
63	`id` 		INT(9) 		NOT NULL,
64	`first-name`	VARCHAR(20),
65	`last-name`	VARCHAR(20)	DEFAULT NULL,
66	`species`	VARCHAR(50)	NOT NULL,
67	'age'		BIGINT(20)	DEFAULT NULL,
68	'alias'		VARCHAR(20)	DEFAULT NULL,
69	'enemy'		INT(9)		NOT NULL,
70	PRIMARY KEY (`id`),
71	FOREIGN KEY (`enemy`) REFERENCES `Heroes`(`id`) ON UPDATE CASCADE ON DELETE CASCADE
72)Engine=INNODB AUTO_INCREMENT=52 DEFAULT CHARSET=latin1;
73
74/* Populate Villains table */
75
76INSERT INTO `Villains` (`id`, `first-name`, `l-name`, `species`, `age`, `alias`, 'enemy')
77VALUES
78
79(01, 'Obadiah','Stane', 'Human', 60, 'Iron Monger', 1),
80(02, 'Emil', 'Blonsky', 'Human', 47, 'Abomination', 2),
81(03, 'Loki','Laufeyson', 'God', 1053, NULL, 3),
82(04, 'Bucky','Barnes', 'Human', 101, 'Winter Soldier', 4),
83(05, 'Ego',NULL, 'Celestial', 1000000, NULL, 5),
84(06, 'Darren', 'Cross', 'Human', 40, 'Yellowjacket', 6),
85(07, 'Norman', 'Osborn', 'Human', 45, 'Green Goblin', 7),
86(08, 'Dormammu', NULL, 'Faltine', NULL, NULL, 8),
87(09, 'Erik', 'Kilmonger', 'Human', 32, NULL, 9),
88(010, 'Minn-Erva', NULL, 'Kree', NULL, 'Doctor Minerva', 10);
89
90/* Create Teams table */
91
92CREATE TABLE `Teams` (
93	`id` 			INT(9) 		NOT NULL,
94	`name`			VARCHAR(50)	NOT NULL,
95	`size`			BIGINT(20)	NOT NULL,
96	PRIMARY KEY (`id`)
97)Engine=INNODB AUTO_INCREMENT=56 DEFAULT CHARSET=latin1;
98
99/* Populate Teams table */
100
101INSERT INTO `Teams` (`id`, `name`, `size`)
102VALUES
103(800, 'Avengers', 20),
104(900, 'Guardians of the Galaxy', 5);
105
106/* Create Abilities table */
107
108CREATE TABLE `Abilities ` (
109	`id` 			INT(9) 		NOT NULL,
110	`name`			VARCHAR(50)	NOT NULL,
111	PRIMARY KEY (`id`)
112)Engine=INNODB AUTO_INCREMENT=56 DEFAULT CHARSET=latin1;
113
114/* Populate Abilities table */
115
116INSERT INTO `Abilities` (`id`, `name`)
117VALUES
118(0005, 'Superhuman Strength'),
119(0006, 'Superhuman Endurance'),
120(0007, 'Precognition'),
121(0008, 'Flight');
122
123/* Create TeamMembers table */
124
125CREATE TABLE `TeamMembers` (
126	`team_id` 	INT(9) 		NOT NULL,
127	`hero_id` 	INT(9) 		NOT NULL,
128	PRIMARY KEY (`team_id`, `hero_id`),
129	FOREIGN KEY (`team_id`) REFERENCES `Teams`(`id`) ON UPDATE CASCADE ON DELETE CASCADE,
130	FOREIGN KEY (`hero_id`) REFERENCES `Heroes`(`id`) ON UPDATE CASCADE ON DELETE CASCADE
131)Engine=INNODB DEFAULT CHARSET=latin1;
132
133/* Populate TeamMembers table */
134
135INSERT INTO `TeamMembers` (`team_id`, `hero_id`)
136VALUES
137(800,1),
138(800,2),
139(800,3),
140(800,4),
141(900,5);
142
143CREATE TABLE `AbilityMembers` (
144	`ability_id` 	INT(9) 		NOT NULL,
145	`hero_id` 	INT(9) 		NOT NULL,
146	PRIMARY KEY (`ability_id`, `hero_id`),
147	FOREIGN KEY (`ability_id`) REFERENCES `Abilities`(`id`) ON UPDATE CASCADE ON DELETE CASCADE,
148	FOREIGN KEY (`hero_id`) REFERENCES `Heroes`(`id`) ON UPDATE CASCADE ON DELETE CASCADE
149)Engine=INNODB DEFAULT CHARSET=latin1;
150
151/* Populate Abilities table */
152
153INSERT INTO `AbilityMembers` (`team_id`, `hero_id`)
154VALUES
155(0005,10),
156(0008,10),
157(0005,3),
158(0005,4);