relational-schema: lotb_ddl.sql

DROP TABLE IF EXISTS Character_Guild, Trader_Item, Enemy_Item, Character_Item, Character_Quest, Market, Trader, Enemy, Item, Quest, Reputation, Faction, Character, Guild, Player CASCADE;

CREATE TABLE Player (
    PlayerID SERIAL PRIMARY KEY,
    Username VARCHAR(50) UNIQUE NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Password TEXT NOT NULL
);

CREATE TABLE Character (
    CharacterID SERIAL PRIMARY KEY,
    PlayerID INT NOT NULL,
    Name VARCHAR(50) UNIQUE NOT NULL,
    Health INT NOT NULL DEFAULT 100,
    EXP INT NOT NULL DEFAULT 0,
    Gold INT NOT NULL DEFAULT 0,
    FOREIGN KEY (PlayerID) REFERENCES Player(PlayerID) ON DELETE CASCADE
);

CREATE TABLE Faction (
    FactionID SERIAL PRIMARY KEY,
    Name VARCHAR(50) UNIQUE NOT NULL,
    Description TEXT NOT NULL
);

CREATE TABLE Reputation (
    CharacterID INT NOT NULL,
    FactionID INT NOT NULL,
    ReputationPoints INT NOT NULL DEFAULT 0,
    Rank VARCHAR(50) NOT NULL,
    PRIMARY KEY (CharacterID, FactionID),
    FOREIGN KEY (CharacterID) REFERENCES Character(CharacterID) ON DELETE CASCADE,
    FOREIGN KEY (FactionID) REFERENCES Faction(FactionID) ON DELETE CASCADE
);

CREATE TABLE Quest (
    QuestID SERIAL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    EXP_Required INT NOT NULL DEFAULT 0,
    EXP_Reward INT NOT NULL,
    Gold_Reward INT NOT NULL
);

CREATE TABLE Item (
    ItemID SERIAL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Type VARCHAR(50) NOT NULL,
    Rarity VARCHAR(20) NOT NULL,
    Craftable BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE Enemy (
    EnemyID SERIAL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Health INT NOT NULL,
    Damage INT NOT NULL,
    LootDrop BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE Trader (
    TraderID SERIAL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Type VARCHAR(50) NOT NULL
);

CREATE TABLE Market (
    MarketID SERIAL PRIMARY KEY,
    ItemID INT NOT NULL,
    SellerID INT NOT NULL,
    Price INT NOT NULL CHECK (Price >= 0),
    FOREIGN KEY (ItemID) REFERENCES Item(ItemID) ON DELETE CASCADE,
    FOREIGN KEY (SellerID) REFERENCES Player(PlayerID) ON DELETE CASCADE
);

CREATE TABLE Guild (
    GuildID SERIAL PRIMARY KEY,
    Name VARCHAR(100) UNIQUE NOT NULL,
    LeaderID INT NOT NULL,
    FOREIGN KEY (LeaderID) REFERENCES Player(PlayerID) ON DELETE SET NULL
);

CREATE TABLE Character_Quest (
    CharacterID INT NOT NULL,
    QuestID INT NOT NULL,
    PRIMARY KEY (CharacterID, QuestID),
    FOREIGN KEY (CharacterID) REFERENCES Character(CharacterID) ON DELETE CASCADE,
    FOREIGN KEY (QuestID) REFERENCES Quest(QuestID) ON DELETE CASCADE
);

CREATE TABLE Character_Item (
    CharacterID INT NOT NULL,
    ItemID INT NOT NULL,
    PRIMARY KEY (CharacterID, ItemID),
    FOREIGN KEY (CharacterID) REFERENCES Character(CharacterID) ON DELETE CASCADE,
    FOREIGN KEY (ItemID) REFERENCES Item(ItemID) ON DELETE CASCADE
);

CREATE TABLE Enemy_Item (
    EnemyID INT NOT NULL,
    ItemID INT NOT NULL,
    PRIMARY KEY (EnemyID, ItemID),
    FOREIGN KEY (EnemyID) REFERENCES Enemy(EnemyID) ON DELETE CASCADE,
    FOREIGN KEY (ItemID) REFERENCES Item(ItemID) ON DELETE CASCADE
);

CREATE TABLE Trader_Item (
    TraderID INT NOT NULL,
    ItemID INT NOT NULL,
    PRIMARY KEY (TraderID, ItemID),
    FOREIGN KEY (TraderID) REFERENCES Trader(TraderID) ON DELETE CASCADE,
    FOREIGN KEY (ItemID) REFERENCES Item(ItemID) ON DELETE CASCADE
);

CREATE TABLE Character_Guild (
    CharacterID INT NOT NULL,
    GuildID INT NOT NULL,
    PRIMARY KEY (CharacterID, GuildID),
    FOREIGN KEY (CharacterID) REFERENCES Character(CharacterID) ON DELETE CASCADE,
    FOREIGN KEY (GuildID) REFERENCES Guild(GuildID) ON DELETE CASCADE
);