Legends of the Balkans - Relational Design

Signs

Below are the annotations used in the relational schema:

• * (asterisk) → Denotes a Primary Key.
• # (hash) → Denotes a Foreign Key.
• (A, B, C) → Represents the attributes within a table.
• [ ] → Represents optional fields (nullable).

Tables

Full list of tables:

• Player (*PlayerID, Username, Email, Password)
• Character (*CharacterID, #PlayerID, Name, Health, EXP, Gold)
• Faction (*FactionID, Name, Description)
• ReputationIn (*#CharacterID, *#FactionID, ReputationPoints, Rank) (tracks a character's reputation with a faction)
• Quest (*QuestID, Name, EXP_Required, EXP_Reward, Gold_Reward)
• Item (*ItemID, Name, Type, Rarity, Craftable)
• Enemy (*EnemyID, Name, Health, Damage, LootDrop)
• Trader (*TraderID, Name, Type)
• Market (*MarketID, #ItemID, #SellerID, Price)
• Guild (*GuildID, Name, #LeaderID)
• Character_Quest (*#CharacterID, *#QuestID)
• Character_Item (*#CharacterID, *#ItemID, Quantity)
• Enemy_Item (*#EnemyID, *#ItemID, Quantity)
• Trader_Item (*#TraderID, *#ItemID, Quantity)
• Character_Guild (*#CharacterID, *#GuildID)
• IsMember (*#CharacterID, *#GuildID, Role) (defines whether a character is a member or a leader of a guild)

DDL Script

The Data Definition Language (DDL) script defines the database schema.

lotb_ddl.sql​

DML Script

The Data Manipulation Language (DML) script contains sample data insertions.

lotb_dml.sql​

Relational Diagram

The relational schema diagram will be placed here: