ChicagoVampires

DROP DATABASE IF EXISTS ChicagoVampires;
CREATE DATABASE ChicagoVampires;
USE ChicagoVampires;

DROP TABLE IF EXISTS Vampires;
CREATE TABLE Vampires
(
    vampire_id INT NOT NULL AUTO_INCREMENT,
    vampire_name VARCHAR(15) NOT NULL,
    generation INT NOT NULL,
    clan_id INT NOT NULL,
    sire_id INT,
    PRIMARY KEY (vampire_id)
);

DROP TABLE IF EXISTS Clans;
CREATE TABLE Clans
(
  clan_id INT NOT NULL AUTO_INCREMENT,
  clan_name VARCHAR(255) NOT NULL,
  primogen_id INT NOT NULL,
  PRIMARY KEY (clan_id),
  FOREIGN KEY (primogen_id) REFERENCES Vampires(vampire_id)
);

INSERT INTO Vampires(vampire_name, generation, clan_id, sire_id) VALUES
                            ('Blake', 8, 1, NULL),
                            ('Syn', 11, 1, NULL),
                            ('Robyn', 12, 1, 2),
                            ('Secundus', 10, 2, NULL),
                            ('Zack', 11, 2, 4),
                            ('Fishmalk', 11, 3, NULL),
                            ('Andrew', 12, 3, 6),
                            ('Greg', 13, 3, 7),
                            ('Orlok', 7, 4, NULL),
                            ('Nathaniel', 9, 4, NULL),
                            ('Lestat', 10, 5, NULL),
                            ('Meda', 11, 5, 11),
                            ('Aiden', 12, 5, 12);

INSERT INTO Clans(clan_name, primogen_id) VALUES
                                        ('Brujah', 1),
                                        ('Gangrel', 4),
                                        ('Malkavian', 6),
                                        ('Nosferatu', 9),
                                        ('Toreador', 11);