practice_time_setup.sql

CREATE DATABASE samurai_db;
USE samurai_db;

-- clans with home provinces
CREATE TABLE clan (
                      clan_id INT PRIMARY KEY,
                      name VARCHAR(100) NOT NULL,
                      province VARCHAR(100) NOT NULL
);

-- dojos where samurai train
CREATE TABLE dojo (
                      dojo_id INT PRIMARY KEY,
                      name VARCHAR(120) NOT NULL,
                      city VARCHAR(100) NOT NULL,
                      style VARCHAR(80) NOT NULL
);

-- samurai linked to clan + dojo
CREATE TABLE samurai (
                         samurai_id INT PRIMARY KEY,
                         name VARCHAR(120) NOT NULL,
                         rank VARCHAR(50) NOT NULL,         -- e.g., retainer, hatamoto, ronin
                         birth_year INT,
                         clan_id INT,
                         dojo_id INT,
                         FOREIGN KEY (clan_id) REFERENCES clan(clan_id),
                         FOREIGN KEY (dojo_id) REFERENCES dojo(dojo_id)
);

-- historic battles
CREATE TABLE battle (
                        battle_id INT PRIMARY KEY,
                        name VARCHAR(150) NOT NULL,
                        year INT NOT NULL,
                        province VARCHAR(100) NOT NULL
);

-- participation of samurai in battles (junction)
CREATE TABLE participation (
                               samurai_id INT NOT NULL,
                               battle_id INT NOT NULL,
                               role VARCHAR(80) NOT NULL,         -- vanguard, rear guard, commander, scout
                               valor_points INT NOT NULL,         -- arbitrary score for practice
                               casualties_caused INT NOT NULL,    -- for aggregation
                               PRIMARY KEY (samurai_id, battle_id),
                               FOREIGN KEY (samurai_id) REFERENCES samurai(samurai_id),
                               FOREIGN KEY (battle_id) REFERENCES battle(battle_id)
);

-- weapons
CREATE TABLE weapon (
                        weapon_id INT PRIMARY KEY,
                        name VARCHAR(100) NOT NULL,
                        type VARCHAR(60) NOT NULL          -- katana, yari, yumi, naginata, tanto
);

-- mastery levels of samurai with weapons (junction)
CREATE TABLE mastery (
                         samurai_id INT NOT NULL,
                         weapon_id INT NOT NULL,
                         level INT NOT NULL,                -- 1..10
                         PRIMARY KEY (samurai_id, weapon_id),
                         FOREIGN KEY (samurai_id) REFERENCES samurai(samurai_id),
                         FOREIGN KEY (weapon_id) REFERENCES weapon(weapon_id)
);


-- clans
INSERT INTO clan (clan_id, name, province) VALUES
                                               (1, 'tokugawa', 'mikawa'),
                                               (2, 'oda', 'owari'),
                                               (3, 'uesugi', 'echigo');

-- dojos
INSERT INTO dojo (dojo_id, name, city, style) VALUES
                                                  (1, 'tenshin shōden katori shintō-ryū', 'edo', 'kenjutsu'),
                                                  (2, 'hoshino dōjō', 'kyoto', 'iaijutsu'),
                                                  (3, 'kasuga dōjō', 'kanazawa', 'yari-jutsu');

-- samurai
INSERT INTO samurai (samurai_id, name, rank, birth_year, clan_id, dojo_id) VALUES
                                                                               (1, 'ito masanori', 'hatamoto', 1565, 1, 1),
                                                                               (2, 'sakai tadatsugu', 'retainer', 1550, 1, 1),
                                                                               (3, 'aoyama kageyuki', 'retainer', 1572, 2, 2),
                                                                               (4, 'kato yoshinari', 'ronin', 1560, NULL, 2),
                                                                               (5, 'naito masahide', 'retainer', 1568, 2, 3),
                                                                               (6, 'nagasawa kanetsugu', 'retainer', 1566, 3, 3);

-- battles
INSERT INTO battle (battle_id, name, year, province) VALUES
                                                         (1, 'battle of nagashino', 1575, 'mikawa'),
                                                         (2, 'siege of odawara',   1590, 'sagami'),
                                                         (3, 'battle of sekigahara', 1600, 'mino'),
                                                         (4, 'skirmish at kasugayama', 1582, 'echigo');

-- participation
INSERT INTO participation (samurai_id, battle_id, role, valor_points, casualties_caused) VALUES
                                                                                             (1, 1, 'vanguard', 85, 7),
                                                                                             (1, 3, 'commander', 92, 10),
                                                                                             (2, 1, 'rear guard', 60, 3),
                                                                                             (2, 3, 'vanguard', 78, 6),
                                                                                             (3, 2, 'scout', 55, 1),
                                                                                             (3, 3, 'rear guard', 71, 2),
                                                                                             (4, 2, 'scout', 40, 0),
                                                                                             (4, 3, 'vanguard', 66, 2),
                                                                                             (5, 1, 'vanguard', 74, 4),
                                                                                             (5, 4, 'scout', 58, 1),
                                                                                             (6, 4, 'commander', 88, 5);

-- weapons
INSERT INTO weapon (weapon_id, name, type) VALUES
                                               (1, 'uchigatana', 'katana'),
                                               (2, 'yari', 'spear'),
                                               (3, 'yumi', 'bow'),
                                               (4, 'naginata', 'polearm'),
                                               (5, 'tanto', 'dagger');

-- mastery
INSERT INTO mastery (samurai_id, weapon_id, level) VALUES
                                                       (1, 1, 9),
                                                       (1, 2, 7),
                                                       (2, 1, 8),
                                                       (2, 3, 6),
                                                       (3, 2, 8),
                                                       (3, 3, 7),
                                                       (4, 1, 6),
                                                       (4, 5, 5),
                                                       (5, 2, 7),
                                                       (5, 4, 6),
                                                       (6, 3, 9),
                                                       (6, 4, 8);