pizHacqM

1DROP DATABASE IF EXISTS ChicagoVampires;
2CREATE DATABASE ChicagoVampires;
3USE ChicagoVampires;
4
5DROP TABLE IF EXISTS Vampires;
6CREATE TABLE Vampires
7(
8    vampire_id INT NOT NULL AUTO_INCREMENT,
9    vampire_name VARCHAR(15) NOT NULL,
10    generation INT NOT NULL,
11    clan_id INT NOT NULL,
12    sire_id INT,
13    PRIMARY KEY (vampire_id)
14);
15
16DROP TABLE IF EXISTS Clans;
17CREATE TABLE Clans
18(
19  clan_id INT NOT NULL AUTO_INCREMENT,
20  clan_name VARCHAR(255) NOT NULL,
21  primogen_id INT NOT NULL,
22  PRIMARY KEY (clan_id),
23  FOREIGN KEY (primogen_id) REFERENCES Vampires(vampire_id)
24);
25
26INSERT INTO Vampires(vampire_name, generation, clan_id, sire_id) VALUES
27                            ('Blake', 8, 1, NULL),
28                            ('Syn', 11, 1, NULL),
29                            ('Robyn', 12, 1, 2),
30                            ('Secundus', 10, 2, NULL),
31                            ('Zack', 11, 2, 4),
32                            ('Fishmalk', 11, 3, NULL),
33                            ('Andrew', 12, 3, 6),
34                            ('Greg', 13, 3, 7),
35                            ('Orlok', 7, 4, NULL),
36                            ('Nathaniel', 9, 4, NULL),
37                            ('Lestat', 10, 5, NULL),
38                            ('Meda', 11, 5, 11),
39                            ('Aiden', 12, 5, 12);
40
41INSERT INTO Clans(clan_name, primogen_id) VALUES
42                                        ('Brujah', 1),
43                                        ('Gangrel', 4),
44                                        ('Malkavian', 6),
45                                        ('Nosferatu', 9),
46                                        ('Toreador', 11);
47